BigQueryの分割テーブルの注意点まとめ

2020年2月17日BigQuery

BigQueryの分割テーブルはデータを高速に検索することやコスト削減につながるために、優れたテーブルなのですが、分割テーブルの制限事項を理解した上で利用する必要があります。

BigQueryの分割テーブルを利用する場合の注意点についてまとめました。

分割テーブルとは

まず、BigQueryのテーブルの種類として標準テーブルと分割テーブルの2種類があります。

標準テーブルと分割テーブルの大きな違いは、データの格納方法です。

分割テーブルは特定の項目の値によってデータを配置する場所が分散されます。

この特定の項目をパーティションキーといいます。

パーティションキーの値によってデータが分散して配置されているため、クエリを発行する際にパーティションキーに抽出条件に含めると、対象のデータ量を絞り込むことができ高速にクエリを実行することができます。

パーティションキーに指定できる項目のデータ型は限定されている

分割テーブルのパーティションキーとして指定できる項目は、どんなデータ型でも利用できるわけではなく、以下の4種類となっています。

例えば、文字列型はパーティションキーとして指定できません。

取り込み時間データの登録日に基づいてテーブルが分割される
日付指定したDATE 列に基づいてテーブルが分割される
タイムスタンプ指定したTIMESTAMP 列に基づいてテーブルが分割される
整数(※)整数列に基づいて分割されます。
注意

整数タイプは2020/2/17時点でまだベータ版のため利用には注意が必要です。

パーティションキーに設定する項目の注意点

分割テーブルを利用する場合に、その列のデータの種類が4000を超えてはいけないという制限があります。

以下は、GCPのBigQueryのヘルプの記載内容です。

パーティション分割テーブルあたりの最大パーティション数 – 4,000

例:NGパターン

会社Aの顧客情報を管理するために以下のテーブルを作成しました。

テーブル顧客マスタ(分割テーブル)
パーティションキー顧客番号(整数)

この会社の顧客が5000人存在する場合、顧客番号をパーティションキーとして設定することはできません。

この場合は、パーティションキーとして別の項目を設定するか、分割テーブルではなく標準のテーブルにするかで対応します。

 

例:OKパターン

会社Aの受注情報を管理するために以下のテーブルを作成しました。

テーブル受注テーブル(分割テーブル)
パーティションキー受注日(日付列)

この場合、4000日分の受注情報まで蓄積することは可能ですので、約10年分は保存可能となる

365日 × 10年 = 3650 < 4000

 

ポイント

パーティションキーとする項目は次の2つを満たすものがよい
・データの種類が4000以下の項目
・クエリの際に必ず抽出条件に含まれる項目

データ登録処理の注意点

分割テーブルに対してデータを登録する場合、BigQueryの以下の制限値に注意が必要です。

取り込み時間パーティション分割テーブルあたりの最大パーティション変更数 – 5,000 件
列パーティション分割テーブルあたりの最大パーティション変更数 – 30,000 件

GCPのヘルプには以下の記載があります。

GCPのヘルプ抜粋

取り込み時間パーティション分割テーブルあたり合計 5,000 パーティション分割、列パーティション分割テーブルあたり 30,000 パーティションに制限されています。
パーティションは、パーティション内でデータを追加または上書きするオペレーションを使用して変更できます。
パーティションを変更するオペレーションの対象となるのは、読み込みジョブ、結果をパーティションに書き込むクエリ、パーティション内のデータを変更する DML ステートメント(INSERT、DELETE、UPDATE、MERGE)などです。

うーん。わかりづらいですよね。具体的な例で説明します。

 

例:NGパターン

具体的な例で説明すると、以下のような処理は制限値を超えてしまいエラーとなります。

会社Aの受注情報を管理するために以下のテーブルを作成しました。

テーブル受注テーブル(分割テーブル)
パーティションキー受注日(日付列)
データの登録方法受注情報CSVファイルを1ファイルずつBigQueryへロードする
受注情報CSVのファイル1ファイルあたり受注日が30日分存在する
1日あたり2,000ファイル登録する
各ファイルに登録されている日付は全て同じ(同じ月のデータ)

パーティション変更数 = 30日 × 2000回 = 60,000

制限値の30,000を超えているためデータをロードできないという結果になってしまいます。

対策はGCSからの一括ロード

上記の例で改善点があるとすれば、データの登録方法です。

「受注情報CSVファイルを1ファイルずつBigQueryへロードする」の部分を1回で登録することで、

パーティション変更回数を少なくすることができます。

上記の例の場合では、パーティション変更回数は30になります。

パーティション変更数 = 30日 × 1回 = 30

GCSからの一括ロード処理は以下の記事を参考にして下さい。

BigQuery

Posted by snow