pyspark - 如何在 GCS 中的增量表之上创建 BQ 外部表并仅显示最新快照

Question

我正在尝试在使用谷歌存储作为存储层的增量表之上创建一个外部 BQ 外部表。在增量表上，我们执行包含删除的 DML。

我可以在所有增量文件都存在的 gs 存储桶之上创建一个 BQ 外部表。但是，它甚至拉取删除记录，因为 BQ 外部表无法读取 delta 的事务日志，其中它说明要考虑哪些 Parquet 文件以及要删除哪个 Parquet 文件。

除了以编程方式将数据从 delta 复制到 BQ 之外，有没有一种方法可以将 BQ 中 delta 表(gs 位置)的最新快照公开为外部表？

Answer 1

所以这个问题是在一年多以前提出的，但我对 Oliver 的回答进行了巧妙但强大的补充，消除了数据重复和额外的加载逻辑。

第 1 步 按照 Oliver 的建议生成 symlink_format_manifest 文件；您可以在每次更新时触发它，也可以按照 here 的说明将 tblproperty 添加到您的文件中到更新增量表时自动创建这些文件；

ALTER TABLE delta.`<path-to-delta-table>` SET TBLPROPERTIES(delta.compatibility.symlinkFormatManifest.enabled=true)

第 2 步创建指向增量表位置的外部表

> bq mkdef --source_format=PARQUET "gs://test-delta/*.parquet" > bq_external_delta_logs
> bq mk --external_table_definition=bq_external_delta_logs test.external_delta_logs

第 3 步 创建另一个指向 symlink_format_manifest/manifest 文件的外部表

> bq mkdef --autodetect --source_format=CSV gs://test-delta/_symlink_format_manifest/manifest > bq_external_delta_manifest
> bq mk --table --external_table_definition=bq_external_delta_manifest test.external_delta_manifest

第 4 步使用以下查询创建 View

> bq mk \
--use_legacy_sql=false \
--view \
'SELECT
  *
FROM
  `project_id.test.external_delta_logs` 
WHERE
  _FILE_NAME in (select * from `project_id.test.external_delta_logs`)' \
test.external_delta_snapshot

现在，只要您的增量表从 test.external_delta_snapshot View 刷新，您就可以获得最新的快照，而无需任何额外的加载或数据复制。 这个解决方案的一个缺点是，如果模式发生变化，您必须手动或使用 BQ 客户端等从您的 spark 管道向表定义添加新字段。对于那些对这个解决方案如何感到好奇的人有效，请继续阅读。

---

这是如何运作的；

符号链接(symbolic link) list 文件包含指向当前增量版本分区的新行分隔格式的 parquet 文件列表；

gs://delta-test/......-part1.parquet
gs://delta-test/......-part2.parquet
....

除了我们的增量位置之外，我们通过将此 list 文件视为 CSV 文件(它实际上是一个单列 CSV 文件)来定义另一个外部表。我们定义的 View 利用了提到的 _FILE_NAME 伪列 here ，它指向表中每一行的 Parquet 文件位置。如文档中所述，_FILE_NAME 伪列是为指向存储在 Cloud Storage 和 Google Drive 中的数据的每个外部表定义的。

所以在这一点上，我们有加载最新快照所需的 parquet 文件列表，并且能够使用 _FILE_NAME 列过滤我们想要读取的文件。我们定义的 View 只是定义了这个过程来获取最新的快照。每当我们的增量表更新时， list 和增量日志表都会查找最新的数据，因此我们将始终获得最新的快照，而无需任何额外的加载或数据重复。

最后，众所周知，在外部表上执行比 BQ 托管表更昂贵(执行成本)，因此最好像 Oliver 建议的那样尝试双写入，并像您要求的那样尝试外部表解决方案。存储比执行成本更低，因此在某些情况下，将数据保存在 GCS 和 BQ 中的成本可能低于像这样保存外部表的成本。