[BigQuery] Clustered Table調査

[PLAID Advent Calendar 2018](https://qiita.com/advent-calendar/2018/plaid) 7日目の記事です。
BigQueryの[Clustered Table(クラスタ化テーブル)](https://cloud.google.com/bigquery/docs/clustered-tables)という機能について調べてみました。

# Clustered Tableとは
- 同じ属性のデータを物理的に同じ場所に配置することによってクエリ実行時の効率を上げる(スキャンするデータ量を減らせる)
- テーブルを作成する際に、クラスタリング列を最大４列まで指定できる
- 現在はPartitioned Tableでのみ使用可能

Partitioned Tableに関しては以下を参照して下さい。
[分割テーブルの概要](https://cloud.google.com/bigquery/docs/partitioned-tables)
[BigQuery の Partitioned Table 調査記録](https://qiita.com/sonots/items/0849f58627944f821beb)


# Clustered Tableの作成方法

以下のようなDDLで作成することができます。

```
CREATE TABLE `mydataset.mytable`
  (
    date DATE,
    category STRING
  )
PARTITION BY
  date
CLUSTER BY
  category
```

# Clustered Tableのパフォーマンス検証

## 試行１: Clustered Tableと通常のテーブルの比較

サンプルデータである `publicdata.samples.natality` をベースにしてテスト用のClustered Tableを作成します。
サンプルデータの一覧については以下のクエリで取得できます。

```
SELECT * FROM `publicdata.samples.__TABLES__`
```

`publicdata.samples.natality`には約1億4000万レコード入っています。

### Clustered Tableを作成

以下のようなDDLでテーブルを作成します。

```
CREATE TABLE
  `mydataset.clustered_natality`
   (
     date DATE,
     state STRING,
     mother_age INT64
   )
PARTITION BY
  date
CLUSTER BY
  state,
  mother_age
```

### データの挿入

作成されたテーブルに`publicdata.samples.natality`のデータを入れます。
宛先テーブルを`mydataset.clustered_natality`に設定し、以下のクエリを実行します。
パフォーマンスを試したかったため大量のデータを入れていますが、重いクエリになるので注意が必要です。

```
SELECT 
  DATE(year, month, 1) date,
  *
FROM `publicdata.samples.natality`
```

dayの部分を1に固定しているのは、partitionの上限2000を超えないようにするためです。

### パフォーマンス測定

#### Clustered Tableに対するクエリ
```
SELECT *
FROM `mydataset.clustered_natality`
WHERE state = 'WY'
```

経過時間: 3.955 秒
処理されたバイト数: **22.01 GB**
消費したスロット時間: **16.769 秒**

#### 元のテーブルに対するクエリ

```
SELECT *
FROM `publicdata.samples.natality`
WHERE state = 'WY'
```

経過時間: 5.790 秒
処理されたバイト数: **21.94 GB**
消費したスロット時間: **30.238 秒**

### 考察

- 「処理されたバイト数」に差が出るかと思ったが、ほとんど出ない。
- partition fieldの方で優先的に分割される？ その結果stateではほとんど分割されないのではないか。

## 試行2: Clustered Table(partitionしない)と通常のテーブルの比較

データの挿入部分を以下のようなクエリに変えてみました。
partitionに使用しているdateを全て同じにするという暴挙です。

```
SELECT 
  DATE(2000, 1, 1) date,
  *
FROM `publicdata.samples.natality`
```

### パフォーマンス測定

#### Clustered Tableに対するクエリ
```
SELECT *
FROM `mydataset.clustered_natality`
WHERE state = 'WY'
```

経過時間: 4.405 秒
処理されたバイト数: **2.41 GB**
消費したスロット時間: **4.734 秒**

#### 元のテーブルに対するクエリ(再掲)

```
SELECT *
FROM `publicdata.samples.natality`
WHERE state = 'WY'
```

経過時間: 5.790 秒
処理されたバイト数: **21.94 GB**
消費したスロット時間: **30.238 秒**

思った通りの結果が！

### 考察
- partitioned fieldで分割されず、stateで分割されるため、処理されたバイト数と消費したスロット時間には大きな差が出た
- 分散するので実行時間についてはそこまで大きな差はない

## 試行3: Clustered TableとPartitioned Tableの比較

### テーブルの作成

以下のDDLでPartitioned Tableを作成します。

```
CREATE TABLE
  `mydataset.partitioned_natality`
   (
     date DATE
   )
PARTITION BY
  date
```

### データの挿入

以下のクエリで`clustered_natality`、`partitioned_natality`のそれぞれにデータを挿入します。

```
SELECT 
  DATE(year, month, 1) date,
  *
FROM `publicdata.samples.natality`
```

### パフォーマンス測定

#### Clustered Tableに対するクエリ
```
SELECT *
FROM `mydataset.clustered_natality`
WHERE
  date = DATE(2000, 1, 1)
  AND state = 'WY'
```

経過時間: 1.405 秒
処理されたバイト数: 60.99 MB
消費したスロット時間: 0.210 秒

#### Partitioned Tableに対するクエリ

```
SELECT *
FROM `mydataset.partitioned_natality`
WHERE
  date = DATE(2000, 1, 1)
  AND state = 'WY'
```

経過時間: 1.189 秒
処理されたバイト数: 60.99 MB
消費したスロット時間: 0.208 秒

差は無し...

### 考察
- partitionで分割されきっていてstateでは分割されていないのではないか

# まとめ

Clustered Tableを使用すれば、使い方によってはスロットの消費をかなり節約できることが分かりました。
データサイズがもっと大きければより効果が発揮されると思います。
ただ現状ではpartition fieldでほとんど決まってしまいそうです。
Partitioned Table以外のテーブルでのクラスタリングのサポートも開発中のようなので、今後に期待です。