Sql

SQL-инъекции считаются одной из наиболее опасных уязвимостей в онлайн-приложениях. Именно поэтому о них должен знать буквально каждый работающий с базами данных специалист, а статей об SQL-инъекциях как таковых очень и очень много.

К старту курса "Этичный хакер" мы решили поделиться именно этой статьёй, потому что она не только предлагает памятку по инъекциям, но и знакомит с платформой snyk.io сканером на уязвимости, который для проектов с открытым исходным кодом можно использовать совершенно бесплатно, а значит, он будет полезен для пет-проектов многих разработчиков и читателей Хабра. По этой причине публикация попала и в хаб Программирование.

Существуют различные типы атак с использованием SQL-инъекций, но, как правило, все они имеют схожую причину. Происходит конкатенация (добавление) ненадёжных данных, введённых пользователем, к строке запроса. В результате введённые пользователем данные могут изменить само первоначальное назначение данного запроса.

Например, SQL-инъекции могут быть такими:

• добавление к фразе where булева оператора, который делает это условие всегда выполняющимся ' OR 1=1;

• удаление части запроса путём ввода строчных комментариев --;

• завершение исходного запроса и запуск нового запроса '; DROP TABLE USERS;

• соединение данных из нескольких таблиц с помощью оператора UNION.

В этой памятке я подробно рассмотрю 8 оптимальных методов, которые можно применять в любом приложении в целях предотвращения атак через SQL-инъекции. Итак, давайте начнём и сделаем ваше приложение действительно защищённым от SQL-инъекций.

Загрузить памятку

1. Не полагайтесь на проверку вводимых данных на стороне клиента.

2. Ограничивайте полномочия пользователя базы данных.

3. Используйте предварительно подготовленные опраторы и параметризацию запросов.

4. Сканируйте код, чтобы обнаружить уязвимость к SQL-инъекциям

5. Используйте слой ORM.

6. Не полагайтесь на чёрные списки.

7. Выполняйте проверку вводимых пользователем данных.

8. С осторожностью используйте хранимые процедуры.

1. Не полагайтесь на проверку вводимых данных на стороне клиента

Проверка вводимых данных на стороне клиента может быть очень полезна. Благодаря ей можно предотвратить отправку в систему недопустимых данных. Однако, к сожалению, такая проверка хорошо работает только для пользователей, не имеющих злого умысла, которые намерены использовать систему строго по её прямому назначению. Выдача пользователю мгновенной подсказки о недопустимости конкретного значения это очень полезная возможность, делающая систему более удобной для пользователей. Поэтому использование проверок вводимых данных на стороне клиента рекомендуется для повышения качества работы клиентов.

Но применительно к SQL-инъекциям это не тот метод защиты, на который действительно можно положиться. Проверку вводимых данных на стороне клиента легко можно отключить, изменив некоторый код javascript, загруженный в ваш браузер. Кроме того, в архитектуре клиент-сервер очень легко выполнить простейший HTTP-вызов серверного приложения, используя в нём параметр, вызывающий SQL-инъекцию. Также это можно сделать, используя различные инструментальные средства, такие как postman, или старые добрые команды curl.

Следует выполнять проверку на серверной стороне желательно как можно ближе к источнику данных. В рассматриваемом случае там, где вы создаёте SQL-запрос. Все данные, передаваемые со стороны клиента, должны рассматриваться как потенциально опасные. Ввиду вышеизложенного возлагать надежды на проверку вводимых данных на стороне клиента в целях защиты от SQL-инъекций плохая идея.

2. Ограничивайте полномочия пользователя базы данных

Как уже было упомянуто, существует несколько типов атак с использованием SQL-инъекций. Некоторые из них более опасны, чем другие. Представьте, к примеру, что мой SQL-запрос имеет вид "SELECT * FROM USER WHERE USERID = '" + userid +"'". Инъекция " foo' OR '1'='1 " предоставит доступ ко всем пользователям, и это само по себе очень опасно. Однако инъекция " '; UPDATE message SET password = 'EVIL приведет к ещё большим проблемам, поскольку злоумышленник в этом случае изменит все записи.

Создавая пользователя в базе данных для вашего приложения, вы должны подумать о его полномочиях. Действительно ли приложению необходима возможность считывать, записывать и обновлять все базы данных? Должны ли быть у него полномочия для выполнения команд truncate и drop в отношении таблиц? Если вы сами ограничите полномочия своего приложения при работе с базой данных, то таким образом можно снизить и потенциальный вред от SQL-инъекций. Наверное, правильным будет создание для приложения нескольких пользователей базы данных и назначение им определённых ролей. Проблемы с безопасностью, как правило, вызывают эффект цепной реакции, поэтому необходимо контролировать все звенья вашей цепи, чтобы предотвратить тяжёлые последствия.

3. Используйте предварительно подготовленные операторы и параметризацию запросов

Во многих языках программирования специально предусмотрены функциональные возможности, помогающие предотвратить SQL-инъекции. При создании SQL-запросов вы можете использовать заранее подготовленные операторы SQL для компоновки своих запросов. Применение подготовленного оператора позволяет выполнить параметризацию запроса. Параметризация запросов это особая техника, позволяющая формировать выполняемые операторы SQL динамически. Вы создаёте базовый вопрос с определёнными плейсхолдерами (метками-заполнителями), а затем безопасно прикрепляете переданные пользователем параметры к этим плейсхолдерам.

Используя действительно подготовленный оператор и параметризованные запросы, вы заставляете саму базу данных заботиться об экранировании. Прежде всего на основе строки запроса с плейсхолдерами в ней создаётся план выполнения этого запроса. На втором шаге в базу данных передаются ненадёжные параметры. Но, поскольку план выполнения уже создан, эти параметры ни на что повлиять уже не могут. Это также предотвращает возможность инъекций.

Пример на Java:

String query = "SELECT * FROM USERS WHERE username LIKE ?";PreparedStatement statement = connection.prepareStatement(query);statement.setString(1, parameter);ResultSet result = statement.executeQuery();

Пример на Python с коннектором MySQL:

cursor = conn.cursor(prepared=True)params = ("foo",)cursor.execute("SELECT * FROM USERS WHERE username = %s", params)

Пример на JavaScript с mysql2:

connection.query("SELECT * FROM USERS WHERE username = ?",[     req.body.username    ],function(error, results){}); //emulates a prepared statement//ORconnection.execute("SELECT * FROM USERS WHERE username = ?",[     req.body.username    ],function(error, results){});//prepared statement

Тем не менее существует несколько способов сделать это в JavaScript при использовании, например, базы данных MySQL. Учитывайте, что при использовании функции .query() подготовленный оператор не выполняется. В этом случае подстановка параметров осуществляется на стороне клиента, то есть происходит эмуляция подготовленного оператора. Чтобы сделать настоящий подготовленный запрос к базе данных, следует использовать функцию .execute().

4. Сканируйте код, чтобы обнаружить уязвимость к SQL-инъекциям

Писать собственный код совсем не сложно. Но при этом нередко случаются ошибки. Для проверки кода у вас могут быть предусмотрены такие процессы, как ревью кода и парное программирование. Но является ли тот, кто проверяет ваш код или программирует в паре с вами, также и специалистом в вопросах безопасности? Сможет ли он заметить в коде ошибку, опасную с точки зрения SQL-инъекций? В любом случае будет полезно автоматически проверять собственный код на предмет потенциальных угроз безопасности, таких как SQL-инъекции.

Используя такой инструмент SAST (технология статического тестирование безопасности приложений), как Snyk Code, вы можете автоматически проверять свой код и обнаруживать в нём угрозы безопасности, включая SQL-инъекции. Этот процесс можно легко автоматизировать в SDLC, например, подключив к Snyk ваш Git-репозиторий.

5. Используйте слой ORM

Также необходимо рассмотреть возможность использования уровня объектно-реляционного отображения (ORM). Слой ORM преобразует данные из базы данных в объекты, а также выполняет обратное преобразование. Применение библиотеки ORM уменьшает количество явных SQL-запросов, а значит, делает приложение менее уязвимым к SQL-инъекциям.

Одним из ярких примеров использования библиотек ORM является применение решений Hibernate для Java и Entity Framework для C#. Эти языки строго типизированы, а значит, в них есть возможность устанавливать соответствие между объектами и таблицами в базах данных. Таким образом, у вас есть возможность полностью отказаться от самостоятельного формирования каких-либо SQL-запросов.

Тем не менее проблема всё равно возникнет, если потребуется написать свои собственные запросы. В библиотеке Hibernate для Java предусмотрен даже собственный язык написания запросов HQL (Hibernate Query Language). При создании запросов на HQL опять же нужно помнить о возможных инъекциях и использовать функцию createQuery(), которая работает аналогично подготовленным операторам.

Для языка JavaScript также существуют хорошо известные библиотеки ORM, такие как sequelize. С помощью sequelize вы сможете определить, как значения будут отображаться в определённые типы в базе данных. Но давайте смотреть правде в глаза, в конечном счёте любой библиотеке ORM требуется преобразование её внутренних логических команд в операторы SQL. Мы верим, что в библиотеках экранирование параметров реализовано правильно.

Чтобы быть уверенным, что в вашей библиотеке ORM нет проблем с SQL-инъекциями, необходимо выполнить её сканирование с целью выявления известных уязвимостей. Работа с некорректной, устаревшей версией библиотек sequelize или hibernate может стать причиной больших проблем. Используя Snyk Open Source для проверки своего проекта, вы сможете защититься от скрытых SQL-инъекций в применяемых библиотеках, а также от многих других потенциальных угроз.

Использование Snyk совершенно бесплатно

Вы можете сразу приступать к работе со Snyk Open Source. Всё, что для этого нужно, пройти регистрацию на получение бесплатного аккаунта.

6. Не полагайтесь на чёрные списки

Уверен, эта ситуация хорошо знакома большинству читателей, но я всё равно повторюсь. Пожалуйста, никогда не применяйте чёрные списки для используемых вами параметров. Подход с применением таких списков задаёт набор правил, определяющих уязвимые входные данные. В случае соответствия вводимых данных этим правилам запрос блокируется. Но если правила проверки окажутся недостаточно жёсткими, то введённые злоумышленником данные всё равно сработают. А если они, наоборот, будут слишком ограничивающими, то будет заблокирован ввод допустимых данных.

Например, мы можем блокировать все запросы, где содержится слово OR. Возможно, в этом даже есть какой-то смысл, но на практике оказывается, что Or это очень часто встречающееся в Израиле имя. И это означает, что множество моих коллег будут блокироваться при попытке ввода своих имён. То же самое справедливо для одиночных кавычек '. Этот символ содержится в огромном количестве имён. Подумайте о пользователях с фамилиями ONeill и ODonnell. А ведь есть ещё и имена, такие как Donta.

7. Выполняйте проверку вводимых данных

Да, вы всегда, обязательно должны выполнять проверку вводимых данных! Хотя предварительно подготовленные операторы с параметризацией запросов и являются лучшей защитой против SQL-инъекций, всегда создавайте несколько уровней защиты. Подобно выдаче ограниченных полномочий пользователю базы данных, проверка ввода это великолепный способ снизить потенциальные риски для вашего приложения в целом.

Также возможны такие ситуации, когда использование предварительно подготовленных операторов оказывается невозможным. Некоторые языки программирования могут не поддерживать этот механизм, а некоторые устаревшие системы баз данных могут не поддерживать ввод данных пользователями в качестве параметров. В таких случаях проверка вводимых данных становится приемлемой альтернативой.

Обязательно убедитесь, что проверка вводимых данных выполняется на основе белых списков, а не чёрных, как уже описывалось выше. Создайте правило, описывающее все разрешённые шаблоны, например с помощью регулярного выражения. Можно также использовать для этого хорошо поддерживаемую библиотеку. Используйте это правило в сочетании с предварительно подготовленными операторами с параметризацией запросов и вы обеспечите надежную защиту.

8. С осторожностью используйте хранимые процедуры

Многие верят, что работа с хранимыми процедурами это хороший способ защититься от SQL-инъекций. Но это не всегда так. Подобно SQL-запросам, созданным в вашем приложении, хранимые процедуры также подвержены инъекциям со стороны злоумышленников.

И, так же как и в случае с SQL-запросами, вам следует использовать в хранимой процедуре параметризованные запросы вместо добавления параметров. SQL-инъекции в хранимой процедуре достаточно легко предотвратить. Но не пытайтесь сделать это в MySQL:

DELIMITER //CREATE PROCEDURE `FindUsers`(    IN Username VARCHAR(50))BEGIN    SET @Statement = CONCAT('SELECT * FROM User WHERE username = ', Username, ' );    PREPARE stm FROM @Statement;    EXECUTE stm;END //DELIMITER ;

Вместо этого лучше используйте в своих хранимых процедурах параметризованные запросы:

DELIMITER //CREATE PROCEDURE `First`(    IN Username VARCHAR(50))BEGIN    PREPARE stm FROM 'SELECT * FROM User WHERE username = ?';    EXECUTE stm USING Username;END //DELIMITER ;

Как показано выше, к хранимым процедурам применяются те же правила, что и к коду приложения. Способы применения хранимых процедур различаются в зависимости от используемой базы данных. Убедитесь, что вы хорошо представляете себе, как хранимые процедуры реализуются в используемой вами базе, и также будьте бдительны в отношении возможных инъекций. Хоть я и уверен, что лучше всего убрать всю логику внутрь вашего приложения, но хранимые процедуры также могут быть разумным решением, если используемый вами язык программирования не поддерживает работу с предварительно подготовленными операторами.

Узнайте, как прокачаться и в других специальностях или освоить их с нуля:

• Профессия Data Scientist

• Профессия Data Analyst

• Курс по Data Engineering

Вступление

Многим JavaScript разработчикам доводилось сортировать данные на стороне клиента. К сожалению, существующие библиотеки имеют мелкие недостатки. Но эти недостатки складываются и ограничивают то как программисты думают о сортировке. Чтобы преодолеть эти ограничения, давайте рассмотрим сортировку в разных языках. Вооруженные этими знаниями, мы сможем выбрать наиболее удобный и строгий интерфейс.

С чего все началось

В один прекрасный летний день, на проекте с AngularJS мне поручили добавить функцию сортировки в таблицу. При этом критериев для сортировки может быть сразу несколько, и направление по каждому критерию может быть независимым.

Список требований:

• использовать несколько выражений как ключ для сортировки

• возможность указать направление сортировки независимо по каждому из ключей

• возможность сортировать строки без учета регистра, и с учетом локали

• устойчивость сортировки

Что нам предлагает AngularJS для сортировки? документация по filter:orderBy

{{ orderBy_expression | orderBy : expression : reverse : comparator }}$filter('orderBy')(collection, expression, reverse, comparator)Example:<tr ng-repeat="friend in friends | orderBy:'-age'">...</tr>

У меня возникло несколько замечаний по поводу этого фильтра. Для начала, знак - в этом примере не может быть математической операцией, потому что есть значения для которых это бессмысленная операция, например строки. В документации говорится что это префикс, который указывает направление сортировки. Если продолжить разбор, что это вообще за выражение? Это, вроде как, похоже на JS, но в то же время не очень. Это синтаксис выражений для AngularJS, который так же опасен как eval, но при этом имеет свои ограничения. То что этот синтаксис исключителен для AngularJS значит что эти знания невозможно перенести на другие проекты на JS. Кроме того, нельзя использовать TypeScript для проверки этих выражений. expression кроме того может принимать не только строку, но и функцию, которая возвращает ключ для сортировки. Но если указывать функцию, то направление сортировки указать нельзя и теряется гибкость. Так же можно указать несколько критериев сортировки, если задать массив из строк или функций.

Идем дальше, направление сортировки так же можно поменять вторым параметром reverse. Но возникает проблема! Если указывать несколько критериев сортировки с помощью функций, то нельзя указать направление индивидуально для каждого критерия. Если же указывать направиление сортировки в каждом критерии индивидуально, то этот параметр лишний.

Теперь посмотрим на третий параметр comparator, который позволяет задать особый способ сравнения элементов. Но при этом если было указано несколько критериев сортировки, то один и тот же comparator будет использоваться для каждого критерия. В результате localeSensitiveComparator будет использоваться для сортировки чисел.

И спрашивается, какая мне польза использовать интерфейс который сам с собой не дружит в трех местах, несовместим с TypeScript и его можно использовать исключительно в богом позабытом фреймворке? В мире JavaScript таких примеров, к сожалению, полным полно.

lodash

Посмотрим на библиотеку lodash, В ней есть функция _.sortBy, которая позволяет сортировать массив по ключу.

var users = [    { 'user': 'fred',   'age': 48 },    { 'user': 'barney', 'age': 36 },    { 'user': 'fred',   'age': 40 },    { 'user': 'barney', 'age': 34 }]; _.sortBy(users, [(o) => o.user]);// => objects for [['barney', 36], ['barney', 34], ['fred', 48], ['fred', 40]] _.sortBy(users, ['user', 'age']);// => objects for [['barney', 34], ['barney', 36], ['fred', 40], ['fred', 48]]

Хм, эта функция не позволяет указывать направление сортировки, почему так? Из-за этого я хотел сразу отбросить lodash, но потом увидел _.orderBy.

This method is like _.sortBy except that it allows specifying the sort orders of the iteratees to sort by.

Добавление опционального параметра не должно ломать обратную совместимость, поэтому мне совсем непонятно почему это сделали отдельной функцией. Посмотрим на его использование:

// Sort by `user` in ascending order and by `age` in descending order._.orderBy(users, ['user', 'age'], ['asc', 'desc']);// => objects for [['barney', 36], ['barney', 34], ['fred', 48], ['fred', 40]]

И сразу же возникает замечание поскольку направление сортировки указывается отдельно от самого критерия, не очевидно в какую сторону он будет сортироваться. Это приведет к тому, что удаление или добавление критериев сортировки может рассинхронизовать эти два массива, и может привести к неожиданным последствиям.

В целом, _.orderBy это терпимый метод сортировки.

Array#sort

Если же мы хотим использовать только стандартную библиотеку JavaScript, у Array нам доступен метод sort. В этом методе можно указать функцию для сравнения элементов. Сам по себе этот интерфейс для сортировки позволяет указать любой возможный критерий для сортировки. Правда он не так удобен для использования как сортировка по ключу, и есть довольно много подводных камней. Самый большой подводный камень, с моей точки зрения, к этой функции есть очень строгое требование линейно упорядоченного множества. Это требование очень просто нарушить. В прошлом, несоблюдение этих требований в некоторых браузерах приводило к бесконечным циклам и крахам.

items.sort(function(a, b) {    if (b.salary < a.salary) {      return -1;    }    if (b.salary > a.salary) {      return 1;    }    if (a.id < b.id) {      return -1;    }    if (a.id > b.id) {      return 1;    }    return 0;});// Для сравнения, эквивалентный код с использованием `lodash`// намного короче и его проще читать:lodash.orderBy(items, ['salary', 'id'], ['desc', 'asc']);

Когда критериев для сортировки будет больше, то разница в удобстве будет еще больше, что приведет повторению кода. Отсутствие удобства и громоздкость обычно приводит к тому, что в код закрадываются трудно уловимые ошибки.

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

SQL / SEQUEL

Стоит начать с того что данные лучше всего нужно сортировать на стороне сервера, и отображать на клиенте как есть, без перестановок. Если можно убрать сортировку на клиенте, поздравляю, проблема решена! Но этот пост не о таких ситуациях, поэтому попробуем позаимствовать опыт SQL.

Как ни как, сортировка это часть языка SQL с 1976 года, и с его помощью люди давно сортируют данные налево и направо. Кому, как не пользователям реляционных баз данных, приходится больше всего сортировать данные по сложным критериям?

SELECT EMPNO,NAME,SALFROM EMPWHERE DNO 50ORDER BY EMPNO

SQL позволяет указывать несколько критериев сортировки, а так же независимо указывать направление сортировки по разным критериям:

SELECT EMPNO,NAME,SALFROM EMPORDER BY SAL DESC, EMPNO ASC

Вот к такому нужно стремиться. К сожалению, SQL это отдельный язык и без специального синтаксиса нет возможности внедрить это напрямую.

Haskell и Rust

Haskell и Rust предоставляют довольно элегантные методы для сортировки по ключу:

Haskell sortOn:

import Data.Ord (Down)import Data.Sort (sortOn)sortOn (\employee -> (Down (salary employee), employee_id employee)) employees

Rust slice::sort_by_key:

use std::cmp::{Reverse};slice.sort_by_key(|employee| (Reverse(employee.salary), employee.id))

Здесь сортировка по нескольким критериям достигается за счет лексикографического порядка кортежей, а сортировка по убыванию за счет оберточных типов (newtype) Down и Reverse, которые инвертирует порядок сортировки своего содержания. Это очень простой для использовани интерфейс, и он полностью совместим со всеми требованиями.

Python

В Python у списков есть встроенный метод list.sort и гобальный метод sorted, в котором можно указать критерий сортировки через именованный аргумент key.

Ранее эти методы так же принимали аргумент cmp, но его убрали потому что он не нужен.

sorted(employees, key=lambda employee: (employee.salary, employee.id))

Python, как Haskell и Rust, здесь использует кортеж для сортировки по нескольким критериям, но нельзя указывать направление сортировки отдельно для каждого критерия. К счастью, это легко исправить, создав клас-обертку для обратной сортировки. Это упростит метод сортировки, убрав один аргумент, и одновременно расширит возможности сортировки.

from ord_reverse import Reversesorted(employees, key=lambda employee: (Reverse(employee.salary), employee.id))

Java и C#

В Java метод Arrays.sort принимает Comparator (который почти состоит одной функции сравнения двух элементов). Но Comparator так же позволяет строить компараторы, добавляя новые критерии сравнения, используя метод thenComparing. Можно обратить направление сортировки используя метод reversed.

Comparator<Employee> comparator =  Comparator.comparing(Employee.getSalary).reversed()    .thenComparing(Employee.getId);Arrays.sort(array, comparator);

Здесь есть небольшой недостаток нет простого способа указать обратное направление сортировки для отдельного критерия. Давайте попробуем написать компаратор вида ORDER BY SALARY ASC, ID DESC:

// Вариант 1, создавать два компаратора, и складывать ихComparator<Employee> comparator =  Comparator.comparing(Employee.getSalary)    .thenComparing(Comparator.comparing(Employee.getId).reversed());// Вариант 2, инвертирует компаратор дважды. Таким образом первый компаратор// будет использовать прямое направиление сортировки.Comparator<Employee> comparator =  Comparator.comparing(Employee.getSalary).reversed()    .thenComparing(Employee.getId).reversed();

Если не учитывать LINQ Query, который есть прямым наследником SQL, в C# для сортировки используется Enumerable.OrderBy и Enumerable.OrderByDescending, а так же Enumerable.ThenBy и Enumerable.ThenByDescending для добавления новых критериев сортировки.

IEnumerable<Employee> query =    employees    .OrderByDescending(employee => employee.Salary)    .ThenBy(employee => employee.Id);

По сравнению с Java здесь легче указать обратною сортировку для индивидуальных ключей. Но есть и недостатки не очевидно когда именно будет происходить сортировка, и слишком множатся методы: IEnumerable 4 метода, по сравнению с 1 в Haskell/Rust/Python. Количество методов в C# можно было бы свести к двум, используя простой класс для инверсии сравнения.

В целом, как Java, так и C# удовлетворяют требования сортировки. К сожалению, оба языка используюь более громоздкий подход с использованием ООП.

C и C++

C qsort:

#include <stdlib.h>int cmp_employee(const void *p1, const void *p2){      const employee *a = (employee*)p1;      const employee *b = (employee*)p2;      if (b->salary < a->salary) {        return -1;      }      if (b->salary > a->salary) {        return 1;      }      if (a->id < b->id) {        return -1;      }      if (a->id > b->id) {        return 1;      }    return 0;  }  /* ... */  qsort(employees, count, sizeof(employee), cmp_employee);

C++ std::sort:

#include <algorithm>/* ... */std::sort(employees.begin(), employees.end(), [](const employee &a, const employee &b) {  return (b->salary < a->salary) || (a->id < b->id);});

Как в C, так и в C++ сортировать можно только предоставляя функцию сравнения элементов. Только в C сравнение элементов требует возвращать не -1, 0, 1, а в C++ проверять что первый элемент меньше второго. В каком-то смысле, так теряется часть информации о сравнении. Скорее всего, это приводит к тому что функция сравнения в C++ вызывается больше чем необходимо.

В C и в C++ нет встроенных способов сортировки по ключу и отсутствуют воспомагательные классы для сборки компараторов из нескольких частей. Array#sort, который мы рассматривали ранее имеет те же недостатки как и эти две функции.

Выбираем то что лучше подходит

Из всех перечисленных интерфейсов, наиболее компактная и выразительная сортировка в Haskell и Rust. Можем ли мы перенести ее в JavaScript?

Кортежей в JS нет, но для этих целей можно использовать массивы, так как JS позволяет хранить разные типы в массивах. Оберточных типов нет, но мы можем выбрать нужные типы и определить их форму в функции сравнения. Как это будет выглядеть?

sortBy(array, (employee) => [{ reverse: employee.salary }, employee.id]);

Определяем линейный порядок в JavaScript

Для того чтобы использовать сортировку по ключу, нужно для начала определиться как сравнивать ключи. Так как в JavaScript нет приемлимого встроенного порядка, нет интерфейсов, Trait-ов и typeclass-ов, то необходимо выбрать достаточное подмножество сравнений для которых будет определен полный порядок, или сравнение будет неуспешным.

Определяем с нуля:

1. null меньше всех значений. Это альтернативно использованию типа Maybe или Option.

2. Если типы разные, сравнение бросает ошибку.

3. Особое значение NaN меньше всех других чисел.

4. Остальные числа, строки, були и BigInt сравниваются между собой как определено в JavaScript.

5. Массивы сравниваются используя лексикографический порядок, рекурсивно сравнивая элементы.

6. Если оба значения имеют форму { reverse: xxx }, то значение xxx будет рекурсивно сравниваться в обратном порядке. Это равносильно использованию Down / Reverse

7. Если оба значения имеют форму { localeCompare: sss, collator: ccc }, строки sss сравниваются используя коллатор ccc. Коллаторы в обоих значений должны быть равны.

8. Все остальное бросает ошибку.

Из-за строгости определения, здесь должно быть достаточно ограничений чтобы сравнение удовлетворяло линейному порядку или было неуспешным. Этого подмножества должно хватить для того чтобы решать все возможные задачи сортировки.

Как только мы выбрали интерфейс для сортировки и определили линейный порядок, осталось дело за малым воплотить это в виде библиотеки: better-cmp

Бонус: почему я не использовал библиотеку X?

• orderBy: не смотря на "Inspired by Angular's orderBy filter", эта библиотека довольно хороша. Но я предпочел пойти дальше.

• thenby: довольно хорошая библиотека, копирует интерфейс Java для комбинации компараторов, но я решил копировать другой язык из-за эргономики.

• multisort: _

  if (/[^\(\r\n]*\([^\(\r\n]*\)$/.test(nextKey)) {    var indexOfOpenParenthesis = nextKey.indexOf("(");    var args = JSON.parse("[" + nextKey.slice(indexOfOpenParenthesis+1, -1) + "]");    nextKey = nextKey.slice(0, indexOfOpenParenthesis);}
  

Заключение

• Надеюсь что этот обзор интерфейсов сортировок в разных языках был вам полезен, и что в будущем вам легче будет разобраться как сортировать данные по многим критериям.

• Довольно странно что для такой распространенной операции как сортировка разные языки используют разные интерфейсы.

• Ещё более странно странно что в "текущем году" в JavaScript нет широко известной и адекватной сортировки по ключу.

• Лучшее решение для JavaScript что смог сделать теперь воплощено в виде библиотеки better-cmp, доступной на npm.

Пролог

Добрый день, уважаемые читатели. Совсем недавно мне пришлось осваивать новую для себя область CI/CD, настраивая с нуля доставку скриптов миграции базы данных в одном из проектов. При этом было тяжело преодолеть самый первый этап "глаза боятся", когда задача вроде бы ясна, а с чего начать, не знаешь. Однако вопрос оказался на поверку значительно проще, чем казалось изначально, давая при этом неоспоримые преимущества ценой нескольких часов работы и не требуя никаких дополнительных средств, кроме обозначенных в заголовке.

Полученным опытом я решил поделиться в данной статье, чтобы помочь тем, кто хочет автоматизировать свои рабочие процессы, но не знает, с чего начать.

Оглавление

• Введение

• Основы

• Liquibase

• Настройка сервера деплоя

• Настройка GitLab Runner

• Настройка CI/CD

• Заключение

Введение

Об авторе

Меня зовут Копытов Дмитрий, я являюсь главным разработчиком и архитектором проектов. Специализируюсь на C#/.NET, Vue.js и Postgres.

К теме CI/CD пришел после получения "в наследство" проекта с уже существующим CI/CD, который пришлось перерабатывать.

О статье

В первую очередь статья предназначена для разработчиков, которые хотят внедрить в свои проекты CI/CD, но не знают, с чего начать, а также для тех, кому в наследство достались проекты с уже внедренным CI/CD, суть работы которого им не понятна.

Целью статьи является подробное описание технических аспектов настройки связки GitLab + Liquibase на конкретном примере, а также основы теории, чтобы у читателя отложилась не только сама инструкция, но и понимание процесса. Многие моменты и возможности GitLab CI/CD & Liquibase будут сознательно опущены во избежание перегрузки информацией.

Уже после написания большей части статьи, осознал насколько она оказалась большая, но разбить ее на несколько частей не вижу возможности без потери целостности. Приготовьтесь, будет многобукофф.

Перед самой публикацией во время выбора хабов обнаружил схожую статью по автоматизации деплоя Liquibase. Это досадная случайность без злого умысла, не более.

Кулинарный рецепт

Для приготовления блюда нужны следующие ингредиенты:

• GitLab Community Edition хранилище Git

• GitLab Runner рабочая лошадка CI/CD

• Liquibase, на момент написания статьи версия 4.3.4

• Драйвер Liquibase для целевой БД

• Выделенный сервер с установленными Liquibase и GitLab Runner и доступом к целевой БД для наката скриптов и к GitLab для получения исходников. Необязательно.

• 3 чашки чая или кофе. Обязательно.

Основы

Используемые технологии

Все технологии, используемые в статье, являются открытыми, вы можете использовать их совершенно бесплатно без необходимости закупки Premium-фич.

Примеры из статьи (скрипты и конфиги) опубликованы на гитхабе: https://github.com/Doomer3D/Gliquibase.

GitLab CI/CD

CI/CD у всех на слуху, поэтому не буду вдаваться в детали, отмечу только, что в контексте статьи будет рассмотрен процесс автоматического (при мерже/коммите) или ручного (по команде) наката скриптов миграции базы данных на целевую среду, будь то дев или прод. В качестве конвейера будут использоваться GitLab pipelines, а непосредственным исполнителем будет GitLab Runner, поэтому если вы не используете GitLab в качестве хранилища, статья вам будет по большому счета бесполезна.

Все примеры будут рассмотрены в контексте используемого в нашей компании GitLab Community Edition13.x.

Liquibase

Liquibase (ликви) платформа c открытым кодом, которая позволяет управлять миграциями вашей базы. Если кратко, то Liquibase позволяет описывать скрипты наката и отката базы в виде файлов чейнжсетов (changeset). Сами скрипты при этом могут быть как обычными SQL-командами, так и БД-независимыми описаниями изменений, которые будут преобразованы в скрипт конкретно для вашей базы. Список поддерживаемых БД можно найти здесь: https://www.liquibase.org/get-started/databases.

Liquibase написан на Java, поэтому может быть запущен на любой машине с JVM.

В статье будет рассмотрен Oracle 19 в качестве целевой базы, потому что именно для него настраивал деплой на рабочем проекте. Но, как писал выше, тип базы не имеет принципиального значения.

Схема работы

В данном разделе я сначала хотел нарисовать схему, но быстро понял, что давно не рисовал диаграммы процессов, поэтому постараюсь объяснить на пальцах.

Когда разработчик делает коммит или мерж реквест, запускается конвейер CI/CD, называемый пайплайном (pipeline), который включает в себя этапы (stage), состоящие из джобов (job).

В нашем примере будет один этап деплой (deploy), т.е. применение скриптов наката. При этом будет столько джобов, сколько стендов мы имеем. Предположим, что у нас всего два стенда дев и прод. Соответственно, будет два джоба деплой на дев (deploy-dev) и деплой на прод (deploy-prod), которые будут отличаться целевой БД. Кроме того, деплой на прод мы хотим запускать только вручную, по команде, а не в момент мержа или коммита.

Джобы обрабатываются раннером (GitLab Runner) специальной программой, которая скачивает себе исходники проекта и выполняет скрипты на сервере деплоя, при этом раннеру передаются различные параметры в виде переменных окружения. Это может быть пользовательская информация, хранимая в настройках проекта, такая как адрес и логин/пароль сервера БД, а также информация о самом процессе CI/CD, например, название ветки, автор, текст коммита и т.п.

Важно! Раннер сам обращается к GitLab по http, а не наоборот, поэтому машина с раннером должна иметь доступ к GitLab, в то время как разработчик и сервер GitLab могут ничего не знать о машине, где находится раннер.

Если оба стенда дев и прод, доступны с одной машины, достаточно будет одного раннера, однако обычно прод находится в другой сети, тогда в ней должен быть отдельный раннер с доступом к БД прода.

В нашем случае раннер будет вызывать Liquibase через командную строку. В параметрах к Liquibase будет передана информация о целевой базе и используемом драйвере, а сами скрипты наката Liquibase сформирует из описаний миграций из файлов вашего проекта.

В случае успеха, если скрипты отработали без ошибок, пайплайн считается успешным, а мерж реквест вливается в ветку. Картина будет примерно такой:

В противном случае пайплайн будет в состоянии failed:

Мы сможем провалиться в джоб и посмотреть логи, чтобы найти ошибку или перезапустить пайплайн, если проблема была в сетевом доступе. В моей практике была ситуация, что поднятый VPN на машине деплоя в какой-то момент стал блокировать доступ к GitLab по доменному имени, при этом пайплайн оказывался в состоянии pending, т.е. ожидал, что раннер подхватит джоб, но этого не происходило. Правка hosts и перезапуск пайплайна разрешили проблему.

Итак, подытожим, как выглядит схема работы:

1. Разработчик делает мерж реквест или коммит в ветку.

2. Запускается пайплайн, который исполняет ассоциированный с этой веткой джоб.

3. Джоб инициирует раннер, находящийся на удаленной машине.

4. Раннер скачивает исходники и выполняет скрипт, вызывающий Liquibase.

5. Liquibase генерирует и исполняет скрипты наката/отката.

6. ...

7. Profit!

А теперь обо всем по порядку.

Liquibase

Этот раздел посвящен Liquibase. Здесь не будет полного изложения документации, потому что эта тема для отдельной статьи или даже для цикла статей, но я постараюсь осветить основы Liquibase.

Ссылка на официальную документацию по Liquibase: https://docs.liquibase.com/concepts/basic/home.html

Предполагается, что если вы, уважаемый читатель, вышли на эту статью, то имеете хотя бы начальное знакомство с Liquibase, именуемом в народе ликви, но если нет, я все же изложу вкратце, что это такое и с чем его едят.

Liquibase это платформа управления и наката миграций БД. В основе лежит понятие чейнжсета (changeset) атомарного изменения базы. Чейнжсетом, например, может быть создание таблицы в базе, добавление колонки/триггера, или наполнение таблицы данными. Чейнжсеты объединяются в чейнжлоги (changelog), которые выстраиваются в цепочку, которая применяется на целевой БД последовательно, таким образом обновляя базу до актуального состояния.

changelog

Начнем рассмотрение Liquibase с понятия чейнжлога. Чейнжлог это отдельный файл, содержащий в себе чейнжсеты или ссылки на другие чейнжлоги. Порядок включения чейнжлогов/чейнжсетов определяет порядок их наката. Как и чейнжсеты, чейнжлоги могут быть описаны в одном из четырех форматов: SQL, XML, JSON и YAML. В статье будут рассмотрены форматы SQL и XML как наиболее популярные и удобочитаемые.

Один из чейнжлогов является корневым, т.е. именно от него идет раскручивание всей цепочки. Назовем его мастером и дадим имя master.xml. Этот чейнжлог несет в себе функцию аккумуляции ссылок на другие чейнжлоги. Изначально у нас в проекте был один такой аккумулятор, в который последовательно дописывались ссылки на другие файлы, которые складировались в той же папке. Это приводило к двум проблемам:

• Папка со временем разрослась до гигантских размеров, и ориентироваться в ней стало невозможно.

• Когда с файлом стали работать два разработчика, изменение файла неизбежно приводило к конфликтам, которые приходилось решать.

Во избежание этих проблем рекомендую придерживаться другого подхода, связанного с разбиением задач по релизам/версиям/датам или каким-либо критериям, чтобы отдельные папки с изменениями были небольшими по размеру. В качестве примера приведу структуру тестового проекта для этой статьи.

Файлы Liquibase хранятся в папке db/changelog, в корне лежит мастер-файл master.xml

В отдельных папках, соответствующих релизам 156 и 157, складируются чейнжлоги. В общем случае один таск = один чейнжлог. Также отдельно выделена папка common для служебных целей, там лежит скрипт пре-миграции, который выполняется при каждом накате скриптов. Аналогичным образом можно было бы добавить скрипт пост-миграции, если это нужно.

Разберем файл master.xml:

<?xml version="1.0" encoding="UTF-8"?><databaseChangeLog xmlns="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog"                   xmlns:pro="http://personeltest.ru/away/www.liquibase.org/xml/ns/pro"                   xmlns:xsi="http://personeltest.ru/away/www.w3.org/2001/XMLSchema-instance"                   xsi:schemaLocation="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">  <preConditions>    <dbms type="oracle" />  </preConditions>  <!-- предварительные скрипты -->  <include file="/common/pre_migration.xml" />  <!-- релизы -->  <includeAll path="/v156" relativeToChangelogFile="true" />  <includeAll path="/v157" relativeToChangelogFile="true" /></databaseChangeLog>

XML-файл выглядит несколько перегруженным неймспейсами, пусть это вас сильно не волнует. Это стандартная обертка для любого XML-файла Liquibase.

Раздел preConditions определяет различные параметры запуска, конкретно здесь мы указываем, что используем oracle, но также можно указать имя пользователя, от которого будет происходить запуск скриптов. Более плотно preConditions используются в чейнжсетах.

Далее идет включение скриптов в нужной последовательности. Для этого существуют команды include (включение отдельного файла) и includeAll (включение папки).

Использование includeAll позволяет избежать проблем с конфликтами при правке файла-аккумулятора внутри релиза, но тогда нужно выработать стратегию именования файлов, т.к. их включение будет происходить в алфавитном порядке. В примере выше я использую дату + номер таска, но этого будет недостаточно в реальном проекте, если изменения по таску с меньшим номером нужно применить после таска с большим номером, и при этом они будут в один день.

Как альтернативу includeAll вы можете внутри папки релиза создать свой мастер-файл (аккумулятор скриптов релиза), в который включать ссылки на отдельные скрипты, что в целом менее удобно.

changeset

Чейнжсеты описываются внутри файлов чейнжлогов в виде отдельных блоков. Рассмотрим на примере файла 2021-05-01 TASK-001 CREATE TEST TABLE.xml:

<?xml version="1.0" encoding="UTF-8"?><databaseChangeLog xmlns="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog"                   xmlns:pro="http://personeltest.ru/away/www.liquibase.org/xml/ns/pro"                   xmlns:xsi="http://personeltest.ru/away/www.w3.org/2001/XMLSchema-instance"                   xsi:schemaLocation="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">  <changeSet author="Doomer" id="20210501-01">    <preConditions onFail="MARK_RAN">      <not>        <tableExists tableName="TEST"/>      </not>    </preConditions>    <createTable tableName="TEST" remarks="Тестовый справочник">      <column name="ID" type="NUMBER(28,0)" remarks="Идентификатор">        <constraints nullable="false" primaryKey="true" primaryKeyName="TEST_PK" />      </column>      <column name="CODE" type="VARCHAR2(64)" remarks="Код">        <constraints nullable="false" />      </column>      <column name="NAME" type="VARCHAR2(256)" remarks="Наименование">        <constraints nullable="false" />      </column>    </createTable>    <rollback>      <dropTable tableName="TEST" />    </rollback>  </changeSet></databaseChangeLog>

В файле описан чейнжсет, который создает таблицу TEST с тремя колонками. При этом описание происходит в виртуальных сущностях, которые транслируются в скрипты для конкретной БД.

Элемент preConditions указывает, что чейнжсет нужно применять только в случае, если таблица не существует в базе.

Элемент rollback указывает, как нужно откатывать скрипт, если такой механизм планируется использовать. Откаты в нашей стране, конечно, популярны, но выходят за рамки данной статьи.

Рассмотрим основные атрибуты чейнжсетов.

Подробную документацию по чейнжсетам можно почитать по ссылке.

DATABASECHANGELOG

Чейнжсеты, которые были применены к базе, сохраняются в специальную таблицу DATABASECHANGELOG, в паре с которой идет таблица DATABASECHANGELOGLOCK, обеспечивающая параллельную работу нескольких экземпляров Liquibase.

В таблице нет физических ключей, но у самих чейнжсетов есть уникальный ключ это комбинация идентификатора, автора и имени файла. Политика именования чейнжсетов у всех может быть своя, у нас исторически сложился формат <дата>-<номер><ФИ автора>, например 20210501-01KD. Отмечу, что это мой первый проект с использованием Liquibase, поэтому не буду давать советов, как лучше именовать чейнжсеты.

Также у каждого чейнжсета вычисляется MD5-сумма на основе его тела, поэтому нельзя просто так менять файл чейнжсета, если он уже был применен к базе. Это может иметь значение при разработке, особенно при освоении Liquibase. Поначалу вы будете часто ошибаться с тем, как правильно составить чейнжсет, в итоге в базе окажется сохраненный чейнжсет с MD-5 суммой и неверно выполненный скрипт. В этом случае нужно будет вручную откатить его изменения, а также удалить соответствующие записи из DATABASECHANGELOG.

runAlways

База данных предоставляет инструменты для настройки контекста окружения, что может быть полезно, например, в сценарии аудита таблиц, когда записи, измененные скриптами, выполненных через Liquibase, нужно как-то помечать. Мы это решаем через установку переменной сессии USER_ID.

Для этой цели в описании мастер-чейнжлога включается скрипт пре-миграции, который занимается настройкой окружения. Приведу его текст:

<?xml version="1.0" encoding="UTF-8"?><databaseChangeLog xmlns="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog"                   xmlns:pro="http://personeltest.ru/away/www.liquibase.org/xml/ns/pro"                   xmlns:xsi="http://personeltest.ru/away/www.w3.org/2001/XMLSchema-instance"                   xsi:schemaLocation="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">  <changeSet author="SYSTEM" id="PRE_MIGRATION" runAlways="true">    <sql splitStatements="true" stripComments="true">      -- устанавливаем в контекст сессии пользователя liquibase      CALL DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT','USER_ID', 13);    </sql>  </changeSet></databaseChangeLog>

Здесь в качестве тела используется SQL-скрипт, который записывает в переменную сессии USER_ID значение 13 наш внутренний идентификатор пользователя Liquibase. Этот скрипт будет влиять на все последующие скрипты, поэтому помечен флагом runAlways и включен перед скриптами релизов.

SQL-чейнжсет

Чейнжсеты можно оформлять и в формате SQL, что особенно полезно при написании сложных запросов. Рассмотрим файл 2021-05-01 TASK-002 TEST.sql, который выполняется сразу после создания таблицы TEST:

--liquibase formatted sql--changeset Doomer:20210501-02--preconditions onFail:MARK_RAN--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = 'TEST' AND OWNER = 'STROY';--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM TEST WHERE ID = 1;insert into TEST (ID, CODE, NAME)values (1, 'TEST', 'Какое-то значение');--rollback not required--changeset Doomer:20210501-03--preconditions onFail:MARK_RAN--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = 'TEST' AND OWNER = 'STROY';--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM TEST WHERE ID = 1;update TEST   set NAME = 'CONTEXT USER_ID=' || nvl(SYS_CONTEXT('CLIENTCONTEXT', 'USER_ID'), 'NULL') where ID = 1;--rollback not required

Это файл с двумя чейнжсетами в составе.

Первый добавляет новую запись в таблицу TEST, проверяя существование таблицы и отсутствие элемента с ID = 1. Если одно из условий не выполнится, чейнжсет не будет применен, но будет помечен в DATABASECHANGELOG как выполненный (MARK_RAN). Подробнее можно почитать в документации по preConditions.

Второй чейнжсет обновляет созданную запись значением из переменной сессии USER_ID.

После наката скриптов мы предсказуемо увидим следующую картину в таблице TEST:

К сожалению, формат статьи и ее и без того обширная тема не позволяет описать чейнжсеты Liquibase более подробно, но тут можно только сказать классическое RTFM.

Командная строка Liquibase

Liquibase является консольным приложением, поэтому нужно понимать, как его вызывать. Вообще, это тема больше относится к области GitLab Runner'а, но чтобы не смешивать обе темы, рассмотрим вызов Liquibase в этом разделе.

Документация по командам Liquibase: https://docs.liquibase.com/commands/community/home.html

Нас в первую очередь интересуют команды:

• update применение изменений

• updateSQL получение SQL-скриптов для анализа, полезно для обучения

Обе команды имеют схожий набор параметров, поэтому рассмотрим основные из них на нашем тестовом примере:

Для обучения можно создать батник, который будет запускать Liquibase c вашим скриптом до его добавления в гите. Разумеется, надо помнить о необходимости отката изменений (см. выше). Рядом помещаете папку с Liquibase и драйвером, об установке которых рассказывается в следующем разделе, а также файл скрипта.

Пример батника для Windows:

call "C:\Temp\liqui\liquibase-4.3.1\liquibase.bat" ^--defaultSchemaName=STROY ^--driver=oracle.jdbc.OracleDriver ^--classpath="C:\Temp\liqui\ojdbc5.jar" ^--url=jdbc:oracle:thin:@1.2.3.4:1521:dev ^--username=xxx ^--password=yyy ^--changeLogFile=.\master.xml ^--contexts="dev"--logLevel=info ^updateSQL

Настройка сервера деплоя

Выделенный сервер для деплоя, вообще говоря, не обязателен. Это может быть самая простая машина, которая имеет доступ к целевой базе данных, чтобы подключиться и накатить скрипты, и к серверу GitLab, откуда будут взяты исходники проекта. В целях безопасности эту машину можно изолировать от прочих ресурсов.

В нашей компании используются виртуальные машины на основе Centos 7, поэтому все примеры команд буду приводить именно для него. Также я исхожу из предположения, что вы знакомы с основами работы в консоли Linux.

Установка Java

Liquibase рекомендует использовать Java 11+, давайте установим его. Я использую OpenJRE 11:

sudo yum install java-11-openjdkjava --version

Установка Liquibase

Официальная документация: https://www.liquibase.org/get-started/quickstart

Liquibase не требует установки, потому что является программой на Java. Заходим на сайт и скачиваем архив с программой. Архив распаковываем в папку по вашему усмотрению, но для себя я решил использовать путь /usr/share/liquibase/<version>, например /usr/share/liquibase/4.3.4

Там же, в папке с установленным Liquibase, создаем папку drivers и копируем в нее нужный драйвер для вашей БД. В моем случае это был ojdbc10.jar

Проверяем, что Liquibase работает:

cd /usr/share/liquibase/4.3.4liquibase --version

Установка Git

Git является зависимостью GitLab Runner и ставится автоматически, но нужно отметить одну неприятную особенность, характерную для Centos 7 и, возможно, для других версий пингвинообразных. Если вы просто установите GitLab Runner на голую ось, он потянет за собой git в виде зависимости, который в стандартном репозитории имеет версию 1.8. Эта версия откровенно баганая, что в связке с GitLab приводит к тому, что в какой-то момент, причем не сразу, CI/CD перестает работать с выдачей совершенно непонятной ошибки.

Чтобы избежать неприятных сюрпризов, необходимо установить более свежую версию git до установки GitLab Runner:

# проверяем текущую версию гитаgit --version# удаляем гит, если он версии 1.8sudo yum remove git*# устанавливаем последнюю версию гита на момент написания статьи (2.30)sudo yum -y install https://packages.endpoint.com/rhel/7/os/x86_64/endpoint-repo-1.7-1.x86_64.rpmsudo yum install git

Установка GitLab Runner

Официальная документация: https://docs.gitlab.com/runner/install/linux-manually.html

# добавляем репуcurl -L "https://packages.gitlab.com/install/repositories/runner/gitlab-runner/script.rpm.sh" | sudo bash# устанавливаемexport GITLAB_RUNNER_DISABLE_SKEL=true; sudo -E yum install gitlab-runner

Настройка GitLab Runner

В этом разделе будет рассказано о том, как создать и настроить свой экземпляр GitLab Runner, который будет вызывать Liquibase.

Ссылка на официальную документацию по GitLab Runner: https://docs.gitlab.com/runner/configuration/

Для начала, нужно узнать, куда был установлен раннер, и дать ему права на исполнение:

# определяем место установкиwhich gitlab-runner # /usr/bin/gitlab-runner# выдаем права на исполнениеsudo chmod +x /usr/bin/gitlab-runner

Далее, нужно создать пользователя для сервиса раннера и установить демона. Вполне возможно, что эти шаги уже были выполнены при установке.

# создаем пользователяsudo useradd --comment 'GitLab Runner' --create-home gitlab-runner --shell /bin/bash# запускаем демонаsudo gitlab-runner install --user=gitlab-runner --working-directory=/home/gitlab-runner

Управлять сервисом раннера можно по аналогии с systemctl:

# статус сервисаsudo gitlab-runner status# запуск сервисаsudo gitlab-runner start# останов сервисаsudo gitlab-runner stop# получения списка зарегистрированных раннеровsudo gitlab-runner list

После завершения настройки сервиса GitLab Runner нужно создать экземпляр раннера для запуска Liquibase. Для этого воспользуемся командой register, но перед этим нам нужно получить токен для нашего проекта в GitLab.

Переходим в интерфейсе GitLab в раздел Settings CI/CD Runners. Здесь мы видим список доступных нам раннеров, которые либо привязаны к группе проектов, либо к конкретному проекту. У меня этот список выглядит примерно так (немного законспирировал имена):

Нас интересуют два пункта:

1. Адрес вашего GitLab. Раннер будет обращаться к нему для получения исходников и команд на запуск джобов.

2. Токен для регистрации раннера. По нему раннер будет связан с нашим проектом или группой, если происходит настройка раннера для группы в целом.

Также у каждого раннера вы можете видеть теги, выделенные синим цветом. Это своего рода метки, по которым определяется, будет ли использовать раннер для конкретного джоба, об этом подробнее будет рассказано ниже. Пока достаточно знать, что раннер будет запущен, если его теги содержат теги джоба.

Регистрация раннера

Для создания раннера нужно выполнить команду sudo gitlab-runner register

Вас попросят ввести следующие параметры:

1. Enter the GitLab instance URL

   Вводим адрес вашего GitLab

2. Enter the registration token

   Вводим токен

3. Enter a description for the runner

   Вводим имя раннера, например, my-awesome-runner

4. Enter tags for the runner

   Вводим теги раннера через запятую. Пример: liquibase,dev

   Выбор можно позже изменить через интерфейс GitLab CI/CD

5. Enter an executor

   Выбираем механизм исполнения раннера. Подробнее по ссылке.

   Вводим shell

shell это простейший механизм исполнения команд оболочки целевой ОС. Мы будем использовать скрипты на bash.

После регистрации раннера можно проверить его наличие через команду sudo gitlab-runner list или через интерфейс GitLab CI/CD:

Настройка CI/CD

Информация о том, что делать в процессе CI/CD хранится в файлах проекта. Главным является файл .gitlab-ci.yml в корне. Остальные файлы, в нашем случае скрипты на bash, размещаются на усмотрение разработчика, например, в папке /ci.

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

Для начала отмечу, что есть встроенный линтер для файла .gitlab-ci.yml, доступный по относительному пути ci/lint в проекте, например: https://gitlab.example.com/gitlab-org/my-project/-/ci/lint. Рекомендую воспользоваться им перед пушем конфига. Также предполагается, что вы знакомы с форматом YAML.

Полный листинг конфига в тестовом проекте:

variables:    LIQUIBASE_VERSION: "4.3.4"stages:    - deploydeploy-dev:    stage: deploy    tags:        - liquibase        - dev    script:        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'    environment:        name: dev    only:        - devdeploy-prod:    stage: deploy    tags:        - liquibase        - prod    script:        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'    environment:        name: prod    when: manual    only:        - prod

Переменные

Документация: https://docs.gitlab.com/ee/ci/variables/README.html

variables:    LIQUIBASE_VERSION: "4.3.4"

В этом блоке описаны переменные окружения, которые в дальнейшем можно использовать в скриптах. Переменная LIQUIBASE_VERSION указывает на версию Liquibase, которая будет применяться для наката скриптов миграции. Если мы захотим сменить версию, просто установим новую версию Liquibase и поменяем значение этой переменной.

На самом деле, лучше описать эту переменную в настройках проекта, о чем мы поговорим далее, но это просто пример того, как можно использовать переменные в конфиге.

Переменные проекта можно настроить в разделе Settings CI/CD Variables.

Пример того, как выглядят переменные в интерфейсе:

Помимо переменных, описанных в непосредственно и конфиге и в настройках проекта, существует целый ряд предопределенных переменных, относящихся к процессу CI/CD, таких как имя коммита, ветки или имя пользователя, совершившего коммит. Они могут быть полезны для настройки обратной связи, например, для автоматических уведомлений по результатам деплоя.

Этапы (stages)

stages:    - deploy

В этом блоке перечисляются этапы деплоя, о которых рассказано в разделе описания схемы работы.

Джобы (jobs)

Рассмотрим на примере джоба деплоя на дев:

deploy-dev:    stage: deploy    tags:        - liquibase        - dev    script:        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'    environment:        name: dev    only:        - dev

Строка stage: deploy указывает на этап, к которому относится данный джоб.

Теги перечислены массивом, т.е. это liquibase и dev. Наш ранее созданный раннер отреагирует на эти теги и запустит скрипты в разделе script. Важно отметить, что в джобе прода указан тег prod, поэтому раннер для него должен быть отдельный, исходя из соображений, что он, вероятно, находится в другой сети. А вообще, один и тот же раннер может обслуживать несколько проектов, если это требуется.

Блок environment указывает на окружение, которое можно настроить в отдельном разделе по пути Operations Environments. Это своего рода дашборд ваших стендов (дев, предпрод, прод и т.п.), где можно увидеть их статус и выполнить, например, ручной деплой. Также можно настроить переменные, привязанные к окружению, но это премиум-фича, которую мне попробовать не удалось.

Пример страницы окружений:

Блок only указывает, для каких веток нужно применять данный джоб. Его братом является блок except, который указывает для каких веток джоб применять не нужно. Помимо веток можно настроить более сложные условия: https://docs.gitlab.com/ee/ci/jobs/job_control.html.

Для прода мы также указали when: manual. Это означает, что запуск деплоя для прода будет в ручном режиме при нажатии на кнопку в пайплайне, а не в момент коммита. Это позволит нам собрать чемодан до того, как что-то пойдет не так выполнить деплой в запланированное время.

Скрипт наката

В блоке script мы указываем список скриптов, которые будут выполнены раннером. Так как у нас shell-исполнитель на линуксе, будем использовать bash. Все переменные автоматически передаются в скрипт в виде переменных окружения, но т.к. реквизиты базы отличаются для разных стендов, передаем их непосредственно при вызове скрипта.

Разберем непосредственно скрипт вызова Liquibase:

#!/bin/bashecho "Environment: $CI_ENVIRONMENT_NAME"cd db/changelog/usr/share/liquibase/$LIQUIBASE_VERSION/liquibase \    --classpath=/usr/share/liquibase/$LIQUIBASE_VERSION/drivers/ojdbc10.jar \    --driver=oracle.jdbc.OracleDriver \    --changeLogFile=master.xml \    --contexts="$CI_ENVIRONMENT_NAME" \    --defaultSchemaName=STROY \    --url=jdbc:oracle:thin:@$1 \    --username=$2 \    --password=$3 \    --logLevel=info \    update

Подробно параметры вызова Liquibase описаны в соответствующем разделе.

Переменные DEV_DB, DEV_DB_USER, DEV_DB_PASS приходят в скрипт в виде $1, $2 и $3 соответственно. Помимо них мы используем указанное в джобе имя окружения, которое приходит в предопределенной переменной $CI_ENVIRONMENT_NAME, что можно использовать, чтобы какие-то скрипты накатывались только на определенном стенде, а не на всех.

Если все настроено правильно, то после коммита конфига и скриптов в гит, заработает деплой.

Успешный лог пайплайна для Liquibase выглядит примерно так:

Отклонение мержей с ошибками

Если процесс деплоя пойдет с ошибками, можно не допустить соответствующий мерж. Особенно это полезно, если в CI/CD встроены тесты как отдельный этап. В нашем примере тестов нет, но Liquibase тоже может сломаться, если, например, указаны неверные пути до файлов.

Для запрета ошибочных мержей нужно установить галочку в разделе General Merge requests.

Важно! Галочку нужно устанавливать после настройки CI/CD, иначе перестанут проходить любые мержи.

Заключение

Статья оказалась большой, но это не должно вас пугать. На самом деле мне удалось с нуля и без каких-либо предварительных знаний разобраться и настроить проект с деплоем примерно за 5 часов времени в пятницу вечером. Очень надеюсь, что данная статья поможет вам освоить деплой быстрее, а кого-то направит на путь CI/CD.

Если какие-то моменты остались за кадром, и есть желание узнать о них подробнее, пишите в комментариях. Буду иметь в виду, но обещать ничего не стану, т.к. эта статья, например, отняла у меня более 20 часов времени в течение трех недель, что в два-три раза больше среднего времени на написание статьи.

Как обычно, если понравилась статья, посмотрите другие:

• Интересная статья, обязательная к прочтению:Умный парсер числа, записанного прописью

• Автоматическое обновление скриптов после деплоя

• Классовая сериализация на JavaScript с поддержкой циклических ссылок

Иногда в интерфейсе наших приложений СБИС возникаетнеобходимость "сгруппировать" часть записейв некотором списке (например, служебные сообщения в чате, контакты и телефонные звонки).

Хорошо, если все эти записи приходят с одного источника, а вот еслииз разных сервисов, да с навигацией по курсору- алгоритм реализации становится весьма нетривиальным.

Я целенаправленно не буду здесь приводить реализацию "в коде", а опишу исключительно алгоритмический подход к решению, чтобы при необходимости вы могли его самостоятельно смасштабировать на свои задачи. Итак...

Постановка задачи

У нас есть два сервиса. Как бы может быть и больше, но, следуя предыдущей картинке, пусть для определенности это будутсервисы Звонков и Контактов.

Спасибо коллегам из CRM за интересную задачу.

Хотим в карточке организациив сквозном хронологическом порядкепо дате выводить контакты и звонки, но все звонки между соседними записями контактов"схлопнуть" в единственную записьс указанием их количества.

Но при этоминтерфейс должен оставаться "живым"для пользователя - то есть не должно быть длительных пауз, когда мы чего-то ждем, но не рисуем в списке.

Сразу договоримся, что сервисы работают с базой адекватно, есть все нужные индексы, и запросы строятся эффективно, примерно как я описывал в прошлых статьях:

• PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN

• PostgreSQL Antipatterns: навигация по реестру

• SQL HowTo: пишем while-цикл прямо в запросе, или Элементарная трехходовка

Неудачное решение #1: "дай мне все"

Понятно, что можновычитать сразу вообще все и смержить на бизнес-логике, но это приведет к ожиданию интерфейсом хоть какой-то реакции, пока БЛ не проделаетвсюработу. А ее может быть реально много - одних толькозвонков могут быть сотни.

Да и для сервиса задача "отдать все" совсем нелегка, если там данных за несколько лет работы. Но зачем нам "все", если пользовательдальше первой страницы листает очень редко?

Неудачное решение #2: "частый гребень"

Так, нам контакты группировать не надо?.. Давайте тогдазапросим первую страницу (20 записей) с сервиса контактов, адля каждого интерваладат между "соседними" контактами спросим, что там есть в звонках - сразу и количество получим.

А теперь давайте представим, что у нас все звонки (или очень много) оказались хронологически "над" первым же контактом - что будет? Абудут те же самые тормоза в интерфейсе, что и в предыдущем варианте.

Кроме того, мы илиотправим на сервис много запросов(на каждый из интервалов), чем создадим избыточную нагрузку. Или отправимодин запрос со списком всех интервалов, но он заведомо будет отрабатывать "долго".

Удачное решение #1: "чтение сегментами"

Набив шишек на предыдущих решениях, приходим к выводу, что нам надозапрашивать данные у обоих сервисов по курсору- сколько-то записей (пусть будет 20), начиная с какого-то индексного ключа.

Что дальше делать с двумяупорядоченными сегментамиданных, достаточно очевидно - сливаем (merge ordered) и отрезаем (limit) от упорядоченного все записи после ближайшего из "крайних" ключей от каждого из сервисов.

Например, в нашем примере получилось, что ключ времени "крайнего" звонка соответствует только 15 из 20 прочитанных контактов. Про порядок оставшихся 5 контактов и других звонков мы не можем ничего сказать, потому что "других звонков" как раз нету в обозримом пространстве - поэтомунарисовать их пока не можем.

Неудачное решение #3: "One Ring to rule them all"

Как любому разработчику хочется сразу упростить себе жизнь в предыдущем варианте? Конечно же, запомнитьтолько один ключ- последней отрисованной записи, чтобы потом итерацию для следующего сегмента делать уже от него. Но при таком вариантечасть данных будет вычитываться повторнораз за разом - ровно те, которые мы отбросили в предыдущей итерации и не отобразили.

В особо клиническом случае, типа "сначала много-много звонков, потом уже начинаются контакты" один и тот же первый сегмент контактовможет запрашиваться повторно много-много раз, с каждым сегментом звонков.

Неудачное решение #4: "два ключа на server-side"

Обернем предыдущую проблему себе на пользу - будем запоминатьнезависимые ключи, отдельно для каждого из источников. Это правильный ход, но он не избавит нас от проблемы повторной вычитки тех же данных, еслинам их негде сохранить.

Поскольку у насstateless server-side БЛ, то либо мы их таки и не сохраним, или вынуждены будем городить где-тоотдельное хранилище состояний. Сделать это можно, но совсем не просто:

• хранение сегментов должно происходить по уникальномуключу экземпляра-выборки-на-странице

• необходимаполитика инвалидацииэтих данных со временем, чтобы память не "текла"

• работа с этим хранилищем подразумеваетдополнительные издержки на сериализацию-пересылку-десериализацию

Удачное решение #2: "два ключа на client-side"

Собственно, а зачем нам уносить все это на сервер-сайд, если вседанные нам нужны только на клиенте?.. Давайте их там и оставим.

То есть ровно теданные, которые "не отрисовали" оставить хранитьсяна клиенте (например, прямо в памяти вкладки, даже не в localStorage), пока нам не понадобится их нарисовать.

В нашем предыдущем примере получится что-то вроде:

• прочитали параллельно 20 контактов и 20 звонков

• звонки "сгруппировали" в 5 записей

• нарисовали 5 "групповых" звонков + 15 контактов

• 5 ненарисованных конктактов оставили в хранилище

• до 20 чего-то не хватает? запрашиваем! (контакты и звонки по 20, параллельно от своих "крайних" ключей)

• "задача сведена к предыдущей", только у нас уже сразу 25 контактов на 20 звонков есть

Edge Cases

Фактически, единственный отрицательный эффект у такого решения - последняя нарисованная"групповая" запись может "крутить счетчиком", пока мы дочитываем "вливающиеся" в нее все новые и новые записи.

К счастью, такая ситуация достаточнопозитивно воспринимается в интерфейсе, поскольку мы показываем пользователю: "Эй, все хорошо, мы не умерли, не повисли, мы работаем!"

Добрый день, уважаемые читатели! Материал адресован всем специалистам, работающим с данными, которые решили написать первое веб-приложение. В данной публикации я не буду выкладывать листинги кода. На просторах Интернета есть масса практических примеров сборки сервисов, написанных на разных фреймворках. Но вот теоретических статей о логике процесса, архитектуре решения, а, главное, трудностях, с которыми впервые столкнется специалист, крайне мало. Я решил заполнить эту нишу и описать свой личный опыт, который кому-то может быть полезен.

Специально для статьи я подготовил два идентичных примера на Flask и Dash и выложил их на GitHub. В них иллюстрируется расчет и вывод показателей юнит-экономики абстрактного IT-маркета, который называется Хабр (а почему бы и нет, ведь сейчас все компании начали заниматься электронной коммерцией:).

Разговор предлагаю построить в форме поэтапного тезисного разбора приложенного материла, в процессе я буду акцентировать ваше внимание на тех моментах, которые лично мне показались наиболее сложными или интересными. И, конечно, мы обязательно остановимся на тех ошибках, которые я допустил как бэкенд-разработчик.

ОПП: не умеешь не берись! Когда речь заходит об ОПП, мне почему-то автоматически вспоминается Django с его классами. Но если посмотреть работы начинающих data scientist-ов или аналитиков данных, то мы увидим совсем другую картину. Классы применяются ради самих классов. В данную структуру языка просто сливается весь код. За что отвечает этот монстр? За все! Как искать ошибки или переписывать код, не понятно. Лично у меня такое мнение на этот счет. Если не знаешь когда, как и почему следует применять ОПП, то лучше для небольших разработок использовать процедурно-функциональный стиль.

Пусть безобразие, но единообразие. Даже если вы работаете один, разработайте единую систему формирования пространства имен. Это, во-первых, улучшит читаемость кода, а, во-вторых, ускорит его написание, так как не нужно вспоминать как именно названа функция на другой странице.

Коммитим, даже если не пушим. Даже если у вас нет GitHub аккаунта, заведите себе практику использовать систему контроля версий. Это реально удобно, так как позволяет производить эксперименты во вспомогательных ветках без создания дополнительных листов для тестирования гипотез. Я часто пренебрегаю данной рекомендацией, а зря.

Муки выбора или о разных фреймворках замолвим слово. Сочетание каких технологий можно использовать для создания собственного сервиса? Приведу несколько вариантов, которые сразу приходят на ум. Заранее прошу прощения, что обойду вниманием PHP, Ruby, C#:

• Flask статичные страницы с шаблонами HTML+CSS

• Django статичные страницы с шаблонами HTML+CSS

• Flask Rest API/FastAPI/Django Rest Framework динамические страницы HTML+CSS+фреймворк Javascript (Vue, React, Angular)

• Dash (по сути работает Flask) Dask (по сути работает React)

Как бы рассуждал я, если передо мной стоял выбор.

• Нужно выводить таблицы, графики, интерактивные элементы здесь и сейчас Dash

• Нужно рендерить отдельные показатели на статичной странице. Есть время на эксперименты с дизайном, но нет помощи фронтенд-разработчика Flask

• Нужно выводить разноплановую информацию, нужна интерактивность. Есть много времени, есть ресурсы, плюс поддержка верстальщика и фронтенд-программиста FastAPI Vue.js

Теперь приведу скриншоты работ на Flask и Dash и сделаю несколько замечаний касательно данных платформ.

Задача состояла в том, что нужно было рассчитать, а потом отобразить 6 таблиц с показателями юнит-экономики, то есть сформировать веб-дашборд. Сразу скажу, что на разработку примеров я потратил примерно одинаковое время. Кардинального различия в результатах я не увидел, но есть нюансы.

В проекте Flask файл, который отвечает за вывод результатов, страницы html и фреймворк css это разные сущности. Документация по Bootstrap4 довольно качественная, но так как у меня нет навыков верстки, мне не удалось добиться корректного вывода всех сводных таблиц.

В проекте Dash за все операции отвечает единый файл, так как я выбрал вариант с хранением таблицы стилей в app.py. Если дашборд простой, то читаемость кода будет приемлемой. Но с ростом проекта с этим могут возникнуть трудности. Стили можно переместить в папку asset. Можно ли как-то еще раздробить основной файл я не знаю. Сразу из коробки имеется хорошая поддержка всех аналитических компонентов, включая таблицы, но нужно время для ознакомления со спецификой разработки.

Архитектура всему голова. Заранее продумывайте архитектуру своего приложения. Все файлы должны быть разнесены по модулям согласно их функционалу. При этом нужно стремиться к тому, чтобы, если изъять из сервиса часть модулей, остальная часть программы сохранила работоспособность. Компоненты должны спокойно интегрироваться в другой сервис с минимальными доработками. Переходим к моим ошибкам. Скрипты для запуска etl-процессов и расчета показателей лежат рядом с главным файлом проекта.

Многофункциональности здесь не место. В продолжение предыдущего пункта. Ваше приложение должно делать хорошо что-то одно. Мой сервис выполняет etl-команды, формирует БД, а затем наполняет ее записями и отвечает за вывод дашборда. Это три разных процесса, которые с большой долей вероятности в реальности будут разнесены во времени. И конечно, нужно убирать файл с данными из приложения, так как он только занимает место.

Что SQL-запросом вытянешь, то и считать будешь. Максимально перенесите расчетную нагрузку на сторону БД. При этом следует учитывать разности в диалектах sql. Старайтесь писать запросы максимально универсальными. Мои ошибки. База данных в качестве физического файла присутствует в проекте. В запросах имеются уникальные конструкции диалекта SQLite.

Pandas мне друг, но производительность дороже. Мне пришлось применить данную библиотеку, так требовалась именно сводная таблица, а получить ее на стороне БД проблематично. В большинстве случаев лучше обойтись только нативным Python.

Не все то золото, что YAML-файл. Идею применения yaml файла для хранения констант проекта я почерпнул из одного видео-ролика практикующего data scientist-а на Youtube. Что в этом плохого или хорошего я не знаю. Решать только вам.

А не замахнуться ли нам на Docker. Небольшое лирическое отступление. Чего мне реально не хватает в Windows, так это Docker. В Windows 10 эту проблему решили, а вот в предыдущих версиях пользователям остается лишь устанавливать Docker Toolbox. Но в настоящее время разработка и поддержка данного продукта завершена, хотя архивный файл можно по-прежнему скачать на официальном аккаунте Docker на GitHub. Лично у меня по некоторым причинам установлен Windows 8.1, поэтому я задался вопросом, как еще можно заполучить в распоряжение эту программу. Установку второй операционной системы я отмел сразу, а вот вариант с виртуальной машиной меня заинтересовал. Для экономии ресурсов я выбрал Debian 10. Если выделить под нужды ВМ один процессор и три гигабайта оперативной памяти, то вполне можно тестировать свои идеи. Но стоит оговориться, что если захочется собрать и запустить контейнер с Apache Airflow, то указанных вычислительных мощностей будет недостаточно.

Теперь можно возвращаться к нашим приложениям. Как сбилдить и запустить контейнер я рассказывать не буду, так как данную информацию легко можно нагуглить в Интернете. Есть лишь пара моментов, на которых я заострю внимание. В процессе сборки будет выдаваться предупреждение о необходимости создания виртуального окружения внутри контейнера. Я решил пренебречь им, так как контейнер и так изолирован от рабочей среды Linux. И еще момент. После того, как приложение на Dash было упаковано в docker-контейнер, перестал отображаться логотип Хабра. Явной причины этого я быстро не нашел, а время, отведенное на эксперимент, было исчерпано.

Семь раз проверь, один раз задеплой. Завершить публикацию я решил на банальной ноте. А именно напомнить вам, о том, как важно проверять результаты перед сдачей. Пара досадных опечаток в комментариях, конечно, не поставят крест на всем проекте, но ведь может сложиться ситуация, что приложение просто не запуститься на демонстрации.

И вот вам конкретный пример. Я построил контейнер на Dash, а дашборд в браузере не отображается. В локальном варианте все было нормально. Оказалось, я просто забыл поменять в файле app.py хост с 127.0.0.1, на 0.0.0.0.

Вместо заключения. За скобками разговора остались моменты связанные с подготовкой проекта к развертыванию на сервере и непосредственно деплой. Пусть это будет вопросами для самостоятельного изучения или темой одной из будущих публикаций.

На этом все. Всем здоровья, удачи и профессиональных успехов!

О чем эта статья и кому адресована?

С SQL работают почти все, но даже опытные разработчики иногда не могут ответить на простой вопрос. Каким образом СУБД выполняет самый обычный INNER JOIN?

С другой стороны - разработчики на C# или других ООП языках часто воспринимают СУБД как всего лишь хранилище. И размещать какие-то бизнес-правила в SQL - плохо. В противовес им создаются библиотеки вродеLinq2Db(не путаем сLinq2Sql- совершенно разные авторы и разные библиотеки). При ее использовании весь код пишется на C# и вы получаете все преимущества типизированного языка. Но это формальность. Затем этот код транслируется на SQL и выполняется на стороне СУБД.

Для того чтобы лучше разобраться как работает одинаковый код на SQL и на C# мы попробуем реализовать одно и то же на первом и на втором, а затем разберем как это работает. Если вы хорошо знаете что такоеNested Loop,Merge Join,Hash Join- вам скорее всего имеет смысл прочитать статью по диагонали. А вот если не знаете - статья для вас должна быть полезной.

Работа с несколькими коллекциями

Предположим, что у нас есть некоторый сервисный центр по техническому обслуживанию автомобилей - станция технического обслуживания (СТО). Есть две сущности:Person- клиенты сервисного центра иVisit- конкретное посещение данного центра.Personкроме идентификатора содержит имя, фамилию и статус активности (например, если клиент поменял машину на другую марку - он переводится в статус не активного и уже не будет в ближайшем времени посещать нас).Visitкроме идентификатора содержит в себе ссылку на клиента, дату визита и сумму, которую заплатил клиент за этот визит. Все вышеперечисленное можно было бы оформить с помощью следующих классов на C# для самого простейшего случая:

internal sealed class Person{    internal int Id { get; set; }    internal string FirstName { get; set; }    internal string LastName { get; set; }    internal bool IsActive { get; set; }}internal sealed class Visit{    internal int Id { get; set; }    internal int PersonId { get; set; }    internal DateTime Date { get; set; }    internal decimal Spent { get; set; }}// ...internal Person[] persons = new Person[];internal Visit[] visits = new Visit[];// ...

В базе данных (в дальнейшем мы будем использоватьPostgreSQL) для двух этих сущностей есть две таблицы с аналогичными полями:

create table public.visit(    id integer,    person_id integer,    visit_datetime timestamp without time zone,    spent money) tablespace pg_default;create table public.person(    id integer,    first_name character varying(100) COLLATE pg_catalog."default",    last_name character varying(100) COLLATE pg_catalog."default",    is_active boolean) tablespace pg_default;

Исходный код для данной статьи находитсяздесь. Если у вас есть какие-то замечания - можете сразу править.

Пусть наша задача сводится к написанию простейшего бизнес-правила -найти общую сумму выручки за 2020 год и ранее, которую принесли клиенты, находящиеся сейчас в активном статусе. Как можно реализовать решение такой простой задачи?

Nested Loop

Самая простая идея, которая приходит на ум. Бежим в цикле по клиентам и во вложенном цикле бежим по всем посещениям. Проверяем все условия и если находим совпадение - добавляем сумму затрат этого визита в итоговый результат.

public decimal NestedLoop(){    decimal result = 0;    var upperLimit = new DateTime(2020, 12, 31);    foreach (var person in persons)    {        if (person.IsActive == false)        {            continue;        }                foreach (var visit in visits)        {            if (person.Id == visit.PersonId && visit.Date <= upperLimit)            {                result += visit.Spent;            }        }    }    return result;}

Эта идея анимирована ниже:

Алгоритм очень простой, не потребляет дополнительной памяти. Но затратность егоO(N), что будет сказываться на большом числе элементов - чем их больше, тем больше телодвижений необходимо совершить.

Для того, чтобы оценить скорость его работы мы создадим тестовый набор данных с помощью следующего SQL скрипта:

select setseed(0.777);delete from public.person;insert into public.person(id, first_name, last_name, is_active)select row_number() over () as id,substr(md5(random()::text), 1, 10) as first_name,substr(md5(random()::text), 1, 10) as last_name,((row_number() over ()) % 5 = 0) as is_activefrom generate_series(1, 5000);/*<-- 5000 это число клиентов*/delete from public.visit;insert into public.visit(id, person_id, visit_datetime, spent)select row_number() over () as id,(random()*5000)::integer as person_id, /*<-- 5000 это число клиентов*/DATE '2020-01-01' + (random() * 500)::integer as visit_datetime,(random()*10000)::integer as spentfrom generate_series(1, 10000); /* 10000 - это общее число визитов в СТО*/

В данном случае число клиентов CTOPравно 5000, число их визитовV- 10000. Дата визита, а также сам факт визита для клиента генерируются случайным образом из указанных диапазонов. Признак активности клиента выставляется для каждого пятого. В итоге мы получаем некоторый тестовый набор данных, приближенный к реальному. Для тестового набора нам интересна характеристика - число клиентов и посещений. Или(P,V)равное в нашем случае(5000, 10000). Для этого тестового набора мы сделаем следующее: выгрузим его в обьекты C# и с помощью цикла в цикле (Nested Loop) посчитаем суммарные траты наших посетителей. Как это определено в постановке задачи. На моем компьютере получаем приблизительно20.040 миллисекунд, затраченное на подсчет. При этом время получение данных из БД составило все те же самые20.27 миллисекунд. Что в сумме дает около40 миллисекунд. Посмотрим на время выполнения SQL запроса на тех же данных.

select sum(v.spent) from public.visit v                    join public.person p on p.id = v.person_idwhere v.visit_datetime <= '2020-12-31' and p.is_active = True

Все на том же компьютере получилось порядка2.1 миллисекундына все. И кода заметно меньше. Т.е. в 10 раз быстрее самого метода, не считая логики по извлечению данных из БД и их материализации на стороне приложения.

Merge Join

Разница в скорости работы в 20 раз наталкивает на размышления. Скорее всего Nested Loop не очень нам подходити мы должны найти что-то получше. И есть такой алгоритм НазываетсяMerge JoinилиSort-Merge Join. Общая суть в том, что мы сортируем два списка по ключу на основе которого происходит соединение. И делаем проход всего в один цикл. Инкрементируем индекс и если значения в двух списках совпали - добавляем их в результат. Если в левом списке идентификатор больше, чем в правом - увеличиваем индекс массива только для правой части. Если, наоборот, в левом списке идентификатор меньше, то увеличиваем индекс левого массива. Затратность такого алгоритмаO(N*log(N)).

Результат работы такой реализации радует глаз -1.4 миллисекундыв C#. Правда данные из базы данных еще нужно извлечь. А это все те же самые дополнительные20 миллисекунд. Но если вы извлекаете данные из БД, а затем выполняете несколько обработок, то недостаток постепенно нивелируется. Но можно ли подсчитать заданную сумму еще быстрее? Можно!Hash Joinпоможет нам в этом.

Hash Join

Этот алгоритм подходит для больших массивов данных. Его идея проста. Для каждого из списков считается хэш ключа, далее этот хэш используется для того, чтобы выполнить сам Join. Детально можно посмотреть в видео:

Видео работы Hash Join (на англ. языке)

Затратность алгоритмаO(N). В .NET стандартный Linq метод как раз его и реализует. В реляционных СУБД часто используются модификации этого алгоритма (Grace hash join,Hybrid hash join) - суть которых сводится к работе в условиях ограниченной оперативной памяти. Замер скорости работы в C# показывает, что этот алгоритм еще быстрее и выполняется за0.9 миллисекунды.

Динамический выбор алгоритма

Отлично! Похоже мы нашли универсальный алгоритм, который самый быстрый. Нужно просто использовать его всегда и не беспокоиться более об этом вопросе. Но если мы учтем еще и расход памяти все станет немного сложнее. Для Nested Loop - память не нужна, Merge Join - нужна только для сортировки (если она будет). Для Hash Join - нужна оперативная память.

Оказывается расход памяти - это еще не все. В зависимости от общего числа элементов в массивах скорость работы разных алгоритмов ведет себя по-разному. Проверим для меньшего числа элементов (P, V) равному (50, 100). И ситуация переворачивается на диаметрально противоположную:Nested Loopсамый быстрый -2.202 микросекунды, Merge Join -4.715 микросекунды, Hash Join -7.638 микросекунды. Зависимость скорости работы каждого алгоритма можно представить таким графиком:

Для нашего примера можно провести серию экспериментов на C# и получить следующую таблицу:

А что если узнать значения X1 и X2 и динамически выбирать алгоритм в зависимости от его значения для данных коллекций? К сожалению не все так просто. Наша текущая реализация исходит из статичности коллекции. Что нужно сделать, чтобы вставить еще один визит за 2020 год? В массив в коде на C#. В массив фиксированного размера он, очевидно, не поместится. Нужно выделять новый массив размером на один элемент больше. Скопировать туда все данные, вставлять новый элемент. Понятно, что это дорого. Как насчет того, чтобы заменить Array на List? Уже лучше, т.к. он предоставляет все необходимое API. Как минимум удобно, но если посмотреть на его реализацию - под капотом используется все тот же массив. Только резервируется памяти больше чем надо С запасом. Для нас это означает лишние траты памяти. LinkedList? Здесь должно быть все нормально. Давайте поменяем коллекцию и посмотрим что из этого получится.

Время выполнения не только изменилось. Сама кривая стала более крутой и с числом элементов время растет:

Таким образом мы приходим к понимаю, что время доступа к каждому конкретному элементу коллекции крайне важно. Одно из главных преимуществ реляционных СУБД в том, что они всегда готовы к добавлению новых данных в любой диапазон. При этом это добавление произойдет максимально эффективным образом - не будет релокации всего диапазона данных или т.п. Кроме того данные СУБД часто хранится в одном файле - таблицы и их данные. Если утрировать, то здесь также используется связанный список. В случае с PostgreSQL данные представлены в страницах (page), внутри страницы располагаются кортежи данных (tuples). В общих чертах вы можете себе это увидеть на картинках ниже. А если захотите узнать больше деталей, то ниже также есть и ссылка.

Более детально описано в первоисточникеЗдесь

Структура кортежа также адаптирована для хранения практически любых данных в таблице, на их обновление и вставку в любой участок диапазона:

Более детально описано в первоисточникеЗдесь.

В оперативную память попадают страницы, а попадают они вbuffer poolчерезbuffer manager. Все это сказывается на стоимости доступа к каждому конкретному значению таблицы. Вне зависимости от того что используетсяNested Loop,Merge JoinилиHash Join. Другой вопрос, что в зависимости от алгоритма число обращений может отличаться в разы. Поэтому реляционные СУБД подходят динамически к выбору алгоритма в каждом конкретном запросе и строят план запроса (Query Plan).

Сравним для большого числа элементов насколько будет отличаться время обработки с одним и тем же алгоритмом в БД и на C#. (P, V) будет равно (50000, 100000). В коде на C# загрузка данных из БД занимает145.13 миллисекунд. Дополнительно к этому выполнение самой логики сNested Loopна основе обычного массива -305.38 миллисекунд,Hash Join-36.59 миллисекунд. Для того чтобы проверить в СУБД такую же реализацию мы будем использовать такой скрипт:

set enable_hashjoin to 'off';--Заставляем БД использовать Nested Loopset enable_mergejoin to 'off';set enable_material to 'off';select sum(v.spent) from public.visit vjoin public.person p on p.id = v.person_idwhere v.visit_datetime <= '2020-12-31' and p.is_active = True

На аналогичных данных в БД сNested Loopзапрос выполнится за11247.022 миллисекунд. Что может говорить о сильно большем времени доступа к каждому конкретному элементу:

Но СУБД приходится заставлять работать так, чтобы она использовалаNested Loop. Изменим наш скрипт таким образом:

set enable_hashjoin to 'on';set enable_mergejoin to 'on';set enable_material to 'on';select sum(v.spent) from public.visit vjoin public.person p on p.id = v.person_idwhere v.visit_datetime <= '2020-12-31' and p.is_active = True

По-умолчанию для такого объема данных будет, конечно выбранHash Join:

И мы видим, что время выполнение составило25.806 миллисекунды, что сопоставимо по скорости с реализацией на C# и даже немного быстрее.

Как мы видим, СУБД может динамически подстраиваться под данные и автоматически выбирать алгоритм, наиболее подходящий в данной конкретной ситуации. Процесс выбора такого способа выполнения запроса возложен напланировщик запросов. В итоге он выдает план запроса, где четко расписано какой алгоритм использовать, какой использовать индекс и т.п.

Выводы

На примере простейшей задачи мы в общих чертах разобрали как работает типичная реляционная СУБД при реализации JOIN. Сравнивать коллекции C# и SQL не очень корректно, за внешней схожестью скрывается серьезное различие в предназначении. Реляционная СУБД призвана обеспечитьконкурентный доступ к данным максимально эффективным способом(при этом подразумевается, что сами данные могут постоянно модифицироваться). Кроме того, данные могут не помещаться в оперативную память и частично храниться на диске.

Более того, СУБД обязана обеспечить сохранность данных на постоянном носителе - одно из основных ее предназначений. При этом на получение данных СУБД динамически выбирает алгоритм, наиболее эффективный в данном случае. В C# аналагичных библиотек или реализаций просто нет И это показательно, т.к. лишь свидетельствует об отсутствии такой необходимости. Linq метод Join реализуетHash Join, который потенциально тратит больше оперативной памяти, но это просто не берется в расчет. Т.к. мало кого интересует применительно к решаемым задачам.

На практике вопрос производительности не является единственным - сопровождаемость кода, покрытие его тестами, скорость работы этих тестов - все тоже очень важно.

Описание проблемы

Не раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием IP-адресов в базе данных. Предположим, что есть типичная задача: необходимо парсить огромное количество диапазонов адресов (~300k) сизвестного ресурса, а далее определять страну по IP-адресу клиента. Кажется, ничего особенного. Это довольно просто решается любым ниже описанным способом при малых нагрузках. Но если у нас тысячи пользователей, или наш сервис является прокси перед всеми остальными? В этом случае не хочется быть бутылочным горлышком и приходится бороться за каждую долю секунды.

Немного про адресацию

Существует 2 типа адресации в сети

INET (Классовая адресация IP-сетей) архитектура сетевой адресации, которая использовалась в Интернете в период с 1981 по 1993 годы. Была вытеснена бесклассовой адресацией ввиду плохой гибкости и неэкономичного использования адресного пространства.

CIDR (Classless Inter-Domain Routing,Бесклассовая адресация) современный метод IP-адресации, при которой количество адресов в сети определяется маской подсети.

Диапазон адресов записывается в видеaddress/y, гдеy число бит маски подсети. Например, /28 означает, что 28 разряда IP-адреса отводятся под номер сети, а остальные 4 разряда полного адреса под адреса хостов этой сети, адрес этой сети и широковещательный адрес сети.

Например, запись192.168.5.0/24означает диапазон адресов от192.168.5.1до192.168.5.254, а также192.168.5.0 адрес сети и192.168.5.255 широковещательный адрес сети.

Типы inet и cidr по умолчанию

PostgreSQL предоставляет 2 типа по умолчанию для хранения IP-адресов и диапазонов:inetиcidr. Существует путаница между официальными названиями классовой и бесклассовой адресации и типамиinet/cidr.

Типinetсодержит адрес узла, а также может содержать подсеть. Вводимое значение должно иметь форматaddress/y. Если компонентyотсутствует, то маска сети считается равной 32 (для IPv4), так что это значение будет представлять один узел.

Типcidrсодержит определение сети IPv4 (или IPv6). Вводимое значение также имеет форматaddress/y. Но еслиyкомпонент отсутствует, то сеть вычисляется по старой классовой схеме нумерации сетей (INET).

Существенным отличием этих двух типов является в том, чтоinetпринимает значения с ненулевыми битами справа от маски сети, аcidrнет. Если у вас сетевая маска /8, то типcidrтребует, чтобы все 24 крайних правых бита были равны нулю,inetне имеет этого требования. Например,255.0.0.2/8будет ошибочным дляcidrт.к. справа от маски255.0.0.0имеются ненулевые значения (цифра 2 в последнем разряде адреса).255.128.128.7/24, 255.255.255.255/31 тоже ошибочны, а вот для типаinetявляются валидными.

А может уже померим что-нибудь?

Выполним несколько предварительных настроек на локальной машине (MacBook 16, 2019 2,6 GHz 6-Core Intel Core i7). Создадим таблицу и добавим индекс для поля с IP-адресом:

CREATE INDEX ON ip_ranges USING GIST (ip_range inet_ops);

Попробуем выполнить большое количество запросов (1.000.000) определения вхождения в диапазон IP-адреса клиента с помощью цикла:

DO$$DECLARE  i RECORD;BEGIN FOR i IN 1..1000000 LOOP  PERFORM country_id FROM ip_ranges WHERE ip_range >>= {random_ip}; end loop;END;$$;

и посчитаем среднее время определения адреса.

Волшебный ip4r

Существует способ и более быстродействующий использование расширенияip4r, позволяющее значительно сократить время определения страны пользователя.

Расширение гарантирует, что умеет в индексы лучше, чем встроенные типы PostgreSQL. И указывает на низкую производительность дефолтных типов даже в новых версиях СУБД. Кроме того, говорит о перегруженности дефолтных типов.

После тех же самых проверок получили значительное снижение времени определения страны пользователя до38 мкс.

Серебряная пуля (или нет?)

Если вдруг вы используетеnginx, то для него естьgeo модуль, позволяющий определять по IP-адресу нужный параметр. Создадим сервис черезdocker-compose.yml:

version: '3.7'services:  web:    image: nginx:latest    volumes:      - ./nginx.conf:/etc/nginx/nginx.conf      - ./GeoIP.dat:/var/geo/GeoIP.dat      - ./geo.conf:/var/geo/geo.conf    ports:      - "8080:80"    environment:      - NGINX_PORT=80

Конфигnginx:

http {        ...    geo $geo {        default        NONE;        include        /var/geo/geo.conf;    }    geoip_country /var/geo/GeoIP.dat;        ...    server {        ...        location / {            ...            add_header Geo-By-File $geo;            add_header Geo-By-Binary $geoip_country_code;        }    }}

Мы можем получать гео клиента, через переменную$geo, предварительно сгенерировав файлgeo.confтипа:

128.0.0.0/1 US;...

Или скачать бинарный файлGeoIP.datи использовать его без генерации, получая гео через переменные ($geoip_country_code).

Измерить скорость определения в данном случае не получилось, но на боевом сервере проблем с быстродействием этого способа не возникало. Несмотря на простоту и удобство данного способа, не всегда возможно его использование (требование частого обновления и проблемы с этим, политики безопасности компании, и т.д.).

Выводы

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

Конечно, все представленные типы позволяют делать не только операцию поиска вхождения в диапазон (она была выбрана как наиболее популярная в нашей команде). А также операциипересечения диапазонов, сравнения и т.д. Буду благодарен, если кто-то напишет об опыте использования других операций и их поведении для разных типов.

В случае если вам не нужно хорошее быстродействие, мало клиентов, вы не боретесь за доли секунды, то вам подойдут типы по умолчаниюinetилиcidr, различие между которыми находятся в рамках статистической погрешности. Расширениеip4rпозволит сократить время в ~20 раз.

Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросовexplain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.

"Ландон из зе кепитал оф Грейт Британ"

Да, наконец свершилось то, о чем нас так долго просили ради иностранных коллег - мы поддержали работу сервиса не только "на русском", но и на других языках. Пока - только на английском, зато полноценно, включая и перевод рекомендательных подсказок.

А чтобы коллегу можно было оперативно подключить к анализу проблем, добавлена возможность оперативно "расшарить" ссылку на план через популярные мессенджеры, соцсети или почту.

Подсказки к плану

Про базовый набор подсказок и способов ими воспользоваться я уже рассказывал в статье "Рецепты для хворающих SQL-запросов" - теперь мы сделали их еще больше и удобнее!

• цветовая и текстовая кодировка разных видов

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

• несколько подсказок у одного узла

  Неудачно использованный индекс с большим количеством отбрасываемых записей может легко соседствовать с неактуальной статистикой на таблице и быть ее следствием.

• на самом видном месте

  Больше не нужно листать огромный план, чтобы увидеть, где там возникла проблема - все подсказки уже сдублированы в заголовке, и достаточно всего одного клика, чтобы перейти к нужному узлу.

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

Масштабируемая диаграмма

Если прежде увидеть "горячие точки" на потоковой диаграмме выполнения большого плана было сложно за счет возникавшей прокрутки, то теперь сразу запускается просмотр в режиме масштабирования. Но вернуться к режиму прокрутки можно в один клик.

Пользуйтесь! Возникнут идеи или замечания - прошу в комментарии.

Иногда при выполнении длительных или плохо написанных запросов в PostgreSQL происходят разные неприятные вещи типа внезапного сбоя процесса или краша всего сервера.

В таких случаях на носителе могут остаться "мертвые души" - файлы (иногда совсем немаленькие, а вполне сравнимые по объему со всей остальной базой), которые были созданы во время работы процесса в качестве временного хранилища промежуточных данных.

Эти данные уже никому не нужны, никем не могут быть использованы, но сервер все равно не торопится избавиться от них как Плюшкин.

Сегодня посмотрим, как их можно найти и безболезненно "зачистить".

Разыскиваем temp buffers

Первая категория возникающих проблем - временное использование дискового пространства при выполнении узла плана, если необходимый объем памяти не уместился в work_mem.

Получить такой эффект достаточно просто - забыть поставить или выбрать слишком большой предел рекурсии:

explain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e4 -- 10k итераций)TABLE T ORDER BY s DESC LIMIT 1;

[просмотреть на explain.tensor.ru]

Корень беды заключается в том, что для сортировки рекурсивной выборки T необходимо вычислить и куда-то записать ее полностью, что и показывает атрибут temp written:

->  CTE Scan on t  (actual time=0.008..374.157 rows=10001 loops=1)      Buffers: temp written=6126

Давайте теперь сэмулируем неприятность, случившуюся во время выполнения запроса - увеличим для этого ограничение рекурсии на порядок:

SELECT pg_backend_pid();-- 15004 - это PID процесса, обслуживающего наше клиентское соединениеexplain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e5 -- 100k итераций)TABLE T ORDER BY s DESC LIMIT 1;

kill -9 15004

Сервер быстро упал - быстро поднялся. Но место на диске у нас убыло почти на 4GB - где же они?

Найти их нам поможет функция получения списка временных файлов pg_ls_tmpdir:

SELECT * FROM pg_ls_tmpdir();

 name            |  size      |  modificationpgsql_tmp15004.0 | 1073741824 | 2021-05-12 10:46:10+03pgsql_tmp15004.1 | 1073741824 | 2021-05-12 10:46:38+03pgsql_tmp15004.2 | 1073741824 | 2021-05-12 10:47:06+03pgsql_tmp15004.3 |  958078976 | 2021-05-12 10:47:31+03

Данная функция появилась только в PostgreSQL 12, поэтому если версия вашего сервера младше, придется воспользоваться pg_ls_dir по <data>/base/pgsql_tmp - это как раз то место, где сохраняются временные файлы, которые мы ищем.

Эти файлы в имени содержат PID процесса, который их породил. Поэтому, чтобы не зачистить лишнего, сразу проверим, что среди активных запросов такого идентификатора нет:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/pgsql_tmp' dir), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), tmp AS (  SELECT    *  , regexp_replace(fn, '^pgsql_tmp(\d+).*$', '\1')::integer pid  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls)SELECT  dir || '/' || fnFROM  tmpLEFT JOIN  pg_stat_activity sa    USING(pid)WHERE  sa IS NOT DISTINCT FROM NULL;

Теперь осталось пройти по полученному списку и поудалять. Замечу, что если "прибивать" запрос через pg_terminate_backend(pid), то и сервер не "падает", и подобного "мусора" в каталоге не остается.

Ничейные TEMPORARY TABLE

CREATE TEMPORARY TABLE x ASSELECT  i, repeat('a', i::integer) sFROM  generate_series(1, 1e5) i;

Теперь в списке схем нашего соединения появилась pg_temp_5:

SELECT current_schemas(true);-- {pg_temp_5,pg_catalog,public}

Именно на эту схему проецируется обращение к псевдосхеме pg_temp - то есть в этом соединении запросы TABLE x, TABLE pg_temp.x и TABLE pg_temp_5.x будут эквивалентны, пока эта временная таблица существует.

Но раз эта таблица полноценная, а не "полуфабрикат", как в случае temp buffers, то мы должны бы увидеть ее и в pg_class:

SELECT  oid, relnamespace::regnamespace, relname, relfilenodeFROM  pg_classWHERE  relnamespace::regnamespace::text ~ '^pg(_toast)?_temp';

 oid  |  relnamespace   |  relname             |  relfilenode66112 | pg_toast_temp_5 | pg_toast_66109       | 6611266114 | pg_toast_temp_5 | pg_toast_66109_index | 6611466109 | pg_temp_5       | x                    | 66109

Выяснение такой странной нумерации схем приводит к письму Tom Lane аж от февраля 2003:

> What is the origin of these schemas? local temporary tables? sorts?

Right, they're made to hold temporary tables. The first time a givenbackend does CREATE TEMP TABLE, it looks for a pg_temp_n schema, and makes it if it's not there. On shutdown, it removes the temp tables, but it seemed like a waste of cycles to remove the pg_temp_n schema itself.

(ObTrivialFact: the 'n' is the backend's pgproc slot number, so it's known not to be in use by any concurrently running backend. But it will certainly be used again in future.)

Итак, при штатном гашении сервера сами файлы временных таблиц должны быть вычищены. Собственно, а где они?

В отличие от temp buffers, относящихся ко всему серверу, файлы временных таблиц и индексов относятся к конкретной базе, но имеют несколько другой формат имени:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir)SELECT  *FROM  lsWHERE  fn ~ '^t';

 dir                |  fn.../data/base/16393 | t5_66109.../data/base/16393 | t5_66112.../data/base/16393 | t5_66114

То есть имя файла временного объекта выглядит как t<temp schema N>_<temp object OID>. Если сейчас мы "уроним" сервер снова, эти файлы останутся, как и записи в pg_class.

Чтобы избавиться от них, можно прогнать VACUUM FULL по всей базе, но это практически невозможно, если она достаточно велика. Или просто подождать когда то же самое доберется сделать autovacuum:

LOG: autovacuum: found orphan temp table "tst.pg_temp_5.x"

Но если таблиц в базе немало, это может наступить ой как нескоро, а дисковое пространство по-прежнему будет занято.

Поэтому, чтобы выловить заведомо "ничейные" временные таблицы, сравним время последней модификации их файлов со временем перезагрузки сервера, которое в обычных условиях можно определить как время сброса статистики по "нулевой" базе:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), lsid AS (  SELECT    *  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls  WHERE    fn ~ '^t'), sch AS (  SELECT DISTINCT    regexp_replace(fn, '^t(\d+)_.*$', '\1') sch  FROM    lsid  WHERE    modification < (      SELECT        stats_reset      FROM        pg_stat_database      WHERE        datid = 0    ))SELECT  string_agg('DROP SCHEMA ' || nspname || ' CASCADE;', E'\n') sFROM  schJOIN  pg_namespace nsp    ON nsp.nspname IN ('pg_temp_' || sch, 'pg_toast_temp_' || sch);

Получаем готовый текст запроса, который останется только выполнить:

DROP SCHEMA pg_temp_5 CASCADE;DROP SCHEMA pg_toast_temp_5 CASCADE;

Так вышло, что на заре моей карьеры в IT меня покусал Oracle -- тогда я ещё не знал ни одной ORM, но уже шпарил SQL и знал, насколько огромны возможности БД.

Знакомство с DjangoORM ввело меня в глубокую фрустрацию. Вместо возможностей -- хрена с два, а не составной первичный ключ или оконные функции. Специфические фичи БД проще забыть. Добивало то, что по цене нулевой гибкости мне продавали падение же производительности -- сборка ORM-запроса не бесплатная. Ну и вишенка на торте -- в дополнение к синтаксису SQL надо знать ещё и синтаксис ORM, который этот SQL сгенерирует. Недостатки, которые я купил за дополнительную когнитивную нагрузку -- вот уж где достижение индустрии. Поэтому я всерьёз считал, что без ORM проще, гибче и в разы производительнее -- ведь у вас в руках все возможности БД.

Так вот, эта история с SQLAlchemy -- счастливая история о том, как я заново открыл для себя ORM. В этой статье я расскажу, как я вообще докатился до такой жизни, о некоторых подводных камнях SQLAlchemy, и под конец перейду к тому, что вызвало у меня бурный восторг, которым попытаюсь с вами поделиться.

Опыт и как результат субъективная система взглядов

Я занимался оптимизацией SQL-запросов. Мне удавалось добиться стократного и более уменьшения cost запросов, в основном для Oracle и Firebird. Я проводил исследования, экспериментировал с индексами. Я видел в жизни много схем БД: среди них были как некоторое дерьмо, так и продуманные гибкие и расширяемые инженерные решения.

Этот опыт сформировал у меня систему взглядов касательно БД:

• ORM не позволяет забыть о проектировании БД, если вы не хотите завтра похоронить проект

• Переносимость -- миф, а не аргумент:

  • Если ваш проект работает с postgres через ORM, то вы на локальной машине разворачиваете в докере postgres, а не работаете с sqlite

  • Вы часто сталкивались с переходом на другую БД? Не пишите только "Однажды мы решили переехать..." -- это было однажды. Если же это происходит часто или заявленная фича, оправданная разными условиями эксплуатации у разных ваших клиентов -- милости прошу в обсуждения

  • У разных БД свои преимущества и болячки, всё это обусловлено разными структурами данных и разными инженерными решениями. И если при написании приложения мы используем верхний мозг, мы пытаемся избежать этих болячек. Тема глубокая, и рассмотрена в циклах лекций Базы данных для программиста и Транзакции от Владимира Кузнецова

• Структура таблиц определяется вашими данными, а не ограничениями вашей ORM

Естественно, я ещё и код вне БД писал, и касательно этого кода у меня тоже сформировалась система взглядов:

• Контроллер должен быть тонким, а лучший код -- это тот код, которого нет. Код ORM -- это часть контроллера. И если код контроллера спрятан в библиотеку, это не значит, что он стал тонким -- он всё равно исполняется

• Контроллер, выполняющий за один сеанс много обращений к БД -- это очень тонкий лёд

• Я избегаю повсеместного использования ActiveRecord -- это верный способ как работать с неконсистентными данными, так и незаметно для себя сгенерировать бесконтрольное множество обращений к БД

• Оптимизация работы с БД сводится к тому, что мы не читаем лишние данные. Есть смысл запросить только интересующий нас список колонок

• Часть данных фронт всё равно запрашивает при инициализации. Чаще всего это категории. В таких случаях нам достаточно отдать только id

• Отладка всех новых запросов ORM обязательна. Всегда надо проверять, что там ORM высрала (тут пара сочных примеров), дабы не было круглых глаз. Даже при написании этой статьи у меня был косяк как раз по этому пункту

Идея сокращения по возможности количества выполняемого кода в контроллере приводит меня к тому, что проще всего возиться не с сущностями, а сразу запросить из БД в нужном виде данные, а выхлоп можно сразу отдать сериализатору JSON.

Все вопросы данной статьи происходят из моего опыта и системы взглядов

Знакомство в SQLAlchemy

Первое, что бросилось в глаза -- возможность писать DML-запросы в стиле SQL, но в синтаксисе python:

order_id = bindparam('order_id', required=True)return \    select(        func.count(Product.id).label("product_count"),        func.sum(Product.price).label("order_price"),        Customer.name,    )\    .select_from(Order)\    .join(        Product,        onclause=(Product.id == Order.product_id),    )\    .join(        Customer,        onclause=(Customer.id == Order.customer_id),    )\    .where(        Order.id == order_id,    )\    .group_by(        Order.id,    )\    .order_by(        Product.id.desc(),    )

Этим примером кода я хочу сказать, что ORM не пытается изобрести свои критерии, вместо этого она пытается дать нечто, максимально похожее на SQL. К сожалению, я заменил реальный фрагмент ORM-запроса текущего проекта, ибо NDA. Пример крайне примитивен -- он даже без подзапросов. Кажется, в моём текущем проекте таких запросов единицы.

Естественно, я сразу стал искать, как тут дела с составными первичными ключами -- и они есть! И оконные функции, и CTE, и явный JOIN, и много чего ещё! Для особо тяжёлых случаев можно даже впердолить SQL хинты! Дальнейшее погружение продолжает радовать: я не сталкивался ни с одним вопросом, который решить было невозможно из-за архитектурных ограничений. Правда, некоторые свои вопросы я решал через monkey-patching.

Производительность

Насколько крутым и гибким бы ни было API, краеугольным камнем является вопрос производительности. Сегодня вам может и хватит 10 rps, а завтра вы пытаетесь масштабироваться, и если затык в БД -- поздравляю, вы мертвы.

Производительность query builder в SQLAlchemy оставляет желать лучшего. Благо, это уровень приложения, и тут масштабирование вас спасёт. Но можно ли это как-то обойти? Можно ли как-то нивелировать низкую производительность query builder? Нет, серьёзно, какой смысл тратить мощности ради увеличения энтропии Вселенной?

В принципе, нам на python не привыкать искать обходные пути: например, python непригоден для реализации числодробилок, поэтому вычисления принято выкидывать в сишные либы.

Для SQLAlchemy тоже есть обходные пути, и их сразу два, и оба сводятся к кэшированию по разным стратегиям. Первый -- применение bindparam и lru_cache. Второй предлагает документация -- future_select. Рассмотрим их преимущества и недостатки.

bindparam + lru_cache

Это самое простое и при этом самое производительное решение. Мы покупаем производительность по цене памяти -- просто кэшируем собранный объект запроса, который в себе кэширует отрендеренный запрос. Это выгодно до тех пор, пока нам не грозит комбинаторный взрыв, то есть пока число вариаций запроса находится в разумных пределах. В своём проекте в большинстве представлений я использую именно этот подход. Для удобства я применяю декоратор cached_classmethod, реализующий композицию декораторов classmethod и lru_cache:

from functools import lru_cachedef cached_classmethod(target):    cache = lru_cache(maxsize=None)    cached = cache(target)    cached = classmethod(cached)    return cached

Для статических представлений тут всё понятно -- функция, создающая ORM-запрос не должна принимать параметров. Для динамических представлений можно добавить аргументы функции. Так как lru_cache под капотом использует dict, аргументы должны быть хешируемыми. Я остановился на варианте, когда функция-обработчик запроса генерирует "сводку" запроса и параметры, передаваемые в сгенерированный запрос во время непосредственно исполнения. "Сводка" запроса реализует что-то типа плана ORM-запроса, на основании которой генерируется сам объект запроса -- это хешируемый инстанс frozenset, который в моём примере называется query_params:

class BaseViewMixin:    def build_query_plan(self):        self.query_kwargs = {}        self.query_params = frozenset()    async def main(self):        self.build_query_plan()        query = self.query(self.query_params)        async with BaseModel.session() as session:            respone = await session.execute(                query,                self.query_kwargs,            )            mappings = respone.mappings()        return self.serialize(mappings)

На всякий случай предупреждаю: не стоит слепо копировать код. Разберитесь с ним, адаптируйте под свой проект. Даже у меня данный код на самом деле выглядит немного иначе, он намеренно упрощён, из него выкинуты некоторые несущественные детали.

Сколько же памяти я заплатил за это? А немного. На все вариации запросов я расходую не более мегабайта.

future_select

В отличие от дубового первого варианта, future_select кэширует куски SQL-запросов, из которых итоговый запрос собирается очень быстро. Всем хорош вариант: и высокая производительность, и низкое потребление памяти. Читать такой код сложно, сопровождать дико:

stmt = lambdas.lambda_stmt(lambda: future_select(Customer))stmt += lambda s: s.where(Customer.id == id_)

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

class FutureSelectWrapper:    def __init__(self, clause):        self.stmt = lambdas.lambda_stmt(            lambda: future_select(clause)        )        def __getattribute__(self, name):        def outer(clause):            def inner(s):                callback = getattr(s, name)                return callback(clause)                        self.stmt += inner            return self        return outer

Промежуточный вывод: низкую производительность query builder в SQLAlchemy можно нивелировать кэшем запросов. Дикий синтаксис future_select можно спрятать за фасадом.

А ещё я не уделил должного внимания prepared statements. Эти исследования я проведу чуть позже.

Как я открывал для себя ORM заново

Мы добрались главного -- ради этого раздела я писал статью. В этом разделе я поделюсь своими откровениями, посетившими меня в процессе работы.

Модульность

Когда я реализовывал на SQL дикую аналитику, старой болью отозвалось отсутствие модульности и интроспекции. При последующем переносе на ORM у меня уже была возможность выкинуть весь подзапрос поля FROM в отдельную функцию (по факту метод класса), а в последующем эти функции было легко комбинировать и на основании флагов реализовывать паттерн Стратегия, а также исключать дублирование одинакового функционала через наследование.

Собственные типы

Если данные обладают хитрым поведением, или же хитро преобразуются, совершенно очевидно, что их надо выкинуть на уровень модели. Я столкнулся с двумя вопросами: хранение цвета и работа с ENUM. Погнали по порядку.

Создание собственных простых типов рассмотрено в документации:

class ColorType(TypeDecorator):    impl = Integer    cache_ok = True    def process_result_value(self, value, dialect):        if value is None:            return        return color(value)    def process_bind_param(self, value, dialect):        if value is None:            return        value = color(value)        return value.value

Сыр-бор тут только в том, что мне стрельнуло хранить цвета не строками, а интами. Это исключает некорректность данных, но усложняет их сериализацию и десериализацию.

Теперь про ENUM. Меня категорически не устроило, что документация предлагает хранить ENUM в базе в виде VARCHAR. Особенно уникальные целочисленные Enum хотелось хранить интами. Очевидно, объявлять этот тип мы должны, передавая аргументом целевой Enum. Ну раз String при объявлении требует указать длину -- задача, очевидно, уже решена. Штудирование исходников вывело меня на TypeEngine -- и тут вместо примеров использования вас встречает "our source code is open 24/7". Но тут всё просто:

class IntEnumField(TypeEngine):    def __init__(self, target_enum):        self.target_enum = target_enum        self.value2member_map = target_enum._value2member_map_        self.member_map = target_enum._member_map_    def get_dbapi_type(self, dbapi):        return dbapi.NUMBER    def result_processor(self, dialect, coltype):        def process(value):            if value is None:                return            member = self.value2member_map[value]            return member.name        return process    def bind_processor(self, dialect):        def process(value):            if value is None:                return            member = self.member_map[value]            return member.value        return process

Обратите внимание: обе функции -- result_processor и bind_processor -- должны вернуть функцию.

Собственные функции, тайп-хинты и вывод типов

Дальше больше. Я столкнулся со странностями реализации json_arrayagg в mariadb: в случае пустого множества вместо NULL возвращается строка "[NULL]" -- что ни под каким соусом не айс. Как временное решение я накостылил связку из group_concat, coalesce и concat. В принципе неплохо, но:

1. При вычитывании результата хочется нативного преобразования строки в JSON.

2. Если делать что-то универсальное, то оказывается, что строки надо экранировать. Благо, есть встроенная функция json_quote. Про которую SQLAlchemy не знает.

3. А ещё хочется найти workaround-функции в объекте sqlalchemy.func

Оказывается, в SQLAlchemy эти проблемы решаются совсем влёгкую. И если тайп-хинты мне показались просто удобными, то вывод типов поверг меня в восторг: типозависимое поведение можно инкапсулировать в саму функцию, что сгенерирует правильный код на SQL.

from sqlalchemy.sql.functions import GenericFunction, register_functionfrom sqlalchemy.sql import sqltypesfrom sqlalchemy import func, literal_columndef register(target):    name = target.__name__    register_function(name, target)    return target# === Database functions ===class json_quote(GenericFunction):    type = sqltypes.String    inherit_cache = Trueclass json_object(GenericFunction):    type = sqltypes.JSON    inherit_cache = True# === Macro ===empty_string = literal_column("''", type_=sqltypes.String)json_array_open = literal_column("'['", type_=sqltypes.String)json_array_close = literal_column("']'", type_=sqltypes.String)@registerdef json_arrayagg_workaround(clause):    clause_type = clause.type    if isinstance(clause_type, sqltypes.String):        clause = func.json_quote(clause)    clause = func.group_concat(clause)    clause = func.coalesce(clause, empty_string)    return func.concat(        json_array_open,        clause,        json_array_close,        type_=sqltypes.JSON,    )def __json_pairs_iter(clauses):    for clause in clauses:        clause_name = clause.name        clause_name = "'%s'" % clause_name        yield literal_column(clause_name, type_=sqltypes.String)        yield clause@registerdef json_object_wrapper(*clauses):    json_pairs = __json_pairs_iter(clauses)    return func.json_object(*json_pairs)

В рамках эксперимента я также написал функцию json_object_wrapper, которая из переданных полей собирает json, где ключи -- это имена полей. Буду использовать или нет -- ХЗ. Причём тот факт, что эти макроподстановки не просто работают, а даже правильно, меня немного пугает.

SELECT concat(  '[',  coalesce(group_concat(product.tag_id), ''),  ']') AS product_tags

SELECT json_object(  'name', product.name,  'price', product.price) AS product,

Такие макроподстановки позволяют сгенерировать огромную кучу SQL кода, который будет выполнять логику формирования представлений. И что меня восхищает -- эта куча кода работает эффективно. Ещё интересный момент -- эти макроподстановки позволят прозрачно реализовать паттерн Стратегия -- я надеюсь, поведение json_arrayagg пофиксят в следующих релизах MariaDB, и тогда я смогу своё костылище заменить на связку json_arrayagg+coalesce незаметно для клиентского кода.

Выводы

SQLAlchemy позволяет использовать преимущества наследования и полиморфизма (и даже немного иннкапсуляции. Флеш-рояль, однако) в SQL. При этом она не загоняет вас в рамки задач уровня Hello, World! архитектурными ограничениями, а наоборот даёт вам максимум возможностей.

Субъективно это прорыв. Я обожаю реляционные базочки, и наконец-то я получаю удовольствие от реализации хитрозакрученной аналитики. У меня в руках все преимущества ООП и все возможности SQL.

В предыдущей статье я описал векторные языки и их ключевые отличия от обычных языков. На коротких примерах я постарался показать, как эти особенности позволяют реализовывать алгоритмы необычным образом, кратко и с высоким уровнем абстракции. В силу своей векторной природы такие языки идеально присоблены для обработки больших данных, и в качестве доказательства в этой статье я полностью реализую на векторном языке простой SQL интерпретатор. А чтобы продемонстрировать, что векторный подход можно использовать на любом языке, я реализую тот же самый интерпретатор на Rust. Преимущества векторного подхода столь велики, что даже интерпретатор в интерпретаторе сможет обработать select с группировкой из таблицы в 100 миллионов строк за 20 с небольшим секунд.

Общий план.

Конечная цель - реализовать интепретатор, способный выполнять выражения типа:

select * from (select sym,size,count(*),avg(price) into r  from bt where price>10.0 and sym='fb'  group by sym,size)  as t1 join ref on t1.sym=ref.sym, t1.size = ref.size

Т.е. он должен поддерживать основные функции типа сложения и сравнения, позволять where и group by выражения, а также - inner join по нескольким колонкам.

В качестве векторного языка я возьму Q, так как его специализация - базы данных.

Интерпретатор будет состоять из лексера, парсера и собственно интерпретатора. Для экономии места я буду приводить только ключевые места, а весь код можно найти здесь. Так же для краткости я реализую лишь часть функциональности, но так, чтобы все важное было на месте: join, where, group by, 3 типа данных, агрегирующие функции и т.п.

На примере лексера вы сможете познакомиться, пожалуй, с одним из самых красивых приемов программирования, который встречается в векторных языках, - использование данных как функций.

Парсеры оперируют деревьями и векторность тут не дает никаких преимуществ. Поэтому в этой части я сделаю упор на Rust и на то, как на нем можно реализовать генератор функциональных рекурсивно-нисходящих парсеров. Несмотря на свою второстепенность, эта часть занимает большое количество места, поэтому я перенес ее в конец, чтобы не утомлять читателей.

Наконец, сам интерпретатор. На Q он имеет весьма скромные размеры - строк 25. На Rust, конечно, гораздо больше, но написать его проще, чем может показаться. Нужно просто по шагам повторять все операции Q, адаптируя их к специфике Rust.

Это моя первая программа на Rust и сразу хочу сказать, что слухи о его сложности сильно преувеличены. Если писать в функциональном стиле (read only), то проблем нет никаких. После того, как Rust несколько раз забраковал мои идеи, я понял, чего он хочет и уже не сталкивался с необходимостью все переделывать из-за контроллера ссылок, а явные времена жизни понадобились только один раз и по делу. Зато взамен мы получаем программу, которую можно распараллелить по щелчку пальцев. Что мы и сделаем, чтобы добиться просто феноменальной производительности для столь примитивной программы.

В завершение о производительности. Векторный подход настолько крут, что без разницы - пишите вы на интерпретируемом языке или на Rust, все равно всю работу делают векторные операции. Поэтому интерпретатор на Q работает всего в полтора раза медленнее самого Q на аналогичных данных.

Лексер

Векторные языки идеальны для написания лексеров. Пусть у нас есть функция fsa, которая принимает на вход текущее состояние лексера и входной символ и возвращает новое состояние:

fsa[state;char] -> state

Если мы применим эту функцию последовательно ко всей входной строке, то получим на выходе последовательность состояний лексера, т.е. каждому символу будет поставлено в соответствие состояние лексера. Все, что нам остается, это найти индексы особых граничных состояний и разрезать по ним входную строку на части. Схему работы лексера можно изобразить наглядно:

Т.е. есть следующие этапы:

• Кодирование. Входные символы отображаются в группы (my.var -> aa.aaa, 12.01 -> 00.00, "str 1" -> "sss 1" и т.д.).

• Трансформация. Закодированные символы пропускаются через fsa (aa.aaa -> aAAAAA, 00.00 -> 0IFFF, "sss 1" -> "SSSSSR).

• Разбиваем начальную строку на части по начальным состояниям (a, 0, " и т.д.). Для удобства все не начальные состояния обозначены большими буквами.

Все три этапа - это векторные операции, поэтому на Q эта идея реализуется одной строкой (все состояния закодированы так, что начальные меньше limit):

(where fsa\[start;input]<limit)cut input

Это в сущности весь лексер. Правда еще необходимо определить fsa. В подавляющем большинстве случаев в качестве fsa можно взять матрицу - таблицу переходов конечного автомата. Простой автомат можно задать и руками, но можно реализовать небольшой DSL. Отображение в группы можно организовать через небольшой массив (ограничимся ASCII символами для простоты):

cgrp: ("\t \r\n";"0..9";"a..zA..Z"),"\\\"=<>.'";c2grp: 128#0; // массив [0;128]// Q позволяет присваивать значения по индексу любой формы.// В данном случае массиву массивов. В Rust необходимы два явных цикла:// cgrp.iter().enumerate().for_each(|(i,&s)| s.iter()//   .for_each(|&s| c2grp[s as usize] = i + 1));c2grp[`int$cgrp]: 1+til count cgrp;

Для краткости я не привожу все цифры и буквы. Нас интересуют пробельные символы, цифры, буквы, а также несколько специальных символов. Мы закодируем эти группы числами 1, 2 и т.д., все остальные символы поместим в группу 0. Чтобы закодировать входную строку, достаточно взять индекс в массиве c2grp:

c2grp `int$string

Автомат задается правилами (текущее состояние(я);группа(ы) символов) -> новое состояние. Для обозначения групп и начальных состояний токенов удобно использовать первые символы соответствующих групп (для группы 0..9 - 0, например). Для обозначения промежуточных состояний - большие буквы. Например, правило для имен можно записать так:

aA А a0.

т.е. если автомат находится в состояниях "a" (начало имени) или "A" (внутри имени), и на вход поступают символы из групп [a,0,.], то автомат остается в состоянии "A". В начальное состояние "a" автомат попадет автоматически, когда встретит букву (это правило действует по умолчанию). После этого, если дальше он встретит букву, цифру или точку, то перейдет во внутреннее состояние "A" и будет там оставаться до тех пор, пока не встретит какой-то другой символ. Я запишу все правила без лишних комментариев (Rust):

let rules: [&[u8];21] =  [b"aA A a0.",                         // имена   b"0I I 0",b"0I F .",b"F F 0",        // int/float   b"= E =",b"> E =",b"< E =",b"< E >", // <>, >= и т.п.   b"\" S *",b"S S *",b"\"S R \"",      // "str"   b"' U *",b"U U *",b"'U V '",         // 'str'   b"\tW W \t"];                        // пробельные символы

Числа и строки заданы в упрощенном формате. "*" в качестве входного символа имеет специальное значение - все возможные символы. Большие буквы - это состояния внутри токенов. Такая договоренность дает нам возможность легко определить начало токена - это все состояния, которые не большие буквы.

Матрица fsa из этих правил генерируется элементарно. Схематично это выглядит так:

fsa[*;y] = y (по умолчанию для всех состояний)"aA A a0." -> "aA","A","a0."; fsa[enc["aA"];enc["a0."]] = enc["A"]...

Необходимо закодировать символы с помощью вектора states:

states: distinct " ",(first each cgrp),raze fsa[;1];limit: 2+count cgrp;enc:states?; // в Q encode - это поиск индекса элемента в векторе

Вперед поместим все начальные состояния (пробел для учета группы 0), чтобы можно было легко определить limit.

Код генерации fsa я опускаю - он следует схеме выше.

Матрица переходов у нас есть, осталось определить саму функцию lex. В парсере нам понадобится знать тип токена, поэтому вычислим и его тоже. На Rust лексер выглядит так:

let s2n = move |v| ["ID","NUM","STR","STR","WS","OTHER"][find1(&stn,&v)];move |s| {    if s.len()==0 {return Vec::<Token>::new()};    let mut sti = 0usize;    let st: Vec<usize> = s.as_bytes().iter().map(|b| { // st:fsa\[0;c2grp x]        sti = fsa[sti][c2grp[std::cmp::min(*b as usize,127)]];        sti}).collect();    let mut ix: Vec<usize> = st.iter().enumerate() // ix:where st<sta        .filter_map(|(i,v)| if *v<sta {Some(i)} else {None}).collect();    ix.push(st.len());    (0..ix.len()-1).into_iter()        .filter_map(|i|            match s2n(st[ix[i]]) {                 "WS" => None,                  kind => Some(Token{ str:&s[ix[i]..ix[i+1]], kind})             }).collect()

На Q получится значительно более кратко:

s2n:(states?"a0\"'\t")!("ID";"NUM";"STR";"STR";"WS");lex:{  i:where (st:fsa\[0;c2grp x])<limit;  {x[;where not "WS"~/: x 0]} (s2n st i;i cut x)};

Если мы запустим лексер, то получим:

lex "10 + a0" -> (("NUM";"";"ID");("10";"+";"a0"))

Интерпретатор

Интерпретатор можно разделить на две части - выполнение выражений и выполнение select. Первая часть тривиальна на Q, но требует большого количества кода на Rust. Я приведу основные структуры данных, чтобы было понятно, как в целом работает интерпретатор. В основе лежит enum Val:

type RVal=Arc<Val>;enum Val {       I(i64),    D(f64),    II(Vec<i64>),    DD(Vec<f64>),    S(Arc<String>),    SS(Vec<Arc<String>>),    TBL(Dict<RVal>),    ERR(String),}

Есть три типа данных - строки, целые и нецелые, две формы их представления - атомарная и вектор. Также есть таблицы и ошибки. Dict - это пара Vec<String> и Vec<T> одинаковой длины. В случае таблицы T = Vec<RVal>, где каждый Val - это II, DD или SS. Rust позволяет в легкую распаралелливать программу, но нужно, чтобы типы данных позволяли передавать свои значения между потоками. Для этого я обернул все разделяемые значения в асинхронный счетчик ссылок Arc. Считается, что атомарные операции более медленные, однако в программе, которая работает с большими данными, это не имеет большого значения.

Интерпретатор работает с выражениями:

enum Expr {    Empty,    F1(fn (RVal) -> RRVal, Box<Expr>), // f(x)    F2(fn (RVal,RVal) -> RRVal, Box<Expr>, Box<Expr>), // f(x,y)    ELst(Vec<Expr>),    ID(String),  // variable/column    Val(Val),    // simple value - 10, "str"    Set(String,Box<Expr>), // 'set var expr' - assignment    Sel(Sel), // select    Tbl(Vec<String>,Vec<Expr>), // [c1 expr1, c2 expr2] - create table }

ELst и Empty используются только парсером. Expr (ссылки на себя) необходимо хранить в куче (Box). Выполняются выражения функцией eval в некотором контексте, где заданы переменные (Set), а также могут быть определены колонки таблицы:

struct ECtx {    ctx: HashMap<String,Arc<Val>>,   // variables}struct SCtx {    tbl: Arc<Table>,                // within select    idx: Option<Vec<usize>>,        // idx into tbl    grp: Arc<Vec<String>>,          // group by cols}

eval сравнительно проста (self = ECtx):

type RRVal=Result<Arc<Val>,String>;fn top_eval(&mut self, e: Expr) -> RRVal {    match e {        Expr::Set(id,e) => {            let v = self.eval(*e, None)?;            self.ctx.insert(id,v.clone()); Ok(v)},        Expr::Sel(s) => self.do_sel(s),        _ => self.eval(e, None)    }}fn eval(&self, e: Expr, sctx:Option<&SCtx>) -> RRVal {    match e {        Expr::ID(id) => self.resolve_name(sctx,&id),        Expr::Val(v) => Ok(v.into()),        Expr::F1(f,e) => Ok(f(self.eval(*e,sctx)?)?),        Expr::F2(f,e1,e2) => Ok(f(self.eval(*e1,sctx)?,self.eval(*e2,sctx)?)?),        Expr::Tbl(n,e) => { self.eval_table(None,n,e) }        e => Err(format!("unexpected expr {:?}",e))    }}

Set и Sel нужен модифицируемый контекст, а его нельзя будет передать просто так в другой поток. Поэтому eval разбит на две части. Задача resolve_name - найти переменную или колонку и при необходимости применить where индекс. eval_table - собрать таблицу из частей и проверить, что с ней все в порядке (колонки одной длины и т.п.). Функции F1 (max, count ...) и F2 (+, >=, ...) сводятся к огромным match блокам, где для каждого типа прописывается нужная операция. Макросы позволяют уменьшить количество кода. Например, для арифметических операций часть match выглядит так:

(Val::D(i1),Val::I(i2)) => Ok(Val::D($op(*i1,*i2 as f64)).into()),(Val::D(i1),Val::D(i2)) => Ok(Val::D($op(*i1,*i2)).into()),(Val::I(i1),Val::II(i2)) => Ok(Val::II(i2.par_iter()    .map(|v| $op(*i1,*v)).collect()).into()),

Это может показаться неоптимальным, но когда вы обрабатываете таблицу на 100 миллионов строк, это не имеет ни малейшего значения. Видите слово par_iter выше? Это все, что нужно сделать в Rust, чтобы операция стала параллельной.

Выполнение select гораздо интереснее и сложнее. Разберем его на Q, потому что код на Rust многословно повторяет код на Q, который и сам по себе непростой.

Select состоит из подвыражений (join, where, group, select, distinct, into), каждое из которых выполняется отдельно. Самое сложное из них - join. В его основе лежит функция rename, задача которой присвоить колонкам уникальные имена, чтобы не возникло конфликта при join:

// если x это name -> найти, select -> выполнитьsget:{[x] $[10=type x;get x;sel1 x]};// в грамматике таблица определяется как '(ID|sel) ("as" ID)?'// так что x это список из 2 элементов: (ID из as или имя таблицы;ID/select)// y - уникальный префиксrename:{[x;y]  t:sget x 1; // получить таблицу: names!vals  k:(k!v),(n,/:k)!v:(y,n:x[0],"."),/:k:key t; // k - оригинальные имена,        // v - уникальные, n - с префиксом (table.name)  (k;v!value t)};

Все эти манипуляции сводятся к построению двух словарей - отображения из настоящих имен колонок и расширенных (table.name) в уникальные и из уникальных имен в сами колонки таблицы. Уникальные имена позволяют иметь в одной join таблице колонки с одинаковыми именами из разных таблиц и обращаться к ним в выражениях через нотацию с точкой.

В основе join следующая функция:

// x - текущая таблица в формате rename// y - следующая таблица в этом формате// z - join выражение, список (колонка в x;и в y)// условие join: x[z[i;0]]==y[z[i;1]] для всех ijoin_core:{[x;y;z]  // m - отображение имен в уникальные для новой таблицы x+y  // имена из x имеют приоритет  // c - переименовываем join колонки в уникальные имена  c:(m:y[0],x 0)z;  // после join z[;0] и z[;1] колонки будут одинаковыми  // поэтому колонки из y перенаправим на x  m[z[;1]]:c[;0];  // x[1]c[;0] - просто join колонки из таблицы x (подтаблица)  // y[1]c[;1] - симметрично из y  // sij[xval;yval] -> (idx1;idx2) найти индексы join в обеих таблицах  // sidx[(i1;i2);x;y без join колонок] -  //  собрать новую таблицу из x и y и индексов  (m;sidx[sij[x[1]c[;0];y[1]c[;1]];x 1;c[;1]_ y 1])}// sidx просто применяет индексы ко всем колонкам и объединяет y и z// y z - это словари, но поскольку традиционно векторные функции имеют// максимально широкую область определения, не нужно обращаться явно к value sidx:{(y@\:x 0),z@\:x 1};

Вся работа выполняется в функции sij, все остальное - это манипуляции именами, колонками и индексами. Если бы мы захотели добавить другой тип индекса, достаточно было бы написать еще одну sij. Конечно, функция выглядит непросто, но учитывая, что она покрывает 80% select, ей можно это простить.

Функция sij сводится к поиску строк таблицы x в таблице y. В Rust для этих целей можно использовать HashMap с быстрой hash функцией FNV - поместить в Map одну таблицу и потом искать в ней строки второй. В Q, судя по времени выполнения, скорее всего используется что-то подобное. В целом в Q у нас есть два варианта - использовать векторные примитивы или воспользоваться встроенными возможностями связанными с таблицами. В первом варианте все по-честному:

// x и y - списки колонокsij:{j:where count[y 0]>i:$[1=count x;y[0]?x 0;flip[y]?flip x]; (j;i j)};// или на псевдокоде// i=find_idx[tblY;tblX]; j=i where not null i; return (j,i[j])

используем функцию поиска значения в векторе (?) и транспозиции матрицы (flip). Этот вариант не такой медленный как может показаться - всего в 2.5 раза медленнее, чем оптимизированный поиск сразу по таблице (который выглядит ровно также - x?y, только x и y - таблицы, а не списки векторов). Это показывает в очередной раз силу векторных примитивов.

Наконец сам join - это просто цикл свертки по всем таблицам (fold):

// "/" это fold, rename' это map(rename)sjoin:{[v] join_core/[rename[v 0;"@"];rename'[v 1;string til count v 1];v 2]};

Остальные части select гораздо проще. where:

swhere:{[t;w] i:til count value[t 1]0;  // все строки по умолчанию  $[count w;where 0<>seval[t;i;();w];i]}; // выбрать те, которые не 0// seval такой же как eval в Rust, т.е. его сигнатура:// seval[table,index;group by cols;expr], ECtx - это сам Q

Основная функция select:

sel2:{[p] // p ~ словарь с элементами select (`j, `s, `g  и т.п.)  i:swhere[tbl:sjoin p`j;p`w]; // сходу делаем join и where  if[0=count p`s; // в случае select * надо найти подходящие имена колонкам    rmap:v[vi]!key[tbl 0] vi:v?distinct v:value tbl 0;    p[`s]:{nfix[x]!x} rmap key tbl 1];  if[count p`g; // group by    // из group колонок нужен только первый элемент, нужно знать их имена    gn:nfix {$[10=type x;x;""]} each p`g;    // sgrp вернет список индексов (idx1;idx2;..) для каждой группы    // затем нужно выполнить seval[tbl;idxN;gn;exprM] для всех idx+expr    // т.е. двойной цикл, который в Q скрыт за двумя "each"    g:sgrp[tbl;i;p`g]];    :key[p`s]!flip {x[z] each y}[seval[tbl;;gn];value p`s] each g;  // если group нет, то все элементарно - просто seval для всех select выражений  (),/:seval[tbl;i;()] each p`s };

Функция sgrp в основе group by - это просто векторный примитив group, возвращающий словарь, где ключи - уникальные значения, а значения - их индексы во входном значении:

sgrp:{[t;i;g] i value group flip seval[t;i;()] each g};

Я опускаю distinct и into части, поскольку они малоинтересны. В целом - это весь select на Q. В краткой записи он занимает всего 25 строк. Можно ли ждать хоть какой-то производительности от столь скромной программы? Да, потому что она написана на векторном языке!

Производительность

Напомню, что этот игрушечный интерпретатор может выполнять выражения типа

select * from (select sym,size,count(*),avg(price) into r  from bt where price>10.0 and sym='fb'  group by sym,size)  as t1 join ref on t1.sym=ref.sym, t1.size = ref.size

и при этом справляться с таблицами в сотни миллионов строк. В частности таблица bt в выражении выше сгенерирована выражением:

// в интерпретаторе на Rust// s = ("apple";"msft";"ibm";"bp";"gazp";"google";"fb";"abc")// i/f - i64/f64 интервалы [0-100)set bt [sym rand('s',100000000), size rand('i', 100000000),    price rand('f', 100000000)]

Т.е. содержит 100 миллионов строк. Поначалу базовый select с group by (получается 800 групп по ~125000 элементов)

select sym,size,count(*),avg(price) into r from bt group by sym,size

работал 44 секунды в программе на Rust, что совсем неплохо и само по себе. Однако после некоторых оптимизаций, а главное распаралелливания ключевых операций, мне удалось добиться скорости порядка 6 секунд. На мой взгляд весьма хороший результат для подобной таблицы.

Самое главное, программа на Rust, несмотря на свой внушительный вид, - это почти 1 в 1 программа на Q. Поэтому больших интеллектуальных усилий и даже отладки она не потребовала. Также благодаря векторности изначального языка ее ускорение путем распараллеливания не потребовало почти никаких усилий - если все операции изначально над массивами, то все что нужно - это вставить там и тут par_iter вместо iter.

Интерпретатор на Q не столь быстр, но вышеуказанный select всего на 50% медленнее аналогичного запроса на самом Q. Это значит, что по сути Q работает на больших данных почти также быстро, как и программа на компилируемом языке.

Хочу также отметить то, насколько великолепным языком проявил себя Rust. За все время разработки и отладки я не получил ни одного segfault и даже panic увидел всего несколько раз, и почти все это были простые ошибки выхода за пределы массива. Также поражает, насколько легко и безопасно в нем можно распараллелить задачу.

Парсер

Я отложил парсер на конец, поскольку он довольно объемен и не имеет прямого отношения к теме статьи. Тем не менее, может вам будет интересно ознакомиться с тем, как легко можно написать весьма серьезный парсер в функциональном стиле на Rust.

Это рекурсивный нисходящий парсер без заглядывания вперед. Из-за этого он не может предсказать следующий шаг и вынужден обходить все варианты. Такой парсер, конечно, медленный и не годится для серьезных задач, но для SQL выражений больше и не надо. Какая разница, парсится выражение 1 микросекунду или 10, если сам запрос займет минимум сотни микросекунд.

Такие парсеры часто пишут руками, и выглядят они примерно так:

parse_expr1(..) {   if(success(parse_expr2(..)) {    if (success(parse_str("+") || success(parse_str("-")) {      if(success(parse_expr1(..)) {         return <expr operation expr>      }      return Fail    }    return <expr>  }  return Fail;}

Главная идея предлагаемого парсера в том, что нет смысла писать это все руками, можно написать генератор подобных парсеров из BNF-подобной формы. Для всех сущностей BNF пишем по функции, затем генерируем из описания грамматики в виде строк набор парсящих функций, и все готово. В Rust, как строго типизированном языке, есть нюансы. В первую очередь определим типы для парсящих и post process функций:

type ParseFn = Box<dyn Fn(&PCtx,&[Token],usize) -> Option<(Expr,usize)>>;type PPFn = fn(Vec<Expr>) -> Expr;

ParseFn будет захватывать правила грамматики, поэтому она должна быть замыканием (closure) и лежать в куче. PCtx содержит другие ParseFn для рекурсивных вызовов и PPFn для постобработки дерева. Если парсинг не удался, она возвращает None, иначе Some с выражением и новым индексом в массив токенов. PPFn обрабатывает узел дерева, поэтому принимает безликий список выражений и превращает его в нужное выражение.

Для понимания процесса приведу часть грамматики, касающуюся выражений:

("expr", "expr1 ('or' expr {lst})? {f2}"),("expr1","'not' expr1 {f1} | expr2 ('and' expr1 {lst})? {f2}"),("expr2","expr3 (('='|'<>'|'<='|'>='|'>'|'<') expr2 {lst})? {f2}"),("expr3","expr4 (('+'|'-') expr3 {lst})? {f2}"),("expr4","vexpr (('*'|'/') expr4 {lst})? {f2}"),("vexpr","'(' expr ')' {2} | '-' expr {f1} | call | ID | STR | NUM |  '[' (telst (',' telst)* {conc}) ']' {tblv}"),("call", "('sum'|'avg'|'count'|'min'|'max') '(' expr ')' {call} |  'count' '(' '*' ')' {cnt} | 'rand' '(' STR ',' NUM ')' {rand}"),

Тут видны ключевые части - имя правила, само правило и PP функции в фигурных скобках. Каждая продукция правила должна заканчиваться на PP функцию, поскольку правило возвращает Expr, а не Vec<Expr>. PP функция по умолчанию возвращает последний элемент вектора, поэтому кое-где PP функций нет. ID, NUM и т.п. должны обрабатываться ParseFn функцией с соответствующим именем.

Генерируется наш парсер с помощью следующей функции:

let parse = |str| {    let t = l(str);  // add ({}) depth map    let mut lvl = 0;    pp_or(&t.into_iter().map(|v| {      match v.str.as_bytes()[0] {        b'(' | b'{' => lvl+=1,        b')' | b'}' => lvl-=1,        _ => ()};      (v,std::cmp::max(0,lvl))}).collect::<Vec<(Token,i32)>>()    , 0)};

l - это лексер, я переиспользую для этого лексер SQL. Нужно добавить карту глубины вложенности скобок, чтобы было удобно выделять BNF подвыражения. Поскольку это парсер для внутренних потребностей, то нет необходимости проверять правильность скобок, беспокоиться о глубине рекурсии и т.п.

Далее наше правило поступает в парсер BNF. Нужно реализовать следующие компоненты:

• or правило - A | B

• and правило - A B C

• const правило - "(", "select".

• token правило - NUM, STR.

• subrule правило - expr1, call.

• optional правило - A?

• 0+ правило - A*

• 1+ правило - A+

• PP правило - {ppfn}

Это работа, требующая тщательности, но проделать ее нужно один раз. Например, or правило:

fn pp_or(t: &[(Token,i32)], lvl:i32) -> ParseFn {    if t.len() == 0 {return Box::new(|_,_,i| Some((Expr::Empty,i)))};    let mut r: Vec<ParseFn> = t      .split(|(v,i)| *i == lvl && v.str.as_bytes()[0] == b'|' )      .map(|v| pp_and(v,lvl)).collect();    if 1 == r.len() {        r.pop().unwrap()    } else {        Box::new(move |ctx,toks,idx|          r.iter().find_map(|f| f(ctx,toks,idx)))    }}

Функция должна вернуть ParseFn замыкание. В общем случае, когда pp_and вернула несколько ParseFn, нужно организовать цикл и выполнять подфункции, пока одна из них не вернет Some.

pp_and работает аналогично, только все ее подфункции должны вернуть Some. Также в случае успеха она должна вызвать нужную PPFn для обработки результата.

fn pp_and(t: &[(Token,i32)], lvl:i32) -> ParseFn {    if t.len() == 0 {return Box::new(|_,_,i| Some((Expr::Empty,i)))};    let (rules,usr) = pp_val(Vec::<ParseFn>::new(),t,lvl);    Box::new(move |ctx,toks,i| {        let mut j = i;        let mut v = Vec::<Expr>::with_capacity(rules.len());        for r0 in &rules {        if let Some((v0,j0)) = r0(ctx,toks,j) {            j = j0; v.push(v0)            } else {return None} };        Some((ctx.ppfns[&usr](v),j))    })}

pp_val рекурсивно обрабатывает круглые скобки и все базовые выражения. Вот некоторые примеры из нее:

// Token - if ok call rules[Token]move |ctx,tok,i| if i<tok.len() && tok[i].kind == s   {ctx.rules[&s](ctx,tok,i)} else {None}// Subrulemove |ctx,tok,i| ctx.rules[&s](ctx,tok,i))}// rule?move |ctx,tok,i| Some(rule(ctx,tok,i).unwrap_or((Expr::Empty,i)))// rule+move |ctx,tok,i| {    let (e,i) = plst(&rule,ctx,tok,i);    if 0<e.len() {Some((Expr::ELst(e),i))} else {None}}// где plstlet mut j = i; let mut v:Vec<Expr> = Vec::new();while let Some((e,i)) = rule(ctx,tok,j) {j=i; v.push(e)};(v,j)

Это весь код, необходимый для создания парсера. Чтобы его сгенерировать, нужно вызвать parse и положить правило в map:

let mut map = HashMap::new();map.insert("expr".to_string(), parse("expr1 ('or' expr {lst})? {f2}"));...  

Также необходимо определить PP функции. В большинстве случаев они сравнительно просты:

let mut pfn: HashMap<String,PPFn> = HashMap::new();// default rulepfn.insert("default".to_string(),|mut e| e.pop().unwrap());// set name expr выражениеpfn.insert("set".to_string(),|mut e| Expr::Set(e.swap_remove(1).as_id(),  e.pop().unwrap().into()) );

В Rust нельзя просто взять элемент из массива, поэтому необходимы функции типа swap_remove, которые делают это безопасно.

Наконец, положим правила в специальную структуру и определим для нее функцию parse:

PCtx { rules:map, ppfns:pfn}...impl PCtx {    fn parse(&self, t:&[Token]) -> Expr {        if let Some((e,i)) = self.rules["top"](&self,t,0) {            if i == t.len() {e}              else {Val::ERR("parse error".into()).into()}        } else {Val::ERR("parse error".into()).into()}    }}

Все, парсер готов. Он не быстр, но зато очень прост. Плюс он полностью динамический - можно менять правила во время выполнения программы, например, отключить какие-то возможности.

Исходные данные:

1. Два SQL Server'а, которые находятся в прямой доступности между собой, на одном из которых настроен Linked Server.

2. SQL запрос вида:

insert into LocalDatabaseName.dbo.TableName (column1, column2, ..., columnN)select column1, column2, ..., columnNfrom LinkedServerName.RemoteDatabaseName.dbo.TableName

Задача: максимально быстро скопировать записи с одного сервера на другой

Столкнулся с тем, что подобный запрос выполняется на 40k (40000) записей больше минуты. С ростом количества подобных запросов или количества записей, производительность сильно падает и оптимизировать запрос средствами SQL никак нельзя. С использованием приложения ImportExportDataSql мне удалось ускорить этот запрос до 2 секунд, не используя Linked Server.

Приложение ImportExportDataSql создавал для себя и постоянно его дорабатывал на протяжении нескольких лет. Основные требования при создании приложения - портативность, работа под всеми версиями Windows без установки сторонних библиотек (кроме NET Framework 3.5), простой интерфейс и высокая производительность.

ImportExportDataSql - универсальный конвертер данных, как альтернатива "bcp"

При работе с данными очень часто требуется загружать файлы из разных файлов в БД (чаще всего CSV и Excel) и обратно (из БД в CSV). До этого пользовался утилитой bcp, но всегда не хватало графического интерфейса. Кроме этого у "bcp", есть недостатки, описанные в моей предыдущей статье.

В ImportExportDataSql кроме графического интерфейса, реализована возможность работы через командную строку. Пример командной строки:

ImportExportDataSql.exe -ConnectionName="Имя соединения с БД" -TaskName="Имя Задачи 1" -TaskName="Имя задачи 2" [-Log="C:\FolderName\LogFileName.log"]

Параметры командной строки:

-ConnectionName - Имя соединения с БД, которое должно быть сохранено на форме "Соединение с БД" по кнопке "Сохранить настройку соединения с БД"

Сохранить настройку соединения с БД

-TaskName - Имя задачи из пользовательского списка задач

-Log - имя лог файла. Необязательный параметр. По-умолчанию, используется лог файл в папке Logs\UserName\ImportExportDataSql.log

Список решаемых задач в ImportExportDataSql

1. Сохранить из БД в файл - если файлы хранятся в БД и их нужно сохранить на диск

2. Сохранить из БД в файл (утилитой bcp) - если файлы хранятся в БД и их нужно сохранить на диск с помощью утилиты bcp (создается bat файл)

3. Сохранить из файла в БД - если нужно загрузить файлы с диска в таблицу БД с полем типа varbinary

4. Сохранить из БД в скрипт SQL - сохраняет результат SELECT запроса в SQL файл

5. Из БД в скрипт SQL (только INSERT)

6. Из БД в скрипт SQL (только UPDATE)

7. Статический скрипт SQL

8. Сохранить из Excel в скрипт SQL

9. Сохранить из БД в CSV

10. Сохранить из CSV в SQL

11. Сохранить из CSV в БД

12. Сохранить конфигурацию БД в SQL - выгружает структуру БД в SQL файл

13. Сохранить из БД в БД - сохраняет результат SELECT запроса на другой или текущий сервер

Все типы обработки, которые заканчиваются словом SQL могут объединяться в один файл, если имя файла одинаковое в нескольких задачах. Это очень удобно для копирования данных из одной БД в другую (например, при переносе данных с прода на тест или наоборот).

Сохранить из БД в БД

Использование данного способа позволило оптимизировать запрос (приведенный в начале статьи) копирования данных через Linked Server, сократив время выполнения с 1 минуты до 2 секунд. Алгоритм копирования данных из одной БД в другую выполнен стандартными классами языка C# из пространства имен System.Data.SqlClient: SqlConnection, SqlDataReader, SqlCommand и SqlBulkCopy.

Чтобы не возникало ошибки нехватки памяти OutOfMemoryException, чтение и запись данных выполняется блоками (частями). Блок ограничивается максимальным количеством записей, который определяется пользователем. Параметры, которые задает пользователь:

1. SQL запрос - выполняется на БД источнике, с которой нужно копировать информацию

2. Настройки выгрузки в БД назначения:

   Имя соединения - выбирается из списка соединений, которые пользователь сохраняет на форме "Соединение с БД", отображаемая при запуске приложения. Точка (.) в параметре "Имя соединения" означает, что используется текущее соединение с БД.

   Имя таблицы - в которую нужно копировать записи. Таблицу можно выбирать из списка, либо указать вручную (может содержать не только имя схемы и имя таблицы, но и имя БД)

   Номер последней обрабатываемой строки - служит для ограничения количества копируемых строк, и применяется для отладки. Например, если запрос возвращает 100 записей, а "Номер последней обрабатываемой строки" = 10, то будет скопировано только 10 первых строк из результата запроса.

   Количество строк в блоке - количество строк сохраняемых одной транзакцией

Способом "Сохранить из БД в БД" я также пользуюсь, когда необходимо скопировать результат запроса с большим количеством записей. Ограничение количества записей, в этом случае, дает преимущество, перед обычным запросом копирования записей (insert into ... select ...), так как снижается нагрузка на диск, не сильно растет журнал транзакций и не используется база tempdb (если "Количество строк в блоке" оптимальное).

Преимущества и применение ImportExportDataSql

Приложение ImportExportDataSql постоянно помогает мне в работе. С помощью него удобно переносить данные из одной БД в другую.

В коде встроено множество проверок, чтобы достаточно быстро можно было понимать на какой строке возникла ошибка при импорте CSV файла или Excel.

Можно загружать большие CSV файлы (больше 1Гб) и добавлять свои поля, которых нет в CSV. Отсекать ненужные поля из CSV, не загружая их в БД.

Скрипты при выгрузке в SQL формат дополнены различными проверками, чтобы при выполнении скрипта на другой базе все ошибки отображались в одной таблице, а не списком ошибок на панеле "Messages" в SQL Server Management Studio.

С помощью типа обработки "Сохранить конфигурацию БД в SQL" и командной строки я автоматизировал создание резервных копий джобов (jobs), репликаций и других объектов БД, чего нельзя сделать стандартными способами.

Заключение

Используя язык C# и класс SqlBulkCopy можно существенно сократить время выполнения запроса, в котором используется Linked Server.

Ссылки

Скачать ImportExportDataSql

Статья с подробным описанием ImportExportDataSql

Статья "Быстрое чтение CSV в C#", в которой рассказывается о недостатках "bcp"

Сообщество VK, для желающих пообщаться с автором

В эфире снова Радио SQL, здравствуйте, согалактчики!

Сегодня у нас обещанный разбор задачи на поиск последней цены. Прошёл как раз земляной месяц. У вас же 60 солов в месяце, да? Я немного путаюсь во всех этих ваших неметрических то 12-ти, то 60-тиричных системах времени. Впрочем, перейдём к делу.

Напомню условие, а чтобы оно не занимало много места в и без того длинной статье, спрячу его под спойлер:

with price(stock_id, prod_id, start_date, kind, price1, cost1, bonus1) as (values (1,1,to_date('2000-01-01','YYYY-MM-DD'),'R',100.0,32.12,6.49),       (1,1,'2000-01-02','P', 80.0, 0,   0),       (1,1,'2000-01-03','P', 70.0, 0,   0),       (1,1,'2000-01-04','R',110.0,33.48,6.19),       (1,1,'2000-01-05','P', 90.0, 0,   0),       (1,1,'2000-01-06','R',120.0,41.22,6.19),       (1,1,'2000-01-07','P', 80.0, 0,   0),       (1,1,'2000-01-08','P', 90.0, 0,   0),       (1,1,'2000-01-09','R', 93.0,36.87,6.49),       (1,1,'2000-01-10','R', 94.0,36.85,6.99),       (1,2,'2000-01-01','R',101.0,52.06,9.00),       (1,2,'2000-01-02','P', 81.0, 0,   0),       (1,2,'2000-01-03','P', 71.0, 0,   0),       (1,3,'2000-01-04','R',111.0,64.96,4.50),       (1,3,'2000-01-05','P', 92.0, 0,   0),       (1,3,'2000-01-06','R',122.0,66.83,4.60),       (1,3,'2000-01-07','P', 82.0, 0,   0),       (1,3,'2000-01-08','P', 92.0, 0,   0))select ...

 stock_id | prod_id | start_date | kind | price1 | cost1 | bonus1 | price1x ----------+---------+------------+------+--------+-------+--------+---------        1 |       1 | 2000-01-01 | R    |  100.0 | 32.12 |   6.49 |   100.0        1 |       1 | 2000-01-02 | P    |   80.0 |     0 |      0 |   100.0        1 |       1 | 2000-01-03 | P    |   70.0 |     0 |      0 |   100.0        1 |       1 | 2000-01-04 | R    |  110.0 | 33.48 |   6.19 |   110.0        1 |       1 | 2000-01-05 | P    |   90.0 |     0 |      0 |   110.0        1 |       1 | 2000-01-06 | R    |  120.0 | 41.22 |   6.19 |   120.0        1 |       1 | 2000-01-07 | P    |   80.0 |     0 |      0 |   120.0        1 |       1 | 2000-01-08 | P    |   90.0 |     0 |      0 |   120.0        ...

Задачу эту можно решать разными способами. Начнём с самого простого:

Первый подход

Это решение в лоб. Так как у нас к каждой записи исходного набора данных нужно выбрать какие-то значения, то сделаем это подзапросом:

select * -- выбрать все поля из источника данных       -- а тут подзапрос, выбирающий нужную цену     , (select price1          from price sub         where sub.stock_id = p.stock_id -- те же склад           and sub.prod_id  = p.prod_id  -- и товар,           and sub.kind = 'R'            -- оставим только настоящие цены           and sub.start_date <= p.start_date  -- с датой более ранней или такой же,         order by start_date desc        -- отсортируем в порядке последние цены раньше         limit 1                         -- и возьмём только первую строку       ) as price1x  from price p;

Всё очень прямолинейно и просто, комментарии объясняют, что и как выбирается. Самое главное обеспечить, чтобы такой подзапрос возвращал не более одной записи, а то будет ошибка. Попутно отметим, что если записей не нашлось, что запрос вернёт NULL. В принципе это логичное поведение, но если дальше эти цены будут участвовать в каких-то расчётах, то это надо будет принять во внимание.

Отлично, мы сделали это! Запускаем! Можно расслабиться, прыснуть себе чего-нибудь зободробительного в жвалы, сделать первый (и самый вкусный) гулп... И пока запрос работает, давайте немного отвлечёмся и поразмышляем об его эффективности.

Итак, по условию у нас исходным набором данных была сборная вьюха. Что она там и как выбирает и не перелопачивает ли для этого полбазы неизвестно. Но сборная вьюха и эффективность это обычно понятия плохо совместимые. То есть ожидания от вьюхи что она тормознутая. И второй неутешительный вывод, который просто напрашивается: сборная вьюха практически не оставляет надежд на наличие индексов, так что в подзапросе, чтобы найти нужный склад и нужный товар на нём, скорее всего придётся прочитать всю вьюху целиком. На каждый подзапрос. А если она в самом деле полбазы вычитывает? Миллион строк, да для каждой полбазы перечитать... Вот так на ровном месте и без использования каких бы то ни было спецсредств можно поставить на колени практически любую базу. И мы получим highload на ровном месте. Вернее в highload это превратится, если оптимизатор найдёт способ распараллелить выполнение запроса, так что может быть всё ещё не так уж плохо. У меня на тестовых данных, кстати говоря, сумел.

А ну-ка запустим в соседнем окошке select count(*) from price и внимательно посмотрим как на количество записей, так и на время исполнения. Потому что именно эти два числа нужно будет умножить друг на друга, чтобы прикинуть общее время выполнения запроса. Наш запрос будет работать хоть сколько-нибудь обозримое время, только если количество записей не будет превышать десятков или сотен, ну максимум тысяч записей. Так что полюбовались на количество записей, и давай, тяни свои ложноножки (или ложноручки, что там у тебя) к консоли и прерывай запрос, пока не прилетели админы, чтобы убедительно объяснить на пальцах, что надо и что не надо делать на боевом сервере.

Подход второй

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

Это в целом годный и рабочий способ, исходная вьюха скорее всего будет прочитана только два раза, если вдруг оптимизатор не найдёт более эффективный план выполнения (что вряд ли) или банально не ошибётся. Это даст хорошую производительность, но... Что же но? Почему не хочется так делать? А всё очень просто. Такая конструкция в виде соединения двух наборов данных с витиеватыми условиями, включая выборку только нужных данных, довольно капризна. В этих декартовых соединениях легко ошибиться и потерять часть исходных данных или наоборот, выбрать чего-то лишнего.

А ещё тяжело будет сопровождать и развивать такой запрос. Если появятся новые требования, понадобится что-то добавить или изменить, или поменять поведение в каких-то граничных случаях, то почти на любое такое изменение скорее всего придётся переписывать запрос заново.

Так что оставим этот способ напоследок, а сперва попробуем способ получше, и это...

Подход третий

Что там у нас сделать-то надо было? Сгруппировать и для каждой группы выбрать значение?.. Ба, да сюда же прямо просятся аналитические функции! Они как раз предназначены для подобных задач, когда нужно что-то сгруппировать и выбрать для каждой группы. Если при этом нужно оставить только значения для каждой группы, то используются аналитические функции, а если нужно сохранить весь исходный набор данных то оконные.

Ну-ка, попробуем. Для каждой строки нам нужно выбрать ближайшую реальную цену, то есть надо использовать именно оконные функции. Определить окно, в которое попадут только нужные записи, отсортировать и выбрать значение из нужной строчки окна:

select *     , agg_func(...) over (...) -- тут нужно будет определить окно и найти подходящую функцию  from price

Казалось бы всё просто. Функция будет из тех, которые возвращают одно из значений в группе, это first_value() или lag() или что-то подобное. А вот с определением окна нужно немного помудрить. Оконные функции позволяют определять окно, указав группировку и сортировку. Понятно, что в определении окна будет partition by stock_id, prod_id и что-то надо добавить, чтобы ближайшая предыдущая строчка с реальной ценой встала на фиксированное место в этой группе. Если это не получается сделать в лоб (как, например, если бы нам надо было выбрать просто самую первую или минимальную цену), то обычно помогает такой приём, когда определяют специальное вычислимое поле и по нему делают или группировку, или сортировку. Навскидку пишем case when kind='R' then, потому что от поля kind у нас точно есть зависимость, и задумываемся... Мнэ...

Как выразился один из комментаторов предыдущей статьи, неожиданно стало интересно. Сходу найти подходящее под условие выражение, чтобы отделить группу записей, начинающихся со строки 'R', от других, что-то не получилось. И не сходу тоже как-то не получилось...

Решение постепенно нашлось, и вот какой приём при этом был применён. Получилась двухходовка. На первом этапе формируем специальное вычислимое поле, по которому на втором этапе уже выбираем нужные значения. Поле (назовём его уровнем цены) формируем так: суммируется нарастающим итогом для каждого склада и товара в порядке даты начала действия следующее: единица для строк с kind='R' и ноль для всего остального. Получается как раз, что уровень цены перещёлкивается на следующий, как только мы встречаем реальную цену:

select *     , sum(case when kind='R' then 1 else 0 end) -- сумма нарастающим итогом       over(partition by stock_id, prod_id       -- в разрезе складов и товаров            order by start_date                  -- порядок важен!            rows between unbounded preceding and current row) as lvl -- нам нужно именно до CURRENT ROW  from price

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

select *     , first_value(price1) over (partition by prod_id, stock_id, lvl                                     order by start_date) as price1x  from (select ...)

Собрать вместе можно хоть через подзапрос, хоть с CTE. Смотрим на результаты выполнения вычисляется за один проход, то есть в высшей степени эффективно (достат кол!).

Итого, вот оно решение:

select p.*     , first_value(price1) over (partition by prod_id, stock_id, lvl                                     order by start_date) as price1x  from (select *             , sum(case when kind='R' then 1 else 0 end)               over(partition by stock_id, prod_id order by start_date                    rows between unbounded preceding and current row) as lvl  from price) p

Дальше, если надо будет вытащить остальные значения из нужной строчки, то можно повторить first_value() нужное количество раз. Для компактности можно вынести определение окна отдельно, чтобы не загромождать запрос:

select *     , first_value(price1) over w as price1x     , first_value(cost1)  over w as cost1x  from (select *             , sum(case when kind='R' then 1 else 0 end)               over(partition by stock_id, prod_id order by start_date                 rows between unbounded preceding and current row) as lvl          from price) pwindow w as (partition by prod_id, stock_id, lvl order by start_date)

Вот собственно и всё, на этом можно закончить, реализация второго подхода не понадобилась.

Выводы

1. Во время составления SQL-запроса имеет смысл думать про эффективность его работы.

2. Использовать оконные функции отличная идея.

3. Если оконную функцию не получается применить в лоб, то можно попробовать разбить задачу на две подзадачи генерация поля для группировки, последующий вызов оконной функции поверх группировки с предыдущего шага.

4. Если оконную функцию применить так и не удалось, то подход 2 даст нужные результаты, а если время работы запроса не является проблемой, то подход 1 даже лучше, потому что он прост и понятен.

Разбор решений, приведённых в комментариях

В статье с условием задачи я пообещал сделать разбор решений из комментариев с раздачей каких-нибудь слонов. Выполняю обещание.

Так или иначе я насчитал 11 разных вариантов решения от 10 представителей. Вообще запросов было приведено больше, но часто следующий SQL-запрос в цепочке комментариев был развитием или исправлением предыдущего. Так что количество сильно зависит от того, как считать. Пройдёмся по хронологии.

Статья с условием задачи была опубликована 16 марта 2021 в 17:12. Первое решение от @nice17 16 марта 2021 в 17:34 было предложено уже через 22 минуты после публикации статьи (22 минуты, Карл! и ещё же надо было хоть по диагонали прочитать условие). Решение было неверным, но подход к решению явно был уже в нужном направлении. Чуть позже автор довёл его до правильного. А первое более-менее работающее решение (от @ZMB138 16 марта 2021 в 19:07) появилось меньше чем через два часа(!) после публикации, это была реализация подхода 2.

Первое решение в третьем подходе от @Kilor появилось ещё спустя полчаса 16 марта 2021 в 19:39. Автор применил хитрый ход, использовав функцию array_agg(), которая допускает использование FILTER в определении окна, что позволило отфильтровать только записи с реальными ценами и избежать рассмотренной мной двухходовости. Также продемонстрирован интересный ход, что вся найденная для сопоставления строка таблицы упаковывалась в JSON, из которого дальше выбиралось нужное поле. Или все нужные поля. Не очень универсально в плане разных диалектов SQL, но изящно. Последняя версия запроса от 17 марта 2021 в 10:18 уже без упаковки/распаковки в JSON получилась компактной и эффективной.

(замечание в сторону) Воистину, вырисовывается универсальный способ, как можно решать сложные задачи на SQL публиковать их на хабре, и дальше остаётся только подождать пару часов!..

Аналогичный подход использовал @AngelloreA в запросе от 16 марта 2021 в 22:30. Сопоставляемая строка с реальными стоимостями упаковывалась правда не в JSON, а просто в текст, что потенциально чревато проблемами при распаковке (надо бы явно указать форматы), но это работает и тоже в один проход, то есть получилась реализация подхода 3.

@viras777 16 марта 2021 в 23:07 привёл необычное решение, где весьма остроумно для генерации уровней цены используется последовательность. В целом, как мне кажется, использовать последовательность для такого случая избыточно (да и Оккам не велел), плюс побочных эффектов в программировании обычно стараются избегать, но тем не менее оно работает.

@AlexKadetov уже 16 марта 2021 к 23:10 получил по сути такое же решение в подходе 3, к которому пришёл и я, пусть даже и с некоторыми огрехами в реализации. Вообще огрехи в реализации были много у кого, я старался не придираться, потому что в реальной жизни конечно всякий запрос ещё какое-то время доводится до кондиции, уточняется его работа и всё подобное вылавливается и исправляется.

Ночью @qvan 17 марта 2021 в 01:39 привёл несколько тяжеловесное, но рабочее решение в подходе 2. Утром @xxxcoltxxx 17 марта 2021 в 09:13 привёл более явное и ясное решение в реализации подхода 2. Чуть позже @jayrumi 17 марта 2021 в 13:11 тоже опубликовал своё пусть и несколько тяжеловесное, но работающее решение тоже в подходе 2.

Дальше опять вернулся @Kilor и 17 марта 2021 в 14:30 привёл пару красивых решений в подходе 2, продемонстрировав виртуозную технику JOIN-ов наборов данных. Ближе к вечеру @Miha_S7 17 марта 2021 в 18:19 привёл своё решение в подходе 3, повторяющее полученное мной в первой части статьи.

На этом активность в комментариях заглохла, статья скрылась из леныт сайта, и больше запросов никто не публиковал.

Итого, из интересного отмечу скорость, с которой тема была раскрыта со всех сторон, включая диалекты дружественных СУБД. Плюшкополучателем был избран @Kilor, как автор самого первого однопроходного решения (в подходе 3), а также за проявленную широту охвата техники владения SQL (использование оконных функций, JSON, FILTER, JOIN-ы) и ясную по форме реализацию с использованием всего перечисленного. С ним я уже связался. Специально отмечу самое первое работающее решение от @ZMB138, оригинальность и неожиданность использования последовательностей от @viras777, а также эталонное на мой взгляд решение от @AlexKadetov.

И отдельное большое спасибо всем, кто принял участие в обсуждении и поделился своими решениями.

Перевод материала подготовлен в рамках курса Экспресс-курс по управлению миграциями (DBVC).

Liquibase это инструмент управления изменениями в базе данных. С его помощью вы можете отслеживать изменения в базе данных, сделанные с помощью SQL (или XML) скриптов. Эти скрипты могут быть добавлены в системы контроля версий, такие как git.

В данной статье мы рассмотрим, как автоматизировать развертывание баз данных с помощью Liquibase. Автоматизация может быть выполнена с помощью

1. Пайплайн Jenkins

2. Shell-скриптов

Я не буду говорить ни об одном из этих двух способов, поскольку это не является темой данной статьи только для того, чтобы все было просто и понятно.

Существует два способа записи наборов изменений в Liquibase, т.е. XML и SQL. Я выбрал SQL, чтобы избежать записи соответствующих изменений XML для каждого выполняемого нами обновления SQL. Таким образом, тот же SQL-файл, который мы пишем для разработки, используется для развертывания Liquibase.

Обратите внимание, что я протестировал эти шаги только для базы данных Postgre, однако те же шаги можно повторить для любой базы данных. Также убедитесь, что на вашем компьютере настроен Liquibase .

Выполните приведенные ниже шаги:

Создать файл changelog (журнал изменений)

Создать XML-файл с именем liquibase-changelog.xml (имя может быть любым!) со следующим содержимым:

<?xml version="1.0" encoding="UTF-8"?><databaseChangeLog xmlns="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangelog"xmlns:xsi="http://personeltest.ru/away/www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://personeltest.ru/away/www.liquibase.org/xml/ns/dbchangeloghttp://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"><include file="<path to changeset SQL file>/<changeset file name>.sql>" relativeToChangelogFile="true"/>...more <include> tags goes here...</databaseChangeLog>

Обратите внимание на тег include в приведенном выше XML. Каждый файл SQL changeset (набор изменений), который должен отслеживаться Liquibase, должен быть зарегистрирован в этом файле changelog (журнал изменений).

Создание наборов изменений (changeset) SQL

Добавьте файлы SQL changeset в выбранное вами место. Синтаксис SQL, который работает с Liquibase, следующий:

--liquibase formatted sql--changeset <author name>:<a unique identifier for the SQL changeset><SQL statements go here><SQL statements go here>--rollback <rollback SQL statements>--rollback <rollback SQL statements>

Рассмотрим пример:

--liquibase formatted sql--changeset xameeramir:create-test-tableCREATE TABLE IF NOT EXISTS testTable(columnName1 VARCHAR (355));--rollback DROP TABLE--rollback testTable

Обратите внимание, что файл SQL changeset отличается от файла XML changelog.

Регистрация SQL changeset в XML-файле changelog

Включите файл SQL changeset в файл changelog, который мы создали ранее, со следующими тегами XML:

<include file=<path to SQL changeset file>/<changeset file name>.sql relativeToChangelogFile="true" />

Добавьте столько SQL changesets и зарегистрируйте их в файле changelog, сколько вам нужно.

Триггер в Liquibase для обновления базы данных

Просто выполните приведенную ниже команду:

liquibase --changeLogFile=<path to changelog file>/<liquibase changelog file name>.xml --username=<database username> --password=<database password> --classpath=<path to the liquibase installation>/postgresql-42.2.5.jar --url=jdbc:postgresql://<database url>/<database name> update

Classpath (путь к классам) - это драйвер JDBC, который мы настроили в предыдущей публикации. Postgresql-42.2.5.jar - это JDBC-драйвер, предназначенный для Postgres, и его можно будет заменить на базу данных по вашему выбору без каких-либо специальных преобразований на этих этапах.

Приведенная выше команда может быть использована в shell-скриптах или в пайплайне CI/CD для запуска обновлений базы данных.

Автоматизация CI/CD

После того, как вышеуказанная конфигурация установлена - автоматизация может быть выполнена либо на клиенте с помощью shell-скриптов, либо на сервере с помощью shell-скриптов или имплементации CI/CD.

Предположим, что имплементация CI/CD, которая запускает развертывание Liquibase, означает выполнение команды триггер (trigger) Liquibase, приведенной выше, при каждом git push в ветку DEVELOP (или любую другую).

Первым предварительным условием будет наличие файла liquibase-changelog.xml. Допустим, мы сохраним его на уровне ~/ с операторами include, указывающими на папку, в которой находятся changeset SQL. Следующий рабочий процесс позволит автоматизировать развертывание базы данных с помощью пайплайна CI/CD:

• Поместите файл SQL changeset в репозиторий функций.

• Отправьте запрос на исправление для ветки DEVELOP

• После достоверной проверки и согласования объедините ветку feature с веткой DEVELOP.

• Имплементация CI/CD, настроенная на сервере DEVELOP, запустит Liquibase для обновления базы данных.

• Liquibase автоматически будет выполнять только новые файлы (любые уже выполненные файлы не будут запущены повторно).

Автоматизация с помощью shell-скриптов

В shell-скриптах будет записана одна и та же команда триггер Liquibase. Как только shell-скрипты будут выполнены, содержащие их changeset (наборы изменений) Liquibase будут выполнены автоматически.

Вы можете задаться вопросом, как shell-скрипты узнают, когда выполнять команду? Ответ прост:

• Shell-скрипты могут выполняться на триггерах cron.

• Shell-скрипты могут быть выполнены при некоторых системных событиях.

Выбор за вами!

Узнать подробнее об экспресс-курсе по управлению миграциями (DBVC)

Привет! На связи Артемий Analytics Engineer из Wheely.

Сегодня хотел бы поговорить о вопросах конвертирования финансовых показателей в разные валюты. Вопрос достаточно актуальный, так как большое количество компаний имеют мультинациональные зоны присутствия, строят аналитику глобального масштаба, готовят отчетность по международным стандартам.

Покажу как этот вопрос решается с помощью современных подходов на примере кейса Wheely:

• Расширение списка базовых валют

• Регулярное обновление и получения актуальных курсов

• Обеспечение корректности исторических показателей

• Максимальное удобство и простота использования в аналитических инструментах

Велком под кат для разбора решения проблемы учета мультивалютных метрик и показателей: Open Exchange Rate, Airflow, Redshift Spectrum, dbt.

Новые требования к сервису валютных курсов

В качестве legacy-источника использовался веб-сервис ЦБ РФ. Однако с изменяющимися требованиями и расширением зон присутствия компании его стало недостаточно. Например, по причине отсутствия котировки AED (дирхам ОАЭ). Для кого-то могут быть актуальны курсы криптовалют BTC, ETH, которые в веб-сервисе ЦБ РФ тоже отсутствуют.

Новые требования можно суммировать следующим образом:

• Поддержка расширенного набора базовых валют, которые отсутствуют в API ЦБ РФ

• Получение самых актуальных котировок, включая внутридневные курсы

• Минимизация трансформаций данных вне Хранилища Данных (лучше если их вообще нет)

Задачи, которые предстоит решить легко визуализировать в виде матрицы. Красным помечены области, поддержку которых предстоит добавить:

• Интеграция нового API для уже использующихся курсов

• Добавление новых базовых валют в выгрузку

• Получение ретроспективных (исторических) данных по новым валютам за прошлые периоды

• Архивирование курсов из legacy-источника

Легаси приложение по выгрузке курсов валют формировало pivot-таблицу с коэффициентом для каждой пары в отдельном столбце. Это удобно, когда у нас есть строго фиксированный набор валют и наименования колонок, но превращается в головную боль если список валют необходимо расширить.

Появилось желание уйти от всех трансформаций и формирований таблиц в pandas до того как данные попадают в Хранилище. Здесь я придерживаюсь принципа применения всех трансформаций (T в ELT) в одном месте, и помогает мне в этом замечательный инструмент dbt.

Интеграция с новым поставщиком данных

Как уже стало понятно, без внешнего поставщика данных обойтись не получится, поэтому предлагаю рассмотреть один из ряда провайдеров курсов валют https://openexchangerates.org/

Минимальный необходимый план Developer включает в себя:

• 10.000 запросов ежемесячно (более чем достаточно)

• Ежечасные внутридневные обновления курсов

• Широкий набор базовых валют, включая криптовалюты

Доступные методы API:

Для получения актуальных курсов валют воспользуемся API endpoint /latest.json

Простой запрос-ответ может выглядеть следующим образом:

Установка на расписание в Airflow

Для регулярного получения актуальных курсов валют я воспользуюсь инструментом Airflow. Apache Airflow де-факто стандарт в области оркестрации данных, data engineering и управления пайплайнами.

Смысловая составляющая графа задачи (DAG):

• Сделать запрос к API

• Сохранить полученный ответ (например, в виде уникального ключа на S3)

• Уведомить в Slack в случае ошибки

Конфигурация DAG:

• Базовые валюты (base currency), от которых отсчитываем курсы

• Синхронизация расписание запусков с расчетом витрин в Хранилище Данных

• Токен доступа к сервису

Самый простой DAG состоит из одного таска с вызовом простого shell-скрипта:

TS=`date +"%Y-%m-%d-%H-%M-%S-%Z"` curl -H "Authorization: Token $OXR_TOKEN" \ "https://openexchangerates.org/api/historical/$BUSINESS_DT.json?base=$BASE_CURRENCY&symbols=$SYMBOLS" \ | aws s3 cp - s3://$BUCKET/$BUCKET_PATH/$BUSINESS_DT-$BASE_CURRENCY-$TS.json

Вот как выглядит результат регулярной работы скрипта в S3:

Сегодня в штатном режиме выполняется около 25 обращений к сервису в сутки, статистика выглядит следующим образом:

Выгрузка истории по новым валютам

После обеспечения регулярной выгрузки всех необходимых валют, можно приступить к формированию истории по новым базовым валютам (которой, очевидно, нет). Это позволит переводить в новые валюты суммы транзакций прошлых периодов.

К сожалению, план Developer не включает обращения к API endpoint /time-series.json, и только ради этой разовой задачи не имеет смысла делать upgrade на более дорогостоящую версию.

Воспользуемся методом /historical/*.json и простым опросом API в цикле для формирования исторической выгрузки:

#!/bin/bash d=2011-01-01while [ "$d" != 2021-02-19 ]; do echo $d curl -H "Authorization: Token $TOKEN" "https://openexchangerates.org/api/historical/$d.json?base=AED&symbols=AED,GBP,EUR,RUB,USD" > ./export/$d.json d=$(date -j -v +1d -f "%Y-%m-%d" $d +%Y-%m-%d)done

Пиковая нагрузка вызвала вопросы у коллег, которые тоже пользуются сервисом, но это была разовая акция:

Архивирование исторических курсов валют

Вся история обменных курсов полученная из legacy-источника ЦБ РФ до даты X (перехода на новый сервис-провайдер) подлежит архивированию в неизменном виде.

Я хочу сохранить все те курсы, которые мы показывали в своих аналитических инструментах без изменений. То есть чтобы суммы в дашбордах и отчетах бизнес-пользователей не были изменены ни на копейку.

Для этого я выполню выгрузку накопленных значений обменных курсов за весь исторический период в Data Lake. Более детально, я произведу:

• Трансформацию legacy pivot-таблицы в двумерную

• Запись в колоночный формат PARQUET в AWS S3

CREATE EXTERNAL TABLE spectrum.currencies_cbrfSTORED AS PARQUETLOCATION 's3://<BUCKET>/dwh/currencies_cbrf/' ASWITH base AS (   SELECT 'EUR' AS base_currency   UNION ALL   SELECT 'GBP'   UNION ALL   SELECT 'RUB'   UNION ALL   SELECT 'USD')SELECT   "day" AS business_dt   ,b.base_currency   ,CASE b.base_currency       WHEN 'EUR' THEN 1       WHEN 'GBP' THEN gbp_to_eur       WHEN 'RUB' THEN rub_to_eur       WHEN 'USD' THEN usd_to_eur       ELSE NULL     END AS eur   ,CASE b.base_currency       WHEN 'EUR' THEN eur_to_gbp       WHEN 'GBP' THEN 1       WHEN 'RUB' THEN rub_to_gbp       WHEN 'USD' THEN usd_to_gbp       ELSE NULL     END AS gbp   ,CASE b.base_currency       WHEN 'EUR' THEN eur_to_rub       WHEN 'GBP' THEN gbp_to_rub       WHEN 'RUB' THEN 1       WHEN 'USD' THEN usd_to_rub       ELSE NULL     END AS rub   ,CASE b.base_currency       WHEN 'EUR' THEN eur_to_usd       WHEN 'GBP' THEN gbp_to_usd       WHEN 'RUB' THEN rub_to_usd       WHEN 'USD' THEN 1       ELSE NULL     END AS usd     FROM ext.currencies c   CROSS JOIN base b;

Таким образом, в хранилище S3 у меня теперь есть статический снимок всех обменных курсов, когда-либо использованных в аналитических приложениях, сериализованный в оптимизированный колоночный формат со сжатием. В случае необходимости пересчета витрин и исторических данных я запросто смогу воспользоваться этими курсами.

Доступ к данным из DWH через S3 External Table

А теперь самое интересное из своего аналитического движка Amazon Redshift я хочу иметь возможность просто и быстро обращаться к самым актуальным курсам валют, использовать их в своих трансформациях.

Оптимальное решение создание внешних таблиц EXTERNAL TABLE, которые обеспечивают SQL-доступ к данным, хранящимся в S3. При этом нам доступно чтение полуструктурированных данных в формате JSON, бинарных данных в форматах AVRO, ORC, PARQUET и другие опции. Продукт имеет название Redshift Spectrum и тесно связан с SQL-движком Amazon Athena, который имеет много общего с Presto.

CREATE EXTERNAL TABLE IF NOT EXISTS spectrum.currencies_oxr (   "timestamp" bigint   , base varchar(3)   , rates struct<aed:float8, eur:float8, gbp:float8, rub:float8, usd:float8>)ROW format serde 'org.openx.data.jsonserde.JsonSerDe'LOCATION 's3://<BUCKET>/dwh/currencies/';

Обратите внимание на обращение ко вложенному документу rates с помощью создания типа данных struct.

Теперь добавим к этой задаче секретную силу dbt. Модуль dbt-external-tables позволяет автоматизировать создание EXTERNAL TABLES и зарегистрировать их в качестве источников данных:

   - name: external     schema: spectrum     tags: ["spectrum"]     loader: S3     description: "External data stored in S3 accessed vith Redshift Spectrum"     tables:       - name: currencies_oxr         description: "Currency Exchange Rates fetched from OXR API https://openexchangerates.org"         freshness:           error_after: {count: 15, period: hour}         loaded_at_field: timestamp 'epoch' + "timestamp" * interval '1 second'         external:           location: "s3://<BUCKET>/dwh/currencies/"           row_format: "serde 'org.openx.data.jsonserde.JsonSerDe'"         columns:           - name: timestamp             data_type: bigint           - name: base             data_type: varchar(3)           - name: rates             data_type: struct<aed:float8, eur:float8, gbp:float8, rub:float8, usd:float8>

Немаловажным элементом является проверка своевременности данных source freshness test на курсы валют. Тем самым мы будем постоянно держать руку на пульсе поступления актуальных данных в Хранилище. Очень важно рассчитывать все финансовые метрики корректно и в срок, а без актуальных значений курсов задачу решить невозможно.

В случае отставания данных более 15 часов без свежих обменных курсов мы тут же получаем уведомление в Slack.

Для прозрачности и простоты пользователей объединим исторические данные (архив) и постоянно поступающие актуальные курсы (новый API) в одну модель currencies:

{{   config(       materialized='table',       dist='all',       sort=["business_dt", "base_currency"]   )}} with cbrf as (  select      business_dt   , null as business_ts   , base_currency   , aed   , eur   , gbp   , rub   , usd  from {{ source('external', 'currencies_cbrf') }} where business_dt <= '2021-02-18' ), oxr_all as (    select      (timestamp 'epoch' + o."timestamp" * interval '1 second')::date as business_dt   , (timestamp 'epoch' + o."timestamp" * interval '1 second') as business_ts   , o.base as base_currency   , o.rates.aed::decimal(10,4) as aed   , o.rates.eur::decimal(10,4) as eur   , o.rates.gbp::decimal(10,4) as gbp   , o.rates.rub::decimal(10,4) as rub   , o.rates.usd::decimal(10,4) as usd   , row_number() over (partition by base_currency, business_dt order by business_ts desc) as rn    from {{ source('external', 'currencies_oxr') }} as o   where business_dt > '2021-02-18' ), oxr as (  select      business_dt   , business_ts   , base_currency   , aed   , eur   , gbp   , rub   , usd  from {{ ref('stg_currencies_oxr_all') }} where rn = 1 ), united as (  select      business_dt   , business_ts   , base_currency   , aed   , eur   , gbp   , rub   , usd  from cbrf  union all  select      business_dt   , business_ts   , base_currency   , aed   , eur   , gbp   , rub   , usd  from oxr ) select    business_dt , business_ts , base_currency , aed , eur , gbp , rub , usd from united

При этом физически справочник с курсами валют копируется на каждую ноду аналитического кластера Redshift и хранится в отсортированном по дате и базовой валюте виде для ускорения работы запросов.

Использование курсов в моделировании данных

В целом, работа с курсами валют для аналитиков и инженеров, которые развивают Хранилище Данных не изменилась и осталась весьма простой. Все детали использования нового API, обращения к внешним полу-структурированным документам JSON в S3, объединению с архивными данными скрыты . В своих трансформациях достаточно сделать простой джоин на таблицу с курсами валют:

   select        -- price_details       , r.currency       , {{ convert_currency('price', 'currency') }}       , {{ convert_currency('discount', 'currency') }}       , {{ convert_currency('insurance', 'currency') }}       , {{ convert_currency('tips', 'currency') }}       , {{ convert_currency('parking', 'currency') }}       , {{ convert_currency('toll_road', 'currency') }}    from {{ ref('requests') }} r       left join {{ ref('stg_currencies') }} currencies on r.completed_dt_utc = currencies.business_dt           and r.currency = currencies.base_currency

Сами метрики конвертируются при помощи простого макроса, который на вход принимает колонку с исходной суммой и колонку с исходным кодом валюты:

-- currency conversion macro{% macro convert_currency(convert_column, currency_code_column) -%}      ( {{ convert_column }} * aed )::decimal(18,4) as {{ convert_column }}_aed   , ( {{ convert_column }} * eur )::decimal(18,4) as {{ convert_column }}_eur   , ( {{ convert_column }} * gbp )::decimal(18,4) as {{ convert_column }}_gbp   , ( {{ convert_column }} * rub )::decimal(18,4) as {{ convert_column }}_rub   , ( {{ convert_column }} * usd )::decimal(18,4) as {{ convert_column }}_usd {%- endmacro %}

Практико-ориентированное развитие

Работа с данными одно из наиболее востребованных и бурно развивающихся направлений. Каждый день я нахожу новые интересные задачи и придумываю решения для них. Это захватывающий и интересный путь, расширяющий горизонты.

В конце мая состоится юбилейный запуск курса Data Engineer в ОТУС, в котором я принимаю участие в роли преподавателя.

По прошествии двух лет программа постоянно менялась, адаптировалась. Ближайший запуск принесет ряд нововведений и будет построен вокруг кейсов реальных прикладных проблем инженеров:

• Data Architecture

• Data Lake

• Data Warehouse

• NoSQL / NewSQL

• MLOps

Детально с программой можно ознакомиться на лендинге курса.

Также я делюсь своими авторскими заметками и планами в телеграм-канале Technology Enthusiast.

Благодарю за внимание.

Oracle diagnostic events - это очень мощное средство, но, к сожалению, слабо документированное, поэтому я решил перечислить и свести воедино несколько неизвестных или малоизвестных способов его использования.

Единственная его документация - это очень краткая встроенная документация, доступная по команде oradebug doc в SQL*Plus, и она отсутствует в официальной документации. Вы можете встретить только отрывки из нее в разных блогах, что не очень удобно, поэтому я скомпилировал ее полностью: http://orasql.org/files/events/

Современный синтаксис и несколько простых примеров приведены в oradebug doc event. Я их здесь приводить не буду и начну сразу с примеров.

alter system set events    'kg_event[1476]        {occurence: start_after 1, end_after 3}            trace("stack is: %\n", shortstack())            errorstack(2)    ';

1. kg_event[errno] - это Kernel Generic event из библиотеки Generic, инструктирующее сработать на ошибку с номером errno;

2. {occurence: start_after X, end_after Y} - это один из фильтров , инструктирующий пропустить X срабатываний данного event и выполниться Y раз;

3. trace(format, str1, str2, ..., str15) - это функция из ACTIONS для вывода в трейс-файл своих сообщений;

4. shortstack() - это функция из ACTIONS , возвращающая call stack в кратком виде;

5. errorstack(level) - это функция из ACTIONS, выводящая в трейс-файл расширенную информацию (level: 0 - только errorstack, 1 - errorstack + call stack, 2 - как level=1 + processtate, 3 - как level=2 + context area). Еще более расширенную информацию можно получить с помощью PROCESSSTATE или SYSTEMSTATE. Если нужен только call stack можно воспользоваться CALLSTACK(level) - при level>1 запишет и аргументы.

alter system set events     'trace[SQL_Compiler.* | SQL_Execution.*]        [SQL: ...]            {process: ospid = ...}            {occurence:end_after 3}                controlc_signal()';

1. trace[component] - это основной диагностический event, позволяющий указать компоненты, внутри которых надо срабатывать. В данном случае, я указал срабатывать внутри всех дочерних функций в SQL_Compiler и SQL_Execution. Например, RDBMS.SQL_Compiler.SQL_Optimizer.SQL_Transform.* указало бы срабатывать только в функциях трансформации запросов.

2. SQL[SQL: sqlid ] - это единственный SCOPE в библиотеке RDBMS, позволяющий отфильтровать все события, связанные с указанными запросами, включая события его рекурсивных запросов(например, если это sql_id PL/SQL вызова, то будут оттрассированы все запросы внутри него, или для запроса - все его внутренние запросы во время парсинга и оптимизации, внутренних запросов PL/SQL функций и тд.);

3. {process: ...} - это фильтр, позволяющий указать процессы для которых данный event будет включен.

4. controlc_signal - это ACTION, вызывающий ошибку "ORA-01013: user requested cancel of current operation", т.е. сессия запустившая этот запрос получит эту ошибку, как будто она сама прервала выполнение запроса.

   Конкретно, данным примером я воспользовался, когда в ходе нагрузочного тестирования большого веб-приложения на hibernate, были обнаружены очень плохие фактически ненужные запросы и нужно было помочь разработчикам быстро найти откуда в их коде они выполняются.

alter system set events     'sql_trace {process: ospid = ...}      trace("sqlid(%): %\n", sqlid(), evfunc())     ';

sql_trace - это старый добрый event 10046, а целиком команда предписывает при каждом событии инструментированным этим event 10046, вывести функцию, его вызвавшую(evfunc) и sqlid запроса (ACTION sqlid).

Пример:

Как видите, при "_rowsource_statistics_sampfreq" =1 инструментировано намного больше событий: 26 против 12! Подробнее тут.

alter system set events     'wait_event["enq: TM - contention"]        {wait: minwait=1000}             errorstack(1)            trace("event=[%] sqlid=%, ela=% p1=% p2=% p3=%\n",                    evargs(5), sqlid(), evargn(1), evargn(2), evargn(3), evargn(4))    ';

1. wait_event[name] - event, срабатывающий по имени событий ожидания (wait events), имена и их параметры вы можете посмотреть в v$event_name:
   select wait_class,name,parameter1,parameter2,parameter3 ,display_name from v$event_name

2. {wait: ... } - фильтр позволяющий отфильтровать как по долготе ожидания(в мс), так и по параметрам P1, P2, P3. Например, для указанного ожидания "TM-contention", P2 - это object #, и можно указать {wait: minwait=1000; p2=12345}, т.е. только ожидания табличной блокировки на таблице с object_id=12345 и длившиеся дольше 1 сек.

3. evargX() - это функции из ACTION, возвращающие аргументы event-check события, где 1-й аргумент это elapsed time(ms), 2-4 - p1-p3, 5-й - имя ожидания. Соответствующие функции имеет и kg_event: errargX.

Или еще пример, когда вам надо узнать какие сессионные переменные были изменены. Допустим, кто-то забыл указать nls-параметры в to_number, on conversion error не указан, и какие-то сессии периодически получают ORA-01722: invalid number:

-- допустим, изначально должны стоять следующие параметры:SQL> alter session set nls_numeric_characters='.,';Session altered.-- и все работает нормальноSQL> select to_number('1,200.3','999g999d999') + 10 from dual;TO_NUMBER('1,200.3','999G999D999')+10-------------------------------------                               1210.3-- до тех пор, пока в какой-то момент они не меняются:SQL> alter session set nls_numeric_characters=q'[.']';'Session altered.-- соответственно вылетает ошибка:SQL> select to_number('1,200.3','999g999d999') + 10 from dual;select to_number('1,200.3','999g999d999') + 10 from dual                 *ERROR at line 1:ORA-01722: invalid number

Хотя нет никакой вьюхи для получения параметров чужой сессии, не входящих в v$ses_optimizer_env, мы можем легко их получить с помощью MODIFIED_PARAMETERS():

alter system set events     'kg_event[1722]         {process: ospid=27556}        {occurence:end_after 1}            MODIFIED_PARAMETERS()';

И благодаря тому, что сейчас есть удобные v$diag_alert_ext - для доступа к alert.log, v$diag_trace_file_contents - для доступа к трейс-файлам, мы можем все получить простым запросом:

select c.payloadfrom v$diag_trace_file_contents cwhere 1=1  and c.session_id   = ... -- sid сессии  and c.serial#      = ... -- serial#  and c.section_name = 'Error Stack' -- параметры будут в секции Error Stack--  and c.payload like '%nls_numeric%' -- можно отфильтровать именно  and c.timestamp>systimestamp-interval'15'minute;-- результат:DYNAMICALLY MODIFIED PARAMETERS:  nls_language             = "AMERICAN"  nls_territory            = "AMERICA"  nls_sort                 = "BINARY"  nls_date_language        = "AMERICAN"  nls_date_format          = "yyyy-mm-dd hh24:mi:ss"  nls_currency             = "$"  nls_numeric_characters   = ".'"  nls_iso_currency         = "AMERICA"  nls_calendar             = "GREGORIAN"  nls_time_format          = "hh24:mi:ssxff"  nls_timestamp_format     = "yyyy-mm-dd hh24:mi:ssxff"  nls_time_tz_format       = "hh24:mi:ssxff TZR"  nls_timestamp_tz_format  = "yyyy-mm-dd hh24:mi:ssxff TZR"  nls_dual_currency        = "$"  nls_comp                 = "BINARY"  local_listener           = ""

PS. Дополнительные детали вы можете узнать в статьях Tanel Poder:
1. The full power of Oracles diagnostic events, part 1: Syntax for KSD debug event handling
2. The full power of Oracles diagnostic events, part 2: ORADEBUG DOC and 11g improvements
3. What Caused This Wait Event: Using Oracle's wait_event[] tracing
4. http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

О внутренних функциях у Frits Hoogland: http://orafun.info/

Полная внутренняя документация по oradebug/diagnostic events: http://orasql.org/files/events

Совсем недавно мне была поставлена задача, написать сервис, который будет заниматься всего лишь одной, но очень емкой задачей собирать большой объем данных из базы, агрегировать и заполнять все это в Excel по определенному шаблону. В процессе поиска лучшего решения было опробовано несколько подходов, решены проблемы, связанные с памятью и производительностью. В этой статье я хочу поделиться с вами основными моментами и этапами реализации данной задачи.

1. Постановка задачи

В связи с тем, что мне нельзя разглашать подробности ТЗ, сущности, алгоритмы сбора данных и т. д. Пришлось придумать что-то аналогичное:

Итак представим, что у нас есть онлайн чат с высокой активностью, и заказчик хочет выгружать все сообщения, обогащенные данными о пользователе, за определенную дату в Excel. В день может копиться более 1 миллиона сообщений.

У нас есть 3 таблицы:

1. User. Хранит имя пользователя и его некий рейтинг (не важно откуда он берется и как считается)

2. Message. Хранит данные о сообщении Имя пользователя, ДатуВремя, Текст сообщения.

3. Task. Задача на формирование отчета, которую создает заказчик. Хранит ID, Статус задачи (выполнено или нет), и два параметра: Дату сообщения начало, Дату сообщения конец.

Состав колонок будет следующим:

В Excel Заказчик хочет видеть 4 колонки 1) message_date. 2) name. 3) rating. 4) text. Ограничение по количеству строк 1 млн. Надо заполнить этими данными excel, а дальше заказчик уже будет работать с этими данными в екселе самостоятельно.

2. Задача понятна, начнем поиск решения

Так как в компании все стараются придерживаться единого стиля в разработке приложений, то и мне пришлось начать с самого обычного подхода, который используется во всех остальных микросервисах это Spring + Hibernate для запуска приложения и работы с БД. В качестве БД используется Oracle, хотя использование любой другой СУБД будет плюс минус похожим.

Для старта приложения нам понадобится зависимость spring-boot-starter-data-jpa, которая объединяет в себе сразу Spring Data, Hibernate и JPA, все это нам понадобится для удобства работы с БД и нашими сущностями.

        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-data-jpa</artifactId>            <version>2.4.5</version>        </dependency>

Для тестирования добавим spring-boot-starter-test

        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <scope>test</scope>        </dependency>

И еще нам нужен сам драйвер для подключения к БД

        <dependency>            <groupId>com.oracle.database.jdbc</groupId>            <artifactId>ojdbc10</artifactId>            <version>19.10.0.0</version>        </dependency>

Далее нам нужно добавить некоторые настройки конфигурации. У нас будет один метод, который будет ходить в таблицу TASK, искать задачу в статусе CREATED и, если такая задача существует, то запускать генерацию отчета с параметрами. Предполагается, что генерация отчета может быть долгой, поэтому наш метод будет запускаться по расписанию в два потока асинхронными процессами. Так же для Spring Data укажем наш репозиторий для поиска соответствующих сущностей. Класс конфигурации будет выглядеть следующим образом:

package com.report.generator.demo.config;import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.PropertySource;import org.springframework.data.jpa.repository.config.EnableJpaRepositories;import org.springframework.scheduling.TaskScheduler;import org.springframework.scheduling.annotation.EnableAsync;import org.springframework.scheduling.annotation.EnableScheduling;import org.springframework.scheduling.concurrent.ThreadPoolTaskScheduler;@Configuration@EnableScheduling@EnableAsync@EnableJpaRepositories(basePackages = "com.report.generator.demo.repository")@PropertySource({"classpath:application.properties"})@ConditionalOnProperty(    value = "app.scheduling.enable", havingValue = "true", matchIfMissing = true)public class DemoConfig {    private static final int CORE_POOL_SIZE = 2;    @Bean(name = "taskScheduler")    public TaskScheduler getTaskScheduler() {        ThreadPoolTaskScheduler scheduler = new ThreadPoolTaskScheduler();        scheduler.setPoolSize(CORE_POOL_SIZE);        scheduler.initialize();        return scheduler;    }}

Класс генерации отчетов содержит в себе @Scheduled метод, который раз в минуту ищет Task и, если находит, то запускает генерацию отчета с параметрами из этой таски.

    @Async("taskScheduler")    @Scheduled(fixedDelay = 60000)    public void scheduledTask() {        log.info("scheduledTask is started");        Task task = getTask();        if (Objects.isNull(task)) {            log.info("task not found");            return;        }        log.info("task found");        generate(task);    }

Класс стартер приложения не имеет ничего примечательного, весь код можно посмотреть на GitHub.

3. Выборка данных из БД

Т.к. в компании повсеместно используется Hibernate было решено использовать его. Добавлено entity MessageData с необходимым набором полей (id, name, rating, messageDate, test). Первой попыткой выбрать необходимые данные была попытка в лоб выгрузить все в List<Message> с помощью простого метода:

List<Message> findAllByMessageDateBetween(Instant dateFrom, Instant dateTo);

А дальше уже в цикле создавать объекты MessageData и обогащать их недостающими данными. Было очевидно, что данных подход в корне не верный и выгружать сразу миллион записей в List как минимум медленно. Но для эксперимента и замера скорости работы проверить хотелось, чтобы потом сравнить с другими вариантами. Но в результате данный набор записей выгружался около 30 минут после чего было получено OutOfMemoryError и на этом эксперимент завершился.

Даже если бы пользователь задал узкие рамки в параметрах и нам бы удалось выбрать все в один List, то дальше мы бы столкнулись со следующей проблемой для заполнения всех необходимых колонок нужно было бы собирать id пользователей, идти снова в базу, получать их имена и рейтинги, и заполнить уже с полными данными. Сложность такого алгоритма вырастала в разы. Было понятно, что выборку надо производить по частям и переложить все возможные действия с данными на сторону бд. Чтобы не выбирать все разом и, чтобы не городить велосипедов, было решено использовать ScrollableResults. Это позволяет нам получить ссылку на курсор и итерироваться по результатам с определенным шагом. Далее пришлось переписать запрос так, чтобы он возвращал сразу все необходимые данные уже после всех джойнов, объединений, группировок и т. д.

Следующий вопрос где хранить сам текст запроса. Это был не простая ситуация т.к. в действительности количество таблиц, которые участвовали в запросе было около десяти, количество джойнов и всяческих группировок было огромным, в результате чего текст запроса вышел на 200+ строк после ревью всевозможных коллег и утверждении самим тех лидом. Хранить такой запрос в java коде не хотелось, плюс в нем были захардкожены некоторые константы в условиях и светить ими в общем репозитории было бы неправильно. Для решения всех этих вопросов мне на помощь пришла идея использовать view. Весь текст запроса прекрасно туда вписывался, плюс на выходе мы получаем готовую сущность, с которой может работать hibernate как с обычной entity.

По началу все выглядело нормально, запрос на выборку 1 млн таких строк выполнялся за разумные 10 мин. или около того. Немного больше, чем хотелось бы, но заказчика это устраивало. Однако в процессе тестирования обнаружился серьезный минус такого подхода когда мы выбираем 1 млн записей, запрос выполняется 10 минут, но когда мы хотим отчет по короче и указываем в параметрах границы даты поуже у нас запрос так же выполняется 10 минут, но в результате мы можем получить хоть 1 запись, хоть миллион. Суть в том, что внутрь запроса view нельзя передавать параметры, мы можем только выполнить статический запрос и уже на результат наложить параметры. Поэтому не важно сколько будет в результате строк, в первую очередь будет выбрано все, что найдется в бд, а только потом будет применены параметры. Заказчику было все равно, его устраивало и то, что отчет с одной строкой будет формироваться практически за такое же время, что и отчет с 1 млн строк. Однако это излишне нагружало бд и было решено отказаться от этого варианта.

Оставался всего один вариант, который нам подходил это хранимая в бд функция. В нее можно передавать параметры, она может вернуть ссылку на курсор и ее результат можно удобно маппить на нашу entity. Таким образом была описана функция, которая принимала на вход несколько параметров, и возвращала sys_refcursor, весь скрипт занял около 300 строк в реальности, а в упрощенном варианте здесь она выглядит так:

create function message_ref(    date_from timestamp,    date_to timestamp) return sys_refcursor as    ret_cursor sys_refcursor;begin    open ret_cursor for        select m.id,               u.name,               u.rating,               m.message_date,               m.text        from message m                 left join users u on m.user_id = u.id        where m.message_date between date_from and date_to;    return ret_cursor;end message_ref;

Теперь как ее использовать? Для этого отлично подходит @NamedNativeQuery. Запрос для вызова функции выглядит следующим образом: "{ ? = call message_ref(?, ?) }", callable = true дает понять, что запрос представляет собой вызов функции, cacheMode = CacheModeType.IGNORE для указания не использовать кэш, т. к. скорость работы нам не так критична, как затрачиваемая память, ну и в конце resultClass = MessageData.class для маппинга результата на нашу entity. Класс MessageData выглядит следующим образом:

package com.report.generator.demo.repository.entity;import lombok.Data;import org.hibernate.annotations.CacheModeType;import org.hibernate.annotations.NamedNativeQuery;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import java.io.Serializable;import java.time.Instant;import static com.report.generator.demo.repository.entity.MessageData.MESSAGE_REF_QUERY_NAME;@Data@Entity@NamedNativeQuery(    name = MESSAGE_REF_QUERY_NAME,    query = "{ ? = call message_ref(?, ?) }",    callable = true,    cacheMode = CacheModeType.IGNORE,    resultClass = MessageData.class)public class MessageData implements Serializable {    public static final String MESSAGE_REF_QUERY_NAME = "MessageData.callMessageRef";    private static final long serialVersionUID = -6780765638993961105L;    @Id    private long id;    @Column    private String name;    @Column    private int rating;    @Column(name = "MESSAGE_DATE")    private Instant messageDate;    @Column    private String text;}

Для того чтобы не использовать кэш было решено выполнять запрос в StatelessSession. Однако есть важная особенность: если попытаться вызвать namedQuery то hibernate при попытке установить CacheMode выдаст UnsupportedOperationException. Чтобы этого избежать необходимо установить два хинта:

            query.setHint(JPA_SHARED_CACHE_STORE_MODE, null);            query.setHint(JPA_SHARED_CACHE_RETRIEVE_MODE, null);

В итоге наш метод генерации имеет следующий вид:

 @Transactional    void generate(Task task) {        log.info("generating report is started");        try (            StatelessSession statelessSession = sessionFactory.openStatelessSession()        ) {            ReportExcelStreamWriter writer = new ReportExcelStreamWriter();            Query<MessageData> query = statelessSession.createNamedQuery(MESSAGE_REF_QUERY_NAME, MessageData.class);            query.setParameter(1, task.getDateFrom());            query.setParameter(2, task.getDateTo());            query.setHint(JPA_SHARED_CACHE_STORE_MODE, null);            query.setHint(JPA_SHARED_CACHE_RETRIEVE_MODE, null);            ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);            int index = 0;            while (results.next()) {                index++;                writer.createRow(index, (MessageData) results.get(0));                if (index % 100000 == 0) {                    log.info("progress {} rows", index);                }            }            writer.writeWorkbook();            task.setStatus(DONE.toString());            log.info("task {} complete", task);        } catch (Exception e) {            task.setStatus(FAIL.toString());            e.printStackTrace();            log.error("an error occurred with message {}. While executing the task {}", e.getMessage(), task);        } finally {            taskRepository.save(task);        }    }

4. Запись данных в Excel

На данном этапе вопрос с выборкой данных из БД был решен и возник следующий вопрос как теперь все это писать в excel так, чтобы это было быстро и не затратно по памяти. Первая попытка была самой очевидной это использование библиотеки org.apache.poi. Тут все просто: подключаем зависимость

        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>5.0.0</version>        </dependency>

Создаем XSSFWorkbook далее XSSFSheet, из него уже row и так далее. Ничего примечательного, примерный код ниже:

package com.report.generator.demo.service;import com.report.generator.demo.repository.entity.MessageData;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.time.Instant;public class ReportExcelWriter {    private final XSSFWorkbook wb;    private final XSSFSheet sheet;    public ReportExcelWriter() {        this.wb = new XSSFWorkbook();        this.sheet = wb.createSheet();        createTitle();    }    public void createRow(int index, MessageData data) {        XSSFRow row = sheet.createRow(index);        setCellValue(row.createCell(0), data.getMessageDate());        setCellValue(row.createCell(1), data.getName());        setCellValue(row.createCell(2), data.getRating());        setCellValue(row.createCell(3), data.getText());    }    public void writeWorkbook() throws IOException {        FileOutputStream fileOut = new FileOutputStream(Instant.now().getEpochSecond() + ".xlsx");        wb.write(fileOut);        fileOut.close();    }    private void createTitle() {        XSSFRow rowTitle = sheet.createRow(0);        setCellValue(rowTitle.createCell(0), "Date");        setCellValue(rowTitle.createCell(1), "Name");        setCellValue(rowTitle.createCell(2), "Rating");        setCellValue(rowTitle.createCell(3), "Text");    }    private void setCellValue(XSSFCell cell, String value) {        cell.setCellValue(value);    }    private void setCellValue(XSSFCell cell, long value) {        cell.setCellValue(value);    }    private void setCellValue(XSSFCell cell, Instant value) {        cell.setCellValue(value.toString());    }}

Но такой подход оказался не очень оптимальным. Примерно 3 минуты потребовалось на выборку 1 млн строк из бд и запись их в excel. И в итоге приводил к OutOfMemoryError. Вот пример:

А когда я выполнял его на терминалке с выделенной оперативной памятью в 2Gb, то падал он с OutOfMemoryError примерно на 30% прогресса.

Грузить весь миллион строк в память в excel было так же плохой идеей, как и выгружать весь запрос в List, очевидно, здесь надо было использовать некий stream, но хоть какой-то годный пример google тогда мне не дал. Была попытка написать свое подобие I/O Stream для работы с excel, но мысль о том, что я пишу велосипед не давала мне покоя. В результате я стал изучать библиотеку org.apache.poi пристальней и оказалось, что там уже есть пакет streaming. В этом пакете уже есть весь необходимый набор классов для работы с большим объемом данных в excel. Оставалось только заменить все ключевые классы на аналогичные из пакета streaming и все:

package com.report.generator.demo.service;import com.report.generator.demo.repository.entity.MessageData;import org.apache.poi.xssf.streaming.SXSSFCell;import org.apache.poi.xssf.streaming.SXSSFRow;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.time.Instant;public class ReportExcelStreamWriter {    private final SXSSFWorkbook wb;    private final SXSSFSheet sheet;    public ReportExcelStreamWriter() {        this.wb = new SXSSFWorkbook();        this.sheet = wb.createSheet();        createTitle();    }    public void createRow(int index, MessageData data) {        SXSSFRow row = sheet.createRow(index);        setCellValue(row.createCell(0), data.getMessageDate());        setCellValue(row.createCell(1), data.getName());        setCellValue(row.createCell(2), data.getRating());        setCellValue(row.createCell(3), data.getText());    }    public void writeWorkbook() throws IOException {        FileOutputStream fileOut = new FileOutputStream(Instant.now().getEpochSecond() + ".xlsx");        wb.write(fileOut);        fileOut.close();    }    private void createTitle() {        SXSSFRow rowTitle = sheet.createRow(0);        setCellValue(rowTitle.createCell(0), "Date");        setCellValue(rowTitle.createCell(1), "Name");        setCellValue(rowTitle.createCell(2), "Rating");        setCellValue(rowTitle.createCell(3), "Text");    }    private void setCellValue(SXSSFCell cell, String value) {        cell.setCellValue(value);    }    private void setCellValue(SXSSFCell cell, long value) {        cell.setCellValue(value);    }    private void setCellValue(SXSSFCell cell, Instant value) {        cell.setCellValue(value.toString());    }}

Теперь сравним скорость обработки данных с этой библиотекой:

Вся обработка заняла пол минуты и, самое главное, никаких OutOfMemoryError.

5. Итог

В результате удалось добиться максимальной производительности за счет использования хранимой функции, StatelessSession, ScrollableResults и использования библиотеки org.apache.poi из пакета streaming. При большом желании можно улучшить производительность еще, если написать все на чистом jdbc, может быть есть еще варианты, как, что и где можно улучшить. Буду рад услышать комментарии от более опытных в этом экспертов. В данном примере не учтено ограничение на 1 млн. строк, т. к. это простая формальность и для примера не очень важна. Для наполнения БД тестовыми данными был добавлен тестовый класс DemoApplicationTests. Весь код можно посмотреть в репозитории на GitHub.

Я уже ранееписал о том, что придумал движок, который позволяет работать с не 1С SQL-базой из клиентов, которыми являются базовые конфигурации 1С:Деньги. Думаю, это классное решение для небольших частных или малотиражных конфигураций! Я назвал его Базовый Учет или Базуха (Базовый Учет Хозяйства).

Решение дешево. 1С:Деньги на каждое рабочее место обойдется в 300 рублей. Сравните с лицензией на 1С, которая стоит 13.000 рублей + 5.000 рублей за каждое дополнительное место.

Так, например, лицензия на 10 пользователей для Базухи обойдется в 3.000 рублей, а для 1С в 28.000 рублей, т.е. по 2.800 рублей на каждого пользователя! Экономия в 10 раз!

Решениелучше чем 1С по производительности в эконом-сегменте. В дешевых 1С используется файловая база, которая работает медленно, т.к. нет посредника в виде SQL-сервера, который обеспечивает быстрое извлечение данных.

Чтобы получить сервер SQL, нужно заплатить достаточно большую сумму. Сервер 1С на 5 подключений стоит 14.000, а на большее количество подключений 86.400 (32-разрядный сервер за 50.400 не рассматриваем, его покупка не имеет смысла). Поэтому в нашем примере на 10 пользователей для нормальной производительности стоимость одного рабочего места уже получается 11.440 (учтена стоимость лицензии на сервер 1С).

Почему-то 1С не предлагает решения, где не используется сервер 1С, а используется только SQL-сервер. Базуха как раз использует такой подход. Он медленнее, чем трехзвенка с сервером 1С (потому что нельзя исполнять код на сервере), но быстрее, чем файловая база 1С (потому что можно исполнять SQL-запросы).

Однако,справедливости ради, следует сказать, что если многопользовательский доступ к файловой базе будет реализован через веб-сервер, то производительность будет сопоставима с Базухой. Веб-сервер является как бы сервером приложений, но у Базухи есть SQL-сервер. Так что сопоставимо. Причем Базуха лучше масштабируется на количество пользователей больше 10, а файловая даже через веб-сервер начинает на таких масштабах задыхаться, т.к. у нее нет SQL-сервера.

Решениеиспользует код и архитектуру 1С. Это важно, потому что поддерживать его может любой программист 1С, потратив буквально пару часов на освоение документации по платформе Базухи. Кроме того, можно использовать все выразительные средства 1С в разработке таблицы значений, МХЛ-файлы, богатые средства 1С для интеграции. Да и пользователю привычен знакомый интерфейс 1с.

Решениеболее свободно чем 1С. Для разработки можно использовать любые модели данных, а не только придуманные 1С прикладные объекты (регистры, планы счетов, планы обмена и т.п.). Можно использовать свои инструменты для разработки, можно делать абстракции кода, выполняя не код, а схемы кода, собственные иерархии данных.

Решение может служитьмостиком от 1С к другим средствам разработки. Учитывая открытость архитектуры базы данных и кода Базухи, со временем можно написать движок, который будет работать с базами Базухи абсолютно свободно, без 1С. В этом смысле Базуха является переходной программой, которая позволяет упростить этот процесс, потому что сейчас подходящего инструмента разработки, увы, нет.

Для кого предназначена Базуха?

Понятно, что конфигурация только начинается, поэтому не может конкурировать с такими монстрами, как УТ, БП, ERP, в которых вложено много труда и ресурсов. Но она и не предназначена для этого. Известно, что 1С предлагает максимально быстрый и удобный инструмент для разработки приложений баз данных. Но, к сожалению, лицензионная политика не позволяет его использовать для мелких, небольших решений.

Базуха закрывает этот вопрос и позволяет реализовывать небольшие заказные или малотиражные решения.

1С тоже позволяет делать недорогие тиражные решения, но для этого нужно предоставить решение самой 1С, она проверит его на 1С:Совместимо и если посчитает невыгодным распространение этой конфигурации, не выдаст базовую лицензию. Кроме того, такое мероприятие затратно по ресурсам.

С помощью Базухи вы можете выпустить недорогое тиражное решение буквально для нескольких клиентов, не прибегая к посредничеству 1С.

Также на Базухе очень удобно писать интернет-магазин, который одновременно является и учетной базой. Потому что все это крутится на одном MySQL сервере в интернете.

Лицензионная чистота

Для выяснения лицензионной чистоты решения я обратился в 1С-франчайзи Первый Бит. Сперва они не поняли, о чем я веду речь, но после телефонного общения с техническим специалистом вынесли вердикт: Что не запрещено, то разрешено. При этом уточнили, что их ответ является ответом, выражающим политику 1С.

Но на всякий случай я обратился и в 1С (lic@1c.ru). Там тоже не нашли возражений против моей схемы, после долгих и муторных споров со стороны 1С было вынесено решение:

С одной стороны, при популяризации решения оно будет выступать как конкурент 1С. С другой будет занимать нишу, куда 1С не пробиться с ее текущей стоимостью лицензий на нишу мелких производительных решений.Будем надеяться на благоразумие 1С!

В конечном итоге, появление подобного решения вызвано именно неповоротливостью маркетинга и невозможностью создавать дешевые малотиражные решения.

Вот текст лицензии на базовые версии 1С:

Как устроена Базуха?

Каждому пользователю устанавливается базовая программа 1С:Деньги.

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

1. Создает два пользователя admin (с паролем) и user (без пароля). admin имеет административные права, user полные.

2. Из специального настроечного файла в базу пользователя заносятся пароль администратора, логин и пароль доступа к SQL-базе, тип SQL-базы.

3. Добавляет основную обработку по работе с SQL базой данных.

4. Выполняет первоначальную синхронизацию с SQL базой данных.

5. Прописывает на рабочий стол ярлык для запуска 1С:Деньги под пользователем user с открытием списка дополнительных внешних обработок.

Окно запуска 1С:Базухи для пользователя выглядит так:

К сожалению, сразу открыть обработку Рабочий стол не получится технически, поэтому пользователь должен запускать ее вручную кликом.

При запуске обработки Рабочий стол пользователь вводит логин и пароль для доступа к SQL-базе. И начинает работу в этой базе добавляет/изменяет/просматривает данные и отчеты.

Код представляет собой совокупность обработок, хранящихся в SQL-базе. Каждая обработка имеет свою версию. Если версия обработки меньше, чем версия обработки в базе данных, она обновляется в локальную базу из SQL-базы.

Конфигуратор системы создает описание структуры данных. По команде это описание отображается на базу, создаются и модифицируются необходимые таблицы. Создаются новые поля, таблицы и индексы, ненужные удаляются. Можно использовать систему для подключения к уже существующей базе и таблицам.

Формы элемента рисуются в обычном конфигураторе 1С. Соответствие данных прописывается кодом. Соответственно, система знает, какие данные из каких полей отображать в полях.

Формы списка не поддерживают динамическое считывание данных, считывают данные порциями, постранично.

Отчеты выглядят как типовые 1С-отчеты, можно использовать даже систему компоновки данных (СКД).

Печатные формы документов программируются также на 1С и выводятся в табличные документы MXL.

Модули объектов (справочников и документов) хранятся в дополнительных обработках.

Особенности реализации Базухи

Для вопросовбезопасностиважно, чтобы в базе-носителя у пользователя не было лишних доступов, чтобы он не мог менять код. Несмотря на то, что у пользователя User полные права, он не может отлаживать код и менять код дополнительных внешних обработок. В этоп плане SQL база данных защищена от несанкционированного доступа.

Для вопросовбыстродействияважно, чтобы COM-соединение с SQL-базой данных было постоянно открыто. 1С:Деньги запускаются в режиме толстого клиента, при этом в серверном коде внешних обработок становятся доступными переменные приложения, т.к. можно постоянно хранить соединение с SQL-базой данных, а не создавать его каждый раз, что увеличивает скорость запросов к SQL-базе. Кроме того, это позволяет кэшировать дополнительные обработки, где хранится код приложения.

В качествесерверапланируется использовать любой SQL-сервер, желательно бесплатный. В начале My-SQL, потому что его проще приобрести пользователю (обычно предоставляются при покупке хостинга). И администрировать его практически не нужно. Далее, можно посмотреть в сторону Postgree.