Re: Оптимизация работы с sql - Философия программирования

Здравствуйте, Gt_, Вы писали:

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

MVK>>Или нужно объяснить?
Gt_>о, давай. будет забавно посмотреть на потуги чудика который еще вчера считал, что спарк лишь передает запрос.
детальное объяснение ниже

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

В результате ты постебался сам над собой

Попробую опять на пальцах на простом примере, spark demystified.

Берем типовую аналитическую задачу: на входе скажем сотня миллиардов записей, на выходе тысяча с посчитанными агрегатами.
Есть две опции где 100 миллиардов записей превратяться в тысячу: spark и clickhouse

Эти две опции выливаются в три возможных сценария:
1) Reduce через расчет агрегатов происходит на стороне спарка. Кликхауз работает как хранилище.

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

Для кликзауза это серия запросов вида:
select dim1, dim2, dim3, val1, val2, val3 from table where id between 1 and 1e9
select dim1, dim2, dim3, val1, val2, val3 from table where id between 1e9+1 and 2e9
...
select dim1, dim2, dim3, val1, val2, val3 from table where id between 99e9+1 and 100e9

Это сценарий, который ты описываешь, когда кликхауз рассматривается просто как "сторадж".
В этом случае данные действительно будут загружены в датафреймы (возможно кусочками), а потом к ним будут применены агрегаты на стороне спарка. Так спарк используют при работе с условными csv файлами в HDFS или гетерогенными источниками. Конечно, можно его так использовать и при работе с Clickhouse, но это как таскать ишака на себе вместо того, чтобы на нем ехать.

2) Расчет агрегатов делается в кликхауз, но spark делает параллелизацию.
Очевидно, что лучше чем первый вариант.
Для кликзауза это будет серия запросов вида.
select dim1, dim2, dim3, agg(val1), agg(val2), agg(val3) from table where id between 1 and 1e9 group by dim1, dim2, dim3
select dim1, dim2, dim3, agg(val1), agg(val2), agg(val3) from table where id between 1e9+1 and 2e9 group by dim1, dim2, dim3
...
select dim1, dim2, dim3, agg(val1), agg(val2), agg(val3) from table where id between 99e9+1 and 100e9 group by dim1, dim2, dim3

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

3) Все делаем в кликхаузе. В этом случае спарк нам вообще не нужен.
Для кликзауза это простой запрос вида:
select dim1, dim2, dim3, agg(val1), agg(val2), agg(val3) from table group by dim1, dim2, dim3

На 99.9% задач это будет самый быстрый вариант. Разница в производительности секунды/минуты vs часы. Собственно, Clickhouse и создавался, чтобы быстро решать такой класс задач. Поэтому да "спарк просто передаст запрос в кликхауз", любой другой вариант приведет к лишнему коду и просадке производительности. Более того, если уж ты правильно сложил данные в кликхауз, то потребность в спарке вообще отпадает, как я уже сказал на 99.9% задач, поэтому я тебе и написал, что спарк ничего не даст. А там где он действительно нужен, ты совсем не понимаешь даже, как оно должно работать.

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

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

1) Данные хранятся в различных источниках. Аналитика каждый раз собирается из этих источников. Результаты возможно где-то материализуются. Сюда попадает спарк, различные инструменты виртуализации данных типа Denodo. Из плюсов — быстрый сетап. Из минусов — все работает очень медленно. Задачи exploratory analytics, где важно оптимизировать цикл "гипотеза — проверка" решаются очень плохо.

2) Данные собираются в одной платформе заточенной под аналитические задачи (Clickhouse, Vertica, etc), где строятся плоские денормализованные представления для быстрого анализа. Из плюсов — если данные есть в платформе, то анализ делается с фантастической скоростью. Из минусов: 1) приходится ждать, когда данные появятся в платформе, 2) разные задачи требуют разного представления и использовать тот же кликхауз для анализа графов или работы с матрицами еще то извращение. Ну и join двух больших таблиц в Clickhouse или Vertica — это очень дорогое удовольствие, поэтому даже если данные есть, то не всегда можно легко построить поверх них аналитику.

3) Гибридный вариант, который чаще всего и встречается на практике. Наиболее востребованные данные собираются (тем же спарком) в нескольких базах, заточенные под разные аналитические задачи: time series, классический olap, анализ графов и так далее. При этом для данных, которые недоступны в аналитических платформах, можно быстро собрать ту же спарковскую джобу которая или создаст новое представление в аналитеческой платформе, или быстро выкатит результат. Из минусов — очень дорого. Из плюсов — гибкость и скорость решения. Плюс это позволяет на уровне платформ разделить роли дата инженера и дата аналитика. Первые отвечают за создание и поддержку различных представлений в аналитических платформах, а вторые пользуются этими платформами для анализа. Первые используют инженерный стек, например, scala, spark, kafka и вот это все, а вторые сидят в питонах, матлабе и Tableau. В моем случае используется гибридный вариант, хотя там много чего еще нужно строить и перестраивать.

------

Ну и для полноты картины, опишу тот самый 0.1% ситуаций, когда данные лежат в кликхаузе, но спарк может помочь.
1. Нужно сджойнить данные в кликхаузе с данными из другого источника.
2. Невозможно провести вычисления на стороне кликхауза. Несмотря на очень богатый набор функций для аналитики, есть задачи которые не решаются на уровне sql запроса.
3. Задача настолько memory intensive или cpu intensive, что выигрыш от переноса вычислений на вычислительный кластер превышает проигрыш от перекачки данных
4. Данные лежат в distributed таблицах и спарк знает о критериях шардирования/партицирования (это отдельный топик, я не буду его затрагивать по причине отсутствия опыта в оптимизации таких сценариев)

Типовая стратегия в таких ситуациях, особенно когда количество данных переваливает за терабайты — это постараться максимально редуцировать данные на стороне кликхауза. Возможно решения обычно такие:
1) Предварительная агрегация
2) Декомпозиция сложного вычисления на функции поддерживаемые кликхаузом
3) Семплинг

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

На счет чтения туторилов — согласен, дело хорошее, чего и тебе желаю.

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

Поэтому у нас в базисе пока:
1) У тебя очень ограниченное представление о том, для чего нужен spark и как он используется. Тебе знаком один сценарий использования и ты пытаешься его натянуть на все остальное.
2) Ты не понимаешь, что такое spark dataframe и не знаешь про rdd
3) Ты не знал, что спарк умеет генерить SQL (pushdown и pruning)
4) Для тебя новость, что spark написан на Scala. В противном случае советовать использовать решение написанное на умершем языке — это прям диверсия.
5) Видимо, ты даже плаваешь в понимании, что такое jvm (тут ты меня удивил очень сильно)
Поэтому я таки рассчитываю на слова благодарности за ликбез.

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

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

Здравствуйте, Gt_, Вы писали:

Раззадорил ты меня

Gt_>отлично, давай я постебаюсь над вторым пунктом. 8 лет опыта, ведь не составит труда показать откуда столь смешное прочтение пришло. ты утверждаешь что спарк знает об agg() функции клика и сделает агрегацию на стороне клика. поведуй нам, как вышло, что ты работал 8 лет и такую в пургу веруешь.

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

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

Во-вторых, тебя в гитхабе забанили? Если уж так интересно выяснить, поддерживает ли кликхаузовский коннектор SupportsPushDownAggregates (ответ: поддерживает), то вот тебе код коннектора.
Прости, но на умершей для тебя скале:

https://github.com/housepower/spark-clickhouse-connector/blob/5aaa994b4783284de7916a2fc8a81d732085d79a/spark-3.5/clickhouse-spark/src/main/scala/xenon/clickhouse/read/ClickHouseRead.scala#L79

https://github.com/housepower/spark-clickhouse-connector/blob/5aaa994b4783284de7916a2fc8a81d732085d79a/spark-3.5/clickhouse-spark/src/main/scala/xenon/clickhouse/SQLHelper.scala#L73

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

Gt_>потом поржем над остальными пунктами, но сейчас сфокусируемся над базисом. показывай откуда ты взял бред из второго пункта и что ты знаешь о каталист оптимиаторе ?
Жду!

Кстати, если поднимешь где-то тестовую среду и найдешь ошибки в моих оценках, то будет реально круто.

Здравствуйте, fmiracle, Вы писали:

Спасибо!

F>Тогда посмотри на LinqPad, если еще такой не видел — микро-ide, в которой удобно писать запросы к БД и получать результаты, как в SQL-студиях, только запросы пишешь на linq (в качестве драйвера можно подключить тот же linq2db).
Я смотрел в него, когда еще на .net писал (8 лет назад), но забыл про него. Сейчас, к сожалению, .net в моем стеке нет совсем.
Посмотрел сейчас фичи, довольно интересно. Можно было бы поиграться, чтобы идей поднабраться, но самые интересные фичи, как ты и говоришь, в платной версии.
Поспрашиваю, может у кого в нашей компании есть. Но в любом случае поставлю бесплатную (триала я так понял у них нет).

F>Базовый функционал идет бесплатно даже, но вот intellisense они дают только в платной версии, без подсказок/дополнений эффективность не та, конечно

F>А в полной версии очень сильная штука.
Да, согласен.

Здравствуйте, Константин Л., Вы писали:

КЛ>я не настаиваю, но джук умеет делать реюз и довольно безопасную генерацию
КЛ>то, чего нет, допишешь raw sql
Мне кажется (могу ошибаться), что затраты на переключение между джуком и дописать raw sql сожрет весь выигрыш от реюза.
Но вообще стоит попробовать в любом случае, опять таки эксперимент даст новую пищу для размышлений.
Отпишусь по результатам. Спасибо за наводку.

КЛ>типовые не в смысле crud etc., а в смысле похожи друг на друга
ок, неправильно тебя понял

Здравствуйте, _FRED_, Вы писали:

_FR>Например, метрика "сумма". У неё параметры: поле, по которому суммировать и поля (или даже выражения) по которым группировать. Имя таблицы и имена и полей (и даже типы полей или содержание выражений) для группировки неизвестны. Пробовал разные движки и ни в одном нормального решения этой задачи почему-то не нашёл. можно пример на linq2db?

Хорошо бы для начала понять задачу.

Здравствуйте, Sinclair, Вы писали:

S>Пока непонятно, в чём именно однообразие. Вот эти вот sumIf(xxx, isFinite(xxx)) as xxx? Ну, напишите свой микро-DSL, который будет metric0.SumF() раскрывать в sumIf(metric0, isFinite(metric0)) as metric0.

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

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

Сначала сетап:
Одна из моих ключевых ролей — это анализ данных. В этой роли 80% кодинга проходит в SQL консоли. Я пробовал различные repl среды: Python, Scala, R и даже Julia, но скорость работы начинает ощутимо падать (возможно, я плохо их готовлю). Кстати, Julia — прекрасна!

1) Написание финального запроса проходит через большое количество экспериментов "гипотеза — проверка". Это обычно довольно простые запросы и их значительно проще делать в SQL. Типовой рабочий файл: много маленьких запросов, которые можно быстро запустить выделив код и нажав Ctrl-Enter.

2) Вывод результата в удобную таблицу в тех же jetbrains-овских продуктах очень важен. Даже R Studio не дает такого удобства, хотя из всего, с чем я работал — это самый удобный REPL инструмент для работы с данными. Удобнее чем jupyter notebook.

3) SQL запрос часто вставляется в Tableau, чтобы быстро покрутить и "почувствовать" данные.

4) Intellisense очень важен. В процессе работы постоянно создается большое количество промежуточных и временных таблиц и view, схема постоянно меняется. Опять же JetBrain-овские продукты умеют быстро перечитывать схему, чтобы заработал автокомлит (на самом деле у меня есть претензии к тому, как это сделано и что можно улучшить).

5) Возможности различных data manipulations фреймворков (pandas и иже с ними) раскрываются лишь в случае, когда все данные можешь затянуть в память. Все опробованные мною lazy решения имеют кучу ограничений. Про спарк — я там в отдельном топике расписал.

В результате, что ни пробуешь, все равно скатываешься в sql консоль. Даже свой data quality framework я сейчас переделываю под написание тестов на sql.

Я попробовал написать более реалистичный пример SQL запроса:

select
  toYear(transaction_date) as order_year,
  toQuarter(transaction_date) as order_quarter,

  --client dimensions
  client_name,
  rg_c.region as client_region,  

  -- product dimensions
  product_name,
  p.product_type,

  -- sales departments
  rg_s.country as sales_country,
  rg_s.region as sales_region,

  -- metrics
  sum(product_qty)   as total_product_qty,

  sum(product_price * product_qty) as total_sales_amount,
  sumIf(shipping_cost, isFinite(shipping_cost)) as total_shipping_cost,

  -- metrics avg
  sum(product_price)/sum(product_qty) as avg_product_price,
  sumId(shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost,

  -- eur metrics 
  sum(eur_rate * product_price) as total_sales_amount_eur,
  sumIf(eur_rate * shipping_cost, isFinite(shipping_cost)) as total_shipping_cost_eur,

  -- eur metrics avg
  sum(eur_rate * product_price)/sum(product_qty) as avg_product_price_eur,
  sumIf(eur_rate * shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost_eur,

  -- quality checks
  if(isFinite(rt.eur_rate), 'OK', 'EUR RATE IS NOT AVAILABLE') as eur_rate_check

from transactions t
  left join regions rg_c on t.client_country_code == rg_c.country_code
  left join regions rg_s on t.sales_country_code == rg_s.country_code
  left join product_catalog pc on t.product_id == pc.product_id
  left join rates rt on t.transaction_ccy = rt.currency and t.transaction_date == rt.date
where order_year >= 2020 
  and transaction_status != 'Cancelled'
  and product_qty > 0
group by 
  order_year,
  order_quarter,
  client_name,
  client_region,
  product_name,
  product_type,
  sales_country,
  sales_region;

Что раздражает: + возможные решения
1. Дублирование названия колонок в секции group by
— можно сделать макрос, чтобы автомкомплитил эту часть (в принципе, copilot это худо-бедно может)

2. Необходимость писать sum(metric) as metric для целой пачки метрик
— сделал простой vim-омский макрос, который делает это за меня

3. Типовые операции вида join rates и потом умножение на rate, чтобы перевести значение в eur. Или sumIf(...).
— сделал функцию to_eur(ccy, date, amount) (работает быстро, так как rates живет в памяти и обращение идет как в dictionary)
— сделал функцию которая возвращает 0, если значение не определено

4. Для любого запроса к таблице transactions есть набор основных полей который вытаскивается почти всегда. Т.е. если уж я написал select ... from transactions, то в 99% случаев я напишу select date, client_name, client_region, ... from transactions
— сделать view не подойдет, будет комбинаторный взрыв, так как дополнительные к основным полям всегда будут разными
— сделать макрос, который будет генерировать шаблон селекта с ключевыми полями из таблицы — вариант, но звучит как костыль
— натренировать бесплатный chatgpt 2 на истории запросов — вариант, но я как-то пока за него не готов взяться (хотя там ничего суперсложного вроде нет)

5. Тоже самое с метриками. В 90% случаев тащатся одни и те же. И агрегаты применяются одни и те же.
— см выше п. 4

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

7. Трансформации более высокого уровня. Это самая сложная тема. Мне нужно, наверное, подумать хорошо, чтобы браться это описать. Но попробую привести несколько примеров, чтобы пояснить идею:
7.1 переписать вышеприведенный пример в запрос, который вместо группировки по годам и кварталам будет выдавать ytd, prior_ytd, last_30_days для каждой метрики
7.2 для каждой строчки в агрегированном представлении добавить 10 строчек входящих в него top 10 by sales_amount записей. По факту нужно написать union all, скопировать оригинальный запрос, убрать агрегаты, убрать group by и в конце добавить order by sales_amount limit 10 by и опять скопипейстить все поля (кроме метрик)
7.3 удобный пивот запроса через трансформацию запроса, а не через использование конструкцию pivot/unpivot

Как это может выглядеть с точки зрения UX (фантазирую сходу в качестве иллюстрации):
1. Я остаюсь в sql консоли intellij или VS Code
2. Печатаю trades и нажимаю tab по аналогии с live templates
=> Появляется типовой запрос к таблице trades с основными атрибутами (возможно пробегаюсь табом и заполняю какие-то поля как в live templates тоже)
4. Выделю trades, нажимаю ctrl+j, пишу clients (j — join)
=> Появляется стандартный join на таблицу clients и стандартные атрибуты которые обычно берутся из clients
5. Выделяю весь запрос, нажимаю ctrl+g, ввожу year, quarter в промпте, нажимаю submit (g — group)
=> Получаю group by по всем измерениям, transaction_date изчезает и вместо него появляется toYear и toQuarter
6. Выделяю метрики sales_amount, total_shipping_cost нажимаю ctrl+a, выбираю to_eur (a — apply)
=> Появляется новые метрики рассчитанные в euro + join на табличку с eur_rates
7. Выделяю весь запрос, нажимаю ctrl+t, выбираю ytd (t-transform, template), ввожу дополнительные параметры, submit
=> Запрос превращается в новое представление с расчетом ytd, prior_ytd и так далее

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

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

В любом случае — это было очень полезное для меня самого упражнение.

Здравствуйте, IT, Вы писали:

IT>Хорошо бы для начала понять задачу.

Я там ниже в ответе Синклеру постарался расписать свой сценарий.
Интересно послушать мнение Фреда, совпадает ли это с его проблематикой.

Здравствуйте, IT, Вы писали:

_FR>>Например, метрика "сумма". У неё параметры: поле, по которому суммировать и поля (или даже выражения) по которым группировать. Имя таблицы и имена и полей (и даже типы полей или содержание выражений) для группировки неизвестны. Пробовал разные движки и ни в одном нормального решения этой задачи почему-то не нашёл. можно пример на linq2db?

IT>Хорошо бы для начала понять задачу.

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

using System.Data;

internal sealed class DbTable
{
  public required string Name { get; init; }

  // Все остальные необходимые метаданные о таблице
}

internal sealed class DbColumn
{
  public required string Name { get; init; }
  public required DbType DbType { get; init; }

  // Все остальные необходимые метаданные о поле
}

// Есть своё дерево выражений для описания преобразхований над полями и, например, фильтрации. С визиторами и всем прочим.
internal abstract class DbExpression
{
}

// Вот так вот в дереве выражений описывается поле таблицы, например
internal abstract class DbColumnExpression : DbExpression
{
  public required DbColumn Column { get; init; }
}

// Что требуется сделать
internal static class Metrics
{
  // First item is value, then dimensions in order
  public static IEnumerable<object[]> CalculateSum(DbTable table, DbExpression value, IEnumerable<DbExpression>? dimensions = null, DbExpression? filter = null) => [];
}

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

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

В EntityFrameworkCore некоторые простые вещи решаются с помощью EF.Property(), но вот как, например, сгруппировать по заранее неизвестным колонкам я не примдумал. А нужно ещё и разбивку по страницам, например.

В итоге пока самый рабочий вариант — это "ручное" построение текста запроса:

var select = new SqlSelect(table) {
  Items = {
    Sql.Sum(value),
    dimensions,
  },
  GroupBy = { dimensions, },
  Where = { filter, }
};

Хочется чего-то немного более высокоуровневого, но, возможно, слишком сложным будет:

var query =
  from item in table.AsSqlSource() // table, value, dimensions - те, что выше описаны в методе CalculateSum
  group item by dimensions into @group
  select new {
    Sum = @group.Sum(item => item),
    dimensions,
  };

То есть генерация запроса по некоторому мета-описанию сущностей.

Здравствуйте, MaximVK, Вы писали:

IT>>Хорошо бы для начала понять задачу.

MVK>Я там ниже в ответе Синклеру постарался расписать свой сценарий.
MVK>Интересно послушать мнение Фреда, совпадает ли это с его проблематикой.

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

В целом спасибо за топик, полезно знать, что эти проблемы не меня одного беспокоят

Здравствуйте, _FRED_, Вы писали:

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

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.Mapping;

namespace L2DBTest
{
    internal class Program
    {
        static DataOptions _options = new DataOptions().UseSQLiteMicrosoft("Data Source=db.sqlite");

        static void Main(string[] args)
        {
            var metaData = new[]
            {
                new { TableName = "Customer", DimensionColumnName = "CustomerID", SumColumnName = "Total" },
                new { TableName = "Order",    DimensionColumnName = "OrderID",    SumColumnName = "Value" },
            };

            foreach (var data in metaData)
            {
                var mb = new FluentMappingBuilder(new())
                    .Entity<Table>()
                        .Member(e => e.DimensionColumn)
                            .HasColumnName(data.DimensionColumnName)
                        .Member(e => e.SumColumn)
                            .HasColumnName(data.SumColumnName)
                    .Build();

                using var db = new DataConnection(_options.UseMappingSchema(mb.MappingSchema));

                var q =
                    from t in db.GetTable<Table>().TableName(data.TableName)
                    group t by t.DimensionColumn into g
                    select new { DimensionColumn = g.Key, SumColumn = g.Sum(t => t.SumColumn) };

                Console.WriteLine(q.ToString());
            }

        }

        public class Table
        {
            public int DimensionColumn { get; set; }
            public int SumColumn       { get; set; }
        }
    }
}

Output:

--  SQLite.MS SQLite
SELECT
        [t1].[CustomerID],
        Sum([t1].[Total])
FROM
        [Customer] [t1]
GROUP BY
        [t1].[CustomerID]

--  SQLite.MS SQLite
SELECT
        [t1].[OrderID],
        Sum([t1].[Value])
FROM
        [Order] [t1]
GROUP BY
        [t1].[OrderID]

Здравствуйте, _FRED_, Вы писали:

Более простое решение с Property

using LinqToDB;
using LinqToDB.Data;

namespace L2DBTest
{
    internal class Program
    {
        static DataOptions _options = new DataOptions().UseSQLiteMicrosoft("Data Source=db.sqlite");

        static void Main(string[] args)
        {
            using var db = new DataConnection(_options);

            var metaData = new[]
            {
                new { TableName = "Customer", DimensionColumnName = "CustomerID", SumColumnName = "Total" },
                new { TableName = "Order",    DimensionColumnName = "OrderID",    SumColumnName = "Value" },
            };

            foreach (var data in metaData)
            {
                var q =
                    from t in db.GetTable<Table>().TableName(data.TableName)
                    group t by Sql.Property<int>(t, data.DimensionColumnName) into g
                    select new { DimensionColumn = g.Key, SumColumn = g.Sum(t => Sql.Property<int>(t, data.SumColumnName)) };

                Console.WriteLine(q.ToString());
            }
        }

        public class Table
        {
            public int DimensionColumn { get; set; }
            public int SumColumn       { get; set; }
        }
    }
}

Результат тот же.

Здравствуйте, IT, Вы писали:

IT>Более простое решение с Property

IT>            var metaData = new[]
IT>            {
IT>                new { TableName = "Customer", DimensionColumnName = "CustomerID", SumColumnName = "Total" },
IT>                new { TableName = "Order",    DimensionColumnName = "OrderID",    SumColumnName = "Value" },
IT>            };
  // …
IT>        public class Table
IT>        {
IT>            public int DimensionColumn { get; set; }
IT>            public int SumColumn       { get; set; }
IT>        }

Сложность в том, что и количество колонок, по которым нужна группировка, заранее не известно, то есть должно быть ("входные данные") как-то так:

IT>var metaData = new[]
IT>{
IT>    new { TableName = "Customers", DimensionColumnNames = ["Country", "Region", "City"],   SumColumnName = "Total" },
IT>    new { TableName = "Orders",    DimensionColumnNames = ["CustomerID", "EmployeeID"],    SumColumnName = "Freight" },
IT>    new { TableName = "Orders",    DimensionColumnNames = [],                              SumColumnName = "Freight" }, // Total
IT>};

Здравствуйте, _FRED_, Вы писали:

_FR>Сложность в том, что и количество колонок, по которым нужна группировка, заранее не известно, то есть должно быть ("входные данные") как-то так:

Есть какое-то разумное ограничение?

Здравствуйте, IT, Вы писали:

_FR>>Сложность в том, что и количество колонок, по которым нужна группировка, заранее не известно, то есть должно быть ("входные данные") как-то так:
IT>Есть какое-то разумное ограничение?

Если бы, тогда способ решения понятен

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

P.S. Вообще, мне сложно спредставить, чтобы кто-то за раз вытягивал бы более пяти измерений (UI иерархический, измерений может быть много, но они запрашиваются "по очереди"). Плюс измерения для даты (год/полугодие/квартал/месяц/день), не всегда они все нужны. Но типы у полей точно могут быть самыми разными (теми, по котороым имеет смысл делать группировку).

Здравствуйте, _FRED_, Вы писали:

_FR>Если бы, тогда способ решения понятен

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

Тогда генерация. Либо T4, либо Linq.Expressions. Второй способ сложнее, но не требует перекомпиляции при изменении метаданных.

Здравствуйте, IT, Вы писали:

_FR>>Если бы, тогда способ решения понятен

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

IT>Тогда генерация. Либо T4, либо Linq.Expressions. Второй способ сложнее, но не требует перекомпиляции при изменении метаданных.

Linq.Expressions я пробовал (но не доделал), получается (я имею в виду чтение и использование кода) ещё сложнее, чем генерация самого SQL.

Спасибо за помощь

Здравствуйте, MaximVK, Вы писали:

MVK>Такие простые сценарии можно упаковать в функции. Я, конечно, криво описал проблему (особенно это заметно по другим ответам). И, видимо, если смогу ее хорошо описать, то решение окажется на поверхности.
+1.

MVK>1) Написание финального запроса проходит через большое количество экспериментов "гипотеза — проверка". Это обычно довольно простые запросы и их значительно проще делать в SQL. Типовой рабочий файл: много маленьких запросов, которые можно быстро запустить выделив код и нажав Ctrl-Enter.
Ну, то есть нужен менее многословный способ описывать запросы. Тогда и промежуточные варианты, и окончательный запрос будут требовать меньше усилий.

MVK>4) Intellisense очень важен. В процессе работы постоянно создается большое количество промежуточных и временных таблиц и view, схема постоянно меняется. Опять же JetBrain-овские продукты умеют быстро перечитывать схему, чтобы заработал автокомлит (на самом деле у меня есть претензии к тому, как это сделано и что можно улучшить).

MVK>В результате, что ни пробуешь, все равно скатываешься в sql консоль. Даже свой data quality framework я сейчас переделываю под написание тестов на sql.
Ну, то есть нужен свой QL, который будет менее ужасен.

MVK>Я попробовал написать более реалистичный пример SQL запроса:

MVK>

MVK>select
MVK>  toYear(transaction_date) as order_year,
MVK>  toQuarter(transaction_date) as order_quarter,

MVK>  --client dimensions
MVK>  client_name,
MVK>  rg_c.region as client_region,  

MVK>  -- product dimensions
MVK>  product_name,
MVK>  p.product_type,

MVK>  -- sales departments
MVK>  rg_s.country as sales_country,
MVK>  rg_s.region as sales_region,

MVK>  -- metrics
MVK>  sum(product_qty)   as total_product_qty,

MVK>  sum(product_price * product_qty) as total_sales_amount,
MVK>  sumIf(shipping_cost, isFinite(shipping_cost)) as total_shipping_cost,

MVK>  -- metrics avg
MVK>  sum(product_price)/sum(product_qty) as avg_product_price,
MVK>  sumIf(shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost,

MVK>  -- eur metrics 
MVK>  sum(eur_rate * product_price) as total_sales_amount_eur,
MVK>  sumIf(eur_rate * shipping_cost, isFinite(shipping_cost)) as total_shipping_cost_eur,

MVK>  -- eur metrics avg
MVK>  sum(eur_rate * product_price)/sum(product_qty) as avg_product_price_eur,
MVK>  sumIf(eur_rate * shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost_eur,

MVK>  -- quality checks
MVK>  if(isFinite(rt.eur_rate), 'OK', 'EUR RATE IS NOT AVAILABLE') as eur_rate_check

MVK>from transactions t
MVK>  left join regions rg_c on t.client_country_code == rg_c.country_code
MVK>  left join regions rg_s on t.sales_country_code == rg_s.country_code
MVK>  left join product_catalog pc on t.product_id == pc.product_id
MVK>  left join rates rt on t.transaction_ccy = rt.currency and t.transaction_date == rt.date
MVK>where order_year >= 2020 
MVK>  and transaction_status != 'Cancelled'
MVK>  and product_qty > 0
MVK>group by 
MVK>  order_year,
MVK>  order_quarter,
MVK>  client_name,
MVK>  client_region,
MVK>  product_name,
MVK>  product_type,
MVK>  sales_country,
MVK>  sales_region;
MVK>

MVK>Что раздражает: + возможные решения
MVK>1. Дублирование названия колонок в секции group by
MVK> — можно сделать макрос, чтобы автомкомплитил эту часть (в принципе, copilot это худо-бедно может)
Тут всё довольно интересно.
SQL позволяет выводить в select не только ключи группировки, но и любые функционально зависимые от них поля. Поэтому — список select отдельно, список group by отдельно. В вашем случае выглядит так, что для вывода client-name и client_region группировать достаточно по client_id, аналогично для product_id и sales_region.
Если вам неинтересны такие мелкие оптимизации, то можно написать DSL, который автоматически выполняет то, что вам нужно, без дублирования. Просто вместо select ... и group by... в нём будут dimensions ... и measures ... .

MVK>2. Необходимость писать sum(metric) as metric для целой пачки метрик
MVK>- сделал простой vim-омский макрос, который делает это за меня
См. предыдущий пункт. Делаем в языке measures clause, в котором перечисляем выражения и методы агрегации, с автоматическим именованием. То есть sum(product_price) порождает колонку product_price, sum(product_price*product_qty) порождает колонку с именем "product_price*product_qty", sum(product_price*product_qty) as sales_total порождает колонку sales_total.

MVK>3. Типовые операции вида join rates и потом умножение на rate, чтобы перевести значение в eur. Или sumIf(...).
Все эти sumif просятся на замену функцией weighted average. С семантикой avg(eur_rate*shipping_price, product_qty) => sumIf(eur_rate * shipping_price*product_qty, isFinite(eur_rate*shipping_cost) )/sumIf(product_qty, isFinite(eur_rate*shipping_cost)).
MVK>- сделал функцию to_eur(ccy, date, amount) (работает быстро, так как rates живет в памяти и обращение идет как в dictionary)
ну вот, уже неплохо.
MVK>- сделал функцию которая возвращает 0, если значение не определено
Хм. А что, в вашей СУБД готовой функции coalesce/isnull нету?
MVK>4. Для любого запроса к таблице transactions есть набор основных полей который вытаскивается почти всегда. Т.е. если уж я написал select ... from transactions, то в 99% случаев я напишу select date, client_name, client_region, ... from transactions
Ну, по хорошему это могло быть чем-то вроде fieldset. В SQL встроены * и sourcename.* , и их, очевидно, не хватает. То есть берём и создаём список с именем transaction_commons — date, client_name, client_region.
MVK>5. Тоже самое с метриками. В 90% случаев тащатся одни и те же. И агрегаты применяются одни и те же.
Тут не вполне понятно, что такое "одни и те же" — но, наверное, тоже чинится при помощи fieldset.
MVK>- см выше п. 4

MVK>6. Одни и те же join-ы
MVK>- да, можно сделать вью, которые инкапсулируют логику джойна. так оно и работает, но есть одно "но"
MVK>- точно такие же джойны встречаются во многих других местах, но к другим таблицам. Это специфика работы с денормализованными данными
Непонятно, что такое "такие же, но к другим таблицам". Вообще, join в SQL настолько ужасен, что любое изменение способно его улучшить. В MySQL прикрутили t1 join t2 using(col1, col2), который разворачивается в t1 join t2 on t1.col1 = t2.col1 AND t1.col2 = t2.col2. Даже такое корявое решение позволяет сильно упростить запись джойна, хоть и опирается на именование полей вместо использования метаданных. Более нормальный способ был бы с join FK fk_name, т.к. внешний ключ позволяет однозначно понять и имя таблицы, и какие колонки с какими нужно сравнивать.
Ещё более нормальный способ был бы вообще выкинуть требование джойнить таблицы по FK, а разрешить использовать в select clause (ну, или в dimensions и measures воображаемого языка) имена FK в качестве квалификатора.
То есть пишем select id, customer.name from orders, а оно само разворачивается в select id, customer.name from orders join customers customer on customer.id = orders.customer_id, потому что у нас есть CONSTRAINT customer FOREIGN KEY (customer_id) REFERENCES Customers(ID).

MVK>7. Трансформации более высокого уровня. Это самая сложная тема. Мне нужно, наверное, подумать хорошо, чтобы браться это описать. Но попробую привести несколько примеров, чтобы пояснить идею:
MVK>7.1 переписать вышеприведенный пример в запрос, который вместо группировки по годам и кварталам будет выдавать ytd, prior_ytd, last_30_days для каждой метрики
MVK>7.2 для каждой строчки в агрегированном представлении добавить 10 строчек входящих в него top 10 by sales_amount записей. По факту нужно написать union all, скопировать оригинальный запрос, убрать агрегаты, убрать group by и в конце добавить order by sales_amount limit 10 by и опять скопипейстить все поля (кроме метрик)
MVK>7.3 удобный пивот запроса через трансформацию запроса, а не через использование конструкцию pivot/unpivot
Ну, вот такие вещи — это предмет для DSL.
MVK>Как это может выглядеть с точки зрения UX (фантазирую сходу в качестве иллюстрации):
MVK>1. Я остаюсь в sql консоли intellij или VS Code
MVK>2. Печатаю trades и нажимаю tab по аналогии с live templates
MVK> => Появляется типовой запрос к таблице trades с основными атрибутами (возможно пробегаюсь табом и заполняю какие-то поля как в live templates тоже)
MVK>4. Выделю trades, нажимаю ctrl+j, пишу clients (j — join)
MVK> => Появляется стандартный join на таблицу clients и стандартные атрибуты которые обычно берутся из clients
MVK>5. Выделяю весь запрос, нажимаю ctrl+g, ввожу year, quarter в промпте, нажимаю submit (g — group)
MVK> => Получаю group by по всем измерениям, transaction_date изчезает и вместо него появляется toYear и toQuarter
MVK>6. Выделяю метрики sales_amount, total_shipping_cost нажимаю ctrl+a, выбираю to_eur (a — apply)
MVK> => Появляется новые метрики рассчитанные в euro + join на табличку с eur_rates
MVK>7. Выделяю весь запрос, нажимаю ctrl+t, выбираю ytd (t-transform, template), ввожу дополнительные параметры, submit
MVK> => Запрос превращается в новое представление с расчетом ytd, prior_ytd и так далее
Думаете в правильную сторону, но DSL тут будет ещё лучше, чем голый SQL.
MVK>Важно, что все трансформации происходят в sql консоли. Я всегда могу выделить запрос и его запустить и посмотреть результат.

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

MVK>В любом случае — это было очень полезное для меня самого упражнение.
Да, верно. Это называется "метод утёнка": рассказываете свою проблему резиновой уточке. Несмотря на то, что уточка не разбирается в программировании, после разговора обычно из тупика находится выход

	От:	fmiracle
	Дата:	05.01.24 09:09
	Оценка:

От:	_FRED_	@ViIvanov
Дата:	05.01.24 19:09
Оценка:	+1

	От:	MaximVK
	Дата:	05.01.24 20:35
	Оценка:	120 (1)

	От:	Gt_
	Дата:	05.01.24 21:09
	Оценка:

	От:	MaximVK
	Дата:	05.01.24 22:10
	Оценка:

От:	IT	linq2db.com
Дата:	06.01.24 00:36
Оценка:	+1

От:	Sinclair	https://github.com/evilguest/
Дата:	07.01.24 06:13
Оценка:	33 (2)