Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
33
Help us understand the problem. What is going on with this article?
@aki3061

BigQueryで「日付別テーブル」や「テーブルパーティショニング」を活用して大規模データを扱う

More than 1 year has passed since last update.

概要

BigQueryで大量のデータを扱う場合、日付別テーブルもしくはパーティショニングされたテーブルを利用すると便利です。

これらのテーブルの種類についてはドキュメントにもありますが、いくつかのページにまたがっていたりして見つけるのに苦労しているので、ここにまとめておきます。

3つの選択肢

「日付別テーブル」「取り込み時間で分割されたテーブル」「分割テーブル」の3つの選択肢があります。

後ろの2つがパーティショニングされたテーブルです。テーブルとしては1つですが内部的に分割(パーティションニング)されていて、実行するクエリで読み込むパーティションを指定することでスキャン対象のデータサイズを減らすことができ、パフォーマンスとコストを向上させることができます。

「日付別テーブル」は、その名の通り日付ごとに作成した複数のテーブルです。命名規則に従ったテーブル名をつけることで、Webコンソール上でも複数テーブルが集約して表示されます。また、BigQueryにはワイルドカードテーブルという機能があり、複数のテーブルをクエリ上でまとめて扱うことができます。

それぞれのタイプについて説明していきます。

テーブルパーティショニングを使わない

日付別テーブル

テーブル名に"_yyyymmdd"のサフィックスを付けるだけです。この方法は、「シャーディング」とも呼ばれます。

例としてfoo_yyyymmddのテーブルを作りました。各テーブルのフィールド定義は一致している必要はありません。
スクリーンショット 2018-10-07 17.26.05.png

このテーブルタイプのメリットは、単純さです。例えば日次バッジでテーブルの作成を行なっているとき、エラーなどでデータが中途半端な状態になったとしても、テーブルを削除してバッジ処理をやり直すことができます。
テーブル同士のフィールドの一貫性を気にしなくてもいいことも、時には便利でしょう。

デメリットとしては、パーティションされたテーブルに比べて、クエリのパフォーマンスが低くなります。

日付別テーブルは、後で紹介する取り込み時間で分割されたテーブルへの変換も可能です。

パーティショニングを使う

「取り込み時間で分割されたテーブル」と「分割テーブル」の2種類があります。後者の方が新しい機能で便利だと思うのですが、一応いずれも紹介します。

取り込み時間で分割されたテーブル

BigQueryがデータを取り込む日にちでパーティションが作成されます。(1日1パーティション) テーブル作成時に、「取り込む時間により分割」を指定します。(Webコンソールの場合)

クエリでパーティションを限定することでクエリのパフォーマンスが向上し、読み込むデータサイズも減少することからコスト(お金)も下がります。

例です。

SELECT * 
  FROM `project.dataset.table` 
 WHERE _PARTITIONTIME >= TIMESTAMP("2018-10-07")

_PARTITIONTIMEは自動で生成される擬似列で、テーブルに読み込まれたデータの日付ベースのタイムスタンプ(UTC)がこの列に格納されています。これを使ってデータを読み込むパーティションを指定します。

デフォルトではデータが入れられた日のパーティションにデータが入りますが、パーティションを指定することもできます。テーブル名$yyyymmddを指定してデータをロードします。

分割テーブル

「取り込み時間で分割されたテーブル」ではデータを格納するパーティションの選択に_PARTITIONTIME列が使われましたが、分割テーブルではそれに変わるフィールドをユーザが指定します。

例えば、Webサーバのログを保存するテーブルがあり、ユーザがアクセスした時刻が入るaccess_timeというフィールドあったとします。
このテーブルに対して実行されるクエリは、access_timeで条件を指定することが多いはずです。(例えば、2018年10月1日以降のログを確認する)

 テーブル作成時に、このフィールドをパーティションに使うフィールドとして指定します。そうすることで、WHERE句にaccess_timeを指定したクエリは、必要なパーティションからのみデータが読み込まれます。

SELECT * 
  FROM `project.dataset.table` 
 WHERE access_time >= TIMESTAMP("2018-10-01")

パーティションに使うフィールドの型は、TIMESTAMP型もしくはDATE型である必要があります。

まとめ

3つのタイプのテーブルについて紹介しました。これらの比較はドキュメントにもありますので、ご参照ください。

以上です。

33
Help us understand the problem. What is going on with this article?
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
aki3061
ソフトウェアエンジニア

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
33
Help us understand the problem. What is going on with this article?