如何在DuckDB中根据file_search_path动态写入CSV文件？

我需要在多个结构相同的目录下重复执行分析任务，只需要修改file_search_path配置项就能正常运行。现在的问题是如何动态生成不同命名的CSV导出文件。

首先我会修改配置项：

set file_search_path to '~/Downloads/some-folder/build-data/n-2000';

-- 目标写入路径：                       /output/result-n-2000.csv

硬编码路径的方式是可行的：

copy information_schema.tables to '~/Downloads/some-folder/output/result-n-2000.csv' ( header, delimiter ',')

但尝试通过子查询动态生成路径的方式无法正常工作：

copy information_schema.tables to (
    select 
        value || '/../../output/result-n-' || regexp_extract(value, '\d+$') ||'.csv' as x
        -- 预期生成路径： ~/Downloads/some-folder/build-data/n-2000/../../output/result-n-2000.csv
    from duckdb_settings() 
    where name = 'file_search_path' 
) (header, delimiter ',')

DuckDB的COPY语句不支持直接将子查询作为目标路径参数，你可以用以下几种方式实现动态路径生成：

方法1：使用DuckDB宏 #

通过宏封装动态路径生成逻辑，调用宏即可完成导出：

CREATE OR REPLACE MACRO export_tables() AS (
    COPY information_schema.tables TO (
        SELECT value || '/../../output/result-n-' || regexp_extract(value, '\d+$') || '.csv'
        FROM duckdb_settings()
        WHERE name = 'file_search_path'
    ) (HEADER, DELIMITER ',')
);

-- 执行导出
CALL export_tables();

方法2：使用脚本变量（命令行/脚本场景） #

先将路径值赋值给变量，再用变量执行COPY：

-- 提取当前file_search_path到变量
SET current_search_path = (SELECT value FROM duckdb_settings() WHERE name = 'file_search_path');
-- 生成目标路径变量
SET target_output_path = current_search_path || '/../../output/result-n-' || regexp_extract(current_search_path, '\d+$') || '.csv';
-- 执行导出
COPY information_schema.tables TO :target_output_path (HEADER, DELIMITER ',');

方法3：客户端参数化生成（如Python） #

如果用编程语言连接DuckDB，可以先获取配置值、生成路径，再执行导出：

import duckdb

# 建立连接
con = duckdb.connect()

# 获取当前file_search_path
search_path = con.execute("SELECT value FROM duckdb_settings() WHERE name = 'file_search_path'").fetchone()[0]
# 生成目标CSV路径
target_path = f"{search_path}/../../output/result-n-{search_path.split('-')[-1]}.csv"

# 执行导出
con.execute(f"COPY information_schema.tables TO '{target_path}' (HEADER, DELIMITER ',')")

con.close()

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