Cloud StorageのログをBigQueryに自動で転送する

Cloud StorageのアクセスログをCloud Storageに出力する機能がある。
https://cloud.google.com/storage/docs/access-logs?hl=ja

このドキュメントでもBigQueryにログを突っ込む手順を解説しているが、ローカルにダウンロードしてローカルで加工してという方法でやっているが、BigQueryの機能を駆使すればそんな手間は不要である。あとBigQueryに突っ込むのにパーティショニングする手順が抜けているのもよくない。

というか、ドキュメントではCloud Storage上にログを吐くより、Cloud Audit Logsを推している。が、この設定をするとAnonymousのアクセスができなくなるという制限（不具合?）がある。
https://cloud.google.com/storage/docs/troubleshooting#trouble-download-storage-cloud

そういう制限がなくても、BigQueryのSQLは割とリッチなので、BigQueryに突っ込んでおくと何かと捗る。
BigQueryにCloud Storage上のCSVを転送する機能があるので、それを活用する方法を説明する。

Cloud Storage上にログを出力するようにする

手順はこちらを参照
https://cloud.google.com/storage/docs/access-logs?hl=ja#delivery
これで指定されたバケットにアクセスログが出力される。

BigQueryに転送する

同じページにBigQueryに流し込む手順があるが、スキーマの設定ファイル(cloud_storage_usage_schema_v0.json)だけ借用する。
https://cloud.google.com/storage/docs/access-logs?hl=ja#BigQuery

BigQueryにはパーティショニングという透過的にシャーディングしてくれる機能がある。クエリの範囲を絞ってコストを抑えることができるが、これをやらないとデータ量が多くなると大変なことになる。

作成時刻でパーティションするのがよさそうだが、生成されるログのcsvのtime_microsがintegerなので"時間単位列パーティショニング"はできない。なので取込み時間でパーティションするように、下記のコマンドでテーブルを作成した。

bq mk \
   --time_partitioning_type DAY \
   --table storageanalysis.usage \
   <ダウンロードDir>/cloud_storage_usage_schema_v0.json

後はこちらの手順でStorageからBigQueryに転送設定すればOK
https://cloud.google.com/bigquery-transfer/docs/cloud-storage-transfer?hl=ja

BigQueryで解析する

データが入ったらSQLを投げればよいが、URLパスをSQLでパースするのは辛い。BigQueryにはUDFをJSで書ける機能があるので、それを使えば楽にできる。

例えば、下記のSQLを投げると

CREATE TEMPORARY FUNCTION parsePath(path STRING)
    RETURNS STRUCT<a STRING, b STRING, c STRING>
    LANGUAGE js AS r"""
        const [a, b, c] = path?.split("/") ?? [];
        return {a, b, c};
""";

SELECT parsePath(cs_object).* FROM `storageanalysis.usage` WHERE DATE(_PARTITIONTIME) = "2022-04-18";

これでkeyの第1から第3階層までをそれぞれa,b,cというフィールドに割り当てることができる。
BigQueryはレコードや配列も扱うことができる。
先の例では、parsePathがa,b,cというフィールドを持つレコードを返し、.*でそのフィールドを展開している。