BigQueryで分割テーブルを作る際にハマったこと

BigQueryで分割テーブルを作った際にエラーになった現象について

3行で

• Bigqueryで2000日を超える分割テーブルを作成しようとするとエラーになる
• だから時系列データが約６年を超えそうならテーブル自体を分けて管理する必要がある
• 分割テーブルの設定有無で課金額が変わってくるので重要

そもそも分割テーブルはなぜ必要か

BigqeuryはRedshiftとは違ってクエリ単位での課金なので、「自分が使った分だけ」請求が来る。
ただ、この「使った分」の定義は列単位での課金なので、次のような問題が起こる。

1. Limitで行を制限してもフルスキャンするのでテーブル全体に対して課金される
2. Whereで行を制限してもフルスキャンするのでテーブル全体に対して課金される

ワォ。。。

列課金だからね、しょうがないね。。。

なので、普通のRDBやRedshiftを使い慣れた人が、数TBあるトランザクションデータで集計しようとしたとき

「特定の期間の特定の製品の販売実績を抽出したから大した課金額にならんやろ」

と思っていてもかなりの金額が課金される可能性がある。

上記のような状態を防ぐためにもBigQueryでは

1. 時系列の分割オプション（さらにクラスタオプションも）を指定して走査範囲を限定する
2. クエリする際に日付部分をワイルドカードで指定して取り出すことで１テーブルあたりのデータ量を小さくする

といった工夫が必要になる。

エラーの原因は何か

私も一部のデータを集計したいのに毎回全件課金されるのは防ぎたかったので、分割テーブルを作成することに。

公式のリファレンスを調べたところ、
CREATE TABLE [PJNAME.DATASETNAME.TABLENAME] AS SELECTで既存のテーブルから新規テーブルが作れるっぽかったので早速実行。

元々のデータは時系列の分割パーティションが適用されていない株価データ１０年分だったので、日付としては365*10=3650と3650日分の分割が実行されるはずだった。

 -- Create table from stock data
 CREATE TABLE `[PJNAME].[DATASETNAME].[NEWTABLE]`  
 PARTITION BY date
 AS
 SELECT
     date
     open
     high
     low
     close
FROM `[PJNAME].[DATASETNAME].[EXISTTABLE]`
;

しかし、下記のエラーが返ってきた。。。

Too many partitions produced by query, allowed 2000, query produces at least 4418 partitions

さらに公式のリファレンスを調べたところ、分割パーティション可能なのは2000
までとのこと

え、

そうなん？？

少なくない？？

じゃあどうすればいいのか？

つまるところ、日付が2000日を超えなければ良いわけなので、エラーが出ないように期間を絞ることに

 -- 期間を絞ってテーブルを作成
 CREATE TABLE `[PJNAME].[DATASETNAME].[NEWTABLE]`  
 PARTITION BY date
 AS
 SELECT
     date
     open
     high
     low
     close
FROM `[PJNAME].[DATASETNAME].[EXISTTABLE]`
WHERE
date between "2015-01-01" and "2018-12-31"
 ;

こうすると確かにテーブルが作成できた。原因はやはり分割テーブルで指定した日付が２０００を超えたことが原因であるらしい。

でも、これだと５年以上の長めの時系列データを１つのテーブルで管理できなくて不便。。。

ワイルドカードと分割テーブルを組み合わせる

１つのテーブルで2000日のデータをぶち込もうとするから問題が起こるわけなので、BQの機能をいい感じに使うのならワイルドカードと分割テーブルを組み合わせるのが良いのでは？？と考えている

今回のケースであれば、１年ごとにテーブル自体を分けることで対応（１年は365日なので2000日に収まるし）

こんな感じ

 -- テーブル名に2015から2017までのサフィックスを付けて３つテーブルを作成する
 CREATE TABLE `[PJNAME].[DATASETNAME].[TABLE_PREFIX]_{2015,2016,2017}`  
 PARTITION BY date
 AS
 SELECT
     date
     open
     high
     low
     close
FROM `[PJNAME].[DATASETNAME].[EXISTTABLE]`
WHERE
--2015年ならば
date between "2015-01-01" and "2015-12-31"
/*
date between "2016-01-01" and "2016-12-31"
date between "2017-01-01" and "2017-12-31"
*/
 ;

で、年を跨いで任意の期間のデータ取得する場合はこんな感じでアクセスする

SELECT
     date
    ,open
    ,high
FROM
    `[PJNAME].[DATASETNAME].[TABLE_PREFIX]_*`
WHERE
     date between "2016-01-01" and "2017-05-31"
LIMIT 1000 --別に課金には影響しないけど一応付けておく
;

おしまい