基于本地文件系统的列式数据库-DuckDB
这两天发现了一款有趣的数据库DuckDB，它的设计思路来源于sqlite，但是与sqlite不同的是，sqlite是行式数据库，而DuckDB是列式数据库。除此以外，两者非常相似：两个都是基于本地文件系统设计的，都有着完整的数据库体系（客户端、SQL解析器、SQL优化器和存储引擎等等），安装和使用都非常方便。在一些数据分析场景下，比如临时跑个数，不想安装MySQL或者分布式数据库等，应该大有可为。下面基于DuckDB的官方文档和相关博客，做一些简单介绍。
DuckDB的安装
DuckDB 的安装非常简单，。因为DuckDB主要为数据分析服务，因此在这里仅仅介绍使用Python或者是R的安装方式
- Python
在命令行界面输入
conda install python-duckdb或者是pip install duckdb即可。 - R
R的话也很简单，输入
remotes::install_github("cwida/duckdb/tools/rpkg", build = FALSE)即可。
DuckDB
sqlite是一款非常优秀的数据库，特别是当开发者想在本地服务器直接运行SQL代码或者是像关系型数据库一样存储数据时，sqlite完美的切合了这个需求。
但是sqlite是行式数据库，本身存储数据和大部分关系型数据库类似，在处理数据分析的相关操作时，消耗就非常大了。这里的分析数据分析操作包括对单列的求和、求平均等操作。
因此DuckDB应运而生，提供与sqlite一样的接口和存储方式，但其底层是列式存储。
导入数据到DuckDB
DuckDB完整的实现了DB-API 2.0协议。因此可以像一般连接数据库一样连接DuckDB。
import duckdb
conn = duckdb.connect('ytd.duckdb')
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE yellow_tripdata_2016_01 (
VendorID bigint,
tpep_pickup_datetime timestamp,
tpep_dropoff_datetime timestamp,
passenger_count bigint,
trip_distance double,
pickup_longitude double,
pickup_latitude double,
RatecodeID bigint,
store_and_fwd_flag varchar,
dropoff_longitude double,
dropoff_latitude double,
payment_type bigint,
fare_amount double,
extra double,
mta_tax double,
tip_amount double,
tolls_amount double,
improvement_surcharge double,
total_amount double
)
""")
cursor.execute("""
COPY yellow_tripdata_2016_01 FROM '/Users/uwe/Development/data-science-io-benchmarks/data/yellow_tripdata_2016-01.csv'
WITH HEADER
""")
cursor.close()
connection.close()
其中COPY yellow_tripdata_2016_01 FROM '/Users/uwe/Development/data-science-io-benchmarks/data/yellow_tripdata_2016-01.csv' WITH HEADER就是导入语句。
COUNT DISTINCT
在行式数据库里执行COUNT DISTINCT是一个代价颇高的操作，它需要数据库将数据一行一行读取出来，是一个相当考验性能的操作。运行SQL如下：
SELECT
COUNT(DISTINCT VendorID),
-- COUNT(DISTINCT tpep_pickup_datetime),
-- COUNT(DISTINCT tpep_dropoff_datetime),
COUNT(DISTINCT passenger_count),
COUNT(DISTINCT trip_distance),
-- COUNT(DISTINCT pickup_longitude),
-- COUNT(DISTINCT pickup_latitude),
COUNT(DISTINCT RatecodeID),
COUNT(DISTINCT store_and_fwd_flag),
-- COUNT(DISTINCT dropoff_longitude),
-- COUNT(DISTINCT dropoff_latitude),
COUNT(DISTINCT payment_type),
COUNT(DISTINCT fare_amount),
COUNT(DISTINCT extra),
COUNT(DISTINCT mta_tax),
COUNT(DISTINCT tip_amount),
COUNT(DISTINCT tolls_amount),
COUNT(DISTINCT improvement_surcharge),
COUNT(DISTINCT total_amount)
FROM yellow_tripdata_2016_01
使用Python的timeit测试下性能。
%%timeit
# DuckDB
cursor.execute(query)
cursor.fetchdf()
# 5.58 s ± ms per loop (mean ± std. dev. of runs, loop each)
%%time
# SQLite
pd.read_sql(query, conn)
# 25.2 s ± ms per loop (mean ± std. dev. of runs, loop each)
DuckDB 的fetchdf操作将读取出来的数据转成pandas.DataFrame格式。从结果来看，DuckDB 的性能提升非常明显。
Min、Max和Avg
性能测试如下:
-- DuckDB
SELECT
MIN(cnt),
AVG(cnt),
-- MEDIAN(cnt),
MAX(cnt)
FROM
(
SELECT
COUNT(*) as cnt
FROM yellow_tripdata_2016_01
GROUP BY
EXTRACT(DOY FROM tpep_pickup_datetime::DATE),
EXTRACT(HOUR FROM tpep_pickup_datetime)
) stats
-- 2.05 s ± 22.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
-- SQLite
SELECT
MIN(cnt),
AVG(cnt),
-- MEDIAN(cnt),
MAX(cnt)
FROM
(
SELECT
COUNT(*) as cnt
FROM yellow_tripdata_2016_01
GROUP BY
strftime('%j', tpep_pickup_datetime),
strftime('%H', tpep_pickup_datetime)
) AS stats
-- 10.2 s ± 40.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
结论
正如我们在上面看到的，DuckDB和sqlite使用方式非常类似，但是DuckDB 提供了基于本地文件系统就可以进行数据分析的能力，性能远远超过传统的sqlite。
腾讯云开发者
