CODEWITHSUNDEEP
google-bigquery
foxwendy
foxwendy

Reputation: 2929

Any feature in BigQuery that can migrate a whole dataset in another project w/o executing copy data?

While our project grows, at some point we realized that we need to create new projects and reorganize our dataset. One case is that we need to isolate one dataset from others into another new project. I know that I can do it by copying tables one by one through API and then delete the old ones. But when it comes to over a thousand of tables, it's really consumes a lot of time as the copying api is executed as a job and it takes time. Is it possible to just change reference(or path) of a dataset?

Follow up I tried copy tables using batch request. I got 200 OK in all request, but the tables just didn't get copied. I wonder why and how to get the real result. Here's my code:

    public async Task CopyTableToProjectInBatchAsync(IList<TableList.TablesData> fromTables, string toProjectId)
    {
        var request = new BatchRequest(BigQueryService);
        foreach (var tableData in fromTables)
        {
            string fromDataset = tableData.TableReference.DatasetId;
            string fromTableId = tableData.TableReference.TableId;
            Logger.Info("copying table {0}...",tableData.Id);
            request.Queue<JobReference>(CreateTableCopyRequest(fromDataset, fromTableId, toProjectId),
            (content, error, i, message) =>
            {
                Logger.Info("#content:\n" + content);
                Logger.Info("#error:\n" + error);
                Logger.Info("#i:\n" + i);
                Logger.Info("#message:\n" + message);
            });
        }
        await request.ExecuteAsync();
    }

   private IClientServiceRequest CreateTableCopyRequest(string fromDatasetId, string fromTableId, string toProjectId,
        string toDatasetId=null, string toTableId=null)
    {
        if (toDatasetId == null)
            toDatasetId = fromDatasetId;
        if (toTableId == null)
            toTableId = fromTableId;
        TableReference sourceTableReference = new TableReference
        {
            ProjectId = _account.ProjectId,
            DatasetId = fromDatasetId,
            TableId = fromTableId
        };
        TableReference targetTableReference = new TableReference
        {
            ProjectId = toProjectId,
            DatasetId = toDatasetId,
            TableId = toTableId
        };
        JobConfigurationTableCopy copyConfig = new JobConfigurationTableCopy
        {
            CreateDisposition = "WRITE_TRUNCATE",
            DestinationTable = targetTableReference,
            SourceTable = sourceTableReference
        };
        JobReference jobRef = new JobReference {JobId = GenerateJobID("copyTable"), ProjectId = _account.ProjectId};
        JobConfiguration jobConfig = new JobConfiguration {Copy = copyConfig};
        Job job = new Job {Configuration = jobConfig, JobReference = jobRef};

        return BigQueryService.Jobs.Insert(job, _account.ProjectId);
    }

Upvotes: 9

Views: 7508

Answers (4)

Jian He
Jian He

Reputation: 510

You can first copy BigQuery dataset to the new project, then delete the original dataset.

The copy dataset UI is similar to copy table. Just click "copy dataset" button from the source dataset, and specify the destination dataset in the pop-up form. See screenshot below. Check out the public documentation for more use cases.

Copy dataset button

enter image description here

Copy dataset form

enter image description here

Upvotes: 7

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

A short shell script which copies all tables from a dataset to another dataset:

export SOURCE_DATASET=$1  # project1:dataset
export DEST_PREFIX=$2  # project2:dataset2.any_prefix_
for f in `bq ls $SOURCE_DATASET |grep TABLE | awk '{print $1}'`
do
  export CP_COMMAND="bq cp $SOURCE_DATASET.$f $DEST_PREFIX$f"
  echo $CP_COMMAND
  echo `$CP_COMMAND`
done

Upvotes: 5

pingles
pingles

Reputation: 111

There's no built-in feature but I helped write a tool that we've open-sourced that will do this for you: https://github.com/uswitch/big-replicate.

It will let you synchronise/copy tables between projects or datasets (within the same project). Most of the details are in the project's README but for reference it looks a little like:

java -cp big-replicate-standalone.jar \
  uswitch.big_replicate.sync \
  --source-project source-project-id \
  --source-dataset 98909919 \
  --destination-project destination-project-id \
  --destination-dataset 98909919

You can set options that will control how many tables to copy, how many jobs run concurrently and where to store the intermediate data in Cloud Storage. The destination dataset must already exist but this means you'll be able to copy data between locations too (US, EU, Asia etc.).

Binaries are built on CircleCI and published to GitHub releases.

Upvotes: 6

Jeremy Condit
Jeremy Condit

Reputation: 7046

Nope, there's currently no move or rename operation in BigQuery. The best way to move your data is to copy it and delete the original.

Follow-up answer: Your batch request created the copy jobs, but you need to wait for them to complete and then observe the result. You can use the BigQuery web UI or run "bq ls -j" from the command line to see recent jobs.

Upvotes: 2

Related Questions