Ad

Why Would BigQuery Suddenly Throw Data Format Error

- 1 answer

The following query has suddenly stopped working:

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_*`

The error is as follows:

bad int64 value:

The query has been working fine for months and something happened today to cause this error. Did anyone have the same issue? Is it likely the issue is in our data? Could it be a temporary issue on the GCP side? Thanks!

Ad

Answer

use safe_cast() instead 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
Ad
source: stackoverflow.com
Ad