BigQuery extract table schema

0
votes

There is a huge nested (structured) table.

The need:

Create identical table with same schema and data on other project other region. Copying is not working as different regions.

Have been trying:

Export table to bucket (JSON as AVRO not allows edit schema once loading data), then extract old table schema and create a new table from bucket, providing schema. As typing schema manually is not possible because of structured table schema size and complexity.

Errors get:

Once creating a table, AVRO auto schema detect is not recognizing some data types correctly, JSON fails with multiple errors.

The question:

How to export table schema, ideally in JSON? Once I run this in CLI:

bq show --format=prettyjson Project1:BQDataset.Table1 | jq '.schema.fields'

I get a massive (hundreds of rows) .json which is not feasible to copy at console by simple highlighting text in CLI result list. fragment of JSON result in CLI

Updated: The issue is solved and this initial approach is not correct.

1
google-bigquery
Look at your other post. I may have a solution for you – rmesteves

1 Answers

1
votes

You can do the following to save the results in a file:

bq show --format=prettyjson Project1:BQDataset.Table1 | jq '.schema.fields > schema.json

Than you can use the following to copy the file to a bucket:

gsutil cp /path/to/file/schema.json gs://bucket/path/

Anyway, if its more suitable for you, you can copy your entire dataset between regions by doing

bq mk --transfer_config --project_id=myproject --data_source=cross_region_copy --target_dataset=123_demo_tokyo --display_name='My Dataset Copy' --params='{"source_dataset_id":"123_demo_eu","source_project_id":"mysourceproject","overwrite_destination_table":"true"}'

You can check the complete reference here