BigQuery: Select REPEATED fields as REPEATED fields

1
votes

I'm loading data into BigQuery via Avro, one of my columns is an Array. That appears to load ok, however, because BigQuery appends root as a prefix to the column names, I need to select all the columns into their appropriate names. I have included the --noflatten_results flag but the query fails with "Cannot create valid output schema for field order_ids. Try renaming order_ids to root.order_ids in the outermost SELECT"

Query:

SELECT
root.a AS a,
root.b AS b,
root.orders_ids AS order_ids,
root.c AS c,
root.d AS d 
FROM dataset.table
1
google-bigquery

1 Answers

2
votes

Removing a parent record from a repeated field is difficult using legacy SQL. --noflatten_results is best-effort and doesn't support many transformations over repeated fields, such as renaming.

If you use standard SQL, it should be fairly simple. Results from standard SQL queries are unflattened by default, and referencing fields within records in a SELECT clause removes them from parent records.

SELECT root.* from dataset.table;

Using the CLI, you can specify to use standard SQL by adding --nouse_legacy_sql to your command line.

Related documentation: