Does Google BigQuery supports ARRAY<STRING>?

Question

I am pushing the data from Google dataflow to Google BigQuery. I have TableRow object with data in it. One of columns in TableRow does contain Array of String.

From here, I found that Google BigQuery supports Array column type. So I tried to create table with ARRAY<SCHEMA> as type. But I got the below error

com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Invalid value for: ARRAY<STRING> is not a valid value",
    "reason" : "invalid"
  } ],
  "message" : "Invalid value for: ARRAY<STRING> is not a valid value"
}
com.google.cloud.dataflow.sdk.util.UserCodeException.wrapIf(UserCodeException.java:47)
com.google.cloud.dataflow.sdk.util.DoFnRunnerBase.wrapUserCodeException(DoFnRunnerBase.java:369)
com.google.cloud.dataflow.sdk.util.DoFnRunnerBase.finishBundle(DoFnRunnerBase.java:162)
com.google.cloud.dataflow.sdk.runners.worker.SimpleParDoFn.finishBundle(SimpleParDoFn.java:194)
com.google.cloud.dataflow.sdk.runners.worker.ForwardingParDoFn.finishBundle(ForwardingParDoFn.java:47)

Here is the code that I use to publish values into BigQuery

    .apply(BigQueryIO.Write.named("Write enriched data")
               .withCreateDisposition(BigQueryIO.Write.CreateDisposition.CREATE_IF_NEEDED)
               .withSchema(getSchema())
               .withWriteDisposition(BigQueryIO.Write.WriteDisposition.WRITE_APPEND)
               .to("table_name"));

And here is the schema construction

private static TableSchema getSchema() {
    List<TableFieldSchema> fields = new ArrayList<>();

    fields.add(new TableFieldSchema().setName("column1").setType("STRING"));
    fields.add(new TableFieldSchema().setName("column2").setType("STRING"));
    fields.add(new TableFieldSchema().setName("array_column").setType("ARRAY<STRING>"));

    return new TableSchema().setFields(fields);
}

How can I insert array of string into BigQuery table?

Willian Fuks Willian Fuks · Accepted Answer · 2017-06-03T17:27:15

To define a ARRAY<STRING> in BigQuery I set the field as 'STRING' and its mode as 'REPEATED'.

In Python for instance it's defined as field = SchemaField(name='field_1', type='STRING', mode='REPEATED')

For the Java client for what I could see you have the same options, you could define the TYPE as STRING and the MODE as REPEATED.

Does Google BigQuery supports ARRAY<STRING>?

1 Answers