BigQueryのジョブのラベル機能が便利でした（小ネタ）

クエリジョブにラベルを付与し、bqコマンドやINFORMATION_SCHEMAから参照する。

#小ネタ

#Google BigQuery

#Google Cloud (GCP)

ikeda

2021.04.07 00:35

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部、池田です。
BigQueryでの作業をした際に、ジョブラベルがとても便利だと感じたのでブログにします。

ラベルについて

BigQueryでは各リソースにラベルをつけることができるそうです。ラベルの対象はデータセット テーブル ビュー ジョブ で、今回はジョブに付与するラベルについて触れます。

【ジョブラベルの追加】
【ラベルの要件】

ジョブラベルは他のリソースに付与するラベルよりも制限があり、あとから変更できなかったり、タグに変換できなかったりします。また、ラベル自体が課金対象となるようです。

ジョブにラベルを追加すると、そのラベルが課金データとしてカウントされます。

使ってみる

ジョブラベルはコンソールからのクエリ実行では付与できないので、 bqコマンドの query から実行してみます。
--label オプションに「KEY:VALUE」の形で設定します。
こんな感じ↓

bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "department:shipping" \
'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality`'

ラベルに利用部門を設定するイメージです。
今回は BigQueryの一般公開データセットから出生データの natality サンプルテーブルを参照しています。

↓ Cloud Shell から実行しました。

ジョブのIDを使って、 bqコマンドの show からジョブの情報を参照しラベルを表示できます。（一番右に Labels 列があります。）

$ bq show -j --format=pretty {ジョブのID}

+----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+
| Job Type |  State  |   Start Time    |    Duration    |          User Email          | Bytes Processed | Bytes Billed | Billing Tier |       Labels        |
+----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+
| query    | SUCCESS | 06 Apr 07:11:38 | 0:00:00.872000 | foo@bar                      | 0               | 0            | 0            | department:shipping |
+----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+

また、 INFORMATION_SCHEMA の JOBS_BY_* ビュー にもラベルは含まれ、抽出条件としても使用できます。

【 INFORMATION_SCHEMA を使用したジョブメタデータの取得】

labels カラムはRECORD型なので、 SAFE_OFFSET() などで↓こんな感じで取得します。

SELECT
    labels[SAFE_OFFSET(0)].value AS dept,
    creation_time, job_id, start_time, end_time,
    REGEXP_REPLACE(LEFT(query, 100), '\r?\n', ' ') AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
    creation_time >= '2021-04-06 07:00:00' -- UTC, partition
AND labels[SAFE_OFFSET(0)].key = 'department'
ORDER BY creation_time DESC;

（部門情報を持つジョブを抽出するイメージ）
↓

+----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+
|   dept   |    creation_time    |                   job_id                   |     start_time      |      end_time       |                                query                                |
+----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+
| shipping | 2021-04-06 07:11:37 | foo                                        | 2021-04-06 07:11:38 | 2021-04-06 07:11:39 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` |
+----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+

特に私が便利だと感じたのは、クエリのチューニングやパフォーマンスの確認で、たくさんある結果を整理する時でした。

↓こんな感じでテスト内容が分かるようにラベルをつけておいて…

bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "title:test1" \
--label "case:male" \
'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` 
WHERE is_male IS true'

bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "title:test1" \
--label "case:not_male" \
'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` 
WHERE is_male IS false'

↓こんな感じのクエリで所要時間やスロットの使われ方を確認する時にラベルがあるとだいぶ探しやすく・見やすいです。

SELECT
    labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`,
    ROUND(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000, 1) AS time_sec,
    ROUND(total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots,
    total_slot_ms, total_bytes_billed, cache_hit,
    (error_result IS NOT NULL) AS has_error,
    creation_time, job_id, priority, start_time, end_time,
    state, total_bytes_processed,
    REGEXP_REPLACE(LEFT(query, 100), '\r?\n', ' ') AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
    creation_time >= '2021-04-06 07:00:00' -- UTC, partition
AND labels[SAFE_OFFSET(0)].value LIKE 'test%'
ORDER BY title, `case`;

↓

+-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+
| title |   case   | time_sec | avg_slots | total_slot_ms | total_bytes_billed | cache_hit | has_error |    creation_time    |                   job_id                   |  priority   |     start_time      |      end_time       | state | total_bytes_processed |                                            query                                            |
+-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+
| test1 | male     |      0.9 |      11.0 |          9869 |          138412032 |     false |     false | 2021-04-06 07:17:03 | foo                                        | INTERACTIVE | 2021-04-06 07:17:03 | 2021-04-06 07:17:04 | DONE  |             137826763 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality`  WHERE is_male IS true  |
| test1 | not_male |      0.8 |      17.0 |         13818 |          138412032 |     false |     false | 2021-04-06 07:17:27 | bar                                        | INTERACTIVE | 2021-04-06 07:17:27 | 2021-04-06 07:17:28 | DONE  |             137826763 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality`  WHERE is_male IS false |
+-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+

state カラムは PENDING, RUNNING, DONE の状態だけ（エラーは無い）のようで、クエリが成功しているのかどうかは error_result カラムで確認するようにしています。（試した限りはそれでたぶん判別できそう…）