458文字
2分
編集

Q. BigQueryでクエリを実行するとNo matching signature for function IFNULLエラーが出る

#BigQuery

#エラーが発生する状況

リクエストに応じてクエリを生成して BigQuery にリクエストを投げるサービスで、次のようなクエリが生成される。

sql
MERGE INTO `dataset_id.table_id` AS T
USING (
    SELECT id, name, updated_at
    FROM UNNEST([STRUCT("123" AS id, NULL AS name, NULL AS updated_at)]) AS source_data
) AS S
ON T.id = S.id
WHEN MATCHED THEN UPDATE SET
    T.name = IFNULL(S.name, T.name),
    T.updated_at = IFNULL(S.updated_at, T.updated_at ),
WHEN NOT MATCHED THEN INSERT (id, name, updated_at)
    VALUES (S.id, S.name, S.updated_at)

ここで STRING 型や INTEGER 型でない TIMESTAMP 型や RECORD 型のカラムにデータを渡そうとすると、BigQuery 側で次のようなエラーが発生する。

txt
No matching signature for function IFNULL
  Argument types: INT64, TIMESTAMP
  Signature: IFNULL(T1, T1)
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {INT64, TIMESTAMP} at [0:0], invalidQuery

#IFNULL の仕様

BigQuery の IFNULL の仕様は次のようになっている。

  • 1 つ目の引数が NULL である場合、2 つ目の引数を返す。
  • 2 つの引数は同じデータ型に強制変換可能である必要がある。

強制変換可能かはConversion rules | BigQueryで確認できる。

#問題の原因

BigQuery では型を識別する方法がない場合、デフォルトの INT64 型が適用される。

つまり生成されるクエリでは NULL に対しての型の指定がなく INT64 型が適用されるため、INT64 と強制変換可能でないデータ型を操作しようとすると IFNULL でエラーが発生する。

#解決策

次のように NULL を本来のデータ型に明示的にキャストすることで回避できる。

sql
CAST(NULL AS TIMESTAMP) AS updated_at

全体のクエリは次のようになる。

sql
MERGE INTO `dataset_id.table_id` AS T
USING (
    SELECT id, name, updated_at
    FROM UNNEST([STRUCT("123" AS id, CAST(NULL AS STRING) AS name, CAST(NULL AS TIMESTAMP) AS updated_at)]) AS source_data
) AS S
ON T.id = S.id
WHEN MATCHED THEN UPDATE SET
    T.name = IFNULL(S.name, T.name),
    T.updated_at = IFNULL(S.updated_at, T.updated_at ),
WHEN NOT MATCHED THEN INSERT (id, name, updated_at)
    VALUES (S.id, S.name, S.updated_at)
編集