Add support for DATE, TIME, and DATETIME schema types

[esheffield]

BigQuery allows creation of tables with fields of type DATE, TIME, and DATETIME. However the Java client does not recognize these types. So for example, executing a load job to import JSON into a table an existing table with one of these field types in the schema will fail.

Schema:

    ItemDate:DATE,Description:STRING

JSON:

{"ItemDate": "2016-11-01", "Description": "Item 1"}
{"ItemDate": "2016-11-02", "Description": "Item 2"}
{"ItemDate": "2016-11-03", "Description": "Item 3"}
{"ItemDate": "2016-11-04", "Description": "Item 4"}

Create job: (Example from Scala repl but using the Java client)

val tableId = TableId.of("instant-bonbon-111304", "TestData", "Date_Test")
val uris = new java.util.ArrayList[String]()
uris.add("gs://ih_test_v1/date_test/date_test.json")

val jobConf = LoadJobConfiguration.newBuilder(tableId, uris).
  setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND).
  setFormatOptions(FormatOptions.json).
  build

val jobInfo = JobInfo.of(jobConf)

var job : Job = null

try {
  job = bigquery.create(jobInfo)
} catch {
  case e: BigQueryException => println(e)
}

Error:

java.lang.IllegalArgumentException: No enum constant com.google.cloud.bigquery.Field.Type.Value.DATE
        at java.lang.Enum.valueOf(Enum.java:238)
        at com.google.cloud.bigquery.Field$Type$Value.valueOf(Field.java:75)
        at com.google.cloud.bigquery.Field.fromPb(Field.java:477)
        at com.google.cloud.bigquery.Field$1.apply(Field.java:46)
        at com.google.cloud.bigquery.Field$1.apply(Field.java:43)
        at com.google.common.collect.Lists$TransformingRandomAccessList.get(Lists.java:451)
        at java.util.AbstractList$Itr.next(AbstractList.java:358)
        at java.util.AbstractCollection.toArray(AbstractCollection.java:141)
        at java.util.ArrayList.<init>(ArrayList.java:177)
        at com.google.common.collect.Lists.newArrayList(Lists.java:119)
        at com.google.cloud.bigquery.Schema$Builder.setFields(Schema.java:86)
        at com.google.cloud.bigquery.Schema.of(Schema.java:189)
        at com.google.cloud.bigquery.Schema.fromPb(Schema.java:200)
        at com.google.cloud.bigquery.LoadJobConfiguration$Builder.<init>(LoadJobConfiguration.java:117)
        at com.google.cloud.bigquery.LoadJobConfiguration$Builder.<init>(LoadJobConfiguration.java:48)
        at com.google.cloud.bigquery.LoadJobConfiguration.fromPb(LoadJobConfiguration.java:553)
        at com.google.cloud.bigquery.JobConfiguration.fromPb(JobConfiguration.java:145)
        at com.google.cloud.bigquery.JobInfo$BuilderImpl.<init>(JobInfo.java:182)
        at com.google.cloud.bigquery.Job.fromPb(Job.java:324)
        at com.google.cloud.bigquery.BigQueryImpl.create(BigQueryImpl.java:229)
        ...

[mziccard]

@esheffield you are right. We'll try to provide support for this ASAP.

[bluecmd]

Yes please, this is something we need. Is there a workaround for this if we want to use the new API with DATE/DATETIME columns?

[garrettjonesgoogle]

I rolled this into #1451 .

[garrettjonesgoogle]

0.8.0 has been released, so I'm going to resolve this now.

[AndySantaCruzAnalytics]

I am a bigQuery newbie. Not sure if this is a bug or not.

My schema type is 'date'. The FieldValue class does not have a getDate() function. I did some hacking. Looks like you get back a string that I have to parse. the test database table was created by uploading a csv file and using auto detect

I expected a getType() for every schema type big query supports?

    for (FieldValueList row : result.iterateAll()) {
      // String type
      String ownerId = row.get("ownerId").getStringValue();
      FieldValue aedwipWhatIsType = row.get("date");
      Object lastRecordDate = row.get("date").getValue();
      logger.info("hack lastRecordDate " + lastRecordDate.getClass().getCanonicalName()  + " " + lastRecordDate.toString());
      String subscriptionId = row.get("subscriptionId").getStringValue();

      String msg = "BIGQuery test  " + " ownerId: " + ownerId + " subscriptionId" + subscriptionId
          + "lastRecordDate" + lastRecordDate;

      logger.info(msg);
    }

INFO: hack lastRecordDate java.lang.String 2022-04-17
Jun 26, 2022 7:39:20 PM com.stx.webapi.BQTestServlet aedwipBiqQueryTest
INFO: BIGQuery test  ownerId: 7Q7309QG78 subscriptionId: 12yz lastRecord. Date:2022-04-17

my gradle dependencies are

  // Google Cloud Storage Client Library
  // https://cloud.google.com/storage/docs/reference/libraries#client-libraries-install-java
  implementation platform('com.google.cloud:libraries-bom:25.4.0')
  implementation 'com.google.cloud:google-cloud-storage'  
  
  //https://cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries#client-libraries-install-java
  implementation 'com.google.cloud:google-cloud-bigquery:2.13.6'

Kind regards

Andy