こんにちは！データサイエンス室（以下、DS室）の山本です（@__Y4M4MOTO__）です。
弊社ではBigQueryのINFORMATION_SCHEMA.JOBS_BY_PROJECTビューを独自で保存しています。理由は、JOBS_BY_PROJECTビューに含まれる情報は180日前までの情報しか載っていないからです1。
弊社では「Yappli Analytics」というアプリ運用ダッシュボードをLooker Studioで提供しています。カスタマーサクセス活動の一環でこのYappli Analyticsの利用状況をモニタリングする必要があり、そのために180日より前のJOBS_BY_PROJECTビューの情報を保存しておく必要がありました。
このとき、将来の分析要件が不確定だったため、汎用性を確保する目的で特定のカラムに絞らず、全カラムを保存するという方針を立てました。しかし、この「全カラム保存」が、運用上、地味に面倒な問題を引き起こすことが判明しました。
本記事では、その詳細と我々の対処法について共有します。
保存用ジョブ
TROCCOで次のクエリを毎日実行することで保存しています。ジョブが失敗してもリカバリーしやすいよう直近7日のDELETE+INSERTで更新するようにしています。
CREATE TEMP FUNCTION timezone() AS ('Asia/Tokyo'); CREATE TEMP FUNCTION today() AS (CURRENT_DATE(timezone())); CREATE TEMP FUNCTION upsert_date() AS (DATE_SUB(today(), INTERVAL 7 DAY)); CREATE TEMP FUNCTION isin_upsert_date_range(creation_time TIMESTAMP) AS ( upsert_date() < DATE(creation_time, timezone()) AND DATE(creation_time, timezone()) < today() ); DELETE FROM `DESTINATION_PROJECT.DESTINATION_DATASET.jobs_by_ds_projects` WHERE DATE(creation_time, timezone()) > upsert_date() ; INSERT `DESTINATION_PROJECT.DESTINATION_DATASET.jobs_by_ds_projects` SELECT * FROM ( SELECT * FROM `PROJECT_1`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE isin_upsert_date_range(creation_time) UNION ALL SELECT * FROM `PROJECT_2`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE isin_upsert_date_range(creation_time) UNION ALL ... ) ;
何が「地味に大変」なのか？
JOBS_BY_PROJECTビューは時々フィールドが追加されます。これにより追加された日のジョブ実行が失敗してしまうため、対処する必要があります。対処には保存先のテーブルスキーマを更新する必要がありますが、そこにはいくつかの落とし穴がありました。
落とし穴1: INSERT SELECT文はカラム順に依存する
BigQueryのINSERT SELECT文はINSERT先のテーブルのカラム順とSELECT句のカラム順が一致している必要があります。
BigQueryのコンソール画面からカラムを追加する機能はありますが、この方法で追加されたカラムは必ずテーブルの末尾に追加されます。一方、JOBS_BY_PROJECTビューに追加された新しいフィールドは、必ずしも末尾に追加されるとは限りません。
結果として、単純にコンソールからカラムを追加しただけでは、カラムの順序が一致せず、INSERTエラーは解消されません。
INSERTでカラム順を合わせる必要があることを示す検証クエリを下記に示します。
CREATE TABLE `YOUR_PJ_ID.YOUR_DATASET.sample_table` AS SELECT "a" AS col1, "b" AS col2 ; INSERT `YOUR_PJ_ID.YOUR_DATASET.sample_table` SELECT "b" AS col2, "a" AS col1 -- カラム順を合わせずINSERT ; SELECT * FROM `YOUR_PJ_ID.YOUR_DATASET.sample_table` ; -- クエリ結果 -- col1 col2 -- a b -- b a <-- カラム名ではなくカラム順でINSERTされている
落とし穴2: ネストされたフィールドはコンソールから追加できない
STRUCT型のカラムへ子フィールドが追加されるケースもありました。しかし、ネストされたフィールドの追加はBigQueryのコンソール画面からは行えません。
現在の対処法
これらの問題に対応するため、ジョブが失敗した際に以下の手順で手動リカバリーを行っています。
- 最新のJOBS_BY_PROJECTビューのスキーマを取得し、保存先テーブルのスキーマを更新
- 今日の増分データと保存先テーブルのデータをFULL OUTER UNION ALL BY NAME
- 最新のJOBS_BY_PROJECTビューのカラム順に並び替えてREPLCE TABLE
STEP 1: 最新のJOBS_BY_PROJECTビューのスキーマを取得し、保存先テーブルのスキーマを更新
bq show コマンドを使って最新のJOBS_BY_PROJECTビューのスキーマを取得し、 bq update コマンドで保存先テーブルのスキーマを更新します。
具体的には次のコマンドを実行します。
gcloudコマンドのactive projectが何になっているか確認しておきましょう。権限エラーが発生したり、オンデマンド課金のプロジェクトでクエリ料金をとかしてしまったりするのを防ぐためです。
確認方法
今、gcloudコマンドのactive projectが何になっているか確認するコマンドは次のとおりです。gcloud config get-value projectactive projectを指定したプロジェクトへ切り替えるコマンドは次のとおりです。
gcloud config set project YOUR_PROJECT_ID`bq ls` コマンドを実行して、指定したプロジェクトのデータセット一覧が出力されればOKです。
bq ls何も出力されていなかった場合は次のコマンドで認証を通すことで解消できる場合があります。
gcloud auth login YOUR_YAPPLI_EMAIL_ADDRESS
まず、bq query コマンドを使って最新のJOBS_BY_PROJECTビューを一旦テーブルとして保存します。
bq query \ --use_legacy_sql=false \ --destination_table=DESTINATION_PROJECT:DESTINATION_DATASET.tmp_new_information_schema_jobs_schema \ 'SELECT * FROM `ANY_PROJECT`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT LIMIT 1'
一旦テーブルとして保存する理由は、 bq show コマンドから直接JOBS_BY_PROJECTビューのスキーマを取得できないためです。
（補足）bq showコマンドから直接スキーマを取得しようとするとこんなエラーが出ます
コマンド:
bq show \ --schema \ --format=prettyjson \ 'region-REGION.INFORMATION_SCHEMA.JOBS_BY_PROJECT'
実行結果（エラー）:
BigQuery error in show operation: Illegal operation on INFORMATION_SCHEMA view: PROJECT:region-REGION.INFORMATION_SCHEMA.JOBS_BY_PROJECT
続いて、 bq show コマンドで先ほど保存したテーブルのスキーマ（＝最新のJOBS_BY_PROJECTビューのスキーマ）を取得し、JSON形式でファイル出力します。
bq show \ --schema \ --format=prettyjson \ DESTINATION_PROJECT:DESTINATION_DATASET.tmp_new_information_schema_jobs_schema > schema.json
最後に、保存したJSONファイルを使って bq update コマンドで保存先テーブルのスキーマを更新します。
bq update DESTINATION_PROJECT:DESTINATION_DATASET.jobs_by_ds_projects schema.json
留意点として、 bq update コマンドによるスキーマ更新であっても新規追加したカラムは一番後ろに追加されるため、カラム順が一致しない問題は解決していません。これはSTEP 3で解決します。
bq updateコマンドで、追加カラムが一番後ろへ追加されることの検証
前準備｜サンプルテーブルを用意
クエリ:
CREATE OR REPLACE TABLE `YOUR_PJ_ID.YOUR_DATASET.Employees` ( employee_id INT64, name STRUCT< first_name STRING, last_name STRING >, address STRUCT< street STRING, city STRING, zip_code STRING >, phone_numbers ARRAY<STRUCT<type STRING, number STRING>>, department STRING ); INSERT INTO `YOUR_PJ_ID.YOUR_DATASET.Employees` (employee_id, name, address, phone_numbers, department) VALUES (1, STRUCT('太郎', '山田'), STRUCT('1-2-3 Shinjuku', 'Tokyo', '160-0022'), [STRUCT('home', '03-1234-5678'), STRUCT('mobile', '090-1234-5678')], 'Sales'), (2, STRUCT('花子', '佐藤'), STRUCT('4-5-6 Shibuya', 'Tokyo', '150-0002'), [STRUCT('home', '03-9876-5432')], 'Marketing'), (3, STRUCT('一郎', '鈴木'), STRUCT('7-8-9 Ueno', 'Tokyo', '110-0005'), [STRUCT('mobile', '080-1111-2222')], 'Sales');
テーブルスキーマ:
field name mode type description employee_id INTEGER name RECORD address RECORD phone_numbers REPEATED RECORD department STRING
手順｜1列目に hire_date というカラムを追加して、テーブルスキーマを更新
コマンド:
echo '|> Create temp table to get new schema...' \
; \
bq query \
--use_legacy_sql=false \
--destination_table=YOUR_PJ_ID:YOUR_DATASET.tmp_new_Employee_schema \
'SELECT DATE("2023-10-01") AS hire_date, 4 AS employee_id, STRUCT("健太" AS first_name, "田中" AS last_name) AS name, STRUCT("10-11-12 Ginza" AS street, "Tokyo" AS city, "104-0061" AS zip_code) AS address, [STRUCT("mobile" AS type, "070-3333-4444" AS number)] AS phone_numbers, "Marketing" AS department'
; \
echo '|> Get new schema...' \
; \
bq show \
--schema \
--format=prettyjson \
YOUR_PJ_ID:YOUR_DATASET.tmp_new_Employee_schema > schema.json
; \
echo '|> Update table schema...' \
; \
bq update YOUR_PJ_ID:YOUR_DATASET.Employees schema.json
; \
echo '|> Delete schema.json...' \
; \
rm schema.json
; \
echo '|> Delete temp table...' \
; \
bq rm -f \
YOUR_PJ_ID:YOUR_DATASET.tmp_new_Employee_schema
; \
echo '|> Done.' \
;
テーブルスキーマ:
field name mode type description employee_id INTEGER name RECORD address RECORD phone_numbers REPEATED RECORD department STRING hire_date DATE
hire_date カラムが一番後ろで追加されていることが確認できる。
実運用では、あらかじめ保存先テーブルのバックアップを取ったり、一旦保存のテーブルやJSONファイルの後始末をしたりする処理を入れて、次のようなコマンドを実行しています。
コマンド
echo '|> Create backup...' \ ; \ bq cp \ --force=true \ DESTINATION_PROJECT:DESTINATION_DATASET.jobs_by_ds_projects \ DESTINATION_PROJECT:DESTINATION_DATASET.jobs_by_ds_projects_backup \ ; \ echo '|> Create temp JOBS_BY_PROJECT table...' \ ; \ bq query \ --use_legacy_sql=false \ --destination_table=DESTINATION_PROJECT:DESTINATION_DATASET.tmp_new_information_schema_jobs_schema \ 'SELECT * FROM `PROJECT`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT LIMIT 1' \ ; \ echo '|> Get new JOBS_BY_PROJECT schema...' \ ; \ bq show \ --schema \ --format=prettyjson \ DESTINATION_PROJECT:DESTINATION_DATASET.tmp_new_information_schema_jobs_schema > schema.json \ ; \ echo '|> Update jobs_by_ds_projects table schema...' \ ; \ bq update DESTINATION_PROJECT:DESTINATION_DATASET.jobs_by_ds_projects schema.json \ ; \ echo '|> Delete schema.json...' \ ; \ rm schema.json \ ; \ echo '|> Delete temp JOBS_BY_PROJECT table...' \ ; \ bq rm -f \ DESTINATION_PROJECT:DESTINATION_DATASET.tmp_new_information_schema_jobs_schema \ ; \ echo '|> Done.' \ ;
STEP 2: 今日の増分データと保存先テーブルのデータをFULL OUTER UNION ALL BY NAME
今日の増分データと保存先テーブルのデータをFULL OUTER UNION ALL BY NAMEで結合します。
FULL OUTER でUNION ALLすることで、保存先テーブルに存在しない（＝今日新たに追加された）カラムをNULLで補完して存在させます。 BY NAME をつけることでカラム順ではなくカラム名でUNION ALLさせます。
WITH insert_data AS ( SELECT * FROM ( SELECT * FROM `PROJECT_1`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE isin_upsert_date_range(creation_time) UNION ALL SELECT * FROM `PROJECT_2`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE isin_upsert_date_range(creation_time) UNION ALL ... ) ), union_all AS ( SELECT * FROM `DESTINATION_PROJECT.DESTINATION_DATASET.jobs_by_ds_projects` FULL OUTER UNION ALL BY NAME SELECT * FROM insert_data ) SELECT * FROM union_all
STEP 3: 最新のJOBS_BY_PROJECTビューのカラム順に並び替えてREPLCE TABLE
STEP 2で、保存先テーブルに今日分のデータを追加できました。しかし、このままだと明日以降のデータの追加がうまくいかない恐れがあります。理由は前述の通り、bq updateで新規追加したカラムは一番後ろに追加されます。そのため、最新のJOBS_BY_PROJECTビューとカラム順が合わなくなる可能性があります。
そこで、最新のJOBS_BY_PROJECTビューのカラム順を取得し、順番を揃えてREPLACE TABLEします。
具体的には次の手順を行なっています。
- 最新のJOBS_BY_PROJECTビューのスキーマをJSON形式で取得
- 次のクエリを実行して、カラム名の一覧を取得
CREATE TEMP FUNCTION schema_json() RETURNS STRING AS (""" -- INFORMATION_SCHEMA.JOBS_BY_PROJECTのテーブルスキーマをJSONでコピーしてここへ貼り付け """); SELECT JSON_EXTRACT_SCALAR(element, "$.name") AS name FROM UNNEST(JSON_EXTRACT_ARRAY(schema_json(), "$")) AS element
- カラム一覧を次のクエリへコピペしてREPLACE TABLE
REPLACE TABLE `DESTINATION_PROJECT.DESTINATION_DATASET.jobs_by_ds_projects` AS SELECT -- ここにカラム一覧をコピペ FROM union_all -- STEP 2のUNION ALL結果
実運用では、カラム順の並び替えとSTEP 2のUNION ALLは1クエリで行なっています。
クエリ
CREATE TEMP FUNCTION timezone() AS ('Asia/Tokyo'); CREATE TEMP FUNCTION today() AS (CURRENT_DATE(timezone())); CREATE TEMP FUNCTION upsert_date() AS (DATE_SUB(today(), INTERVAL 7 DAY)); CREATE TEMP FUNCTION isin_upsert_date_range(creation_time TIMESTAMP) AS ( upsert_date() < DATE(creation_time, timezone()) AND DATE(creation_time, timezone()) < today() ); CREATE OR REPLACE TABLE `DESTINATION_PROJECT.DESTINATION_DATASET.jobs_by_ds_projects` PARTITION BY DATE(creation_time) CLUSTER BY project_id AS WITH insert_data AS ( SELECT * FROM ( SELECT * FROM `PROJECT_1`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE isin_upsert_date_range(creation_time) UNION ALL SELECT * FROM `PROJECT_2`.`region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE isin_upsert_date_range(creation_time) UNION ALL ... ) ), union_all AS ( SELECT * FROM `DESTINATION_PROJECT.DESTINATION_DATASET.jobs_by_ds_projects` FULL OUTER UNION ALL BY NAME SELECT * FROM insert_data ), final AS ( SELECT -- ここにカラム一覧をコピペ FROM union_all ) SELECT * FROM final
最新のJOBS_BY_PROJECTビューからカラム一覧をコピペするところだけ、1クエリに収めるのが難しかったため別手順に分けています（ゆくゆくは自動化したいと思っています）。
以上で、JOBS_BY_PROJECTビューのフィールド追加への対応が完了しました。
まとめ
この記事ではJOBS_BY_PROJECTビューを全カラムで保存する場合の落とし穴と対処法について記しました。
実際に取り組んでみて、よほどの理由がない限りは全カラム保存を避け、欲しいカラムに絞って保存したほうが良いなと思いました。全カラム保存のメリットは後から発生した分析要件に対応できることですが、JOBS_BY_PROJECTビューのスキーマ変更に追従し続ける手間（＝デメリット）と釣り合うかはかなり微妙なところだと思います。
ここまでお読みいただきありがとうございました！🙇
