本文档介绍了如何写入或保存查询结果。
临时表和永久表
BigQuery 将所有查询结果保存到表中,该表可以是永久表,也可以是临时表:
BigQuery 使用临时表来缓存未写入永久表的查询结果。这些表在特殊数据集中创建并随机命名。您还可以创建临时表供自己来使用。如需了解详情,请参阅临时表。
查询完成后,临时表最多存在 24 小时。如需查看表的结构和数据,请转到 BigQuery 控制台,点击查询历史记录,然后选择创建临时表的查询。然后,在目标表行中,点击临时表。
您无法查询或共享临时表,也无法使用任何标准列表或其他表操作方法显示临时表。您不需要支付临时表的存储费用。
永久表可以是您有权访问的任何数据集中的新表或现有表。如果将查询结果写入新表,那么您就需要为存储数据支付费用。将查询结果写入永久表时,需要查询的表必须与目标表所在的数据集位于相同的位置。
所需权限
如需将查询结果写入表中,您必须至少具有以下权限:
bigquery.tables.create
权限:用于创建新表bigquery.tables.updateData
权限:用于将数据写入新表、覆盖表或将数据附加到表中bigquery.jobs.create
权限:用于运行查询作业
如需访问要查询的数据,您可能还需要具备其他权限(例如 bigquery.tables.getData
)。
以下预定义的 IAM 角色同时具有 bigquery.tables.create
和 bigquery.tables.updateData
权限:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
以下预定义的 IAM 角色包含 bigquery.jobs.create
权限:
bigquery.user
bigquery.jobUser
bigquery.admin
此外,如果用户具有 bigquery.datasets.create
权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner
访问权限。借助 bigquery.dataOwner
访问权限,用户可以在数据集内创建和更新表。
如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅预定义的角色和权限。
将查询结果写入永久表
将查询结果写入永久表时,您可以创建新表,将结果附加到现有表,或覆盖现有表。您可以通过以下方式将查询结果写入永久表:
- 使用 Cloud Console。
- 使用
bq
命令行工具的bq query
命令。 - 调用
jobs.insert
API 方法并配置query
作业。 - 使用客户端库。
写入查询结果
使用以下过程将查询结果写入永久表。为帮助控制费用,您可以在运行查询之前预览数据。
控制台
在 Cloud Console 中打开 BigQuery 页面。
在探索器面板中,展开您的项目并选择数据集。
如果查询编辑器处于隐藏状态,请点击窗口右上角的显示编辑器。
在查询编辑器文本区域中输入有效的 SQL 查询。
点击更多,然后选择查询选项。
勾选为查询结果设置目标表复选框。
在目标位置部分中,选择要在其中创建表的相应项目名称和数据集名称,然后选择表名称。
在目标表的写入设置部分,选择以下选项之一:
- 只写入空白表 - 仅在表为空时才将查询结果写入表。
- 附加到表 - 将查询结果附加到现有表。
- 覆盖表 - 使用查询结果覆盖名称相同的现有表。
(可选)在处理位置部分中,点击自动选择并选择您的位置。
点击运行查询。这会创建一个查询作业,并将查询结果写入您指定的表中。
或者,如果您在运行查询之前忘记指定目标表,可以点击编辑器下方的保存结果按钮,将缓存结果表复制到永久表。
SQL
通过数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表。
如需了解详情,请参阅 CREATE TABLE
语句页面和 CREATE TABLE
示例:从现有表创建新表。
bq
输入 bq query
命令,并指定 --destination_table
标志以根据查询结果创建永久表。指定 use_legacy_sql=false
标志可使用标准 SQL 语法。如需将查询结果写入非默认项目中的某个表,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset
。
(可选)提供 --location
标志并将其值设置为您的位置。
如需控制现有目标表的写入处置方式,请指定以下可选标志之一:
--append_table
:如果目标表存在,指定该标志可将查询结果附加到该表。--replace
:如果目标表存在,指定该标志可使用查询结果覆盖该表。
bq --location=location query \ --destination_table project_id:dataset.table \ --use_legacy_sql=false 'query'
请替换以下内容:
location
是用于处理查询的位置的名称。--location
标志是可选的。例如,如果您在东京地区使用 BigQuery,可将该标志的值设置为asia-northeast1
。您可以使用.bigqueryrc
文件设置位置的默认值。project_id
是项目 ID。dataset
是数据集名称,该数据集包含您要向其中写入查询结果的表。table
是您要向其中写入查询结果的表的名称。query
是使用标准 SQL 语法的查询。
如果未指定“写入处置方式”标志,则默认行为是仅在表为空时写入结果。如果表已存在且非空,则系统会返回以下错误:`BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1': Already
Exists: Table project_id:dataset.table
。
示例:
输入以下命令可将查询结果写入 mydataset
中名为 mytable
的目标表。该数据集在默认项目中。由于命令中未指定“写入处置方式”标志,因此该表必须为新表或空表。否则,系统将返回 Already exists
错误。该查询从美国名字数据公共数据集中检索数据。
bq query \ --destination_table mydataset.mytable \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
输入以下命令可使用查询结果覆盖 mydataset
中名为 mytable
的目标表。该数据集在默认项目中。该命令使用 --replace
标志覆盖目标表。
bq query \ --destination_table mydataset.mytable \ --replace \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
输入以下命令可将查询结果附加到 mydataset
中名为 mytable
的目标表。该数据集属于 my-other-project
,而非默认项目。该命令使用 --append_table
标志将查询结果附加到目标表。
bq query \ --append_table \ --use_legacy_sql=false \ --destination_table my-other-project:mydataset.mytable \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
各示例的输出如下所示。为了方便阅读,部分输出已被截断。
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
API
如需将查询结果保存到永久表中,请调用 jobs.insert
方法,配置 query
作业,并添加 destinationTable
属性的值。如需控制现有目标表的写入处置方式,请配置 writeDisposition
属性。
如需控制查询作业的处理位置,请在作业资源的 jobReference
部分中指定 location
属性。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。 如需了解详情,请参阅 BigQuery Java API 参考文档。
如需将查询结果保存到永久表中,请在 QueryJobConfiguration 中将目标表设置为所需的 TableId。
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
Python
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需将查询结果保存到永久表中,请创建 QueryJobConfig 并将目标设置为所需的 TableReference。然后,将作业配置传递给查询方法。写入大型查询结果
通常,查询都存在响应大小上限。如果您计划运行可能返回大型结果的查询,则可以执行以下任一操作:
- 在标准 SQL 中,为查询结果指定目标表。
- 在旧版 SQL 中,指定目标表并设置
allowLargeResults
选项。
为大型查询结果指定目标表时,您需要为存储数据支付费用。
限制
在旧版 SQL中,写入大型结果受到以下限制:
- 您必须指定目标表。
- 您不能指定顶级
ORDER BY
、TOP
或LIMIT
子句。如果指定这些子句,您就无法再并行计算查询输出,如此会抵消使用allowLargeResults
的好处。 - 窗口函数只有在与
PARTITION BY
子句配合使用时才能返回大型查询结果。
使用旧版 SQL 写入大型结果
如需使用旧版 SQL 写入大型结果集,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
点击编写新查询。
在查询编辑器文本区域中输入有效的 SQL 查询。请使用
#legacySQL
前缀,或务必在查询设置中勾选使用旧版 SQL。点击更多,然后选择查询设置。
在目标位置部分中,勾选为查询结果设置目标表。
在项目名称部分,选择将在其中创建目标表的项目。
在数据集名称部分,选择将存储表的数据集。
在表名称字段中,输入表名称。
如需将大型结果集写入现有表,您可以使用目标表的写入设置选项控制目标表的写入处置方式:
- Write if empty:仅在表为空时才将查询结果写入表。
- Append to table:将查询结果附加到现有表。
- 覆盖表:使用查询结果覆盖名称相同的现有表。
在结果大小部分,勾选允许大型结果(无大小限制)。
(可选)在处理位置部分,点击自动选择并选择数据的位置。
点击保存以更新查询设置。
点击运行。这会创建一个查询作业,并将大型结果集写入您指定的表中。
bq
搭配使用 --allow_large_results
标志与 --destination_table
标志,以创建用于存储大型结果集的目标表。由于 --
allow_large_results
选项仅适用于旧版 SQL,因此您还必须指定 --use_legacy_sql=true
标志。如需将查询结果写入非默认项目中的某个表,请按以下格式将相应项目 ID 添加到数据集名称中:PROJECT_ID:DATASET
。
提供 --location
标志并将其值设置为您的位置。
如需控制现有目标表的写入处置方式,请指定以下可选标志之一:
--append_table
:如果目标表存在,指定该标志可将查询结果附加到该表。--replace
:如果目标表存在,指定该标志可使用查询结果覆盖该表。
bq --location=location query \ --destination_table PROJECT_ID:DATASET.TABLE \ --use_legacy_sql=true \ --allow_large_results "QUERY"
请替换以下内容:
LOCATION
是用于处理查询的位置的名称。--location
标志是可选的。例如,如果您在东京地区使用 BigQuery,可将该标志的值设置为asia-northeast1
。您可以使用.bigqueryrc
文件设置位置的默认值。PROJECT_ID
是项目 ID。DATASET
是数据集名称,该数据集包含您要向其中写入查询结果的表。TABLE
是您要向其中写入查询结果的表的名称。QUERY
是使用旧版 SQL 语法的查询。
示例:
输入以下命令可将大型查询结果写入 mydataset
中名为 mytable
的目标表。该数据集属于默认项目。由于命令中未指定“写入处置方式”标志,因此该表必须为新表或空表。否则,系统将返回 Already exists
错误。该查询从美国名字数据公共数据集中检索数据。该查询仅用于举例。所返回的结果集不会超过响应大小上限。
bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
输入以下命令可使用大型查询结果覆盖 mydataset
中名为 mytable
的目标表。该数据集属于 myotherproject
,而非默认项目。该命令使用 --replace
标志覆盖目标表。
bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
输入以下命令可将大型查询结果附加到 mydataset
中名为 mytable
的目标表。该数据集属于 myotherproject
,而非默认项目。该命令使用 --append_table
标志将查询结果附加到目标表。
bq query \
--destination_table myotherproject:mydataset.mytable \
--append_table \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
API
如需将大型结果写入目标表中,请调用 jobs.insert
方法,配置 query
作业,并将 allowLargeResults
属性设置为 true
。
使用 destinationTable
属性指定目标表。如需控制现有目标表的写入处置方式,请配置 writeDisposition
属性。
在作业资源 jobReference
部分的 location
属性中指定您的位置。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
如需启用大型结果,请在 QueryJobConfiguration 中将允许大型结果设置为 true
,并将目标表设置为所需的 TableId。
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
Python
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
通过 Cloud Console 下载和保存查询结果
使用 Cloud Console 运行 SQL 查询后,您可以将结果保存到其他位置。您可以使用 Cloud Console 将查询结果下载到本地文件、表格或云端硬盘中。bq
命令行工具或 API 不支持将结果保存到本地文件、表格和云端硬盘。
限制
查询结果的下载和保存受到以下限制:
- 您只能以 CSV 或换行符分隔的 JSON 格式在本地下载查询结果。
- 不能以 CSV 格式下载包含嵌套和重复数据的查询结果。
- 不能将包含嵌套和重复数据的查询结果保存到 Google 表格中。
- 如需使用 Cloud Console 将查询结果保存到云端硬盘,结果集的大小不得超过 1 GB,且不超过 16000 行。如果结果大于这些限制,则可以改为保存到表中。
- 您只能以 CSV 或换行符分隔的 JSON 格式将查询结果保存到 Google 云端硬盘。
将查询结果下载到本地文件
bq
命令行工具或 API 不支持将查询结果下载到本地文件。
如需以 CSV 格式或换行符分隔的 JSON 文件格式下载查询结果,请使用 Cloud Console:
控制台
在 Cloud Console 中打开 BigQuery 页面。
点击编写新查询。
在查询编辑器文本区域中输入有效的 SQL 查询。
(可选)如需更改处理位置,请点击更多并选择查询设置。在处理位置下,点击自动选择并选择数据的位置。
点击运行。
返回结果后,点击保存结果,并选择要保存结果的格式/位置。
此时文件会下载到您浏览器的默认下载位置。
将查询结果保存到 Google 云端硬盘
bq
命令行工具或 API 不支持将查询结果保存到云端硬盘。
如需将查询结果保存到云端硬盘,请使用 Cloud Console:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器文本区域中输入有效的 SQL 查询。
点击运行。
返回结果后,点击保存结果。
选择 CSV(Google 云端硬盘)或 JSON(Google 云端硬盘)。将结果保存到 Google 云端硬盘时,您无法选择位置。结果始终会保存到根目录“My Drive”位置。
将结果保存到 Google 云端硬盘可能需要几分钟时间。结果保存后,您会收到一条包含下列文件名的弹出消息:
bq-results-[TIMESTAMP]-[RANDOM_CHARACTERS].[CSV or JSON]
。在弹出消息中,点击打开以打开该文件,或导航到 Google 云端硬盘并点击 My Drive。
将查询结果保存到 Google 表格
bq
命令行工具或 API 不支持将查询结果保存到 Google 表格。
如需将查询结果保存到表格,请使用 Cloud Console:
控制台
在 Cloud Console 中打开 BigQuery 页面。
点击编写新查询。
在查询编辑器文本区域中输入有效的 SQL 查询。
(可选)如需更改处理位置,请点击更多并选择查询设置。在处理位置下,点击自动选择并选择数据的位置。
点击运行。
返回结果后,点击保存结果,并选择 Google 表格。
如有必要,请按照系统提示登录到您的 Google 帐号,然后点击允许以授予 BigQuery 相应的权限,使其能够将数据写入您的 Google 云端硬盘的
MY Drive
文件夹。按照系统提示操作后,您应该会收到一封主题为“BigQuery 客户端工具已连接到您的 Google 帐号”(BigQuery Client Tools connected to your Google Account) 的电子邮件。这封电子邮件包含有关您已授予的权限的信息以及移除权限的步骤。
保存结果后,Cloud Console 中的查询结果下方会显示类似如下的消息:
Saved to Sheets as "results-20190225-103531. Open
。您可以点击此消息中的链接以在 Google 表格中查看您的结果,也可以导航到My Drive
文件夹并手动打开文件。当您将查询结果保存到 Google 表格时,文件名会以
results-[DATE]
开头,其中[DATE]
是今天的日期(格式为YYYYMMDD
)。