- PVSM.RU - https://www.pvsm.ru -
Вступление никому не интересно, поэтому начну сразу с примеров использования
% cat log.json
{"type": "hit", "client": {"ip": "127.1.2.3"}}
{"type": "hit", "client": {"ip": "127.2.3.4"}}
{"type": "hit", "client": {"ip": "127.3.4.5"}}
{"type": "hit", "client": {"ip": "127.3.4.5"}}
{"type": "hit", "client": {"ip": "127.1.2.3"}}
{"type": "click", "client": {"ip": "127.1.2.3"}}
{"type": "click", "client": {"ip": "127.2.3.4"}}
Выполняем запрос:
% cat log.json | jl-sql 'SELECT client.ip, COUNT(*) AS count WHERE type = "hit" GROUP BY client.ip'
{"client":{"ip":"127.1.2.3"},"count":2}
{"client":{"ip":"127.2.3.4"},"count":1}
{"client":{"ip":"127.3.4.5"},"count":2}
Как вы могли заметить, для выполнения запроса используется утилита jl-sql [1], которая принимает на вход поток JSON-объектов, разделённых символом перевода строки ("n"
).
Тут стоит отметить, что утилита основана на библиотеке jl-sql-api [2], на базе которой можно достаточно просто реализовать обработку данных в произвольном формате, не ограничиваясь только JSON.
SQL-движок поддерживает WHERE
, GROUP BY
, HAVING
, ORDER BY
и {LEFT|INNER} JOIN
. Что касается JOIN
ов, то тут есть ограничение на выражение в ON
: реализована поддержка объединений только по точному соответствию двух полей, так называемый Equi Join [3]:
SELECT id, @town.name AS townName JOIN `towns.json` ON @towns.id = townId
% cat log.json | jl-sql 'SELECT DISTINCT client.ip AS ip'
{"ip":"127.1.2.3"}
{"ip":"127.2.3.4"}
{"ip":"127.3.4.5"}
% cat log.json | jl-sql 'SELECT type, COUNT(DISTINCT client.ip) AS ipCount GROUP BY type'
{"type":"click","ipCount":2}
{"type":"hit","ipCount":3}
С помомощью альясов (AS
) можно не только назначать псевдонимы для полей, но и создавать сложные структуры внутри объекта:
% echo '{"a": 1, "b": 2}' | jl-sql 'SELECT a AS sub.bar.first, b AS sub.foo.second'
{"sub":{"bar":{"first":1},"foo":{"second":2}}}
Помимо SELECT
поддерживается так же DELETE
и UPDATE
% cat log.json | jl-sql -b :ip=127.1.2.3 'DELETE WHERE client.ip = :ip'
% cat log.json | jl-sql -b :ip=127.1.2.3 'UPDATE SET banned = true WHERE client.ip = :ip'
В этих примерах показывается ещё и использование биндингов (опция -b
/ --bind
), более подробно о них рассказывается в соответствующем разделе [4].
% echo '{"ts": "2017-01-18 00:00:00"}' | jl-sql 'SELECT ts - INTERVAL 2 MONTH 3 DAY 4 HOUR 5 MINUTE 6 SECOND AS past'
Ключевое слово INTERVAL
позволяет "добавлять" и "отнимать" отрезки времени от какой-то даты. Всё это аналогично использованию INTERVAL
в MySQL.
Так как в JSON не предусмотен отдельный тип данных под даты, то для их сохранения используются строки. Утилита понимает форматы из RFC2822 [5] или ISO 8601 [6]. Могут использоваться и другие, но результаты в таком случае будут непредсказуемые.
Следует отметить, что для парсинга и манипуляции датами используется системная временная зона. Если вас это не устраивает, то вы можете установить переменную окружения TZ
с нужной временной зоной перед запуском jl-sql
.
Более подробное описание работы с датами можете прочитать в документации на GitHub [7].
JOIN
)Для JOIN
требуется как минимум ещё один источник данных, в терминах SQL это называется "таблица", в качестве таких источников выступают обычные файлы:
% cat banned.json
{"ip": "127.1.2.3"}
% cat log.json | jl-sql -b :banned=banned.json 'SELECT * INNER JOIN {:banned} ON @banned.ip = client.ip'
{"type":"hit","client":{"ip":"127.1.2.3"}}
{"type":"hit","client":{"ip":"127.1.2.3"}}
{"type":"click","client":{"ip":"127.1.2.3"}}
В этом примере было введено новое понятие — источник данных (@banned
), более подробно об этом и вообще о JOIN можно прочитать в соответствующем разделе [8].
Утилита рассчитана на обработку больших логов. В зависимости от типа, запрос может выполняться либо в потоковом режиме, либо в режиме использования временного хранилища. Огромным плюсом потокового режима является возможность выполнять jl-sql [1] в связке, например, с tail -f
для фильтрации и переформатирования логов в реальном времени.
O(n)
по CPU и O(1)
по памяти. В таком режиме могут исполняться только самые простые запросы, которые не требуют сортировок: SELECT <fields...>
, SELECT ... WHERE expression
.GROUP BY
, ORDER BY
и JOIN
. Размеры внутренних буферов задаются опциями -B
и -S
(смотри описание опций [9]).Для сортировки больших объёмов jl-sql [1] использует системную утилиту sort
, что позволяет использовать более производительную нативную сортировку.
Код написан на JavaScript под Node.js, поэтому проще всего установить пакет через npm
:
# npm install -g jl-sql
Внимание: требуется версия Node.js не менее 6.0.0
Сортировку можно задать стандартным выражением ORDER BY expression [{DESC|ASC}]
, поддерживается сортировка сразу по нескольким полям, в том числе и разнонаправленно.
Неструктурированность данных на входе вносит свои коррективы: поле, по которому идёт сортировка, может отсутствовать в некоторых объектах или иметь разные типы данных в разных объектах. Поэтому, желательно всегда явно указывать тип данных функциями STRING()
и NUMBER()
:
Сортировка по строкам
ORDER BY STRING(field)
Сортировка по числам
ORDER BY NUMBER(field)
Если не указать явно тип сортировки, то утилита попытается определить тип по правилам, описанным здесь [10]. Если тип определить не удалось, то сортировка будет происходить по строкам.
Значения преобразуются в строку по следующим правилам:
Тип данных | Строковое представление |
---|---|
строка | исходная строка |
число | десятичная строка |
boolean | "true" / "false" |
null | "null" |
отсутствующее поле | "" (пустая строка) |
объект | N/A* |
массив | N/A* |
* — преобразование строк и массивов к строке не специфицируется, поэтому полагаться на порядок этих значений в итогом множестве не следует.
Рекомендую всегда явно задавать тип данных т.к. нынешнее поведение по-умолчанию может быть изменено или вовсе удалено, как потенциально опасное.
Для того, чтобы решить проблему экранирования данных в командной строке, утилита позволяет использовать биндинги (подстановки):
jl-sql -b :type=hit jl-sql 'SELECT client.ip, COUNT(*) AS count WHERE type = :type GROUP BY client.ip'
Здесь опция -b :type=hit
задаёт подстановку с именем :type
и значением "hit"
. Это делает возможным использовать обычное экранирование на уровне shell для составления SQL-запросов.
Аналогично, можно использовать подстановки для имён файлов, используемых в JOIN
:
jl-sql -b :towns=towns.json 'SELECT id, @town.name AS townName JOIN {:towns} ON @towns.id = townId'
Это позволит использовать автодополнение имён файлов в вашем любимом shell.
Более подробно конкретно про JOIN
можно почитать в соответствующем разделе JOIN [8].
JOIN
Поддержка JOIN
потребовала введения в синтаксис новой сущности — название источника данных (можно назвать это "таблицей"). Проблема здесь возникает в том, что в "классическом" SQL всегда можно определить, где в идентификаторе ident1.ident2
название таблицы, а где название поля. С JSON всё сложнее: объекты могут иметь разную вложенность, поэтому без специального синтаксиса невозможно точно сказать, что имел в виду пользователь, когда ввёл ident1.ident2
т.к. ident1
может быть как названием поля, так и названием "таблицы".
Для решения этой неоднозначности был введён синтаксис @ident
, где @
— префикс, указывающий, что следующий за ним идентификатор — это название "таблицы".
Вернёмся к примеру из начала статьи и разберём его более подробно:
% cat log.json | jl-sql -b :banned=banned.json 'SELECT * INNER JOIN {:banned} ON @banned.ip = client.ip'
Итак, начнём с начала:
-b :banned=banned.json
— создаём биндинг с названием файла источника, это не обязательный шаг, но он даёт возможность вашему shell делать автодополнение петей, а так же избавляет от необходимости экранирование специальных символов в пути
INNER JOIN {:banned}
— здесь {:banned}
— это специальный синтаксис для подстановки биндинга в JOIN
. Без использования биндингов эта строка выглядела бы как INNER JOIN `banned.json`
. Использование обратных кавычек здесь обязательно т.к. иначе точка (.
) будет интерпретироваться специальным образом.
@banned.ip
— здесь @banned
— это название таблицы. В данном случае название вывелось автоматически из названия биндинга, но вы можете задать его явно через альяс: INNER JOIN {:banned} AS @someName
, тогда обращение к этой таблице будет происходить как @someName.ip
Рекомендую всегда использовать биндинги, как для файлов, так и для обычных данных т.к. это избавляет от множества проблем.
На данный момент поддерживаются только два вида JOIN: INNER JOIN
и LEFT JOIN
. Если не указать тип JOIN в запросе, то будет использоваться INNER JOIN
.
Кау уже было сказано ранее, выражение в ON
должно иметь вид ON @table.primary = foreign
, то есть точное соответствие поля @table.primary
(подключаемая таблица) полю foreign
(главная таблица).
Для удобства, оператор =
(и его псевдоним ==
) не учитывает тип значения и пытается сравнивать значения максимально либерально, поэтому значение выражения 1 = "1"
будет равно true
. В противоположность этому, оператор ===
учитывает при сравнении ещё и тип, поэтому выражение 1 === "1"
уже будет равно false
. Правила сравнений аналогичны принятым в JavaScript, более подробно можно посмотреть здесь [11].
Поведение операторов =
и ===
не определено для массивов и объектов, поэтому на данный момент такого сравнения следует избегать. В будущем сравнение объектов и массивов будет реализовано.
% jl-sql -h
Usage: jl-sql [OPTIONS] SQL
OPTIONS:
-h, --help show this help
-I, --ignore-json-error ignore broken JSON
-v, --verbose display additional information
-B, --sort-in-memory-buffer-length=ROWS save up to ROWS rows for in-memory sort
-S, --sort-external-buffer-size=SIZE use SIZE bytes for `sort` memory buffer
-T, --temporary-directory=DIR use DIR for temporaries, not $TMPDIR or /tmp
-b, --bind=BIND=VALUE+ bind valiable
See full documentation at https://github.com/avz/jl-sql
Более подробно по интересным опциям
-I
: игнорировать ошибки в JSON. По умолчанию jl-sql
завершается с ошибкой (ненулевым кодов возврата) после обнаружения первой же ошибки в JSON. Эта опция позволяет переопределить это поведение и просто игнорировать подобные ошибки. Вывод предупреждений в stderr остаётся-B
: установить лимит на количество строк/объектов, которые будут сортироваться в оперативной памяти без использования внешней сортировки через системную утилиту sort
-S
: установить лимит на количество RAM, используемых утилитой sort
в качестве буфера (смотри описание опции -S
в man sort
)-T
: каталог для размещения временных файлов сортировкиСпасибо за внимание.
Автор: Arepo
Источник [12]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/javascript/234819
Ссылки в тексте:
[1] jl-sql: https://github.com/avz/jl-sql
[2] jl-sql-api: https://github.com/avz/node-jl-sql-api
[3] Equi Join: https://en.wikipedia.org/wiki/Join_(SQL)#Equi-join
[4] соответствующем разделе: #bindingi
[5] RFC2822: https://tools.ietf.org/html/rfc2822#page-14
[6] ISO 8601: https://ru.wikipedia.org/wiki/ISO_8601
[7] документации на GitHub: https://github.com/avz/node-jl-sql-api#dates
[8] соответствующем разделе: #join
[9] описание опций: #opcii-komandnoy-stroki
[10] здесь: https://github.com/avz/node-jl-sql-api#type-casting
[11] здесь: https://developer.mozilla.org/ru/docs/Web/JavaScript/Reference/Operators/%D0%9E%D0%BF%D0%B5%D1%80%D0%B0%D1%82%D0%BE%D1%80%D1%8B_%D1%81%D1%80%D0%B0%D0%B2%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F
[12] Источник: https://habrahabr.ru/post/319722/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best
Нажмите здесь для печати.