Кто в курсе, SQLLite 3.31 умеет CTE из CTE, а-ля

WITH
off_set as
(select distinct master_id from Sheet1 as a
where not exists (select 1 from Sheet1 where sculpture_type = a.sculpture_type and master_id = 'Benjamin'))
select lower(master_id) from Sheet1 where master_id not in (select master_id from off_set) 
and master_id != 'Benjamin'
order by 1

?

#Yandex Cup 2025
По фану, заскочил в последний вагон и зарегался на глобальный ивент от Яндекса (финал пройдет в Стамбуле) в последние минуты несколько дней назад.
Сегодня был контест, о котором я с опозданием узнал случайно из рассылки (my bad), в итоге из регламентных 5 ч на решение 6 задач у меня было 3 с небольшим.
150 место из 537 неплохой результат, с учетом, что уйму времени убил на простую с виду задачку по SQL, которая дает макс. число баллов (8) из представленных,
что на фоне зубодробительных задач по теорверу даже многовато, хотите попробовать ее решить ? 30 лайков и она ваша
https://new.contest.yandex.com/contests/83967/leaderboard?pageSize=100&page=5

Кстати, пока плавал на мелководье sqlite3 ( решение задачки требовалось для этой БД) обнаружил, что в ней
1. Имеется Vacuum, правда таблу не умеет, как м приатаченные базы, только main db
2, Distinct в фильтре not in ( или в not exists ) приводит к деградации запроса, в отличие от GP ( был лимит на run-time итогового SQL скрипта)
3. Помимо LIKE есть более продвинутая GLOB(Unix file globbing syntax), которая может использовать спец символы:
*: соотве-ет любому количеству символов
?: - одному символу
.: - любому одиночному символу
[символы]: любому одиночному символу из списка внутри скобок ([abc])
[начальный_символ-конечный_символ]: любому одиночному символу из диапазона ([a-zA-Z0-9])
^: этот символ используется в начале списка символов и соответствует любому символу, который НЕ входит в список ([^0-9])
Из минусов, функция case-sensitive

На заметку
Оказывается, несмотря на то, что функция EXCEPT очищает результат от дублей,

select id from A
except
select id from B

работает заметно хуже

select id from A
except
select distinct id from B

особенно, когда B густозаселена дублями (1 трлн ключей vs 1.8 млрд уникальных в моем случае)
В частности, 2й запрос поможет избежать ошибки workfile per query size limit exceeded, вызванной 1-м.
Век живи - два учись!
Note
It turns out that although EXCEPT function deduplicates the result,

select id from A
except
select id from B

performs significantly worse

select id from A
except
select distinct id from B

especially when B is heavily populated with duplicates (1 trillion keys vs. 1.8 billion unique ones in my case).
In particular, the second query will help avoid the "workfile per query size limit exceeded" error caused by the first one.
Live once, learn twice!

#hive, hive, как это мило ...
Секрет 45(О важности разметки холодных данных)
В DataLake обновили кодовую базу, и меня попросли принять активное участие в тестировании
интеграции GP и Hadoop.

Здачка простая - проверить загрузку таблицы Hive в GP.

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

Создав строку в таблице foo в Hue интерфейсе к Hive, я стал ее клонировать, рассчитывая, что через 30 итераций одного и того же запроса получу 1 млрд строк:

insert into foo
select * from foo

Тут все прошло гладко, как по маслу.
⚠️
Пробую загрузить полученный сет в pxf_foo в GP через CTAS по PXF и отлуп.

Проверяю, что если ограничить по LIMIT:

select * from pxf_foo limit 10

ERROR: PXF server error : Fetch EMA 0 bytes/sec dropped below threshold (1,048,576), aborting.

Проверяю заливку старой табл-ы 24 г из 1 млн - все ОК, foo_24 загружается в GP без проблем.
⁉️
Гипотеза: видимо GP не может заглотить всю таблицу из 1 млрд, при чем, т.к. LIMIT отсекает сет не до, а после, то и 2й запрос рвет.

И тут я понимаю, что гипотезу на созданной синтетике не проверить, ибо все записи в созданной на скору руку синтетике - дубли,
а нужно взять часть, чтобы убедиться, что она вычленяется
на удаленной базе через PDO ( проброс предиката в удаленную БД, aka Pushdown Optimization)

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

Для этого я добавил в новый сет поле date2, которое пробегает диапазон в 1000 дн начиная с date1 ( существующее поле в foo_24 = '1900-01-01' )
и использовал аналог функции generate_series в Hive:

create table foo_25_1b as
select  t.*
       ,date_add (t.date1,pe.i) as date2

from    foo_24 t
        lateral view 
        posexplode(split(space(datediff(date_add (t.date1+100),t.date1)),' ')) pe as i,x

Таким образом, в hive я получил таблицу из 1 млрд строк, но теперь в нем есть поле селектор - date2 ( без которого строки опять же полные дубли ).

Теперь в GP запросы к pxf_foo_rng ( внешняя табла на foo_25_1b в Hive ) вида :

select * from pxf_foo_rng
where date2 = '1900-01-01'::date
limit 10

select * from pxf_foo_rng
where date2 = '1900-01-01' -- 1 млн строк

select * from pxf_foo_rng
where date2 between '1900-01-01'::date and  '1900-01-10'::date -- 10 млн строк

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


📌Мораль - если охлаждаете данные в Hive и прочие озера данных, откуда потребуется их поднять, обязательно предусмотрите возможность их выгрузки в GP
по диапазону, которым сами сможете управлять.
Маркером такого диапазона может быть дата, номер батча первичного охлаждения , просто кэшированный сиквенс, но что-то, что размечает ваши данные на потенциальные пачки для обратной выгрузки.

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

В мастерской скульпторов в Константинополе XII века создаются декоративные элементы для дворцов. Мастерская имеет богатую и давнюю историю: за многие поколения здесь накопились записи о многих произведениях. Мастера ведут подробный реестр работ, где фиксируются сведения о каждом изделии:

уникальный номер скульптуры id (float);

имя мастера, выполнившего работу master_id (string);

дата завершения creation_date (string);

дата продажи sell_date (string);

тип скульптуры sculpture_type (string).

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

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

Input format
Ваша задача - написать код на SQLite, который выводит имена строчными буквами упорядоченные по алфавиту. Обращаться к изначальной таблице можно через название Sheet1. Вы можете потестировать свой код на открытом датасете: скачать database_sample.xlsx, к примеру с помощью библиотеки sqlite3.

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

Notes
Скульптуры могут быть проданы по предзаказу сделанному сильно заранее до изготовления.

К задачке прилагается файлик

Секрет 26,ч2. (Я знаю, что ничего не знаю: наводим резкость на small data )
Случился прецедент за гранью моего понимания, и по делу проходит снова справочник без статы:
на проме запрос

select cap_.agreement_rk, vc4_.product_bp_nm
from mart$v as cap_
         join dict_product as vc4_
              on cap_.product_bp_cd = vc4_.product_bp_cd
where cast('2025-11-26' as date) >= cap_.effective_from_dttm
  and cast('2025-11-26' as date) < cap_.effective_to_dttm;

ежедневно отрабатывает в среднем за 1.5 мин, без проблем, как ни странно,
где mart$v - SCD2 вью для отбора последних версий, с идеей которой мы уже знакомы:

select *
from public.foo a
         join (
    select n, max(version_id) version_id from public.foo where version_id between :v_min and :v_max group by 1) c
              using (n, version_id);

dict_product - пересобираемый под запрос тривиальный справочник в 1500 строк ( коды которых уникальны ), по которому стата не собрана, согласно параметров GUC.

Начиная с конца недели запрос стал падать по спиллу > 30 TB, при том, что особо ничего не поменялось.
В моей реальности, запрос тогда уж должен бы падать всегда, согласно Секрет 26, и объяснить нормальное поведение ДО начала деградации я не в состоянии.

Мне захотелось что-то выжать из этой аномалии, и вот что я узнал.
Если материализовать вью

 
select 
from mart$v
where '2025-11-26'::date >= effective_from_dttm
  and '2025-11-26'::date) < effective_to_dttm;

в таблицу tst_mart из 1 млрд строк, то запрос

explain analyze
select cap_.agreement_rk, vc4_.product_bp_nm
from tst_mart as cap_
         join dict_product as vc4_
              on cap_.product_bp_cd = vc4_.product_bp_cd

отбегает за 2м 18с, но при этом на материализацию tst_mart ушло 6 мин.

Если заглянуть в план, то ожидаемо увидим Broadcast дикта, ибо он без статы, что не критично :

Gather Motion 384:1  (slice2; segments: 384)  (cost=0.00..1507.73 rows=1 width=264) (actual time=9.319..108483.528 rows=990063918 loops=1)
  ->  Hash Join  (cost=0.00..1507.73 rows=1 width=264) (actual time=9.950..1315.095 rows=2583044 loops=1)
        Hash Cond: (tst_mart.product_bp_cd = (tst1500.product_bp_cd)::text)
"        Extra Text: (seg40)  Hash chain length 1.0 avg, 2 max, using 1498 of 524288 buckets."
        ->  Seq Scan on tst_mart  (cost=0.00..498.18 rows=2775858 width=14) (actual time=0.290..450.133 rows=2781093 loops=1)
        ->  Hash  (cost=431.15..431.15 rows=1 width=264) (actual time=6.116..6.116 rows=1500 loops=1)
              ->  Broadcast Motion 384:384  (slice1; segments: 384)  (cost=0.00..431.15 rows=1 width=264) (actual time=0.023..5.667 rows=1500 loops=1)
                    ->  Seq Scan on tst1500  (cost=0.00..431.00 rows=1 width=264) (actual time=0.537..0.544 rows=12 loops=1)
Planning time: 13.347 ms
  (slice0)    Executor memory: 788K bytes.
"  (slice1)    Executor memory: 268K bytes avg x 384 workers, 268K bytes max (seg0)."
"  (slice2)    Executor memory: 4805K bytes avg x 384 workers, 4805K bytes max (seg0).  Work_mem: 145K bytes max."
Memory used:  2129920kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 138661.777 ms

А теперь к сути поста, которая меня крайне заинтересовала.

Если в исходном запросе в справочнике удалить все строки, или оставить, скажем одну из 1500, запрос отрабатывает на раз ( за 2 и 4 с соотв-но )
Это было неожиданно на фоне предыдущих секретов про пустые таблицы (Секрет 1,6).
Проверил снова запрос на полном справочнике - через 9 мин рухнул с workfile per query size limit exceeded.

Возникла шаткая гипотеза, что Broadcast сета из вью mart$v идет только на те узлы, где лежат строки справочника, что хоть как-то объяснило бы, почему запросы отработали.

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

Оказалось, планы совпадают, а моя гипотеза несостоятельна.
А дело вот в чем.
Анализ плана (ниже фрагмент его критичных слайсов) дал следующий инсайд :
На первом шаге идет также Broadcast справочника, который Hash Join-им с большой табл-ой под вью, и вот этот результат Oops.. снова Broadcast-им.
Соотв-но, последний Broadcast для случая 1 строки в справочнике пересылает в 1500 строк меньше, чем для полного.
Для 0 строк в справочнике последний Broadcast соотв-но просто номинальный шаг.

Остается добавить, если выполнить исходный запрос на справочнике с собранной статой, запрос выполнился без спилла за 8.5 мин с ожидаемым Redistribute, но у меня и РГ другая,
не такая крутая, как у регламентного потока на проме.
Остается загадкой, а зачем нужен 2й Broadcast, кроме первого, который дал нам возможность сделать Hash-Join

К секрету 26,ч.2

В Лас-Вегасе стартовал финал чемпионата мира по Microsoft Excel. 24 финалиста в течение трёх дней решают сложные кейсы на вылет в легендарной HyperX Arena, где обычно соревнуются киберспортсмены. На кону рекордные $60 000 (4,7 млн рублей), а победитель получит чемпионский пояс в стиле WWE.
The Microsoft Excel World Championship finals have kicked off in Las Vegas. Over three days, 24 finalists will solve challenging knockout cases at the legendary HyperX Arena, where esports athletes typically compete. A record-breaking $60,000 (4.7 million rubles) is at stake, and the winner will receive a WWE-style championship belt.

Правильный ответ на опрос выше - 1(Да). Если в запросе есть сортировка ORDER BY, возможен спилл.
Напомню, что в ITAS(CTAS - не суть) это может давать хороший выигрыш по месту, о чем писал неоднократно, напр.

The correct answer to the poll above is 1.If the query contains an ORDER BY clause, a spill is possible.
As a reminder, in ITAS(CTAS - no matter), this can yield significant space savings, as I've written about repeatedly, for example:

А вы знали, что если в ватс апп сменить язык на болгарский то звонки работают без впн( хак от коллеги, не проверял пока)

Вопрос 8
А кто знает, можно ли в GP сохранить результат запроса, в двумерный массив, не прибегая к PL/pgSQL
Does anyone know if it's possible to save the query result in a two-dimensional array in GP without using PL/pgSQL?

SQL> select regexp_matches('a := 1,b := 1', '(\w+)\s*:=\s*([^,]+)', 'g') as m
m
-----
{a,1}
{b,1}

Друзья, выручайте, может у кого то завалялся фильм "Босиком по мостовой" с Швайгером, раздайте, плз

Интересно, в вашем ИТ есть собственный фреймворк для генерации SQL кода в ХД.
Или каждый разработчик A пишет готовый к исполнению код с нуля, даже если он повторяет ту же трасформаацию, написанную другим разработчиком B, пусть и на других таблицах.
Опрос ниже по такому поводу.
I wonder if your IT department has its own framework for generating SQL code in the data warehouse.
Or does each developer A write ready-made code from scratch, even if they're repeating the same transformation written by another developer B, albeit on different tables.
The survey is below

Проблема 1 (gprestore issue)

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

Предположим, аналитик допустил ошибку, и передал в список на рестор лишнюю таблицу, или наоборот в БД нет какой-то подготовленной таблицы (или ее партиции) из списка.

Оказывается, в этом случае в 6-ке утилита gprestore рухнет при первой же ошибке.
Да, вы не ослышались, без использования ключа --on-error-continue - процесс восстановления прерывается при любой первой возникающей ошибке.
⁉️
Так в чем проблема - включили ключ и погнали ?

⚠️
А в том, что ключ этот имеет неожиданное поведение при работе с бекапсетами собранными с использованием --single-data-file и частичной вычиткой из оных.

Иными словами, если из бэкапа в сотни TB надо восстановить скажем 30% из 1000 таблиц, при каждом падении gprestore будет его перечитывать полностью и это уже проблема, которая может растянуться на часы, дни и т.д..
По хорошему, если бы для каждой таблы в бэкапе знать офсет ее DDL скрипта от начала ( а значит прямой указатель на сами данные, которые по идее
идут сразу после), то не пришлось бы перечитывать файл целиком. Собственно, так и работает gprestore без ключа, но до первого падения.

📌Решение: Согласно анонсу, в 6.29.1 данное недоразумение пофиксили, за что компании Аренадата +1 к карме!
Если кто проверял, дайте шуму, дайте знать!

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

Вероятно, у многих, кто использует свой in-house фреймворк, основной набор трансформаций T1, T2, ..., Tn реализован на PL/pgSQL
функциях, у каждой из которых есть параметры, задающие входную таблицу(-ы)
С большой вероятностью, из этих лего деталек T(i) вы строите достаточно сложные PL/pgSQL функции F для наполнения целевых витрин,
которые могут быть достаточно емкими по размеру, но построены по модульному принципу, т.е. представлены цепочкой трансформаций.

А если так, то вполне вероятно, что рано или поздно возникнет задача миграции определенной области ХД,
в которых используются F, на новый сервер.
И вот тут, если скажем исходные данные для F лежат в слое RDV, встает вопрос, какие именно таблицы требуются
для переносимого функционала.
Я имею ввиду, что сценарий, когда надо восстановить из бэкапа ряд таблиц RDV, представляющих набор исходных данных для
F, и выкатить поток с F на новом сервере,
чтобы бесшовно переключиться на работу ХД в новом контуре - вполне жизненная задача,
ибо миграция петабайтного ХД за один присест - задача практически невозможная как с технической точки зрения
так и экономически нецелесообразная, обычно такого слона едят по частям.

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

Повторюсь, что концептуально такая функция должна состоять из набора трансформаций T(i), представленных PL/pgSQL функциями с именованными параметрами.
Конкретно в моем случае я ловил пару параметров p_in_schema и p_in_table, задающих входы операторов фреймворка
исключая ad-hoc трансформацию, признаком которой является наличие параметра p_sql_text.

Также, поддержал случай, когда название таблицы в p_in_table параметризовано, т.е. завязано на название переменной,
значение которой объявлено либо в секции F declare, либо в ее основном блоке begin .. end.

Из интересного, я бы отметил, что мой любимый DeepSeek сделал 30% работы, остальное допиливал сам.
Особо пришлось повозиться с 2D массивами, т.к. надежды на то, что в 2D можно добавить 1D массив через array_append не оправдались.