jl-sql: SQL-запросы по JSON-логами в командной строке

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


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




    Установка


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

    Ссылки



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

    Similar posts

    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 23

      +1
      Много слов действительно никому не интересно. Поэтому просто: Неплохо!
        0
        Любопытно, спасибо за статью, а есть какой правоверный способ вывода логов nginx в json?
          0

          К сожалению, на данный момент нет. В планах есть реализация user-defined шаблона как раз для парсинга обычных логов, ну а пока только sed и генерация JSON с помощью такой-то метери :)

        +1

        Да, весьма неплохо именно в юзкейсе такой утилиты с простыми декларативными правилами на SQL-подобном языке. Мне в целом нравится.

          +1

          Именно для этого и писалось: замучало под каждый формат своих логов писать огромные пайпы на grep + sed + awk + cut + sort + uniq

          0

          Напомнило q для Python, только там работа ведётся с csv'шками.

            0

            Отличная утилита, к сожалению, не знал о ней. Спасибо за наводку на конкурента — будет с кем соревноваться.

              0

              В ней меня немного разочаровал тот факт, что таблицу нельзя использовать в подзапросе. То есть, такой запрос работает:


              q "select * from data.csv"

              а такой — уже нет:


              q "select * from (select * from data.csv) t"

              Пришлось делать через временный файл. А у вас как с этим дела?

                +1

                А разве нельзя то же самое сделать через обычный pipe?


                q "select * from data.csv" | q "select * from - t"

                Я только из соображений ненужности не стал реализовывать подзапросы. Думаете, стоит?

                  0

                  Можно и через канал, но через временный файл было удобнее. Реальный запрос был сложнее.


                  Я только из соображений ненужности не стал реализовывать подзапросы. Думаете, стоит?

                  Я думаю, они были бы очень полезны, особенно в сочетании с фразой with.

              +2

              Посмотрел более внимательно и обнаружил, что перед выполнением запроса все входные данные читаются в оперативную память, а потом уже идёт их обработка. Это фактически делает невозможным обработку действительно больших логов. Кроме того, в связки с tail -f работать не будет даже для самых простых запросов вида SELECT * WHERE f = "value"

              • UFO just landed and posted this here
                  +1

                  Описываемый в статье jl-sql заточен именно под огромные логи, которые заведомо либо не влезают в память, либо вообще потенциально-бесконечны (реалтайм-поток). Поэтому jl-sql использует временное хранилище только там, где без него не обойтись, например, при сортировках.


                  Если сортировка не нужна, то результат на stdout будет выводиться сразу после получения отдельного объекта в stdin, не дожидаясь окончания всего потока объектов. Так работают запросы вида:


                  SELECT smth WHERE condition

                  Простые агрегационные запросы без GROUP BY тоже не будут требовать временного хранилища


                  SELECT SUM(`field`) WHERE condition

                  А вот JSON Path имеет смысл сделать в следующей мажорной версии, спасибо

              0
              Классная вещь! Для PHP такого нет, случайно?

              Еще немного непонятно — как делать выборку по частичному совпадению? Тот же «like %...%», например?
                0

                Я для PHP подобного не встречал.


                Еще немного непонятно — как делать выборку по частичному совпадению? Тот же «like %...%», например?

                LIKE и регулярные выражения появятся в ближайшее время (пара дней)

                  +1
                  LIKE и регулярные выражения появятся в ближайшее время (пара дней)
                  Отлично, спасибо! Уже нашел начал использовать, а с like будет вообще идеально.

                  Еще заметил, что не работает конструкция в WHERE:

                  ts > (now() - INTERVAL 24 HOUR)
                  

                  где ts — дата-время в формате «2017-01-19 14:50:45».
                  Работает как-то так:

                  (ts + INTERVAL 1 SECOND) > (now() - INTERVAL 24 HOUR)
                  

                  т.е. получается, что время приводится к единому формату и только потом сравнивается. При этом в SELECT подобные манипуляции не нужны.
                    +1

                    Хорошее замечание. К сожалению, о таком очевидном юзкейсе я не подумал заранее, но ничего, это исправимо.


                    Если ещё что-то заметите, то обязательно пишите — будем допиливать ;)

                      +1
                      Если ещё что-то заметите, то обязательно пишите — будем допиливать ;)
                      Хорошо :-)
                      0

                      Зарелизил новую версию jl-sql v1.2.0 (jl-sql-api v2.4.0) с поддержкой LIKE и ILIKE (регистро-независимый LIKE). Ещё сделал поддержку автоматического приведения типов для дат, так что теперь


                      ts > (now() - INTERVAL 24 HOUR)

                      должно работать.


                      Поддержки регулярных выражений пока нет.

                        +1

                        Вот и регулярки подоспели


                        jl-sql-api v2.5.0 (2017-01-23)


                        • REGEXP support: "string" REGEXP "/pattern/im"
                        • BETWEEN support
                        • IS support:
                          • IS [NOT] NULL
                          • IS [NOT] BOOL and alias IS [NOT] BOOLEAN
                          • IS [NOT] NUMBER
                          • IS [NOT] ARRAY
                          • IS [NOT] OBJECT
                          • IS [NOT] STRING
                    0

                    Автору на заметку — есть очень интересная консольная утилита для json — jq, советую тоже обратить на неё внимание

                      0

                      Утилита хорошая, я её тоже использую, но jq и jl-sql совсем для разного.

                    Only users with full accounts can post comments. Log in, please.