Google BigQuery: データセットの一覧情報を取得

#Google BigQuery

しんや

2020.03.12

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

小ネタです。

Google BigQueryのデータセットでは、管理下にあるテーブル群に関連する情報等がそれぞれ存在します。それらのデータを一覧参照出来る情報が欲しいなと思い、そのためにはどういう方法が使えるのかを確認し、まとめてみました。

やりたいこと、欲しいもの

「データセット」に関する一覧情報を表示させたい。そのために必要なデータセットにおける列情報を必要な分、何らかの形で取得したい。概ね以下の画面で得られるものが該当。

Pythonプログラムを介してデータセット情報を取得

公式ドキュメントには以下のテーマで展開されている情報があります。

その中で、プログラム言語による情報の取得方法が紹介されていたので試してみました。任意のデータセットIDを設定すれば取得出来る流れになっています。ただ、既存参照コードのままだと得られる情報が少なかったので、途中確認用コードを数行追加してみました。

view_bq_dataset.py

# TODO(developer): Import the client library.
from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
dataset_id = 'cm-xxxxxxxxxxxxxx.cmbqdataset'

dataset = client.get_dataset(dataset_id)  # Make an API request.
print("==================")
print(dir(dataset))
print("-----------")
print(type(dataset))
print(vars(dataset))
print("===================")


full_dataset_id = "{}.{}".format(dataset.project, dataset.dataset_id)
friendly_name = dataset.friendly_name
print(
    "Got dataset '{}' with friendly_name '{}'.".format(
        full_dataset_id, friendly_name
    )
)

# View dataset properties.
print("Description: {}".format(dataset.description))
print("Labels:")
labels = dataset.labels
if labels:
    for label, value in labels.items():
        print("\t{}: {}".format(label, value))
else:
    print("\tDataset has no labels defined.")

# View tables in dataset.
print("Tables:")
tables = list(client.list_tables(dataset))  # Make an API request(s).
if tables:
    for table in tables:
        print("\t{}".format(table.table_id))
else:
   print("\tThis dataset does not contain any tables.")

プログラム実行結果。一部見易さを考慮して改行を加えています。データセット単位で必要な情報は揃ってそうな感じです。

$ python view_bq_dataset.py 
==================
['_PROPERTY_TO_API_FIELD', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_build_resource', '_properties', 'access_entries', 'created', 'dataset_id', 'default_encryption_configuration', 'default_partition_expiration_ms', 'default_table_expiration_ms', 'description', 'etag', 'friendly_name', 'from_api_repr', 'from_string', 'full_dataset_id', 'labels', 'location', 'model', 'modified', 'path', 'project', 'reference', 'routine', 'self_link', 'table', 'to_api_repr']
-----------
<class 'google.cloud.bigquery.dataset.Dataset'>
{'_properties': {
    'kind': 'bigquery#dataset', 
    'etag': 'LFRTXxxXXxxXXXXXXXX==', 
    'id': 'cm-xxxxxxxxxxxxxx:cmbqdataset', 
    'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/cm-xxxxxxxxxxxxxx/datasets/cmbqdataset', 
    'datasetReference': {
        'datasetId': 'cmbqdataset', 
        'projectId': 'cm-xxxxxxxxxxxxxx'
    },
    'description': 'テスト：ブログ用に作成したBigQueryデータセットです。', 
    'defaultTableExpirationMs': '15552000000', 
    'labels': {
        'env': 'develop', 
        'production': 'false'
    }, 
    'access': [
        {'role': 'WRITER', 'specialGroup': 'projectWriters'}, 
        {'role': 'OWNER', 'specialGroup': 'projectOwners'}, 
        {'role': 'OWNER', 'userByEmail': 'xxxxxxxxxxxxx@yyyyyyyyyyyyyyyy'}, 
        {'role': 'READER', 'specialGroup': 'projectReaders'}
    ], 
    'creationTime': '1582620145008', 
    'lastModifiedTime': '1583965989780', 
    'location': 'asia-northeast1'
}}
===================
Got dataset 'cm-xxxxxxxxxxxxxx.cmbqdataset' with friendly_name 'None'.
Description: テスト：ブログ用に作成したBigQueryデータセットです。
Labels:
	env: develop
	production: false
Tables:
	sample_table_1
	t_sales

INFORMATION_SCHEMAの内容を活用する

冒頭参照先として挙げたドキュメントには、現状ベータ版扱いではありますがBigQueryでデータセット、ルーティン、テーブル、ビューに関するメタデータへのアクセスを提供する一連のビューとして用意されているINFORMATION_SCHEMAを活用する方法が載っています。内容を見る限り、これらの情報をを使うのが一番手っ取り早く且つ効率的です。

$ bq query --nouse_legacy_sql '
 SELECT
   * EXCEPT(schema_owner)
 FROM
   INFORMATION_SCHEMA.SCHEMATA
 ORDER BY
   catalog_name, schema_name
'
Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ... (0s) Current status: DONE   
+------------------+----------------------------+---------------------+---------------------+-----------------+
|   catalog_name   |        schema_name         |    creation_time    | last_modified_time  |    location     |
+------------------+----------------------------+---------------------+---------------------+-----------------+
| cm-xxxxxxxxxxxxx | bq_cm_dataset_by_python2nd | 2020-02-25 14:43:57 | 2020-02-25 14:43:57 | asia-east1      |
| cm-xxxxxxxxxxxxx | bq_dataset_by_cli          | 2020-03-11 07:11:52 | 2020-03-11 07:11:52 | asia-northeast2 |
| cm-xxxxxxxxxxxxx | bq_dataset_bycli2          | 2020-03-11 06:41:08 | 2020-03-11 06:41:08 | asia-northeast2 |
| cm-xxxxxxxxxxxxx | cmbqdataset                | 2020-02-25 08:42:25 | 2020-03-11 22:33:09 | asia-northeast1 |
+------------------+----------------------------+---------------------+---------------------+-----------------+

INFORMATION_SCHEMA.SCHEMATA_OPTIONSビューの内容には現在のユーザーがアクセスできるプロジェクトの各データセットの情報が入っています。こちらもクエリ一発でデータが取ってこれるのでお手軽感がありますね。

$ bq query --nouse_legacy_sql '
SELECT
  project_name,
  dataset_name,
  option_name,
  option_type,
  option_value
FROM
  (SELECT
     catalog_name AS project_name,
     schema_name AS dataset_name,
     option_name,
     option_type,
     option_value
   FROM
     INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  )
  ORDER BY
    project_name,
    dataset_name
'

Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1 ... (1s) Current status: DONE   
+------------------+----------------------------+-------------------------------+-------------------------------+-----------------------------------------------------------+
|   project_name   |        dataset_name        |          option_name          |          option_type          |                       option_value                        |
+------------------+----------------------------+-------------------------------+-------------------------------+-----------------------------------------------------------+
| cm-xxxxxxxxxxxxx | bq_cm_dataset_by_python2nd | default_table_expiration_days | FLOAT64                       | 0.041666666666666664                                      |
| cm-xxxxxxxxxxxxx | bq_cm_dataset_by_python2nd | description                   | STRING                        | "Python経由で作られたデータセットです."                   |
| cm-xxxxxxxxxxxxx | bq_dataset_by_cli          | default_table_expiration_days | FLOAT64                       | 92.5925925925926                                          |
| cm-xxxxxxxxxxxxx | bq_dataset_by_cli          | description                   | STRING                        | "CLI経由で作成したデータセットです"                       |
| cm-xxxxxxxxxxxxx | bq_dataset_bycli2          | default_table_expiration_days | FLOAT64                       | 92.5925925925926                                          |
| cm-xxxxxxxxxxxxx | bq_dataset_bycli2          | description                   | STRING                        | "CLI経由で作成したデータセットです"                       |
| cm-xxxxxxxxxxxxx | cmbqdataset                | default_table_expiration_days | FLOAT64                       | 180.0                                                     |
| cm-xxxxxxxxxxxxx | cmbqdataset                | description                   | STRING                        | "テスト：ブログ用に作成したBigQueryデータセットです。"    |
| cm-xxxxxxxxxxxxx | cmbqdataset                | labels                        | ARRAY<STRUCT<STRING, STRING>> | [STRUCT("env", "develop"), STRUCT("production", "false")] |
+------------------+----------------------------+-------------------------------+-------------------------------+-----------------------------------------------------------+

同じ内容をPythonプログラムからアクセスする場合は、以下のようなコードで実現が可能です。

exec-bigquery-sql.py

# TODO(developer): Import the client library.
from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
client = bigquery.Client()

query = """
SELECT
  project_name,
  dataset_name,
  option_name,
  option_type,
  option_value
FROM
  (SELECT
     catalog_name AS project_name,
     schema_name AS dataset_name,
     option_name,
     option_type,
     option_value
   FROM
     INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  )
  ORDER BY
    project_name,
    dataset_name
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    #print("name={}, count={}".format(row[0], row["total_people"]))
    print(row)

出力結果：

$ python exec-bigquery-sql.py 
The query data:
Row(('cm-xxxxxxxxxxxxx', 'bq_cm_dataset_by_python2nd', 'default_table_expiration_days', 'FLOAT64', '0.041666666666666664'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_cm_dataset_by_python2nd', 'description', 'STRING', '"Python経由で作られたデータセットです."'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_by_cli', 'default_table_expiration_days', 'FLOAT64', '92.5925925925926'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_by_cli', 'description', 'STRING', '"CLI経由で作成したデータセットです"'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_bycli2', 'default_table_expiration_days', 'FLOAT64', '92.5925925925926'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'bq_dataset_bycli2', 'description', 'STRING', '"CLI経由で作成したデータセットです"'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'cmbqdataset', 'default_table_expiration_days', 'FLOAT64', '180.0'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'cmbqdataset', 'description', 'STRING', '"テスト：ブログ用に作成したBigQueryデータセットです。"'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})
Row(('cm-xxxxxxxxxxxxx', 'cmbqdataset', 'labels', 'ARRAY<STRUCT<STRING, STRING>>', '[STRUCT("env", "develop"), STRUCT("production", "false")]'), {'project_name': 0, 'dataset_name': 1, 'option_name': 2, 'option_type': 3, 'option_value': 4})

まとめ

という訳で、Google BigQueryにおける「データセット」に関する情報を取得する際の実践例についての実践まとめでした。いずれの場合も非常にシンプル且つ簡単に情報が取得出来ていたので安心しました。

BigQuery ｜特集カテゴリー｜ Developers.IO

Google BigQuery: データセットの一覧情報を取得

やりたいこと、欲しいもの

Pythonプログラムを介してデータセット情報を取得

INFORMATION_SCHEMAの内容を活用する

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

Google BigQuery: データセットの一覧情報を取得

やりたいこと、欲しいもの

Pythonプログラムを介してデータセット情報を取得

INFORMATION_SCHEMAの内容を活用する

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

「【4/17(水)ハイブリッド】クラスメソッドGoogle Cloud Next ’24ふり返り勉強会」に登壇しました

Google Cloud Next’24で発表されたBigQueryのアップデート情報をまとめました

【新機能】BigQuery data canvasを早速触ってみた #GoogleCloudNext

【速報】Google Cloud NextでGemini in BigQueryが追加されました

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩