BigQuery - 将查询结果导出到本地文件/Google 存储 [英] BigQuery - Export query results to local file/Google storage

问题描述

我想将查询结果从 BigQuery 导出到本地文件/Google 存储.

I want to export query results from BigQuery to local file/Google storage.

我尝试了 'bq extract' 命令，但它不允许查询作为输入.

I tried 'bq extract' command but it doesn't allow query as input.

Usage: bq extract <source_table> <destination_uris>

我不想提取整个表，因为该表包含许多不需要的列，我需要聚合数据.

I don't want to extract the whole table as the table contains many columns which are not required and I need to aggregate the data.

到目前为止，我能找到的唯一解决方法是使用bq query"命令创建一个表并使用bq extract"来提取数据.

As of now, the only workaround I could find is to create a table using the 'bq query' command and use the 'bq extract' to extract the data.

我正在寻找通过执行以下操作来实现这一目标的更好方法.

I'm looking for any better way to achieve this by doing something like below.

bq extract 'select dept_id,sum(sal) from temp.employee 
                                 group by dept_id' 'gs://XXXX/employee.csv'

推荐答案

最近添加了从 BigQuery Standard SQL 的直接导出:将数据导出为csv格式

Direct export from BigQuery Standard SQL was added recently: Exporting data to csv format

EXPORT DATA OPTIONS(
  uri='gs://mybucket/myfolder2/*.csv',
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=';') AS
SELECT 1 as field1, 2 as field2 

这篇关于BigQuery - 将查询结果导出到本地文件/Google 存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！