【翻译】SQLite 不只是玩具数据库而已

无论你是研发、数据分析师、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,流行的数据分析工具如MetabaseRedash, 和 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  │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 14833492952566408171199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

关于插件的备注:与其他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               │
├──────┼────────────┼────────────────────────────────┤
│ 0116        │ *********                      │
│ 1254        │ ********************           │
│ 2376        │ ****************************** │
│ 3285        │ **********************         │
│ 4184        │ **************                 │
│ 590         │ *******                        │
│ 654         │ ****                           │
│ 741         │ ***                            │
│ 831         │ **                             │
│ 915         │ *                              │
│ 1011         │ *                              │
│ 1112         │ *                              │
│ 122          │ *                              │
└──────┴────────────┴────────────────────────────────┘

性能

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也行:

simple-graph @ GitHub

全文搜索开箱即用:

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 Anns Down Home Trailer P │ Citadel    │
│ Ruby Ann Boxcar     │ Ruby Anns 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。感谢阅读!

 

【翻译】SQLite 不只是玩具数据库而已

上一篇:MySql查询报错:Illegal mix of collations


下一篇:jmeter操作 mysql 配置参数(一)