1. 概述

DuckDB 是一个分析型关系数据库。本文我们将探讨它的优势,并了解它如何成为处理分析任务的高效解决方案。接下来,我们将逐步安装并学习一些基本操作。

2. 什么是DuckDB?

DuckDB 是一种内存分析关系型数据库,主要用于数据分析。 它被认为是一种分析数据库,因为它采用列式存储,即分别存储每列的数据。相比之下,传统的关系数据库采用基于行的存储,逐行存储数据。

DuckDB的优势包括:

  • 快速查询:DuckDB使用列向量化的查询执行引擎,优化大量数据的查询性能。
  • SQL兼容性:DuckDB支持标准SQL查询,如聚合和窗口函数,非常适合熟悉SQL的数据分析师。
  • 快速部署:DuckDB有很少的外部依赖,运行在我们的应用程序进程中,无需独立的数据库实例,部署和集成非常简单。
  • 免费开源:DuckDB是一个开源项目,对所有人免费提供。其全部源代码在GitHub上可获取,供探索和贡献。

3. DuckDB安装

DuckDB 安装非常方便,官方提供了不同平台和环境的安装方法。这里我们将演示两种常见的安装方法。

3.1. 命令行模式

对于Windows用户,我们可以使用WinGet包管理器来安装DuckDB:

winget install DuckDB.cli

在Mac OS上,我们可以使用Homebrew进行安装:

brew install duckdb

安装完成后,brew会自动将二进制路径添加到现有的环境变量中。我们可以在新的shell会话中运行DuckDB命令行,如下所示:

duckdb

此外我们也可直接去GitHub Releases主页,下载编译好的可执行文件。Duckdb 由C++编写,编译后的可执行文件大小只有10多M,非常轻量。

Duckdb 支持 Python、R、Java、Node.js、Rust、Go、C/C++ 等编程语言集成,下面以Java举例。

3.2. Java 集成

DuckDB可以与Java无缝集成,作为嵌入式数据库,无需安装独立的数据库实例,类似于H2、SQLite,事实上DuckDB正是受到SQLite启发。

首先,在pom.xml中包含以下DuckDB JDBC依赖:

<dependency>
    <groupId>org.duckdb</groupId>
    <artifactId>duckdb_jdbc</artifactId>
    <version>0.10.0</version>
</dependency>

然后,我们可以加载DuckDB的JDBC驱动,并通过以下JDBC URL创建JDBC连接:

Class.forName("org.duckdb.DuckDBDriver");
Connection conn = DriverManager.getConnection("jdbc:duckdb:");

当我们连接到DuckDB时,它会默认创建一个内存数据库实例。但是,一旦DuckDB进程结束,所有持久化在实例中的数据都将丢失。要将数据保存到磁盘,我们可以在连接URL中添加数据库名称:

Connection conn = DriverManager.getConnection("jdbc:duckdb:/test_duckdb");

在这个例子中,DuckDB将在根目录下创建一个名为test_duckdb的数据库文件。作为JDBC库,我们可以通过创建SQL Statement 并执行它来查询数据,获取ResultSet。以下是一个简单的Java JDBC示例,用于获取当前日期:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT current_date");
Date currentDate = rs.next() ? rs.getDate(1) : null;

在本教程的后续部分,我们将使用相同的JDBC方法在Java中执行SQL语句。

4. 数据导入

现在,让我们导入一些数据到DuckDB。它能处理各种数据格式,简化从外部数据源导入数据的过程。

4.1. CSV文件

CSV是存储表格数据的常见格式。假设我们有一个包含客户数据的CSV文件:

CustomerId,FirstName,LastName,Gender
101,John,Smith,Male
102,Sarah,Jones,Female
...

我们可以使用SQL函数read_csv从CSV文件导入数据到名为customer的DuckDB表:

CREATE TABLE customer AS 
SELECT * FROM read_csv('customer.csv')

DuckDB可以从CSV文件的头部行自动推断出表结构。头部的名称被视为列名,而后续行则视为数据行。

4.2. JSON文件

另一种流行的存储和分享数据的方式是JSON。例如,考虑以下product.json文件:

[
  {
    "productId": 1,
    "productName":"EZ Curl Bar",
    "category": "Sports Equipment"
  },
  {
    "productId": 2,
    "productName": "7' Barbell",
    "category": "Sports Equipment"
  }
]

与CSV导入类似,我们可以通过执行SQL语句将数据导入到名为product的DuckDB表:

CREATE TABLE product AS 
SELECT * FROM read_json('product.json')

DuckDB会根据JSON属性名称自动从JSON文件推断出表结构。

4.3. INSERT语句

我们可以使用INSERT语句向DuckDB表中添加数据,因为它是一个SQL关系型数据库系统。下面的示例演示了创建定义customerproduct之间关系的purchase表,并插入几行数据:

CREATE TABLE purchase(customerId BIGINT, productId BIGINT);
INSERT INTO purchase(customerId, productId) VALUES (101,1);
INSERT INTO purchase(customerId, productId) VALUES (102,1);
INSERT INTO purchase(customerId, productId) VALUES (102,2);

5. 数据查询

数据导入后,我们将开始探索如何在DuckDB上查询和分析数据。

5.1. 连接操作

除了将外部数据导入到DuckDB外,我们还可以直接使用这些外部数据。基于前面的例子,我们将使用前一节中的三个数据源。现在,我们将这些数据源连接起来,获取有关客户产品信息的数据。

SELECT C.firstName, C.lastName, P.productName
FROM read_csv('customer.csv') AS C, read_json('product.json') AS P, purchase S 
WHERE S.customerId = C.customerId
AND S.productId = P.productId 

执行后,我们将看到查询结果,显示客户的姓名及其对应的产品购买情况:

firstName lastName productName
John Smith EZ Curl Bar
Sarah Jones 7' Barbell
Sarah Jones EZ Curl Bar

5.2. 聚合函数

DuckDB提供了一套丰富的聚合函数,用于对行组进行计算。让我们通过一个例子来了解这些功能:

SELECT P.productName, COUNT(*) AS purchaseCount
FROM customer C, product P, purchase S
WHERE S.customerId = C.customerId
AND S.productId = P.productId 
GROUP BY P.productName
ORDER BY COUNT(*) DESC

该查询计算每个产品的购买次数,并按购买次数降序排列:

productName purchaseCount
EZ Curl Bar 2
7' Barbell 1

6. 数据导出

在数据分析任务中,我们经常需要将汇总数据导出到其他应用进行进一步分析。

接下来,我们将逐步演示如何从DuckDB以各种格式导出数据。为了演示方便,我们首先创建一个数据库视图:

CREATE VIEW purchase_view AS
SELECT P.productName, COUNT(*) AS purchaseCount
FROM customer C, product P, purchase S
WHERE S.customerId = C.customerId
AND S.productId = P.productId 
GROUP BY P.productName
ORDER BY COUNT(*) DESC;

6.1. CSV文件

在DuckDB中,导出到CSV文件非常简单。我们可以执行以下SQL语句,将数据库视图purchase_view的所有数据复制到根目录下的CSV文件中:

COPY purchase_view TO '/output.csv'

6.2. JSON文件

要导出到JSON文件,我们需要添加一个额外的选项array,指定将数据写入JSON数组,确保导出的JSON文件结构合适:

COPY (SELECT * FROM purchase_view WHERE purchaseCount > 1) TO '/output.json' (array true);

我们不仅可以导出所有数据,还可以根据select查询条件选择部分结果。

7. 结论

在这篇文章中,我们了解了DuckDB数据库及其优势,并通过实例学习了一些基础操作。

一如既往,所有的代码可以在GitHub上找到。