Modify Columns Repeated Bigquery

Question

I have a table with columns repeated and not repeated.

{
"Id": "0",
"textField": [
  "value1",
  "value2",
  "value3"
],
"numericField": [
  "10"
],
"dateField": "2016-02-24 15:48:00 UTC"
}

Id              INTEGER     REQUIRED
textField       STRING      REPEATED
numericField    INTEGER     REPEATED
dateField       TIMESTAMP   NULLABLE

But the "textField" field must be REPEATED and the "numericField" field must be no REPEATED.

I tryed to copy the information into another table with the changed fields but this replaces my fields and let me fields REPEATED https://cloud.google.com/bigquery/docs/tables#copyingtable

I tryed to create a table from a query but I do not know how to define the query so that the "textField" field that is "REPEATED" does not change and the "numericField" becomes into not REPEATED. https://cloud.google.com/bigquery/docs/reference/v2/jobs#configuration.query.tableDefinitions

how do I change the field without losing information?

Danny Kitt Danny Kitt · Accepted Answer · 2016-02-25T20:30:20

You can do this with a query using the FLATTEN function and by turning off query result auto-flattening.

SELECT Id, textField, numericField, dateField 
FROM FLATTEN([dataset.table], numericField);

You can turn off auto-flattening of query results which will preserve the repeatedness of textField. In the query configuration, set allowLargeResults to true and flattenResults to false. Both of these options are available in the web UI or bq CLI by similar names.

Caveat: If you have multiple numericField values in a row, this will result in a cross-product expansion of rows (e.g. one output row per numericField value in each input row). This may or may not be a problem for you (your example row doesn't have multiple numericField values), and may or may not be what you're looking to do.

Modify Columns Repeated Bigquery

1 Answers