Snowflake初心者がGA4のデータをSnowflakeに連携してみた

【オフライン限定】Snowflakeの小技LT〜ChatGPTも知らない？秘密のテクニック教えて！〜

Transcript

1. Snowflake初心者がGA4のデータを Snowflakeに連携してみた Snowflakeの小技LT スターフェスティバル 山﨑 皓平(koonagi3)
2. 自己紹介 2 山﨑 皓平(@koonagi3) 所属 ロール 趣味 好きな Snowflake の機能 スターフェスティバル株式会社 インフラ/データ基盤エンジニア 子供/愛猫と遊ぶ Python Worksheets
3. 今日の話 © 2021 STAR FESTIVAL INC. 8 4月にSnowflakeを導入し、今月GA4のデータをSnowflakeに連携 するようにしました。 本日は、連携の構成やポイントについて共有できればと思います。 似たような構成であれば、流用できるところがあると思いますの で、なにかご参考になればと思います。 3
4. スターフェスティバルについて 8
5. やりたいこと 5 ごちクルのサイト情報をSnowflake上で分析したい！！！
6. やりたいこと 6 やってみた！
7. ゴールイメージ 7 BigQuery上にあるGA4のデータを、Snowflake上のデータベースに格納する
8. 構成 8 GA4からS3へのデータ連携には embulk タスクを使って定期取り込み
9. 【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: <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: <bucket_name> 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に連携
10. 【s3 → Snowflake】 テーブル定義 10 <テーブル定義> RAW_DATA VARIANT, _LOAD_AT TIMESTAMP_NTZ(9), _LOAD_DAILY DATE ▪ point - 今後GA4側でフィールドが変更されることを考 慮して、Snowflake取り込み時には、RAWデー タをそのまま格納する - Snowflakeのメタデータから、ロード時間を取 得して格納するカラムも合わせて作成 フィールドの変更を考慮したデータロード 参考 Snowflake Snowpipeを本番導入する前 に読むやつ
11. 【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://<s3 bucket name >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. 12 embulkとSnowflakeのタスクを使ってロードできました！
13. 13 python Worksheetsからもデータ読み込めました！
14. 調べ物をする際に参考になったサイト 14 Snowflake ドキュメント [DevelopersIO] Snowflakeの記事一覧 まず、チュートリアルをやってみたり、機能の調 査に使いました。 SNOWDAYのレポートなどを見て、設計の参考 に使いました。
15. さいごに • データ連携はembulkが結構楽で良かった • Snowflakeを初めてガッツリ触りましたが、公式ドキュメントが充実していたの で、結構すんなりデータロードまで行くことができた。 • タスクのロードをバルクでやったけど、Snowpipeのほうが楽なのでは...?とこ の資料を書いていて気が付きました（後で試す 15
16. ご清聴ありがとうございました 16