BigQuery 快速指南

BigQuery - 概述

BigQuery 是 Google Cloud Platform (GCP) 的结构化查询语言 (SQL) 引擎。BigQuery 允许用户使用无服务器云基础设施即时查询、创建和操作数据集。因此，学生、专业人士和组织能够以几乎无限的规模存储和分析数据。

与其他一些 Google 计划一样，BigQuery 最初是 Google 开发人员内部用于处理和分析大型数据集的工具。自 2006 年以来，Google 员工一直在使用 BigQuery 及其前身 **Dremel**。在 Google 内部取得成功后，GCP 于 2010 年首次发布了 BigQuery 的 Beta 版，然后在 2011 年将其广泛可用。

BigQuery 如何胜过竞争对手？

虽然在 BigQuery 发布时市场上有很多 **SQL** 引擎和集成开发环境 (IDE)，但 BigQuery 利用了几个竞争优势，其中包括：

率先采用基于槽的查询系统，该系统可以根据用户需求自动分配计算能力或“槽”
提供与各种编程语言（从 **Python** 到 **JavaScript**）的 API 集成
为用户提供使用 SQL 编写的机器学习模型的训练、运行和部署能力
与 Google Data Studio（已弃用）和 Looker 等可视化平台无缝集成
能够存储和查询 ARRAY 和 STRUCT 类型，这两种都是复杂的 SQL 数据类型

为了帮助学生和专业人士，BigQuery 提供了有关函数、公共数据集和可用集成的可靠文档。

Google BigQuery 使用案例

如果使用得当，Google BigQuery 可以用作个人或组织的 **数据仓库**。但是，由于 BigQuery 面向 SQL，因此最适合与具有结构化数据（而不是非结构化数据）的源一起使用。

Google 的云架构促进了可靠的存储并实现了几乎无限的可扩展性。这使得 BigQuery 成为企业流行的选择，尤其是在生成或摄取大量数据的组织中。

要真正理解 BigQuery 的强大功能，了解不同数据团队角色之间的用例差异非常有帮助。

数据分析师 - 将 BigQuery 与 Tableau 或 Looker 等数据可视化工具结合使用，为组织领导层创建顶级报告。
数据科学家 - 利用 BigQuery 的机器学习功能 Big ML 创建和实施机器学习模型。
数据工程师 - 使用 BigQuery 作为数据仓库，并构建视图和用户定义函数等工具，使最终用户能够在清理后的预处理数据集中发现见解。

当结合使用时，BigQuery 提供了一个优化存储并生成细致入微的见解以提高增长和收入的机会。

在 BigQuery 中加载数据

在 BigQuery 中加载数据非常简单直观，云开发人员可以使用多种选项。BigQuery 可以以多种形式摄取数据，包括：

CSV
JSON
Parquet

BigQuery 还可以与 Google Sheets 等 Google 集成同步以创建实时连接的表。并且 BigQuery 可以加载存储在 Google Cloud Storage 等云存储存储库中的文件。

开发人员还可以利用以下方法将数据干净地加载到 BigQuery 中：

BigQuery 数据传输，一种无需代码的数据管道，可与 Google Analytics 等 GCP 数据集成。
包含 CRUD 语句以创建、删除或删除表的基于 SQL 的管道。
利用 **BigQuery API** 执行批处理或流式加载作业的管道。
提供 BigQuery 连接的第三方管道，例如 Fivetran。

定期从上述来源加载数据使下游利益相关者能够可靠地访问及时、准确的数据。

BigQuery 查询基础

了解 BigQuery 用例和如何正确加载数据很有帮助，但学习 BigQuery 的初学者很可能从查询层开始。

BigQuery 中的表引用约定

在编写第一个 SELECT 语句之前，了解 BigQuery 中的表引用约定很有帮助。

BigQuery **语法**与其他 SQL 方言不同，因为正确的 BigQuery SQL 要求开发人员将表引用括在单引号中，如下所示：**' '**。

**表引用**涉及三个元素：

项目
数据集
表

放在一起，当在 SQL 查询或脚本中引用 BigQuery 表时，它看起来像这样：

'my_project.dataset.table'

一个基本的 BigQuery 查询如下所示：

SELECT * FROM 'my_project.dataset.table.'

传统 SQL 和标准 SQL

BigQuery SQL 与其他类型的 SQL 之间的一个重要区别是，BigQuery 支持两种 SQL 分类：**传统**和**标准**。

传统 SQL 使用户能够使用旧的、可能已弃用的 SQL 函数。
标准 SQL 代表了 SQL 的更新解释。

出于我们的目的，我们将使用标准 SQL 指定。

在 BigQuery 中编写查询时需要注意的事项

与其他 SQL 环境不同，当您在 BigQuery 中编写查询时，UI 会自动告诉您您的查询是否会运行以及它将处理多少数据。请注意，处理的数据量与预计的执行时间之间几乎没有明确的相关性。

执行后，您的查询将返回 **在 UI 中可见的结果**，这类似于查看电子表格或 Pandas 数据框。
您还可以选择将结果 **下载**为 CSV 或 JSON 文件，这两种文件都提供了一种简单的方法来保存数据以供以后使用。
如果您需要保存查询文本以供以后运行或编辑，BigQuery 还提供了一个选项来 **保存查询**并进行 **版本控制**以帮助跟踪更改。

在企业级运行 BigQuery

在具有多个 BigQuery 用户的组织中运行查询时，务必牢记几个因素，以确保您的查询能够在不中断或不导致其他用户拥塞的情况下运行：

避免在高峰使用时间执行查询。
尽可能使用 WHERE 子句来缩小处理的数据量。
对于大型表，创建用户可以更轻松、更有效地查询的视图。

从学习 BigQuery 到在企业级运行查询需要时间来了解特定于您组织的变量，例如槽使用情况和计算资源消耗。

但是，首先有必要更深入地了解在 BigQuery 中构建和优化查询。

BigQuery - 初始设置

要尝试以下一些概念，需要创建一个 Google Cloud Platform 帐户。通过创建 GCP 帐户，您将能够访问 Google 的云应用程序套件，包括 BigQuery。

为了向广大受众公开 GCP 产品，Google 为其许多旗舰产品（包括 BigQuery）添加了 **免费使用层**。BigQuery 用户，无论定价计划如何，每月都可以免费查询最多 1 TB（TB）的数据和存储最多 10 GB（GB）的数据。

但是，为了避免必须将信用卡附加到您的帐户并产生任何意外费用，新用户可以注册 90 天的免费试用期。免费试用期提供 300 美元的 GCP 积分。老实说，除非用户正在运行计算密集型流程（如虚拟机）或部署机器学习模型，否则这绰绰有余。

根据 Google 的说法，如果用户满足以下条件，则有资格参加该计划：

从未成为 Google Cloud 或关联产品的付费客户
以前从未注册过 Google Cloud Platform 免费试用版

要注册 BigQuery 的 90 天试用版，请执行以下步骤：导航到 cloud.google.com/free 您应该会看到此页面。

点击“免费开始”（页面中央）

登录您的 Google 帐户 -

完成注册流程后，您将可以访问 Google Cloud Platform。

每次登录时，您都会看到 GCP 徽标以及“欢迎”字样。在“欢迎”页面底部，您将看到指向您常用的 GCP 应用程序的“快速访问”链接。

此外，您还将能够访问本教程将重点介绍和探索的 BigQuery 的所有功能。

BigQuery 与本地 SQL 引擎

由于 **SQL** 已经存在 40 多年了，因此 BigQuery 并不是第一个 SQL 环境。在 BigQuery 发布之前，SQL 开发人员主要使用本地或“本地”数据库。

允许开发人员管理和交互数据库的系统称为数据库管理系统 (DBMS)。

在 **“云工程师”** 或 **“数据工程师”** 职位名称流行和广泛使用之前，那些使用 **DBMS** 工具的人拥有“架构师”等职位。通常，这个职位名称是字面意思，因为那些使用数据库的人维护着物理和数字基础设施。

数据建模

随着数据库技术的不断发展，架构师的职责主要涉及一项名为 **数据建模**的任务。

虽然像 INSERT() 这样的函数可以让从源获取数据并将其添加到数据库中看起来很容易，但在采用数据建模的组织中，如何存储和塑造数据需要大量的思考。

流行的数据建模概念包括诸如“星型模式”或“规范化”和“范式”之类的短语。

BigQuery 与任何传统 DBMS 有何不同？

虽然在创建 BigQuery 表时仍然建议遵循最佳实践，但 BigQuery 比传统的 DBMS 界面更具开箱即用性。

BigQuery 在以下方面与更 **“传统”** 的 DBMS 不同：

可扩展性 - 由于 Google 数据中心提供的惊人数量的云存储，BigQuery 可以扩展以满足几乎任何存储或查询需求。
API 集成 - BigQuery 的 SQL 引擎可以通过编程方式利用，而像 Postgre 这样的 DBMS 只能运行本机 SQL 查询。
ML/AI 功能，与 Vertex AI 集成。
可用于长期存储的更多 **特定数据类型**。
一种 Google 专属的 SQL 方言，即 **Google 查询语言** (GQL)，它包含比传统 SQL 方言更多更专业的函数。

从用户的角度来看，BigQuery 还提供了更多关于执行统计信息和用户活动的可见性 -

允许用户在执行相应的 SQL 之前查看查询是否可接受
提供执行计划和查询血缘关系
扩展元数据存储，以深入了解查询使用情况、存储和成本

BigQuery 确实是云端 SQL 查询的标准，它始于 Google Cloud Console。

BigQuery - Google Cloud Console

由于 BigQuery 是一个基于云的 **数据仓库** 和查询工具，因此可以通过 **Google Cloud Console** 在任何地方访问它。与本地数据库（例如 **PostgreSQL** 或 **MySQL**）不同，Google 没有为 BigQuery 创建应用程序。相反，BigQuery 存在于 Google Cloud Console 中的“云端”。

Google Cloud Console：所有 GCP 应用程序的主页

如果您已创建 Google 帐户并关联了 GCP 项目，您将能够访问 Google Cloud Console。

要导航到 BigQuery，您可以在搜索栏中键入 **“BigQuery”** 或从 Google Cloud Console 左侧的弹出菜单中选择它，您可以在“分析”类别下找到它。

为了确保在本教程中始终能够访问，建议您将其固定，以将“BigQuery”指定为您的环境中固定产品。

通过控制台，您将能够访问 BigQuery 的所有方面，包括 -

BigQuery Studio
BigQuery API
与 BigQuery 相关的日志和错误
BigQuery 时段使用量的计费

就像您设置 IDE 以满足您的舒适度和效率标准一样，强烈建议您花时间自定义您的 Google Cloud Console 体验，以便能够快速可靠地访问相关的 BigQuery 资源。

BigQuery - Google Cloud 层级结构

在继续之前，了解与 BigQuery 及其相关流程相关的基本概念和词汇非常重要。

首先，重要的是要理解，即使云计算提供了几乎无限的处理能力，如果 BigQuery 用户需要执行以下活动，他们也会遇到问题 -

执行计算量大的 SQL 操作，例如交叉连接或笛卡尔积连接。
尝试运行大型查询而未指定目标表。
在使用高峰时段运行大型查询（如果将 BigQuery 用作企业用户）。
按需或 **“临时”** 查询可能并且会造成死锁，尤其是在与计划流程争夺执行时段时。

Google Cloud 层次结构

如果您预计要在 BigQuery 中创建和填充数据源，请务必注意 Google Cloud 层次结构 -

组织
项目
数据集
表

1. 组织层

除非您是帐户所有者、高管或决策者，否则您不太可能需要担心组织层。将其视为包含您在导航 BigQuery Studio 和在 SQL 环境中编写 SQL 查询时遇到的其他元素的实体。

2. Google Cloud 组织中的多个项目

任何 Google Cloud 组织都可以有多个项目。有时，公司或企业用户（为了避免混淆，我们将有意避免使用“组织”一词）创建不同的项目以分离暂存和生产环境。

其他时候，这些高级用户创建不同的项目以更好地控制可能敏感的数据，例如个人身份信息 (PII) 和机密收入信息。

无论哪种情况，当您开始使用 BigQuery 时，您将创建或接收权限以访问 BigQuery，作为具有特定权限和角色范围的用户。

3. 项目中的数据集和表

在项目中，最重要的是要记住数据集和表。为了澄清，**数据集包含一个表**或**多个表**。为了在技术讨论中保持准确性，请尽量避免互换使用这些术语。

您将在数据集中看到的其他元素包括 -

例程
模型
视图

这些其他数据元素将在后续章节中更详细地讨论。

什么是 Dremel？

BigQuery 不是一个无限的工具。事实上，最好避免将 BigQuery 视为一个谚语中的黑盒。为了加深您的理解，有必要“深入了解”并检查 BigQuery 引擎的一些内部工作原理。

Google 的 Dremel：一个分布式计算框架

BigQuery 基于一个名为 **Dremel** 的分布式计算框架，Google 在 2010 年的白皮书“Dremel：Web 规模数据集的交互式分析”中更详细地解释了这一点。

该白皮书描述了定义现代 BigQuery 的许多核心特征的愿景，例如临时查询系统、几乎无限的计算能力以及强调处理大数据（TB 和 PB）。

Dremel 如何工作？

由于 Dremel 最初是内部产品（自 2006 年以来在 Google 内部使用），因此它结合了搜索和并行数据库管理系统的各个方面。

为了执行查询，Dremel 使用了一种“树状”结构，其中查询的每个阶段都按顺序执行，并且可以并行执行多个查询。Dremel 将 SQL 查询转换为执行树。

时段：BigQuery 执行的基本单元

在 **“查询执行”** 标题下，作者描述了 BigQuery 执行的基本单元：**时段**。

时段是表示可用处理单元的抽象。
时段是有限的，这就是为什么项目中的任何阻塞通常是由于时段可用性不足造成的。
由于时段的使用会根据许多因素（如处理的数据量和一天中的时间）而变化，因此可以想象，在一天早些时候快速执行的查询现在可能需要几分钟。

**时段的抽象**可能是 Dremel 论文中表达的最适用的概念；其他信息有助于了解，但主要描述了 BigQuery 产品的早期迭代。

BigQuery：定价和使用模型

无论您是练习第一个 BigQuery 查询的学生还是高层决策者，了解定价对于定义您可以在 BigQuery 中存储、访问和操作内容的限制至关重要。

要彻底了解 BigQuery 定价，最好将成本分为两个部分 -

使用情况（BigQuery 的文档将其称为“计算”）
存储

使用情况涵盖了您可以想到的几乎所有 SQL 活动，从运行简单的 SELECT 查询到部署机器学习模型或编写复杂的自定义函数。

对于任何与使用相关的活动，BigQuery 提供以下选择 -

按使用付费或“按需”模型。
批量时段或“容量”模型，其中客户按时段付费。

哪个定价模型最适合您？

在决定使用哪种定价模型时，务必考虑以下因素 -

查询的数据量
产生的用户流量

“按需”模型按 TB 定价，这意味着对于拥有许多大型（多个 TB）表的用户来说，这可能是一种直观且方便的跟踪费用的方式。

**“容量”** 或时段模型对于正在发展其数据基础设施的组织或个人很有帮助，他们可能没有固定数量的数据来帮助他们计算可靠的每月费率。与其担心每个资源生成多少数据，不如将问题转移到改进最佳实践，以便将查询时间分配给计划流程和个人临时查询。

从本质上讲，时段模型遵循 Dremel 项目建立的框架，其中时段（服务器）被预留并相应定价。

什么是 BigQuery Studio？

在建立了基本的产品知识和理论之后，是时候返回到 **Google Cloud Console** 并进入 BigQuery Studio 了。BigQuery Studio 曾经被称为“SQL 工作区”，用户不仅可以在其中运行 BigQuery 查询，还可以运行一系列其他数据和 **AI** 工作流。

BigQuery 的目标是提供一个类似于 GitHub 的空间，它使用户能够编写和部署 SQL、**Spark** 甚至 **Python** 代码，同时维护版本历史记录并促进数据团队之间的协作。

SQL 查询、Python 笔记本、数据画布

第一次打开 BigQuery Studio 让人联想到任何其他 **SQL** IDE。但是，与本地 SQL IDE 不同，当 BigQuery 打开时，您有三种操作选择 -

SQL 查询
Python 笔记本
数据画布

单击 SQL 查询应该会打开一个空白页面，您可以在其中编写和运行查询。因此，SQL 查询和 Python 笔记本选项应该是不言自明的。数据画布是 AI 集成，在本教程中不会介绍。

假设您已创建或有权访问 BigQuery 项目，在左侧菜单中，您将看到一个项目名称的下拉列表，以及该项目范围内的任何数据集。

单击任何这些数据集，您将看到在该数据集中创建的表。

在底部，您将看到与您运行的 SQL 作业相关的信息。这些作业分为“个人”或由您的个人资料创建和运行的查询，或“项目”，这使您能够查看在项目中运行的所有作业的元数据。

在 BigQuery Studio 中保存工作可以通过两种方式实现：一种是无版本历史记录的“经典”保存查询，另一种是有版本历史记录的保存查询。保存功能还便于轻松创建视图，这将在后面的章节中更详细地介绍。

BigQuery - 数据集

数据集是存在于项目中的实体。数据集充当 BigQuery 表以及视图、例程和机器学习模型的容器。

表不能独立于数据集存在，因此在 BigQuery Studio 中创建新的数据源时，必须创建一个数据集。

除了可读名称等属性外，开发人员还必须在授权创建数据集时指定一个 **位置**。这些位置对应于全球 Google 数据中心的物理位置。

指定位置时，您需要指定单个区域或多区域。例如，您不会选择芝加哥的数据中心，而是指定“us-central-1”。

将数据集设置为多区域实体可以提供额外的优势，即当特定区域没有资源来满足当前需求时，BigQuery 会转移位置。当前的多区域位于美洲（美国）或欧盟（欧洲）。

在 BigQuery 中创建数据集的步骤

要创建数据集，请按照以下步骤操作。首先，导航到您的项目名称，然后单击三个点，这将触发一个包含“创建数据集”的弹出窗口 -

单击“创建数据集”后，系统将提示您输入 -

数据集 ID
位置类型（区域与多区域）。
默认表格过期时间（表格过期前的天数）。

最终结果是一个数据集，它充当未来表格、视图和物化视图的容器。

“共享”选项允许开发人员管理对数据集的访问控制，以限制未经授权的用户。

BigQuery：公共数据集

如果您是 BigQuery 的新手，并且可能也是 SQL 的新手，那么您可能还没有生成要存储和操作的数据。这是使用 BigQuery Studio 作为 SQL 沙箱的优势之一。除了无服务器基础设施之外，BigQuery 还提供了数 TB 的示例数据，学生和专业人士可以使用这些数据来学习和改进他们的 SQL 技能。

通过 Google Cloud 公共数据集计划发布，BigQuery 公共数据集存储在它们自己的通用访问项目中：bigquery-public-data。
根据每 TB 付费定价模型，开发人员每月可以免费查询最多 1 TB 的数据。
与许多库存数据集不同，表格中包含的数据是真实的，即“杂乱的”，并且有时需要进行大量转换才能产生可操作的见解。

BigQuery 还提供了一些独立于其 BigQuery 公共数据集的示例表格，这些表格可以在bigquery-public-data:samples表格数据集中找到 -

gsod
github_nested
github_timeline
natality
shakespeare
trigrams
wikipedia

也许访问 BigQuery 公共数据集最重要的优势在于，数据是从 BBC、Hacker News 和约翰霍普金斯大学等真实数据源中提取的。

BigQuery - 表

表格是 BigQuery 的基础数据源。BigQuery 是一个SQL数据存储，因此数据以结构化（而不是非结构化或 NoSQL）的方式存储。BigQuery SQL 表是列式的，其结构类似于电子表格，属性或字段映射到列，记录填充行。

与数据集不同，创建表格时，用户无需指定位置。

BigQuery 中的表格类型

BigQuery 中有两种重要的表格类型 -

标准表格（如任何面向 SQL 的表格）。
视图（一个可以像标准表格一样查询的半永久性表格）。

表格示例

表格创建将在后面的章节中介绍。但是，在此期间，识别和识别上面讨论的表格类型很有帮助。

这是一个标准表格的示例 -

值得注意的是，用户可以看到一个元数据属性“描述”，该属性立即向开发人员和用户告知表格包含哪些数据。

视图示例

创建或访问表格使开发人员能够基于此数据源构建后续资源。您一定会遇到并使用的表格类型之一是视图。

这是视图在 BigQuery 中的显示方式。

它的模式和在 UI 中的显示几乎与标准表格相同。

最后，视图是使用视图定义创建的，它实际上只是一个物化查询。

BigQuery - 视图

SQL 中的视图是什么？

在SQL中，视图是一个虚拟化表格，它不包含像 CSV 文件这样的数据源的输出，而是包含一个预执行的查询，该查询会在新数据可用时更新。

由于视图仅包含预过滤数据，因此它们是减少处理数据量范围的常用方法，并且还可以缩短某些数据源的执行时间。

表格是数据源的全部，而视图表示由保存的查询生成的数据片段。
查询可能从给定表格中选择所有内容，而视图可能仅包含最近一天的数据。

创建 BigQuery 视图

BigQuery 视图可以通过数据操作语言 (DML) 语句创建 -

CREATE OR REPLACE VIEW project.dataset.view

这是一个创建视图定义的示例，该定义仅包含 2022 年的奥斯汀自行车共享站数据（来自同名的 BigQuery 公共数据集）。

或者，BigQuery 用户可以在 BigQuery 用户界面 (UI) 中创建视图。单击数据集后，只需选择“创建视图”，而不是选择“创建表格”。BigQuery 提供了单独的图标来区分标准表格和视图，以便开发人员可以一目了然地看出区别。

要访问我们上面创建的视图，只需运行 SELECT 语句，就像您用于访问在标准表格中生成的数据一样。

使用此查询，您将获得如下所示的输出表格 -

物化视图

除了标准视图之外，BigQuery 用户还可以创建物化视图。物化视图位于视图和标准表格之间。

BigQuery 文档将物化视图定义为：“ [P] 定期缓存视图查询结果的预计算视图。缓存的结果存储在 BigQuery 存储中。”

需要注意的是，标准视图不会无限期地存储数据，因此不会产生长期存储费用。

BigQuery - 创建表

要开始利用 BigQuery 的强大功能，有必要创建一个表格。在本章中，我们将演示如何在 BigQuery 中创建表格。

创建 BigQuery 表格的要求

创建 BigQuery 表格的要求为 -

表格来源（“从…创建表格”）
项目
数据集
表格名称
表格类型

示例：创建 BigQuery 表格

回到奥斯汀自行车共享数据集，我们可以运行此 CREATE TABLE 语句。

此语句创建了一个名为 bikeshare_2022_store_data 的新表格。接下来，让我们运行一个查询以从这个新创建的表格中提取一些数据 -

此外，表格可以进行分区和聚类，这两种存储方法都可以帮助提高表格存储和查询的效率。

最后，表格会生成并包含有用的元数据，让开发人员知道其内容上次更改的时间（上次修改字段），提供表格用途的简要说明，甚至规定表格数据过期之前的时间（分区过期天数）。

BigQuery - 基本 Schema 设计

与Excel电子表格不同，SQL表格不会自动接受呈现的数据。数据源和表格必须就数据的范围和类型达成一致，然后才能成功摄取。类型必须一致且采用 BigQuery 可以解析并在BigQuery Studio中提供的格式。

什么是模式？

为此，开发人员必须提供一个模式。本质上，模式是有序的属性列表和它们对应的类型。

在 BigQuery 中，列顺序和列数很重要，因此任何提供的模式都必须与源表格的模式匹配。

指定模式的三种方法

在 BigQuery 中，有三种方法可以指定模式 -

在“创建表格”步骤期间在 UI 中创建模式。
将模式作为 JSON 文本文件编写或上传。
告诉 BigQuery 自动推断模式。

自动推断模式

虽然自动推断模式对于开发人员来说工作量最小，但这也会给数据管道带来最大的风险。

即使数据类型在一个运行中保持一致，它们也可能会意外更改。如果没有固定的模式，BigQuery 必须“确定”要接受哪种数据类型，这可能导致模式不匹配错误。

创建模式的 UI 方法

由于创建模式的 UI 方法相当直观，因此下一部分将重点介绍如何将模式创建为 JSON 文件。

将模式创建为 JSON 文件

JSON 模式格式是列表“[ ]”内的字典“{ }”。每个字段可以有三个属性 -

字段名称
列类型
列模式

默认列模式为“NULLABLE”，这意味着列接受 NULL 值。其他列模式将在嵌套数据类型的讨论中介绍。

JSON 模式的一行示例如下 -

{"name": "id", "type": "STRING", "mode": "NULLABLE"}

如果您只是添加一列或更改现有列的类型，您可以使用此查询生成现有表格的模式 -

[Generate schema query]

只需确保将结果设置为“JSON”即可复制/下载生成的 JSON 文件。

GCP Cloud Shell：创建表格

Cloud Shell 是 Google Cloud Platform 的命令行界面 (CLI) 工具，允许用户直接从终端窗口与数据源交互。就像可以使用 GCP Console 中的 BigQuery UI 创建表格一样，也可以通过 CLI 使用类似 Linux 的语法快速创建表格。

与在本地机器上配置 CLI 不同，只要您登录到 Google 帐户，您就会自动登录到 Cloud Shell，因此可以在终端中与 BigQuery 资源交互。也可以（但更复杂）在本地 IDE 中配置 gcloud CLI。

“bq”命令行

无论哪种情况，BigQuery Cloud Shell 集成都依赖于一个命令：bq。bq 命令行是一个基于 Python 的命令行工具，与 Cloud Shell 兼容。

要创建表格，有必要将“bq”与“mk”结合使用 -

--bq mk

此语法与“–table”或“-t”标志结合使用。还可以像在 BigQuery UI 中创建表格时一样指定多个参数。

可用的参数包括 -

过期规则（以秒为单位的过期时间）
描述
标签
添加标签（策略标签）
项目 ID
数据集 ID
表格名称
模式

这是一个示例 -

注意 - 提供了一个内联模式。

Bq mk -t sample_dataset.bikeshare_table_cli
name:STRING,station_id:STRING,modified_date:TIMESTAMP

成功执行后，您将获得如下输出 -

选择 Cloud Shell 而不是 UI 不会带来性能优势；这仅仅取决于用户偏好。但是，以这种方式创建表格在创建重复或自动流程时非常有用。

BigQuery - 修改表

在 SQL 开发过程中，几乎肯定会需要以某种形式编辑您已完成的工作。这可能意味着更新查询或细化视图。但是，通常这意味着更改**SQL 表格**以满足新的需求或促进新数据的传输。

ALTER 命令的使用场景

为了更改现有表格，BigQuery 提供了一个ALTER关键字，它允许对表格结构和元数据进行强大的操作。

在 SQL 环境中更改任何表格的语法为“ALTER TABLE”。ALTER 命令的使用场景包括：

添加列
删除列
重命名表格
添加表格描述
添加分区过期天数

现在让我们逐一介绍这些情况。

添加列

以下是修改前原始表格架构。

这是用于添加列的SQL 语句：

以下是添加新列后的表格架构。

删除列

这是现有表格的架构，在删除sale_id之前。

这是用于删除sale_id的DML：

以下是结果架构：

重命名表格

您可以使用以下命令重命名表格：

添加表格描述

使用以下查询添加表格描述：

您可以在以下屏幕截图中看到，此语句成功地向表格添加了描述。

添加分区过期天数

使用以下查询添加分区过期天数：

与 SELECT 语句不同，任何以 ALTER 开头的 SQL 代码都将从根本上改变给定表格的结构或元数据。

注意：您应该谨慎使用这些查询。

BigQuery - 复制表

SQL 表格可以像桌面上的文件一样根据需要复制或删除。

复制表格可以采取两种形式：

复制/重新创建表格
克隆表格

让我们了解一下克隆表格与复制表格的区别。

在 BigQuery 中克隆表格

在 BigQuery 中创建现有表格的完美副本称为克隆表格。此任务可以通过**BigQuery Studio** UI 或通过 SQL 复制过程来完成。

无论哪种情况，重要的是要记住，任何创建的新表格，即使是克隆的表格，仍然会产生长期存储和使用费用。

在 BigQuery 中复制表格

复制表格会保留其所有当前属性，包括：

存储的所有数据
分区规范
聚类规范
元数据（如描述）
敏感数据保护策略标签

要在 BigQuery Studio UI 中复制表格，请导航到查询环境。点击您要复制的表格。选择“复制”。

需要注意的是，此复制过程不是自动的。当您点击“复制”时，您需要指定要将新表格复制到的数据集，并提供一个新的表格名称。

注意：GCP 的默认命名约定是在原始表格名称的末尾附加“_copy”。

BigQuery 不支持“SQL COPY”命令。相反，开发人员可以使用几种不同的方法复制表格。

创建或替换表格

CREATE OR REPLACE TABLE 通常被认为是 BigQuery 中的默认创建表格语句，它可以充当事实上的 COPY。

CREATE OR REPLACE TABLE project.dataset.table

它需要使用 AS 关键字提供某种查询：

CREATE OR REPLACE TABLE project.dataset.table AS (
)

要执行复制，您可以简单地“SELECT * from”现有表格。

为了创建完美的克隆，开发人员可以使用“CREATE TABLE CLONE”关键字。此命令创建现有表格的完美副本，无需提供查询。

通过 UI 和支持的 SQL 语法，BigQuery 提供了与复制和克隆表格相关的灵活性。

BigQuery - 删除和恢复表格

删除表格提供了相同的两个选项：UI 和SQL 语法。要在 UI 中删除表格，只需选择要删除的表格并选择“删除表格”即可。由于这是一个永久性操作，因此在删除之前系统会提示您键入表格名称。

注意：您也可以在 SQL 环境中 DROP 表格。

要仅删除表格的内容并保留其中的数据，可以使用 DELETE 命令，该命令需要 WHERE 子句。

要在没有任何参数的情况下删除任何内容，可以使用以下查询：

DELETE FROM project.dataset.table
WHERE 1=1

注意：请谨慎使用此查询。

在 BigQuery 中恢复表格

尽管采用了分两步的删除过程，但仍然很可能意外地 DROP 或删除表格。因此，BigQuery 的创建者意识到可能需要为用户提供一种方法来恢复过早删除的表格。

BigQuery 表格快照

在 SQL 环境中创建和使用表格时，这一点并不明显，但在后台，BigQuery 正在自动保存您的工作——在一定程度上。

BigQuery 使用“快照”定期保存表格，作为提供即时备份的一种方式，以备不时之需。需要注意的是，如果用户从快照中恢复表格，他们实际上并没有恢复其原始表格——他们只是恢复到表格的快照或副本。

此工具并非没有限制。BigQuery 表格快照仅保留 7 天。因此，只能在初始删除后的 7 天内恢复已删除的表格。

两种恢复表格的方法

BigQuery 提供了两种恢复表格的方法。顺便说一句，它们都没有使用 BigQuery SQL。它们需要通过gcloud 命令行或通过访问 API以编程方式访问 BigQuery。任一语句都将依赖于选择要恢复到的快照的正确时间戳。

1. Gcloud 命令

在此，命令“bq cp”为“bq copy”。“No-clobber”是一个参数，如果目标表格不存在，则会指示命令失败。

bq cp \
– restore \ 
– no-clobber \
–snapshot_project_id.snapshot_dataset_id.snapshot_table_id \ 
–target_project_id.target_dataset_id.target_table_id

2. Python 函数

理想情况下，开发人员将实施预防措施以避免需要恢复。但是，如果发生最坏的情况，BigQuery 提供了此故障保护。

BigQuery - 填充表

在初学 BigQuery 时，立即使用其功能和编写SQL 查询的最快速方法之一是提供现有的数据源。最容易上传和开始使用的其中一个数据源是静态文件。

BigQuery 接受的文件类型

BigQuery 将任何从文件创建的表格视为外部表格。

BigQuery 接受来自 GCP 的 Google Cloud Storage 中的本地上传或云存储的文件输入。

接受的文件类型包括：

CSV（逗号分隔值）
JSONL（换行符分隔的 JSON）
Parquet
Avro
ORC

就像创建空表格一样，要填充数据，需要指定架构。提醒一下，架构可以是推断的（自动生成的），也可以在 UI 中或作为 JSON 文件提供。

检查 BigQuery 是否支持数据类型

使用外部文件（尤其是 CSV）时面临的挑战之一是，BigQuery 对某些数据类型有特殊要求。

在上传文件之前，最好检查 BigQuery 是否支持数据类型并相应地调整输入。例如，BigQuery 对其摄取时间戳数据的方式非常挑剔，即使对于缩写日期，也更喜欢每个值有两个整数而不是一个。

BigQuery 如何避免文件上传错误？

开发人员在将文件加载到任何 SQL 表格时遇到的另一个挑战是特殊字符的存在，例如换行符“\n”。虽然始终建议以编程方式删除这些字符并充分清理数据，但 BigQuery 提供了过滤掉或完全忽略可能导致文件上传错误的行的方法。

首先，BigQuery 允许开发人员指定一个整数，表示允许出现多少个错误。如果有一行特定的错误行不包含任何有意义的数据，这将很有帮助。
此外，BigQuery 提供了一个参数用于跳过标题行，包括带引号的换行符，并允许参差不齐或格式错误的行。

不幸的是，即使使用这些选项，确定文件是否可以上传的唯一方法是进行一些试用、错误和迭代。

BigQuery Studio：从 SQL 语句填充表格

根据具体情况，有两种方法可以从“头开始”（即简单的 SQL 语句）填充表格。

1. CREATE OR REPLACE 命令

第一个 CREATE OR REPLACE 已经介绍过了。在这种情况下，开发人员正在创建一个全新的表格，并且必须定义表格架构以及表格内容。通常，当聚合或扩展 SQL 表格中已存在的数据时，最好使用 CREATE OR REPLACE。

2. INSERT 命令

如果您发现自己拥有一个表格的“外壳”，包括定义良好的架构但尚未添加任何数据，并且您没有外部文件或 API 有效负载之类的源，那么添加数据的方式就是通过 INSERT 命令。

在这种情况下，要正确使用 INSERT，不仅需要定义列、类型和模式，还需要提供要插入的数据。

在 SQL 的限制范围内定义数据涉及提供值和列别名。例如：

3. UNION 命令

要添加多行，请使用 UNION 命令。对于不熟悉 UNION 的人来说，它类似于 Pandas 的concat，并且本质上是将行条目“堆叠”在彼此之上，而不是根据公共键进行连接。

UNION 有两种类型：

UNION ALL
UNION DISTINCT

为了确保您的数据使用 UNION 正确插入，有必要确保数据类型彼此一致。例如，由于我为 station_id 提供了 STRING 值，因此我无法将后续行的类型更改为 INTEGER。

在专业环境中，INSERT 不用于一次插入一行。相反，INSERT 通常用于插入数据分区，通常按日期（即最近一天的数据）进行分区。

从连接的电子表格填充表格

如您现在所见，BigQuery 中的查询与在本地环境中编写 SQL 不同，部分原因在于开发人员能够将 BigQuery 数据与 GCP 的云端工具同步。其中一个功能强大且直观的集成是将 BigQuery 与 Google Sheets 结合使用。

展开外部表源的下拉菜单，您会发现“Sheets”作为开发人员可用于使用数据填充表的源。

Google Sheet 作为主要数据源

使用 Google Sheet 作为主要数据源非常有用，因为与静态 CSV 文件不同，Sheet 是动态实体。这意味着对连接的 Sheet 中的行所做的任何更改都将实时反映在关联的 BigQuery 表中。

要将 Google Sheet 连接到 BigQuery 表，请按照原始的创建表流程操作。您需要选择**“驱动器”**作为源，而不是从上传中选择创建表。即使 Google Sheet 是在 Sheets UI 中创建的，它们也位于 Google Drive 中。

与 CSV 文件类似，连接的 Sheet 必须具有定义的标题。但是，与 CSV 文件不同，开发人员可以指定要包含和省略的列。

其**语法**类似于创建和执行 Excel 公式。只需编写列字母和行号即可。要选择给定行之后的全部行，请在列前加上感叹号。

Like: !A2:M

使用相同的语法，您甚至可以选择提供的 Sheet 中的不同选项卡。例如 -

"Sheet2 !A2:M"

与使用 CSV 文件一样，您可以指定要忽略哪些行和标题，以及是否允许带引号的新行或参差不齐的行。

重要说明

DML 语句（如 INSERT 或 DROP）不适用于连接的 Sheet。要省略列，您需要在 Sheet 中隐藏它或在初始配置中指定它。

BigQuery 标准 SQL 与传统 SQL

虽然SQL 没有像Python（或任何其他脚本语言）那样的底层依赖项，但您选择的 BigQuery SQL 版本之间存在差异。

由于 Google Cloud Platform 认识到开发人员熟悉不同的 SQL 方言，因此他们为那些习惯于使用更传统或“传统”SQL 版本的开发人员提供了选项。

标准 SQL 与传统 SQL 之间的区别

标准 SQL 和传统 SQL 之间的主要区别在于**类型的映射**。传统 SQL 支持更接近于通用数据类型的类型，而标准 SQL 类型则更特定于 BigQuery。例如，BigQuery 标准 SQL 支持的时间戳值的范围要窄得多。

其他区别包括 -

使用反引号而不是方括号来转义特殊字符。
表引用使用冒号而不是点。
不支持通配符运算符。

在 BigQuery SQL 环境中切换标准 SQL 和传统 SQL 非常简单。在编写和执行 SQL 查询之前，只需在第 1 行添加注释：“#legacy”。

BigQuery 的标准 SQL 优势

与派生的传统 SQL 方言相比，标准 SQL 使 SQL 开发人员能够更灵活、更高效地编写查询。标准 SQL 通过提供在处理专业环境中遇到的“杂乱”数据时有用的函数和框架，提供了更多“现实世界”的实用程序。

标准 SQL 促进了以下功能 -

更灵活的 WITH 子句，使用户能够在脚本中多次重复使用子查询和 CTE。
可以用 SQL 或 JavaScript 编写的用户定义函数。
SELECT 子句中的子查询。
相关子查询。
复杂数据类型（ARRAY 和 STRUCT 类型）。

标准 SQL 与传统 SQL 之间的兼容性问题

通常，标准 SQL 和传统 SQL 操作之间的不兼容性不会导致很多问题。但是，有一种情况适用于**AirFlow** 用户。

如果使用**BigQueryExecuteQuery** 运算符，则可以指定是否使用传统 SQL。要使用标准 SQL，请设置**"use_legacy_sql = FALSE"**。

但是，如果开发人员没有这样做并使用了仅与标准 SQL 兼容的函数，例如**TIMESTAMP_MILLIS()**(一个时间戳转换函数)，则整个查询可能会失败。

BigQuery - 编写第一个查询

可以在查询编辑器中打开一个空白页面，但是最好直接从表选择步骤编写第一个查询，以避免语法错误。

要以这种方式编写第一个查询，请首先导航到包含要查询的表的dataset。单击“查看表”。在上面面板中，选择“查询”。此过程将打开一个新窗口，其中表名已填充，以及创建者添加的任何限制。

例如，表可能需要 WHERE 子句，或者建议的查询可能会将用户限制为例如 1000 行。为了**遵循最佳实践**，请将“*”替换为您要查询的列的名称。

如果向**SELECT**添加任何聚合，请注意包含**GROUP BY**子句。
如果您想格外注意语法错误，还可以通过单击提供的架构中的列名来选择列名。

如果您按照这些步骤操作，则无需编写表名。但是，为了养成构建正确表引用的习惯，请记住公式为：**project.dataset.table**。这些元素都用**反引号**（而不是引号）括起来。

BigQuery Studio 的一个独特之处在于，IDE 会告诉您查询是否会运行。这将由一个绿色复选标记指示。

确认所有内容看起来都正确后，点击**运行**。在查询运行时，您将看到执行指标，例如处理的数据量、查询运行所需的时间以及所需的步骤数。如果您查看底部面板，您还将看到运行所需的插槽数量。

在 Cloud Shell 终端编写您的第一个查询

与在 UI 中查询类似，在 Cloud Shell 终端中查询遵循类似的结构，并允许用户使用 SQL 语法访问和操作数据。

“bq”查询及其常用标志

使用命令**bq** query 在 Cloud Shell 中编写和执行查询非常简单。在同一行中，用户可以提供标志来指示执行的某些方面。

**bq** query 命令的一些更**常用标志**包括 -

–allow-large-results（不会因结果过大而取消作业）
–batch = {true | false}
–clustering-fields = [ ]
–destination-table = table_name

您可能会注意到，所有这些参数都对应于在 UI 中创建表或运行查询时出现的下拉菜单。

要在 Cloud Shell 中运行查询 -

登录 GCP
进入 Cloud Shell 终端
进行身份验证（自动完成）
编写并执行查询

看起来像 -

(ornate-reaf-332816)$ bq query --use_legacy_sql=false \ 'SELECT * FROM 
ornate-reef-332816.sample_dataset.bikeshare_2022_stsore_date';

**结果**显示为终端输出。虽然结果没有像 BigQuery UI 结果那样呈现，但输出仍然整洁易懂。

BigQuery - CRUD 操作

CRUD 代表创建、替换、更新和删除，是一个基础的 SQL 概念。与仅在临时表中返回数据的常规查询不同，运行 CRUD 操作后，表的结构和架构会发生根本性变化。

CREATE OR REPLACE 查询

BigQuery 将 CRUD 的 C 和 R 与其语句**CREATE** OR **REPLACE** 结合起来。

CREATE OR REPLACE 可用于 BigQuery 的各种实体，例如 -

表
视图
用户定义函数 (UDF)

使用 CREATE OR REPLACE 命令的**语法**为 -

CREATE OR REPLACE project.dataset.table

创建操作将创建一个全新的实体，而 UPDATE 语句将在行（而不是表）级别更改记录。

UPDATE 查询

与 CREATE OR REPLACE 不同，UPDATE 使用了另一个语法片段 SET。最后，UPDATE 必须与 WHERE 子句一起使用，以便 UPDATE 知道要更改哪些记录。

组合在一起，它看起来像这样 -

以上查询更新了表，但仅影响日期等于当前日期的行。如果是这种情况，它将把日期更改为昨天。

DELETE 命令

与 UPDATE 类似，DELETE 也需要 WHERE 子句。DELTE 查询的语法很简单 -

DELETE FROM project.dataset.table
WHERE condition = TRUE

ALTER 命令

除了 CRUD 语句之外，BigQuery 还具有之前介绍过的 ALTER 语句。提醒一下，ALTER 用于 -

添加列
删除列
重命名表

谨慎使用这些功能中的每一个，尤其是在处理生产数据时。

BigQuery - 分区和聚类

由于本教程中已经使用了**“分区”**和**“聚类”**这两个术语，因此提供更多上下文很有帮助。

什么是分区和聚类？

这两个术语用于描述优化数据存储和处理的两种方法。

分区是指开发人员如何分割数据，通常（但不总是）按日期元素（如年、月或日）进行分割。聚类描述了如何在指定分区内对数据进行排序。

要使用任何一种存储方法，您必须定义所需的字段。只能为分区使用一个字段，但可以为聚类使用多个字段。

需要注意的是，要应用分区或聚类，必须在构建的“创建表”阶段执行此操作。否则，您将需要删除/重新创建具有更新的分区/聚类规范的表。

如何将分区或聚类应用于表

要在创建表时应用分区和/或聚类，请运行以下命令 -

Apply Partitioning or Clustering to a Table

您也可以在 UI 中指定这些方向。在点击“创建表”之前，请花点时间填写架构创建框正下方找到的字段。

如果您正确应用了分区/聚类，它可以显着降低长期存储成本和处理时间，尤其是在查询大型表时。

BigQuery - 数据类型

在加载和查询数据时，了解 BigQuery 对数据类型的使用和解释至关重要。如架构章节所示，加载到 BigQuery 中的每一列都必须具有定义的、可接受的类型。

BigQuery 接受其他 SQL 方言使用的许多数据类型，并且还提供 BigQuery 独有的数据类型。

跨 SQL 方言的常用数据类型

跨 SQL 方言通用的数据类型包括 -

STRING
INTEGER
FLOAT
BOOLEAN

BigQuery 还支持特殊数据类型，例如 JSON 数组，这将在后面的章节中讨论。

**注意** - 如果在加载过程中未提供架构或未指定类型，BigQuery 将推断类型。但是，对于依赖于不可预测的上游数据的集成，这并非一定是积极的结果。

与分区和聚类列一样，必须在创建表时指定类型。

使用 CAST() 函数更改类型

可以在查询中（临时或永久）更改数据类型。为此，请使用 CAST() 函数。CAST 使用以下语法：

CAST(column_name AS column_type)

例如：

CAST(id AS STRING)

类似于指定错误的类型，尝试强制将不兼容的类型 CAST 为其他类型可能会令人沮丧，并导致基础架构中断。

为了更可靠地转换数据类型，请使用 **SAFE_CAST()**，它会对不兼容的行返回 NULL，而不是完全中断：

SAFE_CAST(id AS STRING)

深入了解 BigQuery 如何解释给定输入的类型对于创建健壮的 SQL 查询至关重要。

BigQuery 数据类型 STRING

在 SQL 开发人员使用的大多数常见数据类型中，BigQuery 中的 STRING 类型通常易于识别。但是，字符串的操作或解释偶尔会出现一些特殊情况。

通常，字符串类型由字母数字字符组成。虽然字符串类型可以包含类似整数的数字和符号，但如果指定了 STRING 类型，则这些信息将像常规字符串一样存储。

新开发人员遇到的一个棘手的情况是处理包含整数或浮点数以及符号的行，例如货币情况。

虽然可能认为 $5.00 由于小数点而是一个 FLOAT，但美元符号使它成为一个字符串。因此，在加载包含美元符号的行时，BigQuery 会期望在架构中将其定义为 STRING 类型。

DATE 数据类型

DATE 数据类型是 STRING 类型的一个子集。

即使 BigQuery 有自己的日期值表示法，但默认情况下，日期值本身也以字符串形式表示。
对于使用 Python 中的 Pandas 的用户来说，这类似于日期字段如何在数据框中表示为对象。
有多种函数专门用于解析 STRING 数据。

STRING 函数

值得注意的 STRING 函数包括：

**LOWER()** - 将字符串中的所有内容转换为小写
**UPPER()** - LOWER 的反向操作；将值转换为大写
**INITCAP()** - 将每个句子的第一个字母大写；即句子大小写
**CONCAT()** - 组合字符串元素

重要的是，BigQuery 中的数据类型默认为 STRING。除了传统的字符串元素之外，这也包括开发人员未指定 REPEATED 模式列表。

INTEGER & FLOAT

如果您在企业（商业）规模上使用 BigQuery，则很可能需要处理涉及数字的数据。这可能是任何数据，从出席数据到营业收入。

无论如何，STRING 数据类型对于这些用例都没有意义。这不仅是因为这些数字没有像美元符号或欧元这样的货币符号，还因为为了生成有用的见解，需要利用特定于数值数据的函数。

**注意** INTEGER 和 FLOAT 之间的区别很简单：**"."**

在许多 SQL 方言中，当指定数值时，开发人员需要告诉 SQL 引擎期望多少位数字。这就是传统 SQL 获取像 **BIGINT** 这样的指定的来源。

**BigQuery 将 FLOAT 类型编码为 64 位实体**。因此，当您将列 CAST() 为 FLOAT 类型时，您会这样操作：

CAST(column_name AS FLOAT64)

值得注意的 INTEGER 和 FLOAT 函数

其他值得注意的 INTEGER 和 FLOAT 函数包括：

ROUND()
AVG()
MAX()
MIN()

注意事项

FLOAT 类型的 **一个重要注意事项** 是，除了指定由句点分隔的数字外，FLOAT 类型也是 NULL 的默认类型。

如果使用 **SAFE_CAST()**，最好包含其他逻辑将从 FLOAT 返回的任何 NULL 值转换为所需的类型。

BigQuery - 复杂数据类型

BigQuery 除了支持 STRING、INTEGER 和 BOOLEAN 等“常规”数据类型外，还提供对所谓的 **复杂数据** 的支持。通常，这也称为 **嵌套数据**，因为数据不适合传统的扁平表，并且必须位于列的子集中。

复杂数据结构很常见

对嵌套架构的支持允许更简化的加载过程。尽管 Google Cloud 列出了关于以下数据类型的各种教程为“高级”，但嵌套数据非常常见。

了解如何展平并使用这些数据类型是任何面向 SQL 的开发人员的一项有价值的技能。

这些数据结构常见的原因是源数据（例如 API 的 JSON 输出）通常以这种格式返回数据：

[{data: "id": '125467", "name": "Acme Inc.", "locations": 
   {"store_no": 4, "employee_count": 15}}]

请注意，“id”和“name”如何处于相同的引用级别。可以使用“data”作为键访问它们。
但是，要获取诸如“store_no”和“employee_count”之类的字段，就需要不仅访问 data 键，还需要展平“locations”数组。

这就是 BigQuery 的复杂数据类型支持有帮助的地方。数据工程师无需编写一个脚本遍历并展开“locations”，而是可以原样将这些数据加载到 BigQuery 表中。

BigQuery 中的复杂数据结构

BigQuery 支持以下三种类型的复杂或嵌套数据：

STRUCT
ARRAY
JSON

处理这些类型的策略将在接下来的章节中解释。

BigQuery - STRUCT 数据类型

STRUCT 和 ARRAY 是开发人员在 BigQuery 的列式结构中存储嵌套数据的方式。

什么是 Struct？

STRUCT 是一个字段集合，具有指定的类型（必需）和字段名称（可选）。值得注意的是，与 ARRAY 不同，STRUCT 类型可以包含混合的数据类型。

为了更好地理解 STRUCT 类型，请再次查看上一章中的示例，现在进行了一些更改。

"locations": [
   {"store_no": 4, "employee_count": 15, "store_name": "New York 5th Ave"}, 
   {"store_no": 5, "employee_count": 30, "store_name": "New York Lower Manh"}
]

之前“locations”是相同类型的 **dict**（用 JSON 表示），现在它包含两个 STRUCT，其类型为 **<INTEGER, INTEGER, STRING>**。

尽管支持 STRUCT 类型，但在表创建阶段，BigQuery 没有提供可用的显式标签 STRUCT。
相反，STRUCT 被表示为具有 NULLABLE 模式的 RECORD。

**注意** - 将 STRUCT 视为容器而不是专用数据类型。

在架构中定义时，STRUCT 内部的元素将使用“.”表示和选择。此处，**架构**将是：

{"locations", "RECORD", "NULLABLE"},
{"locations.store_no", "INTEGER", "NULLABLE},
{"locations.employee_count", "INTEGER", "NULLABLE"},
{"locations.store_name", "STRING", "NULLABLE"}

点表示法

要选择 STRUCT 元素，需要在 FROM 子句中使用 **点表示法**：

查询执行后，您可能会得到如下所示的 **输出**：

BigQuery - ARRAY 数据类型

与允许包含不同类型数据的 STRUCT 类型不同，ARRAY 数据类型必须包含相同类型的元素。

在像 Python 这样的编程语言中，ARRAY（也称为列表）用方括号表示：**[ ]**。
STRUCT 类型可以包含其他 STRUCT（以创建非常嵌套的数据），ARRAY 不能包含另一个 ARRAY。
但是，ARRAY 可以包含 STRUCT，因此开发人员可能会遇到一个 ARRAY，其中嵌入了多个 STRUCT。

BigQuery 不会将列标记为显式的 ARRAY 类型。相反，它以不同的模式表示。虽然常规 STRING 类型具有“NULLABLE”模式，但 ARRAY 类型具有“REPEATED”模式。

可以使用点选择 STRUCT 类型，但是 ARRAY 类型在表面级操作方面受到更多限制。

可以将 ARRAY 作为分组元素进行选择

但是，使用点或任何其他方法选择数组的元素是不可能的，因此这将不起作用：

UNNEST() 函数

要访问 store_information 中的名称，需要执行一个额外的步骤：**UNNEST()**，这是一个函数，它将展平数据，使其更易于访问。

UNNEST() 函数在 FROM 子句中与逗号一起使用。作为上下文：逗号表示隐式 CROSS JOIN。

要正确访问此 ARRAY，请使用以下查询：

它将为您获取如下所示的 **输出** 表：

除了使用 UNNEST() 之外，还可以为记录设置别名。然后，生成的别名 **“wd”** 可用于访问展开的数据。

BigQuery - JSON 数据类型

JSON 是 BigQuery 支持的最新数据类型。与 STRUCT 和 ARRAY 类型不同，JSON 相对容易识别。

对于使用脚本语言处理过数据或解析过 API 响应的开发人员来说，JSON 数据将很熟悉。

JSON 数据由花括号表示：{ }，就像 Python 字典一样。

**注意** - 在 BigQuery 引入对 JSON 类型的支持之前，JSON 对象需要表示为具有 NULLABLE 模式的 STRING。

开发人员可以在 UI 和基于文本的架构定义中指定 JSON：

不将 JSON 数据存储为 JSON 类型不一定导致加载失败，因为 BigQuery 可以支持 JSON 数据的 STRING 类型。

但是，不正确地存储 JSON 数据意味着开发人员将无法访问强大的 JSON 特定函数。

强大的 JSON 函数

借助内置函数，在 BigQuery 中使用 JSON 数据的开发人员无需编写脚本来展平 JSON 数据。相反，他们可以使用 JSON_EXTRACT 提取 JSON 对象的内容，然后允许处理和操作生成的数据。

其他强大的 JSON 函数包括：

JSON_EXTRACT_ARRAY()
PARSE_JSON()
TO_JSON()

能够在 BigQuery 中准确直观地查询 JSON 数据，使开发人员无需使用复杂的 CASE 逻辑或编写自定义函数来提取有价值的数据。

BigQuery - 表元数据

能够分析和理解组织数据的范围和内容固然重要，但对于 SQL 开发人员来说，了解使用 BigQuery 的性能和存储成本方面也很重要。

这时，查询 BigQuery 表元数据对于开发人员很有用，对于寻求充分利用 SQL 引擎的组织来说也至关重要。

对于那些没有广泛使用元数据的人来说：元数据顾名思义，是关于数据的数据。这通常与围绕资源性能或监控等内容的统计数据相关。

BigQuery 提供了 **多个元数据存储**，用户可以查询这些存储以更好地了解他们的项目如何消耗资源，其中一些包括：

INFORMATION_SCHEMA
__TABLES__ 视图
BigQuery 审计日志

每个表都可以像存储数据一样进行查询。

对于 INFORMATION_SCHEMA 和 __TABLES__，需要注意的是，表引用的语法有所不同。

而不是遵循典型的：**project.dataset.table** 表示法，INFORMATION_SCHEMA 和 __TABLES__ 的元素是在结束反引号后引用的。

INFORMATION_SCHEMA 是一个数据源，它有几个分支资源，如 COLUMNS 或 JOBS_BY_PROJECT

例如，引用 INFORMATION_SCHEMA 如下所示：

它将获取以下 **输出**：

TABLES 视图

TABLES 视图提供表级别的信息，例如表创建时间和上次访问表的用户。需要注意的是，TABLES 视图可以在数据集级别访问。

它将获取以下 **输出**：

基于现有表创建架构

INFORMATION_SCHEMA.COLUMNS 的一个有用的用例是能够使用此查询基于现有表创建架构：

Creating a Schema Based on an Existing Table

BigQuery - 用户定义函数

BigQuery 的优势之一是能够创建自定义逻辑来操作数据。在像**Python**这样的编程语言中，开发者可以轻松地编写和定义函数，这些函数可以在脚本中的多个位置使用。

BigQuery 中的持久化用户自定义函数

许多**SQL**方言，包括 BigQuery，都支持这些函数。BigQuery 将它们称为持久化用户自定义函数。简称为 UDF（用户自定义函数）或 PUDF（持久化用户自定义函数）。

用户自定义函数的本质可以分解成两个步骤：

定义函数逻辑
在脚本中使用函数

定义用户自定义函数

定义用户自定义函数从一个熟悉的 CRUD 语句开始：CREATE OR REPLACE。

这里，不需要使用 CREATE OR REPLACE TABLE，而是需要使用 CREATE OR REPLACE FUNCTION，后面跟着AS()命令。

与可以在 BigQuery 中编写的其他 SQL 查询不同，在创建 UDF 时，需要指定输入字段和类型。

这些输入的定义方式类似于 Python 函数：

(column_name, type)

为了看到这些步骤组合在一起，我创建了一个简单的临时 UDF，由 TEMP FUNCTION 指定，它根据用户输入解析各种 URL。

创建上述临时函数的步骤如下：

CREATE TEMP FUNCTION
指定函数名称 (get_sitetype)
指定函数输入和类型 (inp_url, STRING)
告诉函数返回哪种类型 (STRING)

REGEXP_CONTAINS() 函数搜索包含提供的 URL 字符串的字符串中的匹配项。NET.HOST() 函数从输入 URL 字符串中提取主机域名。

将其应用于 hacker news 数据集（BigQuery 公共数据集），我们可以生成一个输出，将存储的 URL 分类到不同的媒体类别中：

注意：临时函数必须紧跟在查询后面。

BigQuery - 连接外部数据源

虽然到目前为止，本教程的大部分内容都涉及 UI 和云终端，但现在是时候探索通过外部数据源连接到 BigQuery 了。

在 UI 中编写查询的局限性

即使在 BigQuery Studio 中编写查询可能很方便，但事实是，这只能满足有限范围的目的：

最初开发 SQL 查询或脚本
调试查询
进行抽查或质量保证

仅仅在 UI 中编写和运行查询并不能帮助交付自动化的数据解决方案。这意味着在 BigQuery SQL 环境中，您无法：

访问 BigQuery API
与 Airflow 集成
创建 ETL 管道

外部 BigQuery 集成

在接下来的章节中，我们将探讨如何将 BigQuery 与以下内容集成：

BigQuery 计划查询
BigQuery API (Python)
Cloud Composer / Airflow
Google Sheets
BigQuery 数据传输

外部 BigQuery 集成使开发人员能够利用 SQL 的强大功能来执行以下任务：

创建自动化的提取加载 (EL)
提取转换加载 (ETL)
提取加载转换 (ELT) 作业

BigQuery - 集成计划查询

计划查询除了是与 BigQuery 集成的最直观的外部集成（外部是因为该机制依赖于 BigQuery 数据传输服务）之外，还可以从**BigQuery Studio**中进行计划。

如果您愿意，也可以导航到侧边栏上的“计划查询”。但是，即使此页面也会提示您“创建查询”，这将带您返回到 UI。因此，最好通过以下方式在 UI 中创建和计划您的查询：

在**SQL**工作区中编写查询。
验证并运行查询（只有有效的查询才能运行或计划）。
选择“计划查询”，这将打开一个下拉菜单。

进入“计划查询”菜单后，您必须填写：

查询名称
计划频率
开始时间
结束时间

如果您不填写结束时间，则查询将永久按照其分配的计划运行。

注意：关于计划查询的一个注意事项是，一旦它们被计划，查询运行参数的某些方面（例如与查询关联的用户）就无法在 UI 中更改。

为此，需要通过**BigQuery API**或命令行访问计划查询。

集成 BigQuery API

BigQuery API 允许开发人员利用 BigQuery 的处理能力和 Google SQL 数据操作功能来执行重复性任务。

BigQuery API 是一个 REST API，支持以下语言：

C#
Go
Java
js
PHP
Python
Ruby

由于 Python 是数据科学和数据分析中最流行的语言之一，本章将探讨 Python 上下文中的 BigQuery API。

BigQuery API 部署选项

就像开发人员无法直接从 BigQuery Studio 部署 SQL 一样，对于生产工作流，访问 BigQuery API 的代码必须通过相关的 GCP 产品进行部署。

部署选项包括：

Cloud Run
Cloud Functions
虚拟机
Cloud Composer (Airflow)

BigQuery API 需要身份验证

使用 BigQuery API 需要身份验证：

如果在本地运行脚本，可以下载与运行 BigQuery 的服务帐户关联的凭据文件，然后将其设置为环境变量。
如果在连接到云的上下文中运行 BigQuery，例如在 Vertex AI 笔记本中，则会自动进行身份验证。

为了避免下载文件，GCP 还为大多数应用程序支持Oauth2 身份验证流程。

一旦经过身份验证，典型的 BigQuery API 用例包括：

运行包含给定表的 CRUD 操作的 SQL 脚本。
检索项目或数据集元数据以创建监控框架。
运行 SQL 查询以使用来自另一个数据源的数据合成或丰富 BigQuery 数据。

".query()" 方法

毫无疑问，最流行的 BigQuery API 方法之一是".query() 方法"。当与 Pandas 的.to_dataframe()结合使用时，它为以可读形式查询和显示数据提供了一个强大的选项。

此查询应获取以下输出：

BigQuery API 不是黑盒。除了日志记录（使用 Google Cloud Logging 客户端）之外，开发人员还可以查看 UI 中个人用户和项目级别上的实时作业信息细分。对于任何失败的作业进行故障排除，这应该是您的首选步骤。

BigQuery - 集成 Airflow

运行**Python**脚本加载 BigQuery 表格对于单个作业可能很有帮助。但是，当开发人员需要创建多个顺序任务时，孤立的解决方案并非最佳选择。因此，有必要超越简单的执行。需要编排。

BigQuery 可以与一些流行的编排解决方案集成，例如Airflow和DBT。但是，本教程将重点介绍 Airflow。

有向无环图 (DAG)

Apache Airflow 允许开发人员创建称为有向无环图 (DAG) 的执行块。每个 DAG 由许多任务组成。

每个任务都需要一个操作符。有两个重要的 BigQuery 兼容操作符：

BigQueryCheck 操作符
BigQueryExecuteQuery 操作符

BigQueryCheck 操作符

BigQueryCheck 操作符允许开发人员进行上游检查以确定数据是否已更新当天。

如果表在架构中不包含上传时间戳，则可以查询元数据（如前所述）。

开发人员可以通过运行此查询的版本来确定上次更新表的日期：

BigQueryExecuteQuery 操作符

要执行依赖于上游数据的**SQL**脚本，SQL 开发人员可以使用BigQueryExecuteQuery 操作符来创建加载作业。

Airflow 的更深入解释超出了本教程的范围，但 GCP 为想要了解更多信息的人员提供了广泛的文档。

BigQuery - 集成连接的 Sheets

对于选择使用 BigQuery 等云服务作为**数据仓库**的用户，通常的目标是从电子表格迁移数据到数据库。因此，将数据仓库和电子表格配对可能显得多余。

但是，将 Google Sheet 连接到 BigQuery 允许无缝重复“刷新”电子表格数据，因为源是 BigQuery 中的视图或表。

Google Sheets 通过两种方式支持 BigQuery 集成：

直接连接到表。
连接到自定义查询的结果。

与 BigQuery 中可用的外部数据源以下拉菜单形式显示不同，在 Google Sheets 中查找数据源需要一些挖掘。

将 BigQuery 资源连接到 Google Sheets

要将 BigQuery 资源连接到 Google Sheets，请按照以下步骤操作：

打开一个新的 Google Sheet
点击“数据”选项卡
在数据下，导航到数据连接
选择现有数据集
找到所需的表
或者，编写自定义查询
点击连接

该表单应从标准电子表格更改为类似于电子表格和 SQL 表混合的 UI。

如何确保同步并计划刷新？

虽然按照这些步骤可以确保连接处于活动状态，但就此停止将无法确保未来的同步。

要随着其关联资源的更新自动更新表单，您必须计划刷新。
您可以通过导航到“连接设置”来计划刷新。
与配置计划查询一样，计划刷新也很简单。选择您的刷新间隔、开始时间和结束时间。

配置完成后，表单现在将按照该计划更新，假设 BigQuery 表格中有可用数据。

BigQuery - 集成数据传输

BigQuery 数据传输促进来自 Google 关联产品的同步，并将生成的数据有效载荷导入 BigQuery。由于这些数据传输是在 Google 产品之间进行的，因此它们基于现成的报表。

在配置期间，用户可以选择他们想要导入的报表。不幸的是，这意味着没有空间进行上游自定义。

虽然担心给定报表缺少特定字段可能很直观，但情况往往相反。Google 的报表通常包含如此多的数据，因此需要构建视图以仅提取与您的用例相关的相关信息。

数据传输需要身份验证

与其他 BigQuery 集成一样，数据传输需要身份验证。

幸运的是，由于传输最初是在 UI 中配置的，因此这是一个简单的身份验证流程。
使用 Oauth2，设置 BigQuery 传输的用户需要登录并验证连接到 Google Cloud Platform 的帐户。
经过身份验证后，开发人员可以从 Google 产品和报表类型的下拉列表中进行选择。

为了便于使用生成的数据，该过程中最重要的方面之一是为生成表的尾缀提供一个难忘的名称。

注意：某些报表（如计费报表）不允许用户更改导入表的名称。

报表示例

可以作为数据传输计划的报表示例包括：

DFP
Google Ad Manager
YouTube 频道报表

这些现成的报表可以消除某些 BigQuery 兼容数据资源的开发负担。

BigQuery - 物化视图

除了创建表和视图之外，BigQuery 还支持创建物化视图。

什么是物化视图？

物化视图类似于表，因为它也是数据的“快照”。但是，物化视图的不同之处在于，真正的物化视图会动态更新——无需运行查询即可。

物化视图的类型

从广义上讲，有两种类型的物化视图：

已保存为表的视图，并将定期从外部源更新。
在 BigQuery Studio 中创建的“真正”的物化视图。

以下是第一种视图的示例架构：

SQL 查询现有视图
在 Python 脚本中，该视图被转换为数据框
数据框上传到 BigQuery
物化视图被追加或覆盖

由于以上概述了一个多步骤过程，因此 BigQuery 简化了物化视图的创建和维护。

创建 BigQuery 物化视图

用户可以通过运行CREATE MATERIALIZED VIEW SQL 语句，后跟以下内容来创建 BigQuery 物化视图：

项目
数据集
新的 mv 名称
SQL 语句

示例

这是一个示例，其中物化了包含假设销售数据的现有表：

根据 BigQuery 文档，请注意以下限制：

每个表在一个数据集内最多有 20 个物化视图
一个项目中最多只能有 100 个物化视图
一个组织中最多只能有 500 个物化视图

在 BigQuery 中编写简单的 SQL 脚本

现在将所有内容整合在一起，是时候编写一个简单的脚本，该脚本将：

利用动态变量
删除昨天的数据
将新数据插入表中
使用查询选择/加载数据

到目前为止，尚未介绍如何在 SQL 脚本中定义和使用变量。

在 BigQuery 中，变量语法如下：

DECLARE variable_name TYPE DEFAULT function used to create dynamic variable

例如：

DECLARE yesterday DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

我们将以此作为以下脚本的开头，该脚本将删除奥斯汀共享单车分区表中的先前数据，并仅插入最新数据。

在 BigQuery 中运行此脚本时，由于分号的存在，SQL 引擎将分阶段运行它。可以通过点击“查看结果”查看最终结果。

点击“查看结果”将生成此输出。

最后，我们可以看到添加到表中的新行。

BigQuery - 角色和权限

在 BigQuery Studio UI 中运行查询看似非常简单。由于开发人员已登录其 Google Cloud Platform 帐户，因此无需进行身份验证。但是，在幕后，某些限制和防护措施确保开发人员只能在项目中执行某些操作。

身份访问和管理 (IAM) 角色

这些限制或指定称为角色和权限。在 GCP 中，这些称为身份访问和管理 (IAM) 角色。

从广义上讲，这些角色分为 3 个层级：

BigQuery 管理员
BigQuery 数据编辑器
BigQuery 用户

1. BigQuery 管理员

BigQuery 管理员可以在项目中执行任何操作，例如创建或删除表以及启动和停止作业的运行——即使是由其他用户启动的作业。

2. BigQuery 数据编辑器

BigQuery 数据编辑器的权限略少。虽然他们可以读取、更新和删除表或视图，但他们缺乏项目级别的控制和权限，并且无法控制其他用户的作业。

3. BigQuery 用户

BigQuery 用户是 BigQuery IAM 角色的最低层级。在访问和操作资源方面，他们的权限非常有限。他们有限的能力包括：列出表和访问元数据。

自己使用 BigQuery 无需了解任何这些角色或权限。但是，当您使用企业级数据时，了解角色和权限可以帮助加快解决访问问题或在配置服务帐户时派上用场。

BigQuery：策略标签和 PII

就像 BigQuery 管理员可以授予权限并影响拥有较低级别访问权限的用户一样，他们还可以控制个人可以查看和交互的数据。这可以通过使用策略标签来实现。

什么是策略标签？

策略标签本质上是组织数据的审查栏。管理员可以应用此标签来阻止组织内的用户访问敏感数据。虽然确定构成敏感数据的部分内容是主观的，但也存在客观定义。

什么是个人身份信息 (PII)？

在数据治理中，敏感数据称为个人身份信息 (PII)。PII 包括任何可用于立即和密切识别特定个人的属性。它包括以下信息：

电话号码
生物识别信息
电子邮件
社会安全号码（美国）
信用卡号码

以上任何信息都被视为极其敏感的信息，必须小心保管。为了指导保护，GCP 在其数据治理产品 Data Loss Prevention 的文档中识别了 150 多个 PII 属性。

可以配置策略标签

策略标签也可以用于保护组织免受不应访问业务关键信息（如收入数据）的内部用户的侵害。

可以通过以下方式配置策略标签，然后在 BigQuery 中应用：

选择一个表
点击“编辑架构”
选择所有可能包含敏感信息的列
应用已配置的策略标签

开发人员可以知道何时应用了此类标签，因为它将在表架构中字段名称旁边显示为灰色框。

BigQuery - 查询优化

BigQuery 由云计算提供支持，但这并不意味着计算能力是无限的。这也不意味着每个查询都会在相同的时间内运行，无论一天中的时间或有多少进程在争夺插槽。

什么是查询优化？

优化是一个经常被数据工程和其他编程学科的人员使用的流行词。

使用SQL，优化有两种形式：

基于代码的优化
基于平台的优化

基于代码的优化概念化和执行起来很复杂。因此，它超出了本教程的范围。相反，我们将重点介绍 BigQuery 中允许用户准确跟踪和主动抑制过度使用的工具。

通过提高可见性和创造性的插槽分配，可以维护一个具有多个用户的 BigQuery 项目，并为所有用户提供足够的存储空间和插槽空间。这可以通过以下方式实现：

通过执行图和数据血缘工具跟踪使用情况。
以不同的模式运行查询以减少在给定时间处理的数据量。
利用 BI Engine 等工具对重复违规者（表）进行细分，以主动限制在高流量时段处理的数据范围。

批处理与交互式模式

在 BigQuery Studio SQL 环境中编写了前几个查询后，您可能会觉得所有查询都以相同的方式运行。从某种意义上说，您是正确的。所有查询在执行期间都会使用一定数量的插槽，以插槽小时表示。但是，实际上有两种不同的方法可以运行 BigQuery 查询以节省处理和成本。

大多数 BigQuery 查询是在所谓的交互式模式下执行的。事实上，这是 BigQuery 查询的默认执行状态。并且 UI 没有使更改模式的能力可见或明显。要查看或更改查询的执行模式，需要导航到查询设置。

在该视图中，您可以配置下一个查询运行。除了选择查询模式外，开发人员还可以选择如何保存查询的结果，可以选择从临时表、新的 BigQuery 表或覆盖现有表的內容等选项。在这些选项下方是选择批处理与交互式的菜单。

虽然交互式模式会立即执行查询，但批处理模式允许用户：

排队所需的 BigQuery 作业。
运行较低优先级的查询，而不会影响较高优先级的作业（这些作业可能会消耗更多资源）。

运行批处理模式作业可以帮助用户规避 BigQuery 查询执行限制：用户最多可以同时运行 20 个查询。

如果批处理作业与正在进行的交互式作业争夺插槽，则批处理作业将被挂起或“排队”，直到有可用空间。这有助于节省资源并避免达到对交互式查询施加的硬性速率限制。

BigQuery - BI 引擎

除了 BigQuery 中的优化设置外，BigQuery 还提供了一个并发服务BI Engine，其目的是扫描和优化 BigQuery 查询性能。

BI Engine 是一种内存中服务，用于分析正在运行的作业范围与执行时可用的插槽和计算资源的数量。
BI Engine 不仅分析查询资源，而且在分配可用资源后主动加速其执行（因此称为“引擎”）。
BI Engine 是可配置和可自定义的，这意味着开发人员可以选择将其范围内的表和视图。
BI Engine 是 BigQuery 中的一个产品。要访问 BI 着陆页，只需在 Cloud Console 搜索栏中搜索“BI Engine”即可。

BI Engine 页面将提示您创建预留。

点击“创建预留”后，您将能够配置要在 BI Engine 范围内设置多少千兆字节，并添加要在 BI Engine 的性能优化功能范围内包含的表。

BI Engine 的查询加速

此外，BI Engine 与 BigQuery API 同步，为从自动化流程加载、更新或修改的表提供查询加速优势。

BI Engine 最大的成就就是矢量化运行时，它允许它利用云 CPU 并能够压缩数据以实现无缝运行。

BI Engine 的真正强大之处在于它能够与 BigQuery 相关平台和应用程序集成。例如，基于 BigQuery 查询创建数据的 Looker 仪表板将有资格获得BI Engine 加速。

BI Engine 的用例

对于具有经常查询的大量数据表的用户，BI Engine 将最受益。

BI Engine 的用例包括：

由 BigQuery 提供支持的资源密集型可视化。
您有一些特定的大型且经常被查询的表格。
多个用户在相似的时间查询资源，导致性能瓶颈。

无论如何，BI 引擎仍然是任何寻求提高流程效率并降低计算密集度的用户强大的优化策略。

BigQuery - 监控使用情况和性能

了解使用情况和性能限制至关重要，尤其是在组织内部工作的人员。持续关注使用不成比例的插槽时间的用户和数据源，可以帮助 BigQuery 管理员做出明智的决策，限制对资源的访问，并在运行资源密集型作业的团队之间引发富有成效的对话。

性能仪表板

为了实现更透明的实时监控，BigQuery 在 BigQuery Studio UI 的监控选项卡中提供了性能仪表板。

注意 - 每次执行查询时都会绘制性能图表。

此外，BigQuery 还提供了一个执行图，以便更直观地解释查询性能。

在项目级别，管理员可以在“监控”仪表板中查看 BigQuery 数据。监控可在项目级别和数据集级别使用。

项目级监控

项目级别显示当前正在运行或“进行中”的查询数量。

数据集级监控

在数据集级别，我们可以看到每个表的存储字节数。

要访问和操作这些图表，请按照以下步骤操作 -

导航到 BigQuery
选择管理
点击监控
如果需要：切换实时数据

在此 UI 中，用户还可以访问运行状况，它以两种不同的视图提供信息：摘要视图和更详细的视图。

管理员和用户都会发现操作运行状况摘要表中的一些主要指标很有帮助，包括 -

插槽使用情况
混洗（预留重新分配）使用情况
并发性（同时作业）
处理的字节数
作业持续时间
总存储量

注意 - 使用单位为字节（如有必要，可以转换为千兆字节或太字节）。

要获得更细粒度的视图，用户可以使用本教程前面部分的元数据查询技术查询 INFORMATION_SCHEMA 视图。

BigQuery 的常见错误

尽管掌握了有关 BigQuery 功能的教育和信息，但与任何开发过程一样，错误是可能的，事实上，错误是不可避免的。鉴于新用户缺乏对平台的经验，他们特别容易受到 BigQuery 错误的影响。

BigQuery 错误类型

BigQuery 错误分为两类 -

基于代码的错误
基于平台的错误

虽然 BigQuery 的 SQL 方言（Google SQL）旨在得到普遍理解并让人想起其他 SQL 方言，但语法错误可能会发生，坦率地说，这可能会令人非常沮丧。

BigQuery 语法错误

这是一个 BigQuery 语法错误的非详尽列表 -

使用撇号而不是反引号。
在 FROM 子句中省略数据集或表格。
错误地使用 UNNEST()（在 STRUCT 而不是 ARRAY 上）。
在使用 AVG() 等聚合函数时忘记 GROUP BY 子句。
在列名之间忘记逗号。

基于平台的错误

基于平台的错误源于对 BigQuery 执行约束的误解，可能包括 -

运行超过 20 个并发查询。
未将大型查询的结果写入表中，导致“结果过大”错误。
未将大型查询作为批处理作业运行。
覆盖或截断表而不是追加结果。

不幸的是，不可能标记新 BigQuery SQL 开发人员将遇到的几乎所有错误。但是，以上代表了您可能遇到的各种问题。

BigQuery - 数据仓库

对于许多组织而言，BigQuery 是数据仓库的自然选择。数据仓库是一个业务系统和中央存储库，用于存储数据以进行分析，然后进行下游报告。

注意通常，存储在数据仓库中的数据是结构化的或半结构化的，而不是数据湖，后者存储非结构化数据。

BigQuery 连接到Looker或Tableau等可视化平台的能力使其成为为企业仪表板和临时报告提供支持的理想引擎。应用分区和聚类等存储优化功能意味着数据团队可以自信且高效地存储数据数年或数十年，而无需担心性能下降。能够与 Python 和 JavaScript 等脚本语言集成，允许软件工程师、数据架构师和数据工程师等专业人员创建自动化的定期加载作业。

将 BigQuery 与 Google Sheets 等其他应用程序集成，使存储在 BigQuery 中的数据对可能更喜欢或专门使用电子表格的非技术利益相关者更可见且更易访问。

利用BigQuery API，开发人员可以将 BigQuery SQL与程序逻辑相结合以生成自定义见解。

能够按需添加插槽、升级存储和加速查询，例如使用BI引擎，对于最初构建或扩展其数据基础设施的组织来说是一个有吸引力的建议。

这些功能的缺点是成本。但是，通过固定和按使用付费的定价模式，业务用户和决策者可能会发现使用面向 BigQuery 的数据仓库的成本效益是值得的。

下图由 Google Cloud 提供，说明了如何构建和实现面向基本数据仓库的解决方案。

(来源：https://cloud.google.com/architecture/big-data-analytics/data-warehouse)

除了数据源、应用程序和数据分析之外，此图还可以细分为更具体的类别并解释为 -

上游源（第三方 API 或外部数据源）。
中间/暂存存储（Cloud Storage 存储桶）。
永久/长期存储（BigQuery）。
ML/AI 应用程序（Vertex AI、Cloud Functions 和 Compute Engine 虚拟机）。
下游用户：通过 Looker 等可视化平台访问模型和查询输出的业务用户。

在任何一种解释中，BigQuery 都是包含数据在生成下游用户业务价值之前的数据的中央存储库或“数据仓库”。

BigQuery - 挑战与最佳实践

作为一种云计算工具，BigQuery 也并非没有挑战。在本简短章节中，我们试图突出 BigQuery 面临的一些显着挑战。

从业务角度看 BigQuery

从业务角度来看，关于 BigQuery 的最大障碍之一是确保领导层同意测试、调整或扩展平台以满足特定组织的需求。

许多企业满足于依赖本地数据存储，并且不考虑云存储选项。
或者，更糟糕的是，企业甚至可能不认为任何形式的数据仓库都是其大数据存储的可行解决方案。
业务领导者可能会将 BigQuery 的可变成本视为潜在的资源消耗，尤其是在其组织中有许多开发人员、工程师、架构师和最终用户依赖存储在 BigQuery 中并从中查询的数据时。

从用户角度看 BigQuery

从用户的角度来看，BigQuery 存在一定的学习曲线。

可用的两种 SQL 版本，标准 SQL和传统 SQL，意味着那些使用过其他 SQL 方言的人可能会对需要启用哪种模式来运行给定查询或使用特定函数感到困惑。
当尝试将基于 BigQuery 的数据仓库与外部连接（如 Google Sheet 或 BigQuery API）集成时，使用 BigQuery 进行开发可能会具有挑战性。
尽管在执行时间前后提供了对所消耗资源和其他性能指标的可见性，但 BigQuery 的错误日志可能很模糊，这在进行故障排除时会导致沮丧——尤其对于较新的开发人员而言。

BigQuery 最佳实践

为了避免或克服这些挑战，有必要了解和实施 BigQuery 最佳实践。充分利用 BigQuery 需要了解 Google Cloud Platform、云计算和一般的 SQL。

为了减少收到意外的高昂月度账单的可能性，请启用监控并经常查看过滤在 BigQuery 上的计费和使用情况仪表板。

与其不断地临时增加插槽，不如迫使开发人员考虑并在代码级别实施最佳实践。这可能包括 -

通过避免像“SELECT *”这样的广泛查询来减少处理的数据范围
选择高效的 SQL 查询设计模式以优化查询操作
避免计算密集型查询，例如那些使用通配符引用和过度元数据读取的查询
使用BigQuery 的 BI 引擎等可用工具来识别有问题的操作，并在必要时提供更高的性能提升
通过仅允许用户使用 WHERE 子句进行查询来为大型表格指定查询限制

掌握平台和 SQL 知识的用户将是构建、扩展和推广平台的人，将 BigQuery 的强大功能添加到他们的个人技能和组织技术栈中。

打印页面