CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-cloud-storage
JDaniel
JDaniel

Reputation: 179

Clone BigQuery Project to another account

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
                        -> ...
             -> ...

Upvotes: 2

Views: 1799

Answers (3)

rmesteves
rmesteves

Reputation: 4085

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)

Upvotes: 4

Priya Agarwal
Priya Agarwal

Reputation: 512

To copy the dataset from one project to another project then you can use the below command to make the transfer job:

bq mk --transfer_config --project_id=[PROJECT_ID] --data_source=[DATA_SOURCE] --target_dataset=[DATASET] --display_name=[NAME] --params='[PARAMETERS]'

where PROJECT_ID : The destination project_ID DATA_SOURCE : cross_region_copy DATASET : Target dataset NAME : Display name of your job. PARAMETERS : Source project ID, Source Dataset ID and other parameteres can be defined( overwrite destination table etc.)

You can go through this link for detailed explanation.

Upvotes: 1

guillaume blaquiere
guillaume blaquiere

Reputation: 76018

You can use the Bigquery Data Transfer service for this. You can't copy all your project, but dataset per dataset. You can script this if you have a lot of dataset.

Be careful, you don't export from the source project to a target project, you import into the target project from the source project (I mean you have to define the transfert in the destination project)

Upvotes: 1

Related Questions