无论你是研发、数据分析师、QA工程师、DevOps负责人,或是产品经理,SQLite 都是完美的工具。理由如下。
从一些基础的事实说起:
- SQLite是世界上最常用的 DBMS(数据库管理系统),所有的流行系统都带有它
- SQLite是serverless的
- 对于开发者来说,SQLite 直接嵌入到 app 之中
- 对于其他人来说,SQLite 有便利的数据库控制台(REPL,命令行),以单文件的形式被提供。(Windows的sqlite3.exe, Linux / macOS上的sqlite3)
控制台,导入与导出
控制台是SQLite为数据分析提供的杀手应用:比Excel强大,比pandas简单。一行命令就可以导入csv数据,表会被自动建好:
> .import --csv city.csv city > select count(*) from city; 1117
控制台支持基础的SQL功能,并且会以友善的 ASCII 表格绘制查询结果。同时支持高级的SQL功能,之后详述。
select century || ‘ century‘ as dates, count(*) as city_count from history group by century order by century desc;
┌────────────┬────────────┐ │ dates │ city_count │ ├────────────┼────────────┤ │ 21 century │ 1 │ │ 20 century │ 263 │ │ 19 century │ 189 │ │ 18 century │ 191 │ │ 17 century │ 137 │ │ ... │ ... │ └────────────┴────────────┘
可以导入的数据包括SQL、CSV、JSON,甚至markdown和HTML。只需要几行命令:
.mode json .output city.json select city, foundation_year, timezone from city limit 10; .shell cat city.json
[ { "city": "Amsterdam", "foundation_year": 1300, "timezone": "UTC+1" }, { "city": "Berlin", "foundation_year": 1237, "timezone": "UTC+1" }, { "city": "Helsinki", "foundation_year": 1548, "timezone": "UTC+2" }, { "city": "Monaco", "foundation_year": 1215, "timezone": "UTC+1" }, { "city": "Moscow", "foundation_year": 1147, "timezone": "UTC+3" }, { "city": "Reykjavik", "foundation_year": 874, "timezone": "UTC" }, { "city": "Sarajevo", "foundation_year": 1461, "timezone": "UTC+1" }, { "city": "Stockholm", "foundation_year": 1252, "timezone": "UTC+1" }, { "city": "Tallinn", "foundation_year": 1219, "timezone": "UTC+2" }, { "city": "Zagreb", "foundation_year": 1094, "timezone": "UTC+1" } ]
如果相比于控制台爱好者你更倾向于BI,流行的数据分析工具如Metabase, Redash, 和 Superset 都支持 SQLite。
原生JSON
分析和转换JSON领域,没有什么比SQLite更方便的了。你可以从文件中直接选取数据,就如同对一张普通的表进行操作。或者将数据导入到表,并且从表中查询。
select json_extract(value, ‘$.iso.code‘) as code, json_extract(value, ‘$.iso.number‘) as num, json_extract(value, ‘$.name‘) as name, json_extract(value, ‘$.units.major.name‘) as unit from json_each(readfile(‘currency.sample.json‘)) ;
┌──────┬─────┬─────────────────┬──────────┐ │ code │ num │ name │ unit │ ├──────┼─────┼─────────────────┼──────────┤ │ ARS │ 032 │ Argentine peso | peso │ │ CHF │ 756 │ Swiss Franc │ franc │ │ EUR │ 978 │ Euro │ euro │ │ GBP │ 826 │ British Pound │ pound │ │ INR │ 356 │ Indian Rupee │ rupee │ │ JPY │ 392 │ Japanese yen │ yen │ │ MAD │ 504 │ Moroccan Dirham │ dirham │ │ RUR │ 643 │ Russian Rouble │ rouble │ │ SOS │ 706 │ Somali Shilling │ shilling │ │ USD │ 840 │ US Dollar │ dollar │ └──────┴─────┴─────────────────┴──────────┘
不管JSON深度有多深,你可以提取任何深层的对象:
select json_extract(value, ‘$.id‘) as id, json_extract(value, ‘$.name‘) as name from json_tree(readfile(‘industry.sample.json‘)) where path like ‘$[%].industries‘ ;
┌────────┬──────────────────────┐ │ id │ name │ ├────────┼──────────────────────┤ │ 7.538 │ Internet provider │ │ 7.539 │ IT consulting │ │ 7.540 │ Software development │ │ 9.399 │ Mobile communication │ │ 9.400 │ Fixed communication │ │ 9.401 │ Fiber-optics │ │ 43.641 │ Audit │ │ 43.646 │ Insurance │ │ 43.647 │ Bank │ └────────┴──────────────────────┘
常见表单表达式和集合操作
当然了,SQLite支持常见表单表达式(如with语句)和 join 等,我甚至没有必要在这里举例。如果数据是分层级的(表引用自身,比如通过parent_id)- with recursive 就变得很实用。任何层级,无论多深,都可以通过一句查询轻松展开。
with recursive tmp(id, name, level) as ( select id, name, 1 as level from area where parent_id is null union all select area.id, tmp.name || ‘, ‘ || area.name as name, tmp.level + 1 as level from area join tmp on area.parent_id = tmp.id ) select * from tmp;
┌──────┬──────────────────────────┬───────┐ │ id │ name │ level │ ├──────┼──────────────────────────┼───────┤ │ 93 │ US │ 1 │ │ 768 │ US, Washington DC │ 2 │ │ 1833 │ US, Washington │ 2 │ │ 2987 │ US, Washington, Bellevue │ 3 │ │ 3021 │ US, Washington, Everett │ 3 │ │ 3039 │ US, Washington, Kent │ 3 │ │ ... │ ... │ ... │ └──────┴──────────────────────────┴───────┘
集合?没有问题:UNION,INTERSECT,EXCEPT 全部可以使用。
select employer_id from employer_area where area_id = 1 except select employer_id from employer_area where area_id = 2;
通过其他表格的数据计算某列的值?输入被创建的列:
alter table vacancy add column salary_net integer as ( case when salary_gross = true then round(salary_from/1.04) else salary_from end );
被创建的列可以像普通列一样被查询:
select substr(name, 1, 40) as name, salary_net from vacancy where salary_currency = ‘JPY‘ and salary_net is not null limit 10;
数学统计
描述性统计?简单:均值、中位数、百分位数、标准差,你想到的基本都有。需要读一个插件,不过也只一行命令而已:
.load sqlite3-stats select count(*) as book_count, cast(avg(num_pages) as integer) as mean, cast(median(num_pages) as integer) as median, mode(num_pages) as mode, percentile_90(num_pages) as p90, percentile_95(num_pages) as p95, percentile_99(num_pages) as p99 from books;
┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐ │ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │ ├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤ │ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │ └────────────┴──────┴────────┴──────┴─────┴─────┴──────┘
关于插件的备注:与其他DBMS比如PostgreSQL相比,SQLite缺失了很多功能。但是这些功能都很好添加,大家一般也都会来添加插件,因此往往造成一些混乱。
因此,我决定制作一些稳定的插件,按照领域分类,并为主流的操作系统编译。目前已经完成了一些,更多的还在制作中。sqlean @ GitHub
来看些更有趣的。你可以将数据分布直接在控制台中作图。多可爱啊。
with slots as ( select num_pages/100 as slot, count(*) as book_count from books group by slot ), max as ( select max(book_count) as value from slots ) select slot, book_count, printf(‘%.‘ || (book_count * 30 / max.value) || ‘c‘, ‘*‘) as bar from slots, max order by slot;
┌──────┬────────────┬────────────────────────────────┐ │ slot │ book_count │ bar │ ├──────┼────────────┼────────────────────────────────┤ │ 0 │ 116 │ ********* │ │ 1 │ 254 │ ******************** │ │ 2 │ 376 │ ****************************** │ │ 3 │ 285 │ ********************** │ │ 4 │ 184 │ ************** │ │ 5 │ 90 │ ******* │ │ 6 │ 54 │ **** │ │ 7 │ 41 │ *** │ │ 8 │ 31 │ ** │ │ 9 │ 15 │ * │ │ 10 │ 11 │ * │ │ 11 │ 12 │ * │ │ 12 │ 2 │ * │ └──────┴────────────┴────────────────────────────────┘
性能
SQLite 可以顺利处理亿级数据量的数据。通常的INSERT操作在我的笔记本上每秒可以跑24万条。另外,如果你是做CSV文件关联到虚拟表(通过插件)的话,INSERT操作还可以再快2倍。
.load sqlite3-vsv create virtual table temp.blocks_csv using vsv( filename="ipblocks.csv", schema="create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)", columns=10, header=on, nulls=on );
.timer on insert into blocks select * from blocks_csv; Run Time: real 5.176 user 4.716420 sys 0.403866
select count(*) from blocks; 3386629 Run Time: real 0.095 user 0.021972 sys 0.063716
开发者间流行的观点是SQLite不适用于互联网,因为它不支持并行查询。这是错觉。在很早就支持的write-ahead log模式下,想要多少并发读取就可以有多少。只能有一个并发写入,不过通常一个就够了。
SQLite 完美适用于小型网站和应用。sqlite.org 使用SQLite 作为数据库,在不优化的情况下(大概每页200个请求)。它每个月可以处理70万的访问并且比我见过的95%的网站都更快。
文档,图,搜索
SQLite支持部分索引以及索引到表达式,就像那些“大型”DBMS一样。你可以索引创建的列,甚至可以吧SQLite转换为一个文档数据库。只需要存入原始JSON,并且在json_extract()得到的列上建立索引:
create table currency( body text, code text as (json_extract(body, ‘$.code‘)), name text as (json_extract(body, ‘$.name‘)) ); create index currency_code_idx on currency(code); insert into currency select value from json_each(readfile(‘currency.sample.json‘));
explain query plan select name from currency where code = ‘EUR‘; QUERY PLAN `--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)
你也可以像用图数据库一样来用SQLite。可以通过一系列复杂的WITH RECURSIVE来实现,你比较倾向加点Python也行:
全文搜索开箱即用:
create virtual table books_fts using fts5(title, author, publisher); insert into books_fts select title, author, publisher from books; select author, substr(title, 1, 30) as title, substr(publisher, 1, 10) as publisher from books_fts where books_fts match ‘ann‘ limit 5;
┌─────────────────────┬────────────────────────────────┬────────────┐ │ author │ title │ publisher │ ├─────────────────────┼────────────────────────────────┼────────────┤ │ Ruby Ann Boxcar │ Ruby Ann‘s Down Home Trailer P │ Citadel │ │ Ruby Ann Boxcar │ Ruby Ann‘s Down Home Trailer P │ Citadel │ │ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │ │ Daniel Defoe │ Robinson Crusoe │ Ann Arbor │ │ Ann Thwaite │ Waiting for the Party: The Lif │ David R. G │ └─────────────────────┴────────────────────────────────┴────────────┘
或者你需要一个内存数据库来进行一些即时的运算?一行Python搞定:
db = sqlite3.connect(":memory:")
你甚至可以通过多个连接请求它:
db = sqlite3.connect("file::memory:?cache=shared")
还有更多...
有炫酷的窗函数(window functions),就像PostgreSQL。UPSERT,UPDATE FROM,以及generate_series()。R树索引。正则表达式,模糊搜索,以及地理数据(geo)。
在功能层面,SQLite可以与任何“大型”DBMS竞争。
围绕SQLite有一批很棒的工具。我尤其喜欢 Datasette —— 一个探索、发布SQLite数据集的开源工具。 DBeaver 是另一个优秀的开源数据库IDE,并且支持最新版本的SQLite。
我希望这篇文章能够促使你尝试下 SQLite。感谢阅读!