Чтение и запись файлов в 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 │
└────────────────┴──────────┘
Порядок!
Установка
Скачайте свежий релиз
Подключите в командной строке SQLite (
sqlite.exe):
sqlite> .load ./fileio
sqlite> select * from fileio_read('data.txt');
Как установить расширение для IDE, Python и других сценариев.
Документация расширения с подробным описанием.
★ Подписывайтесь на твитер.