Challenge Google Cloud Data Engineering part3

前回に引き続き 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では、データの非正規化が一般的といわれています。***

データの重複を無くすことで、整合的にデータを取り扱えるようにデータベースを設計すること```正規化```と呼びます。
```非正規化```とは、各列に重複するフィールド値を使用できるようにしたものです。正規化されたデータベースに対して、性能を上げるために重複したデータを使用する設計を行う事を言います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/7aa0fc30-573f-8fe3-886c-e4d4cf0fb075.png)

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 |     +-------+     +---+
+---+     +---+
```
※参考文献

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#join_types


***但し、主キー、外部キーの定義は出来ません。***

これらのスキーマは```ファクトテーブル```、```ディメンションテーブル```から構成されるデータモデルです。

***ファクトテーブルとは事実が保管されるテーブルであり、複数のディメンションテーブルへの結合を可能とします。ファクトテーブルが分析用途に使用されます。
ディメンションテーブルとは、ファクトテーブルに対しての属性情報が保管されるテーブルとなります。ファクトテーブルとの結合を行います。***

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/bde411b4-7c87-cf08-262f-db5c00fbbfe2.png)


ネストされたデータと繰り返しデータ
-------------------------------
負荷の高い結合を避けるため、BigQuery では、以下をサポートしています。

- ***ネストされた列の定義***

ネストされたデータは、ネストする列のスキーマを ```RECORD``` に設定する事で、階層的にデータを配置する事が出来ます。
ネストされた列は親子関係にあり、date列にネストされたname列は ```date.name``` と表示します。

- ***繰り返し列の定義*** 

繰り返し列とは対象に含まれる複数の要素から構成される列のことです。
要素とは，繰返し列中で繰り返されている各項目を示します。

繰り返しデータが含まれる列を作成するには、スキーマでモードを ```REPEATED``` に設定します。繰り返しフィールドには、標準 SQL の ```ARRAY``` 型としてアクセスできます。

例えば、以下のように ```type:RECORD``` / ```mode:REPORTED```を指定した```json```ファイルを用意します。

``` testschema.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"
    }
    ]
  }
]
```

実際のデータ構造はこのようになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/56d55840-fca9-9cc9-12b4-089ceb77f802.png)


テーブルのパーティショニング
=========================

```パーティション分割テーブル``` は、セグメントに分割されたテーブルで、データの管理や照会をより簡単に行うことができます。
テーブルのパーティショニングを行う事で、クエリで読み取られるバイト数を減らし、コストを管理できます。

パーティショニングの有効化は、***テーブルの作成プロセスで行います。***以下3点の場合に有効化されます。なお、テーブルに追加される新しいレコードは、適切なパーティションに格納されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/15b21fd6-89ef-211c-455d-013d411e6403.png)


- ***取り込み時間***

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_clusteredtable.sql:
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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1597898/ef32f10e-1f26-20e1-a176-32c2acadbd98.png)

内部的には、データがクラスタ化されたテーブルに書き込まれると、値のソート処理が行われ、```BigQuery Storage```に複数のブロック単位で整理します。
クラスタ化列を含むデータ参照をした場合、このブロック単位でデータを整理し、不要なデータスキャンを排除し、ブロックごとに値がまとめて配置されるため、パフォーマンスが向上するものとなります。

以上です。