標準 SQL の変換規則

「変換」にはキャスティングや強制型変換などがあります。ただし、それに限定されるわけではありません。

  • キャスティングは明示的変換であり、CAST() 関数を使用します。
  • 強制型変換は暗黙的変換であり、以下で説明されている状態の場合に BigQuery によって自動的に実行されます。
  • UNIX_DATE() などの独自の関数名を持つ、変換関数の 3 番目のグループがあります。

以下の表に、考えられるすべての CAST と、BigQuery データ型に対する強制型変換の可能性の要約を示します。「強制型変換後」は所定のデータ型(列など)のすべての式に適用されますが、リテラルとパラメータも強制型変換されます。詳細については、リテラル強制型変換パラメータ強制型変換をご覧ください。

元の型 キャスト先 強制型変換後
INT64 BOOL
INT64
NUMERIC
FLOAT64
STRING
NUMERIC
FLOAT64
NUMERIC INT64
NUMERIC
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
NUMERIC
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
NUMERIC
FLOAT64
STRING
BYTES
DATE
DATETIME
TIME
TIMESTAMP
 
BYTES STRING
BYTES
 
日付 STRING
DATE
DATETIME
TIMESTAMP
 
DATETIME STRING
DATE
DATETIME
TIME
TIMESTAMP
 
TIME STRING
TIME
 
TIMESTAMP STRING
DATE
DATETIME
TIME
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

キャスティング

構文:

CAST(expr AS typename)

キャスト構文は、式の結果の型を他の型に変換する必要があることを示す目的でクエリで使用されます。

例:

CAST(x=1 AS STRING)

これは、x1 の場合は "true"、その他の非 NULL 値の場合は "false"xNULL の場合は NULL になります。

サポートされる型の間でキャストするとき、元の値からターゲット ドメインに正常にマッピングされない場合は、ランタイム エラーが発生します。たとえば、バイト列が有効な UTF-8 ではない場合に BYTES を STRING キャスティングするとランタイム エラーが発生します。

次の型の x 式をキャスティングする場合、以下の規則が適用されます。

型変換の前 型変換の後 x をキャスティングする場合の規則
INT64 FLOAT64 近似値だが厳密には正確でない可能性のある FLOAT64 値を返します。
INT64 BOOL x0 の場合は FALSE、それ以外の場合は TRUE を返します。
NUMERIC 浮動小数点 NUMERIC は最も近い浮動小数点数に変換されますが、精度が低下する可能性があります。
FLOAT64 INT64 最も近い INT64 値を返します。
1.5 や -0.5 などの中間値の場合は、ゼロから離れるように丸められます。
FLOAT64 STRING 近似する文字列表現を返します。
FLOAT64 NUMERIC 浮動小数点数はゼロから遠ざかるように丸められます。NaN+inf-inf をキャストするとエラーが返されます。NUMERIC の範囲外の値をキャストすると、オーバーフロー エラーが返されます。
BOOL INT64 xTRUE の場合は 1、それ以外の場合は 0 を返します。
BOOL STRING xTRUE の場合は "true"、それ以外の場合は "false" を返します。
STRING FLOAT64 有効な FLOAT64 リテラルと同じ形式を持つと解釈して、x を FLOAT64 値として返します。
また、"inf""+inf""-inf""nan" からのキャストをサポートします。
変換では大文字と小文字を区別しません。
STRING NUMERIC STRING に含まれる数値リテラルは、NUMERIC 型の最大精度や範囲を超えてはなりません。超えた場合はエラーが発生します。小数点以下が 10 桁以上の場合、キャスト後の NUMERIC 値は小数点以下が 9 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。
STRING BOOL x"true" の場合は TRUE を、x"false" の場合は FALSE を返します。
x のそれ以外の値はすべて無効であり、BOOL にキャスティングせずにエラーをスローします。
BOOL に変換するときに、STRING では大文字と小文字が区別されません。
STRING BYTES STRING は UTF-8 エンコーディングを使用して BYTES にキャストされます。たとえば、STRING "©" は BYTES にキャストされると、16 進数 C2 と A9 を持つ 2 バイト数列になります。
BYTES STRING UTF-8 STRING と解釈される x を返します。
たとえば BYTES リテラル b'\xc2\xa9' の場合、STRING にキャストすると UTF-8 として解釈され、Unicode 文字「©」になります。
x が有効な UTF-8 でない場合、エラーが発生します。
ARRAY ARRAY 正確に同じ ARRAY 型になっている必要があります。
STRUCT STRUCT 次の条件が満たされる場合に許可されます。
  1. 2 つの STRUCT が同じ数のフィールドを持っている。
  2. 元の STRUCT フィールド型を、対応する目的の STRUCT のフィールドの型に(フィールド名ではなく、フィールド順序によって定義されるように)明示的にキャストできる。

Safe キャスティング

CAST を使用する場合、BigQuery がそのキャスティングを実行できなければクエリは失敗します。たとえば、次のクエリによってエラーが発生します。

SELECT CAST("apple" AS INT64) AS not_a_number;

このようなエラーからクエリを守るには、SAFE_CAST を使用できます。SAFE_CAST はエラーの代わりに NULL を返すという点を除き、CAST とまったく同じです。

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

バイトから文字列にキャストする場合は、SAFE_CONVERT_BYTES_TO_STRING 関数も使用できます。無効な UTF-8 文字列は Unicode 置換文字 U+FFFD に置き換えられます。詳しくは、SAFE_CONVERT_BYTES_TO_STRING をご覧ください。

16 進文字列から整数へのキャスティング

16 進数(0x123)を処理する場合、それらの文字列を整数としてキャストできます。

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+

SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123    | -291       |
+-----------+------------+

時間型のキャスティング

BigQuery では、文字列と時間型の間のキャスティングが次のようにサポートされています。

CAST(time_expression AS STRING)
CAST(string_expression AS TIME)

時間型から文字列へのキャスティングはタイムゾーンとは独立しており、HH:MM:SS の形式になります。文字列から時間にキャスティングする場合、文字列はサポートされる時間のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の時間を表している場合は、エラーが発生します。

日付型のキャスティング

BigQuery では、文字列と日付型の間のキャスティングが次のようにサポートされています。

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

データ型から文字列へのキャスティングはタイムゾーンとは独立しており、YYYY-MM-DD の形式になります。文字列から日付にキャスティングする場合、文字列はサポートされる日付のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の日付を表している場合は、エラーが発生します。

日時型のキャスティング

BigQuery では、文字列と日時型の間のキャスティングが次のようにサポートされています。

CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)

日時型から文字列へのキャスティングはタイムゾーンとは独立しており、YYYY-MM-DD HH:MM:SS の形式になります。文字列から日時型にキャスティングする場合、文字列はサポートされる日時型のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の日時を表している場合は、エラーが発生します。

タイムスタンプ型のキャスティング

BigQuery では、次のように文字列に対するタイムスタンプ型のキャスティングがサポートされています。

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

タイムスタンプから文字列にキャスティングするとき、タイムスタンプはデフォルトのタイムゾーンである UTC を使用して解釈されます。生成されるサブ秒の桁数は、サブ秒部分の末尾のゼロの数によって決まります。CAST 関数によって 0、3、または 6 桁に切り捨てられます。

文字列からタイムスタンプにキャスティングする場合、string_expression はサポートされるタイムスタンプのリテラル形式に従う必要があります。従わない場合、ランタイム エラーが発生します。string_expression 自体に time_zone が含まれる場合があります。詳細については、タイムゾーンをご覧ください。string_expression にタイムゾーンが含まれる場合、そのタイムゾーンが変換に使用されます。それ以外の場合、デフォルトのタイムゾーンである UTC が使用されます。文字列が 6 桁未満の場合、暗黙的に拡大されます。

string_expression が無効である、サブ秒が 6 桁よりも多い(マイクロ秒よりも精度が高い)、またはサポートされるタイムスタンプ範囲外にある時刻を表している場合、エラーが発生します。

日付型、日時型、タイムスタンプ型の間のキャスティング

変換ルールテーブルに示すように、BigQuery では、日付型、日時型、タイムスタンプ型の間のキャスティングがサポートされています。

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

日付からタイムスタンプへのキャスティングでは、date_expression はデフォルトのタイムゾーンである UTC の午前 0 時(1 日が開始する時刻)を基点として解釈されます。タイムスタンプから日付へのキャスティングでは、タイムスタンプはデフォルトのタイムゾーンを基点にして効率的に切り捨てられます。

CAST(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)

日時からタイムスタンプへのキャスティングでは、datetime_expression はデフォルトのタイムゾーンである UTC の午前 0 時(1 日が開始する時刻)を基点として解釈されます。

強制型変換

BigQuery は、関数のシグネチャを一致させる必要がある場合、式の結果の型を別の型に強制的に変換します。たとえば、関数 func() が型 INT64 の単一の引数を取るように定義され、式が FLOAT64 の結果の形式を持つ引数として使用される場合、その式の結果が INT64 型に強制変換されてから、func() が計算されます。

リテラル強制型変換

BigQuery では、次のリテラル強制型変換がサポートされています。

入力データ型 結果のデータ型 メモ
STRING リテラル DATE
DATETIME
TIME
TIMESTAMP

リテラル強制型変換は、実際のリテラル型が、対象の関数によって要求される型と異なる場合に必要です。たとえば、関数 func() が DATE 引数を取る場合、STRING リテラル "2014-09-27" は DATE に強制変換されるため、式 func("2014-09-27") は有効です。

リテラル変換は解析時に評価され、入力リテラルを目的の型に正常に変換できない場合、エラーが発生します。

注: 文字列リテラルが数値型に強制変換されることはありません。

パラメータ強制型変換

BigQuery では、次のパラメータ強制型変換がサポートされています。

入力データ型 結果のデータ型
STRING パラメータ

パラメータ値を目的の型に正常に強制変換できない場合は、エラーが発生します。

その他の変換関数

BigQuery では、次の変換関数も使用できます。