Search code examples
csvgoogle-bigquerytalend

Google BigQuery doesn't parse nullable 2nd timestamp field

I'm trying to import a large amount of data into a flat table using CSV. During the import BigQuery reports:

- File: 0 / Line:6659 / Field:11: Could not parse '' as a timestamp

In the schema I have 2 timestamps: fields 4 and 11 . Both are nullable. For the second timestamp the row entry on field 11 is indeed null.

Is it supposed to work like this or do I need to send it as "0" ?

Solution

  • Note the difference between a null value and an empty string. Line 6659 copied above has empty strings, but not nulls.

    To reproduce, I created a table with this schema:

    [{"name":"a","type":"TIMESTAMP","mode":"NULLABLE"},
 {"name":"b","type":"TIMESTAMP","mode":"NULLABLE"}]

    Then uploaded this file to it:

    "",""

    Where I got this error:

    File: 0 / Line:1 / Field:1, Could not parse '' as a timestamp

    But if I upload this file:

    ,

    Everything works fine, as there are 2 nulls surrounding that comma.

    You can either transform all your files, getting rid of the empty strings, or import those columns to BigQuery as string, and run an ETL from string to timestamp inside BigQuery.