Recently, I needed to move parts of a complex project to a different physical location (a separate region). One of the requirements was to ensure that the end result was completely decoupled from the original project. As part of this process, I had to copy numerous schemas to a separate BigQuery project located in a different region.

The number of schemas was so large that a manual approach was not effective. To address this, I composed a set of scripts that utilize the bq command-line tool to perform these operations automatically.

The scripts I created greatly reduced the amount of manual work for me. As a result, I decided to publish them in a public repository so that others who need to perform similar tasks can use them. Here is the link to the repository: BigQuery schema copying tools

Usage scenarios for this toolkit:

Copying a single table to a dataset with the same name. The destination table will also retain the same name as the original one.

./import_schema.sh --source-project=denis_project --dataset=features --table=categories --dest-project=denis_project_asia --location=asia-southeast2

Copying a single table to a different project, dataset and region.

./import_schema.sh --source-project=denis_project --dataset=features --table=categories --dest-project=denis_project_asia --dest-dataset=new_dataset --location=asia-southeast2

Bulk copying a whole dataset. Copying every next table should be confirmed in an interactive mode.

./import_dataset.sh --source-project=denis_project --dataset=features --dest-project=denis_project_asia --location=asia-southeast2

Limitations

This toolkit does not copy views or materialized views. It works only with simple table schemas.