Clone BigQuery Project to another account

Question

Due to a change in the business I need to copy a whole BigQuery project from one account to another, also, the accounts are not related and is not possible to link it in any way.

Throughout the CLI I was able to export a table to Cloud Storage in a dataset. Also, list tables in a dataset looks possible so loop over it shouldn't be a problem.

But I can't find any suitable way to manage the datasets neither for exporting or creating in the new account so it left a lot of manual task.

I'm missing something? There is a way to export the whole project with all datasets or a manual task will be always required?

The data structure is not complex at all:

     Project -> dataset -> table
                        -> table
                        -> ...

             -> dataset -> table
                        -> table
                        -> ...
             -> ...

If the answer was useful, consider accepting and upvoting it. You can accept it clicking on the ✓ and upvote it clicking on the ▲ — rmesteves

rmesteves rmesteves · Accepted Answer · 2020-05-15T13:53:27

You can't copy the whole project at once but you can try to automate the copy using a script in Python like this:

from google.cloud import bigquery
import os

source_project = "<your source project>"
new_project = "<your new project>"

#I suppose that you have access to the source project in your new project
client = bigquery.Client(project=source_project)

datasets = []

#List all the datasets in the source project and save it in a list
for i in client.list_datasets():    
     datasets.append(i.dataset_id)

#For all the datasets, build the commands and then execute them
for i in datasets:
    create_command = "bq mk -d " + i
    copy_command = "bq mk --transfer_config --project_id=" + new_project + " --data_source=cross_region_copy --target_dataset=" +  i + " --display_name='My Dataset Copy' --params='{\"source_dataset_id\":\"" + i + "\",\"source_project_id\":\"" + source_project + "\",\"overwrite_destination_table\":\"true\"}'"
    os.system(create_command)
    os.system(copy_command)

Clone BigQuery Project to another account

3 Answers