SQLite 的 zstd extension

看到「sqlite-zstd」這個實驗性質的專案,可以針對 SQLite 的 row-level 層壓縮:

Extension for sqlite that provides transparent dictionary-based row-level compression for sqlite. This basically allows you to compress entries in a sqlite database almost as well as if you were compressing the whole DB file, but while retaining random access.

看起來在空間上有很不錯的成果:

作者在他自己的 blog 上面有給了一篇比較完整的說明,除了空間上的優勢以外,還包含了效能上的分析:「sqlite-zstd: Transparent dictionary-based row-level compression for SQLite」。

在文章裡面測出來的數據看起來在效能上就不一定有比較好的結果,本來的 SQLite 就已經處理的還不錯了。

但看起來是個還蠻有趣的東西,舉例來說,如果可以透過 WebAssembly 編譯,再配合之前的「把 SQLite 的 VFS 掛上 WebTorrent 的 PoC Demo」,可以省下不少傳輸的量...

可以看看後續會不會真的有人這樣幹 XD

直接用 SQLite 查詢 Excel 檔案的 XLite (還有 dsq)

Hacker News 上看到「Xlite: Query Excel and Open Document spreadsheets as SQLite virtual tables (github.com/x2bool)」這個專案,就如同說明,是一個支援讀取 Excel 檔案的 SQLite extension,原網站在 x2bool/xlite 這邊。

依照說明支援舊的 .xls 與新的 .xlsx 的格式,但不知道公式運算支援到什麼程度...

先 load extension:

sqlite3 # will open SQLite CLI
> .load libxlite

接著是建立 virtual table:

CREATE VIRTUAL TABLE class_data USING xlite(
    FILENAME './path/to/example.xlsx',
    WORKSHEET 'Class Data',
    RANGE 'A2:F'
);

接下來就可以搞事了:

SELECT COUNT(*), D FROM class_data GROUP BY D ORDER BY COUNT(*);

看起來是用 Rust + C 寫的,然後作者有提醒這是寫興趣的專案:

This project is experimental, use at your own risk. The project is developed in my free time as a way to learn Rust and database systems.

反倒是 Hacker News 討論串裡面提到了 multiprocessio/dsq 這個專案,看起來發展的比較久,支援度也比較完整了:

Commandline tool for running SQL queries against JSON, CSV, Excel, Parquet, and more

不過就不是綁 SQLite 了 (雖然還是有關),從範例可以看到他是獨立的程式:

$ dsq testdata.json "SELECT * FROM {} WHERE x > 10"

Under the hood dsq uses DataStation as a library and under that hood DataStation uses SQLite to power these kinds of SQL queries on arbitrary (structured) data.

如果是真的要用的話,這套看起來應該會好一些...

在 Shell 下一行用 SQLite 查詢 CSV 內的資料

Simon Willison 這邊看到 command line 下用 SQLite 的技巧:「One-liner for running queries against CSV files with SQLite」。

範例指令是這樣 (整理了一下排版):

sqlite3 :memory: \
    -cmd '.import -csv taxi.csv taxi' \
    'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

可以看出來這個方式是將 csv 檔先讀到 in-memory database (:memory:),再用 SQLite 下指令處理,另外也可以自己變化,應該可以透過 /dev/stdin 這樣的方式讀 pipe 的東西。

拿來簡單跑一些東西應該還不賴?

Cloudflare 的 D1 (SQLite as a service)

Hacker News Daily 上看到 Cloudflare 推出了新產品 D1:「Announcing D1: our first SQL database」,在 Hacker News 上對應的討論在「D1: Our SQL database (cloudflare.com)」這邊可以看到。

就如同 Hacker News 上的討論提到的,這篇文章不像一般的 Cloudflare 文章會帶有很多技術上的說明 (尤其是在描述技術產品),這篇算是非常的行銷導向的文章,目前大家只能靠「猜」的去理解:

For a Cloudflare article, this one is surprisingly light on technical details. And for the product where it most matters.

翻了一下這兩個屬名的作者,Rita Kozlov 是 Director of Product at Cloudflare,而 Glen Maddern 是 Systems Engineer at Cloudflare。

目前知道的是,D1 是架構在 SQLite 上面:

D1 is built on SQLite.

然後從範例的程式碼內可以看到,在 JavaScript 裡面的用法是透過 await env.DB.get() 操作:

export default {
  async fetch(request, env, ctx) {
    const { pathname } = new URL(request.url)
    if (pathname === '/num-products') {
      const { result } = await env.DB.get(`SELECT count(*) AS num_products FROM Product;`)
      return new Response(`There are ${result.num_products} products in the D1 database!`)
    }
  }
}

然後從 screenshot 上沒有看到 region,但是 class 那邊出現了一個 tokyo3 不知道是什麼東西:

目前看到的就這些,沒提到 replication 機制 (這其實還蠻重要的,某些資料是有法規限制的),另外開發者會很在意的 performance 或是 latency 也沒提到 (所以可以預期應該不會太好?)。

另外在 RDBMS 內很重要的 ACID 特性與分散式系統中 CAP theorem 的性質也都沒有分析。

可以先放著看看就好...

把 SQLite 的 VFS 掛上 WebTorrent 的 PoC Demo

Hacker News Daily 上看到「Static torrent website with peer-to-peer queries over BitTorrent on 2M records (boredcaveman.xyz)」這個討論,作者試著在網頁上跑 SQLite + VFS + WebTorrent

這好像是這陣子一連串的 combo 技累積出來的東西:

  • 首先當然是把 SQLite 丟到網頁上跑的「sql.js」,這個專案比較久了,2019 年有第一個 release;
  • 然後最近有人透過 HTTP Range (Byte serving) 實做 SQLite VFS 的「sql.js-httpvfs」,這樣就不需要一次下載整包 SQLite;

接下來就是文章作者把 HTTP Range 換掉,改用 BitTorrent 的 pieces 來處理,在網頁端的話就順勢拿 WebTorrent 來用,對於很熱門的網站來說還蠻有趣的設計,但也可以預期網頁的反應速度應該不會太快,偏 PoC...

SQLite 3.37.0 以及 STRICT table 的設計

Hacker News 首頁上看到「SQLite Release 3.37.0 (sqlite.org)」,原文在「SQLite Release 3.37.0 On 2021-11-27」這邊。

這個版本引入了 STRICT Tables,先前在「SQLite 目前在規劃的 Strict Table,以及我從來不知道原來可以這樣惡搞...」這邊有提過。

官方給出來的範例是這樣,如果沒有要求 STRICT 的話,可以看到各種變化:

CREATE TABLE t1(a ANY);
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: integer 123

加上 STRICT 後就會與「預期」的結果比較接近:

CREATE TABLE t1(a ANY) STRICT;
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: text '000123'

對於希望 database 在處理資料嚴謹一點的人來說,應該是個不錯的新功能,但畢竟不是預設值,對於剛跨進來用的人應該還是有中獎機會 XD

用 PostgreSQL 的 int4range 與 GiST

發現自己根本還不熟悉 PostgreSQL 的特性,寫一下記錄起來。

產品上常常會有 coupon 與 voucher 之類的設計,這時候通常都會設定 coupon 或 voucher 的有效期間,在 MySQL 的環境下可能會這樣設計:

CREATE TABLE coupon (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(255) NOT NULL,
  started_at INT UNSIGNED NOT NULL,
  ended_at INT UNSIGNED NOT NULL
);

另外是設計 index 的部份,在產品推出夠久後,通常是過期的 coupon 或 voucher 會比目前還有效的多,而還沒生效的 coupon 與 voucher 通常都不多,所以會設計成對 ended_at 放一組 B-tree index:

CREATE INDEX ON t1 (ended_at);

這個設計不算差,不過用了一些假設。

如果不想要用這些假設,可以改用 Spatial 的資料型態去模擬並且加上 index (使用到 LineString Class),這樣就直接對 a < x < b 這類查詢更有效率,不過缺點就是可讀性會比較差。

在 PostgreSQL 這邊就有更清晰的資料結構來處理這些事情,主要是有一般性的 int4rangeint8range 以及時間類的 tsrangetstzrangedaterange (參考「Range Types」這邊有更多資料型態),所以會變成:

CREATE TABLE coupon (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  code VARCHAR NOT NULL,
  active_at INT4RANGE NOT NULL
);

然後用 GIST 建立 index:

CREATE INDEX ON t1 USING GIST(active_at);

後續的 query 語法就用 <@ 的語法:

SELECT COUNT(*) FROM coupon WHERE 10000 <@ active_at;

塞了 10M 筆資料後的 table 可以看到本來需要的時間是:

Time: 779.542 ms

變成:

Time: 5.510 ms

不過缺點就是 SQLite 沒支援這些資料型態,對於 test case 就一定得跑個 PostgreSQL 起來測...

SQLite 目前在規劃的 Strict Table,以及我從來不知道原來可以這樣惡搞...

Hacker News Daily 上看到「STRICT Tables」這篇,在講 SQLite 目前在規劃 strict table,對應的討論可以參考「Strict Tables – Column type constraints in SQLite - Draft (sqlite.org)」這邊。

我在 draft 文件開頭看到這個驚人的事實:轉型失敗的時候會直接寫進去,不是錯誤或是 0 或是 NULL 之類的值 XDDD

For example, if a table column has a type of "INTEGER", then SQLite tries to convert anything inserted into that column into an integer. So an attempt to insert the string '123' results in an integer 123 being inserted. But if the content cannot be losslessly converted into an integer, for example if the input is 'xyz', then the original string is inserted instead. See the Datatypes In SQLite document for additional information.

實際上測試建了一個表格測試:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY NOT NULL, col1 INTEGER);
sqlite> INSERT INTO a (id, col1) VALUES (1, 'a');
sqlite> SELECT * FROM a;
id          col1      
----------  ----------
1           a         

我果然跟 SQLite 不熟...

快速產生 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...