Google BigQuery - bq 命令行工具快速入门

Google Cloud Platform

5.00/5 (1投票)

2014 年 12 月 1 日

CC (Attr 3U)

8分钟阅读

18872

本页面将通过一个 15 分钟的练习，介绍如何使用 bq 命令行工具运行查询、加载数据和导出数据。

必备组件
以交互模式运行 bq
检查表
运行查询
创建新表
后续步骤

必备组件

在开始本教程之前，您必须完成以下先决条件

使用 Google APIs Console 项目激活 BigQuery 服务
如果您已加入一个已启用 BigQuery 的现有 Google APIs Console 项目，则可以使用该项目，或者创建新项目。如果您创建新项目，还必须在该项目上开启结算功能，然后才能充分使用 BigQuery 的所有功能，具体操作请参阅注册流程。
安装 Google Cloud SDK
如果您尚未安装 SDK，请立即下载 Cloud SDK。

以交互模式运行 bq

您可以以交互模式运行 bq，此时无需在命令前加上“bq”前缀。要启动交互模式，请调用 bq shell。提示符是默认项目的 ID。要退出交互模式，请键入“exit”。

$ bq shell
Welcome to BigQuery! (Type help for more information.)
10000000021> ls
   datasetId
 -------------
  mydataset
10000000021> exit
Goodbye.

检查表

如果您已注册使用 BigQuery，则可以针对我们提供的任何示例表运行查询或检查其架构。我们将尝试针对 Shakespeare 表运行一些查询，该表包含莎士比亚所有戏剧中每个单词的条目。

要检查特定表的架构，请运行 bq show projectId:datasetId.tableId，其中项目和数据集 ID 如果是 bq 工具的默认值，则可以省略。以下示例检查 samples 数据集中的 shakespeare 表。

$ bq show publicdata:samples.shakespeare
    tableId      Last modified                  Schema
 ------------- ----------------- ------------------------------------
  shakespeare   01 Sep 13:46:28   |- word: string (required)
                                  |- word_count: integer (required)
                                  |- corpus: string (required)
                                  |- corpus_date: integer (required)

运行查询

让我们看看子字符串“raisin”在莎士比亚作品中出现了多少次。

要运行查询，请运行命令 bq query "query_string"，其中查询字符串必须用引号括起来，并遵循 BigQuery SQL 语法。请注意，查询字符串中的任何引号都必须用反斜杠 \ 进行转义，否则请使用与外部引号不同的引号类型（例如，“ 与 '）。

以下示例统计了子字符串“raisin”在莎士比亚所有作品中出现的次数。所示查询区分大小写；字符串比较区分大小写，除非您使用 SQL 功能 IGNORE CASE。

$ bq query "SELECT word, COUNT(word) as count FROM publicdata:samples.shakespeare WHERE word CONTAINS 'raisin' GROUP BY word"
Waiting on job_dcda37c0bbed4c669b04dfd567859b90 ... (0s) Current status: DONE
+---------------+-------+
|     word      | count |
+---------------+-------+
| Praising      |   4   |
| raising       |   5   |
| raisins       |   1   |
| praising      |   7   |
| dispraising   |   2   |
| dispraisingly |   1   |
+---------------+-------+

在下一组查询结果中，我们尝试从“word”列中选择“huzzah”，但由于没有匹配项，因此没有返回任何结果。

$ bq query "SELECT word FROM publicdata:samples.shakespeare WHERE word = 'huzzah' IGNORE CASE"
Waiting on job_e19 ... (4s) Current status: DONE$

创建新表

现在尝试创建自己的表。每个表都必须位于一个数据集内，数据集只是表的集合。数据集分配给单个项目。有关管理和填充表的详细信息，请参阅表。

步骤 1：创建数据文件

我们将使用美国政府每年编制的 1000 个最受欢迎的婴儿姓名文件。您可以从 Social Security Online 网站获取按年份压缩的文件集。下载国家数据文件。此压缩集合包含过去 120 年中每年姓名的逗号分隔文件 (CSV)。这些文件具有 BigQuery 的正确格式（请参阅 BigQuery 的 CSV 要求）。在本例中，我们将使用 2010 年姓名的文件。

在文本编辑器中打开文件以查看其内容；该文件是一个逗号分隔值 (CSV) 文件，包含以下三列：姓名、性别（“M”或“F”）以及具有该姓名的儿童数量。文件没有标题行。

（可选）将数据文件上传到 Google Cloud Storage

如果您希望从 Google Cloud Storage 加载数据而不是直接从计算机加载，则可以按以下方式将数据上传到 Google Cloud Storage。

为您的项目启用 Google Cloud Storage。 您会将数据文件上传到 Google Cloud Storage，然后从那里加载。要启用 Cloud Storage
1. 打开您 BigQuery 项目的 Google APIs Console
2. 在 **Services**（服务）窗格中启用 Cloud Storage。
3. 在 **Billing**（结算）窗格中启用结算。本练习中的示例表足够小，如果您在运行练习后禁用项目中的结算，则不应收取任何费用。
打开 Cloud Storage Manager。 从您项目的 Google APIs Console 中，打开 **Google Cloud Storage**（Google Cloud Storage）窗格，然后点击“Google Cloud Storage Manager”链接。
将您的文件上传到 Google Cloud Storage
1. 在 Cloud Storage Manager 中，创建一个全局唯一的存储桶名称，例如：bigquerybucket
2. 选择新存储桶，然后点击 **Upload**（上传）以上传您的文件。

按照其余步骤继续加载您的数据。

步骤 2：创建新数据集

您可以查看项目是否有可用的现有数据集，或者创建新数据集。尝试使用以下 bq 命令列出数据集，并可选地创建新数据集。

列出数据集

# Without modifiers, ls lists datasets in the default project:
$ bq ls
   datasetId
 -------------
  olddataset

# Provide the project ID to list datasets in a specific project
$ bq ls 292244645228:
  datasetId
 -----------
  workspace

# To get a list of all your projects, use the -p flag
$ bq ls -p
            projectId                  friendlyName
 ------------------------------- ------------------------
  588503209435                    Main Project
  38995755354                     Example Project
  292244645228                    My Project
  1099142187650                   Project with billing

创建新数据集

数据集名称最多可以为 1024 个字符，可以包含 A-Z、a-z、0-9 和下划线，但不能以数字或下划线开头，也不能包含空格。

$ bq mk mydataset
Dataset dataset '1099142187650:mydataset' successfully created.
$ bq ls
   datasetId
 -------------
  olddataset
  mydataset

步骤 3：上传表

bq load 命令在一个步骤中创建或更新表并加载数据。该命令的语法如下：

bq load [--source_format=NEWLINE_DELIMITED_JSON|CSV] destination_table data_source_uri table_schema

destination_table

要创建的表的完全限定名称。

--source_format

BigQuery 支持 CSV 和 JSON 文件。默认情况下，如果您不显式指定文件类型，BigQuery 会期望 CSV 文件。如果您上传 JSON 文件，则必须提供 --source_format=NEWLINE_DELIMITED_JSON 标志。您的源文件和架构也必须遵循正确的 JSON 结构。

data_source

用于填充表的源 CSV 数据文件。请注意，这可以是一个未压缩的本地文件，或者，也可以是引用未压缩或 gzip 压缩文件的、完全限定的 Google Cloud Storage URI。格式为 gs://bucket/file。例如，以下都是有效的文件类型：my_file.csv、gs://my_file.csv.gz 或 my_file.csv.gz。您可以使用逗号分隔的列表指定多个源 URI，如下所示。

gs://my_file2.csv,gs://my_file3.csv.gz

每个文件必须具有相同的架构，才能将其上传到单个目标表中。

注意：目前，无法将多个本地文件加载为数据源。您只能加载多个源 URI。

table_schema

要使用的表架构的描述。这可以是文件名，也可以是 column_name:datatype 对的逗号分隔列表。在本例中，我们将使用逗号分隔列表。尝试为您的表使用以下架构描述符：name:string,gender:string,count:integer，其中“name”、“gender”和“count”是分配给新表中列的标签。

如果愿意，您也可以在一个单独的文件中指定架构，并将该文件作为表架构提供。您的架构文件必须包含一个单一的数组对象，其中包含提供以下属性的条目：

"name"：列的名称
"type"：数据类型，例如 string
"mode"（可选）：此字段是否可以为 null

示例文档可能如下所示：

[
  {"name": "name", "type": "string", "mode": "required"},
  {"name": "gender", "type": "string", "mode": "nullable"},
  {"name": "count", "type": "integer", "mode": "required"}
]

运行以下命令将源文件加载到上述数据集中名为“names2010”的新表中。默认情况下，此操作是同步运行的，加载作业需要几秒钟才能完成。要了解如何异步运行 bq 命令，请参阅运行异步操作。

$ bq load mydataset.names2010 yob2010.txt name:string,gender:string,count:integer
Waiting on job_4f0c0878f6184119abfdae05f5194e65 ... (35s) Current status: DONE
$ bq ls mydataset
     tableId
 ---------------
  names2010

$ bq show mydataset.names2010
   tableId     Last modified         Schema
 ----------- ----------------- -------------------
  names2010   25 Oct 17:40:53   |- name: string
                                |- gender: string
                                |- count: integer

默认情况下，加载数据时，BigQuery 期望 UTF-8 编码的数据。如果您拥有 ISO-8859-1（或 Latin-1）编码的数据，并且在加载数据时遇到问题，可以通过使用 -E 标志明确告诉 BigQuery 将您的数据视为 Latin-1。

bq load -E ISO-8859-1 mydataset.names2010 gs://bigquerybucket/yob2010.txt name:string,gender:string,count:integer

有关更多信息，请参阅字符编码。

步骤 5：运行查询

现在您可以开始查询您的新表了。

# Most popular girls' names
$ bq query "SELECT name,count FROM mydataset.names2010 WHERE gender = 'F' ORDER BY count DESC LIMIT 5"
Waiting on job_58c0f5ca52764ef1902eba611b71c651 ... (0s) Current status: DONE
+----------+-------+
|   name   | COUNT |
+----------+-------+
| Isabella | 22731 |
| Sophia   | 20477 |
| Emma     | 17179 |
| Olivia   | 16860 |
| Ava      | 15300 |
+----------+-------+

# Most unusual boys' names
# Data does not include names with < 4 occurrences
$ bq query "SELECT name,count FROM mydataset.names2010 WHERE gender = 'M' ORDER BY count ASC LIMIT 5"
Waiting on job_556ba2e5aad340a7b2818c3e3280b7a3 ... (1s) Current status: DONE
+----------+-------+
|   name   | COUNT |
+----------+-------+
| Aarian   |     5 |
| Aaidan   |     5 |
| Aamarion |     5 |
| Aadhavan |     5 |
| Aaqib    |     5 |
+----------+-------+

字符编码

默认情况下，BigQuery 服务期望所有源数据都采用 UTF-8 编码。可选地，如果您有采用 ISO-8859-1 格式编码的 CSV 文件，则在导入数据时应明确指定编码，以便 BigQuery 在导入过程中能够正确地将您的数据转换为 UTF-8。目前，只能导入 ISO-8859-1 或 UTF-8 编码的数据。在指定数据字符编码时，请牢记以下几点：

如果您不指定编码，或者显式指定数据为 UTF-8 但随后提供的 CSV 文件不是 UTF-8 编码，BigQuery 将尝试将您的 CSV 文件转换为 UTF-8。
通常，您的数据将成功导入，但可能与您期望的字节不完全匹配。为避免这种情况，请指定正确的编码并重试导入。
分隔符必须编码为 ISO-8859-1。
通常，最佳做法是使用标准分隔符，例如制表符、竖线或逗号。
如果 BigQuery 无法转换字符，则该字符将被转换为标准的 Unicode 替换字符：�。

JSON 文件必须始终使用 UTF-8 编码。

要在使用命令行工具加载数据时指定编码，请使用 -E 标志，后跟编码类型。例如，以下命令指定文件为 ISO-8859-1 编码。

bq load -E ISO-8859-1 mydataset.mytable mydata.csv field1:string,field2:string

后续步骤

阅读 bq 用户指南，了解有关 bq 工具的更多信息。如果您计划使用 REST API，请查看 API 参考指南。
阅读开发者指南，详细了解使用 BigQuery 的相关信息。
查看 SQL 查询指南，了解 BigQuery SQL 方言的详细信息。

除非另有说明，本页的代码示例根据 Apache 2.0 许可证授权。