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

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

MVK>По работе приходится очень много писать SQL кода.

Если речь про .NET, то сегодня писать SQL руками — это нонсенс. Тем более под разные диалекты SQL. linq2db и подобное всё это умеет и поддерживает в том числе всевозможные техники повторного использования кода.

Здравствуйте, 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 (тут ты меня удивил очень сильно)
Поэтому я таки рассчитываю на слова благодарности за ликбез.

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

Здравствуйте, 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 консоли. Я всегда могу выделить запрос и его запустить и посмотреть результат.

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

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

Gt_>>если миллиарды записей и развесистая логика где хочется нормального ООП, с наследованиями и патерн матчингом то надо брать spark.
MVK>Нет, ооп совсем не нужен, более того он тут только мешать будет. Спарк уже есть, конечно, но для других задач, для этой он плохо подходит.

именно тут спарк идеален, ты просто совсем нулевый как я погляжу.


Gt_>>как бонус сумашедшая параллельность, которую клик думаю любит. ну и упомянутые struct поля думаю спарк супортит.
MVK>А каким тут это боком? Спарк просто передаст запрос в кликхауз, кликзауз изобразит параллельность. Максимум, что он сможет это правильно раскидать запросы на кластер, но когда я последний раз туда смотрел, там все было кривовато с этим.

а зрение давно проверял ? ты явно не на спарк смотрел. спарк ничего не транслиует, спарк вычитывает в свой датафрейм и вся магия происходит в его jvm. клик для спарка просто тупой сторидж, мало чем отличающийся от S3.

MVK>Но в целом, если данные лежат в каком-то паркете на S3 кластере, то спарк, конечно хорош. Ну или если есть зоопарк различных баз и файлов, и задачи хорошо ложатcя на map reduce. Ну и скала довольно выразительна для таких задач тоже.

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

MVK>Мда, апломба много, а знаний ноль.

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

MVK>Одна вот эта фраза выдает полную неграмотность в вопросе: "как бонус сумасшедшая параллельность, которую клик думаю любит". Ты понимаешь, что у тебя полная бредятина написана?
MVK>Или нужно объяснить?

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

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

IT>…linq2db и подобное всё это умеет и поддерживает в том числе всевозможные техники повторного использования кода.

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

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

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

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

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

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

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

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

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

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

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>>};
_FR>

Вот первое что приходит на ум — это сделать какой ни будь класс SqlPattern который получает необходимые методанные и по ним строит запрос. Его можно использовать с паттерном Builder — это позволить создавать SqlPattern по частям. Еще есть вариант с формальными грамматиками. Если мы можем описать грамматику мета-языка по которому потом будут строится запросы, то можно написать парсер который будет получать входную строку мета-языка и по ней генерить sql-запрос.

По работе приходится очень много писать SQL кода.

Код довольно однообразен , например раздражает когда нужно для 10 метрик написать что-то типа (в синтаксисе кликзауз):
select
dim1,
dim2,
...
sumIf(metric0, isFinite(metric0)) as metric0,
...
sumIf(metric9, isFinite(metriс9)) as metric9
group by
dim1,
dim3,
...


Пока написал кастомные команды к ideavim, но хотелось бы чего-то более умного. В идеале доступа к ast и схеме базы, и манипуляций с ним.
Не очень пока понимаю, c какой стороны к этой задаче лучше подступиться, чтобы начать с малого и понемногу расширять количество тулов для повышения своей производительности.
Работаю в основном в JetBrains-овских продуктах.

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

MVK>Пока написал кастомные команды к ideavim, но хотелось бы чего-то более умного. В идеале доступа к ast и схеме базы, и манипуляций с ним.

Если у вас Постгрес, посмотрите на EdgeQL

А вообще эту часть айти только ядерная война исправит, и то не 100%

Возможно вам поможет генерация через linq2db или JOOQ и какие-то самописные расширения.

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

У меня микс, Clickhouse, Oracle, Postgre, MySQL и экзотика в виде kdb (но это вообще отдельный мир).
Возможно, еще MS SQL приедет
Но пока в основном Oracle и Clickhouse.

С>Если у вас Постгрес, посмотрите на EdgeQL
Спасибо, посмотрю.

С>Возможно вам поможет генерация через linq2db или JOOQ и какие-то самописные расширения.
linq2db и jooq — это, имхо, про другое. Не очень понятно, как мне их приткнуть.


>А вообще эту часть айти только ядерная война исправит, и то не 100%
да, пока все попытки это исправить приводили к еще большим проблемам

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

[]

С>>Возможно вам поможет генерация через linq2db или JOOQ и какие-то самописные расширения.
MVK>linq2db и jooq — это, имхо, про другое. Не очень понятно, как мне их приткнуть.

писать (генерить) экспрешны в jooq а потом генерить из них sql

[]

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

КЛ>писать (генерить) экспрешны в jooq а потом генерить из них sql

Ну это прямо скажем еще то извращение будет. SQL получится так себе, особенно если используешь какие-то не совсем типовые диалекты.
Я сомневаюсь, что, например, jooq умеет в high order functions и arrays в Clickhouse.

Идея именно в том, чтобы удобнее и быстрее было писать сам SQL.

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

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

КЛ>>писать (генерить) экспрешны в jooq а потом генерить из них sql

MVK>Ну это прямо скажем еще то извращение будет. SQL получится так себе, особенно если используешь какие-то не совсем типовые диалекты.

во-первых, почему "получится так себе"? Во-вторых, если такой диалект поддерживается jooq, то это проблема не jooq. В общем, я не понял.

MVK>Я сомневаюсь, что, например, jooq умеет в high order functions и arrays в Clickhouse.

так проверь

MVK>Идея именно в том, чтобы удобнее и быстрее было писать сам SQL.

если запросы типовые, то зачем его вообще писать? нужно его генерить по схеме с помощью jooq

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

IT>Если речь про .NET, то сегодня писать SQL руками — это нонсенс.
Ну, я бы сказал не нонсенс, а удел только узких нишевых решений.
Для большинства энтерпрайза/веба linq2db решает 100 процентов задач весьма эффективно.

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

IT>Если речь про .NET, то сегодня писать SQL руками — это нонсенс. Тем более под разные диалекты SQL. linq2db и подобное всё это умеет и поддерживает в том числе всевозможные техники повторного использования кода.

linq2db я активно использовал, когда писал под .Net. Очень удобно.


Отвечаю сразу и IT и Константину (спасибо за комментарии!)

У меня сейчас другая задача.
Нужно писать большое количество аналитических запросов и у меня нет необходимости вызывать этот sql код из приложения.
Максимум — это питонячий скрипт, который потом докручивает аналитику или ML.
С точки зрения кол-во строк кода 80-90% — это SQL скрипты, которые процессят относительно большие объемы, несколько миллиардов записей.
Поэтому используются различные оптимизационные техники, так как один и тот же запрос можно написать очень по разному.
Но так как SQL очень а) избыточен, б) плохо композируется, в) плохо поддерживает переиспользование, то приходится постоянно писать плюс/минус одинаковые куски кода, что раздражает. Code pilot помогает, но далеко не всегда ну и это не совсем то, что хотелось бы.

Update:
Из близкого к тому, что может как-то помочь — это питонячий dbt. Но это по сути темплейтер на стероидах: текстовые макросы + "компиляция". В кавычках, потому что как таковой компиляции (как MS SQL project) там, конечно, нет и близко. Но dbt закрывает лишь часть задач, и я все еще сильно сомневаюсь в том, чтобы его тащить в проект.

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

Отписался более подробно в треде с IT. Здесь отчету про jooq.

КЛ>во-первых, почему "получится так себе"? Во-вторых, если такой диалект поддерживается jooq, то это проблема не jooq. В общем, я не понял.
Я посмотрел, что умеет jooq. Не очень понятно в чем будет выигрыш, при условии что мне нет необходимости вызывать sql из java.
Бесспорно, полезная штука, если пилишь приложение, которому нужно ходить в базу, но для дата аналитических задач — только усложнит и удлинит процесс разработки.

КЛ>так проверь
Clickhouse поддержки нет на официальном сайте, но похоже идет работа в одном из форков.


КЛ>если запросы типовые, то зачем его вообще писать? нужно его генерить по схеме с помощью jooq
Я видимо плохо объяснил сценарий. Запросы аналитические, т.е. типовыми их не назовешь. Это не стандартный crud + проекции.

MVK>У меня сейчас другая задача.
MVK>Нужно писать большое количество аналитических запросов и у меня нет необходимости вызывать этот sql код из приложения.
MVK>Максимум — это питонячий скрипт, который потом докручивает аналитику или ML.
MVK>С точки зрения кол-во строк кода 80-90% — это SQL скрипты, которые процессят относительно большие объемы, несколько миллиардов записей.
MVK>Поэтому используются различные оптимизационные техники, так как один и тот же запрос можно написать очень по разному.
MVK>Но так как SQL очень а) избыточен, б) плохо композируется, в) плохо поддерживает переиспользование, то приходится постоянно писать плюс/минус одинаковые куски кода, что раздражает. Code pilot помогает, но далеко не всегда ну и это не совсем то, что хотелось бы.


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

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


Gt_>если миллиарды записей и развесистая логика где хочется нормального ООП, с наследованиями и патерн матчингом то надо брать spark.
Нет, ооп совсем не нужен, более того он тут только мешать будет. Спарк уже есть, конечно, но для других задач, для этой он плохо подходит.


Gt_> там полчучиться в цикле добавление к датафрейму колонок с метриками, новая метрика наследуется там от чего-то.
В реальности на боевых задачах у тебя будет определена куча udf, которые спарк не сможет оттранслировать в clickhouse native functions и будет тащить в память все 100 миллиардов записей. spark clickhouse connector может в push down, но только для основных операций. Ну а умные оптимизации типа там с битмэпами можно вообще забыть.

Gt_>как бонус сумашедшая параллельность, которую клик думаю любит. ну и упомянутые struct поля думаю спарк супортит.
А каким тут это боком? Спарк просто передаст запрос в кликхауз, кликзауз изобразит параллельность. Максимум, что он сможет это правильно раскидать запросы на кластер, но когда я последний раз туда смотрел, там все было кривовато с этим.

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


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

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

[]

КЛ>>во-первых, почему "получится так себе"? Во-вторых, если такой диалект поддерживается jooq, то это проблема не jooq. В общем, я не понял.
MVK>Я посмотрел, что умеет jooq. Не очень понятно в чем будет выигрыш, при условии что мне нет необходимости вызывать sql из java.
MVK>Бесспорно, полезная штука, если пилишь приложение, которому нужно ходить в базу, но для дата аналитических задач — только усложнит и удлинит процесс разработки.

я не настаиваю, но джук умеет делать реюз и довольно безопасную генерацию

КЛ>>так проверь
MVK>Clickhouse поддержки нет на официальном сайте, но похоже идет работа в одном из форков.

то, чего нет, допишешь raw sql


КЛ>>если запросы типовые, то зачем его вообще писать? нужно его генерить по схеме с помощью jooq
MVK>Я видимо плохо объяснил сценарий. Запросы аналитические, т.е. типовыми их не назовешь. Это не стандартный crud + проекции.

типовые не в смысле crud etc., а в смысле похожи друг на друга

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

Мда, апломба много, а знаний ноль.
Ну поехали...

Gt_>именно тут спарк идеален, ты просто совсем нулевый как я погляжу.
Боюсь, что нулевый у нас тут ты, судя по тому что ты пишешь.
Одна вот эта фраза выдает полную неграмотность в вопросе: "как бонус сумасшедшая параллельность, которую клик думаю любит". Ты понимаешь, что у тебя полная бредятина написана?
Или нужно объяснить?

Gt_>а зрение давно проверял ? ты явно не на спарк смотрел.
спишем хамство на твою невоспитанность


Gt_>спарк ничего не транслиует,
Да ладно?!
Открываем документацию и внимательно читаем про то как работают push downs и pruning
Также внимательно изучаем спецификацию DataSource V2
Также открываем для себя df.explain() и с изумлением смотрим на pushedFilters, pushedAggregates и даже pushedJoins

Gt_>спарк вычитывает в свой датафрейм
Еще одна глупость. Спарк как раз и был построен, чтобы можно было загрузку данных абстрагировать от логики расчетов.

1) Задумчиво читаем про RDD. Dataframe — это абстракция более высокого уровня построенная поверх RDD. С более-менее боевыми задачами на одних датафреймах не уедешь.
2) Ознакомившись с RDD внимательно читаем про то, что такое dataframe и в качестве продвинутого топика про catalyst.

Если на пальцах то пайплайн такой:
1. строиться логический план
2. план оптимизируется
3. строится физический план (тут как раз появляются push downs)
4. генерация кода
5. запуск
6. сбор результатов

Датафрейм — это не контейнер для данных.
При создании датафрейма и описания трансформаций никакой загрузки данных не происходит.


Gt_>и вся магия происходит в его jvm.
Чего?! какой еще "его jvm"? у спарка нет никакого "своего jvm".
Можно говорить, что для спарка будет лучше — грааль или хотспот — и в каких случаях.
Ну и с магией — это в Хогвартс. В спарке инженерно все понятно.


Gt_>клик для спарка просто тупой сторидж, мало чем отличающийся от S3.
Ну это же полная ахинея. Я даже не понимаю, с какого конца начать, чтобы объяснить.
Это как сказать, что для спарка Oracle — это как HDD

Ну еще можно было бы сказать для спарк не делает разницы между, скажем, parquet файлом на S3 и Clickhouse. Это, конечно, тоже бред, но намного меньший.
В этом случае можно просто открыть доку по различным коннекторам и обнаружить, что они, внезапно, предоставляют разные возможности и спарк с ними будет работать по разному. Опять таки, отсылаю к чтению спеки по DataSource V2

Gt_>scala умирает уже достаточно давно, может даже уже и не живая.
Держите меня семеро. А ничего, что сам спарк написан на скале и скала рекомендованный язык для спарка?
Для простых задач можно выжить с pyspark-ом, но на более-менее серьезном процессинге альтернативы Scala уже не будет. Можно, конечно, на java, но это прямо скажем так себе язык для работы с data. Ну и почитай еще что Захария пишет.


Gt_>вобщем почитай концепции, попробуй вникнуть, где происходит магия и 100 раз подумай, прежде чем запихивать логику в тормозные udf
Ты сейчас комментируешь какие-то свои иллюзии, а не то, что я писал. У меня логика не живет в udf, но лишь по той причине, что спарком я пользуюсь на скале.
А вот в питоне есть pandas udf, которые построены поверх apache arrow и если udf можно векторизировать, то будет работать очень шустро.
Поэтому и тут ты тоже плаваешь.


Суммируя:
Так громко и с размаху сесть в лужу — это надо постараться.
Поэтому иди и тренируйся на заднем дворе. Буду вопросы — приходи.
Не благодари

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


MVK>По работе приходится очень много писать SQL кода.

к счастью код пишется один раз. не стоит из-за этого заморачиваться. особенно если это ваша работа.

MVK>Пока написал кастомные команды к ideavim, но хотелось бы чего-то более умного. В идеале доступа к ast и схеме базы, и манипуляций с ним.
DSL нужен самописный вам.

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

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

Р>DSL нужен самописный вам.
Да, возможно выльется все в свой dsl. Но тут как обычно, dsl несложно написать, но сложно придумать.
Поэтому я и пошел пока по пути написания разносортных и несистемных макросов с надеждой, что накопив критическую массу таковых у меня начнет вырисовываться какая-то система, которая возможно и выльется в dsl, а возможно во что-то другое.

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

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

MVK>>По работе приходится очень много писать SQL кода.

IT>Если речь про .NET, то сегодня писать SQL руками — это нонсенс. Тем более под разные диалекты SQL. linq2db и подобное всё это умеет и поддерживает в том числе всевозможные техники повторного использования кода.

У кого нонсенс, а у нас это обыденное явление. Я бы давно на ddd перешел, но от меня это не зависит.

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


MVK>По работе приходится очень много писать SQL кода.

MVK>Код довольно однообразен , например раздражает когда нужно для 10 метрик написать что-то типа (в синтаксисе кликзауз):
MVK>select
MVK> dim1,
MVK> dim2,
MVK> ...
MVK> sumIf(metric0, isFinite(metric0)) as metric0,
MVK> ...
MVK> sumIf(metric9, isFinite(metriс9)) as metric9
MVK>group by
MVK> dim1,
MVK> dim3,
MVK> ...

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

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

IT>>Если речь про .NET, то сегодня писать SQL руками — это нонсенс. Тем более под разные диалекты SQL. linq2db и подобное всё это умеет и поддерживает в том числе всевозможные техники повторного использования кода.
MVK>linq2db я активно использовал, когда писал под .Net. Очень удобно.

MVK>У меня сейчас другая задача.
MVK>Нужно писать большое количество аналитических запросов и у меня нет необходимости вызывать этот sql код из приложения.

Тогда посмотри на LinqPad, если еще такой не видел — микро-ide, в которой удобно писать запросы к БД и получать результаты, как в SQL-студиях, только запросы пишешь на linq (в качестве драйвера можно подключить тот же linq2db).
И, собственно, не только запросы, но, при необхоидимости, и код на C# вокруг них. Для более простой генерации запросов (сделать набор функций и комбинировать в единый запрос) или для того, чтобы выполнить запрос, обработать кодом, выполнить дополнительные запросы, объединить и вывести финальный отчет.
И можно просмотреть реально отправленные SQL, если их надо куда-то потом сохранить для выполнения в чистом виде.

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

отлично, давай я постебаюсь над вторым пунктом. 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., а в смысле похожи друг на друга
ок, неправильно тебя понял

Здравствуйте, 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.

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