BigQueryのSQLいろいろ (2) 文字列型 | Wantedly Engineer Blog

BigQueryのSQLについて、ドキュメントを読んだり実験したりしながら挙動を解き明かしていこうと思います。第2回は文字列型についてです。

文字列型と関連する型

本稿では以下を扱います。

• 文字列型はUnicode文字列を扱います。
• BYTES型はバイト列を扱います。
• JSON型はJSON Valueを表現した型です。内部表現上はSTRUCTやARRAYと近い型とも言えますが、BigQueryの操作上はSTRINGと互換性があることが多いため、本稿で取り扱います。

文字列型

文字列型はUnicode文字列を扱う型です。エンコード形式としてUTF-8を使っています。

BigQueryの文字列型は、well-formedなものだけを扱います。つまり、UTF-8のルールに沿っていない入力は弾かれるようになっています。他プログラミング言語では、Goがill-formedな文字列も許容するのに対してRustがwell-formedな文字列に限定しているという例があります。

well-formedなUTF-8文字列は、コードポイント (Unicode Scalar Value) の並びと1対1対応します。そのため、普段はUTF-8であるかどうかを意識する必要はありません。BigQueryでエンコーディングを意識するのは主に以下の場合です。

他の言語にも通じることですが、Unicode文字列を扱うにあたっては以下のような点に注意する必要があります。

• 文字の区切りや文字数に関する適切な定義は、それを必要とするコンテキストによって異なります。視覚的な操作においては、Extended Grapheme Clusterが適切であることも多いです。
• 同じ意味の文字列が複数の表現を持つことがあります。これらは正準等価性に基づく正規化 (NFC, NFD) で同一視することが望ましい場合があります。
• 文字列の表示にあたっては、Bidiに由来する動作に気をつける必要があります。
• 文字列の適切な並び順(照合順)は、それを必要とするコンテキストによって異なります。デフォルトの比較はコードポイントに基づくものであり、機械的な操作には適していますが、ユーザー向けには必ずしも適切ではありません。照合順のユースケースについては Unicode Collation Algorithm に挙げられている様々な言語の例を見るのがわかりやすいでしょう。

照合順

照合順は、文字列の比較演算の振舞いを定めるオプションです。現時点で、BigQueryは以下の2つの照合順のみをサポートしています。

• ” (空の名前): バイナリ比較。デフォルトの照合順。
• ‘und:ci’: 言語非依存の、大文字小文字を同一視した比較。

オプションを指定しない場合は空の照合順指定 (”) と同等とみなされ、バイナリ比較が使われます。

バイナリ比較はUTF-8のバイト列による辞書順比較です。この辞書順比較では、接頭辞は元の文字列よりも前にソートされます。このアルゴリズムは、コードポイントごと (=UTF-32) で辞書順比較した場合と同じ結果になります。

‘und:ci’ は大文字と小文字を同一視して比較します。

SELECT COLLATE(x, 'und:ci') AS x FROM UNNEST(['java', 'Java', 'JAVA', 'Jake', 'jail']) AS x
ORDER BY x

‘und:ci’ は非ASCII文字に対してもある程度期待通りに動作します。仮名文字に対しても動作することから、内部的にはDUCETではないかと推察されます。

PostgreSQLなどの他のSQLでは比較演算などの構文にコレーション情報を付加します。いっぽう、BigQueryでは比較構文ではなく COLLATE 関数で比較対象の式にコレーション情報を付加します。しかし、COLLATE関数の効果は実際には静的に解決されるため、異なるコレーションの入った式を比較しようとすると実行時エラーではなく静的検査でエラーになります。おそらく内部的にSTRING型をコレーション別の型に分けて推論しているのではないかと思います。

つまり、BigQueryのコレーション構文は一見すると独特に見えますが、内部的にはPostgreSQLなどの他のSQLと同じような形で実行されていると考えられます。

長さの制約

テーブルカラムでは STRING(10) のように長さの上限を指定できます。これはコードポイント (Unicode Scalar Value) の個数で数えられます。そのため以下の点に注意が必要です。

• 日本語テキストのように3byteまたは2byteと考えられがちな文字も、1文字として数えられます。
• 正準等価な文字列の中でも受理できるものとできないものが分かれます。多くの場合はNFCで正規化したほうが受理されやすいと考えられます。
• 仮にNFCで正規化したとしても、ユーザーが受容する文字数とは一致しない場合があります。特に、Extended Grapheme Clusterで数えた場合との差異に注意が必要です。

BYTES型

BYTES型は名前の通り、バイト列を表す型です。バイナリデータを扱う場合やUnicode以外の文字列を扱う場合などはこちらの型を使うことになります。BigQueryのUIやAPI上はBase64で表示されます。

文字列操作に使われる関数の多くはBYTES型でも利用できます。 (BYTE_LENGTH, CONCAT, STARTS_WITH, SPLIT, SUBSTR など)

長さの制約

テーブルカラムでは BYTES(10) のように長さの上限を指定できます。これはバイト数で数えられます。 STRING(10) とは大きく意味が異なる点に注意が必要です。

JSON型

JSON型はJSON Valueを表現した型です。つまり内部的には構造をそのまま保持しており、ARRAYやSTRUCTに近いと考えたほうが適切です。ただし、BigQuery上はSTRING型と共通で利用できる処理が多いため、ここで紹介しています。

パース済みのJSONからは以下のような情報が欠落しているので注意が必要です。

• レイアウト情報 (空白)
• オブジェクトのキーの順序
• オブジェクトの重複キー
• 数値データ・文字列データの表記ゆれ
• ゼロの符号。ただし、JSONをパースせずにJSON_ARRAYなどの関数を使ってJSON Valueを組み立てた場合はゼロの符号は保存されるようです。

また、null返却時の振舞いにも差異があります。

BigQueryのJSON Valueの数値は、内部的には以下のいずれかで表現されます。

• 有限の FLOAT64 値
• INT64 値
• UINT64 値

同じ数値を複数の方法で表現できる場合、内部表現の差異は基本的に区別されません。ただし負のゼロは他と区別される点に注意が必要です。

JSONの数値のパースの規則は以下のようになっているようです。

• 小数点 . や指数 e がついている場合は FLOAT64 としてパースする。 (つまり、丸めが発生する)
• 整数値であっても、範囲が INT64 と UINT64 を越える場合は FLOAT64 としてパースする。 (つまり、丸めが発生する)
• それ以外の場合は、範囲に応じて INT64 または UINT64 としてパースする。

SELECT
  
  INT64(JSON '9123456789012345678'),
  
  INT64(JSON '9123456789012345678.0'),

比較不可能

JSON型はSTRING型と異なり、 Orderable, Groupable, Comparable のいずれでもありません。したがって同等性の比較・大小比較・ソート・グルーピングはいずれも不可能です。

まとめ

BigQueryの以下の型について、細部の振舞いに注意しながら紹介しました。

• STRING型: Unicode文字列(UTF-8)
• BYTES型: 任意のバイト列
• JSON型: 任意のJSON Value (JSON関係の処理で、文字列と同様に利用可能)