DuckDB 完全指南从入门到精通第一部分入门篇1.1 什么是 DuckDBDuckDB 是一个嵌入式、进程内、列式存储的 SQL 数据库管理系统DBMS。它的设计目标是提供一种高性能、易于使用且零配置的数据分析解决方案。核心特点嵌入式没有独立的服务器进程直接在应用程序如 Python、R、Node.js中运行。列式存储专为分析型查询OLAP优化在聚合、扫描大量数据时性能远超传统行式数据库如 SQLite。零配置无需安装、启动服务或创建用户。pip install duckdb即可使用。SQL 支持支持标准 SQL并扩展了针对分析的强大功能如窗口函数、列表/结构体操作、JSON 处理。与数据生态深度集成可以直接读取/写入 CSV、Parquet、JSON 文件甚至可以“直接查询”这些文件无需先导入表。1.2 安装与连接Python 环境最常用pipinstallduckdbCLI 环境命令行工具下载官方提供的duckdb可执行文件单文件放到系统路径后即可运行duckdb# 或打开指定数据库文件duckdb mydb.duckdb其他语言支持 R、Java、Node.js、C 等语法类似。1.3 第一个查询在 Python 中importduckdb# 1. 内存模式不持久化connduckdb.connect(:memory:)# 2. 执行 SQLresultconn.execute(SELECT 1 1).fetchall()print(result)# [(2,)]# 或使用快捷方式自动管理连接print(duckdb.sql(SELECT Hello, DuckDB! as greeting).fetchone())在命令行中SELECTHello, DuckDB!asgreeting;第二部分核心概念与基础操作2.1 数据库与连接DuckDB 支持两种数据库模式持久化数据库数据保存在.duckdb文件中。关闭连接后数据不丢失。connduckdb.connect(my_database.duckdb)内存数据库数据仅在内存中程序退出后消失用于临时分析。connduckdb.connect(:memory:)2.2 创建表与导入数据创建表conn.execute(CREATE TABLE users (id INTEGER, name VARCHAR, age INTEGER))# 插入数据conn.execute(INSERT INTO users VALUES (1, Alice, 30), (2, Bob, 25))直接从 CSV 创建表conn.execute( CREATE TABLE customers AS SELECT * FROM read_csv_auto(customers.csv) )注册 Pandas DataFrame将 Pandas DataFrame 注册为虚拟表可以直接查询importpandasaspd dfpd.DataFrame({id:[1,2],value:[10,20]})conn.register(my_df_view,df)resultconn.execute(SELECT * FROM my_df_view WHERE value 10).fetchdf()2.3 查询 CSV/Parquet 文件无需导入这是 DuckDB 的杀手锏特性可以直接在文件上进行 SQL 查询无需CREATE TABLE。# 直接查询 CSVduckdb.sql( SELECT PassengerId, AVG(Fare) FROM read_csv_auto(titanic.csv) GROUP BY PassengerId )# 直接查询 Parquet性能极高duckdb.sql( SELECT COUNT(*), PULocationID FROM read_parquet(yellow_tripdata_2021-01.parquet) GROUP BY PULocationID )第三部分进阶篇3.1 高级 SQL 特性DuckDB 的 SQL 引擎非常强大支持现代数据分析所需的几乎所有语法。窗口函数SELECTid,sales,ROW_NUMBER()OVER(PARTITIONBYregionORDERBYsalesDESC)asrank_in_region,SUM(sales)OVER(PARTITIONBYregion)astotal_sales_in_regionFROMsales_data;公共表表达式 (CTE)WITHhigh_value_ordersAS(SELECT*FROMordersWHEREamount1000)SELECTcustomer_id,COUNT(*)FROMhigh_value_ordersGROUPBYcustomer_id;处理嵌套数据Struct 和 ListDuckDB 原生支持复杂类型# 创建包含 List 的表duckdb.sql( CREATE TABLE orders ( id INT, items VARCHAR[] -- 数组类型 ); INSERT INTO orders VALUES (1, [apple, banana]); )# 展开数组UNNESTduckdb.sql(SELECT id, UNNEST(items) FROM orders).show()JSON 支持DuckDB 对 JSON 的处理性能极高甚至可以超过专门的 JSON 工具。-- 直接从 JSON 文件查询SELECTjson_extract_string(data,$.name)asname,json_extract_int(data,$.age)asageFROMread_json_auto(people.json);-- 使用 - 和 - 操作符类似 PostgreSQLSELECTdata-nameFROMpeople;3.2 数据导入与导出DuckDB 提供了多种高效的数据交换方式。导出到 Parquet推荐conn.execute( COPY (SELECT * FROM large_table) TO output.parquet (FORMAT PARQUET); )导出到 CSVconn.execute( COPY users TO users.csv (HEADER, DELIMITER ,); )从多种格式导入# 支持 CSV, JSON, Parquet, Arrow, Pandas, SQLite 等conn.execute(CREATE TABLE t1 AS SELECT * FROM data.parquet)conn.execute(CREATE TABLE t2 AS SELECT * FROM data.csv)3.3 与 Python 生态无缝集成DuckDB 可以高效地在 Pandas、Polars、Arrow 之间传递数据实现“零拷贝”交互。importpandasaspdimportpolarsaspl# 查询返回 Pandas DataFramedfduckdb.sql(SELECT * FROM sales.parquet).df()# 查询返回 Polars DataFramedf_plduckdb.sql(SELECT * FROM sales.parquet).pl()# 查询返回 PyArrow Tablearrow_tableduckdb.sql(SELECT * FROM sales.parquet).arrow()# 直接操作 Pandas DataFrame无需复制pandas_dfpd.DataFrame({a:[1,2,3],b:[4,5,6]})resultduckdb.sql(SELECT a, SUM(b) FROM pandas_df GROUP BY a).df()第四部分精通篇4.1 架构与性能优化列式存储原理DuckDB 按列存储数据在执行聚合查询时只需读取相关列大幅减少 I/O。同时利用向量化执行引擎一次处理 2048 行提高 CPU 缓存效率。分区与分区剪裁对于大表按时间或关键字段分区可以显著提升查询速度。-- 创建分区表CREATETABLEevents(idINT,event_dateDATE,dataVARCHAR)PARTITIONBY(event_date);-- 查询时只扫描相关分区SELECT*FROMeventsWHEREevent_date2023-01-01;索引虽然 DuckDB 在大多数分析场景中依赖分区和文件统计信息但也支持创建索引CREATEINDEXidx_idONusers(id);查询分析EXPLAIN使用EXPLAIN查看查询计划识别瓶颈EXPLAINSELECTCOUNT(*)FROMlarge_tableWHEREcolumn100;4.2 内存管理DuckDB 允许用户控制内存使用量防止 OOM内存溢出。# 设置最大内存为 4GBconn.execute(SET memory_limit 4GB)# 启用外部排序当内存不足时使用磁盘conn.execute(SET enable_external_sorting true)4.3 扩展系统DuckDB 支持动态加载扩展以增强功能。常用扩展httpfs支持直接查询 HTTP/HTTPS 上的文件如 S3、公共数据集。INSTALL httpfs;LOAD httpfs;SELECT * FROM read_parquet(‘https://example.com/data.parquet’);- **json**增强 JSON 处理默认已加载。 - **sqlite**直接读写 SQLite 数据库文件。 sql INSTALL sqlite; LOAD sqlite; ATTACH old.db AS sqlite_db (TYPE SQLITE); SELECT * FROM sqlite_db.users;mysql/postgres直接连接外部数据库进行联合查询。4.4 实战构建数据管道下面是一个典型的 ETL 场景展示了 DuckDB 的简洁与高效。场景从 S3 读取每日日志Parquet进行清洗聚合将结果写入本地数据库。importduckdb connduckdb.connect(analytics.duckdb)# 加载 httpfs 扩展以访问 S3需要配置凭证conn.execute(INSTALL httpfs; LOAD httpfs;)conn.execute(SET s3_region us-east-1;)# 执行复杂的 ETL 流程conn.execute( CREATE OR REPLACE TABLE daily_agg AS WITH raw_data AS ( -- 直接查询 S3 上的多个 Parquet 文件 SELECT * FROM read_parquet(s3://my-bucket/logs/date2023-*/*.parquet) WHERE user_id IS NOT NULL -- 数据清洗 ), enriched AS ( SELECT u.user_id, u.region, COUNT(r.event_id) AS event_count, SUM(r.revenue) AS total_revenue FROM raw_data r JOIN users u ON r.user_id u.id GROUP BY u.user_id, u.region ) SELECT * FROM enriched; )# 导出结果到 CSVconn.execute(COPY daily_agg TO daily_report.csv (HEADER);)4.5 性能对比与调优场景传统方案 (Pandas)DuckDB 方案优势读取 10GB CSV 并聚合容易内存溢出慢SELECT col, COUNT(*) FROM file.csv GROUP BY col列式扫描内存友好速度快 5-20 倍多表 Join需手动 merge内存开销大SQL Join自动优化代码简洁性能高处理嵌套 JSON使用json_normalize繁琐read_json_auto()自动展开开发效率高调优建议尽量使用Parquet作为数据源其列式存储和压缩特性与 DuckDB 天生契合。对于超大文件使用read_parquet()时利用filename字段或分区列进行过滤。使用SUMMARIZE命令快速了解数据概况SUMMARIZESELECT*FROMlarge_table;第五部分总结与最佳实践适用场景嵌入式分析在 Python 应用内部进行复杂的数据聚合和清洗替代 Pandas 的内存密集型操作。数据探索在本地或 Jupyter Notebook 中分析 GB 级别的 CSV/Parquet 文件无需搭建 Hadoop/Spark。ETL 管道作为轻量级的数据处理引擎连接不同数据源SQLite、S3、本地文件进行转换。边缘计算资源受限环境下需要运行 SQL 分析。不适用场景高并发写入DuckDB 是 OLAP 数据库不适合作为在线交易OLTP系统的后端如替代 PostgreSQL。极大规模分布式计算虽然支持多线程但本质是单节点引擎无法横向扩展到集群除非结合 PySpark。核心原则文件即表能直接查询的文件就不要导入。善用 Parquet无论存储还是交换Parquet 是最佳伴侣。利用向量化尽可能用 SQL 表达逻辑避免逐行循环。内存控制在内存紧张时设置memory_limit和external_sorting。DuckDB 正在迅速成为数据科学家和工程师的“新瑞士军刀”。它巧妙地填补了 Pandas 与分布式引擎Spark之间的空白让我们在单机上就能高效处理“中等数据”1GB 至 1TB。通过本教程的学习我们应该已经掌握了从基础查询到高级优化的全部技能可以在实际项目中充分发挥 DuckDB 的威力。
DuckDB 完全指南：从入门到精通
DuckDB 完全指南从入门到精通第一部分入门篇1.1 什么是 DuckDBDuckDB 是一个嵌入式、进程内、列式存储的 SQL 数据库管理系统DBMS。它的设计目标是提供一种高性能、易于使用且零配置的数据分析解决方案。核心特点嵌入式没有独立的服务器进程直接在应用程序如 Python、R、Node.js中运行。列式存储专为分析型查询OLAP优化在聚合、扫描大量数据时性能远超传统行式数据库如 SQLite。零配置无需安装、启动服务或创建用户。pip install duckdb即可使用。SQL 支持支持标准 SQL并扩展了针对分析的强大功能如窗口函数、列表/结构体操作、JSON 处理。与数据生态深度集成可以直接读取/写入 CSV、Parquet、JSON 文件甚至可以“直接查询”这些文件无需先导入表。1.2 安装与连接Python 环境最常用pipinstallduckdbCLI 环境命令行工具下载官方提供的duckdb可执行文件单文件放到系统路径后即可运行duckdb# 或打开指定数据库文件duckdb mydb.duckdb其他语言支持 R、Java、Node.js、C 等语法类似。1.3 第一个查询在 Python 中importduckdb# 1. 内存模式不持久化connduckdb.connect(:memory:)# 2. 执行 SQLresultconn.execute(SELECT 1 1).fetchall()print(result)# [(2,)]# 或使用快捷方式自动管理连接print(duckdb.sql(SELECT Hello, DuckDB! as greeting).fetchone())在命令行中SELECTHello, DuckDB!asgreeting;第二部分核心概念与基础操作2.1 数据库与连接DuckDB 支持两种数据库模式持久化数据库数据保存在.duckdb文件中。关闭连接后数据不丢失。connduckdb.connect(my_database.duckdb)内存数据库数据仅在内存中程序退出后消失用于临时分析。connduckdb.connect(:memory:)2.2 创建表与导入数据创建表conn.execute(CREATE TABLE users (id INTEGER, name VARCHAR, age INTEGER))# 插入数据conn.execute(INSERT INTO users VALUES (1, Alice, 30), (2, Bob, 25))直接从 CSV 创建表conn.execute( CREATE TABLE customers AS SELECT * FROM read_csv_auto(customers.csv) )注册 Pandas DataFrame将 Pandas DataFrame 注册为虚拟表可以直接查询importpandasaspd dfpd.DataFrame({id:[1,2],value:[10,20]})conn.register(my_df_view,df)resultconn.execute(SELECT * FROM my_df_view WHERE value 10).fetchdf()2.3 查询 CSV/Parquet 文件无需导入这是 DuckDB 的杀手锏特性可以直接在文件上进行 SQL 查询无需CREATE TABLE。# 直接查询 CSVduckdb.sql( SELECT PassengerId, AVG(Fare) FROM read_csv_auto(titanic.csv) GROUP BY PassengerId )# 直接查询 Parquet性能极高duckdb.sql( SELECT COUNT(*), PULocationID FROM read_parquet(yellow_tripdata_2021-01.parquet) GROUP BY PULocationID )第三部分进阶篇3.1 高级 SQL 特性DuckDB 的 SQL 引擎非常强大支持现代数据分析所需的几乎所有语法。窗口函数SELECTid,sales,ROW_NUMBER()OVER(PARTITIONBYregionORDERBYsalesDESC)asrank_in_region,SUM(sales)OVER(PARTITIONBYregion)astotal_sales_in_regionFROMsales_data;公共表表达式 (CTE)WITHhigh_value_ordersAS(SELECT*FROMordersWHEREamount1000)SELECTcustomer_id,COUNT(*)FROMhigh_value_ordersGROUPBYcustomer_id;处理嵌套数据Struct 和 ListDuckDB 原生支持复杂类型# 创建包含 List 的表duckdb.sql( CREATE TABLE orders ( id INT, items VARCHAR[] -- 数组类型 ); INSERT INTO orders VALUES (1, [apple, banana]); )# 展开数组UNNESTduckdb.sql(SELECT id, UNNEST(items) FROM orders).show()JSON 支持DuckDB 对 JSON 的处理性能极高甚至可以超过专门的 JSON 工具。-- 直接从 JSON 文件查询SELECTjson_extract_string(data,$.name)asname,json_extract_int(data,$.age)asageFROMread_json_auto(people.json);-- 使用 - 和 - 操作符类似 PostgreSQLSELECTdata-nameFROMpeople;3.2 数据导入与导出DuckDB 提供了多种高效的数据交换方式。导出到 Parquet推荐conn.execute( COPY (SELECT * FROM large_table) TO output.parquet (FORMAT PARQUET); )导出到 CSVconn.execute( COPY users TO users.csv (HEADER, DELIMITER ,); )从多种格式导入# 支持 CSV, JSON, Parquet, Arrow, Pandas, SQLite 等conn.execute(CREATE TABLE t1 AS SELECT * FROM data.parquet)conn.execute(CREATE TABLE t2 AS SELECT * FROM data.csv)3.3 与 Python 生态无缝集成DuckDB 可以高效地在 Pandas、Polars、Arrow 之间传递数据实现“零拷贝”交互。importpandasaspdimportpolarsaspl# 查询返回 Pandas DataFramedfduckdb.sql(SELECT * FROM sales.parquet).df()# 查询返回 Polars DataFramedf_plduckdb.sql(SELECT * FROM sales.parquet).pl()# 查询返回 PyArrow Tablearrow_tableduckdb.sql(SELECT * FROM sales.parquet).arrow()# 直接操作 Pandas DataFrame无需复制pandas_dfpd.DataFrame({a:[1,2,3],b:[4,5,6]})resultduckdb.sql(SELECT a, SUM(b) FROM pandas_df GROUP BY a).df()第四部分精通篇4.1 架构与性能优化列式存储原理DuckDB 按列存储数据在执行聚合查询时只需读取相关列大幅减少 I/O。同时利用向量化执行引擎一次处理 2048 行提高 CPU 缓存效率。分区与分区剪裁对于大表按时间或关键字段分区可以显著提升查询速度。-- 创建分区表CREATETABLEevents(idINT,event_dateDATE,dataVARCHAR)PARTITIONBY(event_date);-- 查询时只扫描相关分区SELECT*FROMeventsWHEREevent_date2023-01-01;索引虽然 DuckDB 在大多数分析场景中依赖分区和文件统计信息但也支持创建索引CREATEINDEXidx_idONusers(id);查询分析EXPLAIN使用EXPLAIN查看查询计划识别瓶颈EXPLAINSELECTCOUNT(*)FROMlarge_tableWHEREcolumn100;4.2 内存管理DuckDB 允许用户控制内存使用量防止 OOM内存溢出。# 设置最大内存为 4GBconn.execute(SET memory_limit 4GB)# 启用外部排序当内存不足时使用磁盘conn.execute(SET enable_external_sorting true)4.3 扩展系统DuckDB 支持动态加载扩展以增强功能。常用扩展httpfs支持直接查询 HTTP/HTTPS 上的文件如 S3、公共数据集。INSTALL httpfs;LOAD httpfs;SELECT * FROM read_parquet(‘https://example.com/data.parquet’);- **json**增强 JSON 处理默认已加载。 - **sqlite**直接读写 SQLite 数据库文件。 sql INSTALL sqlite; LOAD sqlite; ATTACH old.db AS sqlite_db (TYPE SQLITE); SELECT * FROM sqlite_db.users;mysql/postgres直接连接外部数据库进行联合查询。4.4 实战构建数据管道下面是一个典型的 ETL 场景展示了 DuckDB 的简洁与高效。场景从 S3 读取每日日志Parquet进行清洗聚合将结果写入本地数据库。importduckdb connduckdb.connect(analytics.duckdb)# 加载 httpfs 扩展以访问 S3需要配置凭证conn.execute(INSTALL httpfs; LOAD httpfs;)conn.execute(SET s3_region us-east-1;)# 执行复杂的 ETL 流程conn.execute( CREATE OR REPLACE TABLE daily_agg AS WITH raw_data AS ( -- 直接查询 S3 上的多个 Parquet 文件 SELECT * FROM read_parquet(s3://my-bucket/logs/date2023-*/*.parquet) WHERE user_id IS NOT NULL -- 数据清洗 ), enriched AS ( SELECT u.user_id, u.region, COUNT(r.event_id) AS event_count, SUM(r.revenue) AS total_revenue FROM raw_data r JOIN users u ON r.user_id u.id GROUP BY u.user_id, u.region ) SELECT * FROM enriched; )# 导出结果到 CSVconn.execute(COPY daily_agg TO daily_report.csv (HEADER);)4.5 性能对比与调优场景传统方案 (Pandas)DuckDB 方案优势读取 10GB CSV 并聚合容易内存溢出慢SELECT col, COUNT(*) FROM file.csv GROUP BY col列式扫描内存友好速度快 5-20 倍多表 Join需手动 merge内存开销大SQL Join自动优化代码简洁性能高处理嵌套 JSON使用json_normalize繁琐read_json_auto()自动展开开发效率高调优建议尽量使用Parquet作为数据源其列式存储和压缩特性与 DuckDB 天生契合。对于超大文件使用read_parquet()时利用filename字段或分区列进行过滤。使用SUMMARIZE命令快速了解数据概况SUMMARIZESELECT*FROMlarge_table;第五部分总结与最佳实践适用场景嵌入式分析在 Python 应用内部进行复杂的数据聚合和清洗替代 Pandas 的内存密集型操作。数据探索在本地或 Jupyter Notebook 中分析 GB 级别的 CSV/Parquet 文件无需搭建 Hadoop/Spark。ETL 管道作为轻量级的数据处理引擎连接不同数据源SQLite、S3、本地文件进行转换。边缘计算资源受限环境下需要运行 SQL 分析。不适用场景高并发写入DuckDB 是 OLAP 数据库不适合作为在线交易OLTP系统的后端如替代 PostgreSQL。极大规模分布式计算虽然支持多线程但本质是单节点引擎无法横向扩展到集群除非结合 PySpark。核心原则文件即表能直接查询的文件就不要导入。善用 Parquet无论存储还是交换Parquet 是最佳伴侣。利用向量化尽可能用 SQL 表达逻辑避免逐行循环。内存控制在内存紧张时设置memory_limit和external_sorting。DuckDB 正在迅速成为数据科学家和工程师的“新瑞士军刀”。它巧妙地填补了 Pandas 与分布式引擎Spark之间的空白让我们在单机上就能高效处理“中等数据”1GB 至 1TB。通过本教程的学习我们应该已经掌握了从基础查询到高级优化的全部技能可以在实际项目中充分发挥 DuckDB 的威力。
