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

Transcript

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

6. 大量のイベントログ
   オークションログに限っても…
   ● 毎秒1万回以上 (Google Open Bidding のみ)
   ● どこの広告枠？
   ● 誰が入札して、どんな広告が出た？
   ● 値段は？
   ● etc.
   

   View Slide

7. 全体
   

   View Slide

8. 最初はAthenaを使っていた
   メリット
   ● 手軽
   ● S3に置いたログをそのまま読める
   デメリット
   ● 遅い
   ● テーブル定義が面倒（スキーマの変更に弱い）
   ● パーティションの作られるタイミングが謎
   社内ではBigQueryをよく使ってるし、そっちに乗っけてしまいたい…
   

   View Slide

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())
   

   View Slide

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
    

    View Slide

11. 雑なスキーマでも読み込みたい…
    ● スキーマの変更が（頻繁ではないが）ありうる
    ● JSONを全部パースするほどでもないフィールド
    ○ デバッグ用のフィールドなど
    ○ BigQueryにJSONを読ませようとすると、すべてパースされる
    1カラムにJSON文字列が全部入ったテーブルをつくればいいのでは！？
    （その後 JSON_EXTRACT で必要なフィールドのみ抽出できる）
    

    View Slide

12. 絶対に1カラムになる定義
    ● ファイル形式にJSONを指定するとパースされてしまう
    ● 他に1行1レコードとなるファイル形式はCSV
    ● デリミタやクォートに絶対入らない文字を指定すればいい
    

    View Slide

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

    View Slide

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
    }
    }
    ここに制御文字を使いたい
    課金事故を防ぐために
    クエリ時のパーティション指定を強制
    

    View Slide

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
    

    View Slide

16. 誤魔化す
    def check_printable(self, data):
    pass
    他にいい方法あったら教えて下さい！！！！！！！！！！
    

    View Slide

17. BigQuery Scheduled Queries
    ● 指定クエリを定期実行してくれる
    ○ 1日ごと、1時間ごと、cronもどきなど
    ● クエリパラメータに実行時間を使える
    ○ @run_time (TIMESTAMP型)
    ● クエリ結果のテーブルを作れる
    ○ destination table
    ○ テーブル名に実行時間をフォーマットして使える
    ■ table_{run_time+8h|"%Y%m%d"}
    ● 1時間前のデータを1時間後にクエリするので+8h
    

    View Slide

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))
    

    View Slide

19. まとめ
    ● BigQueryは便利だぞ！
    ● External Tableは銀の弾丸かも
    ○ ゆるふわスキーマもOK
    ○ bq load に悩まされることもない
    ○ 速いは正義
    ● Scheduled Queriesは癖があるけど使いこなせると便利
    

    View Slide