BigQueryのParameterized Typesを試す

# BigQueryの固定長カラム

日本情報通信の中根です。
本記事は[弊社のアドベントカレンダー](https://qiita.com/advent-calendar/2021/niandc_teamgcp)2日目の記事になります。  

BigQueryは先月[Parameterized Types(日本語訳パラメータ化されたデータ型)](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#parameterized_data_types)というデータ型制約をサポートしました。
今回はこの機能について触れたいと思います。

## TL;DR

- 所謂固定長カラム
- データ挿入時にマッチしていない場合はエラーとなるためバリデーションとしても利用できる
- スケールを超えた場合は四捨五入

## どのようなもの？

Parameterized Typesは[2021年11月2日](https://cloud.google.com/bigquery/docs/release-notes#November_02_2021)にGAされた機能となります。

以下の型に対してカラム長を設定することが可能となります。

- STRING(Length)
- BYTES(Length)
- NUMERIC(Precision) / NUMERIC(Precision, Scale)
- BIGNUMERIC(Precision) / BIGNUMERIC(Precision, Scale)

## テーブルの見え方

BigQuery コンソールからはどのように見えるのでしょうか。
Create TableにてParameterized Typesを含むテーブルを作成しましょう。  

```sql
CREATE OR REPLACE TABLE blog.parameterized_table (
    -- 従来の型
    NORMAL_STR STRING OPTIONS(description="従来のSTRING型")
    ,NORMAL_NUMERIC NUMERIC OPTIONS(description="従来のNUMERIC型")
    -- Parameterized Types
    ,PARAM_STR STRING(10) OPTIONS(description="文字長10のSTRING型")
    ,PARAM_NUMERIC NUMERIC(3,2) OPTIONS(description="最大精度3, 最大スケール2のNUMERIC")
);
```

コンソール上から設定した文字長や精度、スケールが確認できます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532023/ae2aafdf-5aa3-3328-e015-ddf32bfba30f.png" width="800">

## データのINSERT

### 成功パターン

それではこのテーブルにデータをINSERTRTしてみましょう。

```sql
INSERT INTO blog.parameterized_table (
    NORMAL_STR
    ,NORMAL_NUMERIC
    ,PARAM_STR
    ,PARAM_NUMERIC
)
SELECT 
    "いろはにほへとちりぬるを"                    -- 12文字
    ,CAST(9876543210.123456789 AS NUMERIC) -- 整数部10桁、小数部9桁
    ,"いろはにほへと"                          -- 7文字
    ,CAST(1.23 AS NUMERIC)                 -- 整数部3桁、小数部2桁
;
```

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532023/ca3dd83f-e61a-202e-7827-9f64db982e2b.png" width="800">

問題なくINSERTできました。

### 最大文字列長を超える値のINSERT

10文字までの`PARAM_STR`に12文字をINSERTしてみます。

```sql
INSERT INTO blog.parameterized_table (
    NORMAL_STR
    ,NORMAL_NUMERIC
    ,PARAM_STR
    ,PARAM_NUMERIC
)
SELECT 
    "いろはにほへとちりぬるを"                  -- 12文字
    ,CAST(9876543210.123456789 AS NUMERIC)  -- 整数部10桁、小数部9桁
    ,"いろはにほへとちりぬるを"                  -- 12文字
    ,CAST(321.23 AS NUMERIC)                -- 整数部3桁、小数部2桁
;
```

Dry Runによるチェックは通りましたが実行したところエラーが出ました。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532023/cab49aa0-564d-e321-4866-570b23ce49f8.png" width="800">

文字列が10文字なのに12文字がINSERTされることに対してエラーが出ていることがわかります。

### 最大精度長を超える値のINSERT

次に最大精度3、最大スケール2`PARAM_NUMERIC`に精度5のデータをINSERTしてみます。

```sql
INSERT INTO blog.parameterized_table (
    NORMAL_STR
    ,NORMAL_NUMERIC
    ,PARAM_STR
    ,PARAM_NUMERIC
)
SELECT 
    "いろはにほへとちりぬるを"                  -- 12文字
    ,CAST(9876543210.123456789 AS NUMERIC)  -- 整数部10桁、小数部9桁
    ,"いろはにほへと"                          -- 7文字
    ,CAST(321.23 AS NUMERIC)                -- 整数部3桁、小数部2桁
;
```

こちらもDry Runによるチェックは通りましたが実行したところエラーが出ました。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532023/029cf34b-b66d-8df3-ecda-0608819e2d15.png" width="800">

最大精度3、最大スケール2の場合取り得る値が-9.99〜9.99までである旨が表示されました。

### 範囲外のスケールのINSERT

次に小数部が最大スケールを超える場合も見てみましょう。

```sql
INSERT INTO blog.parameterized_table (
    NORMAL_STR
    ,NORMAL_NUMERIC
    ,PARAM_STR
    ,PARAM_NUMERIC
)
SELECT 
    "いろはにほへとちりぬるを"                  -- 12文字
    ,CAST(9876543210.123456789 AS NUMERIC)  -- 整数部10桁、小数部9桁
    ,"いろはにほへと"                          -- 7文字
    ,CAST(0.12345 AS NUMERIC)                -- 整数部1桁、小数部5桁
UNION ALL SELECT 
    "いろはにほへとちりぬるを"                  -- 12文字
    ,CAST(9876543210.123456789 AS NUMERIC)  -- 整数部10桁、小数部9桁
    ,"いろはにほへと"                          -- 7文字
    ,CAST(0.56789 AS NUMERIC)                -- 整数部1桁、小数部5桁
;
```

上記クエリはエラーなくINSERTできましたのでデータを見てみましょう。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532023/60bab92b-0f37-4173-76b2-9e7a78feb2f0.png" width="800">

小数部はスケールにて指定した桁で四捨五入されています。

NUMERIC/BIGNUMERICの挙動については[ドキュメント](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#parameterized_decimal_type)に解説があります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532023/cda90a91-97c4-8a70-b40f-7baedc7dc08e.png" width="800">

## まとめ

固定長カラムをもつデータマートをBigQueryへ移行をする際にカラム長チェックの仕組みが必要でした。
従来はUDFで実装をされていたりしたかと思いますがParameterized Typesでも代用できそうです。

BigQueryは11年目を迎えますがGCPで最も人気のあるサービスの1つです。
現在でも毎週のようにアップデートがあります。
普段使われる中でこんな機能があれば、と思ったものがいつの間にか追加されていたりしますのでリリースノートを覗いてみると新たな発見があるかもしれません。