お金がない。給料日前の社会人の共通の悩みですね。
今月もBigQueryで実行しすぎて、こんなにも出費がかさんでいる、、給料が入るまで白湯しか飲めない。。。今回はそんなそこのあなたにお送りします。
概要
BigQueryで、日付単位でテーブルを分ける機能、分割テーブル（パーティション）の読み込み方法をまとめてました。
参考ページ
使用テーブル: shopping
日時が記載されている適当なテーブルを作成しました。
| user_id | last_date | item |
|---|---|---|
| A | 2019/1/29 | ちくわぶ |
| A | 2019/12/30 | 白菜 |
| B | 2019/10/31 | みかん |
| B | 2019/6/29 | りんご |
| C | 2016/1/30 | メロン |
| C | 2019/12/30 | みかん |
| B | 2019/10/31 | りんご |
| C | 2019/6/29 | ちくわぶ |
| A | 2019/1/30 | メロン |
| B | 2019/12/30 | ねぎ |
| B | 2019/10/31 | みかん |
| C | 2019/6/30 | りんご |
読み込み手順１:自動認識でファイルを読み込ませる
BigQueryの画面から「テーブルを作成」を選択（以下画像）
ポップアップした画面で、今回はローカル保存しているshopping.csvを「ファイル名を選択」で指定します。
任意の「テーブル名」をつけ、最後にスキーマの「自動検出 スキーマと入力パラメータ」の欄にチェックします。今回はパーティションとクラスタの設定は「パーティショニングなし」を選びます。ここで「取り込み時間により分割」を選ぶと、実行を行った日付で一つパーティショニングが行われます。last_dateに関係なく、本日の日付で一つの分割テーブルを作るとき使います
通常通りshoppingのテーブルが取り込まれました。日付の入ったlast_dateのカラムはDATE型として認識されています。
読み込み手順2:テーブルを分割（パーテーショニング）する
CREATE TABLE
test1.p_shopping
PARTITION BY
last_date AS
SELECT
*
FROM
test1.shopping;
上記のコードで、
CREATE TABLE 'データセット名'.'テーブル名' PARTITION BY '日付型のカラム' AS .....と書き出すことで、作成されるファイルが分割されます。ちなみにこの実行ではテーブル全体が指定されるため、処理量は節約されません涙
こうしてできた分割テーブルは、BigQuery上のアイコンではおしゃれに表示されます。
p_shoppingのテーブル詳細を見てみると、表情報の欄にしっかり、フィールドで分割 'last_date'と記載されてます。
今後このカラムで日付指定すると、通常より少ない処理容量となります。
実践：分割テーブルを使ってみる。
先ほど作成した分割テーブルを使い、last_dateが”2019-01-30”の行を抜き出してみます。
SELECT
*
FROM
test1.p_shopping
WHERE
last_date ='2019-01-30';
ありがたいことに、元々の264Bのテーブルがこんなにも節約されました涙
実験:分割テーブルできるカラムと、できないカラム
どうせなら日付以外でも分割テーブルが作れたらいいなぁ、と思い試してみました。
DATE型を二つ指定しても分割できるのか → 無理
試しにDATE型のカラムが二つあるテーブルを分割テーブルにできるか、確認してみました。
利用したのは以下のテーブルです。
| user_id | last_date | p_date | item |
|---|---|---|---|
| 1 | 2016-01-30 | 2017-12-30 | メロン |
| 2 | 2017-12-30 | 2017-12-31 | 白菜 |
| 3 | 2016-10-31 | 2017-08-31 | りんご |
| 4 | 2017-06-30 | 2018-01-31 | りんご |
PARTITION BY にカラム二つを指定し、実行してみます、が．．．
PARTITION BYは一つしか使えません、二つあるよ！とエラーが出ました。
TIMESTAMP型でも日付で分割してくれるのではないか → 無理
shoppingのテーブルのlast_dateをTIMESTAMP型へ変更して、PARTITION BYを使い分割テーブル化を試みましたが、、、
CREATE TABLE
test1.shopping_1 AS
SELECT
user_id,
TIMESTAMP(last_date) AS last_date,
item
FROM
test1.shopping
分割テーブルは現在、DATE型のみ対応しています。。。
まとめ
分割テーブル（パーテーション）を使うと、利用するテーブルの容量が限定されるため、課金対象となるテーブルの作成容量の節約になります。
しかし、現状分割対象は日付のみで、利用者によってはあまり使い勝手がよくない機能なのかもしれません。