[Tech × Marketing meetup #4] 秒間1万のイベントログをDHWにETLするまで

[Tech × Marketing meetup #4] 秒間1万のイベントログをDHWにETLするまで

53a2c42ea86589c5916b550438de3128?s=128

Hiromu Kobayashi (himu)

October 14, 2020
Tweet

Transcript

  1. 秒間1万のイベントログを DHWにETLするまで @__himu__ 2020/08/27

  2. 自己紹介 Twitter@__himu__ VOYAGE GROUP/fluct 2017〜現在 ちょっと前までGo, 最近はPython ボルダリング, ゲーム

  3. 秒間1万のイベントログとは?

  4. A. RTBのオークションログ

  5. Real-Time Bidding 広告の1インプレッション(表示)毎にリアルタイムのオークション方式で広告を 選択するシステムで、広告主や広告会社から入札された中から最も単価の高 い広告が配信される仕組み。

  6. 大量のイベントログ オークションログに限っても… • 毎秒1万回以上 (Google Open Bidding のみ) • どこの広告枠?

    • 誰が入札して、どんな広告が出た? • 値段は? • etc.
  7. 全体

  8. 最初はAthenaを使っていた メリット • 手軽 • S3に置いたログをそのまま読める デメリット • 遅い •

    テーブル定義が面倒(スキーマの変更に弱い) • パーティションの作られるタイミングが謎 社内ではBigQueryをよく使ってるし、そっちに乗っけてしまいたい…
  9. Lambda • s3:ObjectCreated でトリガー • パス構造、オブジェクト名そのままにコピーするだけ for record in event['Records']:

    stream = io.BytesIO() s3_bucket = record['s3']['bucket']['name'] key = unquote_plus(record['s3']['object']['key']) s3_client.download_fileobj (s3_bucket, key, stream) gcs_bucket = gcs_client.bucket('hogehoge') blob = gcs_bucket.blob(key) blob.upload_from_string (stream.getvalue())
  10. BigQuery External Table • 雑に言うと「GCP版Athena」 • Google Cloud Storageなどに置いたファイルをBigQueryで読める •

    Loadできるフォーマットなら使える (CSV, JSON Lines, etc.) • HIVEパーティションが使える • Cloud Storage ソースで使ってみた感じ、かなり速い bq mk --external_table_definition=tabledef.json dataset.table
  11. 雑なスキーマでも読み込みたい… • スキーマの変更が(頻繁ではないが)ありうる • JSONを全部パースするほどでもないフィールド ◦ デバッグ用のフィールドなど ◦ BigQueryにJSONを読ませようとすると、すべてパースされる 1カラムにJSON文字列が全部入ったテーブルをつくればいいのでは!?

    (その後 JSON_EXTRACT で必要なフィールドのみ抽出できる)
  12. 絶対に1カラムになる定義 • ファイル形式にJSONを指定するとパースされてしまう • 他に1行1レコードとなるファイル形式はCSV • デリミタやクォートに絶対入らない文字を指定すればいい

  13. 絶対に入らない文字 • 「制御文字」です • ログにはUnicodeなどを含む任意の文字が入りうる • JSONは制御文字を使えない(エスケープしなければならない) • BigQueryのCSVデリミタには制御文字が使える

  14. 外部テーブル定義ファイルはJSON { "csvOptions": { "fieldDelimiter" : "", "quote": "" },

    "schema": { "fields": [ { "name": "fields", "type": "STRING" } ] }, "sourceFormat" : "CSV", "sourceUris": [ "gs://hogehoge/*.gz" ], "hivePartitioningOptions" : { "mode": "STRINGS", "sourceUriPrefix" : "gs://hogehoge/" , "requirePartitionFilter" : true } } ここに制御文字を使いたい 課金事故を防ぐために クエリ時のパーティション指定を強制
  15. 怒られる File ".../bq/third_party/yaml/lib2/reader.py", line 144, in check_printable 'unicode', "special characters

    are not allowed") ReaderError: unacceptable character #x0001: special characters are not allowed in "tabledef.json", position 174
  16. 誤魔化す def check_printable(self, data): pass 他にいい方法あったら教えて下さい!!!!!!!!!!

  17. BigQuery Scheduled Queries • 指定クエリを定期実行してくれる ◦ 1日ごと、1時間ごと、cronもどきなど • クエリパラメータに実行時間を使える ◦

    @run_time (TIMESTAMP型) • クエリ結果のテーブルを作れる ◦ destination table ◦ テーブル名に実行時間をフォーマットして使える ▪ table_{run_time+8h|"%Y%m%d"} • 1時間前のデータを1時間後にクエリするので+8h
  18. クエリ抜粋 SELECT JSON_EXTRACT_SCALAR(fields, '$.req.id') AS `req_id`, STRUCT< `creative_id` STRING, `price`

    FLOAT64 >( JSON_EXTRACT_SCALAR(fields, '$.res.creative_id'), CAST(JSON_EXTRACT_SCALAR(fields, '$.res.price') AS FLOAT64) ) AS `res`, JSON_EXTRACT(fields, '$.debug') AS `debug`, FROM `hogehoge` WHERE ts = FORMAT_TIMESTAMP('%Y%m%d%H', TIMESTAMP_ADD(@run_time, INTERVAL -1 HOUR))
  19. まとめ • BigQueryは便利だぞ! • External Tableは銀の弾丸かも ◦ ゆるふわスキーマもOK ◦ bq

    load に悩まされることもない ◦ 速いは正義 • Scheduled Queriesは癖があるけど使いこなせると便利