如何在R中使用DuckDB或Arrow连接远程CSV文件？

目标 #

连接大型远程CSV文件以查询数据子集。

某页面提供CSV下载按钮，复制链接后用以下代码可将数据加载到内存：

foo <- data.table::fread("https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD")

尝试过程 #

1. dbplyr + DuckDB #

执行以下代码创建连接：

library(DBI)
library(dbplyr)
library(tidyverse)
library(arrow)
library(duckdb)

(con <- DBI::dbConnect(duckdb::duckdb(), 
                  dbdir = ":memory:",
                  "https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD"))
# <duckdb_connection f96e0 driver=<duckdb_driver fd1f0 dbdir=':memory:' read_only=FALSE bigint=numeric>>

但查询数据库表列表结果为空：

> dbListTables(con)
character(0)

2. Arrow #

执行open_dataset时触发错误：

> open_dataset(sources = "https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD")
Error: Invalid: Unrecognized filesystem type in URI: https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD

问题 #

请问这是数据提供方的限制，还是我使用这些包的方式有误？

解答 #

这是工具使用方式有误，并非数据提供方限制，以下是正确用法：

DuckDB + dbplyr 正确流程

DuckDB的dbConnect不支持直接传入远程CSV链接作为参数，需先创建内存连接，再显式导入CSV为数据库表：

library(DBI)
library(duckdb)
library(dbplyr)

# 建立内存数据库连接
con <- dbConnect(duckdb::duckdb(), dbdir = ":memory:")

# 将远程CSV导入为数据库表（表名自定义为ngsim_data）
duckdb_read_csv(
  con,
  table_name = "ngsim_data",
  files = "https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD"
)

# 此时可查询到表
dbListTables(con)
# [1] "ngsim_data"

# 用dbplyr进行子集查询（仅加载所需数据到内存）
ngsim_tbl <- tbl(con, "ngsim_data")
subset_data <- ngsim_tbl %>% 
  filter(Velocity > 20) %>%  # 示例过滤条件
  select(Vehicle_ID, Time, Velocity) %>%  # 示例选择列
  collect()

Arrow 正确流程

open_dataset默认针对云存储上的分区数据集设计，直接读取单个HTTPS CSV需显式处理文件流，或先临时缓存文件：

library(arrow)
library(curl)

# 方法1：通过curl创建文件流直接读取
csv_stream <- curl("https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD")
data <- read_csv_arrow(csv_stream)

# 方法2：缓存到临时文件后用open_dataset（适合后续分区操作）
temp_csv <- tempfile(fileext = ".csv")
download.file(
  url = "https://datahub.transportation.gov/api/views/8ect-6jqj/rows.csv?date=20231118&accessType=DOWNLOAD",
  destfile = temp_csv
)
ds <- open_dataset(temp_csv, format = "csv")
subset_data <- ds %>% 
  filter(Lane > 1) %>% 
  collect()

补充说明

data.table::fread能直接读取远程链接，是因为它内部封装了远程文件的下载与解析逻辑；而DuckDB和Arrow需要显式指定导入/读取步骤，无法在连接/初始化时直接识别远程CSV链接。

内容的提问来源于stack exchange，提问作者umair durrani