CODEWITHSUNDEEP
sqlgoogle-bigquery
F.D
F.D

Reputation: 837

Cast in Google BigQuery not appropriate?

I have a #StandardSQL query 

 SELECT
    CAST(created_utc AS STRING),
    author,
 FROM
    `table`
 WHERE
   something = "Something"

which gives me the following error,

Error: Cannot read field 'created_utc' of type STRING as INT64

An example of created_utc is 1517360483

If I understand that error, which I clearly don't. created_utc is stored a string, but the query is trying unsuccessfully to convert it to a INT64. I would have hoped the CAST function would enforce it to be kept as a string.

What have I done wrong?

Upvotes: 0

Views: 2218

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

As Elliott has already pointed - some of your values are actually cannot be casted to INT64 because they are not represented integers and rather have some other characters than digits

Using below SELECT you can identify such values so it will help you to locate problematic entries and make then decision on next actions 

#standardSQL
SELECT created_utc, author
FROM `table`
WHERE something = "Something"
  AND NOT REGEXP_CONTAINS(created_utc , r'[0-9]')

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33755

The problem is that you don't actually have a single table. In your question, you wrote table, but I suspect that you are querying table*, which matches multiple tables where one of them happens to have a different type for that column. Instead of using table*, your options are to:

  • Use UNION ALL with the individual tables, preforming casts as appropriate in the SELECT lists.
  • If you know which table(s) have that column as an INT64 instead of a STRING, and you are okay with excluding them, you can use a filter on _TABLE_SUFFIX to skip reading from certain tables.

Upvotes: 1

Related Questions