BigQueryの固定長カラム

日本情報通信の中根です。
本記事は弊社のアドベントカレンダー2日目の記事になります。

BigQueryは先月Parameterized Types(日本語訳パラメータ化されたデータ型)というデータ型制約をサポートしました。
今回はこの機能について触れたいと思います。

TL;DR

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

どのようなもの？

Parameterized Typesは2021年11月2日にGAされた機能となります。

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

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

テーブルの見え方

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

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")
);

コンソール上から設定した文字長や精度、スケールが確認できます。

データのINSERT

成功パターン

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

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桁
;

問題なくINSERTできました。

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

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

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によるチェックは通りましたが実行したところエラーが出ました。

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

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

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

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によるチェックは通りましたが実行したところエラーが出ました。

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

範囲外のスケールのINSERT

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

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できましたのでデータを見てみましょう。

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

NUMERIC/BIGNUMERICの挙動についてはドキュメントに解説があります。

まとめ

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

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

BigQueryのParameterized Typesを試す