改めてSQLiteを知る、シンプルはシンプルじゃない

???? 勤労は勤勉に行うが、遊戯は行わない。行いは思慮深く行うが、追随して破壊する。-- 韓裕

みなさん、こんにちは。ヘアカットの話ではなく、テクニックの話ばかりする先生、トニーです。

世界で最も使われているデータベース製品は何かと聞かれたら、Oracle、MySQL、Microsoft SQL Serverのどれかと答えますか？

上記のどれでもない。世界で最も多くインストールされ、利用されているデータベースは SQLite . そうです、小さな組み込み型データベースエンジンです。すべての電話、コンピュータ、ブラウザ、そして無数のアプリケーションにSQLiteデータベースが組み込まれており、PHPやPython言語にもSQLiteサポートが組み込まれていて、推定1兆（10）個のSQLiteデータベースが使用されています。 ¹² )以上となります。

SQLiteは、開発者/テスト担当者、データアナリスト/科学者、IT運用担当者、プロダクトマネージャにとって非常に有用なツールです。この記事では、SQLiteが提供する便利な機能のいくつかを見ていきます。もし、この記事が役に立つと思われましたら、お気軽にコメントください。をお願いします。おすすめは？

コマンドラインツール

SQLite は非常に便利なデータベースです。 コンソール データアナリストにとってはExcelよりも強力ですが、Pythonのpandasよりもシンプルです。コマンドでCSVファイルをインポートすれば、対応するデータテーブルが自動的に作成されます。

> .import --csv city.csv city
> select count(*) from city;
1117

SQLiteコマンドラインツールは、様々なSQLステートメントをサポートし、クエリ結果をASCIIスタイルで表示します。

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

SQLiteはJSONデータの解析と変換を非常に簡単に行うことができ、ユーザーはファイルから直接データを問い合わせたり、テーブルにインポートしてから問い合わせを行ったりすることができるようになります。

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オブジェクトが何層のネストを含んでいても、SQLiteはその中のデータをフェッチすることができます。

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 │
└────────┴──────────────────────┘

CTE 与集合运算

SQLite 支持 通用表达式 （共通テーブル式）和连接查询，对于具有层级关系的数据（例如组织构等），可以通过 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 │
│ ...  │ ...                      │ ...   │
└──────┴──────────────────────────┴───────┘

SQLite 还提供了 UNION、INTERSECT 以及 EXCEPT 集合运算符： 还提供了 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;

统计函数

通过加载 統計插件，SQLite 支持以下描述性统计：均值，中位数，百分位，标准差别。

.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 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

SQLite 比其他数据库管理系统提供的函数更少一些，不过可以通过扩展插件的方式获得额外的支持。 这个项目 按照不同的领域编集译文了一些常用的插件。

以下示例在控制台中描画绘了一个数据分布图：。

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 语句也可以达到 10 万条/秒以上如果使用虚拟连接 CSV 文件，插入性能会更好：件，插入性能会更好。

.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 は同時アクセスをサポートしていないので、Web アプリケーションのバックエンドデータベースとして適していないと考えています。これは実際に噂で、SQLite は write-ahead log モードで同時読み出しを提供します。書き込みは単一のプロセスに対してしかできませんが、多くの場合これで十分です。

sqlite.orgはデータベースとしてSQLiteを使用しており、最適化なし（各ページはおよそ200のクエリーリクエストを含む）で、95%のウェブサイトを凌駕しながら月70万アクセスを処理することが可能です。

文書・グラフ・全文検索

SQLite は部分インデックスと式インデックス (関数インデックス) をサポートしており、計算されたカラムに基づくインデックスを作成することができます。 SQLiteをドキュメントデータベースとして利用する 使用しています。

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=?)

WITH RECURSIVEクエリを使えば、SQLiteをグラフデータベースとして使うこともできますし、次のように使うこともできます。 シンプルグラフ (Pythonモジュール)を使用します。

SQLiteは組み込みの全文検索機能を提供します。

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 │
└─────────────────────┴────────────────────────────────┴────────────┘

インメモリデータベースを中間計算モジュールとして動作させたい場合、必要なのは1行のPythonコードだけです。

db = sqlite3.connect(":memory:")

複数の接続でアクセスすることも可能です。

db = sqlite3.connect("file::memory:?cache=shared")

その他の機能

SQLite は他にもウィンドウ関数、UPSERT 文、UPDATE FROM、generate_series() 関数、R-tree インデックス、正規表現、ファジー検索、GEO など、多くの高度な機能を提供します。

SQLiteの管理用開発ツールをお探しなら、フリーでオープンソースの2つのツールをお勧めします。 Dビーバー と DB Browser for SQLite .