Cannot write date in BigQuery using Java Bigquery Client API

Question

I'm doing some ETL from a CSV file in GCS to BQ, everything works fine, except for dates. The field name in my table is TEST_TIME and the type is DATE, so in the TableRow I tried passing a java.util.Date, a com.google.api.client.util.DateTime, a String, a Long value with the number of seconds, but none worked.
I got error messages like these: Could not convert non-string JSON value to DATE type. Field: TEST_TIME; Value: ...
When using DateTime I got this error: JSON object specified for non-record field: TEST_TIME.

//tableRow.set("TEST_TIME", date);
//tableRow.set("TEST_TIME", new DateTime(date));
//tableRow.set("TEST_TIME", date.getTime()/1000);
//tableRow.set("TEST_TIME", dateFormatter.format(date)); //e.g. 05/06/2016

Answer 1

I think that you're expected to pass a String in the format YYYY-MM-DD, which is similar to if you were using the REST API directly with JSON. Try this:

tableRow.set("TEST_TIME", "2017-04-06");

If that works, then you can convert the actual date that you have to that format and it should also work.

Answer 2

While working with google cloud dataflow, I used a wrapper from Google for timestamp - com.google.api.client.util.DateTime.

This worked for me while inserting rows into Big Query tables. So, instead of

tableRow.set("TEST_TIME" , "2017-04-07");

I would recommend

tableRow.set("TEST_TIME" , new DateTime(new Date()));

I find this to be a lot cleaner than passing timestamp as a string.