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

BigQueryでパーテーションを使い、利用料金の節約をする

More than 1 year has passed since last update.

お金がない。給料日前の社会人の共通の悩みですね。
今月もBigQueryで実行しすぎて、こんなにも出費がかさんでいる、、給料が入るまで白湯しか飲めない。。。今回はそんなそこのあなたにお送りします。

概要

BigQueryで、日付単位でテーブルを分ける機能、分割テーブル(パーティション)の読み込み方法をまとめてました。

参考ページ

https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja&_ga=2.63498345.-710407308.1550214548&_gac=1.246598192.1550709266.EAIaIQobChMIxqaXjMrL4AIVlaqWCh2M1gzIEAAYASAAEgJXCfD_BwE

使用テーブル: 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 りんご

読み込み手順1:自動認識でファイルを読み込ませる

BigQueryの画面から「テーブルを作成」を選択(以下画像)

partition1.png

ポップアップした画面で、今回はローカル保存しているshopping.csvを「ファイル名を選択」で指定します。
任意の「テーブル名」をつけ、最後にスキーマの「自動検出 スキーマと入力パラメータ」の欄にチェックします。今回はパーティションとクラスタの設定は「パーティショニングなし」を選びます。ここで「取り込み時間により分割」を選ぶと、実行を行った日付で一つパーティショニングが行われます。last_dateに関係なく、本日の日付で一つの分割テーブルを作るとき使います

shopping2.png

通常通りshoppingのテーブルが取り込まれました。日付の入ったlast_dateのカラムはDATE型として認識されています。
image.png

読み込み手順2:テーブルを分割(パーテーショニング)する

BigQuery
CREATE TABLE
  test1.p_shopping
PARTITION BY
  last_date AS
SELECT
  *
FROM
  test1.shopping;

上記のコードで、
CREATE TABLE 'データセット名'.'テーブル名' PARTITION BY '日付型のカラム' AS .....と書き出すことで、作成されるファイルが分割されます。ちなみにこの実行ではテーブル全体が指定されるため、処理量は節約されません涙
image.png

こうしてできた分割テーブルは、BigQuery上のアイコンではおしゃれに表示されます。

image.png

p_shoppingのテーブル詳細を見てみると、表情報の欄にしっかり、フィールドで分割 'last_date'と記載されてます。
今後このカラムで日付指定すると、通常より少ない処理容量となります。
shopping3.png

実践:分割テーブルを使ってみる。

先ほど作成した分割テーブルを使い、last_dateが”2019-01-30”の行を抜き出してみます。

BigQuery
SELECT
  *
FROM
  test1.p_shopping
WHERE
  last_date ='2019-01-30';

image.png

image.png

ありがたいことに、元々の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 にカラム二つを指定し、実行してみます、が...

image.png

PARTITION BYは一つしか使えません、二つあるよ!とエラーが出ました。

TIMESTAMP型でも日付で分割してくれるのではないか  → 無理

shoppingのテーブルのlast_dateをTIMESTAMP型へ変更して、PARTITION BYを使い分割テーブル化を試みましたが、、、

BigQuery
CREATE TABLE
  test1.shopping_1 AS
SELECT
  user_id,
  TIMESTAMP(last_date) AS last_date,
  item
FROM
  test1.shopping

image.png

分割テーブルは現在、DATE型のみ対応しています。。。

まとめ

分割テーブル(パーテーション)を使うと、利用するテーブルの容量が限定されるため、課金対象となるテーブルの作成容量の節約になります。
しかし、現状分割対象は日付のみで、利用者によってはあまり使い勝手がよくない機能なのかもしれません。

20190227現在なので、更新すると、DATE型以外も使えるm(_ _ )m

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