基于本地文件系统的列式数据库-DuckDB

2019 年 11 月 6 日
筆記

这两天发现了一款有趣的数据库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。