使用适用于 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

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

此 Codelab 使用具有 BigQuery 沙盒限制的 BigQuery 资源。结算账号不是必需的。如果您以后想取消沙盒限制，可以通过注册 Google Cloud 免费试用来添加结算账号。

Cloud Shell

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

激活 Cloud Shell

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

bce75f34b2c53987.png

如果您以前从未启动过 Cloud Shell，系统会显示一个中间屏幕（非首屏）来介绍 Cloud Shell。如果是这种情况，请点击继续（此后您将不会再看到此通知）。一次性屏幕如下所示：

70f315d7b402b476

预配和连接到 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

运行 cloudshell edit 命令，以在 Cloud Shell 的代码编辑器中打开 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 查询联接数据与公共数据集。

后续步骤

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