Exporting BigQuery table from one project to another

Question

I'm trying to copy a BigQuery table (Table1) stored within a Google Cloud Project (Project1) to another Google Cloud Project (Project2). The table is on the order of TBs. What's the best way to do this so that I don't have to export the table locally? Should I export the table from Project1 to Google Cloud Storage, and then to Project2? Or is there a better way?

Solution 1

Use bq command line tool to copy a table from one project to another. You can have a look at the following sample command

Source:

• projectid: 123456789123
• dataset: dataset1
• table: table1

Destination:

• projectid: 0987654321098
• dataset: dataset2
• table: table2

Command:

bq cp 123456789123:dataset1.table1 0987654321098:dataset2.table2

Solution 2

If Source and Destination in the same location - you can just use Copy Table even between different projects

Solution 3

If you are wondering 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.

Solution 4

A mass copy quick script using python

This uses the command @kanishka-mohaia gave above with the return format as json

Each each table of each dataset will be copied to the destination

This script doesn't take care of the creation of the datasets and tables

import json
import time
from subprocess import check_output

data_sets = json.loads(check_output("bq ls --format=json", shell=True).decode())

FROM_PROJECT = ''
TO_PROJECT = ''

for ds_item in data_sets:
    dataset = ds_item['datasetReference']['datasetId'].strip()

    tables = json.loads(
        check_output(f"bq ls --format=json {dataset}", shell=True).decode())

    for table_item in tables:
        table = table_item['tableReference']['tableId']
        print(f"Table = {dataset}.{table}")

        command = f'echo y | bq cp {FROM_PROJECT}:{dataset}.{table} ' \
                  f'{TO_PROJECT}:{dataset}.{table}'

        print(command)

        try:
            result = check_output(command, shell=True)

        except Exception as e:
            continue

        print(result)

        time.sleep(5)

View more solutions