DuckDB: 从GitHub批量抽取CSV文件

Jeff Sackmann的tennis_atp存储库是最好的网球数据集合之一，我想使用DuckDB CLI获取ATP巡回赛单打比赛数据。在这篇博文中，我们将学习如何批量获取github远程csv文件数据。

提出需求

通常，当我将数据抽取到DuckDB中时，我会使用通配符语法指定要摄取的文件。在这种情况下，这意味着运行这样的查询：

CREATE OR REPLACE TABLE matches AS
SELECT * FROM "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_*.csv"

但我们不能对GitHub上的文件使用这种技术，因为它不是文件系统。如果我们运行上面的查询，我们将得到以下错误消息：

HTTP Error: Unable to connect to URL "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_*.csv": 404 (Not Found)

因此，我们需要构造一个url列表，这可以使用generate_series和list_transform函数的组合来实现。

构造列表

我们将使用generate_series来创建年份列表，如下所示：

D SELECT range(1968, 1970);
┌───────────────────┐
│ range(1968, 1970) │
│      int64[]      │
├───────────────────┤
│ [1968, 1969]      │
└───────────────────┘

也可以使用generate_series函数：

SELECT generate_series(1968, 1969);  # generate_series函数包括结尾变量

然后我们可以使用list_transform来映射或投影每个值来构造一个URL：

SELECT list_transform(
  range(1968, 1970),
  y ->  'https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_' || y || '.csv'
) AS files;

输出如下：

files
[https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_1968.csv, https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_1969.csv]

批量抽取数据

最后，我们可以把所有这些放在一起，消化从1968年到2023年的文件：

CREATE OR REPLACE TABLE matches AS
SELECT * FROM read_csv_auto(
    list_transform(
      range(1968, 2023),
      y ->  'https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_' || y || '.csv'
    ),
    types={'winner_seed': 'VARCHAR', 'loser_seed': 'VARCHAR'}
);

winner_seed和loser_seed是 CSV 文件中的列名。在导入过程中，DuckDB 会根据这个映射关系，将winner_seed列的数据存储为VARCHAR（可变长度字符串）类型，将loser_seed列的数据也存储为VARCHAR类型。这需要几秒钟的时间，但一旦完成，我们可以做一个探索性查询，以确保所有内容都被摄入：

SELECT count(*) FROM matches;