快速產生 SQLite 資料的方式:一分鐘內產生十億筆資料

在「Towards Inserting One Billion Rows in SQLite Under A Minute」這邊看到作者想要在一分鐘內在 MBP 2019 上面寫 1B 筆資料進 SQLite,裡面有些方法還蠻值得玩一下的,這台 MBP 2019 機器的規格是:

The machine I am using is MacBook Pro, 2019 (2.4 GHz Quad Core i5, 8GB, 256GB SSD, Big Sur 11.1)

第一版是 Python 寫的,塞 10M 筆花了 15 分鐘:

In this script, I tried to insert 10M rows, one by one, in a for loop. This version took close to 15 minutes, sparked my curiosity and made me explore further to reduce the time.

加了五個 PRAGMA 的版本變成 100M 筆十分鐘:

The naive for loop version took about 10 minutes to insert 100M rows.

用批次處理則可以降到八分半:

The batched version took about 8.5 minutes to insert 100M rows.

再來是拿經典神器 PyPy 出來用,降到兩分半:

All I had to do was run my existing code, without any change, using PyPy. It worked and the speed bump was phenomenal. The batched version took only 2.5 minutes to insert 100M rows. I got close to 3.5x speed :)

接下來就是跳槽到 Rust 了,中間也有不少 tuning 相關的討論,但直接先跳到最後面好了... 最後 100M 只用了 33 秒:

I created a threaded version, where I had one writer thread that received data from a channel and four other threads which pushed data to the channel. This is the current best version which took about 32.37 seconds.

能用 PyPy 的地方還是可以考慮一下的...

用 Python 的 DuckDB 下 SQL 指令翻 Parquet 的資料

在「Querying Parquet using DuckDB」這邊看到 DuckDB 這個東西,裡面引用的文章是「Querying Parquet with Precision using DuckDB」,可以直接對 Parquet 格式的資料下 SQL 找資料。

先前好像有看到 DuckDB 但沒有太注意,剛剛再次看到,然後玩了一下還蠻有趣的。DuckDB 支援蠻多程式語言與資料格式,不過這邊文章拿 Python 與 Parquet 玩還蠻有趣的...

先把 Parquet 的範例資料抓下來,然後透過 pip 裝 duckdb:

cd /tmp; wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_04.parquet; pip install -U duckdb

然後進到 Python 3 的互動界面:

>>> import duckdb
>>> print(duckdb.query("SELECT COUNT(*) FROM 'taxi_2019_04.parquet' WHERE pickup_at BETWEEN '2019-04-15' AND '2019-04-20'").fetchall())
[(1276565,)]

然後在範例裡面,檔名的部份還可以用 *,看了一下說明,底層是 glob 類的用法:

DuckDB supports the globbing syntax, which allows it to query all three files simultaneously.

文章裡有提到速度比 Pandas 快很多,不過我覺得這好像不太能這樣比,會拿 Pandas 出來的時候常常是其他用法,但至少看起來速度是個 DuckDB 在意的點。

不過反而馬上想到的是,之後處理 CSV 之類的檔案應該也會試看看 DuckDB...

DuckDB

看到篇有趣的介紹,在講 DuckDB:「DuckDB」。

[I]t uses the PostgreSQL parser but models itself after SQLite in that databases are a single file and the code is designed for use as an embedded library, distributed in a single amalgamation C++ file (SQLite uses a C amalgamation).

看起來是個以 OLAP 為中心而設計出來的資料庫,然後在 Python 下可以直接透過 pip 裝起來。

看起來像是個用單機拼 throughput 的東西,但提供大家熟悉的界面。

Hacker News 上可以看到「DuckDB – An embeddable SQL database like SQLite, but supports Postgres features (duckdb.org)」這邊給了不少方向,

Canonical 推出的 Dqlite (High-Availability SQLite)

第一眼看到的時候直接有種不知道 Canonical 在幹什麼的感覺,翻完說明後大概知道可以用的地方,但還是覺得範圍有點小:「Dqlite - High-Availability SQLite」。

一種使用情境是,在 embedded system 上面同步資料的一種方案... 吧?例如網路連線的頻寬或是品質受限,無法順利傳到 Internet 端的伺服器上,所以希望在本地端就可以解決一些事情,但又不方便在本地端直接弄個 PostgreSQL 出來?

Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.

另外一個是用到了 C 實做的 Raft 協定:

Dqlite (“distributed SQLite”) extends SQLite across a cluster of machines, with automatic failover and high-availability to keep your application running. It uses C-Raft, an optimised Raft implementation in C, to gain high-performance transactional consensus and fault tolerance while preserving SQlite’s outstanding efficiency and tiny footprint.

讓 IoT 裝置參與 Raft 嗎... 好像只能說有趣... XD

SQLite 的全文搜尋功能

算是補充之前看過,但一直沒研究的東西...

看到 Simon Willison 的「Exploring search relevance algorithms with SQLite」這篇才花些時間看了一下 SQLite 的搜尋功能。

看起來不論是 FTS4 或是 FTS5 都沒有處理 CJK 文字的功能,可能要當作 unigram 之類的方式處理 (參考「Unicode support for non-English characters with Sqlite Full Text Search in Android」這篇),不過排名的部份有支援 BM25,整體看起來應該是還算堪用。

SQLite 的 CLI 操作工具 litecli

之前應該都是用 SQLite 提供的 cli 操作,現在有人提供支援 auto completion 與顏色的 cli 軟體了:「CLI for SQLite Databases with auto-completion and syntax highlighting」。

工具是用 Python 寫的,可以直接用 pip 安裝。