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

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は癖があるけど使いこなせると便利