sql - 为什么BigQuery会突然抛出数据格式错误

Question

以下查询突然停止工作:

SELECT
  CAST((SELECT up.value.string_value FROM UNNEST(user_properties) up WHERE key = "user_id") AS INT64) AS user_id,
  (TIMESTAMP_MILLIS(CAST(event_timestamp/1000 AS INT64))) AS event_date
`firebase.dataset.events_*`

错误如下:

bad int64 value:

该查询几个月以来一直运行良好，今天发生了一些事情导致此错误。有没有人有同样的问题？问题可能出在我们的数据中吗？这可能是 GCP 方面的临时问题吗？谢谢!

Answer 1

使用 safe_cast() 代替强制转换

SELECT
  safe_cast((SELECT up.value.string_value FROM UNNEST(user_properties) up WHERE key = "user_id") AS INT64) AS user_id,
  (TIMESTAMP_MILLIS(CAST(event_timestamp/1000 AS INT64))) AS event_date