Google Cloud Platform IT

【GCP】BigQueryのシャーディングとパーティション

2021年7月6日


ここではBigQueryの重要なポイントである、シャーディングと、パーティショニングについて説明します。
シャーディングとパーティショニングを行うのにテーブル設計は大事です。
ただBigQueryは従量課金と言って、通常のRDB代表格のMySQLのようにSQLを実行しても毎回無料ですが、BigQueryではSQLを実行するたびにスキャン量に応じて課金されます。
毎回データをフルスキャンして実行するとスキャン量も膨大になり、お金がかかってしまいます。

 
そこで、そのスキャン量を減らすための工夫として、シャーディングとパーティショニングがあります。
この機能についてここでは説明していこうと思います。

シャーディング

シャーディングとは、テーブルで同じ名前があったら、それを1つのテーブルにして表示する仕組みである。
BigQueryでは以下のようなテーブルがあれば、1つのテーブルにまとめてくれる習性があります。
同じデータセット内でtablename_YYYYMMDDというテーブル名の形式で作成すると、1つのテーブルでシャーディングを行います。
 
例えば以下のように、
table_name_20220101
table_name_20220102
と作成すると、table_name_(2)という表示でBigQuery上にテーブルが作成されます。(まとめられるという表現の方が正しいかも)
 
形式はなんでもいいというわけではなく、
table_name_1
table_name_2

table_name_a
table_name_b
とした時、それぞれtable_name_(2)という表示で作成されず、それぞれ別テーブルとして作成されます。

 
以下のような関東と関西の人口データのcsvがそれぞれあったとします。

それぞれ、
関東の人口データはpref_data_20220101というテーブルで、
関西の人口データはpref_data_20220102というテーブルで
作成してみます。
そうすると、以下の赤枠のように作成されます。pref_data_(2)で2つのテーブルが1つにまとめられている感じになります。

 
ちなみに、
同様にして同じ上記のデータで関東はpref_data_kanto、関西はpref_data_kansaiという名前でテーブル作成しても、まとめられず、
また関東はpref_data_1、関西はpref_data_2で作成しても、上記の画像のようにまとめられませんでした。

 

シャーディングの_TABLE_SUFFIX

シャーディングしたテーブルへは、_TABLE_SUFFIXというカラムを用いてアクセスすることができます。
BigQueryでは従量課金で、スキャン量が多くなるほどお金がかかります。
そしてBigQueryに限らずSQLは基本的にテーブルのデータ全量をスキャンします。

_TABLE_SUFFIXは正規表現になるため、*をつけることで全量のテーブルを参照します。

select
  *
  from `< project名 >.bigquery_test.pref_data_20220102`
;

select
  *
  from `< project名 >.bigquery_test.pref_data_*`
  where _TABLE_SUFFIX = "20220102"

上記2つともまとめられた(シャーディング)テーブルの中から必要なテーブルのみ参照するSQLです。
当然上のSQLはそのまま指定しているので、取得できます。
下のSQLでは、シャーディングされているテーブル名のYYYYYMMDD部分が*になっていて、その*の部分を_TABLE_SUFFIXで参照しています。
このようにして、シャーディングされているテーブルへアクセスできます。
もしシャーディングテーブル全てのデータを参照したい場合は、_TABLE_SUFFIXで条件せず、*のみでOKです。

 

別にそれなら、上のSQLでいいじゃんと思いそうですが、
複数の期間のデータを取りたい場合は、
_TABLE_SUFFIXはカラムなので、between andを用いて期間を指定することができます。
ここは_TABLE_SUFFIXでないといけない部分かと思います。そうしないとunion allで複数の日付で指定して取ることになってしまうため。
where _TABLE_SUFFIX between '20210701' and '20210706'
というように指定できます。

 

*は正規表現でのアスタリスクを意味していて、ただそこに値が入るわけではないので注意。
上記でpref_data_20211231を作成して、2022年1月だけのテーブル全てを参照したい場合は、以下のようにすることで取得ができる。

select
  *
  from `< project名 >.bigquery_test.pref_data_202201*`

 

そして以下のようにして、20220101テーブルを参照することもできる。

select
  *
  from `< project名 >.bigquery_test.pref_data_202201*`
  where _TABLE_SUFFIX = "01"

 

パーティション

先程のシャーディングはテーブル名に対して設定するもので、
このパーティションはテーブルの中のカラムに対して指定します。

パーティションを設定したいカラムを指定します。
パーティションを設定したいカラムはテーブルを作成時に指定します。
そうすることで、

そして日付系のカラムの場合は、以下のような特別なカラムを使用して参照することができます。

カラム 説明
_PARTITIONDATE 日付粒度でパーティションを行う
_PARTITIONTIME 時間粒度でパーティションを行う

created_atというカラムはTIMESTAMP型で、
その中には日付のDateと、時間のDATETIMEが情報として入っています。

ちなみにcreated_atは、unixtimeなので日本時間では15時が日付の変わる時間となります。

_PARTITIONDATE,_PARTITIONTIME

パーティション、シャード化

シャード化とは、特定のカラムの値でパーティションを切るというもの。
シャード化をテーブルに適用するには、
作成する段階でシャード化するカラム(スキーム)を指定する必要がある。

そうすることで、_PARTITIONで値を切ることができる。

シャーディング

以下のように関東と関西の都道府県

-Google Cloud Platform, IT
-,

© 2022 Yosshi Blog Powered by AFFINGER5