Google BigQuery doesn't parse nullable 2nd timestamp field

4
votes

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" ?

2
csvgoogle-bigquerytalend
I'm trying to reproduce, but on my simple tests this works. Can you share more information? (Paste row 6659 here and the table description) – Felipe Hoffa
@Fh. Schema too big to paste, but these are the first few fields: channel:string, type:string, kind:string, activity_datetime:timestamp, user_id:integer, user_country_iso:string, user_language_iso:string, user_segment:string, sale_id:integer, sale_name:string, sale_start_datetime:timestamp, sale_audience_id:integer, sale_audience_name:string, ... – Wouter
@Fh. and this is line 6659 "Web","View","Login","2013-10-01 06:31:50.0354","2861905","BE","nl-BE","","0","","","","","","","","","0","","","","","","","","","","","","","" ,"","" – Wouter
@Fh. I converted all null dates to the UNIX epoch for now, which works. But using an actual null is probably better – Wouter
I have tried sending NULL values for when I don't have a timestamp to bigquery, but I have failed. I tried Null, None, "", but I get this: ``` - File: 0 / Line:101 / Field:53: Could not parse 'NULL' as a timestamp. Required format is YYYY-MM-DD HH:MM[:SS[.SSSSSS]] ``` and 0000-00-00 00:00 results in 0-0-0 does not exist for timestamp: 0000-00-00 00:00 I'll probably switch to UNIX timestamp too :( – Caio Iglesias

2 Answers

2
votes

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.

1
votes

I had similar problem like this

The error was 

- File: 2 / Line:9940 / Field:13: Day 0-0-0 does not exist for
 timestamp: 0000-00-00 00:00:00

"9718470";"2769586jfghjkdf";"2015-08-03 15:57:28";"2015-08-03 16:10:10";"479";"128833308";"May 2015";"1461782";"ab_20150616";"1130567689";"69466";"emailid@hotmail.com";"0000-00-00 00:00:00";"2015-08-03 23:53:44"

The 13th field was TIMESTAMP in my BigQuery Schema, and I was sending "0000-00-00 00:00:00" . Big Query doesn't accept "0000-00-00 00:00:00".

To solve this problem I send

"9718470";"2769586jfghjkdf";"2015-08-03 15:57:28";"2015-08-03 16:10:10";"479";"128833308";"May 2015";"1461782";"ab_20150616";"1130567689";"69466";"emailid@hotmail.com";;"2015-08-03 23:53:44"

In short you have to send "someValue";;"someOtherValue" instead of "someValue";"0000-00-00 00:00:00";"someOtherValue or "someValue";"";"someOtherValue

Please note , I am using ; as delimiter.