#stored-procedures #snowflake-cloud-data-platform #snowflake-task #snowsql
#хранимые процедуры #snowflake-cloud-data-platform #snowflake-задача #snowsql
Вопрос:
У меня есть код tsql, который я пытаюсь перенести в хранимый процесс на Snowflake. У меня есть версия 1 процедуры, встроенная и находящаяся в производстве. НО теперь нужно выполнить изменение поиска на основе логики IF-ELSE. Я могу легко сделать это, как предложено в T-sql, но как мне выполнить то же самое в Snowflake. Ниже приведен код на стороне TSQL, который я хочу преобразовать в Snowflake.
if object_id ('tempdb..#CTE_Y_Date') is not null drop table #CTE_Y_Date;
if object_id ('tempdb..#CTE_PY_Date') is not null drop table #CTE_PY_Date;
declare @week53_check int
,@Cal_Date int = 20210101
select @week53_check = Calendar_Week FROM calendar where cast([Calendar_Date] as varchar) = @Cal_Date
--print @week53_check
SELECT [Calendar_Date]
,[Fiscal_Year]
,[Fiscal_Period]
,[Calendar_Week]
,[Fiscal_Period_Week]
,[Fiscal_Period_Day]
,[Begnning_Date_PV]
,Fiscal_Quarter = case when Fiscal_Period between 1 and 3 then 1
when Fiscal_Period between 4 and 6 then 2
when Fiscal_Period between 7 and 9 then 3
when Fiscal_Period between 10 and 12 then 4 else 0 end
into #CTE_Y_Date
FROM calendar
where cast([Calendar_Date] as varchar) = @Cal_Date
-- select * from #CTE_Y_Date
if @week53_check = 53
(
SELECT a.[Calendar_Date]
,a.[Fiscal_Year]
,a.[Fiscal_Period]
,a.[Calendar_Week]
,a.[Fiscal_Period_Week]
,a.[Fiscal_Period_Day]
--,[Fiscal_Date]
-- ,[Begnning_Date]
,a.[Begnning_Date_PV]
,Fiscal_Quarter = case when a.Fiscal_Period between 1 and 3 then 1
when a.Fiscal_Period between 4 and 6 then 2
when a.Fiscal_Period between 7 and 9 then 3
when a.Fiscal_Period between 10 and 12 then 4 else 0 end
into #CTE_PY_Date
FROM calendar a
left join #CTE_Y_Date b on a.[Fiscal_Year] = b.[Fiscal_Year] and a.[Fiscal_Period_Day] = b.[Fiscal_Period_Day]
where a.[Fiscal_Year] = b.[Fiscal_Year] and a.Calendar_Week = 1
)
else insert into #CTE_PY_Date
SELECT a.[Calendar_Date]
,a.[Fiscal_Year]
,a.[Fiscal_Period]
,a.[Calendar_Week]
,a.[Fiscal_Period_Week]
,a.[Fiscal_Period_Day]
--,[Fiscal_Date]
-- ,[Begnning_Date]
,a.[Begnning_Date_PV]
,Fiscal_Quarter = case when a.Fiscal_Period between 1 and 3 then 1
when a.Fiscal_Period between 4 and 6 then 2
when a.Fiscal_Period between 7 and 9 then 3
when a.Fiscal_Period between 10 and 12 then 4 else 0 end
FROM calendar a
left join #CTE_Y_Date b on a.Fiscal_Period = b.Fiscal_Period and a.Fiscal_Period_Week = b.Fiscal_Period_Week and a.[Fiscal_Period_Day] = b.[Fiscal_Period_Day]
where a.[Fiscal_Year] = b.[Fiscal_Year] - 1
select * from #CTE_PY_Date
Здесь приведена версия кода Snowflake 1. Мне нужно выполнить блок IF из приведенного выше Tsql здесь.
CREATE or replace procedure "SP_53WeeksTest"()
returns FLOAT
language javascript
as
$
//drop temp tables
var dtt_CTE_Y_Date = 'drop table if exists CTE_Y_Date';
var dtt_CTE_Y_Date_stmt = snowflake.createStatement({sqlText: dtt_CTE_Y_Date});
var dtt_CTE_Y_Date_res = dtt_CTE_Y_Date_stmt.execute();
var dtt_CTE_PY_Date = 'drop table if exists CTE_PY_Date';
var dtt_CTE_PY_Date_stmt = snowflake.createStatement({sqlText: dtt_CTE_PY_Date});
var dtt_CTE_PY_Date_res = dtt_CTE_PY_Date_stmt.execute();
var cttCTE_Y_Date = `create temporary table CTE_Y_Date (CALENDAR_DATE NUMBER(38,0)
,FISCAL_YEAR NUMBER(38,0)
,FISCAL_PERIOD NUMBER(38,0)
,CALENDAR_WEEK NUMBER(38,0)
,FISCAL_PERIOD_WEEK NUMBER(38,0)
,FISCAL_PERIOD_DAY NUMBER(38,0)
,BEGNNING_DATE_PV DATE
,Fiscal_Quarter NUMBER(38,0)
)`;
var cttCTE_Y_Date_stmt = snowflake.createStatement({sqlText: cttCTE_Y_Date});
var cttCTE_Y_Date_res= cttCTE_Y_Date_stmt.execute();
var CTE_Y_Date_insert = `insert into CTE_Y_Date
select CALENDAR_DATE,FISCAL_YEAR,FISCAL_PERIOD,CALENDAR_WEEK,FISCAL_PERIOD_WEEK,FISCAL_PERIOD_DAY,BEGNNING_DATE_PV
,case when Fiscal_Period between 1 and 3 then 1
when Fiscal_Period between 4 and 6 then 2
when Fiscal_Period between 7 and 9 then 3
when Fiscal_Period between 10 and 12 then 4 else 0 end as Fiscal_Quarter
from FISCAL_CALENDAR
where BEGNNING_DATE_PV = (select max(BUSINESS_DATE) from RAW_TABLE_DB.POS_TABLES.PS_TRANS_HEADER)`;
var CTE_Y_Date_insert_stmt = snowflake.createStatement({sqlText: CTE_Y_Date_insert});
var CTE_Y_Date_res= CTE_Y_Date_insert_stmt.execute();
var cttCTE_PY_Date = `create temporary table CTE_PY_Date (CALENDAR_DATE NUMBER(38,0)
,FISCAL_YEAR NUMBER(38,0)
,FISCAL_PERIOD NUMBER(38,0)
,CALENDAR_WEEK NUMBER(38,0)
,FISCAL_PERIOD_WEEK NUMBER(38,0)
,FISCAL_PERIOD_DAY NUMBER(38,0)
,BEGNNING_DATE_PV DATE
,Fiscal_Quarter NUMBER(38,0)
)`;
var cttCTE_PY_Date_stmt = snowflake.createStatement({sqlText: cttCTE_PY_Date});
var cttCTE_PY_Date_res= cttCTE_PY_Date_stmt.execute();
var CTE_PY_Date_insert = `insert into CTE_PY_Date
select A.CALENDAR_DATE,A.FISCAL_YEAR,A.FISCAL_PERIOD,A.CALENDAR_WEEK,A.FISCAL_PERIOD_WEEK,A.FISCAL_PERIOD_DAY,A.BEGNNING_DATE_PV
,case when A.Fiscal_Period between 1 and 3 then 1
when A.Fiscal_Period between 4 and 6 then 2
when A.Fiscal_Period between 7 and 9 then 3
when A.Fiscal_Period between 10 and 12 then 4 else 0 end as Fiscal_Quarter
from FISCAL_CALENDAR AS A
LEFT JOIN CTE_Y_Date AS B ON A.FISCAL_PERIOD=B.FISCAL_PERIOD AND A.FISCAL_PERIOD_WEEK=B.FISCAL_PERIOD_WEEK AND A.FISCAL_PERIOD_DAY=B.FISCAL_PERIOD_DAY
WHERE A.FISCAL_YEAR = (B.FISCAL_YEAR - 1)`;
var CTE_PY_Date_insert_stmt = snowflake.createStatement({sqlText: CTE_PY_Date_insert});
var CTE_PY_Date_res= CTE_PY_Date_insert_stmt.execute();
return 0;
$
Ответ №1:
Хранимые процедуры T-SQL представляют собой поток сверху вниз. Хранимые процедуры Snowflake (на данный момент) — это JavaScript, который должен быть модульным.
Чтобы найти одно значение и сохранить его в переменной, вы захотите использовать вспомогательную функцию, подобную этой:
function getSingleValueQuery(columnName, queryString) {
var out;
cmd1 = {sqlText: queryString};
stmt = snowflake.createStatement(cmd1);
var rs;
rs = stmt.execute();
rs.next();
return rs.getColumnValue(columnName);
return out;
}
Это упрощает и делает более понятным выполнение повторяющихся задач, таких как поиск, выполнение запроса и возврат результатов, когда вы ожидаете только однорядный возврат.
Затем вы можете вызвать функцию и сохранить результат в переменной примерно так, в зависимости от вашей структуры данных:
var calDate = 20210101;
var week53_check = getSingleValueQuery('CALENDAR_WEEK',
`select CALENDAR_WEEK from CALENDAR where CALENDAR_DATE = ${calDate}`);
Тогда это просто вопрос настройки блока if-then при условии week53_check следующим образом:
if (week53_check == 53) {
// Do this
} else
// Do something else
}
Для лучшей читаемости я рекомендую модулировать ваш код с помощью вспомогательных функций:
if (week53_check == 53) {
doWeek53Insert();
} else
doNonWeek53Insert();
}
Затем у вас могут быть две функции, подобные этой:
function doWeek53Insert() {
var out;
cmd1 = {sqlText: `insert into CTE_Y_Date... (etc)... `};
stmt = snowflake.createStatement(cmd1);
var rs;
rs = stmt.execute();
}
Комментарии:
1. Заранее спасибо, Грег; Я рассмотрю. если у меня возникнут еще какие-либо вопросы, я свяжусь с вами. И оставит положительный отзыв.
Ответ №2:
Я думаю, что использование некоторых помощников помогает перенести код в snowflake javascript еще проще. это обычный фрагмент, который я использую:
// -------------- EXEC SNIPPET BEGIN --------------
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE, SQLSTATE, ERROR_HANDLERS, ACTIVITY_COUNT, INTO;
var fixBind = (arg) => arg instanceof Date amp;amp; arg.toISOString() || arg || null;
var fetch = (count,rows,stmt) =>
(count amp;amp; rows.next() amp;amp; Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i 1))) || [];
var INTO = () => (ROW_COUNT amp;amp; fetch(ROW_COUNT,_ROWS,_RS)) || [];
var EXEC = function (stmt,binds,noCatch) {
try {
binds = (binds amp;amp; binds.map(fixBind)) || binds;
_RS = snowflake.createStatement({sqlText : stmt,binds : binds});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
ACTIVITY_COUNT = _RS.getNumRowsAffected();
return { INTO: () => INTO() };
}
catch(error) {
MESSAGE_TEXT = error.message;
SQLCODE = error.code;
SQLSTATE = error.state;
if (!noCatch amp;amp; ERROR_HANDLERS) ERROR_HANDLERS(error);
var newError = Error(`SQLCODE: ${SQLCODE} MESSAGE: ${MESSAGE_TEXT}`)
throw newError;
}
};
// -------------- EXEC SNIPPET END --------------
Я использую это так часто, что создал простой инструмент: https://github.com/orellabac/SnowJS-Helpers / это позволяет мне просто расширить этот код для развертывания:
Например, взяв ваш код сверху:
CREATE or replace procedure SP_53WeeksTest()
returns FLOAT
language javascript
as
$
"@USING_EXEC";
EXEC(`DROP TABLE IF EXISTS tempdb."#CTE_Y_Date";`)
EXEC(`DROP TABLE IF EXISTS tempdb."#CTE_PY_Date";`)
var week53_check = 0;
var Cal_Date = 20210101;
EXEC(`select Calendar_Week FROM calendar where cast(Calendar_Date as varchar) = ?`,[Cal_Date])
[week53_check] = INTO();
//print @week53_check
EXEC(`
INSERT INTO "CTE_Y_Date"
SELECT Calendar_Date
,Fiscal_Year
,Fiscal_Period
,Calendar_Week
,Fiscal_Period_Week
,Fiscal_Period_Day
,Begnning_Date_PV
,Fiscal_Quarter = case when Fiscal_Period between 1 and 3 then 1
when Fiscal_Period between 4 and 6 then 2
when Fiscal_Period between 7 and 9 then 3
when Fiscal_Period between 10 and 12 then 4 else 0 end
FROM calendar
where cast(Calendar_Date as varchar) = ?]`,[Cal_Date]);
// select * from #CTE_Y_Date
if (week53_check == 53 )
{
EXEC(`
insert into "#CTE_PY_DATE"
SELECT a.Calendar_Date
,a.Fiscal_Year
,a.Fiscal_Period
,a.Calendar_Week
,a.Fiscal_Period_Week
,a.Fiscal_Period_Day
--,Fiscal_Date
-- ,Begnning_Date
,a.Begnning_Date_PV
,Fiscal_Quarter = case when a.Fiscal_Period between 1 and 3 then 1
when a.Fiscal_Period between 4 and 6 then 2
when a.Fiscal_Period between 7 and 9 then 3
when a.Fiscal_Period between 10 and 12 then 4 else 0 end
FROM calendar a
left join "#CTE_Y_Date" b on a.Fiscal_Year = b.Fiscal_Year and a.Fiscal_Period_Day = b.Fiscal_Period_Day
where a.Fiscal_Year = b.Fiscal_Year and a.Calendar_Week = 1
)`,[]);
}
else {
EXEC(`insert into "#CTE_PY_Date"
SELECT a.Calendar_Date
,a.Fiscal_Year
,a.Fiscal_Period
,a.Calendar_Week
,a.Fiscal_Period_Week
,a.Fiscal_Period_Day
--,Fiscal_Date
-- ,Begnning_Date
,a.Begnning_Date_PV
,Fiscal_Quarter = case when a.Fiscal_Period between 1 and 3 then 1
when a.Fiscal_Period between 4 and 6 then 2
when a.Fiscal_Period between 7 and 9 then 3
when a.Fiscal_Period between 10 and 12 then 4 else 0 end
FROM calendar a
left join "#CTE_Y_Date" b on a.Fiscal_Period = b.Fiscal_Period and a.Fiscal_Period_Week = b.Fiscal_Period_Week and a.Fiscal_Period_Day = b.Fiscal_Period_Day
where a.Fiscal_Year = b.Fiscal_Year - 1`);
// If you want to return this last statement
// This is want i do, I use a temporary table which I drop before use to make sure it was empty and the from the caller
// SP just select from RESULTS_SP_53WeeksTest
EXEC("DROP TABLE IF EXISTS RESULTS_SP_53WeeksTest",[]);
EXEC(`CREATE TEMPORARY TABLE RESULTS_SP_53WeeksTest AS select * from "#CTE_PY_Date"`,[]);
}
$;
Я просто использую свой инструмент для расширения фрагмента EXEC перед развертыванием, но если вы хотите, просто скопируйте и вставьте фрагмент EXEC вместо комментария «@USING_EXEC»
Комментарии:
1. Спасибо, Orellabac. Позвольте мне сделать обзор, и я также оставлю отзыв и оставлю положительный отзыв.
2. Привет, Orellabac, одно наблюдение. У вас отсутствует «}» в конце блока else в строке 98 для меня. Я помещаю ваш фрагмент EXEC как часть моей сохраненной процедуры. И я получаю следующую ошибку: —«Ошибка выполнения JavaScript: неперехваченная ошибка: SQLCODE: неопределенное СООБЩЕНИЕ: binds.map не является функцией в SP_53WeeksTest в ‘throw newError;’ позиция 2 stackstrace: строка EXEC: 23 Строка SP_53WeeksTest: 36»
3. О, извините, я только что написал все это, но я не проверял это. Я бы рекомендовал использовать VSCode. Мне нравится использовать SQLTools для непосредственного выполнения кода, и я нахожу marketplace.visualstudio.com /… это расширение полезно для раскраски синтаксиса
4. @junkete Я отредактировал сообщение с некоторыми исправлениями. Сработало ли это?
5. Приведенное ниже решило это для меня. Архитектор предложил использовать это. Я использую [LastYear] в качестве объединяющего столбца с CTE_PY_Date, а измерение календаря заботится об остальных. выберите top 10 business_date в качестве текущего года, dateadd(неделя, случай, когда mod(year(business_date), 4) = 1, затем -53 else -52 end, Business_Date) LastYear из календаря, где business_date = dateadd(день, -1,current_date());