BigQuery: リテラルとデータ型

February 7, 2021

BigQueryでサポートされるデータ型 #

• 数値型 INT64 NUMERIC BIGNUMERIC FLOAT64
• ブール型 BOOL
• 文字列型 STRING
• バイト型 BYTES
• 日付型 DATE
• 日時型 DATETIME
• 時刻型 TIME
• タイムスタンプ型 TIMESTAMP
• 配列型 ARRAY
• 構造体型 STRUCT
• 地理型 GEOGRAPHY

SELECT
  NULL AS val_of_NULL,
  1 AS val_of_INT64,
  0xFF AS val_of_INT64,
  1.0 AS val_of_FLOAT64,
  NUMERIC '1' AS val_of_NUMERIC,
  BIGNUMERIC '1' AS val_of_BIGNUMERIC,
  TRUE AS val_of_BOOL_TRUE,
  FALSE AS val_of_BOOL_FALSE,
  'string with single quote' AS val_of_STRING,
  "string with double quote" AS val_of_STRING,
  '''string with triple single quote
it can have multi lines''' AS val_of_STRING,
  """string with triple double quote
it can have multi lines""" AS val_of_STRING,
  B'bytes' AS val_of_BYTES,
  R'regexp' AS val_of_STRING_as_RegExp,
  DATE '2021-02-07' AS val_of_DATE,
  DATETIME '2021-02-07 15:10:20' AS val_of_DATETIME,
  TIME '15:10:20' AS val_of_TIME,
  TIMESTAMP '2021-02-07T15:10:20+0900' AS val_of_TIMESTAMP,
  [1,2,3] AS val_of_ARRAY_OF_INT64,
  ARRAY[1,2,3] AS val_of_ARRAY_OF_INT64,
  ARRAY<INT64>[1,2,3] AS val_of_ARRAY_OF_INT64,
  (123, 'string') AS val_of_STRUCT,
  STRUCT(123 AS num, 'string' AS str) AS val_of_STRUCT,
  STRUCT<num INT64, str STRING>(123, 'string') AS val_of_STRUCT,

R プレフィックスの正規表現リテラル #

正規表現のデータ型があるわけではないので中身は STRING かもしれない。 R プレフィックスを使うとエスケープシーケンスの扱いが変わり正規表現が書きやすい。

SELECT
  REGEXP_REPLACE('___(abc)___', '(abc)', '*replacement*'),
  REGEXP_REPLACE('___(abc)___', R'(abc)', '*replacement*'),
  -- '\(abc\)' とは書けない
  REGEXP_REPLACE('___(abc)___', R'\(abc\)', '*replacement*'),

日時系のリテラル #

日時形式の文字列リテラルの前に DATETIME とか DATE って置けば良い。

暗黙的な型変換 #

関数の期待する型と異なるデータ型のリテラルを指定した場合は暗黙的に型変換がなされる。

SELECT FORMAT_DATE('%b %Y', '2021-02-03')

以下はエラーとなる。

SELECT
  -- FORMAT_DATE(STRING, DATE) を期待しており型不一致でエラー
  FORMAT_DATE('%b %Y', val)
FROM
  UNNEST(['2021-02-03']) AS val

2項演算で異なるデータ型の場合も実際は暗黙的な型変換が発生している。

SELECT DATE '2021-02-03' > '2021-02-02'

キャスト 明示的な型変換 #

CAST 関数を使うことで明示的な型変換が可能。変換できない時は実行時エラーとなる。

CAST([変換する値] AS [変換後のデータ型])

SELECT
  FORMAT_DATE('%b %Y', CAST(val AS DATE))
FROM
  UNNEST(['2021-02-03']) AS val

配列型 #

[ と ] で配列型のリテラルが記述できる。

SELECT [1,2,3]

配列型の個々の要素にアクセスするには OFFSET(i) もしくは ORDINAL(n) を利用する。

SELECT
  [1,2,3][OFFSET(0)],
  [1,2,3][OFFSET(1)],
  [1,2,3][OFFSET(2)],
  [1,2,3][ORDINAL(1)],
  [1,2,3][ORDINAL(2)],
  [1,2,3][ORDINAL(3)],

範囲外の要素にアクセスすると out of bounds エラー

構造体型 #

( と ) でグルーピングすると構造体型のリテラルが記述できる。 構造体は key と value を持つ連想配列や辞書のようなもの。

SELECT (1, 'string')

ただし上記の書き方（タプル構文）だと匿名フィールドとなり個々の値へアクセスはできない。 まとめて値を比較する時には使える。

SELECT
  val,
  val = (1, 'a'),
FROM
  UNNEST([(1, 'a'), (2, 'b'), (3, 'c')]) AS val

型を明示的に指定する場合はフィールド名を指定できる。ネストした値へのアクセスは . ドットを利用する。

SELECT
  val.num,
  val.str,
FROM
  UNNEST(ARRAY<STRUCT<num INT64, str STRING>>[(1, 'a'), (2, 'b'), (3, 'c')]) AS val

サブクエリで SELECT AS STRUCT を宣言すれば STRUCT を返すサブクエリが書けるので、この例の方が直感的か。

SELECT
  val,
  val.num,
  val.str,
FROM
  (SELECT AS STRUCT 1 AS num, 'a' AS str) AS val

やっぱ直感的ではないか。

NULL が含まれる構造体型の等値比較 #

結果は NULL となる。ただし比較は先頭から順に行われ NULL 値の比較前に false になる場合は false となる。

SELECT
  val = (1, NULL),
FROM
  UNNEST(ARRAY<STRUCT<num INT64, str STRING>>[(1, 'a'), (2, NULL), (1, NULL)]) AS val

結果は以下の通り。

null
false
null