基於本地文件系統的列式數據庫-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。