Чтение и запись файлов в SQLite

Иногда бывает удобно загрузить набор данных из внешнего файла или выгрузить результаты запроса в файл.

По умолчанию SQLite не поддерживает работу с файловой системой. Но вы легко можете включить ее с помощью расширения sqlean-fileio.

Примечание. В отличие от других СУБД, добавить расширение в SQLite элементарно. Скачиваете файл, выполняете одну команду — и готово.

sqlean-fileio помогает загружать и выгружать данные:

• Загрузить JSON-документ из файла.
• Построчно прочитать текстовый файл.
• Построчно записать результаты выполнения запроса в файл.
• Импортировать файлы из каталога.

Давайте посмотрим на примеры.

Загрузить JSON-документ из файла

Допустим, есть JSON-файл с информацией о сотрудниках:

{
    "employees": [
        { "id": 11, "name": "Diane", "salary": 70 },
        { "id": 12, "name": "Bob", "salary": 78 },
        { "id": 21, "name": "Emma", "salary": 84 },
        { "id": 22, "name": "Grace", "salary": 90 },
        { "id": 23, "name": "Henry", "salary": 104 },
        { "id": 24, "name": "Irene", "salary": 104 },
        { "id": 25, "name": "Frank", "salary": 120 },
        { "id": 31, "name": "Cindy", "salary": 96 },
        { "id": 32, "name": "Dave", "salary": 96 },
        { "id": 33, "name": "Alice", "salary": 100 }
    ]
}

И таблица employees:

create table employees (
  id integer primary key,
  name text,
  salary integer
);

Чтобы загрузить данные из файла в таблицу, скомбинируем функции fileo_read() и json_tree():

insert into employees(id, name, salary)
select
  json_extract(value, '$.id'),
  json_extract(value, '$.name'),
  json_extract(value, '$.salary')
from json_tree(
  fileio_read('employees.json')
)
where type = 'object' and fullkey like '$.employees%';

fileio_read() читает данные из файла, а json_tree() итерирует по объектам внутри JSON-массива employees. В результате данные будут разложены по столбцам и строкам таблицы employees:

select * from employees;

┌────┬───────┬────────┐
│ id │ name  │ salary │
├────┼───────┼────────┤
│ 11 │ Diane │ 70     │
│ 12 │ Bob   │ 78     │
│ 21 │ Emma  │ 84     │
│ 22 │ Grace │ 90     │
│ 23 │ Henry │ 104    │
│ 24 │ Irene │ 104    │
│ 25 │ Frank │ 120    │
│ 31 │ Cindy │ 96     │
│ 32 │ Dave  │ 96     │
│ 33 │ Alice │ 100    │
└────┴───────┴────────┘

Построчно прочитать текстовый файл

Если файл большой (например, лог на миллион записей), то загружать его в память одним куском через fileo_read() может быть плохой идеей. Лучше считать построчно.

Допустим, есть файл app.log с миллионом строк:

ts=2023-02-26 13:00:00,level=INFO,message=begin processing
ts=2023-02-26 13:01:00,level=INFO,message=processed 1000 records
ts=2023-02-26 13:02:00,level=INFO,message=processed 2000 records
ts=2023-02-26 13:03:00,level=INFO,message=processed 3000 records
ts=2023-02-26 13:03:25,level=ERROR,message=invalid record data
ts=2023-02-26 13:03:25,level=INFO,message=processing failed
...

И таблица app_log:

create table app_log (
  line text
);

Построчно обойдем файл с помощью fileio_scan() и добавим записи в таблицу:

insert into app_log(line)
select value from fileio_scan('app.log');

select count(*) from app_log;
-- 1000000

Теперь извлечем отдельные поля лога с помощью функции regexp_capture из расширения sqlean-regexp:

alter table app_log add column ts text;
alter table app_log add column level text;
alter table app_log add column message text;

update app_log set ts = regexp_capture(line, 'ts=([^,]+)', 1);
update app_log set level = regexp_capture(line, 'level=([^,]+)', 1);
update app_log set message = regexp_capture(line, 'message=([^,]+)', 1);

В результате каждое поле лога хранится в отдельном столбце:

select ts, level, message from app_log limit 5;

┌─────────────────────┬───────┬────────────────────────┐
│         ts          │ level │        message         │
├─────────────────────┼───────┼────────────────────────┤
│ 2023-02-26 13:00:00 │ INFO  │ begin processing       │
│ 2023-02-26 13:01:00 │ INFO  │ processed 1000 records │
│ 2023-02-26 13:02:00 │ INFO  │ processed 2000 records │
│ 2023-02-26 13:03:00 │ INFO  │ processed 3000 records │
│ 2023-02-26 13:03:25 │ ERROR │ invalid record data    │
└─────────────────────┴───────┴────────────────────────┘

Удобно!

Построчно записать результаты в файл

Допустим, мы хотим выгрузить записи лога с уровнем ERROR в отдельный файл. Для этого используем fileio_append():

select sum(
  fileio_append('error.log', printf('%s: %s', ts, message) || char(10))
) from app_log
where level = 'ERROR';

Вот error.log после выгрузки:

2023-02-26 13:03:25: invalid record data

Импортировать файлы из каталога

Допустим, есть несколько лог-файлов:

app.log.1
app.log.2
app.log.3
...

Загрузим их одним запросом с помощью функции fileio_ls().

Для начала проверим, что выбираем только подходящие файлы:

select * from fileio_ls('logs')
where name like 'logs/app.log%';

┌────────────────┬───────┬────────────┬──────┐
│      name      │ mode  │   mtime    │ size │
├────────────────┼───────┼────────────┼──────┤
│ logs/app.log.2 │ 33188 │ 1677425479 │ 316  │
│ logs/app.log.3 │ 33188 │ 1677425496 │ 377  │
│ logs/app.log.1 │ 33188 │ 1677425467 │ 316  │
└────────────────┴───────┴────────────┴──────┘

Выглядит нормально. Теперь загрузим файлы в таблицу logs:

create table logs(fname text, line text);

with files as (
  select name from fileio_ls('logs')
  where name like 'logs/app.log%'
)
insert into logs(fname, line)
select files.name, value from fileio_scan(files.name), files;

Убедимся, что все записи логов попали в таблицу:

select fname, count(*)
from logs
group by fname;

┌────────────────┬──────────┐
│     fname      │ count(*) │
├────────────────┼──────────┤
│ logs/app.log.1 │ 5        │
│ logs/app.log.2 │ 5        │
│ logs/app.log.3 │ 6        │
└────────────────┴──────────┘

Порядок!

Установка

1. Скачайте свежий релиз

2. Подключите в командной строке SQLite (sqlite.exe):

sqlite> .load ./fileio
sqlite> select * from fileio_read('data.txt');

Как установить расширение для IDE, Python и других сценариев.

Документация расширения с подробным описанием.