CODEWITHSUNDEEP
google-bigquery
Sara
Sara

Reputation: 2769

Querying a BigQuery table with multiple nested records

I’m trying to copy a table’s schema to an empty table. It works for schemas with no nested records, but when I try to copy a schema with multiple nested records via this query:

SELECT * FROM [table] LIMIT 0

I get the following error:

Cannot output multiple independently repeated fields at the same time.

Upvotes: 2

Views: 315

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

SELECT * FROM [table] LIMIT 0 with Allow Large Results and Unflatten Results

The drawback of above approach is that user can end up with quite a bill – as this way of copying schema will cost the whole original table scan.

Instead I would programmatically get/acquire table schema and then create table with this schema

Upvotes: 2

Sara
Sara

Reputation: 2769

BigQuery will automatically flatten all results (see docs), which won't work when you have more than one nested record. In the BigQuery UI, click on Show Options:

enter image description here

Then select your destination table and make sure Allow Large Results is checked and Flatten Results is unchecked:

enter image description here

Upvotes: 3

Related Questions