16163

Exporting BigQuery table from one project to another

<h3>Question</h3>

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?


<h3>Answer1:</h3>

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:

<ul><li>projectid: <em>123456789123</em></li> <li>dataset: <em>dataset1</em></li> <li>table: <em>table1</em></li> </ul>

Destination:

<ul><li>projectid: <em>0987654321098</em></li> <li>dataset: <em>dataset2</em></li> <li>table: <em>table2</em></li> </ul>

Command:

bq cp 123456789123:dataset1.table1 0987654321098:dataset2.table2
<h3>Answer2:</h3>

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


<h3>Answer3:</h3>

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)
<h3>Answer4:</h3>

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.

来源:https://stackoverflow.com/questions/38922939/exporting-bigquery-table-from-one-project-to-another

Recommend

  • Exporting BigQuery table from one project to another
  • convert hexadecimal number string to double-precision number in java
  • Proguard Error: Unknown option '(' in argument number 12 | 鏄撳鏁欑▼
  • Manipulating variables to produce a new dataset in R
  • Output of ls | wc -l
  • How do I use getData() to plot a LineChart with ObjC PNChart library in Swift?
  • Mutating an item inside of nested loops
  • CKEditor 4 - how to add font family and font size controls to the toolbar
  • Mercurial gives “invalid pattern” error for simple GLOB syntax
  • Shadows built-in names “function” and “module” with PyCharm
  • hide column based on cell font color in a row vba
  • Reading a text file & adding the content to an ArrayList
  • Flask Blueprints: How to use them?
  • java.lang.ClassCastException: android.graphics.drawable.BitmapDrawable cannot be cast to android.gra
  • How to integrate Struts2 with Thymeleaf?
  • How to call a CUDA file from a C++ header file?
  • Samples, isosamples, and how they affect contour lines
  • How to set title name of the pdf. While viewing the Document(New Tab)
  • Eclipse ADT Plugin crashed after updating to version 22.0
  • magento 2 rest api : get cart items with images
  • get all files in git diff in intellij
  • Can't access web service when connected to the network :: HTTP 407
  • Oracle REF can't go through
  • Refresh JSF component after custom javascript Ajax call
  • How to get the Owner of the ContextMenu (from Silverlight 4 toolkit)?
  • css: column-count 3, image floating spanning 2, chrome not playing. why?
  • 'Edit' function for forum posts and such
  • Regex not working in java 1.5
  • How to check if a database and tables exist in sql server in a vb .net project?
  • How to create subsets of a single set of elements with XSLT?
  • Can I read another applications memory?
  • Rotating Towards Path in OpenGL
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • JQuery: Infinite input select
  • How to change user identity when git pushing via ssh?