当前位置： 首页 > article >正文

duckdb 连接postgres 和 jdbc 的使用

article 2024/9/23 10:17:35

why？

主要是特别快

  • 嵌入式，不需要服务器，使用超级方便

  • 扩展机制灵活，可以直接读取CSV、JSON、Parquet等文件

    Parquet文件格式详解（含行、列式存储区别）_parquet格式-CSDN博客

  • 采用列式存储（用于高效聚合）及向量化处理（用于提高性能）功能

1.下载

https://github.com/duckdb/duckdb/releases

duckdb_cli-windows-amd64.zip

2. 安装扩展和使用

2.1 直接使用命令行

D:\soft\pgsql>duckdb.exe
v1.1.0 fa5c2fe15f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

安装扩展：

postgres 扩展 PostgreSQL Extension – DuckDB

mysql 扩展 MySQL Extension – DuckDB

下边以 postgres 扩展 为例：

 -- 安装扩展 postgres 扩展
 INSTALL postgres;

 -- duckdb 会默认下载扩展并安装在
 -- C:\Users\xueji\.duckdb\extensions\v1.1.0\windows_amd64

 -- 加载扩展
 LOAD postgres;

 -- 查询是否安装扩展 注意【installed=true】
 SELECT extension_name, installed, description FROM duckdb_extensions();

 SELECT extension_name, installed FROM duckdb_extensions() where installed=true;
┌──────────────────┬───────────┐
│  extension_name  │ installed │
│     varchar      │  boolean  │
├──────────────────┼───────────┤
│ autocomplete     │ true      │
│ fts              │ true      │
│ icu              │ true      │
│ json             │ true      │
│ parquet          │ true      │
│ postgres_scanner │ true      │
│ shell            │ true      │
│ tpch             │ true      │
└──────────────────┴───────────┘

 -- 连接到postgres
 ATTACH 'dbname=postgres user=postgres  password=123456 host=127.0.0.1  port=5433' AS db (TYPE POSTGRES, SCHEMA 'public');
-- 显示所有数据库 https://duckdb.org/docs/configuration/pragmas.html
 PRAGMA database_list;
┌───────┬─────────┬──────────────────────────────────────────────────────────────────────────┐
│  seq  │  name   │                                   file                                   │
│ int64 │ varchar │                                 varchar                                  │
├───────┼─────────┼──────────────────────────────────────────────────────────────────────────┤
│  1148 │ memory  │                                                                          │
│  2114 │ db      │ dbname=postgres user=postgres  password=123456 host=127.0.0.1  port=5433 │
└───────┴─────────┴──────────────────────────────────────────────────────────────────────────┘

 -- 显示所有表
 SHOW ALL TABLES;


┌──────────┬─────────┬────────────────┬──────────────────────┬─────────────────────────────────────────────┬───────────┐
│ database │ schema  │      name      │     column_names     │                column_types                 │ temporary │
│ varchar  │ varchar │    varchar     │      varchar[]       │                  varchar[]                  │  boolean  │
├──────────┼─────────┼────────────────┼──────────────────────┼─────────────────────────────────────────────┼───────────┤
│ db       │ public  │ accounts       │ [id, client, amount] │ [BIGINT, VARCHAR, DOUBLE]                   │ false     │
│ db       │ public  │ cacheme        │ [id]                 │ [INTEGER]                                   │ false     │
│ db       │ public  │ demo           │ [id, name, str_arr…  │ [INTEGER, VARCHAR, VARCHAR[], INTEGER[], …  │ false     │
│ db       │ public  │ hot            │ [id, s]              │ [INTEGER, VARCHAR]                          │ false     │
│ db       │ public  │ pg_buffercache │ [bufferid, relfile…  │ [INTEGER, UINTEGER, UINTEGER, UINTEGER, S…  │ false     │
│ db       │ public  │ student        │ [id, name, age]      │ [INTEGER, VARCHAR, INTEGER]                 │ false     │
│ db       │ public  │ t              │ [id, s]              │ [INTEGER, VARCHAR]                          │ false     │
│ db       │ public  │ tfreeze        │ [id, s]              │ [INTEGER, VARCHAR]                          │ false     │
│ db       │ public  │ vac            │ [id, s]              │ [INTEGER, VARCHAR]                          │ false     │
└──────────┴─────────┴────────────────┴──────────────────────┴─────────────────────────────────────────────┴───────────┘

-- 此处注意 {database}.{schema}.{name} 是表的访问路径
-- db.public.student 表示 
-- database=db
-- schema=public

-- 查询student 表
select * from db.public.student;

-- 导出数据为csv 
copy db.public.student to 'd:\student.csv' with(header, delimiter '|');


-- 导出部分数据
copy (select * from db.public.student limit 10) to 'd:\student.csv' with(header, delimiter '|');

-- 直接查询csv 文件
 select * from 'd:\student.csv' limit 10;
┌───────┬─────────┬───────┐
│  id   │  name   │  age  │
│ int64 │ varchar │ int64 │
├───────┼─────────┼───────┤
│     1 │ 张三    │    23 │
│     2 │ 李四    │    24 │
│     3 │ 王五    │    25 │
│     4 │ 赵六    │    26 │
└───────┴─────────┴───────┘ 

--分组查询

select count(*),max(age) from 'd:\student.csv' limit 10;
┌──────────────┬──────────┐
│ count_star() │ max(age) │
│    int64     │  int64   │
├──────────────┼──────────┤
│            4 │       26 │
└──────────────┴──────────┘

 -- 读取csv 查询结果并把结果保存到表
create table main.test1 as select count(*) cnt ,max(age) m from 'd:\student.csv';

-- 查询新生成的表
select * from memory.main.test1;
┌───────┬───────┐
│  cnt  │   m   │
│ int64 │ int64 │
├───────┼───────┤
│     4 │    26 │
└───────┴───────┘


-- 导出为 Parquet格式
copy db.public.student to 'd:\test.parquet' (format 'parquet');

-- 使用文件名查询
select count(*) from 'd:\test.parquet';

-- 部分导出
copy (select * from db.public.student where id<=2) to 'd:\student1.parquet' (format 'parquet');
copy (select * from db.public.student where id>2)  to 'd:\student2.parquet' (format 'parquet');


-- 使用文件通配符查询，可以查询多个文件
select count(*) from 'd:\student*.parquet';
-- 读取 多个 parquet 查询结果并把结果保存到表
create table main.test2 as select count(*) cnt ,max(age) m from 'd:\student*.parquet';
-- 查询内部表
select * from memory.main.test1;


-- 也可以在duckdb 直接插入到postges 中
insert into db.public.demo(id,name) values(8,'duckdb');
-- 查询插入的数据
select id,name from db.public.demo where id=8;
┌───────┬─────────┐
│  id   │  name   │
│ int32 │ varchar │
├───────┼─────────┤
│     8 │ duckdb  │
└───────┴─────────┘

扩展安装的地址

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

认识DuckDB | 遇码MeetCoding

2.2 在java中使用

2.2.1 引入 maven包

DuckDB Installation – DuckDB

Maven Repository: org.duckdb » duckdb_jdbc

 <dependency>
	<groupId>org.duckdb</groupId>
	<artifactId>duckdb_jdbc</artifactId>
    <!-- 目前1.1.0 在window 上测试有bug -->
	<version>1.0.0</version> 
 </dependency>
2.2.2 使用jdbc

D:\work20220906\java\myopen\work20240821\async-demo202402\src\test\java\com\xue\duckdb\DuckdbTest.java

package com.xue.duckdb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 
 * @date 2024年9月13日 10:13:13
 */
public class DuckdbTest {
	// https://duckdb.org/docs/api/java.html
	public static void main(String[] args) throws Exception {
		Class.forName("org.duckdb.DuckDBDriver");
		Connection conn = DriverManager.getConnection("jdbc:duckdb:");
		// create a table
		Statement stmt = conn.createStatement();
		stmt.execute("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)");
		// insert two items into the table
		stmt.execute("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)");

		try (ResultSet rs = stmt.executeQuery("SELECT * FROM items")) {
			while (rs.next()) {
				System.out.printf("%s  %04f  %03d\n", rs.getString(1), rs.getDouble(2), rs.getInt(3));
			}
		}

		stmt.execute("INSTALL postgres");
		stmt.execute("LOAD postgres");
		// duckdb_jdbc 1.0.0 可以,但是 无法识别 SCHEMA 选项
		stmt.execute(
				"ATTACH 'dbname=postgres user=postgres  password=123456 host=127.0.0.1  port=5433' AS db (TYPE POSTGRES, READ_ONLY)");
		// duckdb_jdbc 1.1.0 奔溃 参考 https://github.com/duckdb/duckdb-java/issues/81
		// stmt.execute("ATTACH 'dbname=postgres user=postgres password=123456
		// host=127.0.0.1 port=5433' AS db (TYPE POSTGRES, SCHEMA 'public')");

		try (ResultSet rs = stmt.executeQuery("select * from db.public.student")) {
			while (rs.next()) {
				System.out.printf("%05d  %s  %03d\n", rs.getInt(1), rs.getString(2), rs.getInt(1));
			}
		}
		System.out.println("=============导出到csv");
		stmt.execute("copy db.public.student to 'd:/student.csv' with(header, delimiter '|');");
		System.out.println("=============查询csv");
		try (ResultSet rs = stmt.executeQuery("select * from 'd:/student.csv'")) {
			while (rs.next()) {
				System.out.printf("%05d  %s  %03d\n", rs.getInt(1), rs.getString(2), rs.getInt(1));
			}
		}

		stmt.close();

	}

}

执行结果

在这里插入图片描述

Java JDBC API – DuckDB

五分钟玩转超人气OLAP数据库DuckDB | 遇码MeetCoding

3.postgres关于DuckDB 扩展

DuckDB Foreign Data Wrapper for PostgreSQL是一个外数据包装器（FDW），旨在将PostgreSQL与DuckDB数据库文件连接起来。这一工具支持PostgreSQL 9.6至16版本，并且与相同版本的libduckdb完全兼容。通过这一FDW，用户可以在PostgreSQL中直接操作DuckDB的数据，实现数据的无缝集成和高效处理。

DuckDB FDW的核心在于其外数据包装器的实现，它允许PostgreSQL通过标准的SQL接口访问DuckDB的数据。这一技术架构不仅确保了数据的一致性和完整性，还提供了高效的数据传输和处理能力。

https://duckdb.org/docs/guides/database_integration/postgres.html

4.DuckDB 关于 postgres 扩展

The postgres extension allows DuckDB to directly read and write data from a running PostgreSQL database instance. The data can be queried directly from the underlying PostgreSQL database. Data can be loaded from PostgreSQL tables into DuckDB tables, or vice versa. See the official announcement for implementation details and background.
postgres扩展允许 DuckDB 直接从正在运行的 PostgreSQL 数据库实例读取和写入数据。可以直接从底层PostgreSQL数据库查询数据。数据可以从 PostgreSQL 表加载到 DuckDB 表中，反之亦然。实施细节和背景请参见官方公告。

https://duckdb.org/docs/extensions/postgres.html

https://duckdb.org/docs/guides/database_integration/postgres.html

Querying Postgres Tables Directly From DuckDB
直接从 DuckDB 查询 Postgres 表

https://duckdb.org/2022/09/30/postgres-scanner.html

5. 预先卸载数据到parquet 然后又duckdb查询

使用 PostgreSQL 和 duckdb_fdw 实现轻量级 OLAP

https://www.ctyun.cn/developer/article/563275316723781

6. 如何导入数据到duckdb

Importing Data – DuckDB

更多：

  • DuckDB百亿级数据性能测试 | 遇码MeetCoding
  • spring jdbctemplate 集成duckdb - 荣锋亮 - 博客园

http://www.kler.cn/news/303402.html

相关文章：

  • Prism 教程
  • Java集合框架（Collections Framework）入门
  • Compose Multiplatform+kotlin Multiplatfrom第三弹
  • 车辆检测与分类系统源码分享
  • [图论]街道赛跑
  • 【EI会议征稿通知】第四届材料工程与应用力学国际学术会议（ICMEAAE 2025）
  • Spring下载文件
  • 《征服数据结构》滚动数组
  • uni-app生命周期（三）
  • 基于vue框架的城市网约车管理系统v34td（程序+源码+数据库+调试部署+开发环境）系统界面在最后面。
  • 民间故事推广系统小程序的设计
  • PMP--一模--解题--41-50
  • 初级练习[3]:Hive SQL子查询应用
  • 其它查询优化策略
  • 基于SSM的大学新生报到系统+LW参考示例
  • Vue3实现打印功能
  • 数据结构---非线性--树
  • prometheus 集成 grafana 保姆级别安装部署
  • 数据结构与算法 第12天（排序）
  • 字符分类函数和字符串函数
  • 【PostgreSQL数据库表膨胀的一些原因】
  • springboot 单独新建一个文件实时写数据，当文件大于100M时按照日期时间做文件名进行归档
  • 2024121读书笔记|《不急：我们慢慢慢慢来》——做人呢，最重要的是开心
  • 从底层原理上理解ClickHouse 中的 Distributed 引擎
  • tomcat项目报错org.apache.jasper.JasperException: java.lang.NullPointerException
  • Python中的“异常”之旅：探索异常处理的艺术
  • 大语言模型之ICL(上下文学习) - In-Context Learning Creates Task Vectors
  • 用于安全研究的 Elastic Container Project
  • Java 行为型设计模式一口气讲完！*^____^*
  • Spring Cloud 搭建 Gateway 网关与统一登录模块：路径重写、登录拦截、跨域配置