BigQuery テーブルチューニングに効果的なクラスタリングが、既存テーブルにも追加できるようになりました！

みかみ

2021.04.23

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

こんにちは、みかみです。

BigQuery で、テーブル作成後にクラスタリングカラムを追加できるようになったそうです！

リリースノートによると、2020/10 からサポート開始されてたんですね。（しらなんだ。。

やりたいこと

BigQuery の既存テーブルにクラスタリングを追加したい
Python クライアントライブラリ経由でもクラスタリングを追加できるのか確認したい

クラスタリングって何？

BigQuery でクラスタ化テーブルを作成すると、テーブルのスキーマ内の 1 つ以上の列の内容に基づいてテーブルのデータが自動的に編成されます。指定した列は、関連するデータを同じ場所に配置するために使用されます。複数の列を使用してテーブルをクラスタ化する場合は、指定する列の順序が重要です。指定した列の順序によって、データの並べ替え順序が決まります。

抜粋：クラスタ化テーブルの概要｜ BigQuery ドキュメント

ということで、AWS Redshift でいうところの Sort Key のようなものですね。

チューニング不要で大量データを高速にクエリ可能な BigQuery ですが、やはり処理データ量が多いとそれだけ時間はかかります。また、SQL 実行時の処理ステップが多いほど時間がかかるので、必要に応じてあらかじめデータの並び替えをしておけば、効率よく SQL が実行できます。

注意事項

非クラスタ化テーブルがクラスタ化テーブルに変換、またはクラスタ化列セットが変更された場合、自動再クラスタリングはその時間以降にのみ使用できます。

抜粋：クラスタリング仕様の変更｜ BigQuery ドキュメント

既存テーブルにクラスタリングが追加できるようにはなったものの、現在のところ、既に格納済みのデータが自動で並び替えされることはないとのことです。

前提

bq コマンドおよび BigQuery Python クライアントライブラリの実行環境は準備済みです。

動作確認時には Cloud Shell を使用しました。

また、以下のテーブルを準備しました。

このテーブルに、クラスタリングを追加してみたいと思います。

bq コマンドでクラスタリングを追加・更新・削除

コマンドラインリファレンスの英語ドキュメントで、--clustering_fields パラメータを確認できました。

bq update｜ Command-line tool reference

※2021/04/23 現在、日本語のリファレスには、まだ --clustering_fields パラメータの記載はありませんでした。

また、bq update --help コマンド実行でも、クラスタリング変更用のパラメータが確認できます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --help | grep clustering
                               --clustering_fields: Comma separated field names.
                                 remove clustering on a table.

help 全文を表示してみると、こんな感じ。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --help
（省略）
--clustering_fields: Comma separated field names. Can only be specified for time based partitioned tables. Data will be first partitioned and
  subsequently "clustered on these fields. Set this to an empty string to remove clustering on a table.
（省略）

テーブルカラムをカンマ区切りで指定すれば良いようです。また、空文字列を指定すると、クラスタリング設定を削除することもできるようです。

コマンド実行して実際にクラスタリングが追加できるか試してみます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields region,Date dataset_1.avocado_no_clustering
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_no_clustering' successfully updated.

BigQuery 管理画面からも、クラスタリングが追加されたことが確認できました。

では、クラスタリングカラムを別のカラムに更新してみます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields year dataset_1.avocado_no_clustering
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_no_clustering' successfully updated.

パラメータで指定した通り、クラスタリングカラムが更新されました。

最後に、クラスタリングを削除してみます。

ikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields '' dataset_1.avocado_no_clustering
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_no_clustering' successfully updated.

クラスタリングが削除されたことが確認できました。

パーティションテーブルにクラスタリングを追加・更新・削除

先ほどは非パーティショニングテーブルにクラスタリングを追加してみましたが、パーティション設定済みのテーブルに対してもクラスタリングの update ができるのか確認してみます。

以下の、データロード日時を日単位でパーティショニング指定したテーブルを準備しました。

クラスタリングカラムを追加してみます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields region,Date dataset_1.avocado_partitioning
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_partitioning' successfully updated.

非パーティショニングテーブル同様、クラスタリングが追加できました。

念のため、更新と削除も問題なく実行できるか確認してみます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields year dataset_1.avocado_partitioning
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_partitioning' successfully updated.

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields '' dataset_1.avocado_partitioning
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_partitioning' successfully updated.

パーティショニングテーブルにも、クラスタリングを追加・更新・削除できることが確認できました。

Python クライアントライブラリでクラスタリングを追加

BigQuery Python クライアントライブラリのバージョンを確認します。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ pip3 list | grep bigquery
google-cloud-bigquery          2.13.1
google-cloud-bigquery-storage  2.3.0

リファレンスドキュメントによると最新バージョンは 2.13.1 で、インストール済みのライブラリバージョンと同じです。

Python Client for Google BigQuery

念のため、pip install -U で、最新バージョンにアップデートします。

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ pip3 install -U google-cloud-bigquery
Requirement already up-to-date: google-cloud-bigquery in /usr/local/lib/python3.7/dist-packages (2.13.1)
Requirement already satisfied, skipping upgrade: protobuf>=3.12.0 in /usr/local/lib/python3.7/dist-packages (from google-cloud-bigquery) (3.15.8)
（省略）
Requirement already satisfied, skipping upgrade: pycparser in /usr/local/lib/python3.7/dist-packages (from cffi>=1.0.0->google-crc32c<2.0dev,>=1.0; python_version >= "3.5"->google-resumable-media<2.0dev,>=0.6.0->google-cloud-bigquery) (2.20)

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ pip3 list | grep bigquery
google-cloud-bigquery          2.13.1
google-cloud-bigquery-storage  2.3.0

やはり、Cloud Shell にプリインストール済みの 2.13.1 が最新バージョンのようです。

update_table でクラスタリングカラムを追加する、以下の Python コードを実行しました。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.avocado_no_clustering'
client = bigquery.Client()
table = client.get_table(table_id)

print('clustering: {}'.format(table.clustering_fields))
table.clustering_fields = ['region','Date']
table = client.update_table(table, ['clustering_fields'])

table = client.get_table(table_id)
print('clustering: {}'.format(table.clustering_fields))

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 update_clustering.py
clustering: None
Traceback (most recent call last):
  File "update_clustering.py", line 9, in <module>
    table = client.update_table(table, ['clustering_fields'])
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/client.py", line 1098, in update_table
    partial = table._build_resource(fields)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/table.py", line 928, in _build_resource
    return _helpers._build_resource_from_properties(self, filter_fields)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/_helpers.py", line 717, in _build_resource_from_properties
    raise ValueError("No property %s" % filter_field)
ValueError: No property clustering_fields

エラーです。。（やはりクライアントライブラリはまだ対応してないのかな。。

念のため、クライアントライブラリのエラー発生箇所のコードを確認してみます。

（抜粋）
def _build_resource_from_properties(obj, filter_fields):
    """Build a resource based on a ``_properties`` dictionary, filtered by
    ``filter_fields``, which follow the name of the Python object.
    """
    partial = {}
    for filter_field in filter_fields:
        api_field = obj._PROPERTY_TO_API_FIELD.get(filter_field)
        if api_field is None and filter_field not in obj._properties:
            raise ValueError("No property %s" % filter_field)
        elif api_field is not None:
            partial[api_field] = obj._properties.get(api_field)
        else:
            # allows properties that are not defined in the library
            # and properties that have the same name as API resource key
            partial[filter_field] = obj._properties[filter_field]

    return partial
（抜粋）

抜粋：python-bigquery/google/cloud/bigquery/_helpers.py ｜ GitHub

_PROPERTY_TO_API_FIELD にも、テーブルプロパティにも定義されていないフィールドの更新は受け付けてくれないようです。ということは、クラスタリング設定済みのテーブルであれば、更新はできそうです。テーブルにクラスタリングを追加して、再度実行してみます。

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq update --clustering_fields year dataset_1.avocado_no_clustering
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_no_clustering' successfully updated.
mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 update_clustering.py
clustering: ['year']
Traceback (most recent call last):
  File "update_clustering.py", line 10, in <module>
    table = client.update_table(table, ['clustering_fields'])
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/client.py", line 1098, in update_table
    partial = table._build_resource(fields)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/table.py", line 928, in _build_resource
    return _helpers._build_resource_from_properties(self, filter_fields)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/_helpers.py", line 717, in _build_resource_from_properties
    raise ValueError("No property %s" % filter_field)
ValueError: No property clustering_fields

やはりエラーです。。クライアントライブラリのコードをもう少しよくみてみます。

（抜粋）
@clustering_fields.setter
def clustering_fields(self, value):
    """Union[List[str], None]: Fields defining clustering for the table
    (Defaults to :data:`None`).
    """
    if value is not None:
        prop = self._properties.setdefault("clustering", {})
        prop["fields"] = value
    else:
        if "clustering" in self._properties:
            del self._properties["clustering"]
（抜粋）

抜粋：python-bigquery/google/cloud/bigquery/table.py ｜ GitHub

clustering_fields の setter です。テーブルオブジェクトのクラスタリングプロパティは、実際は clustering という名前で定義されているようです。

実行する Python コードを、以下に修正して再実行します。

from google.cloud import bigquery

table_id = 'cm-da-mikami-yuki-258308.dataset_1.avocado_no_clustering'
client = bigquery.Client()
table = client.get_table(table_id)

print('clustering: {}'.format(table.clustering_fields))
table.clustering_fields = ['region','Date']
#table = client.update_table(table, ['clustering_fields'])
table = client.update_table(table, ['clustering'])

table = client.get_table(table_id)
print('clustering: {}'.format(table.clustering_fields))

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 update_clustering.py
clustering: ['year']
clustering: ['region', 'Date']

期待通り、クラスタリングカラムが更新できました。

では、Python クライアントコードの API パラメータフィールドにクラスタリング項目を追加してあげれば、クラスタリング未設定の既存テーブルにも、クラスタリング追加できるのでは？！（ほんとはそんなことやっちゃダメだけど。。

（抜粋）
_PROPERTY_TO_API_FIELD = {
    "encryption_configuration": "encryptionConfiguration",
    "expires": "expirationTime",
    "external_data_configuration": "externalDataConfiguration",
    "friendly_name": "friendlyName",
    "mview_enable_refresh": "materializedView",
    "mview_query": "materializedView",
    "mview_refresh_interval": "materializedView",
    "partition_expiration": "timePartitioning",
    "partitioning_type": "timePartitioning",
    "time_partitioning": "timePartitioning",
    "view_use_legacy_sql": "view",
    "view_query": "view",
    "require_partition_filter": "requirePartitionFilter",
    # add mikami
    "clustering_fields": "clustering",
}
（抜粋）

抜粋：python-bigquery/google/cloud/bigquery/table.py ｜ GitHub

テーブルのクラスタリングを削除した後、再度 python コードを実行してみます。

ikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq update --clustering_fields '' dataset_1.avocado_no_clustering
Table 'cm-da-mikami-yuki-258308:dataset_1.avocado_no_clustering' successfully updated.
mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ python3 update_clustering.py
clustering: None
clustering: ['region', 'Date']

（いけたっぽい。） BigQuery 管理画面からも確認してみます。

クラスタリングが追加できました！

現時点では、Python クライアントライブラリではまだクラスタリングの追加がサポートされていませんが、この分ならすぐに対応されるかな？

まとめ（所感）

インデックスなしで高速クエリを実現するパワフルな BigQuery ですが、テーブルデータが大量になると、処理時間もそれなりに増えてしまいます。

基本的にはチューニング不要な BigQuery ではありますが、テーブルや SQL をチューニングすることで、より良いパフォーマンスが期待できます。

BigQuery のテーブルチューニングにおいて、パーティショニングとクラスタリングは重要なポイントなので、クラスタリングが後からでも追加できるようになった今回のリリースは喜ばしいですね。

今後、格納済みデータの自動並び替えや、既存テーブルへのパーティショニングの追加もできるようになるとさらに嬉しいところです！

BigQuery テーブルチューニングに効果的なクラスタリングが、既存テーブルにも追加できるようになりました！

やりたいこと

クラスタリングって何？

注意事項

前提

bq コマンドでクラスタリングを追加・更新・削除

パーティションテーブルにクラスタリングを追加・更新・削除

Python クライアントライブラリでクラスタリングを追加

まとめ（所感）

参考

イベント

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（木）】組織的なクラウド統制はじめの一歩

BigQuery テーブルチューニングに効果的なクラスタリングが、既存テーブルにも追加できるようになりました！

やりたいこと

クラスタリングって何？

注意事項

前提

bq コマンドでクラスタリングを追加・更新・削除

パーティションテーブルにクラスタリングを追加・更新・削除

Python クライアントライブラリでクラスタリングを追加

まとめ（所感）

参考

イベント

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（木）】組織的なクラウド統制はじめの一歩