jl-sql: працюємо з JSON-логами в командному рядку за допомогою SQL

Вступ нікому не цікаво, тому почну відразу з прикладів використання
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 AS SECOND 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
, що дозволяє використовувати більш продуктивну нативну сортування.


Установка
Код написаний на 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
    : каталог для розміщення тимчасових файлів сортування
Посилання
Дякую за увагу.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.