在 DuckDB 中读写 Google 表格

电子表格无处不在

对于数据从业者来说，还有什么比电子表格更具争议性吗？等等，别回答，我们没时间再谈论前导和后导逗号了……

事实是，电子表格无处不在。据估计，全球有超过 7.5 亿电子表格用户，而程序员数量只有 2000 万到 3000 万。这包括所有语言的总和！

使用电子表格可以改善数据工作流的方式有很多。你说是亵渎！好吧，想象一下如果你的数据库真的可以读写这些电子表格。电子表格通常是手动编辑数据的最佳场所，它们还为自助分析提供了高度可定制的透视功能。

现在，你可以使用 DuckDB 无缝地连接数据人员和业务人员！通过简单的浏览器内认证流程，或者可自动化的私钥文件流程，你可以既从 Google 表格查询数据，也能将数据加载到其中。

此 GSheets 扩展 最初由来自 Evidence 团队的 Archie 编写，但自那以后，Alex 和 Michael 也做出了重大贡献。

GSheets 扩展入门

前几个步骤是安装 gsheets 社区扩展 并使用 Google 进行身份验证。

INSTALL gsheets FROM community;
LOAD gsheets;

-- Authenticate with a Google Account in the browser (default)
CREATE SECRET (TYPE gsheet);

作为 CREATE SECRET 命令的一部分，将打开一个浏览器窗口，允许你登录并复制一个临时令牌，然后将其粘贴回 DuckDB。

从表格读取数据的示例

现在你已经通过身份验证，DuckDB 可以查询你的 Google 帐户有权访问的任何表格。这包括任何公开可用的表格，例如下面这个，所以快去运行一下吧！

FROM 'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit?gid=0#gid=0';

在工作簿中查看感兴趣的表格时，复制该表格的 URL 以进行查询。gid 查询字符串参数是该特定表格的 ID。

有两种方法可以传入额外参数

1. 将它们作为查询字符串参数添加到 URL 的末尾，或者
2. 使用 read_gsheet 表函数并将它们指定为单独的 SQL 参数。

此 仓库 README 中包含各种示例，下方列出了一些！

查询字符串参数必须放在 ? 之后。每个参数都格式化为 key=value 对，多个参数之间用 & 分隔。

读取特定表格和范围

默认情况下，GSheets 扩展会读取工作簿中第一个表格上的所有数据。sheet 和 range 参数（或其查询字符串等效项）允许进行有针对性的读取。

例如，要只读取 We <3 Ducks 表格上的前 3 个单元格，以下两个语句是等效的

-- The sheet with the gid of 0 is named 'We <3 Ducks' (because of course it is!)
FROM read_gsheet(
    'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit',
    sheet = 'We <3 Ducks',
    range = 'A1:A3'
    );

FROM 'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit?gid=0#gid=0&range=A1:A3';

Google Sheets API 会自动跳过数据集末尾的空行或右侧的空列，这很方便。如果你的数据可能会增长，请随意指定一个稍大的 range！此外，range 也可以指定为一组列（例如，D:X），以便更好地适应可变行数的情况。

数据类型

该扩展会采样表格中的第一行数据，以尝试确定列的数据类型。（我们计划改进此采样方法，并欢迎贡献！）要跳过此步骤并在 SQL 中定义数据类型，请将 all_varchar 参数设置为 true。下面的示例还演示了不需要完整的 URL——只需 Google 工作簿标识符。

FROM read_gsheet(
    '1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE',
    sheet = 'We <3 Ducks',
    range = 'A:A',
    all_varchar = true
    );

还可以通过将 header 参数设置为 false 来查询不带标题行的数据。列将获得默认名称，并且可以在 SQL 中重命名。

写入 GSheet 的示例

GSheets 扩展的另一个关键功能是将任何 DuckDB 查询的结果写入 Google 表格！

默认情况下，整个表格将被查询的输出替换（包括列名的标题行），从第一个表格的 A1 单元格开始。请参阅下面的示例，了解如何调整此行为！

-- Here you will need to specify your own Sheet to experiment with!
-- (We can't predict what folks would write to a public Sheet...
-- Probably just memes, but there is always that one person, you know?)
COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...'  (
    FORMAT gsheet
);

写入特定表格和范围

与读取类似，查询字符串参数和 SQL 参数都可以用于写入特定的 sheet 或 range。同样，SQL 参数具有优先权。这些示例是等效的

COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...?' (
    FORMAT gsheet,
    sheet 'The sheet name!',
    range 'A2:Z10000'
);

COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...?gid=123#gid=123&range=A2:Z10000' (
    FORMAT gsheet
);

header 布尔参数也可以用来确定是否应写入列名。

覆盖或追加

有时，在复制之前避免清除表格中的其他数据会很有帮助。这在写入特定范围时特别方便。也许 C 列和 D 列可以来自 DuckDB，而其余部分可以是电子表格公式。如果只清除 C 列和 D 列就太好了！

要调整此行为，请将这些布尔参数传入 COPY 函数。OVERWRITE_SHEET 是默认值，表示在复制前清空整个表格。OVERWRITE_RANGE 只会清除指定范围。

如果两者都设置为 false，则数据将追加，而不会清除任何其他单元格。通常，在追加数据时，不希望在输出中包含列标题。很方便的是，在追加情况下，header 参数默认为 false，但如果需要，可以进行调整。

-- To append, set both flags to false.
COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...?gid=123#gid=123&range=A2:Z10000' (
    FORMAT gsheet,
    OVERWRITE_SHEET false,
    OVERWRITE_RANGE false
    -- HEADER false is the default in this case!
);

自动化工作流

使用电子表格对于即席工作非常有用，但当其融入自动化流程时，它也会变得非常强大。如果你想安排与 Google 表格的交互，则需要一个包含私钥的密钥文件，而不是浏览器内认证方法。

获取此密钥文件后，持久化的私钥必须每 30 分钟转换为一次临时令牌。该过程现已通过 key_file 密钥提供程序实现自动化。使用如下命令创建密钥，指向从 Google 导出的 JSON 文件。

CREATE OR REPLACE PERSISTENT SECRET my_secret (
    TYPE gsheet,
    PROVIDER key_file,
    FILEPATH 'credentials.json'
);

创建密钥后，私钥将存储在 DuckDB 中，并生成一个临时令牌。密钥可以存储在内存中，也可以选择使用 PERSISTENT 关键字持久化到磁盘（未加密）。临时令牌也缓存在 SECRET 中，如果超过 30 分钟，它将被重新创建。

这使得 GSheets 扩展可以在 GitHub Actions (GHA) 或 dbt 等其他编排工具的管道中使用。最佳实践是将 credentials.json 文件作为秘密存储在你的编排工具中，并将其写入临时文件。GHA 工作流示例在此，它使用此 Python 脚本来查询表格。

开发扩展

Google Sheets 扩展很好地展示了 DuckDB 的扩展 GitHub 模板和 CI/CD 工作流如何让即使是非 C++ 专家也能为社区做出贡献！到目前为止贡献的几位开发者（谢谢你们！！），包括本文的作者，都不是传统的 C++ 程序员。结合优秀的模板、其他扩展的示例，以及一些由 LLM 驱动的“初级开发者”的帮助，这一切成为可能。我们鼓励你尝试实现你的扩展想法，如果需要帮助，请在 Discord 上联系我们！

路线图

我们正在为该扩展考虑更多有趣的功能——我们欢迎 PR 和合作者！

我们希望在从表格读取数据时使用更好的启发式方法来检测数据类型。DuckDB 的类型系统比 Sheets 更高级，因此提高精度会很有益。

使 GSheets 扩展在 DuckDB-Wasm 中工作将允许浏览器内应用程序直接查询表格——无需服务器！一些 http 函数需要一些修改才能在浏览器环境中工作。

支持基于浏览器的登录的 OAuth 流程可能对其他 API 的身份验证有用。我们正在考虑是否有可能开发一个通用的 OAuth 社区扩展。目前对此没有具体计划，但如果有人感兴趣，请联系我们！

结束语

在 MotherDuck（Alex 的工作地点），我们已将此扩展用于生产环境中的多个内部数据管道！我们已将预测数据从仓库自动导出到 Sheets，并持续将手动收集的客户支持数据加载到我们的（由 MotherDuck 支持的）数据仓库中。因此，我们的 KPI 仪表板包含了与客户直接交流的人员提供的信息！

Michael Harris 也为该扩展做出了贡献（谢谢！），并且 Definite 已为多个客户在生产环境中部署了 GSheets 计划任务！

你在数据分析工作流中如何使用 Google 表格？DuckDB 又能如何提供帮助？我们很乐意在 BlueSky、LinkedIn 或 X / Twitter 上听到你的想法！

现在，用 SQL 自动化你的表格吧！