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 表中时遇到的另一个挑战是存在特殊字符，例如换行符" "。虽然删除这些字符并通过编程充分清理数据始终是明智之举，但 BigQuery 提供了过滤或完全忽略可能导致文件上传错误的行的方法。
- 首先，BigQuery 允许开发人员指定一个整数值，表示允许的错误数。如果有一个特定的坏行不包含任何后续数据，这可能会很有帮助。
- 此外，BigQuery 提供了一个参数，用于跳过标题行，包括带引号的换行符以及允许锯齿状或格式错误的行。
不幸的是，即使有了这些选项，确定文件是否上传的唯一方法也只是反复试验。
BigQuery Studio:通过 SQL 语句填充表
根据情况，有两种方法可以从"头"填充表，即简单的 SQL 语句。
1. CREATE OR REPLACE 命令
第一个命令 CREATE OR REPLACE 已经介绍过了。在这种情况下，开发人员正在创建一个全新的表，除了表内容之外，还必须定义表模式。通常，CREATE OR REPLACE 最适合用于聚合或扩展 SQL 表中已存在的数据。
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 表格结合使用。
探索外部表格源的下拉菜单将显示"表格"，开发人员可以使用该源来填充表格数据。
Google 表格作为主要数据源
使用 Google 表格作为主要数据源非常有用，因为与静态 CSV 不同，表格是动态实体。这意味着对连接表格中的行所做的任何更改都将反映在关联的 BigQuery 表中 - 所有这些都是实时的。
要将 Google 表格连接到 BigQuery 表，请按照原始的创建表流程进行操作。您需要选择"驱动器"作为源，而不是选择从上传创建表。尽管 Google Sheets 是在 Sheets UI 中创建的，但它们存在于 Google Drive 中。
与 CSV 一样，连接的工作表必须具有定义的标题。但是，与 CSV 不同的是，开发人员可以指定要包含和省略的列。
语法类似于创建和执行 Excel 公式。只需输入列字母和行号。要选择给定行之后的所有行，请在列前面加上感叹号。
例如:!A2:M
使用相同的语法，您甚至可以在提供的表中选择不同的选项卡。例如 −
"Sheet2 !A2:M"
与使用 CSV 一样，您可以指定要忽略哪些行和标题，以及是否允许引用新行或锯齿行。
重要警告
INSERT 或 DROP 等 DML 语句不适用于连接的工作表。要省略某一列，您需要在工作表中隐藏它或在初始配置中指定它。
