Snowflake初心者がGA4のデータを Snowflakeに連携してみた Snowflakeの小技LT スターフェスティバル 山﨑 皓平(koonagi3)

自己紹介 2 山﨑 皓平(@koonagi3) 所属 ロール 趣味 好きな Snowflake の機能 スターフェスティバル株式会社 インフラ/データ基盤エンジニア 子供/愛猫と遊ぶ Python Worksheets

今日の話 © 2021 STAR FESTIVAL INC. 8 4月にSnowflakeを導入し、今月GA4のデータをSnowflakeに連携 するようにしました。 本日は、連携の構成やポイントについて共有できればと思います。 似たような構成であれば、流用できるところがあると思いますの で、なにかご参考になればと思います。 3

スターフェスティバルについて 8

やりたいこと 5 ごちクルのサイト情報をSnowflake上で分析したい！！！

やりたいこと 6 やってみた！

ゴールイメージ 7 BigQuery上にあるGA4のデータを、Snowflake上のデータベースに格納する

構成 8 GA4からS3へのデータ連携には embulk タスクを使って定期取り込み

【GA4 → s3】 Embulkによる取り込み設定 9 {% capture day_before_yesterday %}{{ 'today' | date: '%s' | minus: 172800 | date: '%Y%m%d' }}{% endcapture %} {% assign year = day_before_yesterday | slice: 0, 4 %} {% assign month = day_before_yesterday | slice: 4, 2 %} {% assign day = day_before_yesterday | slice: 6, 2 %} in: type: bigquery_extract_files project: <プロジェクト名 > json_keyfile: '/config/production/keyfile-bigquery-embulk-input.json' gcs_uri: temp_local_path: /tmp/embulk/data dataset: <データセット名 > table: events_{{ day_before_yesterday }} file_format: 'NEWLINE_DELIMITED_JSON' compression: 'GZIP' decoders: - {type: gzip} parser: type: json out: type: s3_parquet bucket: path_prefix: parquet/embulk/ga4_analytics_events/{{ year }}/{{ month }}/{{ day }}/data. file_ext: snappy.parquet compression_codec: snappy default_timezone: Asia/Tokyo region: ap-northeast-1 ■ point - embulkはLiquid形式に対応してい るため、関数を利用して GA4側の パーティション分割テーブルを特定 できるようにする(テーブル名 _yyyymmdd) - 出力先のS3の日付パーティション できるように年月日でフォルダをき るようにした GA4の分割テーブルを毎日S3に連携

【s3 → Snowflake】 テーブル定義 10 <テーブル定義> RAW_DATA VARIANT, _LOAD_AT TIMESTAMP_NTZ(9), _LOAD_DAILY DATE ■ point - 今後GA4側でフィールドが変更されることを考 慮して、Snowflake取り込み時には、RAWデー タをそのまま格納する - Snowflakeのメタデータから、ロード時間を取 得して格納するカラムも合わせて作成 フィールドの変更を考慮したデータロード 参考 Snowflake Snowpipeを本番導入する前 に読むやつ

【s3 → Snowflake】 データ取り込み設定 11 外部テーブル作成とデータロードのストアドを作成 し、タスクで定期実行 ■ 外部テーブル作成のストアドから抜粋 // Get the date for the day before yesterday var currentDate = new Date(); currentDate.setDate(currentDate.getDate() - 2); // Format the date var year = currentDate.getFullYear(); var month = ("0" + (currentDate.getMonth() + 1)).slice(-2); // JavaScript months are 0-indexed var day = ("0" + currentDate.getDate()).slice(-2); // Generate the URL var url = `s3://parquet/embulk/ga4_analytics_events/${year}/${month}/${day}`; ■ point - タスク実行時に、外部テーブルを作成し、取り 込みたい日時のS3のパスを指定 - データロードでは、S3のパス配下をすべて取り 込むように - 外部テーブル作成時点で読み込み元の S3が 絞り込まれているので、実質的に日時の差分 取り込みをするようにする ■ データロードのストアドから抜粋 COPY INTO GA4_ANALYTICS_EVENTS FROM( SELECT $1::variant as raw_data, metadata$start_scan_time::timestamp_ntz as _load_at, time_slice(_load_at, 1, 'day')::date as _load_daily FROM @GA4_ANALYTICS_EVENTS_stage

12 embulkとSnowflakeのタスクを使ってロードできました！

13 python Worksheetsからもデータ読み込めました！

調べ物をする際に参考になったサイト 14 Snowflake ドキュメント [DevelopersIO] Snowflakeの記事一覧 まず、チュートリアルをやってみたり、機能の調 査に使いました。 SNOWDAYのレポートなどを見て、設計の参考 に使いました。

さいごに ● データ連携はembulkが結構楽で良かった ● Snowflakeを初めてガッツリ触りましたが、公式ドキュメントが充実していたの で、結構すんなりデータロードまで行くことができた。 ● タスクのロードをバルクでやったけど、Snowpipeのほうが楽なのでは...?とこ の資料を書いていて気が付きました（後で試す 15

ご清聴ありがとうございました 16