jl-sql: работаем с JSON-логами в командной строке с помощью SQL

в 13:10, , рубрики: cli, data mining, javascript, json, json logs, node.js, sql, terminal, Программирование

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

json-pipe-sql

% 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, которая принимает на вход поток JSON-объектов, разделённых символом перевода строки ("n").

Тут стоит отметить, что утилита основана на библиотеке jl-sql-api, на базе которой можно достаточно просто реализовать обработку данных в произвольном формате, не ограничиваясь только JSON.

SQL-движок поддерживает WHERE, GROUP BY, HAVING, ORDER BY и {LEFT|INNER} JOIN. Что касается JOINов, то тут есть ограничение на выражение в ON: реализована поддержка объединений только по точному соответствию двух полей, так называемый Equi Join: 

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), более подробно о них рассказывается в соответствующем разделе.

Работа с датами

% 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 или ISO 8601. Могут использоваться и другие, но результаты в таком случае будут непредсказуемые.

Следует отметить, что для парсинга и манипуляции датами используется системная временная зона. Если вас это не устраивает, то вы можете установить переменную окружения TZ с нужной временной зоной перед запуском jl-sql.

Более подробное описание работы с датами можете прочитать в документации на GitHub.

Объединения (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 можно прочитать в соответствующем разделе.

Производительность и потребляемые ресурсы

Утилита рассчитана на обработку больших логов. В зависимости от типа, запрос может выполняться либо в потоковом режиме, либо в режиме использования временного хранилища. Огромным плюсом потокового режима является возможность выполнять jl-sql в связке, например, с tail -f для фильтрации и переформатирования логов в реальном времени.

  • потоковый режим — режим, в котором запрос обрабатывает входящие данные сразу же, не дожидаясь завершения входящего потока. Вычислительная сложность такой обработки O(n) по CPU и O(1) по памяти. В таком режиме могут исполняться только самые простые запросы, которые не требуют сортировок: SELECT <fields...>, SELECT ... WHERE expression.
  • режим использования временного хранилища — для выполнения запроса требуется сортировка входящего потока. Это означает, что может потребоваться временное хранилище в ФС. К этой категории относятся запросы с GROUP BY, ORDER BY и JOIN. Размеры внутренних буферов задаются опциями -B и -S (смотри описание опций).

Для сортировки больших объёмов jl-sql использует системную утилиту sort, что позволяет использовать более производительную нативную сортировку.

Установка

jl-sql: работаем с JSON-логами в командной строке с помощью SQL - 2 Код написан на 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)

Если не указать явно тип сортировки, то утилита попытается определить тип по правилам, описанным здесь. Если тип определить не удалось, то сортировка будет происходить по строкам.

Значения преобразуются в строку по следующим правилам:

Тип данных Строковое представление
строка исходная строка
число десятичная строка
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.

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, более подробно можно посмотреть здесь.

Сравнение объектов и массивов

Поведение операторов = и === не определено для массивов и объектов, поэтому на данный момент такого сравнения следует избегать. В будущем сравнение объектов и массивов будет реализовано.

Опции командной строки

% 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

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js