快速入门:使用 bq 工具将数据加载到 BigQuery 中

使用 bq 工具将数据加载到 BigQuery 中

本快速入门介绍如何使用 bq 命令行工具运行查询以及将数据加载到 BigQuery 中。

准备工作

探索 BigQuery 之前,您必须登录 Cloud 控制台并创建项目。除非您选择附加结算帐号,否则可通过 BigQuery 沙盒免费探索 BigQuery。如需详细了解 BigQuery 沙盒,包括限制、如何添加结算帐号和升级项目以及问题排查,请参阅启用 BigQuery 沙盒

  1. 在 Google Cloud Console 中的项目选择器页面上,选择或创建一个 Google Cloud 项目

    转到“项目选择器”

  2. 可选:如果您选择现有项目,请确保启用 BigQuery API。新项目会自动启用 BigQuery API。
  3. 在 Cloud Console 中,激活 Cloud Shell。

    激活 Cloud Shell

    在本快速入门中,您将通过 Cloud Console 在 Cloud Shell 中运行所有 bq 工具命令。

运行查询

本部分介绍如何编写查询和获取帮助。

查看表

检查特定表的架构:

bq show PROJECT_ID:DATASET_ID.TABLE_ID

替换以下内容:

  • PROJECT_ID:您的项目 ID
  • DATASET_ID:您的数据集 ID
  • TABLE_ID:您的表 ID

如果项目 ID 和数据集 ID 是 bq 命令行工具的默认值,则可以省略项目 ID 和数据集 ID:

bq show TABLE_ID

BigQuery 提供了多个示例表,您可以对这些示例表执行查询操作。在本快速入门中,您将针对 shakespeare 表运行查询,该表包含每部莎士比亚戏剧中每个单词的条目。

检查 samples 数据集中的 shakespeare 表:

 bq show bigquery-public-data:samples.shakespeare

输出内容类似如下:

  Table bigquery-public-data:samples.shakespeare

     Last modified                  Schema                 Total Rows   Total Bytes   Expiration
   ----------------- ------------------------------------ ------------ ------------- ------------
    26 Aug 14:43:49   |- word: string (required)           164656       6432064
                      |- word_count: integer (required)
                      |- corpus: string (required)
                      |- corpus_date: integer (required)

查看 bq 工具帮助

bq 工具帮助提供了 bq 命令行工具可用的命令和参数列表。

  1. 查看有关 bq 工具的详细信息:

    bq help
  2. 查看有关特定命令的信息:

    bq help query

    在此示例中,调用 bq help 会检索有关 bq query 命令的信息。

搜索字符串

使用 bq query 命令,您可以对数据运行 SQL 查询。

  1. 使用 bq query 命令运行查询,以查看子字符串 raisin 在莎士比亚作品集中出现的次数:

    bq query --use_legacy_sql=false \
    'SELECT
      word,
      SUM(word_count) AS count
    FROM
      `bigquery-public-data`.samples.shakespeare
    WHERE
      word LIKE "%raisin%"
    GROUP BY
      word'
    

    输出类似于以下内容:

    Waiting on job_dcda37c0bbed4c669b04dfd567859b90 ... (0s) Current status:
    DONE
    +---------------+-------+
    |     word      | count |
    +---------------+-------+
    | Praising      |   4   |
    | raising       |   5   |
    | raisins       |   1   |
    | praising      |   8   |
    | dispraising   |   2   |
    | dispraisingly |   1   |
    +---------------+-------+
    

  2. 使用 bq query 命令运行查询,以查看子字符串 huzzah 在莎士比亚作品集中出现的次数:

    bq query --use_legacy_sql=false \
    'SELECT
     word
    FROM
     `bigquery-public-data`.samples.shakespeare
    WHERE
     word = "huzzah"'
    
    由于该子字符串未出现在莎士比亚作品集中,因此不会返回任何结果。

    输出内容类似如下:

    Waiting on job_e19 ... (4s) Current status: DONE
    

创建表和加载数据

在接下来的部分中,您将创建一个新表并将其放入新数据集中。 每个表都存储在一个数据集中。一个数据集是一组资源(例如表和视图)。

下载示例数据

请按照以下步骤下载示例数据。示例数据由美国社会保障管理局提供,其中包含了约 7 MB 的热门婴儿姓名数据。

  1. 下载并解压缩婴儿名字 ZIP 文件

    该 ZIP 文件中包含一个描述数据集架构的 NationalReadMe.pdf 文件。详细了解该数据集

  2. 打开 yob2010.txt 文件,查看其具体内容。这是一个英文逗号分隔值 (CSV) 文件,其中包含以下三列:姓名、性别(MF)和使用该姓名的儿童人数。该文件没有标题行。

  3. 将文件 yob2010.txt 复制或移动到用来运行 bq 命令行工具的目录中。如果您要在 Cloud Shell 中运行 bq 命令行工具,请上传 yob2010.txt 文件。如需了解详情,请参阅使用 Cloud Shell 管理文件

创建数据集

接下来,创建新数据集。

  1. 如需查看您的默认项目是否包含任何现有数据集,请使用 bq ls 命令。

    bq ls
        

    输出类似于以下内容:

      datasetId
     -------------
      olddataset
  2. 列出特定项目中的数据集,方法是添加项目 ID,后跟一个英文冒号 (:):
    bq ls publicdata:

    此示例列出了 publicdata 项目中的数据集。

    输出内容类似如下:

      datasetId
     -----------
      samples
  3. 在您为本快速入门选择的项目中,创建名为 babynames 的新数据集:
    bq mk babynames

    数据集名称不得超过 1024 个字符,可包含 A-Z、a-z、0-9 和下划线。该名称不能以数字或下划线开头,也不能包含空格。

    输出内容类似如下:

    Dataset 'myprojectid:babynames' successfully created.
  4. 确认该数据集现已位于默认项目中:
    bq ls

    输出内容类似如下:

      datasetId
     -------------
      olddataset
      babynames

加载数据

  1. 在您创建的 babynames 数据集中,将源文件 yob2010.txt 加载到名为 names2010 的新表中:

    bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer

    bq load 命令只需一步即可创建表并加载数据。

    该命令包含以下参数:

    • datasetID:babynames
    • tableID:names2010
    • source:yob2010.txt(如有必要,请提供完整路径)
    • schema:name:string,gender:string,count:integer

    输出内容类似如下:

    Upload complete.
    Waiting on job_4f0c0878f6184119abfdae05f5194e65 ... (35s)
    Current status: DONE
  2. 确认该表现已显示在相应数据集内:

    bq ls babynames

    输出内容类似如下:

       tableId    Type
     ----------- -------
      names2010   TABLE
    
  3. 查看架构:

    bq show babynames.names2010

    输出类似于以下内容

    Table myprojectid:babynames.names2010
    
       Last modified         Schema         Total Rows   Total Bytes   Expiration
     ----------------- ------------------- ------------ ------------- ------------
      13 Mar 15:31:00   |- name: string     34041        653855
                        |- gender: string
                        |- count: integer
    

默认情况下,当您加载数据时,BigQuery 会要求 UTF-8 编码的数据。 如果您的数据采用 ISO-8859-1(或 Latin-1)编码,并且您遇到问题,请使用 -E 标志指示 BigQuery 将数据视为 Latin-1。如需了解详情,请参阅编码

查询示例数据

  1. 查询数据以获得最热门的女孩名字:

    bq query
    'SELECT
       name, count
     FROM
       babynames.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 |
    +----------+-------+
    
  2. 查询数据以获得最少见的男孩名字:
    bq query
    'SELECT
       name, count
     FROM
       babynames.names2010
     WHERE
       gender = "M"
     ORDER BY
       count ASC
     LIMIT 5'

    最小计数为 5,因为源数据省略了出现次数少于 5 次的名字。

    输出内容类似如下:

    Waiting on job_556ba2e5aad340a7b2818c3e3280b7a3 ... (1s) Current status: DONE
    +----------+-------+
    |   name   | COUNT |
    +----------+-------+
    | Aarian   |     5 |
    | Aaidan   |     5 |
    | Aamarion |     5 |
    | Aadhavan |     5 |
    | Aaqib    |     5 |
    +----------+-------+
    

清理

为避免因本页中使用的资源导致您的 Google Cloud 帐号产生费用,请按照以下步骤操作。

  1. 移除 babynames 数据集:

    bq rm --recursive=true babynames

    --recursive 标志会删除数据集中的所有表,包括 names2010 表。

  2. 如需确认删除命令,请输入 y

如果您在新项目中使用此快速入门,则可以删除项目

后续步骤