Вступление никому не интересно, поэтому начну сразу с примеров использования

% 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

---

Примеры

Определение уникальных ip-адресов

% 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: каталог для размещения временных файлов сортировки

Ссылки

• https://github.com/avz/jl-sql ^[1] — основной репозиторий утилиты
• https://github.com/avz/node-jl-sql-api ^[2] — репозиторий SQL-движка

Спасибо за внимание.

Автор: Arepo

Источник ^[12]