BigQuery - 填充表

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

BigQuery 接受的文件类型

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

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

接受的文件类型包括：

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

使用外部文件（尤其是 CSV）的挑战之一是 BigQuery 对某些数据类型特别讲究。

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

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

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

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

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

如果您发现自己有一个表的“外壳”，包括一个定义良好的模式但还没有添加数据，并且您没有像外部文件或 API 负载这样的来源，那么添加数据的方法是使用 INSERT 命令。

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

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

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

UNION 有两种：

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

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

正如现在所看到的那样，BigQuery 中的查询与在本地环境中编写 SQL 不同，部分原因是开发人员能够将 BigQuery 数据与 GCP 的基于云的工具同步。最强大和最直观的集成之一是将 BigQuery 与 Google Sheets 结合使用。

浏览外部表源的下拉菜单将显示“表格”作为开发人员可以使用的数据源来使用数据填充表。

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

要将 Google 表格连接到 BigQuery 表，请遵循原始创建表的过程。您无需从上传中选择创建表，而是需要选择“驱动器”作为源。即使 Google 表格是在表格 UI 中创建的，它们也位于 Google Drive 中。

与 CSV 一样，连接的表格必须具有已定义的标题。但是，与 CSV 不同的是，开发人员可以指定要包含和省略的列。

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

Like: !A2:M

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

"Sheet2 !A2:M"

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

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

打印页面