BigQueryのテーブルのメタデータをCloud Data Catalogで管理する

自分が使いたいと思ったBigQuery上のリソース(tableやview)、内容を事前に完全に把握できている、ということは結構少ないのではないかと思います。そういったときに手助けをしてくれるのがメタデータです。BigQueryのリソースに対するメタデータを、Cloud Data Catalogのタグとして付与する方法を紹介します。Cloud Data Catalogを使うことで、分析者が必要なリソースに素早く辿り付いたり、正確な分析をするためのサポートができます。

BigQuery関連のAudit logを元に、以下の情報をData Catalogのタグに入れた。

- 最後にクエリを投げた{日, 人}
- クエリを投げられた回数

「あまり使われていないので、信用できないデータかも」「最後にXXXさんがクエリ投げてるから、詳細詳しいかも」みたいな用途を想定してる pic.twitter.com/Wgo91UVy4B
— Yasuhisa Yoshida (@syou6162) 2020年5月16日

Cloud Data Cagalogとは
もう少し構造化してメタデータを保持したい: Tag Template
必要なメタデータをAudit logから取得する
Tagとしてメタデータを登録

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に対してだけしか使えていないけど