Субд

Продолжаем беседовать с разработчиками экосистемы сервисов Serverless. В начале нашего путешествия Глеб Борисов описал ситуацию с Yandex Cloud Function, затем Данил Ошеров погрузил нас в мир протокола S3 и сервиса Object Storage, а сегодня Андрей Фомичев поделится подробностями о NewSQL.

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

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

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

Для меня облако это не просто возможность быстро создать виртуалочку, а потом удалить её. А его сервисная сущность, когда мы рассматриваем Platform as a Service, базы данных и так далее. Это те сервисы, которые человек и сам себе может организовать, но стоить это будет неимоверно дорого.

Если мы говорим об эволюции использования баз данных, мой путь начался с СУБД Berkeley DB. Очень старая, немного эзотерическая, но очень опенсорсная в своё время. Потом были и MySQL, и PostgreSQL. В основном применял их как записную книжку для данных с удобным поиском. А с каких баз данных ты начинал?

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

Тогда технология XML была на пике популярности. Мы использовали XML-файлы. Но однажды файлов стало так много, что потребовалась база данных, которая могла бы хранить, обрабатывать их и поддерживать декларативный язык запросов. Таким языком был XQuery, он был достаточно мощным, даже мощнее SQL. Многие, наверно, знают XPath, это такое подмножество XQuery. И среди тех, кто работал с XML, XPath очень популярен.

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

По факту все эти СУБД должны хорошо решать задачу масштабирования. А есть распределённые системы, построенные поверх клиент-серверных?

Если брать классические клиент-серверные базы данные, то многие из них (например, PostgreSQL) не распределённые. Они обеспечивают отказоустойчивость и масштабирование, но это лишь дополнения к основной функциональности. Они очень хорошо реализованы: стандартные способы, технологии репликации лога всё работает. Но эти механизмы появились потом, и поэтому такие базы данных на write-нагрузку масштабируются очень ограниченно или почти не масштабируются.

Если же мы касаемся именно распределённых систем, то для них есть свои интересные классификации.

К примеру, есть деление на shared nothing и shared что-нибудь. В случае с shared storage, например, можно подключить полку к EMC или NetApp, хранить там данные, и такое решение будет работать. И это хорошее enterprise-решение, оно масштабируется, но с нюансами. Shared nothing это узлы или машины, которые соединены только сетью. Идеальный кейс для отличного масштабирования это shared nothing: cluster, commodity, hardware классика жанра.

Этот класс систем появился почти одновременно у нескольких компаний. Откуда вообще возникла такая потребность?

Традиционно считается, что она возникла с появлением и развитием интернета. Неудивительно, что в ряду производителей есть Яндекс, который также приложил много усилий к развитию интернета. Если погружаться, то история появления Yandex Database (сокращённо YDB) длинная.

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

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

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

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

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

Да, конечно. Вот классика: у нас есть key-value база данных. Я по ключу меняю значение, а мой сосед, который сидит рядом, за другим компьютером, его читает. Возникает вопрос: сосед увидит старое значение, которое я записал, или какие-то спецэффекты, особенно если в кластере что-то произойдёт? Усложняем задачу: я транзакционно пишу две записи, и у меня два ключа два значения, которые я хочу поменять транзакционно. Например, у меня есть два счёта, я перекладываю сто рублей с одного на другой. Что увижу я и что увидит наблюдатель, мой сосед?

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

Получается, изначально вы ставили задачу обрабатывать данные от веб-роботов? И придумали решение, которое позволило распределённо обрабатывать большие объёмы данных, причём отказоустойчиво?

К задаче надо добавить ещё одно важное обстоятельство, которое стало стартовым катализатором: требование real-time. Real-time, понятно, условный. И в контексте YDB мне больше нравится говорить не real-time, а интерактивность. Система должна обеспечивать все эти характеристики так, чтобы она могла интерактивно взаимодействовать с человеком.

Представь: ты разрабатываешь сложный сервис для миллионов пользователей. Хороший пример соцсеть, хотя есть много других веб-сервисов с похожей сложностью. Тебе нужно хранить данные в базе данных и обеспечивать отказоустойчивость с оглядкой на реакцию пользователя. Он подружился с кем-нибудь или сохранил запись и ты хочешь, чтобы это обрабатывалось за 10 или 100 миллисекунд, чтобы для пользователя всё происходило очень быстро.

Каждый крупный поисковик или соцсеть сталкивается с такой задачей. Но чем YDB полезна для простого разработчика на Java, Python или Go? Он говорит: Я приду завтра в свою контору, у нас там CRM-система всего на сто-двести-триста одновременно работающих пользователей. Что ему от такой СУБД?

Тут как раз много аспектов и нюансов. Архитектура в основе YDB очень интересная. Каждая инсталляция отвечает не за определённый объём данных (допустим, десять терабайт), которые хранятся локально. Система состоит из очень большого числа маленьких объектов tablet. Tablet реализует некий консенсус, и он хранит условно пару гигабайтов данных. Это означает, что Yandex Database может расти до очень больших значений, но быть маленькой и эффективной. Благодаря этому ты получаешь гибкость: если сервис вырастет в десятки, сотни, тысячи раз ты масштабируешься быстро и удобно. Но пока сервис маленький, он и стоить будет дёшево. Мне эта мысль очень нравится, она ко мне не сразу пришла.

Вот чем хороши виртуальные машины? Чем хороши облака? Чем хороша виртуализация в общем смысле?

Раньше было как? Хочешь запустить систему на сервере купи сервер. У него десятки (а сейчас встречаются и сотни) ядер, и есть вероятность, что тебе не нужно столько ресурсов, а они дорогие и сложные, требуют обслуживания... А виртуализация позволяет запустить виртуалку всего на два, четыре, шесть, восемь ядер и так далее: ровно по потребности. И если база данных начинает тебе предоставлять то же самое, что и виртуализация (CPU, RAM, диск), то это же по-настоящему удобно. Прямо по-настоящему.

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

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

Мы очень плавно перешли к serverless-составляющей YDB.

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

Serverless-решение по-другому работает и иначе масштабируется. Serverless YDB это managed-сервис. Появился он следующим образом: для YDB как для продукта нужна ниша, и очевидно, что она есть. YDB используют в Яндексе и Yandex.Cloud для масштабных сервисов. Понятно, что мало каким потребителям и клиентам нужны кластеры из десятков и сотен машин. Но serverless-парадигма позволяет большому количеству пользователей проектировать работу со всеми возможностями YDB.

Например, быстрый failover. В YDB шард очень маленький, и вся система устроена таким образом, что даже в dedicated-варианте, когда всё работает на выделенных виртуальных машинах, эти маленькие сущности удобно и легко балансировать. Они переезжают с машины на машину и поднимаются за доли секунды. Если вы пришли, создали таблицу и положили туда 100 МБ, все эти 100 МБ будут жить в одном шардике. YDB реализована с применением модели акторов этот шардик будут обслуживать несколько акторов. Она потребляет мало CPU и обходится дёшево с точки зрения аппаратных ресурсов.

Для небольших проектов это очень выгодно. К тому же у нас есть Free Tier: каждый месяц можно выполнить 1 миллион операций (в единицах RU) бесплатно. А крупные и высоконагруженные проекты могут быстро масштабироваться в зависимости от пиковых нагрузок.

Интересный подход использовать в тарификации условные единицы. Можешь рассказать, что такое RU и как они пересчитываются в рубли?

RU это request unit, некая условная единица. Фактически это стоимость чтения по ключу нескольких килобайт данных. Бесплатно во Free Tier дается миллион таких RU. Если запрос сложнее, чем просто чтение по ключу, то его стоимость пропорционально увеличивается. Так и происходит тарификация. Но миллион простых запросов в месяц это достаточно много. И простенький микросервис (или даже не совсем простенький) во Free Tier легко укладывается.

Давай под конец сформулируем: зачем современному разработчику сервисов в облаке использовать YDB?

Из-за многих причин. Возможность бесконечно масштабироваться. Меньше заботы о работоспособности часто самого критичного и сложного компонента сервиса базы данных (обычно, если база данных падает или перестаёт работать, всё становится плохо и сложно).

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

Причём (я немножко похвастаюсь) YDB на голову выше других систем, в том числе serverless. Их на рынке немного. И далеко не все из них технически продвинуты. Взять хотя бы Amazon DynamoDB это NoSQL нереляционная база данных. Там нет SQL, и она этим проигрывает. У Google Cloud Spanner нет публичной serverless-версии. Поэтому Yandex.Cloud предлагает по-настоящему интересную, современную и в чём-то уникальную технологию.

Итак, с YDB разработчик получает: распределённую NewSQL СУБД с поддержкой бессерверных вычислений, высокую доступность, масштабируемость, поддержку строгой консистентности и ACID-транзакций. А в дополнение ещё и API-сервис, в режиме бессерверных вычислений совместимый с API Amazon DynamoDB. Ну а подробности о новых шагах Serverless-сервисов, подробностях и нюансах разработки можно узнать в сообществе Serverless в Telegram:Yandex Serverless Ecosystem.

Введение

Базы данных это Святой Грааль для хакеров, поэтому их необходимо защищать с особой тщательностью. Это первая из серии статей, в которых мы дадим обзор best practice в обеспечении безопасности баз данных. Мы начнем с одной из самых популярных СУБД с открытым исходным кодом, PostgreSQL, и рассмотрим несколько уровней безопасности, о которых стоит задуматься:

• Безопасность на сетевом уровне

• Безопасность на транспортном уровне

• Безопасность на уровне базы данных

Безопасность на сетевом уровне

Файрволы

В идеале ваш сервер PostgreSQL должен быть полностью изолированным и не допускать никаких входящих подключений, SSH или psql. К сожалению, PostgreSQL не поддерживает стандартную настройку такого типа.

Следующий способ, который можно предпринять для повышения безопасности сервера базы данных, это заблокировать доступ к узлу, на котором работает база данных, на уровне порта, с помощью брандмауэра. По умолчанию, PostgreSQL прослушивает TCP-порт 5432. В зависимости от операционной системы существуют разные способы блокировки других портов. В Linux можно использовать iptables - наиболее доступную утилиту для управления файрволом:

# Make sure not to drop established connections.iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT# Allow SSH.iptables -A INPUT -p tcp -m state --state NEW --dport 22 -j ACCEPT# Allow PostgreSQL.iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -j ACCEPT# Allow all outbound, drop everything else inbound.iptables -A OUTPUT -j ACCEPTiptables -A INPUT -j DROPiptables -A FORWARD -j DROP

Примечание. При обновлении правил iptables рекомендуется использовать iptables-apply, который автоматически откатывает изменения в случае, если вы случайно заблокируете себя.

Приведенное выше правило PostgreSQL позволит любому подключаться к порту 5432. Его можно сделать более строгим, принимая подключения только с определенных IP-адресов или подсетей:

# Only allow access to PostgreSQL port from the local subnet.iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -s 192.168.1.0/24 -j ACCEPT

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

Такой метод называется обратным туннелированием. Его можно продемонстрировать при помощи удаленного перенаправления портов. Вы можете открыть обратный туннель, выполнив следующую команду с узла, на котором работает база данных PostgreSQL:

ssh -f -N -T -R 5432:localhost:5432 user@<client-host>

Конечно, <client-host> должен быть доступным из узла PostgreSQL и иметь запущенного SSH-демона. Команда перенаправит порт 5432 на сервере базы данных на порт 5432 на клиентском компьютере, и вы сможете подключиться к базе данных через туннель:

psql "host=localhost port=5432 user=postgres dbname=postgres" 

Прослушивание адресов

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

listen_addresses = 'localhost, 192.168.0.1' 

Если клиенты, подключающиеся к базе данных, всегда находятся на одном и том же узле (или, скажем, совместно расположены в одном поде Kubernetes с PostgreSQL, работающим в качестве дополнительного контейнера), отключение прослушивания сокетов TCP может полностью исключить сеть из рассматриваемой картины. Запись пустой строки в параметр прослушиваемых адресов заставляет сервер принимать только соединения сокетов домена Unix:

listen_addresses = ''

Безопасность на транспортном уровне

Поскольку большая часть всемирной паутины переходит на HTTPS, найдется мало оправданий тому, чтобы не использовать надежное шифрование для соединений с базой данных. PostgreSQL поддерживает TLS (который по-прежнему называется SSL в документации, конфигурации и CLI по legacy-причинам) и позволяет использовать его для аутентификации как сервера, так и клиента.

Серверный TLS

Для аутентификации сервера сначала необходимо получить сертификат, который сервер будет предоставлять подключающимся клиентам. Let's Encrypt упрощает получение бесплатных сертификатов X.509, например, с помощью инструмента CLI certbot:

certbot certonly --standalone -d postgres.example.com

Учтите, что по умолчанию certbot использует вызов HTTP-01 ACME для проверки запроса сертификата, который требует действительного DNS для запрошенного домена, указывающего на узел, и порт 80, который должен быть открыт.

Если по какой-то причине вы не можете использовать Let's Encrypt и хотите сгенерировать все сертификаты локально, то можно использовать openssl CLI:

# Make a self-signed server CA.openssl req -sha256 -new -x509 -days 365 -nodes \    -out server-ca.crt \    -keyout server-ca.key# Generate server CSR. Put the hostname you will be using to connect to# the database in the CN field.openssl req -sha256 -new -nodes \    -subj "/CN=postgres.example.com" \    -out server.csr \    -keyout server.key# Sign a server certificate.openssl x509 -req -sha256 -days 365 \    -in server.csr \    -CA server-ca.crt \    -CAkey server-ca.key \    -CAcreateserial \    -out server.crt

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

Клиентский TLS

Аутентификация клиента по сертификату позволяет серверу проверить личность подключающегося, подтверждая, что сертификат X.509, представленный клиентом, подписан доверенным центром сертификации (CA).

Рекомендуется использовать разные центры сертификации для выдачи сертификатов клиенту и серверу, поэтому давайте создадим клиентский CA и воспользуемся им для подписи сертификата клиента:

# Make a self-signed client CA.openssl req -sha256 -new -x509 -days 365 -nodes \    -out client-ca.crt \    -keyout client-ca.key# Generate client CSR. CN must contain the name of the database role you# will be using to connect to the database.openssl req -sha256 -new -nodes \    -subj "/CN=alice" \    -out client.csr \    -keyout server.key# Sign a client certificate.openssl x509 -req -sha256 -days 365 \    -in client.csr \    -CA client-ca.crt \    -CAkey client-ca.key \    -CAcreateserial \    -out client.crt

Обратите внимание, что поле CommonName (CN) сертификата клиента должно содержать имя учетной записи базы данных, к которой подключается клиент. Сервер PostgreSQL будет использовать его для идентификации клиента.

Конфигурация TLS

Собрав все части вместе, теперь вы можете настроить сервер PostgreSQL для приема соединений TLS:

ssl = onssl_cert_file = '/path/to/server.crt'ssl_key_file = '/path/to/server.key'ssl_ca_file = '/path/to/client-ca.crt'# This setting is on by default but its always a good idea to# be explicit when it comes to security.ssl_prefer_server_ciphers = on# TLS 1.3 will give the strongest security and is advised when# controlling both server and clients.ssl_min_protocol_version = 'TLSv1.3'

Последняя часть настройки обновить файл host-based аутентификации сервера PostgreSQL (pg_hba.conf), чтобы требовать TLS для всех подключений и аутентифицировать клиентов с помощью сертификатов X.509:

# TYPE  DATABASE        USER            ADDRESS                 METHODhostssl all             all             ::/0                    certhostssl all             all             0.0.0.0/0               cert

Теперь пользователи, подключающиеся к серверу, должны будут предоставить действительный сертификат, подписанный клиентским CA:

psql "host=postgres.example.com \      user=alice \      dbname=postgres \      sslmode=verify-full \      sslrootcert=/path/to/server-ca.crt \      sslcert=/path/to/client.crt \      sslkey=/path/to/client.key"

Стоит обратить внимание, что по умолчанию psql не будет выполнять проверку сертификата сервера, поэтому для параметра sslmode необходимо установить значение verify-full или verify-ca, в зависимости от того, подключаетесь ли вы к серверу PostgreSQL, используя то же имя хоста, которое закодировано в поле CN его сертификата X.509.

Чтобы уменьшить размер команд и не вводить пути к сертификатам TLS каждый раз, когда вы хотите подключиться к базе данных, вы можете использовать файл службы подключения PostgreSQL. Он позволяет группировать параметры подключения в службы, на которые затем можно ссылаться в строке подключения через параметр служба.

Создайте ~/.pg_service.confсо следующим содержанием:

[example]host=postgres.example.comuser=alicesslmode=verify-fullsslrootcert=/path/to/server-ca.crtsslcert=/path/to/client.crtsslkey=/path/to/client.key

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

  psql "service=example dbname=postgres"  

Безопасность на уровне базы данных

Роли

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

PostgreSQL имеет комплексную систему прав пользователя, основанную на концепции ролей. В современных версиях PostgreSQL (8.1 и новее) роль является синонимом пользователя, поэтому любое имя учетной записи базы данных, которое вы используете, скажем, с psql (например, user = alice), на самом деле является ролью с LOGIN атрибутом, который позволяет ей подключаться к базе данных. Фактически, следующие команды SQL эквивалентны:

CREATE USER alice;CREATE ROLE alice LOGIN;

Помимо возможности входа в систему, роли могут иметь иные атрибуты, позволяющие им обходить все проверки прав доступа (SUPERUSER), создавать базы данных (CREATEDB), создавать другие роли (CREATEROLE) и другие.

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

Предоставление роли прав доступа

В нашем воображаемом примере мы будем отслеживать инвентаризацию серверов:

CREATE TABLE server_inventory (    id            int PRIMARY KEY,    description   text,    ip_address    text,    environment   text,    owner         text,);

По умолчанию, конфигурация PostgreSQL включает роль суперпользователя (обычно называемую postgres), используемую для начальной загрузки базы данных. Использование этой роли для всех операций с базой данных было бы эквивалентно постоянному использованию root в Linux, что никогда не являлось хорошей идеей. Вместо этого давайте создадим непривилегированную роль и при необходимости назначим ей права доступа, следуя принципу наименьших привилегий.

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

-- Create a group role that doesn't have ability to login by itself and-- grant it SELECT privileged on the server inventory table.CREATE ROLE developer;GRANT SELECT ON server_inventory TO developer;-- Create two user accounts which will inherit "developer" permissions upon-- logging into the database.CREATE ROLE alice LOGIN INHERIT;CREATE ROLE bob LOGIN INHERIT;-- Assign both user account to the "developer" group role.GRANT developer TO alice, bob;

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

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

CREATE ROLE intern;GRANT SELECT(id, description) ON server_inventory TO intern;CREATE ROLE charlie LOGIN INHERIT;GRANT intern TO charlie;

Другие наиболее часто используемые привилегии объектов базы данных INSERT, UPDATE, DELETE и TRUNCATE аналогичны соответствующим SQL выражениями. Также вы можете назначить права для подключения к определенным базам данных, создания новых схем или объектов в схеме, выполнения функции и так далее. Взгляните на раздел Privileges документации PostgreSQL, чтобы увидеть весь список.

Безопасность на уровне строк

Одной из наиболее продвинутых функций системы привилегий PostgreSQL является безопасность на уровне строк, которая позволяет вам предоставлять привилегии подмножеству строк в таблице. Сюда входят как строки, которые могут быть запрошены с помощью оператора SELECT, так и строки, которые могут быть результатами операторов INSERT, UPDATE и DELETE.

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

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

ALTER TABLE server_inventory ENABLE ROW LEVEL SECURITY;

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

Политика безопасности строк это логическое выражение, которое PostgreSQL будет применять для каждой строки, которая должна быть возвращена или обновлена. Строки, возвращаемые оператором SELECT, проверяются на соответствие выражению, указанному в разделе USING, в то время как строки, обновленные операторами INSERT, UPDATE или DELETE , проверяются на соответствие с WITH CHECK выражением.

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

CREATE POLICY select_all_servers    ON server_inventory FOR SELECT    USING (true);CREATE POLICY update_own_servers    ON server_inventory FOR UPDATE    USING (current_user = owner)    WITH CHECK (current_user = owner);

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

Аудит

До сих пор мы в основном говорили о превентивных мерах безопасности. Следуя краеугольным принципам безопасности defence in depth, мы рассмотрели, как они накладываются друг на друга, чтобы замедлить продвижение гипотетического злоумышленника через систему.

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

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

; Log successful and unsuccessful connection attempts.log_connections = on; Log terminated sessions.log_disconnections = on; Log all executed SQL statements.log_statement = all

К сожалению, это практически всё, что можно сделать с помощью стандартной self-hosted установки PostgreSQL из коробки. Конечно, это лучше, чем ничего, но данный метод не масштабируется дальше нескольких серверов баз данных и простого grepping.

Для более продвинутого аудита PostgreSQL вы можете использовать сторонние решения, такие как pgAudit . Если вы используете self-hosted экземпляр PostgreSQL, вам придется установить расширение вручную. Некоторые размещенные версии, такие как AWS RDS, поддерживают его прямо из коробки, поэтому вам просто нужно включить его.

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

Заключение

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

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

Одновременный сеанс в IRIS: SQL, объекты, REST, GraphQL

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

Казимир Малевич (1916)

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

Есть несколько соображений о том, почему стоит поговорить про обмен данными через API на основе SQL/REST/GraphQL, в противовес представлению их в виде типов/объектов:

• это массово изучаемые и достаточно легко используемые технологии;

• их широкая популярность в доступных и открытых программных продуктах просто невероятна;

• часто, особенно в вебе и в базах данных, у вас просто нет выбора;

• или наоборот, когда у вас всё ж таки есть выбор его надо сделать осознанно:

• и главное, внутри в границах этих API остаются объекты, как наиболее адекватный способ их реализации в коде.

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

Сегодня, данные хранятся, как давно повелось, на жёстких дисках в HDD или, уже по современному, в микросхемах флеш-памяти в SSD. Данные пишутся и читаются потоком, состоящем из отдельных блоков хранения на HDD/SSD.

Деление на блоки не случайное, а задаваемое физикой/механикой/электроникой накопителя данных. В HDD это дорожки/сектора на вращающемся магнитном диске. В SSD это сегменты памяти в перезаписываемом кремниевом чипе. Суть одна это блоки информации, из частей которых необходимо найти и собрать воедино нужные нам кусочки данных. Собрать в структуры, которые соответствуют нашей модели/типу данных со значением необходимым на момент запроса. За этот процесс как раз отвечает связка из СУБД и файловой подсистемы в операционной системе.

По правде говоря, можно обращаться и минуя СУБД напрямую к операционной системе или даже напрямую к HDD/SSD. Но тогда мы теряем два супер важных мостика к данным первый, между блоками хранения и файловыми потоками, и, второй, между файлами и упорядоченной структурой в модели базы данных. Или, говоря другими словами, мы берём на себя ответственность по разработке всего этого объёма кода для обработки блоков/файлов/моделей со всеми оптимизациями, тщательной отладкой и долговременными испытаниями на надёжность.

Так вот, СУБД дают нам прекрасную возможность обращаться с данными на языке высокого уровня сразу оперируя понятными моделями и представлениями. Хвала им за это. А хорошие СУБД и платформы данных, такие как InterSystems IRIS, предоставляют больше доступ к упорядоченным данным сразу множеством способов одновременно. И выбор уже за программистом, какой из них выбрать для своего проекта.

Так воспользуемся же множественными привилегиями, которые даёт нам IRIS. Сделаем код красивее и полезнее сразу будем применять объектно-ориентированные язык ObjectScript для использования и разработки API. То есть, например, SQL код будем вызывать прямо изнутри программы на ObjectScript. А для других API воспользуемся готовыми библиотеками и встроенными средствами ObjectScript.

Для примеров будем использовать данные из замечательного интернет-проекта "SQL Zoo", в котором обучают языку запросов SQL. Эти же данные будем использовать в других примерах API.

Если хочется сразу посмотреть на многообразие подходов к проектированию API и воспользоваться готовыми решениями, то вот интересная и полезная коллекция публичных API, которая коллективно собирается в проекте на гитхабе https://github.com/public-apis/public-apis

SQL

Начинаем вполне естественно с SQL. Кто ж его не знает?

Для изучения SQL есть гигантское количество учебных курсов и книг. Мы будем опираться на https://sqlzoo.net. Это хороший начальный онлайн курс по SQL с примерами, прохождением заданий и справочником языка.

Будем переносить задачки из SQLZoo на платформу IRIS и получать аналогичные решения разными способами.

Как быстро получить доступ к InterSystems IRIS на своём компьютере? Один из самых быстрых вариантов развернуть контейнер в докере из готового образа InterSystems IRIS Community Edition бесплатная версия InterSystems IRIS Data Platform

Другие способы получить доступ к InterSystems IRIS Community Edition на портале обучения.

Перенесём данные с SQLZoo в хранилище нашего собственного экземпляра IRIS. Для этого:

1. Открываем портал управления (у меня, например, по ссылке http://localhost:52773/csp/sys/UtilHome.csp),

2. Переключаемся на область USER - в Namespace %SYSнажимаем ссылку Switch и выбираем USER

3. Переходим в меню Система > SQL - открываем Обозреватель системы, затем SQL и жмём кнопку Запустить.

4. Справа на будет открыта закладка "Исполнить запрос" с кнопкой "Исполнить" - она то нам и нужна.

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

Кстати, другим, удобным вам, способом попробовать SQL доступ к базе данных в IRIS, может оказаться популярный у разработчиков редактор Visual Studio Code с плагином SQLTools и драйвером "SQLTools Driver for InterSystems IRIS". Попробуйте этот вариант.

Инструкция как настроить доступ к IRIS и разработке на ObjectScript в VSCode.

Готовые скрипты для развёртывания базы данных и набора тестовых данных SQLZoo есть в описании на сайте в разделе Данные.

Пара прямых ссылок для таблицы World:

• http://sqlzoo.net/w/index.php?title=Createworld.txt&action=raw a script to create the world database

• http://sqlzoo.net/w/index.php?title=Tabworld.txt&action=raw the data to go in that table

Скрипт для создания базы данных можно выполнить тут же на портале управления IRIS в форме "Исполнитель запросов".

CREATE TABLE world(

name VARCHAR(50) NOT NULL

,continent VARCHAR(60)

,area DECIMAL(10)

,population DECIMAL(11)

,gdp DECIMAL(14)

,capital VARCHAR(60)

,tld VARCHAR(5)

,flag VARCHAR(255)

,PRIMARY KEY (name)

)

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

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

SELECT * FROM world

Теперь нам доступны примеры и задания с сайта "SQL Zoo". Все примеры ниже реализуют SQL запрос из первого задания:

SELECT population

FROM world

WHERE name = 'France'

Так что можете смело продолжать начатое нами исследование API перенося задания из SQLZoo на платформу IRIS.

Внимание! Как я обнаружил, данные в интерфейсе сайта SQLZoo и данные его экспорта отличаются. Как минимум в первом примере расходятся значения по населению Франции и Германии. Не переживайте. Вот данные Евростата для ориентировки.

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

Class User.worldquery

{

ClassMethod WhereName(name As %String)

{

&sql(

SELECT population INTO :population

FROM world

WHERE name = :name

)

IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT}

ELSEIF SQLCODE=100 {WRITE "Query returns no results" QUIT}

WRITE name, " ", population

}

}

Проверяем результат в терминале:

do ##class(User.worldquery).WhereName("France")

В ответ должны вернуться название страны и число жителей.

Объекты/типы

Это общая преамбула к истории про REST/GraphQL. Мы реализуем API в сторону веб протоколов. Чаще всего у нас под капотом на серверной стороне будет исходный код на каком-то из языков, хорошо поддерживающих типизацию или даже целиком объектно-ориентированную парадигму. Вот те самые: Spring на Java/Kotlin, Django на Python, Rails на Ruby, ASP.NET на C# или даже Angular на TypeScript. И безусловно объекты в ObjectScript, родном для платформы IRIS.

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

Если посмотреть на вопрос с другой стороны, то пока не видно на горизонте технологий и инструментов легко передающих типы/объекты из программы на одном языке в программу на другом. Что остаётся? Вот эти упрощения SQL/REST/GraphQL и разливанное море документации с описанием API на человечески понятном языке. Неформальная, с компьютерной точки зрения, документация для разработчиков это как раз то, что следует всячески переводить в формальный код, который компьютер обрабатывать умеет.

Подходы к решению этих проблем предпринимаются регулярно. Одно из успешных это кросс языковая парадигма в объектной СУБД платформы IRIS.

Что бы чётко понимать как соотносятся модели ОПП и SQL в IRIS, предлагаю посмотреть на таблицу:

Более подробно про отображение объектной и реляционной модели можно посмотреть в документации IRIS.

При выполнении нашего SQL запроса на создание таблицы world из примера выше, IRIS автоматически генерирует у себя описания соответствующего объекта класс с именем User.world.

Class User.world Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {_SYSTEM}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = world ]

{

Property name As %Library.String(MAXLEN = 50) [ Required, SqlColumnNumber = 2 ];

Property continent As %Library.String(MAXLEN = 60) [ SqlColumnNumber = 3 ];

Property area As %Library.Numeric(MAXVAL = 9999999999, MINVAL = -9999999999, SCALE = 0) [ SqlColumnNumber = 4 ];

Property population As %Library.Numeric(MAXVAL = 99999999999, MINVAL = -99999999999, SCALE = 0) [ SqlColumnNumber = 5 ];

Property gdp As %Library.Numeric(MAXVAL = 99999999999999, MINVAL = -99999999999999, SCALE = 0) [ SqlColumnNumber = 6 ];

Property capital As %Library.String(MAXLEN = 60) [ SqlColumnNumber = 7 ];

Property tld As %Library.String(MAXLEN = 5) [ SqlColumnNumber = 8 ];

Property flag As %Library.String(MAXLEN = 255) [ SqlColumnNumber = 9 ];

Parameter USEEXTENTSET = 1;

/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement. Do not edit the SqlName of this index.

Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];

/// DDL Primary Key Specification

Index WORLDPKey2 On name [ PrimaryKey, Type = index, Unique ];

}

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

Пробуем реализовать тот же пример, что выше делали на SQL, Добавляем в класс User.world метод WhereName, который играет роль конструктора объекта "информация о стране"по заданному названию страны:

ClassMethod WhereName(name As %String) As User.world

{

Set id = 1

While ( ..%ExistsId(id) ) {

Set countryInfo = ..%OpenId(id)

if ( countryInfo.name = name ) { Return countryInfo }

Set id = id + 1

}

Return countryInfo = ""

}

Проверяем в терминале:

set countryInfo = ##class(User.world).WhereName("France")

write countryInfo.name

write countryInfo.population

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

Например, для нашего класса, зная сгенерированное IRIS имя индекса по названию страны WORLDPKey2 можно инициализировать/конструировать объект из базы данных одним скоростным запросом:

set countryInfo = ##class(User.world).WORLDPKey2Open("France")

Проверяем так же:

write countryInfo.name

write countryInfo.population

Хорошие рекомендации по выбору между объектным и SQL доступом к хранимым объектам есть в документации. Хотя, в любом случае, вы вольны для своих задач использовать только один из них на 100%.

Плюс ещё и том, что благодаря наличию в IRIS готовых бинарных связок с распространёнными ООП языками, какими как Java, Python, С, C# (.Net), JavaScript и, даже быстро набирающему популярность, Julia [1][2]. Всегда есть возможность выбора удобных вам языковых средств разработки.

Теперь приступим непосредственно к разговору о данных в веб-API.

REST он же RESTful веб-API

Вырываемся за границы сервера и уютного терминала. Пробираемся поближе к массовым интерфейсам браузерам и им подобным приложениям. Туда, где в основе взаимодействия систем используются гипертекстовые протоколы семейства http. В IRIS "из коробки" для этого работает связка из собственно сервера баз данных и http-сервера Apache.

Representational state transfer (REST) архитектурный стиль проектирования распределённых приложений и, в частности, веб-приложений. Ему, между прочим, пошел уже третий десяток лет. REST применяется повсеместно не имея какой-либо стандартизации, кроме как опоры на протоколы http/https. Совсем не то, что его коллеги/конкуренты в веб-службах на базе стандартов для протоколов SOAP и XML-RPC.

Глобальным ID в REST является URL и он определяет каждую очередную информационную единицу при обмене с базой данных или с бекэнд приложением.

Документация по разработке REST-сервисов в IRIS.

В нашем примере основой идентификатором будет что-то вроде основы из адреса сервера IRIS http://localhost:52773 и приставленного к нему пути до наших данных /world/ или более конкретно по стране /world/France.

Примерно так в докер-контейнере:

http://localhost:52773/world/France

При разработке полноценного приложения в документации IRIS рекомендуется воспользоваться одним из трёх генераторов кода. Например, один из них основан на описании REST API по спецификации OpenAPI 2.0.

Мы пойдём простым путём реализуем API вручную. В нашем примере сделаем простейшее REST-решение, которое требует всего два шага в IRIS:

1. Создать класс-диспетчер путей в URL, который будет унаследован от системного класса %CSP.REST

2. Добавить обращение к нашему классу-диспетчеру при настройке веб-приложения IRIS

Шаг 1 Класс-диспетчер

Реализация класса должна быть достаточно понятна. Делаем по инструкции в документации для "ручного" REST: https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=GREST_csprest#GREST_csprest_urlmap

/// Description

Class User.worldrest Extends %CSP.REST

{

Parameter UseSession As Integer = 1;

Parameter CHARSET = "utf-8";

XData UrlMap [ XMLNamespace = "http://www.intersystems.com/urlmap" ]

{

<Routes>

<Route Url="/:name" Method="GET" Call="countryInfo" />

</Routes>

}

}

И внутри добавляем метод-обработчик ровно так же, как были устроены вызовы в терминале из предыдущего примера:

ClassMethod countryInfo(name As %String) As %Status

{

set countryInfo = ##class(User.world).WhereName(name)

write "Country: ", countryInfo.name

write "<br>"

write "Population: ", countryInfo.population

return $$$OK

}

Как можете видеть, для передачи из входящего REST-запроса в параметра name вызываемого метода-обработчика в диспетчере указывается параметр с двоеточием в начале ":name".

Шаг 2 Настройка веб-приложения IRIS

В меню System Administration > Security > Applications > Web Applications

добавляем новое веб-приложение с указанием точки входа по URL на /world и обработчик наш класс-диспетчер worldrest.

Лайфхаки

Веб-приложение после настройки должно сразу отзываться по ссылке http://localhost:52773/world/France (регистр букв для передачи данных запроса в параметр метода важен, должно быть как в базе данных).

При необходимости используйте инструменты отладки хорошее описание есть в этой статье из двух частей (в комментариях загляните тоже). https://community.intersystems.com/post/debugging-web

Если появляется ошибка "401 Unauthorized", а вы уверены, что класс-диспетчер есть на сервере и ссылка правильная, то попробуйте добавить в настройках веб-приложения роль %All во вкладке "Application Roles". Это не совсем безопасно и надо понимать что вы разрешаете, но для локальной работы допустимо.

GraphQL

Эта территория новая, в том смысле, что в документации IRIS об API с использованием GraphQL вы ничего не найдёте сегодня. Но это не помешает нам воспользоваться таким замечательным инструментом.

Всего пять лет как GraphQL появился на публике.

Это язык запросов для API. И, наверное, это лучшая технология, которая возникла при совершенствовании REST-архитектуры и различных веб-API. Развитием GraphQL занимается Linux фонд. Небольшая вводная статья для начинающих.

А благодаря стараниям энтузиастов и инженеров InterSystems воспользоваться возможностями GraphQL в IRIS можно с 2018 года.

Статья на Хабре: Как я реализовал GraphQL для платформ компании InterSystems

En: GraphQL понимаем, объясняем, внедряем

Приложение для GraphQL состоит из двух модулей бекенд приложения на стороне IRIS и фронтэнд части, работающей в браузере. То есть, необходимо настроить по инструкции для веб-приложения GraphQL и GraphiQL.

Для примера, как это выглядит у меня на IRIS в докер-контейнере. Настройки веб-приложения GraphQL выполняющее функции REST-диспетчера и обработчика схемы базы данных:

И второе приложение GraphiQL пользовательских интерфейс для браузера, написан на HTML и JavaScript:

Запускает по адресу http://localhost:52773/graphiql/index.html

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

Пример GraphQL запроса к нашей базе данных:

{

User_world ( name: France ) {

name

population

}

}

И соотвествующий ему ответ:

{

"data": {

"User_world": [

{

"name": "France",

"population": 65906000

}

]

}

}

Так это выглядит в браузере:

Итоги

1. Жизненной силы и энергии у технологий SQL, REST и, теперь наверное, GraphQL много и это надолго. Они все прекрасно уживаются внутри платформы IRIS и, при должном внимании разработчика, дарят радость в создании программ работы с данными.

2. Хоть это не упомянуто в статье, другие великолепные API на основе XML (SOAP) и JSON в IRIS также реализованы на должном уровне. Пользуйтесь на здоровье.

3. Помните, что любой обмен данными через API это всё же неполноценная, а урезанная версия передачи объектов. И задача корректной передачи информации о типа данных в объекте, потерянной в API остаётся, на разработчике, а не в коде.

Вопрос к вам, дорогие читатели

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

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

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

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

ClickHouse для тестов железа

Самое простое, что можно сделать с ClickHouse, если есть свободные серверы это использовать его для тестов оборудования. Потому что его тестовый dataset содержит те же данные с production Яндекса, только анонимизированные и они доступны снаружи для тестирования. Про то, как подготовить хорошие анонимизированные данные, я рассказывал на Saint HighLoad++ 2019 в Санкт-Петербурге.

Ставим ClickHouse на любой Linux (x86_64, AArch64) или Mac OS. Как это сделать? мы собираем его на каждый коммит и pull request. ClickHouse Build Check покажет нам все детали всех возможных билдов:

Отсюда можно скачать любой бинарник с gcc и clang в релизе, в debug, со всякими санитайзерами или без, для x86, ARM или даже Mac OS. ClickHouse использует все ресурсы железа: все ядра CPU, шины памяти и грузит все диски. Какой сервер ему не дай проверит полностью, хорошо или плохо тот работает.

По этой инструкции можно скачать бинарник, тестовые данные и запустить запросы. Тест займёт около 30 минут и не требует установки ClickHouse. И даже если на сервере уже установлен другой ClickHouse, вы все равно сможете запустить тест.

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

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

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

ClickHouse без сервера

Конечно, обычно ClickHouse это сервер + клиент. Но иногда нужно просто обработать какие-то текстовые данные. Для примера я взял все исходники ClickHouse, собрал их в файл и сконкатенировал в файл под названием code.txt:

И, например, я хочу проверить, какие строчки в коде на C++ самые популярные. С помощью типичной shell-команды удалим из каждой строчки кода начальные пробелы и пустые строки, отсортируем и посчитаем количество уникальных. После сортировки видим, что, конечно, самая популярная строчка это открывающая фигурная скобка, за ней закрывающая фигурная скобка, а еще очень популярно return false.

Этот результат я получил за 1,665 секунд. Потому что все это было сделано с учетом сложной локали. Если локаль заменить на простую, выставив переменную окружения LC_ALL=C, то будет всего лишь 0,376 с, то есть в 5 раз быстрее. Но это всего-лишь шел скрипт.

Можно ли быстрее? Да, если использовать clickhouse-local, будет еще лучше.

Это как-будто одновременно и сервер и клиент, но на самом деле ни то, и ни другое clickhouse-local может выполнять SQL запросы по локальным файлам. Вам достаточно указать запрос, структуру и формат данных (можно выбрать любой из форматов, по умолчанию TabSeparated), чтобы обработать запрос на входном файле. За 0.103 секунд то есть в 3,716 раз быстрее (в зависимости от того, как вы запускали предыдущую команду).

Для демонстрации чего-то более серьезного давайте посмотрим на те данные, которые собирает GitHub Archive это логи всех действий всех пользователей, которые происходили на GitHub, то есть коммиты, создание и закрытие issue, комментарии, код-ревью. Все это сохраняется и доступно для скачивания на сайте https://www.gharchive.org/ (всего около 890 Гб):

Чтобы их как-нибудь обработать, выполним запрос с помощью ClickHouse local:

Я выбрал все данные из табличной функции file, которая берет файлы вида *.json.gz то есть все файлы в формате TSV, интерпретируя их как одно поля типа string. С помощью функции для обработки JSON я вытащил из каждой JSONины сначала поле 'actor', а потом поле 'login' в случае, когда оно равно Алексей Миловидов и выбрал таких первых 10 действий на GitHub.

Может возникнуть впечатление, что 890 Гб данных смогли обработаться за 1,3 секунды. Но на самом деле запрос работает потоково. После того, как находятся первые 10 строк, процесс останавливается. Теперь попробуем выполнить более сложный запрос, например, я хочу посчитать, сколько всего действий я совершил на GitHub. Используем SELECT COUNT... и через полторы секунды кажется, что ничего не происходит. Но что происходит на самом деле, мы можем посмотреть в соседнем терминале с помощью программы dstat:

И мы видим, что данные читаются с дисков со скоростью примерно 530 Мб/с и все файлы обрабатываются параллельно почти с максимальной скоростью насколько позволяет железо (на сервере RAID из нескольких HDD).

Но можно использовать ClickHouse local даже без скачивания этих 980 Гб. В ClickHouse есть табличная функция url то есть можно вместо file написать адрес https://.../*.json.gz, и это тоже будет обрабатываться.

Чтобы можно было выполнять такие запросы в ClickHouse, мы реализовали несколько вещей:

1. Табличная функция file.

2. Поддержка glob patterns. В качестве имени файлов можно использовать шаблон с glob patterns (звёздочка, фигурные скобки и пр.)

3. Поддержка сжатых файлов в формате gzip, xz и zstd из коробки. Указываем gz и всё работает.

4. Функции для работы с JSON. Могу утверждать, что это самые эффективные функции для обработки JSON, которые мы смогли найти. Если вы найдёте что-нибудь лучше, скажите мне.

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

6. Тот самый параллельный парсинг.

Применять можно, само собой, для обработки текстовых файлов. Еще для подготовки временной таблицы и партиций для MergeTree. Можно провести препроцессинг данных перед вставкой: читаете в одной структуре, преобразовываете с помощью SELECT и отдаете дальше в clickhouse-client. Для преобразования форматов тоже можно например, преобразовать данные в формате protobuf с разделителями в виде длины в JSON на каждой строке:

clickhouse-local --input-format Protobuf --format-schema такая-то --output format JSONEachRow ...

Serverless ClickHouse

ClickHouse может работать в serverless-окружении. Есть пример, когда ClickHouse засунули в Лямбда-функцию в Google Cloud Run: https://mybranch.dev/posts/clickhouse-on-cloud-run/ (Alex Reid). Там на каждый запрос запускается маленький ClickHouse на фиксированных данных и эти данные обрабатывает.

Текстовые форматы

Для обработки текстовых данных, естественно, есть поддержка форматов tab separated (TSV) и comma separated (CSV). Но еще есть формат CustomSeparated, с помощью которого можно изобразить и тот, и другой в качестве частных случаев.

CustomSeparated:

format_custom_escaping_rule

format_custom_field_delimiter

format_custom_row_before/between/after_delimiter

format_custom_result_before/after_delimiter

Есть куча настроек, которые его кастомизируют. Первая настройка это правило экранирования. Например, вы можете сделать формат CSV, но в котором строки экранированы как в JSON, а не как CSV. Разница тонкая, но довольно важная. Можно указать произвольный разделитель типа | и пр. между значениями, между строками и т.п.

Более мощный формат это формат Template:

format_template_resultset

format_template_row

format_template_rows_between_delimiter

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

Есть формат Regexp:

format_regexp

format_regexp_escaping_rule

format_regexp_skip_unmatched

И тут clickhouse-local превращается в настоящий awk. Указываете регулярные выражения, в Regexp есть subpatterns, и каждый subpattern теперь парсится как столбец. Его содержимое обрабатывается согласно некоторому правилу экранирования. И конечно можно написать пропускать строки, для которых регулярное выражение сработало, или нет.

ClickHouse для полуструктурированных данных

Первая идея в этом случае не использовать ClickHouse. Берите Mongo, которая отлично подходит именно для этого. Но если вдруг вы так любите ClickHouse, что просто не можете им не пользоваться можно и с его помощью.

Допустим, у вас есть таблица с логами, в ней есть столбец с датой и временем, а вот всё остальное вообще непонятно что. Очень соблазнительно всю эту кучу данных записать в один столбец 'message' с типом String. Если эта куча в формате JSON, функции для работы с JSON будут работать. Но неэффективно каждый раз, когда нам будет нужно только одно поле, например 'actor.login', читать придется весь JSON не будет преимущества столбцовой базы данных. С помощью ClickHouse мы легко это исправим прямо на лету, добавив с помощью запроса ALTER материализованный столбец:

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

ALTER TABLE logs UPDATE actor_login = actor_login

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

Ускорение MySQL

В ClickHouse можно создать таблицу на основе табличной функции MySQL. Это просто: указываете хост: порт, БД, таблицу, имя пользователя и пароль (прямо так, как есть), делаем SELECT и всё выполняется за 15 секунд:

Работает это тоже без всякой магии: табличная функция MySQL переписывает запрос, отправляет его в MySQL и читает все данные назад, на лету на всё 15 секунд. Но что будет, если я тот же самый запрос выполню в MySQL как есть?

5 минут 41 секунда это позор! У ClickHouse тут как-будто нет преимуществ данные нужно переслать из MySQL в ClickHouse и потом уже обработать. А MySQL обрабатывает сам у себя локально почему же он так медленно работает?

Еще одна проблема результаты расходятся. У ClickHouse две строки счетчик (20577 и 13772), у MySQL один (44744), потому что он здесь учитывает collation (правила сравнения строк в разном регистре) при GROUP BY. Чтобы это исправить, можно перевести имя в нижний регистр, сгруппировать по нему и выбрать любой вариант:

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

Словарь будет периодически загружать таблицу в оперативку, она будет кэшироваться. Можно применять SELECT:

Получилось уже 6 секунд, хотя основное предназначение словаря использование для джойнов, когда, например, нам нужно получить данные динамически через другие столбцы. Можно также создать словари MySQL на каждом сервере в кластере ClickHouse и быстро с ними работать. Но если MySQL не выдержит нагрузки при обновлении словаря на каждом сервере в кластере, то можно создать из MySQL словарь на двух ClickHouse-серверах, и они будут доступны в виде таблиц в ClickHouse. Если с помощью движка Distributed создать таблицу, которая смотрит на эти два сервера как на реплики, то можно на всех ClickHouse-серверах создать словари из ClickHouse, которые смотрят на таблицу, которая смотрит на словарь MySQL.

Словари еще можно использовать для шардирования, если схема расположена во внешней мета-базе (и не обязательно в ClickHouse). Это тоже будет работать:

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

Видим, что SELECT выполняется за 0,6 с. Вот это настоящая скорость, какая должна быть это скорость ClickHouse!

В ClickHouse можно даже создать базу данных типа MySQL. Движок БД MySQL создает в ClickHouse базу данных, которая содержит таблицы, каждая из которых представляет таблицу, расположенную в MySQL. И все таблицы будут видны прямо в ClickHouse:

А вообще в ClickHouse много табличных функций. Например, с помощью табличной функции odbc можно обратиться к PostgreSQL, а с помощью url к любым данным на REST-сервере. И все это можно поджойнить:

Примечение: в свежих релизах ClickHouse появилась табличная функция postgresql, движок баз данных PostgreSQL и даже поддержка бинарного протокола PostgreSQL. Кажется это даже слишком круто.

Машинное обучение в ClickHouse

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

Это можно использовать для заполнения пропусков в данных. Пример: компания, занимающаяся недвижимостью, публикует объявления о квартирах с разными параметрами: количество комнат, цена, метраж. Часто некоторые параметры не заполнены например, квадратные метры есть, а количества комнат нет. В этом случае мы можем использовать ClickHouse с моделью CatBoost, чтобы заполнить пропуски в данных.

Более простые модели можно обучать прямо в ClickHouse. Модель машинного обучения у нас будет представлена как агрегатная функция например, стохастическая логистическая регрессия. Задаются гиперпараметры, значение предсказываемой функции и значения фич, причем обучение будет независимым для каждого ключа агрегации, если в запросе указан GROUP BY:

А еще мы можем добавить к агрегатной функции суффикс State:

SELECT stochasticLogisticRegressionState(...

Так можно обучить логистическую регрессию для каждого k и получить состояние агрегатной функции. Состояние имеет полноценный тип данных AggregateFunction(stochasticLogisticRegression(01, 00, 10, 'Adam'), ...), который можно сохранить в таблицу. Достать его из таблицы и применить обученную модель можно функцией applyMLModel:

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

Более развернуто описано в этой презентации.

ClickHouse как графовая база данных

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

Это работает, и говорят, даже быстрее, чем некоторые другие графовые базы данных. Разработал его наш друг, один из ведущих контрибьюторов Amos Bird. Правда, эта разработка не доступна в open-source. Но мы не обижаемся.

UDF в ClickHouse

Казалось бы, в ClickHouse нет возможности написать пользовательские функции (user defined functions). Но на самом деле есть. Например, у вас есть cache-словарь с источником executable, который для загрузки выполняет произвольную программу или скрипт на сервере. И в эту программу в stdin передаются ключи, а из stdout в том же порядке мы будем считывать значения для словаря. Словарь может иметь кэширующий способ размещения в памяти, когда уже вычисленные значения будут кэшированы.

И если вы пишете произвольный скрипт на Python, который вычисляет, что угодно пусть те же модели машинного обучения, и подключаете его в ClickHouse, то получаете вы как раз аналог user defined function.

Примечание: полноценная реализация UDF находится в roadmap на 2021 год.

ClickHouse на GPU и как Application Server

Это ещё два необычных примера. В компании nVidia ClickHouse заставили работать на графических ускорителях, но рассказывать я про это не буду.

А наш друг Zhang2014 превратил ClickHouse почти в Application Server. У Zhang2014 есть pull request, где можно определить свои HTTP-хэндлеры и этим хэндлерам приписать подготовленный запрос (SELECT с подстановками или INSERT). Вы делаете POST на какой-то хэндлер для вставки данных, или делаете вызов какой-то GET ручки, передаете параметры, и готовый SELECT выполнится.

Вывод

ClickHouse очень интересная система, в которой всегда можно найти что-то новое, причем, что интересно, что-то новое там всегда могу найти даже я. Многие разработчики удивляют меня тем, что они могут реализовывать внутри ClickHouse всего лишь чуть-чуть поменяв его код. И эти штуки будут работать и в production!

(статья обновлена в мае 2021г.)

Какие системы управления базами данных (СУБД) распространены в мире больше всего? Как они изменились с 2006года и какие входят в десятку самых популярных? В этой статье мы проанализируем базы данных, которые были на пике популярности с 2006 по 2021год. Данные обновляются каждый месяц. Подробнее в индексе ведущих баз данных TOPDB. Итак, рассмотрим самые популярные базы данных с 2006 по 2021год.

15 самых популярных баз данных с 2006 по 2021год

Какая база данных стала самой популярной в 2021году? Согласно рейтингу БД, это Oracle. Этой базой данных пользуются 30,2% респондентов. В два раза меньше респондентов используют MySQL (16,65%) и SQL Server (13,21%) второе и третье места соответственно. В совокупности на долю этих трех СУБД приходится более 62% общего числа пользователей. На четвертой строчке расположилась СУБД Microsoft Access 9%. На долю баз данных, занявших пятое и последующие места, приходится менее 5%.

При этом Oracle занимает то же положение, что и 15лет назад. В мае 2006года этой СУБД пользовались 31,8% респондентов. На втором месте была MySQL 24,5%. В совокупности этими двумя базами данных в 2006году пользовались более 55% респондентов. Третью строчку в 2006году занимала СУБД Microsoft Access. Тогда ее использовали 17,6% респондентов, но в 2021году их количество сократилось почти вдвое и составило 9,07%. СУБД SQL Server с тех пор поднялась на одну позицию, и хотя ее показатель по-прежнему составляет около 13%, ей удалось обойти Access.

Рейтинг баз данных DB-Engines май 2021года

В мае 2021года лидером рейтинга DB-Engines остается Oracle. За ней следует MySQL, которая набрала 1236баллов, и Microsoft SQL Server 992,66балла.

Рейтинг DB-Engines март 2021года: Визуализация данных через платформу Flourish

Мы рассмотрели самые популярные базы данных в рейтинге TOPDB. TOPBD рассчитывает показатель так: Индекс ведущих баз данных TOPDB основывается на анализе частоты поисковых запросов в Google, содержащих названия баз данных. Но какие базы данных наиболее популярны в мире по версии DB-Engines?

На первых трех строчках размещаются все те же СУБД. Лидирует Oracle (1321,73балла), на втором месте MySQL (1254,83балла), далее Microsoft SQL Server (1015баллов). Но начиная с четвертой строки рейтинг меняется: по версии DB-Engines четвертой самой популярной в мире СУБД стала PostgreSQL, которая набрала 549,29балла.

Рейтинг DB-Engines Топ 10 наиболее популярных баз данных март 2021года: Визуализация данных через платформу Flourish

Еще один интересный пример: в TOPDB Microsoft Access занимает четвертое место, но в рейтинге DB-Engines Access набирает 118,14балла. В десять раз меньше, чем Oracle. (Подробнее о том, как рассчитываются показатели БД в этом рейтинге, можно прочитать по ссылкеhttps://db-engines.com/en/ranking_definition.)

Самые быстрорастущие базы данных в прошлом году

Какие из 50 баз данных проявили себя лучше других в прошлом году, а какие не продемонстрировали блестящих результатов? Начнем с хорошего. Microsoft Azure SQL Database, PostgreSQL, Mongo DB и Snowflake показали высокий рост. Из них наибольший рост продемонстрировала СУБД Microsoft Azure (35,44%), а наименьший Snowflake (+20,77%). Показатели неплохо поднялись у Google BigQuery, Redis и Amazon DynamoDB. Среди них самый высокий рост наблюдался у BigQuery (+8,51%), а наименьший у Amazon DynamoDB (+6,38%).

Рейтинг DB-Engines Топ 50 наиболее популярных баз данных март 2021года: Визуализация данных через платформу Flourish

Наибольшую отрицательную динамику показали три базы данных: Microsoft SQL Server (82,55%), Oracle (18,91%) и Hive (9,34%). Однако некоторые из баз данных, показатели которых ухудшились по сравнению с показателями марта, по-прежнему занимают лидирующие позиции в общем рейтинге. Oracle, MySQL и Microsoft SQL самые популярные в мире базы данных в среднем потеряли по 35,55%.

Выше представлена интерактивная таблица рейтинга DB-Engines (ссылки на официальные данные можно найти здесь: https://db-engines.com/en/ranking). Вы можете посмотреть данные для разных столбцов.

Источники и полезные ссылки

Работая над этой статьей, я использовал несколько источников, в том числе рейтинги TOPDB и DB-Engines. Ссылки на источники указаны в статье.

Видео о самых популярных базах данных с 2006 по 2021год: https://youtu.be/thuG2PXVbBU

Статья о самых популярных игровых консолях: https://statisticsanddata.org/data/best-selling-consoles-in-history-1972-2021/

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

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

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

Проблематика

Главная на мой взгляд проблема и сложность это работа программы только с нативной СУБД Clarion, доступ к данным при таком подходе очень неудачный, требуется большой объем кода для написания даже простейших задач, которые решаются отправкой на сервер простейшего Update или Insert в Clarion это десятки строчек кода по открытию файла, получению доступа к инфе и его последующего закрытия. Ниже пример:

       Access:Agent.Open !Открываем файл       Access:Agent.UseFile!Открываем файл       clear(AGN:Record)!Делаем очистку записи на всякий случай       AGN:ID_AGENT = some_id !Присваиваем ключу значение       set(AGN:BY_ID,AGN:BY_ID)!Устанавливаем "каретку" на первое значение ключа       next(agent)!Встаем на первую запись удовлетворяющую ключу       IF errorcode() or AGN:ID_AGENT <> some_id!Проверяем не вышла ли каретка за область ключа            RETVAL = 'Контрагент не найден'!Выкидываем ошибку          ELSE            RETVAL = AGN:N_AGENT!Возвращаем имя агента       .       Access:Agent.Close  !Закрываем файл

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

select agent.name where id = some_id

Задача

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

Характеристики системы

Обе системы примерно схожие по объему кодовой базы и по количеству пользователей

Общее количество пользователей: около 80

Общее количество таблиц: около 250

Сфера деятельности: Торговля + Сфера обслуживания (Салоны красоты)

Подразделения:

3 Салона красоты

5 Подразделений торговых предприятий - мелкооптовая торговля

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

• Самодельная программа миграции

• DCT2SQL

• Cldump

• BULK insert

• UltimateSQL & Ultimate Debug

Самодельная программа миграции

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

DCT2SQL

Данный компонент позволяет генерировать скрипты для экспорта структуры БД из Dictionary в SQL, поддерживает экспорт всех возможных типов данных, также экспортирует индексы и foreign keys. Очень удобно работает, все импортируется в пару кликов. Данные скрипты я храню в таблице миграций.

Можно скачать на Github - https://github.com/RobertArtigas/DCT2SQL

Также есть обучающие ролики на youtube по работе и правильной выгрузке. Вообще там очень много инфы в этих роликах по миграции на SQL.

https://www.youtube.com/watch?v=MjMgQYMc_xY

https://www.youtube.com/watch?v=bAolfvrz2oE&t=7067s

CLDUMP

Данная программа конвертирует данные из *.dat файла в csv таблицы готовые для загрузки через скрипт BULK. Достоинство этой программы - скорость. Она может сконвертировать таблицу накладных за 10 лет за 15-20 секунд. Главная проблема данной утилиты в том, что она доступна только в репозиториях Linux, в частности debian. Пришлось на основе этой команды создать микро-сервис, который на входе принимает post запрос, а на выходе выдает ссылку для скачивания данного файла в виде csv таблицы.

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

Программу cldump можно скачать командой в любой debian подобной системе:

apt-get install cldump

BULK insert

За доли секунд втягивает таблицу из csv в SQL. В данном случает, т.к. связки данных уже настроены на существующие ID, но при этом надо чтобы работал автоинкремент, поэтому его временно надо отключить, также потратил довольно много времени чтобы подобрать подходящие разделители:

BULK INSERT dbo.%table_name%FROM table_name.csv WITH ( FORMAT = 'CSV', FIELDQUOTE = '', FIRSTROW = 1, FIELDTERMINATOR = '0x3b', ROWTERMINATOR = '0x0a', CODEPAGE='65001',TABLOCK, KeepIdentity)

UltimateSQL & Ultimate Debug

Данные компоненты позволяют загружать данные из SQL в QUEUE примерно таким образом:

SQL_Result = sql.query('select id, path_to_result from dbo.export_tasks as et where (status_complete = 0 or status_complete = 2) and export_table_id = '& exp:id,qexport_tasks)

Выполнять запросы без возвращаемых значений:

sql.Query('Update export_tasks set status_complete = 2 where id = ' & qexport_tasks.id)

Есть отличное описание как использовать на youtube:

https://www.youtube.com/watch?v=RVit-5RPncs&t=2259s

Также при установке внутри шаблонов есть "пасхалка" от автора, как решить квест описывается по ссылке:

https://clarionhub.com/t/need-some-help-with-ultimatesql-error-when-trying-to-include-it-in-my-project/4182