SQL *

Пишем эффективные аналитические запросы: первая лекция курса в МГТУ им. Баумана

Делимся лекциями курса «Deep dive into SQL and DWH» от Павла Ковалёва, дата-архитектора Сравни.

Первая лекция прошла 7 ноября — предлагаем посмотреть её видеозапись.

Внутри лекции:

• Обзор основных понятий курса (OLTP и OLAP, хранилище данных, его базовые слои, архитектуры обработки данных и т.д.)

• Устройство DWH — на примере компании Сравни

• Основы оптимизации аналитических SQL-запросов

• Как решать проблемы с неэффективными запросами: практические кейсы

Посмотреть запись можно здесь:

✅YouTube

✅RUTUBE

✅VK

Следующая лекция курса, посвященная архитектурному обзору инструментов для построения DWH, пройдёт уже в ближайший четверг, 14 ноября, с 17:25 до 19:00. Присоединяйтесь в онлайне!

***

Больше информации о наших лекциях, митапах, статьях и других полезных материалах про ИТ, в тг-канале Sravni Tech.

Привет! Я — Ося, разработчик в Инновационном Центре «Безопасный транспорт», ваш виртуальный проводник в мир технологий и разработки. В блоге рассказываю, как решаю сложные задачи и делюсь знаниями. Здесь мы будем разбираться в коде, обсуждать подходы к проектам и актуальные тренды в IT. Пишу о разработке, больших данных и инновационных технологиях.

Я — робот-осьминог и талисман ИЦ, который иллюстрирует разносторонность и многозадачность нашей команды. Мои 8 щупалец представляют различные направления работы, а 3 сердца обеспечивают высокую эффективность. Отсутствие скелета позволяет гибко адаптироваться к изменениям, а более 1000 рецепторов на щупальцах помогают мне воспринимать малейшие колебания в окружении.

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

Как обрабатывать 5 млн изменяющихся форм в минуту с SLI 99.99% — Кирилл Грищук

Опубликовали запись доклада секции «Разработка» с Ural Digital Weekend 2024.

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

Ссылка на запись доклада в ВКонтакте.

Ссылка на презентацию: https://goo.su/qHW9

Сделал Неовим-плагин, для работы дбт-моделями. Пока реализованы несколько сценариев:

• запуск модели;

• полный перезапуск модели;

• компилирование модели с выводом в новый буфер;

• генерация yaml-описания модели с выводом в новый буфер;

• отображение зависимостей модели в Телескопе.

Установка и доступные команды доступны в репозитории. Если есть пожелания и рекомендации — пишите в гит или телеграм.

Чем дольше копаю тему статистического анализа производительности СУБД, тем больше удивляюсь - почему никто не занимался/не занимается использованием математических методов в DBA ? Статей и материалов практически - нет. По performance engineering - можно найти, по DBA в общем то тишина.

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

Это же так просто и в общем то лежит на поверхности - сделал изменение , собери статистику влияния и оцени характер полученных результатов по совокупности опытов. Нужно подчеркнуть - не картинки, не "кажется" , "наверное" , "скорее всего", "может быть" , а цифры. И цифры взятые не с потолка , а рассчитанные математически. И даже не надо ничего нового придумывать и изобретать - медиана, мода , стандартное отклонение , дисперсия , корреляция - 3й курс КАИ, если не ошибаюсь. Вполне достаточно , для получения объективных результатов анализа, а не гаданий и шаманских танцев с бубнами.

Почему DBA не используют математику ? Риторический вопрос ....

О способе оценки производительности отдельного SQL запроса .

В дополнении к теме:

Размышления о мониторинге производительности отдельного SQL запроса

Для возможного варианта решения задачи:

https://habr.com/ru/posts/833162/

если производительность отдельного SQL запроса в настоящее время не мониторится.

Предположение.

Для того, чтобы оценить производительность отдельного SQL запроса необходимо и достаточно получить отношение стоимости запроса (EXPLAIN ANALYSE) к актуальному времени выполнения запроса .

Важное следствие и ограничение:

Данную оценку производительности можно получить только на данный конкретный момент времени при данном конкретном состоянии СУБД в целом.

Данное весьма существенное ограничение , вообще говоря, влечет реальные проблемы для использования данной методики в промышленной эксплуатации СУБД(запрос может быть весьма ресурсоемким). Но с другой стороны, позволяет очень чётко и однозначно отследить причины изменения производительности запроса при изменении текста запроса и/или, что важнее - при изменении входящих параметров запроса .

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

Друзья! У Петра вышло продолжение серии обзорных статей про ClickHouse — open-source OLAP базу данных, ориентированную на колонки. В новой статье наш DevOps-инженер рассказывает про особенности репликации в этой СУБД.

Из этой части вы узнаете:

• как работают сервисы координации Zookeeper и ClickHouse Keeper;

• по какой причине может произойти падение репликации;

• почему не следует очищать Keeper вручную.

Чтобы вспомнить, о чём Пётр рассказывал в первой части — нажмите сюда.

А чтобы ознакомиться с новой частью — сюда.

В продолжении темы - "время отклика СУБД" https://habr.com/ru/posts/827054/

Метрику не имеет смысла использовать для алерта о деградации производительности СУБД .

Допустим имеется OLTP нагрузка на СУБД - большое количество коротких запросов. В результате, имеем некоторое значение метрики "Среднее время отклика" = sum(total_exec_time) / sum(calls).

И вот , ситуация изменилась - запросов OLTP стало меньше , но появились аналитические/долгие запросы .

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

Является ли данная ситуация алертом для создания инцидента о деградации производительности СУБД ?

Конечно же - нет.

Более того - в этом случае резко увеличится количество обрабатываемых блоков разделяемой области.

Что также не является показателем деградации производительности СУБД , даже совсем наоборот .

P.S. Использование данной метрики оправдано только в одном сценарии - нагрузочное тестирование инфраструктуры при обязательном условии постоянства нагрузки на СУБД.

На днях опубликовал на GitHub свой скрипт 2013 года, который наполнял с ИБП Ippon Smart Winner 750 базу данных IBM DB2 данными по напряжению сети (за каждую секунду в течение года, по результатам наблюдений скорректировал уставки на реле напряжения, их безопасность для техники подтвердилась при отгорании нуля), обновлял статус моего DynDNS клиента по данным с роутера, запускал и останавливал виртуальную машину VMware Player (там у меня крутилась openSUSE с сайтом на Apache/Django) по расписанию и когда в планировщике BeholdTV была запланирована запись кабельного телевидения (это было необходимо, поскольку видео захватывалось в .asf/x264 - crf18/AC3 без использования графического ускорения и на всё ресурсов не хватало), следил за качеством ADSL линии. А сегодня дополнил ещё рядом скриптов: в 2014 перенёс сайт на Raspberri Pi (Arch Linux ARM) и там стал захватывать IPTV видео. Про захват у меня на Дзене можно почитать, а скрипы создания оглавления и некоторого контроля версии файлов (WSH/JS) опубликовал на GitHub здесь же. Также скрипты по установке времени и некоторой оптимизации скорости отклика сайта, мониторинга доступности посредством Online Domain Tools. Ещё дополнил своими Windows скриптами по работе с СУБД IBM DB2 Express-C и её оптимизации по книге "Best practices Tuning and monitoring database system performance" (она тоже выложена в соответствии с лицензией). Изначально не включил библиотеку RGraph для построения графиков, теперь выложил под лицензией MIT 2013 года.

На днях вышел из строя ИБП. Переключился на стабилизатор. Было уже. До техники APC стоял ИБП Ippon Smart Winner 750. Мониторил напряжение. Сохранял в базе данных SQL сервера IBM DB2 Express-C 10.5 данные за каждую секунду и хранил в течение года. Это помогло выставить уставки на реле напряжения и нормально пережить несколько отгораний нуля без потерь для техники. Скрипты соответствующие опубликовал сегодня на GitHub у себя. При работе от стабилизатора приходилось потом чинить неоднократно SQL базу данных, благо у меня было настроено журналирование в "Архивном" режиме, а сама база данных периодически тестировалась на наличие повреждений. Сложнее с остальной частью данных на компьютере: в NTFS принято частичное журналирование и внезапное отключение ведёт к необходимости решать, восстанавливать ли из бэкапа или соглашаться с тем, что возможно какие-то повреждения будут не сразу обнаружены и могут вызвать проблемы с дальнейшим ремонтом. Чуть раньше такой же пост на Дзене у себя опубликовал.

Обновление библиотеки asyncpg-lite до версии 0.3.1.1

Рад сообщить, что библиотека asyncpg-lite обновлена до версии 0.3.1. Все предыдущие версии были удалены и больше не доступны для установки. Для корректной работы, пожалуйста, удалите старые версии с помощью следующей команды:

pip uninstall asyncpg-lite

Чтобы установить последнюю версию, используйте:

pip install --upgrade asyncpg-lite

Актуальная версия: 0.3.1.1

Вы можете найти страницу библиотеки на GitHub по следующему адресу: asyncpg-lite на GitHub.

Что нового в версии 0.3.1.1

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

Причины обновления

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

Подробный разбор новой версии библиотеки я постараюсь опубликовать завтра в формате статьи.

Благодарю за ваше внимание и поддержку!

Все те кто пользовался библиотекой asyncpg-lite. Сообщаю, что завтра вечером будет выпущено обновление библиотеки asyncpg-lite. В новой версии библиотека будет полностью переписана с использованием SQLAlchemy и asyncpg (в старой версии использовался чистый asyncpg), при этом базовый синтаксис останется практически неизменным.

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

Начиная с версии asyncpg-lite 0.3, библиотека будет основываться на SQLAlchemy и драйвере asyncpg для работы с PostgreSQL. Версии ниже 0.3 будут сняты с доступности для скачивания и установки с завтрашнего дня.

С выходом asyncpg-lite 0.3 настоятельно рекомендуем удалить старые версии библиотеки и установить актуальную.

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

Благодарю за внимание! 🚀

asyncpg-lite обновлена до версии 0.2.2.1!

В новой версии:

• Убран флаг dsn_flag (теперь достаточно не передавать данные в параметр dns и состояние этого флага будет автоматически сброшено на dsn_flag = False)

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

• Добавлен флаг debug: bool во все методы (по умолчанию его значение False)

• Исрпавлены ошибки и улучшены логи (теперь там больше полезной информации)

Зачем нужен флаг debug?

Установив этот флаг в методе вы сможете вывести в консоль дополнительную информацию, такую как параметры запроса и сам SQL-запрос.

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

С библиотекой вы сможете ознакомиться тут: asynpg-lite: Простой асинхронный менеджер для PostgreSQL на Python

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

SQL исполняется 50 лет. Сегодня вспомним историю его создания

В далёком 1970 году Эдгар «Тед» Кодд, информатик из IBM, опубликовал революционную статью под названием «A Relational Model of Data for Large Shared Data Banks», тем самым заложив основы теории реляционной модели данных.

Поначалу IBM не горела желанием внедрять наработки Кодда. В то время у компании была IMS, довольно успешная иерархическая база данных. Лишь спустя три года, в 1973 году, IBM запустила System R — исследовательский проект по изучению теории Кодда. Факт: сам Эдгар практически не принимал участия в разработке. Зато активно трудились двое других сотрудников IBM — Дон Чемберлин и Рэй Бойс, которые ещё до запуска проекта разработали SQUARE.

В синтаксисе SQUARE было много нижних индексов и некоторые математические обозначения. Печатать команды с клавиатуры было неудобно. Чемберлин и Бойс решили сделать язык более простым, таким, чтобы он напоминал структуру английского предложения. Так и появился SEQUEL, который потом переименовали в Structured Query Language — «язык структурированных запросов».

Первый выпуск SQL состоялся в июне 1974-го.

Как к SQL относился Тед Кодд? Он считал, SQL неправильно реализовывал теорию реляционной модели данных. В 1985-м Эдгар опубликовал «12 правил Кодда», описывающие, что должна содержать НАСТОЯЩАЯ реляционная СУБД. Стоит ли говорить, что SQL сам по себе не соблюдает их все?

📺 Советую посмотреть свежее интервью с Доном Чемберлином, где он рассказывает об истории своего детища.

SQL vs NoSQL: как выбрать архитектуру БД для мобильного приложения

SQL (Structured Query Language) — это язык запросов, которые мы используем для работы с реляционными базами данных. У таких БД жесткая структура в виде таблиц. Вся информация там хранится в столбцах и строках. Структурированность — одно из главных преимуществ SQL баз данных.

NoSQL — нереляционные БД, у них нет жесткой структуры. Скажем, у нас есть класс — пользователи. У каждого из них есть ID, имя, фамилия и проч. Эти данные помещаются в отдельный файл (а не в таблицу), и взаимодействие происходит только с этим файлом. Вы уже не можете забрать какое-то одно поле и работаете только с этим классом.

Чтобы выбрать между SQL и NoSQL, нужно исходить из задач бизнеса.

• Если вы делаете маленькое приложение на узкую аудиторию или MVP, можно смело выбирать NoSQL. Такие базы быстрые, с ними проще работать, они легко масштабируются. А если проект растет как на дрожжах — NoSQL можно быстро переписать на SQL.

• Если вы делаете средний по объему проект, лучше SQL. Хотя, если очень хочется, можно и NoSQL. Но тут есть особенности: выбирайте NoSQL, только если у вас есть налаженные ивенты, налаженная имплементация баз данных и опыт работы с NoSQL.

• Если вы делаете энтерпрайз, лучше выбрать SQL. Представим, что в каком-то крупном маркетплейсе 10 тысяч человек оплатили покупки, но не получили товары, потому что транзакция данных прошла некорректно. Цена ошибки тут слишком высока — поэтому SQL.

Больше подробностей — в нашем блоге.

Google Firebase сдался и добавил в свои сервисы SQL базу данных (облачную PostgreSQL) в форме Firebase Data Connect.

Пока в виде preview сервис можно попробовать бесплатно. Потом собираются брать плату и за саму базу, и за API доступа к ней.

Вряд ли Google с такими политиками сможет конкурировать с Supabase.На данный момент это две основные площадки, с которыми фронтендер или мобильный разработчик может без излишних усилий сделать удобный облачный бэкенд, как без логики (просто CRUD доступ), так и с ней (Functions), и оставаясь в рамках стандартов (не сильно привязываясь к проприетарным решениям сервисов).

Состоялся релиз мажорной версии открытого масштабируемого решения для кластеризации баз данных MySQL — Vitess 19. Исходный код проекта опубликован на GitHub под лицензией Apache License 2.0.

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

Изменения и дополнения в Vitess 19:

• прекращение поддержки MySQL 5.7. Разработчики советуют пользователям выполнить обновление до MySQL 8.0, используя Vitess 18, прежде чем переходить на Vitess 19. Однако Vitess 19 по-прежнему будет поддерживать импорт из MySQL 5.7;

• добавлены новые метрики для консолидации потоков и версия сборки в /debug/vars, чтобы обеспечить более глубокое понимание и отслеживаемость;

• улучшена совместимость запросов, реализована поддержка операций удаления из нескольких таблиц, новый запрос SHOW VSCHEMA KEYSPACES и несколько других улучшений синтаксиса SQL, которые расширяют совместимость Vitess с MySQL;

• поддержка отсрочки попыток переключения в случае блокировки. Поддержка принудительного отключения;

• улучшение процесса инкрементного резервного копирования: поддержка имён резервных копий и пустых резервных копий.

«Следуя тенденции последних трёх лет, новая версия Vitess быстрее предыдущей во всех тестах, которые мы отслеживаем в Arewefastyet. Мы исправили несколько проблем с производительностью, доработали интерфейс и код», — пояснили разработчики, порекомендовав изучить документацию проекта и список исправлений.

«Циан» перенесла за 6 недель более 500 микросервисов, 2 монолитных приложения и 500 ТБ своих данных на платформу Yandex Cloud без перерывов в работе сервиса, включая Terraform и Salt.

Миграция проходила в рамках совместной работы специалистов «Циан» и поддержки Yandex Cloud. На облачную платформу они перенесли системы управления базами данных, очереди сообщений и поисковую систему. Данные поступают в объектное хранилище. Для эффективной работы с микросервисами компания использует Yandex Managed Service for Kubernetes.

Технологический стек компании включает Python, C#, Node.js. Работа с данными велась в PostgreSQL, MSSQL, Cassandra, Elasticsearch; приём и отправка сообщений — в RabbitMQ и Kafka.

Подготовительный этап миграции в Yandex Cloud занял 4 месяца. Из них 2 месяца ушло на проверку осуществимости миграции.

За полтора месяца миграции микросервисы и один монолит перенесли в Yandex Managed Service for Kubernetes, а монолит на Windows IIS — на виртуальные машины Compute Cloud. Для хранения данных используют объектное хранилище Object Storage, для гибкой работы с документоориентированными моделями данных — Managed Service for MongoDB, для эксплуатации и администрирования реляционных баз данных — Managed Service for PostgreSQL и Managed Service for MySQL.

Чтобы избежать даунтаймов, пришлось сделать утилиту для миграции микросервисов, развернуть несколько кластеров на Vanilla Kubernetes, синхронизатор эндпоинтов, перейти на нереплицируемые SSD для работы MSSQL.

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

Тут представлена попытка заNULLить значение, если оно равно чему-то.

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

NULLIF(значение1, значение2)

Функция NULLIF выдаёт значение NULL, если значение1 равно значение2; в противном случае она возвращает значение1. Это может быть полезно для реализации обратной операции к COALESCE. В частности, для примера, показанного выше:

SELECT NULLIF(value, '(none)') ...

В данном примере если value равно (none), выдаётся null, а иначе возвращается значение value.

То есть в примере выше стоит переписать короче и понятнее:

, nullif(sdate, '1900-01-01') sdate
, nullif(mdate, '1900-01-01') mdate

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

Но иногда он вовсе не нужен! Обратите внимание на картинку сверху...

Посмотрим на использованный тут синтаксис CASE:

CASE
  WHEN условие THEN результат
  [WHEN ...]
  [ELSE результат]
END

Или еще конкретнее:

CASE
  WHEN условие THEN TRUE -- [условие IS TRUE]
  ELSE FALSE             -- [условие IS FALSE, IS NULL]
END

Хм... То есть результат этого CASE эквивалентен значению условия с точностью до NULL!

При обращении условия в NULL такой CASE вернет FALSE, но этого же поведения можно добиться с помощью coalesce:

coalesce(условие, FALSE)

Но если мы говорим о конкретном примере с условием EXISTS, то уж оно-то точно никак не может принимать значение NULL! Значит, coalesce-обертка нам тут не требуется и эту часть запроса можно сократить до одного лишь условия, без всяких CASE:

EXISTS(
  SELECT
    NULL
  FROM
    _inforg20687 t15
  WHERE
    t15._fld1329 = 0::numeric AND
    t15._fld20688rref = t6._idrref AND
    t15._fld20689_type = '\\010'::bytea AND
    t15._fld20689_rtref = '\\000\\000\\001\\010'::bytea AND
    t15._fld20689_rrref = t4._fld6883rref
)

В общем, пишите меньше SQL-кода - и ваши запросы "будут мягкими и шелковистыми"!