SQLite虚拟表不虚

2022-03-17 00:00:00 专区扩展虚拟加载逗号

前言

熟悉PostgreSQL的朋友，都知道它有一个很强大的功能FDW（Foreign Data Wrappers, 外部数据包装器），通过FDW，你可以方便地在PostgreSQL里访问外部数据库或者文件。有兴趣的可以看我介绍的clickhouse fdw。

测试开源项目q时（如何让你的Python应用程序分发变得很容易?），发现当CSV数据量比较大的时候，速度不是很理想，于是我想是不是可以借鉴PostgreSQL的FDW思路，实现不需要每次导入CSV，就可以查询呢？经过一番探索发现，SQLite早为我们想到了，这就是虚拟表（Virtual Table）。

官方提供的虚拟表列表里就有csv虚拟表的代码，

一个虚拟表，将逗号分隔值或CSV文件（ RFC 4180 ）表示为只读表，因此可以用作较大查询的一部分。

如果想使用这个虚拟表功能，需要自己编译并加载。

编译

gcc -g -fPIC  -I/Users/steven/anaconda3/include/ \-L/Users/steven/anaconda3/lib/ \-lsqlite3 -shared csv.c -o csv.so

加载与测试，

import sqlite3#创建连接con = sqlite3.connect(":memory:")
# 允许加载扩展con.enable_load_extension(True)
# 加载csv扩展con.execute("select load_extension('csv.so');")#禁止加载扩展con.enable_load_extension(False)
如果存在表diamonds，删除它，并创建临时虚拟表diamondssql="""drop table if exists diamonds;CREATE VIRTUAL TABLE temp.diamonds USING csv(filename='/Users/steven/data/diamonds.txt',header=1);"""#执行SQL脚本（多条语句）con.executescript(sql)
#查看diamonds表结构for row in con.execute("PRAGMA table_info(diamonds);"):    print(row)#把钻石售价按5000美金一档汇总数量（仅是测试）for row in con.execute("select price/5000,count(*) from diamonds group by 1"):    print(row)con.close()

#返回(, 'carat', 'TEXT', , None, )(1, 'cut', 'TEXT', , None, )(2, 'color', 'TEXT', , None, )(3, 'clarity', 'TEXT', , None, )(4, 'depth', 'TEXT', , None, )(5, 'table', 'TEXT', , None, )(6, 'price', 'TEXT', , None, )(7, 'x', 'TEXT', , None, )(8, 'y', 'TEXT', , None, )(9, 'z', 'TEXT', , None, )(, 39213)(1, 9504)(2, 3567)(3, 1656)#耗时time: 56.3 ms

对比q

python q.py -H -d "," -O -b \"select price/5000,count(*) from  \/Users/steven/data/diamonds.txt group by 1"

返回price/5000,count(*)         ,39213   1         ,9504    2         ,3567    3         ,1656
#耗时time: 507 ms

对比发现，近10倍速度的差异。

不过这个官方提供的csv扩展，还有些局限，仅仅支持逗号分隔符的CSV，对于其它格式的，需要改写代码扩展。

CSV虚拟表的其它实现

SQLiteODBC的源码也提供了一份类似的实现，csvtable
spatialite 的VirtualText（spatialite的重点是GIS，类似PostGIS的实现，以后会单独介绍这个很牛的扩展）

其它强大的扩展

Parquet虚拟表，实现在SQLite访问Parquet格式的文件
SpatiaLite:类似于PostGIS, Oracle Spatial, 和 SQL Server 的 spatial 扩展

链接与参考

https://www.sqlite.org/vtab.html
https://www.sqlite.org/lang_createvtab.html

http://www.ch-werner.de/sqliteodbc/
https://github.com/cldellow/sqlite-parquet-vtable
https://www.gaia-gis.it/fossil/libspatialite/index

相关文章