Invalid DateTime error while trying to insert datetime value into BigQuery from Dataflow

Question

We wrote a Google Data Flow code that inserts a value into a bigquery table whose column is of type DateTime. The logic was running fine most of the times. But suddenly we get Invalid DateTime issue.

Exception: java.lang.RuntimeException: java.io.IOException: Insert failed: [{"errors":[{"debugInfo":"generic::out_of_range: Invalid datetime string \"2017-09-26T21:16\"

It is unclear how and why the above value is invalid. We see that it is adhering to DateTime datatype mentioned in https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

Also it remains unclear why it is throwing this error only on occasions.

We wrote a custom Transformation code that extends DoFn The ProcessElement code goes like this

 public void processElement(ProcessContext c) throws Exception {

    TableRow tableRow = c.element();
    try {
       // do some processing then 
      tableRow.set("PredictedDate",**LocalDateTime.now().toString()**);
      c.output(tableRow);
    }catch(Exception exc){
        LOG.error("Exception while processing and hence not attempting to write to bigquery");
    }
} 

enter code here

It was working fine but fails occasionally during night times(US Central timezone). Can you please help us find the root cause.

Instead of `2017-09-26T21:16`, the string should be `2017-09-26T21:16:00`. — Elliott Brossard, Oct 04 '17 at 12:32

score 8 · Accepted Answer · answered Oct 04 '17 at 17:04

8

The format described for DateTime indicates that a seconds field is required.

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]]

Specifically, note that the second S is not enclosed in square brackets making it optional.

answered Oct 04 '17 at 17:04

Ben Chambers

6,070
11
16

Thanks. Also i was using LocalDateTime.now().toString() which seems to discard seconds when the seconds is zero.Used ISO formatter to set it right.Thanks for the help – Kaarthikraaj Ramanan Oct 05 '17 at 06:50
I had 7 subseconds 2018-04-22T18:15:54.9046289, used `CONVERT(DATETIME2(6), CreatedDate)` to get it down to 6 subseconds 2018-04-22T18:15:54.904629 – ono2012 Aug 27 '18 at 12:16
Too bad that ```DateTimeFormat.forPattern("YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]]")``` cannot handle the official BigQuery pattern. ```java.lang.IllegalArgumentException: Illegal pattern component: T``` – vdolez Sep 19 '18 at 15:09

score 4 · Answer 2 · answered Nov 06 '20 at 11:09

4

In my case I'm retrieving time in timestamp and make convertion to the string:

new Date(event_timestamp_ms).toISOString().slice(0, -1);

Since DATETIME doesn't contain information about timezone, if helps to remove Z symbol from UTC format.

answered Nov 06 '20 at 11:09

lyha

517
4
7

Invalid DateTime error while trying to insert datetime value into BigQuery from Dataflow

2 Answers2