前回に引き続き Bigquery の記事を投稿します。
スキーマ設計
テーブル作成時、あるいはデータの読み込みの際にテーブルのスキーマを指定する事になります。データベースでは、論理設計をする際にスキーマを設計します。
データタイプ
BigQueryでは列を指定する際に以下のデータタイプをサポートしています。
| 名前 | データタイプ | 説明 |
|---|---|---|
| Integer | INT64 | 整数 |
| Floating point | FLOAT64 | 小数部分のある値 |
| Numeric | NUMERIC | 小数部分のある正確な数値 |
| BigNumeric | BIGNUMERIC | 小数部分のある正確な数値 |
| Boolean | BOOL | trueまたはfalse |
| String | STRING | 可変長文字データ |
| Bytes | BYTES | 可変長バイナリデータ |
| Date | DATE | 日時 |
| Date/Time | DATETIME | 年、月、日、時、分、秒 |
| TIMESTAMP | TIMESTAMP | タイムスタンプ |
| Struct (Record) | STRUCT | データ型とフィールド名が記載された順序付きフィールドのコンテナ |
| Geography | GEOGRAPHY | 地表上のポイントセット |
また、モードと呼ばれる列の属性を指定出来ます。
デフォルトは、NULLABLEです。
| モード | 説明 |
|---|---|
| NULLABLE | 列で NULL 値が許可されます |
| REQUIRED | NULL 値は許可されません |
| REPEATED | 列に指定された型の値の配列が含まれます |
非正規化と正規化
DWHでは、データの非正規化が一般的といわれています。
データの重複を無くすことで、整合的にデータを取り扱えるようにデータベースを設計すること正規化と呼びます。
非正規化とは、各列に重複するフィールド値を使用できるようにしたものです。正規化されたデータベースに対して、性能を上げるために重複したデータを使用する設計を行う事を言います。
Bigquery はスタースキーマとスノーフレークスキーマに基づいて従来のデータモデルを結合機能としてサポートしています。
INNER JOIN
FROM A INNER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
+---------------+
CROSS JOIN
FROM A CROSS JOIN B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
FULL [OUTER] JOIN
FROM A FULL OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
| 4 | NULL | p |
+--------------------+
LEFT / RIGHT JOIN
FROM A LEFT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
+---------------------------+
FROM A RIGHT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
ON句/USING句
FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)
Table A Table B Result ON Result USING
+---+ +---+ +-------+ +---+
| x | * | x | = | x | x | | x |
+---+ +---+ +-------+ +---+
| 1 | | 2 | | 2 | 2 | | 2 |
| 2 | | 3 | | 3 | 3 | | 3 |
| 3 | | 4 | +-------+ +---+
+---+ +---+
※参考文献
但し、主キー、外部キーの定義は出来ません。
これらのスキーマはファクトテーブル、ディメンションテーブルから構成されるデータモデルです。
ファクトテーブルとは事実が保管されるテーブルであり、複数のディメンションテーブルへの結合を可能とします。ファクトテーブルが分析用途に使用されます。
ディメンションテーブルとは、ファクトテーブルに対しての属性情報が保管されるテーブルとなります。ファクトテーブルとの結合を行います。
ネストされたデータと繰り返しデータ
負荷の高い結合を避けるため、BigQuery では、以下をサポートしています。
- ネストされた列の定義
ネストされたデータは、ネストする列のスキーマを RECORD に設定する事で、階層的にデータを配置する事が出来ます。
ネストされた列は親子関係にあり、date列にネストされたname列は date.name と表示します。
- 繰り返し列の定義
繰り返し列とは対象に含まれる複数の要素から構成される列のことです。
要素とは，繰返し列中で繰り返されている各項目を示します。
繰り返しデータが含まれる列を作成するには、スキーマでモードを REPEATED に設定します。繰り返しフィールドには、標準 SQL の ARRAY 型としてアクセスできます。
例えば、以下のように type:RECORD / mode:REPORTEDを指定したjsonファイルを用意します。
[
{
"mode": "REQUIRED",
"name": "date",
"type": "STRING"
},
{
"mode": "REQUIRED",
"name": "name",
"type": "STRING"
},
{
"mode": "REPEATED",
"name": "test",
"type": "RECORD",
"fields":
[
{
"name": "date",
"type": "STRING"
},
{
"name": "randomvalue",
"type": "STRING"
}
]
}
]
実際のデータ構造はこのようになります。
テーブルのパーティショニング
パーティション分割テーブル は、セグメントに分割されたテーブルで、データの管理や照会をより簡単に行うことができます。
テーブルのパーティショニングを行う事で、クエリで読み取られるバイト数を減らし、コストを管理できます。
パーティショニングの有効化は、***テーブルの作成プロセスで行います。***以下3点の場合に有効化されます。なお、テーブルに追加される新しいレコードは、適切なパーティションに格納されます。
- 取り込み時間
BigQuery がデータを取り込む際のタイムスタンプに基づいてテーブルが分割されます。
bq query --destination_table mydataset.mytable --time_partitioning_type=DAY...
- DATE 型または、TIMESTAMP 型の列
テーブルの TIMESTAMP、DATE、DATETIME 列に基づいてテーブルが分割されます。
bq mk --table --schema a:STRING,tm:TIMESTAMP --time_partitioning_field tm
- 整数型の列
テーブルは整数列に基づいて分割されます。
bq mk --table --schema "customer_id:integer,value:integer" --range_partitioning=customer_id,0,100,10 my_dataset.my_table
クラスタリング
クラスタ化 とは、フィルタ句を使用するクエリや、データを集約するクエリなどを使用する場合にパフォーマンスを向上させることができます機能です。
具体的には、テーブル作成時に列を指定してクラスタ化します。
CREATE TABLE dataset.clusteredtable
(
a1 STRING,
a2 STRING,
a3 STRING,
Date TIMESTAMP,
a4 GEOGRAPHY
)
PARTITION BY DATE(Date)
CLUSTER BY a2
OPTIONS (
partition_expiration_days=3,
description="cluster")
AS SELECT * FROM dataset.othertable
内部的には、データがクラスタ化されたテーブルに書き込まれると、値のソート処理が行われ、BigQuery Storageに複数のブロック単位で整理します。
クラスタ化列を含むデータ参照をした場合、このブロック単位でデータを整理し、不要なデータスキャンを排除し、ブロックごとに値がまとめて配置されるため、パフォーマンスが向上するものとなります。
以上です。