使用适用于 BigQuery 的 bq 命令行工具加载和查询数据

1. 简介

BigQuery 是 Google 推出的全托管式、无需运维、费用低廉的分析数据库。借助 BigQuery，您可以查询 TB 级的数据，而无需数据库管理员或基础设施。BigQuery 使用熟悉的 SQL，并采用“按实际用量付费”的收费模式。有了 BigQuery，您就可以专心分析数据，从中发掘有意义的数据洞见。在此 Codelab 中，您将使用 bq 命令行工具将本地 CSV 文件加载到新的 BigQuery 表中。

学习内容

  • 如何使用 BigQuery 的 bq 命令行工具
  • 如何将本地数据文件加载到 BigQuery 表中

所需条件

2. 进行设置

启用 BigQuery

如果您还没有 Google 账号，则必须先创建一个。

  1. 登录 Google Cloud 控制台，然后前往 BigQuery。您还可以在浏览器中输入以下网址，直接打开 BigQuery 网页界面。
https://console.cloud.google.com/bigquery
  1. 接受服务条款。
  2. 您必须先创建一个项目，然后才能使用 BigQuery。按照提示创建新项目。

选择项目名称，并记下项目 ID。1884405a64ce5765.png

项目 ID 是一个在所有 Google Cloud 项目中均保持唯一的名称。它稍后将在此 Codelab 中被称为 PROJECT_ID。

此 Codelab 使用的 BigQuery 资源在 BigQuery 沙盒限额范围内。不需要结算账号。如果您日后想移除沙盒限制，可以注册 Google Cloud 免费试用版，从而添加结算账号。

Cloud Shell

您将使用 Cloud Shell，它是在 Google Cloud 中运行的命令行环境。

激活 Cloud Shell

  1. 在 Cloud Console 中，点击激活 Cloud Shell4292cbf4971c9786.png。

bce75f34b2c53987.png

如果您以前从未启动过 Cloud Shell，将看到一个中间屏幕（非首屏），描述它是什么。如果是这种情况，请点击继续（您将永远不会再看到它）。一次性屏幕如下所示：

70f315d7b402b476.png

预配和连接到 Cloud Shell 只需花几分钟时间。

fbe3a0674c982259.png

这个虚拟机已加载了您需要的所有开发工具。它提供了一个持久的 5GB 主目录，并且在 Google Cloud 中运行，大大增强了网络性能和身份验证。只需使用一个浏览器或 Google Chromebook 即可完成本 Codelab 中的大部分（甚至全部）工作。

在连接到 Cloud Shell 后，您应该会看到自己已通过身份验证，并且相关项目已设置为您的项目 ID：

  1. 在 Cloud Shell 中运行以下命令以确认您已通过身份验证：
gcloud auth list

命令输出

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`
  1. 在 Cloud Shell 中运行以下命令，以确认 gcloud 命令了解您的项目：
gcloud config list project

命令输出

[core]
project = <PROJECT_ID>

如果不是上述结果，您可以使用以下命令进行设置：

gcloud config set project <PROJECT_ID>

命令输出

Updated property [core/project].

3. 创建数据集

创建一个数据集来包含您的表。

什么是数据集？

BigQuery 数据集是表的集合。数据集中的所有表都存储在同一数据位置。您还可以附加自定义访问权限控制，以限制对数据集及其表的访问权限。

创建数据集

在 Cloud Shell 中，使用 bq mk 命令创建名为“bq_load_codelab”的数据集。

bq mk bq_load_codelab

查看数据集属性

使用 bq show 命令查看数据集的属性，验证您是否已创建该数据集。

bq show bq_load_codelab

您应看到类似于以下内容的输出：

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. 创建数据文件

BigQuery 可以加载多种数据格式的数据，包括以换行符分隔的 JSON、Avro 和 CSV。为简单起见，您将使用 CSV。

创建 CSV 文件

在 Cloud Shell 中，创建一个空的 CSV 文件。

touch customer_transactions.csv

在 Cloud Shell 中运行 cloudshell edit 命令，以在代码编辑器中打开 CSV 文件。该命令将打开一个新浏览器窗口，其中包含代码编辑器和 Cloud Shell 面板。

cloudshell edit customer_transactions.csv

在代码编辑器中，输入一些以逗号分隔的值，以便加载到 BigQuery 中。

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

依次点击文件 > 编辑，以保存 CSV 文件。

5. 加载数据

使用 bq load 命令将 CSV 文件加载到 BigQuery 表中。

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

您使用了以下选项：

  • --source_format=CSV 在解析数据文件时使用 CSV 数据格式。
  • --skip_leading_rows=1 跳过 CSV 文件中的第一行，因为它是标题行。
  • Bq_load_codelab.customer_transactions—the first positional argument—用于定义应将数据加载到哪个表中。
  • ./customer_transactions.csv（第二个位置实参）用于定义要加载的文件。除了本地文件，bq load 命令还可以使用 gs://my_bucket/path/to/file URIs 从 Cloud Storage 加载文件。
  • 架构，可在 JSON 架构文件中定义，也可以以英文逗号分隔列表的形式定义。（为简单起见，您使用了逗号分隔列表。）

您在 customer_transactions 表中使用了以下架构：

  • Id:string：客户标识符
  • Zip:string：美国邮政邮政编码
  • Ttime:timestamp：交易发生的日期和时间
  • Amount:numeric：交易金额（数值列以十进制形式存储数据，这对于货币价值非常有用。）
  • Fdbk:float：反馈调查中针对交易的评分
  • Sku:string：所购商品的标识符

获取表格详细信息

通过显示表格属性来验证表格是否已加载。

bq show bq_load_codelab.customer_transactions

输出：

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. 查询数据

现在，您的数据已加载完毕，您可以使用 BigQuery 网页界面、bq 命令或 API 来查询数据。您可以针对您有读取权限的任何数据集（或数据集，只要它们位于同一位置）联接数据。

运行一个标准 SQL 查询，该查询会将您的数据集与 美国邮政编码数据集联接起来，并按美国州汇总交易。使用 bq query 命令执行查询。

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

该命令应输出类似以下内容：

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

您运行的查询使用了公共数据集和您的私有数据集。如需了解详情，请参阅同一查询的注释版本：

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. 清理

使用 bq rm 命令删除您创建的数据集。使用 -r 标志移除其中包含的所有表格。

bq rm -r bq_load_codelab

8. 恭喜！

您已将表格上传到 BigQuery 并查询了该表格！

所学内容

  • 使用 bq 命令行工具与 BigQuery 进行交互。
  • 使用 BigQuery 查询联接您的数据和公共数据集。

后续步骤

不妨查看以下内容了解详情：