BigQuery 界面导航和数据探索 Codelab

1. 简介

BigQuery 是一个扩缩能力极强且经济实惠的无服务器数据仓库。您只需将数据迁移至 BigQuery，繁琐的工作交由我们来处理，这样您就可以集中精力处理业务运营真正重要的事务。您可以根据业务需求控制对项目和数据的访问权限，例如授权其他人来查看或查询您的数据。

在本实验中，您将探索 BigQuery 的分析可能性。您将了解如何从 Google Cloud Storage 存储分区导入数据集，并通过使用零售银行数据集来大致了解 BigQuery 界面。此外，本实验还会介绍如何探索 BigQuery 中的一些关键功能，这些功能可大大简化日常分析工作，例如将查询结果导出到电子表格中、查看和运行查询历史记录中的查询、查看查询性能，以及创建表视图以供其他团队和部门使用。

学习内容

在本实验中，您将学习如何执行以下任务：

  • 将新数据加载到 BigQuery 中
  • 熟悉 BigQuery 界面
  • 在 BigQuery 中运行查询
  • 查看查询效果
  • 在 BigQuery 中创建视图
  • 安全地与他人共享数据集

2. 简介：了解 BigQuery 界面

在本部分中，您将了解如何浏览 BigQuery 界面、查看可用的数据集以及运行简单的查询。

加载 BQ 界面

  1. 输入“BigQuery”位于 Google Cloud Platform 控制台顶部。
  2. 从选项列表中选择 BigQuery。请务必选择带有 BigQuery 徽标的选项，即放大镜。

查看数据集和运行查询

ee95ce13969ee1ad.png

  1. 在左侧窗格中的“资源”部分，点击您的 BigQuery 项目。
  2. 点击 bq_demo 即可查看该数据集中的表
  3. 在搜索框中，输入“卡片”查看包含“卡片”的表和数据集的列表。
  4. 选择“card_transactions”从搜索结果列表中选择一个表格

beb6ff6ca2930125.png

  1. 点击 card_transactions 窗格下的“详细信息”标签页可查看此表的元数据。
  2. 点击“预览”标签页可预览表

[竞争话题]： 与 Google Data Catalog 的集成意味着 BigQuery 元数据可以与其他数据源（例如数据湖或运营数据源）一起进行管理。从这个例子可以看出，Google Cloud 不仅是一个关系型数据仓库，还是一个完整的数据分析平台。

  1. 点击放大镜图标查询“card_transactions”表格。自动生成的文本将填充到 BigQuery 查询编辑器中。
  2. 输入以下代码，向我们显示 Card_Transaction 表中的不同商家
SELECT distinct (merchant) FROM bq_demo.card_transactions LIMIT 1000
  1. 点击“运行”按钮以运行查询。

35113542e7ec6fa6.png

3. 创建数据集和共享视图

共享数据和治理至关重要，可以在 BQ 界面中直观地完成这些操作。在本部分中，您将了解如何创建新数据集、使用视图填充和共享该数据集。

查看查询历史记录

  1. 点击“查询历史记录”在 GCP 控制台的左侧窗格中
  2. 点击“Query History”窗格中的“刷新”
  3. 点击查询最右侧的下载图片/箭头可查看查询结果。

6e3232ed96f647b8

创建新数据集

  1. 在 BigQuery 界面的资源窗格中选择 [您的项目名称]。
  2. 选择“新建数据集”在“项目信息”窗格中
  3. 对于数据集 ID：

bq_demo_shared

  1. 将其他所有字段保留为默认值
  2. 点击“创建数据集”

b433eba38f55124f.png dd774aca416e7fbc.png

创建视图

[竞争性谈话要点]： BigQuery 完全符合 ANSI SQL 标准，既支持简单和复杂的多表联接，也支持丰富的分析功能。我们持续发布对传统数据仓库中的常见 SQL 数据类型和函数的增强支持，以简化迁移过程。

  1. 选择“编写新查询”。
  2. 在查询编辑器中插入以下代码
WITH revenue_by_month AS (
SELECT
    card.type AS card_type,
    FORMAT_DATE('%Y-%m', trans_date) as revenue_date,
    SUM(amount) as revenue
FROM bq_demo.card_transactions
JOIN bq_demo.card ON card_transactions.cc_number = card.card_number
WHERE trans_date  DATE_ADD(CURRENT_DATE, INTERVAL -1 YEAR)
GROUP BY card_type, revenue_date
)
SELECT
    card_type,
    revenue_date,
    revenue as monthly_rev,
    revenue -  LAG(revenue) OVER (ORDER BY card_type, revenue_date ASC) as rev_change
FROM revenue_by_month
ORDER BY card_type, revenue_date ASC;
  1. 点击“保存视图”
  2. 为“项目名称”选择当前项目
  3. 选择新创建的数据集：

bq_demo_shared

  1. 对于表名称：

rev_change_by_card_type

  1. 点击“保存”。

4b111056b544c27d

共享视图和数据集

  1. 选择“bq_demo_shared”数据集。
  2. 点击“共享数据集”在“数据集信息”窗格中
  3. 输入电子邮件地址
  4. 选择“BigQuery Data Viewer”在“角色”下拉菜单中
  5. 点击“添加”
  6. 点击 完成

1c04b6b5ebc191dc

探索 Google 表格中的数据

[竞争性谈话要点]： BigQuery 与其竞争对手相比的另一个优势是 BI Engine。BI Engine 可用于通过内存中缓存引擎使 BI 类型摘要查询在 1 秒内返回。Google 数据洞察目前支持此功能，但很快就会推出，以加快 BigQuery 中的所有查询速度。

例如：

Snowflake 依靠第三方 BI 工具实现信息中心和数据可视化，而 GCP 提供一系列集成式 BI 工具，包括关联工作表、数据洞察和 Looker。

  1. 选择“rev_change_by_card_type”视图。
  2. 点击放大镜查询视图 255be22b0eaf339
  3. Type:

SELECT *

来自 bq_demo_shared.rev_change_by_card_type

  1. 点击“运行”
  2. 点击“导出”结果窗格中的图标
  3. 选择“使用 Google 表格探索数据”

9617b522025fd337

  1. 点击“开始分析”
  2. 选择“数据透视表”
  3. 选择“新建工作表”
  4. 点击“创建”
  5. 添加“revenue_date”数据透视表编辑器（位于表格窗口右侧）的“行”部分下方
  6. 添加“card_type”在数据透视表编辑器的“列”部分下
  7. 添加“monthly_rev”在数据透视表编辑器的“列”部分下
  8. 点击“应用”

48e67c2e04965796

  1. 前往 Google 表格界面的顶层文件夹，然后选择“插入图表”

4. 设置：数据集成

在本部分中，您将学习如何创建新表，并对 Google Cloud 提供的众多公共数据集之一执行联接。

[竞争性谈话要点]：

多年来，BigQuery 一直支持共享数据集。任何项目中的客户都可以查询公开数据集以及与其共享的其他项目中的数据集。

BigQuery 可以通过使用外部表在 GCS 中支持数据湖。除批量加载外，BigQuery 还支持以高达每秒数百 MB 的速率将数据流式传输到数据库。Snowflake 不支持流式数据。

将数据导入新表

  1. 在资源窗格中，选择 bq_demo 数据集
  2. 在数据集信息窗格中，选择“创建表”
  3. 选择 Google Cloud Storage 作为来源
  4. 在文件路径文本框中：

gs://retail-banking-looker/district

  1. 选择 CSV 作为文件格式
  2. 输入“区”对于表名称
  3. 选中“自动检测架构”对应的复选框
  4. 点击“创建表”

查询公共数据集

  1. 在查询编辑器中输入以下查询：
SELECT
    CAST(geo_id as STRING) AS zip_code,
    total_pop,
    median_age,
    households,
    income_per_capita,
    housing_units,
    vacant_housing_units_for_sale,
    ROUND(SAFE_DIVIDE(employed_pop, pop_16_over),4) AS rate_employment,
    ROUND(SAFE_DIVIDE(bachelors_degree_or_higher_25_64, pop_25_64),4) AS rate_bachelors_degree_or_higher_25_64
  FROM
    `bigquery-public-data.census_bureau_acs.zip_codes_2017_5yr`;
  1. 点击“运行”
  2. 查看结果

dff40709db70d75.png

  1. 现在，我们将这些公开数据与另一个查询合并。在查询编辑器中输入以下 SQL 代码：
WITH customer_counts AS (
    select regexp_extract(address, "[0-9][0-9][0-9][0-9][0-9]") as zip_code, 
    count(*) as num_clients
    FROM bq_demo.client
    GROUP BY zip_code
    )
SELECT 
    CAST(geo_id as STRING) AS zip_code,
    total_pop,
    median_age,
    households,
    income_per_capita,
    ROUND(SAFE_DIVIDE(employed_pop, pop_16_over),4) AS rate_employment,
    num_clients
FROM
    `bigquery-public-data.census_bureau_acs.zip_codes_2017_5yr`
JOIN customer_counts on zip_code = geo_id
ORDER BY num_clients DESC
  1. 点击“运行”
  2. 查看结果

b853ad571e7a3038.png

5. 容量管理

使用槽和预留

BQ 提供多种价格模式来满足您的需求。大多数大客户主要采用固定费率来获得可预测的价格，并预留容量。对于超出基准容量的爆发，BQ 提供了灵活槽，可让您即时增长到额外的容量，然后自动缩减，而不会影响正在运行的查询。BQ 还提供字节扫描模型，让您只需为运行的查询付费。

[竞争性谈话要点 ：一些竞争对手只采用固定容量模式，在这种模式下，客户必须为其组织中的每个工作负载分配一个虚拟仓库。

  1. 转到“预订”标签页。

964f4ab78d35d067

  1. 点击“购买槽”

c8cb5ee61bbea814.png

  1. 选择“Flex”作为持续时间。
  2. 选择 500 个槽。
  3. 确认购买。

d615f5908dffc1ee.png

  1. 点击“查看槽承诺”。
  2. 点击“创建预留”
  3. 用户“demo”作为预留名称
  4. 选择“美国”作为地理位置
  5. 输入“500”作为槽（全部可用）
  6. 点击“分配关系”
  7. 为组织项目选择当前项目
  8. 选择“演示”对于预留 ID
  9. 点击“创建”。