Динамические PIVOT в SQL с помощью Snowflake
Простые в использовании динамические опорные точки в Snowflake: как использовать и создавать внутри хранимую процедуру JavaScript.
Есть что-то в SQL и PIVOT: каждый хочет PIVOT своих результатов, но, кажется, нет простого способа для этого. К счастью, у пользователей Snowflake есть собственный способ выполнения сводных операций в SQL с помощью функции PIVOT ():
select *
from (
select * from table(result_scan(last_query_id(-1)))
)
pivot(max(pivot_value)
for pivot_column in ('Spider-Man', 'Wonder Woman', 'Iron Man'))
Тем не менее, у PIVOT () в Snowflake есть одно ограничение: пользователям необходимо явно вызывать значения столбцов, в которые нужно выполнить поворот. Затем пользователи переходят в Stack Overflow, чтобы спросить как заставить Snowflake предоставлять PIVOT () с динамическими столбцами, и, похоже, нет однозначного ответа.
Вот почему я хочу предложить здесь отличный способ получения динамических сводных диаграмм в Snowflake с помощью простой для вызова хранимой процедуры JavaScript. Сначала позвольте мне представить, как использовать решение, а затем мы поговорим о крутых трюках в Snowflake, которые я использовал для этого:
Как получить динамические пивоты в Snowflake
- Напишите запрос, который объединяет данные, которые вы хотите сводить. Обязательно определите столбец с pivot_values и столбец с pivot_columns:
select name , date_trunc(quarter, month) pivot_column , sum(month_views) pivot_value from hero_views group by 1,2
2. Вызовите хранимую процедуру pivot_prev_results():
call pivot_prev_results();
3. Теперь найдите свои сводные результаты, просмотрев вывод этой хранимой процедуры:
select * from table(result_scan(last_query_id(-2)));
Вот и все!
Теперь, если вы хотите вместо этого вращаться по их именам, просто повторите процесс, переключая имена столбцов в первом запросе:
- Запрос
select name pivot_column , date_trunc(quarter, month) month , sum(month_views) pivot_value from hero_views group by 1,2
2. Вызов хранимой процедуры
call pivot_prev_results();
3. Найдите результаты:
select * from table(result_scan(last_query_id(-2)));
Создание хранимой процедуры
Если вы хотите вызвать pivot_prev_results(), сначала вам нужно его создать. Это исходный код:
Крутые уловки, заставляющие pivot_prev_results() работать:
Давайте рассмотрим хранимую процедуру, чтобы выявить несколько интересных идей в Snowflake:
create or replace procedure pivot_prev_results()
да. Вы можете определить хранимые процедуры в Snowflake.
returns string
Чтобы эта хранимая процедура работала, нам не нужно ничего возвращать, но мы можем использовать это позже.
language javascript
Это круто: хранимые процедуры в Snowflake могут быть определены в JavaScript, что открывает целый мир возможностей.
execute as caller
Хранимая процедура может выполняться с собственным набором разрешений и контекста или в контексте вызывающего ее лица. В этом случае выполнение в контексте вызывающего позволит нам посмотреть на их предыдущие результаты запроса и оставить результаты этой процедуры в их истории запросов.
as $$
Мы используем «$$» для ограничения кода JavaScript. $$ позволяет нам избежать более сложного побега изнутри.
var cols_query = ` select ‘\\’’ || listagg(distinct pivot_column, ‘\\’,\\’’) within group (order by pivot_column) || ‘\\’’ from table(result_scan(last_query_id(-1)))`;
Это запрос, который позволит нам определить столбцы в результирующей сводной таблице.
Обратите внимание на использование result_scan(last_query_id(-1) - с его помощью мы можем посмотреть на результаты предыдущего выполненного запроса, значения которого, как мы ожидаем, будут повернуты (в соответствии с инструкциями перед вызовом этой хранимой процедуры).
Ключевая концепция Snowflake. Запросы Snowflake выполняются в рамках сеанса, что позволяет вам обращаться к предыдущим запросам, результатам и даже устанавливать переменные сеанса.
Чтобы получить все различные значения в столбце pivot_column, мы используем listagg(distinct pivot_column), а чтобы убедиться, что они выглядят отсортированными в конечном результате, мы добавляем within group(order by pivot_column).
Затем мы выполняем запрос внутри процедуры. Что круто, Snowflake предоставляет среде JavaScript UDF API, поэтому код JS может просить Snowflake выполнять запросы:
var stmt1 = snowflake.createStatement({sqlText: cols_query});
var results1 = stmt1.execute();
results1.next();
Результатом этого запроса является список столбцов, которые мы собираемся использовать, чтобы запросить у Snowflake точку поворота:
var col_list = results1.getColumnValue(1);
Затем нам просто нужно создать простой запрос PIVOT () в Snowflake, используя значения столбцов, которые мы только что выяснили:
pivot_query = `
select *
from (select * from table(result_scan(last_query_id(-2))))
pivot(max(pivot_value) for pivot_column in (${col_list}))
`
var stmt2 = snowflake.createStatement({sqlText: pivot_query});
stmt2.execute();
Обратите внимание, что в этом запросе используется max (pivot_value). Идея состоит в том, что предыдущий запрос, запущенный пользователем, будет агрегировать результаты любым способом, которым они хотят, и на этом шаге мы должны выбрать не более одного значения для агрегирования.
Вот и все. После выполнения этого запроса сводные результаты сохраняются в истории запросов вызывающего абонента. Поскольку нам не нужно ничего возвращать, мы можем использовать это пространство, чтобы дать вызывающей стороне подсказку о двух способах доступа к результатам только что выполненного запроса: либо по его идентификатору, либо путем просмотра их истории:
return `select * from table(result_scan(‘${stmt2.getQueryId()}’));\n select * from table(result_scan(last_query_id(-2)));`;
$$;
Обратите внимание, что пользователи найдут результаты с last_query_id(-2), потому что -1 - это результаты вызова хранимой процедуры.
Удачных поворотов!
Хочу больше?
Я Фелипе Хоффа, защитник облачных данных для Snowflake. Спасибо, что присоединились ко мне в этом приключении. Вы можете следить за мной в Twitter и проверять reddit.com/r/snowflake, чтобы узнать самые интересные новости о Snowflake.
