Dataformを活用したBigQueryのテーブルメタデータ管理 Now in REALITY Tech #116
どうも、サーバエンジニアのうすぎぬです。今回はREALITYのデータ分析基盤改善のために結成された「DataEngineeringチーム」についてお話しします。
REALITYのデータ分析体制の課題
最近、REALITYでは施策開発・運用に積極的な仮説検証を取り入れることで、分析タスクが増加し、データアナリストの作業が圧迫される問題が生じています。その結果、分析依頼から完了までのリードタイムが増加し、場合によっては分析を断念するケースもポツポツと見られるようになりました。 そこで、これまでデータアナリストや一部のPdMのみが担当していた分析体制を見直し、その他のPdMやエンジニアも分析作業を担えるような体制を構築したいと考えました。
また、REALITYではBigQueryに全ての分析用ログデータを集約していますが、現状テーブルスキーマやデータ品質を適切に管理できていないため、データを活用する難易度が非常に高いという問題があります。データを適切に利用するためにはドメイン知識や機能開発の変遷を理解する必要がありますし、簡単な分析をこなすだけでも複雑なリレーションを構築する必要がある現在の環境では分析担当者を増やすこと自体に大きなコストがかかってしまいます。
そこで、この問題を解決するためにデータを誰でも扱いやすい状態に管理することを目標にした「DataEngineeringチーム」を有志で組成しました。本記事では、DataEngineeringチームの取り組みの一例である「スプレッドシートからのメタデータ移管」について解説します。
REALITYのログテーブルに関するメタデータの管理事情
REALITYではBigQueryのテーブルやカラムの説明などのメタデータをスプレッドシートで管理していました。例えば、新規機能開発で分析用のログを追加する際には、PdMが仕様とともに分析用のログをスプレッドシート（ログ定義シート）に設計し、それをエンジニアがレビューする形で新たにログテーブルが定義されていました。そして、分析を実施する際にはログ定義シートで必要なログを検索し、ログが落ちるタイミング、テーブルやカラムの説明などを確認します。
しかし、この管理方法では、特にログの定義から時間が経つにつれて「カラムが機能改修によりいつの間にか廃止されている」「BigQueryのテーブルに存在するカラムが定義シートには存在しない（またその逆）」「カラムの説明が誤っている」などの、ログの定義と実データの実態が乖離する問題が多く発生することになりました。
そこで、DataEngineeringチームではこれらの「ログ定義シートと実データのズレ」を解消するためにテーブル・カラムの説明を全てDataplex(Data Catalog)に移管する作業を開始しました。
Dataformで楽してメタデータを登録する
テーブル・カラムの説明の登録にはDataformを利用しました。Dataformはデータパイプラインの開発と管理を支援するツールで、SQLの拡張形式であるSQLXを使ってデータ変換や集計のタスクを定義し、バージョン管理やテスト、デプロイの自動化を実現します。また、JavaScriptを利用して変数や条件分岐、繰り返し処理を表現可能で、複雑なデータ変換・動的なSQL生成を実現します。
（本記事ではDataform自体の詳しい説明は割愛します。代わりにDataformを利用するにあたって参考になった記事を紹介させていただきます。多謝。）
今回は、Dataformのワークスペース上にテーブル・カラムの説明をまとめたJSONを保存し、JSONのコンテンツを一括でテーブル・カラムに反映する処理を作成しました。WebUIから一つずつぽちぽち説明を追加するよりも遥かに楽にメタデータを登録できるようになりました。JavaScriptによるSQLの動的生成機能が本当に便利でした。
// descriptions.json
// definitions配下に設置する
[
{
"table_name": "example_table_name",
"table_description": "テーブルの説明をここに書きます。",
"columns": [
{
"name": "log_id",
"description": "log_idの説明をここに書きます。"
},
{
"name": "time",
"description": "timeの説明をここに書きます。"
},
{
"name": "hogehoge",
"description": "hogehogeの説明をここに書きます。"
},
{
"name": "fugafuga",
"description": "fugafugaの説明をここに書きます。"
}
]
}
]// update_descriptions.js
// descriptions.jsonと同階層に設置する
// description定義をimportする
const descriptions = require('./descriptions.json');
// 実行タグ(https://cloud.google.com/dataform/docs/tags?hl=ja)
const executionTags = ["UPDATE_DESCRIPTIONS"];
// descriptionを更新する
for (const details of descriptions) {
const tableName = details.table_name;
const tableDescription = details.table_description;
const columns = details.columns;
// テーブルのdescriptionを更新
operate(`${tableName}_update_table_description`)
.tags(executionTags) // 実行タグを付与
.queries(ctx => `
ALTER TABLE \`${YOUR_PROJECT_ID}.${YOUR_DATASET_ID}.${tableName}\`
SET OPTIONS (
description = '${tableDescription}'
)
`);
// カラムのdescriptionを更新
if (columns.length === 0) continue
const alterColumnStatements = columns
.map( (column) => `ALTER COLUMN \`${column.name}\` SET OPTIONS (description = '${column.description}')` )
.join(',\n');
operate(`${tableName}_update_column_descriptions`)
.tags(executionTags) // 実行タグを付与
.queries(ctx => `
ALTER TABLE \`${YOUR_PROJECT_ID}.${YOUR_DATASET_ID}.${tableName}\`
${alterColumnStatements}
`);
}また、既存のテーブルのdescriptionを集約したJSONを作成する処理も作成しました。テーブルとカラムの説明を取得するViewをDataformで作成し、BigQueryのNodeクライアントライブラリから実行する形を取りました。
// aggregate_descriptions.sqlx
// definitions配下に設置する
config {
type: "view",
name: "table_and_column_descriptions_view",
description: "テーブル・カラムのdescriptionを集約する",
columns: {
table_name: "テーブル名",
table_description: "テーブルの説明",
columns: "カラム名・カラムの説明",
}
}
js {
// 対象のテーブル名を入力する
const raw_target_tables = [
"hogehoge",
"fugagfuga"
]
const target_tables = raw_target_tables.map(table => `"${table}"`).join(',\n');
}
WITH table_info AS (
-- raw_target_tablesで指定したテーブル情報を取得する
SELECT
table_name AS table_name,
COALESCE(option_value, '') AS description
FROM
`${YOUR_PROJECT_ID}.${YOUR_DATASET}.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE
table_name IN (${target_tables}) -- jsの変数使えるの本当に便利です
AND option_name = 'description'
),
column_info AS (
-- raw_target_tablesで指定したカラム情報を取得する
SELECT DISTINCT
table_name AS table_name,
field_path AS name,
COALESCE(description, '') AS description
FROM
`${YOUR_PROJECT_ID}.${YOUR_DATASET}.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE
table_name IN (${target_tables})
)
SELECT
t.table_name AS table_name,
t.description AS table_description,
ARRAY_AGG(STRUCT(c.name, c.description)) AS columns
FROM
table_info t
LEFT JOIN
column_info c
ON
t.table_name = c.table_name
GROUP BY
t.table_name, t.description
// BigQueryのNode.jsクライアントライブラリを利用して、descriptions.jsonを生成する
const { BigQuery } = require("@google-cloud/bigquery");
const fs = require("fs");
// BigQueryにクエリを投げて結果をjsonで保存する
async function query() {
const bigquery = new BigQuery();
const query =
"SELECT * FROM `${YOUR_PROJECT_ID}.${YOUR_DATASET}.table_and_column_descriptions_view`";
const options = {
query: query,
location: `${YOUR_LOCATION}`,
};
const [job] = await bigquery.createQueryJob(options);
const [rows] = await job.getQueryResults();
const json = JSON.stringify(rows, null, 2);
fs.writeFileSync("descriptions.json", json);
}
async function exec() {
await query();
}
exec();（ちなみに、最初はログ定義シートからGASを使ってJSONを一撃で生成しようと考えていましたが、実データとの乖離が大きすぎて有志メンバーでメタデータの真偽を確認しながら作業を進めています。）
Dataplex(Data Catalog)で楽してデータを探索する
Dataplexでは次の画像のようにログを検索できます。常にBigQueryの最新のテーブルスキーマ・メタデータから検索されますし、検索結果からBigQuery Studioにそのまま遷移できるのは本当に便利です。欲しいカラムの説明が欠損し、テーブル定義を検索するのにも苦労したかつてのログ定義シート時代のことを思うと涙を禁じ得ません。
まとめ
今回紹介した内容はDataEngineeringチーム組成後の最初の取り組みです。REALITYのデータ分析基盤には他にも多くの課題がありますが、精力的なメンバーにより少しずつ改善されていくことでしょう。近年はデータ分析の民主化をサポートするツールが数多く登場していますが、個人的には今後それらを活用していくことも非常に楽しみにしています。（今回の取り組みもGemini in BigQueryやBigQuery Data Canvasの精度向上を視野に入れた内容でありました。）