将10个数据集（每个数据集有80个表格）从bigquery导出到Google存储的有效方法？

问题描述

我在BigQuery中有10个数据集，每个数据集有80个表。我知道我可以使用控制台或Web UI将每个数据集中的每个表格逐个导出到Google存储中。这是为了备份的目的。但是，这将需要一段时间。

我想知道是否有更方便的方法来处理这个问题？使用 bq 命令行工具（）可以执行此操作。 href =https://cloud.google.com/bigquery/bq-command-line-tool =nofollow noreferrer> https://cloud.google.com/bigquery/bq-command-line-tool

以下代码可以帮助您达到同样的效果：

  b 
 
 $ get 
 
 
 
 
 
 
 
 
 
 
 
 
 $ = $（bq ls$ project：$ dataset| awk'{print $ 1}'| tail +3）
 
用于$表格中的表格
 do 
 bq extract  - -destination_formatCSV--compressionGZIP$ project：$ dataset。$ tablegs：//$bucket/$table/$table.csv.gz
 done 
  code> 

另外，如果你想在 async 模式下运行命令replace extract command与以下

  bq --nosync extract --destination_formatCSV--compressionGZIP$ project：$ dataset。 $ tablegs：// $ bu cket / $ table / $ table.csv.gz
  

PS：



• 确保已安装 bq 命令行工具并且它位于您的环境中。它带有 google云端SDK 。要验证您可以运行 bq help

I have 10 datasets in BigQuery and each dataset has 80 tables. I know I can export each table in each dataset one-by-one to google storage using console or Web UI. This is for back-up purposes. However, this would take a while.

I wonder if there is a more handy way to handle this?

解决方案

You can do it using bq command line tools (https://cloud.google.com/bigquery/bq-command-line-tool)

Following code will help you to achieve the same:

project="bigquery-project"
dataset="your-dataset"
bucket="your-bucket"

# get list of tables
tables=$(bq ls "$project:$dataset" | awk '{print $1}' | tail +3)

for table in $tables
do
    bq extract --destination_format "CSV" --compression "GZIP" "$project:$dataset.$table" "gs://$bucket/$table/$table.csv.gz"
done

Additionally, if you want to run command in async mode replace extract command with following

    bq --nosync extract --destination_format "CSV" --compression "GZIP" "$project:$dataset.$table" "gs://$bucket/$table/$table.csv.gz"

PS:

• Make sure that bq command line tools is installed and it is in your environment. It comes with google cloud SDK. To verify you can run bq help

这篇关于将10个数据集（每个数据集有80个表格）从bigquery导出到Google存储的有效方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！