自分が使いたいと思ったBigQuery上のリソース(tableやview)、内容を事前に完全に把握できている、ということは結構少ないのではないかと思います。そういったときに手助けをしてくれるのがメタデータです。BigQueryのリソースに対するメタデータを、Cloud Data Catalogのタグとして付与する方法を紹介します。Cloud Data Catalogを使うことで、分析者が必要なリソースに素早く辿り付いたり、正確な分析をするためのサポートができます。
BigQuery関連のAudit logを元に、以下の情報をData Catalogのタグに入れた。
— Yasuhisa Yoshida (@syou6162) 2020年5月16日
- 最後にクエリを投げた{日, 人}
- クエリを投げられた回数
「あまり使われていないので、信用できないデータかも」「最後にXXXさんがクエリ投げてるから、詳細詳しいかも」みたいな用途を想定してる pic.twitter.com/Wgo91UVy4B
Cloud Data Cagalogとは
BigQueryと比べると認知度は大分まだ低いと思うので、概要を先に紹介します。Cloud Data CagalogはGCPが面倒を見てくれるフルマネージドなメタデータ管理サービスです。フルマネージドというのがポイントで、チームにデータ整備人(データエンジニア)が一人しかいない!という状況でもオペレーションなど追加的な負荷少なく使うことができます。先日GAにもなったので、安心して使えますね。
BigQueryの{テーブル, カラム}のdescriptionにあれこれメタデータを書き込んでおけば、自動的にCloud Data Cagalogで検索できるようになります。(個人ではなく)チームでメタデータを整備していく体制を整える、という話題については先日ブログにも書きました。
BigQueryだけでなく、Cloud Pub/Sub、Cloud Storageのメタデータの管理もしてくれます*1。
また、「このカラムは個人情報が入っているから、チーム内でも特定の人しかクエリ叩けないようにしたい」ということ、割とあると思いますが、そういったこともData Catalogを使うと簡単にできるようになります。
もう少し構造化してメタデータを保持したい: Tag Template
descriptionに書き込むのはいいのですが、全部テキストでフラットに情報を持つのは段々厳しくなってくると思います。「最後にクエリを打たれた人をDATETIME型で」とか「クエリを叩かれた回数をINT型で」持ちたいという欲求は自然と出てくると思います。Cloud Data CagalogではそれをTagとして表現でき、Tagにどういった情報を持たせるかの雛形(この項目はSTRING型で必須、などを指定できる)をTag Templateとして表現します。
私はバッチ処理の中でTagを洗い替えするので十分なケースだったので、すでにTag Templateが存在すれば消して新規に作るようにしました。
#!/bin/bash set -ux TAG_TEMPLATE=resource_usage_info LOCATION=asia-northeast1 gcloud data-catalog tag-templates describe ${TAG_TEMPLATE} --location=${LOCATION} if [ $? -eq 0 ]; then gcloud data-catalog tag-templates delete ${TAG_TEMPLATE} --location=${LOCATION} --force --quiet fi gcloud data-catalog tag-templates create ${TAG_TEMPLATE} --location=${LOCATION} \ --display-name="Resource usage info" \ --field=id=last_queried_person,display-name="Last queried person",type=string \ --field=id=queries_counts,display-name="Queries count",type=double \ --field=id=last_queried_at,display-name="Last queried timestamp",type=timestamp
必要なメタデータをAudit logから取得する
次に考えたいのは「最後にクエリを投げられた日」や「クエリを投げられた回数」といった情報をどこから取ってきてTagにするか、です。このような情報はAudit logからBigQueryで取得できるようにしておくと簡単です。マジで10分でできる。
結構色んな情報が入ってくるので、Tagを作るときにやりやすいよう、以下のSQLをviewとして保存しておきます。名前は何でもいいですが、my_project.my_mart.tables_last_accessed_atみたいな感じで。
Audit logの情報からクエリの統計量を計算するクエリ
WITH raw_resource_usage AS ( SELECT protopayload_auditlog.authenticationInfo.principalEmail, timestamp AS last_queried_at, info.resource, ROW_NUMBER() OVER (PARTITION BY info.resource ORDER BY timestamp DESC) AS rank, FROM `my_project.source__cloudaudit__bigquery.cloudaudit_googleapis_com_data_access` CROSS JOIN UNNEST(protopayload_auditlog.authorizationInfo) AS info WHERE NOT REGEXP_CONTAINS(info.resource, r"LOAD_TEMP_") -- Embulk関係で呼ばれるものは計算に入れない AND NOT REGEXP_CONTAINS(info.resource, r"jobs") ), latest_access_by_resource AS ( SELECT * EXCEPT(rank) FROM raw_resource_usage WHERE rank = 1 ), resource_called_counts AS ( SELECT resource, COUNT(*) AS count FROM raw_resource_usage GROUP BY resource ORDER BY count DESC ), existing_tables AS ( SELECT project_id, dataset_id, table_id, FORMAT("projects/%s/datasets/%s/tables/%s", project_id, dataset_id, table_id) AS table_full_name FROM ( SELECT * FROM `my_project`.project__source__db.__TABLES__ UNION ALL SELECT * FROM `my_project`.project__warehouse.__TABLES__ UNION ALL SELECT * FROM `my_project`.project__mart.__TABLES__ ) ) SELECT dataset_id, table_id, principalEmail AS last_accessed_person, last_queried_at, resource_called_counts.count AS queries_counts FROM existing_tables INNER JOIN latest_access_by_resource ON latest_access_by_resource.resource = existing_tables.table_full_name INNER JOIN resource_called_counts ON resource_called_counts.resource = existing_tables.table_full_name ORDER BY last_queried_at
Tagとしてメタデータを登録
必要な情報がこのviewで簡単に取得できるようになったので、実際にTagとして登録していきます。
メタデータをTagとして登録するシェルスクリプト
#!/bin/bash set -ux PROJECT_ID=my_project TAG_TEMPLATE=resource_usage_info LOCATION=asia-northeast1 MY_TABLE=${PROJECT_ID}.my_mart.tables_last_accessed_at bq query --max_rows 10000 --format json --use_legacy_sql=false "SELECT * FROM ${MY_TABLE}" | jq -c '.[]' > resource_usage_info.json cat resource_usage_info.json | while read -r json; do echo $json | jq ' { "last_queried_person": .last_accessed_person, "queries_counts": .queries_counts | tonumber, "last_queried_at": .last_queried_at } ' > tag_file.json DATASET=$(echo $json | jq -r .dataset_id) TABLE=$(echo $json | jq -r .table_id) ENTRY_NAME=$(gcloud data-catalog entries lookup "//bigquery.googleapis.com/projects/${PROJECT_ID}/datasets/${DATASET}/tables/${TABLE}" --format="value(name)") TAG_NAME=$(gcloud data-catalog tags list --entry "projects/${PROJECT_ID}/locations/${LOCATION}/entryGroups/@bigquery/entries/$(echo ${ENTRY_NAME} | tr '/' '\n' | tail -n 1)" --format="value(name)") if [ -n "$TAG_NAME" ]; then gcloud data-catalog tags delete ${TAG_NAME} --quiet fi gcloud data-catalog tags create \ --entry=${ENTRY_NAME} \ --tag-template=${TAG_TEMPLATE} --tag-template-location=${LOCATION} --tag-file=tag_file.json --format=json done
これで完了です。こうしておくと、Cloud Data Catalogで検索した結果、このようにメタデータを登録することができます。このリソースの最近の利用状況、より詳しいことは誰に聞けばいいかといった情報が提供できるため、利用者がやりたかった分析に少し早く、より正確に到達できる状況にできるのではないかな、と思います。
*1:自分はまだBigQueryに対してだけしか使えていないけど
