Help us understand the problem. What is going on with this article?

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

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

3行で

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

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

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

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

ワォ。。。

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

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

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

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

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

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

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

エラーの原因は何か

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

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

元々のデータは時系列の分割パーティションが適用されていない株価データ10年分だったので、日付としては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を超えたことが原因であるらしい。

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

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

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

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

こんな感じ

 -- テーブル名に2015から2017までのサフィックスを付けて3つテーブルを作成する
 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 --別に課金には影響しないけど一応付けておく
;

おしまい

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away