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

Был интересный проект автономной базы https://github.com/cmu-db/peloton, но его прикопали :(
Крайне рекомендую всем интересующимся вот этот плейлист от автора проекта https://www.youtube.com/playlist?list=PLSE8ODhjZXjasmrEd2_Yi1deeE360zv5O
и другие видео с его канала.

Что-то в области self-driving баз делает Oracle, но проверять я это, конечно же, не буду :)

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

Поэтому пока нам придется решать эти проблемы снаружи.

Интересно еще и то, что сферы применения AST - парсера, компилятора и анализатора не ограничиваются генерацией типов и поиском проблем в запросах.
Сегодня накидаю еще вариантов. Предлагайте свои :)

1) ПРОВЕРКА ЦИКЛИЧЕСКИХ ЗАВИСИМОСТЕЙ
Совершенно реальна ситуация, когда VIEW или FK были созданы таким образом, что базе образуются циклические зависимости между объектами.

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

2) ТОПОЛОГИЧЕСКАЯ СОРТИРОВКА
В Snowflake обнаружилась такая проблема - при экспорте DDL с помощью стандартных средств, объекты сортируются по имени. Если у вас есть VIEW с именем "A", которое зависит от таблицы "B", то потом по этому DDL восстановить схему не получится.

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

3) GRAFANA QUERIES
Был такой фичреквест: сделать тулзу, которая будет подготавливать обычные запросы под дашборды графаны. Если вы делали дашборды, вам это проблема знакома ;) Сделаем отдельным пакетом и интерфейсом на parsers.dev

Будет достаточно только AST, но обязательно нужен deparser, чтобы преобразовать измененный запрос обратно в SQL. Недавно для PostgreSQL такой появился:
https://pganalyze.com/blog/pg-query-2-0-postgres-query-parser
Если с ним все ок, то все просто :)

4) ОЦЕНКА СЛОЖНОСТИ ЗАПРОСА
В Snowflake для запуска запросов нужно указать WAREHOUSE. Это виртуалка. При запуске нужно указать ее размер, который отображает количество выделяемых ресурсов.
Если запрос затрагивает много таблиц, возможно, имеет смысл взять WAREHOUSE побольше.

Можно определить количество таблиц c помощью AST traverse, но проще взять из IR-объекта.

5) GOVERNANCE AND REGULATORY COMPLIANCE.
В некоторых базах пермишены можно накладывать только на объекты целиком, в некоторых можно установить ограничения на уровне строки, в некоторых на уровне колонок.

В Snowflake есть MASKING POLICY. Это очень удобный инструмент для преобразования контента указанного столбца.
Можно, например, экранировать строку в зависимости от роли пользователя
https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy.html#examples

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

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

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

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

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

6) FEATURE ENGINEERING
В процессе подготовки данных для ML, дата-инженерам приходится решать задачу поиска зависимых фич. Иными словами, зависимых столбцов. Например, день недели и число. Или дата рождения и возраст.
Такие связи могут быть гораздо сложнее.

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

7) ANOMALY DETECTION
Дата инженеры часто не утруждают себя описанием связей между таблицами в БД. Обычно для таких вещей используются PK/FK, но в аналитических базах эти ограничения все равно не работают.

Но данные-то все равно связаны, не зависимо от наличия FK.
Выявить эти связи можно, проанализировав DML-запросы за некоторый период.
На основании этих данных можно не только попытаться автоматически задать PK и FK, но и найти те запросы, которые выбиваются из общей массы.

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

8) QUERY CATALOG/DISCOVERY TOOL
Еще было бы неплохо не делать одну и ту же работу несколько раз. Если запрос, похожий на тот, который мы хотим сделать, уже кто-то писал, то его просто можно отыскать.

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

9) ETL CODEGEN
А еще можно попробовать генерить ETL пайплайны из SQL. Прикиньте, не надо будет разработчиков заставлять питон учить :)
AST + IR.

10) DOCOPS
Еще одно применение - автоматизация создания документации и контроля за ее актуальным состоянием.
AST + IR.

11) DATA LINEAGE
Пункты (5)-(10) объединяются под одним общепринятым названием - родословная данных. Если мы можем проследить связи между данными с момента их появления в системе, мы можем много что узнать и главное, предотвратить.

Причем отслеживать только SELECT совершенно недостаточно. Так же важно распознавать связи из TABLE CLONE, TABLE AS SELECT, multitarget INSERT, UPDATE и MERGE.
В Snowflake так же имеются специфичные инструменты вроде SNOWPIPE и TASK+STREAM

12) СОЗДАНИЕ ИНСТРУМЕНТОВ ДЛЯ РЕДАКТИРОВАНИЯ И ПОДСВЕТКИ SQL КОДА
Не самая популярная опция, тем не менее, для тех, кто этим занимается, важно иметь AST, компилятор, deparser и другие необходимые инструменты.

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

Это даст возможность формулировать ранее недоступные задачи. Например "измените схему БД так, чтоб заданный запрос возвращал 1 строку" или "исправьте запрос, чтобы он не могу выбросить следующие исключения".
У кого-нибудь есть знакомые в coursera или udemy? )

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

Чтобы понять больше о наших потенциальных участниках и о том, какой формат школы выбрать, нам нужна ваша помощь: если бы вы хотели поехать, заполните, пожалуйста, небольшую форму https://docs.google.com/forms/d/e/1FAIpQLScfJ3tguxHNgxwHX_XpsWOiABTwB2R74sKh6OXEmcpYCCQqOQ/viewform.

Кому претифаер для постгри нужен был? )

 This totally doesn't work yet. 

https://github.com/houseabsolute/pg-pretty

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

Особенно это касается cloud-only баз, которые могут быть обновлены фактически в момент работы запроса :)
Как минимум по этой причине круто было бы иметь инструмент, который может предупредить об этих особенностях.

Все, что касается PostgreSQL, больше вызывает эмоции "нифига себе, че бывает", в вот с локомотивом аналитических баз Snowflake все намного интереснее...

Я сделал несколько выпусков SQL-TIL, рекомендую:
t.me/nosingularity/607

Но сегодня будет неизданное про Snowflake.
Пожалуйста, дайте знать, если что-то из описанного является нормой и я зря гоню :)

Snowflake "special" behavior:

Никакие ограничения кроме NOT NULL в этой базе не применяются.
По определению PRIMARY KEY = UNIQUE + NOT NULL
Видимо, из-за NOT NULL имеются очень странные сайд-эффекты с PK:

1) Не запрещено делать многоколоночный PK/UNIQUE по одной и той же колонке:

CREATE TABLE t (id INT, CONSTRAINT pk PRIMARY KEY (id, id));

2) Только inline PK гарантирует NOT NULL

CREATE TABLE t1 (id INT PRIMARY KEY);
INSERT INTO t1(id) VALUES (null); -- not ok

CREATE TABLE t2 (id INT, CONSTRAINT pk PRIMARY KEY (id));
INSERT INTO t2(id) VALUES (null); -- ok

CREATE TABLE t3 (id INT);
ALTER TABLE t3 ADD CONSTRAINT pk PRIMARY KEY (id);
INSERT INTO t3(id) VALUES (null);  -- ok

3) Из предыдущего поведения автоматически следует, что многоколоночный PK не будет гарантировать ограничение NOT NULL.
Соответственно (1) так же не будет гарантировать NOT NULL.

4) Даже несмотря на то, что ограничение UNIQUE поддерживается только из-за совместимости со стандартом SQL, Невозможно создать UNIQUE после PK, а наоборот можно.

CREATE TABLE t1 (id INT PRIMARY KEY);
ALTER TABLE t1 ADD CONSTRAINT u UNIQUE (id); -- not ok

CREATE TABLE t2 (id INT UNIQUE);
ALTER TABLE t2 ADD CONSTRAINT pk PRIMARY KEY (id); -- ok

Казалось бы, да и черт с ними, раз UNIQUE все равно не применяется. Да, но теперь нельзя надеяться на NOT NULL, там где вы создали PK. Как тебе такое, Илон?

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

5) Допустимы невозможные параметры функции
Кажется, что с чем чем, но с округлением вопросов быть не должно:
CEIL - вверх
FLOOR - вниз
ROUND - или вверх, или вниз

Но в Snowflake что-то пошло не так...

SELECT CEIL(1::FLOAT, 1.1); -- 1.032626705
SELECT CEIL(1::FLOAT, 1.2); -- 1.009531751
SELECT CEIL(1::FLOAT, 1.5); -- 1.011928851
SELECT CEIL(1::FLOAT, 10); -- 1

SELECT CEIL(1::FLOAT, -1); -- 10
SELECT CEIL(1::FLOAT, -1.1); -- 12.589254118
SELECT CEIL(1::FLOAT, -1.2); -- 15.848931925
SELECT CEIL(1::FLOAT, -1.5); -- 31.622776602
SELECT CEIL(1::FLOAT, -10); -- 10000000000

SELECT FLOOR(1::FLOAT, 1.1); -- 0.9531938817
SELECT FLOOR(1::FLOAT, 1.2); -- 0.9464360167
SELECT FLOOR(1::FLOAT, 1.5); -- 0.9803060747
SELECT FLOOR(1::FLOAT, 10); -- 1

SELECT FLOOR(1::FLOAT, -1); -- 0

Я не представляю, что это значит, но мне почему-то страшно...
Попробуйте поискать эти числа в документации:
https://docs.snowflake.com/en/search.html#q=1.032626705

6) Давайте будем трактовать параметры функции по-разному, в зависимости от того, в какой диапазон эти параметры попадают
https://docs.snowflake.com/en/sql-reference/functions/to_date.html#usage-notes

7) Или давайте трактовать параметры как... как-нибудь!

SELECT next_day(current_date(), 'Friday-night'); -- ok!

Приблизительно так же при использовании COLLATE - там можно все :)

Я понимаю, что часто строгость бывает избыточной. Но представьте, что все эти функции используются в ETL процессах, после которых не узнать как данные выглядели в источнике.
Что мне делать с 1.032626705 после "округления"?

А что мне делать с датами, полученными из TO_DATE на границе перехода параметров?
Или на сколько можно доверять next_day, если в параметр попадет случайный кусок текста?

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

SELECT next_day(current_date(), a)
FROM (VALUES
        ('friday'), ('fr'), ('fri')
) t(a);

SELECT next_day(current_date(), a)
FROM (VALUES
    ('freedom'), ('frozen'), ('france')
) t(a);

8) Алиасы могут совпадать с ключевыми словами.
Это валидный синтаксис:

SELECT * FROM EMPLOYEES LIMIT LIMIT 1;

9) Совершенно неочевидное автоматическое приведение типов.
Подозреваю, что не один я смущен таким поведением (Snowflake vs PostgreSQL):

-- SNOWFLAKE
SELECT
    typeof(1 % 2), -- INTEGER
    typeof(1.0 % 2), -- INTEGER
    typeof(1 % 2.0), -- INTEGER
    typeof(1 % 1.2), -- DECIMAL
    typeof(4.5 % 1.2) -- DECIMAL
;

-- POSTGRESQL
SELECT
    pg_typeof(1 % 2), -- integer
    pg_typeof(1.0 % 2), -- numeric
    pg_typeof(1 % 2.0), -- numeric
    pg_typeof(1 % 1.2), -- numeric
    pg_typeof(4.5 % 1.2) -- numeric
;

10) Возможно удалить объекты, имеющие зависимые объекты.
Запросто можно удалить таблицу, от которой зависит VIEW. Или таблицу, или у которой есть зависимые STREAMS.
Из VIEW станет невозможно получить данные, а все объекты-сироты будут видны при экспорте DDL.

Я отлично понимаю, что все что происходило на этой неделе тревожит довольно небольшое количество людей в реальной жизни.
Отвечу словами Марти Макфлая из "Назад в будущее-2":
Наверное, вы еще не готовы к такой музыке, но вашим детям она понравится.
:)

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

Смысл работы любого разработчика ПО заключается в уменьшение энтропии :)

Я стараюсь формализовать то, что раньше формализовать не получалось.

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

​Что может пойти не так с тайпкастом к строке, если вы работаете с несколькими базами?

Выяснилось, что dbt для генерации уникального id берет md5 от объединения приведенных к строкам значений из колонок.

Отдельный сюрприз будет ожидать, если у timestamp указана размерность...

Я тут крутую ORM-ку нашел!..

... для регекспов
... на расте

https://github.com/pemistahl/grex

https://twitter.com/mebeim/status/1373763490465333248?s=21

Продолжаем любить YAML

Геймдев это не только тотальный говнокод, но еще и очень много творческой работы.

Отличная документалка про бекстейдж суперигры Samorost:
https://www.youtube.com/watch?v=6W3kXEGy_GQ
Задроты 80lvl!

Пользуясь случаем напомню историю одного рекламного плаката для моего уже несуществующего интернет-магазина.

И еще пара постов на геймдевную тему:
https://news.1rj.ru/str/nosingularity/469
https://news.1rj.ru/str/nosingularity/570

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

Короче, я принял оффер...
Теперь я MongoDB Senior Developer Advocate at Developer Relations Team (CEEMEA)

Друзья-стартаперы попросили пофорсить их вакансию:

https://news.1rj.ru/str/profunctor_jobs/1147

Иногда наваливается тупняк.
Но просто сидеть и ничего не делать не позволяет то, что стартап :) Как говорится, "никто, кроме нас".

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

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

В PostgreSQL это делается просто - SELECT из information_schema и вот тебе все операторы и функции.
Кстати, в PostgreSQL можно делать свои операторы.

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

Но иногда приходится "просыпаться"...

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

Например, числовых всего два - NUMBER (фиксированная точка) и FLOAT (плавающая точка).
И все целые типы это NUMBER(38,0). Хочешь что-то этакого - задавай сам.

Зато даты и времени разных аж 5 штук.
А VARCHAR автоматом превращается в VARCHAR(16777216). Очень сильно надеюсь, что они не выделяют физически по 16 килобайт на каждую ячейку...

К слову, в PostgreSQL ШЕСТЬ разных числовых типов, не считая 3 разных SERIAL.

Как задается сложение в PostgreSQL?
integer + integer -> integer
bigint + bigint -> bigint

Как задается сложение в Snowflake?
При сложении двух NUMBER L1.S1 и L2.S2

Leading digits: L = max(L1, L2) + 1
Scale: S = max(S1, S2)
Precision: P = L + S

И надо не забыть проверить, чтобы L не превышал 38.

С.У.К.А.

Ладно, хоть с FLOAT + FLOAT -> FLOAT

Остальные операции задаются в том же духе

Если вдруг операндами будут числа, размерность которых не получится распознать на этапе компиляции (например, числа из JSON, которые имеют динамическую точность), то получится херня :)

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

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

Или вот агрегатные функции MIN и MAX

 The data type of the returned value is the same as the data type of the input values. 

Ага. Щаз!

 [22000][2016] SQL compilation error: Function MIN does not support ARRAY argument type 

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

 WITHIN GROUP (ORDER BY order_by_expr)

и тип результата зависит от типа выражения order_by_expr ... mic drop ...

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

Вот, кстати, эта красавица

Давайте-ка попробуем что там в качестве константы запихать можно...

 select system$typeof(PERCENTILE_CONT(0.11111111111111111111111111111111111111) within group (order by a))  from t; 

БАДУМС!

 [22023][1015] SQL compilation error: argument 0 to function PERCENTILE_CONT needs to be constant, found 'CAST(0.1111111111111111 AS NUMBER(18,0))' 

Сейчас вот не понял...
Почему, блин AS NUMBER(18,0) ? И почему разрядов после точки в ошибке меньше, чем я указал?
И почему если уменьшить количество разрядов на один, то все работает ?

Подождите расходиться, это еще не все :)

VAR_POP

 The data type of the returned value is NUMBER(precision, scale). The scale depends upon the values being processed. 

спасибо_нахер.jpg

PS: будем считать, что это очередной SQL-TIL :)

​Со всеми этими страртап челенджами забегался и забыл вам рассказать, что Mailru Cloud Solutions запилили нативную интеграцию с holistic.dev!

Теперь пользователям managed PostgreSQL в облаке MCS можно просто в пару кликов добавить расширение "Сервис для оптимизации Postgres (holistic)" в соответствующей вкладке в свойствах нужного инстанса.
Нужно будет указать имя базы, holisticdev api key, имя проекта в holisticdev

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

В плане нативной интеграции Mail.ru оказались впереди планеты всей!

А для YandexCloud, Selectel и SberCloud есть легкие интеграции через FaaS, список которых можно найти здесь:
https://docs.holistic.dev/cloud-service-integrations

Если у вас есть желание и возможность помочь запилить FaaS-интеграции для aws/gcp/azure, постучите, пожалуйста мне: @antonrevyako

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

https://news.1rj.ru/str/snowflakedbchat

Поглядываю на разные ключевики в твитере и вот такое попалось по "static analysis".
Может пригодится кому.

Static analysis tool for Android/iOS apps focusing on security issues outside the source code

https://github.com/nightwatchcybersecurity/truegaze

Сегодня какой-то день фронта, извините...

Король разработки оккупировал jsunderhood, а злой фронтенд разразился душной телегой на тему lodash.

И нет повода не напомнить вам про последнего :)

Как мы знаем, sqlite не нужен, но мне больше понравилась сцена с унижением mysql :)

Забыл спросить, зачем SQLite, когда есть Clickhouse-local ? (идите в главу "ClickHouse без сервера")

https://habr.com/ru/company/oleg-bunin/blog/551572/