TypeQL: SQL для аналитиков, который знает о данных всё

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

Перед тем как начать восхвалять своё детище, нужно основательно поругать SQL. Итак, что же мне в нём не нравится:

Проблемы SQL

• Нет функций и циклов (некоторые БД реализуют, но в стандарте нет). А это явно необходимо, если мы хотим писать большие запросы и переиспользовать код

• Переусложнённый синтаксис — WHERE, HAVING, QUALIFY по сути выполняют операцию фильтрации (эту проблему решил другой интересный проект PRQL ^[1])

• Хочется хороший LSP сервер, чтобы язык больше опирался на структуру БД, знал что с чем можно джойнить, какие там типы связи 1:m, m:1, не давал складывать строки с числами ещё на этапе написания кода

Я ещё долго и подробно могу критиковать SQL, как и каждый из нас, но пора двигаться дальше.

Новый подход

Я хочу предложить язык, который будет:

1. опираться на структуру данных

2. метрико-ориентированным

Что же такое структура данных? В классическом подходе это список таблиц, у каждой таблички есть колонки, у каждой колонки тип и некоторые ограничения:

• PRIMARY KEY — уникальное значение, по смыслу поле, по которому предполагается искать нужную строку в табличке

• FOREIGN KEY — ссылка на колонку в другой таблице (обычно primary key). Строка �� зависимой таблице должна определяться однозначно

• UNIQUE — уникальное значение

• NOT NULL — оно и в Африке not null

• CHECK — произвольное ограничение, например status IN ('created', 'completed') или age > 0

Вот собственно и всё. Использовал бы SQL эту информацию о БД — уже было бы прекрасно. Но раз уж мы создаём новый язык, можно и структуру данных по-новому описать.

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

В реальном (объектно-ориентированном) мире у нас есть:

• Типы (структура таблицы — какие у неё колонки и какие у них ограничения)

• Объекты (строки таблиц) — обязательно имеют какой-то тип

• Параметры объектов (колонки таблиц)

• Другие объекты также могут быть параметрами

• Для каждого объекта однозначно определяются значения его параметров

Например, пусть у меня есть биржа, описывающаяся 3-мя табличками:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100),
  invited_by INT,

  FOREIGN KEY (invited_by) REFERENCES users(id)
);

-- товары 2 типов: либо питомец, либо техника
CREATE TABLE goods (
  id INT PRIMARY KEY,
  is_alive BOOLEAN NOT NULL,
  sex VARCHAR(100), -- у питомцев есть пол
  brand VARCHAR(100) -- у техники есть бренд
);

CREATE TABLE deals (
  id INT PRIMARY KEY,
  seller INT NOT NULL,
  buyer INT NOT NULL,
  good_id INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  currency VARCHAR(100) NOT NULL,
  deal_date DATETIME NOT NULL,

  FOREIGN KEY (seller) REFERENCES users(id),
  FOREIGN KEY (buyer) REFERENCES users(id),
  FOREIGN KEY (good_id) REFERENCES goods(id)
);

Давайте переделаем в объектно-ориентированное представление, напишем его на псевдо-питоне:

class User:
    def id() -> NumberSQL: ...
    def name() -> StringSQL: ...
    def email() -> StringSQL | Null: ...
    def invited_by() -> User | Null: ...

class Pet:
    def id() -> NumberSQL: ...
    def is_alive() -> BooleanSQL: ...
    def sex() -> StringSQL: ...

class Machine:
    def id() -> NumberSQL: ...
    def is_alive() -> BooleanSQL: ...
    def brand() -> StringSQL: ...

class Deal:
    def id() -> NumberSQL: ...
    def seller() -> User: ...
    def buyer() -> User: ...
    def good() -> Pet | Machine: ...
    def price() -> NumberSQL: ...
    def currency() -> StringSQL: ...
    def deal_date() -> DateTimeSQL: ...

Такое представление очень близко к 4-ой нормальной форме, даже немного шире — позволяет описывать discriminated union типа def good() -> Pet | Machine: ... В БД такую зависимость красиво не опишешь.

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

Идея не нова — похожее представление описывал Давид Спивак например тут ^[2]. И пока готовился к этой статье, наткнулся на проект, который пошёл ещё дальше, применив полноценную теорию типов для описания структуры БД: https://typedb.com ^[3] (вообще проект с очень близкой идеей). Как я его раньше не нашёл!

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

Итак, у нас уже есть структура данных — её знает IDE и может подсказывать:

Уже прекрасно! Вон и комментарии к каждому параметру отображает!

Метрико-ориентированный подход

Но нам пора двигаться дальше к созданию полноценного Query Language. Тут мы вспоминаем, что мы делаем именно метрико-ориентированный язык, поэтому результат любого запроса — метрика (связка размерность → параметр), где размерность — это один из наших типов (или декартово произведение нескольких), а параметр как и раньше — то, что однозначно определяется по размерности.

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

d = BD().deals  # это размерность нашей метрики — элемент класса Deals
price_usd = caseSQL({
    d.currency == 'RUB': d.price * 0.0127,
    d.currency == 'EUR': d.price * 1.1,
    d.currency == 'USD': d.price,
})  # в зависимости от валюты домножаем на нужный обменный курс

d.price_usd = price_usd  # теперь его будет подсказывать IDE

(Тут нужно оговориться, что это синтаксис, который я хотел бы видеть; в реальности его добиться не удалось — пришлось сделать более громоздко)

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

Например, нашу метрику мы можем задать в виде функции:

def price_usd(rub_rate: float, eur_rate: float):
    return caseSQL({
        d.currency == 'RUB': d.price * rub_rate,
        d.currency == 'EUR': d.price * eur_rate,
        d.currency == 'USD': d.price,
    })

d.price_usd = price_usd  # чтобы подсказывала IDE

Кстати, идея, что метрико-ориентированный язык для аналитических запросов — это хорошо, тоже не нова, описана например тут ^[4].

Формализм метрик

Ура, мы научились вычислять простые метрики! В питоновской реализации я определил метрику как generic type: result[source], где result — это тип параметра, а source — тип, указывающий на размерность. У метрики из нашего примера тип NumberSQL[DealsSrc].

Опишем, что мы уже умеем делать с метриками в новом формализме. Мы можем выполнить любую скалярную функцию (допустимую в SQL), если:

1. У аргументов одинаковый source. У результата source будет такой же: f(a[s], b[s], c[s]) -> metrica[s]. А вот f(a[s1], b[s2]) выдаст ошибку

2. Их типы допустимы для данной функции (NumberSQL + NumberSQL — можно, NumberSQL + StringSQL — нельзя)

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

Агрегация

Тут всё просто. Для агрегации мне нужно:

1. Агрегационная функция (их всего 5: aggSum, aggAvg, aggMin, aggMax, aggCount, aggUniq)

2. Метрика (которую мы будем агрегировать) metrica[source]

3. Путь к новой размерности newSource[source] (фактически тоже метрика). Это может быть tuple из нескольких путей (newS1[source], newS2[source])

В результате получится метрика уже с новым источником (размерностью) metrica[newSource]. Или, если было несколько путей, новый источник будет их декартовым произведением metrica[cartesian[newS1, newS2]].

В общем-то поведение полностью аналогично такому запросу:

SELECT
    path,
    SUM(metrica) -- или другая агрегационная функция
FROM some_table
GROUP BY path

Пример метрики с явным указанием типов для наглядности (в реальности всё вычисляется):

result: NumberSQL[UserSrc] = aggSum(
    deals.price: NumberSQL[DealsSrc],
    deals.buyer: User[DealsSrc]
)

Единственное отличие — что результат остаётся метрикой и её можно дальше переиспользовать.

Вобщем-то и всё

К этому моменту мы научились делать всего 4 вещи:

Для параметров:

1. Вычислять скалярные функции от параметров

2. Вычислять агрегационные функции

Для размерностей (источников):

1. Создавать новые размерности через декартово произведение имеющихся

2. Создавать новые размерности через сложение имеющихся (то, что в питоне через | обозначается, а в SQL это UNION ALL)

Как ни странно, нам этого уже достаточно, чтобы выразить любой SELECT запрос (ORDER BY не хватает, но это мелочь). Конечно, хочется ещё оконных функций — их хоть и можно выразить через подзапросы, но уж очень это неудобно. Так что надо бы придумать, как добавить их в мой синтаксис.

На счёт фильтрации: отфильтровать строки сейчас можно через caseSQL, например так:

success_deals = caseSQL({
    deals.status == "success": deals
}, default=Null)

Но поскольку операция частая, наверное под неё стоит выделить отдельную функцию.
Вот собственно и вся моя идея метрико-ориентированного и хорошо типизированного языка на замену SQL. Интересно, кто что думает — пишите, буду всё читать.

Вот тут https://github.com/korbash/typeql ^[5] я реализовал прототип компилятора в SQL. Компилировать он не умеет и схема данных тоже захардкожена, но можно посмотреть примеры запросов ^[6] и потестить как IDE помогает писать запрос: знает, у кого какие параметры, не даёт сложить метрики с разными источниками, не даёт сложить строку и число.

Технические сложности

Надо сказать, что при написание компилятора у меня возникли сложности — я упёрся в лимиты типизации в питоне, уже приходится использовать всякие костыли. Даже нашёл баг ^[7] в Pyright.

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

И вот эта особенность, как ни странно, мало у каких языков есть. Она есть у Python и TypeScript, а вот известные хорошей работой с типами языки тут по этому параметру подходят плохо: Lean, OCaml, Haskell — всё мимо. Scala возможно, но не уверен.

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

Генерация схемы по БД

На счёт генерации схемы по БД тут всё не так печально. Все БД поддерживают комментарии к столбцам, и в этих комментах через специальные теги типа @id, @ignore, @virt(currency.id ^[8]), @link(pets, machines) можно передавать дополнительную информацию о схеме — то, что не удалось передать стандартными способами через PRIMARY / FOREIGN KEY.

Они легко парсятся, кроме них может быть и обычный человеческий комментарий. Теги компактные — читать комментарий не мешают.

Применения

Вообще, изначально всё началось с того, что меня сильно выбесило, что в BI-системах сложно встраивать фильтры. Нельзя просто написать запрос и чтобы фильтры сами по нему подтянулись — для каждого нужно указывать, какие у него допустимые значения или каким запросом их получить. Это если говорить про инструменты типа Superset, Metabase. Если про DataLens, Tableau, то там другая проблема — они по факту всё объединяют в одну большую таблицу (через вьюшку, но всё равно) и потом с ней работают. А тут уже теряется структура данных.

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

buyerFilter: User[DealsSrc] = filter(deals.buyer)

(Тип так указал для лучшего понимания)

В дашборде уже ничего дополнительно указывать не нужно — он уже всё знает. Из фильтра знает что фильтруем по пользователям, какие у пользователя есть параметры известно из структуры данных, по всем ним прям в UI можно настроить фильтрацию — хоть по дате регистрации, хоть по стране или возрасту. А дефолтные параметры можно передать прямо в коде запроса.

Поэтому язык в первую очередь именно для встраивания в BI и системы изучения данных. В будущем возможно в ELT pipelines типа SQLMesh или dbt. Для не-аналитических запросов мой язык наверное будет бесполезен.