just migrate development process from database side to dbt side. the dbt seems very good to handle table, view create. but how could it handle execute sql process?
like in the sql server, i have several stored procedures, i need to schedule job to execute mutiple stored procedures on daily basis. Anyone can help on how dbt deal with this situation. how can dbt execute sql?
Thanks,
Alex
Thanks for the reply. can you shows me with a little bit more details? tried to add sql command in the macro. but when i create a model script to call this macro, got error.
Thanks
alex
To run that macro, you don’t put it in a model, you use run-operation like so;
dbt run-operation grant_select --args '{sql_query: john}'
That said, what are you wanting this macro to do? Generally creating tables and inserting data using an operation is a dbt antipattern.
Building on what @jaypeedevlin wrote…
I am not sure I can guess what is your goal…
if it is to run the sql and putting the results into a table… that is what DBT is designed for: just put the SQL code into a model (.sql file) and name it as the table you want to be created, then select the materialisation as view or table as you need and just dbt run -m <your_model_name>.
If what you want is something that you pass in a piece of SQL and you run it…
… well you could re purpose the passing of a variable and have a model that just expands that variable, but it looks ugly… and the name is fixed.
Probably much easier to do a small shell script to pipe your SQL trough a command line sql command interpreter like psql for postgres.
If you just want to grant privileges to a table created by DBT, use the hooks functionality of DBT.
I hope it helps.
Ciao, Roberto
Thanks so much for the reply, putting results into table just a example. what i am looking for is to be able to execute series of sql to replace our existing stored procedure. for example, in the stored procedure, need to create table first, then do logic insert, then update, and partial delete,. also need to run this sql on daily basis. i know model can handle table creation, but my table is not easy to be done by one select or with cte. need to do the update, insert, delete.
i am wondering, if this is possible? write bunch of sql and execute them one by one using dbt?
Thanks,
Alex
you could turn your stored procedure into a macro, which could accept params as how your stored procedure accepts params. and in the macro, you could have multiple SQL statements just like a SQL script.
From there, you could decide where to run this sp-macro, there are essentially four places, model pre-hook and post-hook, and at-the-start and at-the-end of the dbt runs.
Having said that, from what I read, your stored procedure might be doing a mini pipeline itself. You could consider refactoring your stored procedure, break them into several dbt models where it makes sense, and add pre-hook and post-hook to accomplish the same pipeline that you had in that stored procedure.
Thanks so much for reply. can you please give me a little bit more details? for example, if i want to create a table inside the sp and insert data into to it, how can i do that? i tried, failed. also, how can i call that macro? for example, below macro, keep give me error. thanks so much for the help.
{% macro testt() %}
{% set sql %}
create or replace table DBT_DIF_SEMANTIC.test(name nvarchar);
insert into DBT_DIF_SEMANTIC.test(name) values(‘zxc’);
{% endset %}
– {% do run_query(sql) %}
{% set results = run_query(sql) %}
{% endmacro %}
You could define your sp macro like below
{% macro testt() %}
create or replace table DBT_DIF_SEMANTIC.test(name nvarchar);
insert into DBT_DIF_SEMANTIC.test(name) values(‘zxc’);
{% endmacro %}
then put this macro in one of the four places, model pre-hook and post-hook, and at-the-start and at-the-end of the dbt runs.
if you want to run it as a post-hook of a model_A, you could set it in model_A.sql file
{{
config(
materialized = "view",
post_hook = testt(),
)
}}
Thanks so much for the reply. this is exactly what i am looking for, execute sql stuff.
so i get it , i can put sql list into macro. and execute it through macro. but question is, how can i run it without create model file. i tried your way, it worked. but as a result, i have to create a model_a view. If there any way that i can just call this macro without create model file?
Thanks,
Alex
Thanks for the reply. what i want to is to do daily insert into target table and also recreate some table. so i can put those insert, update, statement sql into macro. and find a way to call that macro. someone shows me a method to put it in the model file and it works. but as a result, i need to create a unuse table daily. so i am looking for a way to execute the macro only without create a table or view through model file. please take a look at below stored procedure, this is the sp that i am trying to translate using dbt. thanks so much for the help .
CREATE OR REPLACE PROCEDURE SEMANTIC.SP_AMMR_OCCUPANCY(PERIOD VARCHAR, PROPERTYPERIODID VARCHAR, CRTDUSER VARCHAR, LUPDUSER VARCHAR)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS ’
var sql, rs ;
rs = snowflake.execute( { sqlText:
"CREATE OR REPLACE TEMPORARY TABLE PropertyPeriod (PropertyPeriodKey int identity, PropertyPeriodId int, OperPropCD varchar)"});
if (PERIOD != 0) // go for all properties
{
sql = "INSERT INTO PropertyPeriod (PropertyPeriodId, OperPropCD) " +
"SELECT PropertyPeriodID, OperPropCD FROM GOVERNED.AMMR_PropertyPeriod WHERE Period = ''" + PERIOD + "'' AND Locked = 0 "
rs = snowflake.execute({ sqlText: sql }) ;
}
else
{
sql = "INSERT INTO PropertyPeriod (PropertyPeriodId, OperPropCD) " +
"SELECT PropertyPeriodID, OperPropCD FROM GOVERNED.AMMR_PropertyPeriod WHERE PropertyPeriodID = ''" + PROPERTYPERIODID + "'' AND Locked = 0 "
rs = snowflake.execute({ sqlText: sql }) ;
sql = "SELECT Period FROM GOVERNED.AMMR_PropertyPeriod WHERE PropertyPeriodId = " + PROPERTYPERIODID
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
PERIOD = rs.getColumnValue(1);
}
sql = "TRUNCATE TABLE GOVERNED.STG_AMMR_Occupancy"
rs = snowflake.execute({ sqlText: sql }) ;
sql = "INSERT INTO GOVERNED.STG_AMMR_Occupancy (DateKey, PropertyPeriodId, UnitNbr, Company, Unit_Type_Cd, Area_Type_Cd, Lease_Type_Cd, Occupancy_Status_Cd, Sqr_Footage) " +
"SELECT a.DateKey, PropertyPeriod.PropertyPeriodId, Unit.Unit_Nbr, b.Company, Unit.unit_type_cd, Unit.area_type_cd, C.lease_type_cd, " +
"f.Occupancy_Status_Cd, Unit.sqr_footage " +
"FROM (SELECT DISTINCT DateKey, Business_Unit, Unit_Key, Lease_Key, Occupancy_Status_Cd FROM SEMANTIC.FACT_RENTROLL) a " +
"INNER JOIN SEMANTIC.DIM_BUSINESS_UNIT b on b.BUSINESS_UNIT = a.BUSINESS_UNIT " +
"INNER JOIN (SELECT DISTINCT PropertyMap.JDE_Company Company, JDE_OperPropCD Parent_OperPropCd, PropertyPeriod.PropertyPeriodId " +
"FROM PropertyPeriod " +
"INNER JOIN GOVERNED.AMMR_PropertyMap PropertyMap on PropertyPeriod.OperPropCD = IFNULL(PropertyMap.Parent_OperPropCd, PropertyMap.JDE_OperPropCd)) P " +
"ON P.Company = b.Company " +
"INNER JOIN PropertyPeriod on PropertyPeriod.PropertyPeriodId = P.PropertyPeriodId " +
"INNER JOIN SEMANTIC.DIM_UNIT Unit on Unit.Unit_Key = a.Unit_Key " +
"INNER JOIN SEMANTIC.DIM_LEASE c on c.lease_key = a.lease_key " +
"INNER JOIN SEMANTIC.DIM_OCCUPANCY_STATUS f on f.Occupancy_Status_Cd = a.Occupancy_Status_Cd "
rs = snowflake.execute({ sqlText: sql }) ;
return "Success";
';
Looking at your sp, why don’t you create a dbt model for PropertyPeriod and another model for STG_AMMR_Occupancy, so that you could turn what you do in the store procedure into dbt model DAG?
Thanks for the reply. this one should be fine using model. but i got other sp which is more complicated. , for example, based on input parameter, will loop insert mutiple time. So want to get a way to execute the macro as general solution. you recommend me one way which is using model. it worked. thanks so much. do you have any other way that can help me avoid creating a table or view through model. because all i want to do is execute those sp and do the insert,
one of the sp that doing mutiple insert based on parameter is as below.:
CREATE OR REPLACE PROCEDURE SEMANTIC.SP_AMMR_INCOMESTATEMENT(PERIOD VARCHAR, PROPERTYPERIODID VARCHAR, CRTDUSER VARCHAR, LUPDUSER VARCHAR)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS ’
var sql, rs, ThresholdAmt, ThresholdAmt, LedgerType, cnt, Cntmax, PropertyPeriodIdLoop, cnt1, EndOfPeriod;
rs = snowflake.execute( { sqlText:
"CREATE OR REPLACE TEMPORARY TABLE PropertyPeriod (PropertyPeriodKey int identity, PropertyPeriodId int, OperPropCD varchar)"});
if (PERIOD != 0) // go for all properties
{
sql = "INSERT INTO PropertyPeriod (PropertyPeriodId, OperPropCD) " +
"SELECT PropertyPeriodID, OperPropCD FROM GOVERNED.AMMR_PropertyPeriod WHERE Period = ''" + PERIOD + "'' AND Locked = 0 "
rs = snowflake.execute({ sqlText: sql }) ;
}
else
{
sql = "INSERT INTO PropertyPeriod (PropertyPeriodId, OperPropCD) " +
"SELECT PropertyPeriodID, OperPropCD FROM GOVERNED.AMMR_PropertyPeriod WHERE PropertyPeriodID = ''" + PROPERTYPERIODID + "'' AND Locked = 0 "
rs = snowflake.execute({ sqlText: sql }) ;
sql = "SELECT Period FROM GOVERNED.AMMR_PropertyPeriod WHERE PropertyPeriodId = " + PROPERTYPERIODID
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
PERIOD = rs.getColumnValue(1);
}
sql = "SELECT CAST(TO_VARCHAR(LAST_DAY(TO_DATE(TRIM(RIGHT(" + PERIOD + ",2)) || ''/1/'' || TRIM(LEFT(" + PERIOD + ", 4)))), ''YYYYMMDD'') AS INT)"
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
EndOfPeriod = rs.getColumnValue(1);
sql = "SELECT LedgerType FROM GOVERNED.AMMR_ForecastingSchedule WHERE PeriodMonth = RIGHT(''" + PERIOD + "'',2) "
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
LedgerType = rs.getColumnValue(1);
cnt = 1;
sql = "SELECT Max(PropertyPeriodKey) FROM PropertyPeriod"
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
cntmax = rs.getColumnValue(1);
sql = "CREATE OR REPLACE TEMPORARY TABLE FactAccountBalances (Period_Date_Id, PropertyPeriodId, Business_Unit, Account_Id, Object_Account, AcctCatCd22, AcctCatCd22Desc, AssetTypeCD, AssetTypeDesc, BUCatCD12, BUCatCD12Desc, Current_Mnth_Actuals, Current_Mnth_Budget, Curr_Mnth_Forecast_1F, Curr_Mnth_Forecast_2F, Curr_Mnth_Forecast_3F, Curr_Mnth_Forecast_4F, YTD_Actuals, YTD_Budget, FCST) AS " +
"SELECT Period_Date_Id, P.PropertyPeriodId, " +
"FACT_ACCOUNT_BALANCES.Business_Unit, " +
"FACT_ACCOUNT_BALANCES.Account_Id, " +
"FACT_ACCOUNT_BALANCES.Object_Account, " +
"DimHHCFinAccount.Acct_Cat_Cd22, DimHHCFinAccount.Acct_Cat_Cd22_Desc, NULL AssetTypeCD, NULL AssetTypeDesc, BU_Cat_CD12, BU_Cat_CD12_Desc, " +
"SUM(CASE WHEN Ledger_Type = ''AA'' THEN MTD_Balance ELSE 0 END) Current_Mnth_Actuals, " +
"SUM(CASE WHEN Ledger_Type = ''BA'' THEN MTD_Balance ELSE 0 END) Current_Mnth_Budget, " +
"SUM(CASE WHEN Ledger_Type = ''1F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_1F, " +
"SUM(CASE WHEN Ledger_Type = ''2F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_2F, " +
"SUM(CASE WHEN Ledger_Type = ''3F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_3F, " +
"SUM(CASE WHEN Ledger_Type = ''4F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_4F, " +
"SUM(CASE WHEN Ledger_Type = ''AA'' THEN YTD_Balance ELSE 0 END) YTD_Actuals, " +
"SUM(CASE WHEN Ledger_Type = ''BA'' THEN YTD_Balance ELSE 0 END) YTD_Budget, " +
"(SUM(CASE WHEN Ledger_Type = ''AA'' THEN YTD_Balance ELSE 0 END) \\+ " +
"CASE WHEN ''" + LedgerType + "'' = ''1F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''1F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''1F'' THEN YTD_Balance ELSE 0 END,0))) " +
"WHEN ''" + LedgerType + "'' = ''2F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''2F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''2F'' THEN YTD_Balance ELSE 0 END,0))) " +
"WHEN ''" + LedgerType + "'' = ''3F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''3F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''3F'' THEN YTD_Balance ELSE 0 END,0))) " +
"WHEN ''" + LedgerType + "'' = ''4F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''4F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''4F'' THEN YTD_Balance ELSE 0 END,0))) END ) FCST " +
"FROM SEMANTIC.FACT_ACCOUNT_BALANCES FACT_ACCOUNT_BALANCES " +
"INNER JOIN SEMANTIC.DIM_ACCOUNT DimHHCFinAccount ON DimHHCFinAccount.Account_ID = FACT_ACCOUNT_BALANCES.Account_Id " +
"INNER JOIN SEMANTIC.DIM_BUSINESS_UNIT DimHHCFinBusinessUnit on DimHHCFinBusinessUnit.Business_Unit = FACT_ACCOUNT_BALANCES.Business_Unit " +
"INNER JOIN (SELECT DISTINCT PropertyMap.JDE_Company Company, JDE_OperPropCD Parent_OperPropCd, PropertyPeriod.PropertyPeriodId " +
"FROM PropertyPeriod " +
"INNER JOIN GOVERNED.AMMR_PropertyMap PropertyMap on PropertyPeriod.OperPropCD = IFNULL(PropertyMap.Parent_OperPropCd, PropertyMap.JDE_OperPropCd)) P " +
"ON P.Company = DimHHCFinBusinessUnit.Company " +
"WHERE DimHHCFinBusinessUnit.Bu_Cat_Cd27 = ''OPR'' " +
"GROUP BY Period_Date_Id, P.PropertyPeriodId, FACT_ACCOUNT_BALANCES.Business_Unit, FACT_ACCOUNT_BALANCES.Account_Id, FACT_ACCOUNT_BALANCES.Object_Account, DimHHCFinAccount.Acct_Cat_Cd22, DimHHCFinAccount.Acct_Cat_Cd22_Desc, BU_Cat_CD12, BU_Cat_CD12_Desc "
rs = snowflake.execute({ sqlText: sql }) ;
sql = "TRUNCATE TABLE GOVERNED.STG_AMMR_INCOMESTATEMENT"
rs = snowflake.execute({ sqlText: sql }) ;
while (cnt <= cntmax)
{
sql = "SELECT PropertyPeriodId FROM PropertyPeriod where PropertyPeriodKey = " + cnt
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
PropertyPeriodIdLoop = rs.getColumnValue(1);
//--------Now bring in the updated numbers
sql = "INSERT INTO GOVERNED.STG_AMMR_INCOMESTATEMENT (PropertyPeriodId, Level1CD, Level1Desc, Level2CD, Level2Desc, Level3CD, Level3Desc, AcctCatCd22, AcctCatCD22Desc, AssetTypeCD, AssetTypeDesc, BUCatCD12, BUCatCD12Desc, CMActual , CMBudget , YTDActual , YTDBudget , FCST, FCSTBudget ) " +
"WITH ETL (PropertyPeriodId, AcctCatCd22, AcctCatCD22Desc, AssetTypeCD, AssetTypeDesc, BUCatCD12, BUCatCD12Desc, CMActual , CMBudget , YTDActual , YTDBudget , FCST, FCSTBudget ) AS " +
"(SELECT F.PropertyPeriodId, F.AcctCatCd22, F.AcctCatCD22Desc, F.AssetTypeCD, F.AssetTypeDesc, F.BUCatCD12, F.BUCatCD12Desc, " +
"SUM(F.Current_Mnth_Actuals), " +
"SUM(F.Current_Mnth_Budget), " +
"SUM(F.YTD_Actuals), " +
"SUM(F.YTD_Budget), " +
"SUM(F.FCST), NULL " +
"FROM FactAccountBalances F " +
"WHERE Period_Date_Id = " + EndOfPeriod + " AND PropertyPeriodId = " + PropertyPeriodIdLoop + " " +
"GROUP BY F.PropertyPeriodId, F.AcctCatCd22, F.AcctCatCD22Desc, F.AssetTypeCD, F.AssetTypeDesc, F.BUCatCD12, F.BUCatCD12Desc " +
"), ETLAnnualBudget (PropertyPeriodId, AcctCatCd22, AssetTypeCD, BUCatCD12, FCSTBudget) AS " +
"(SELECT F.PropertyPeriodId, F.AcctCatCd22, F.AssetTypeCD, F.BUCatCD12, " +
"SUM(F.Current_Mnth_Budget) " +
"FROM FactAccountBalances F " +
"WHERE LEFT(Period_Date_Id,4) = LEFT(" + PERIOD + ",4) AND PropertyPeriodId = " + PropertyPeriodIdLoop + " " +
"GROUP BY F.PropertyPeriodId, F.AcctCatCd22, F.AssetTypeCD, F.BUCatCD12) " +
"SELECT " + PropertyPeriodIdLoop + ", LUP.Level1CD, LUP.Level1Desc, LUP.Level2CD, LUP.Level2Desc, LUP.Level3CD, LUP.Level3Desc, " +
"LUP.AcctCatCd22, LUP.AcctCatCD22Desc, ETL.AssetTypeCD, ETL.AssetTypeDesc, ETL.BUCatCD12, ETL.BUCatCD12Desc, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.CMActual ELSE ETL.CMActual END, 0)) CMActual, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.CMBudget ELSE ETL.CMBudget END, 0)) CMBudget, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.YTDActual ELSE ETL.YTDActual END, 0)) YTDActual, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.YTDBudget ELSE ETL.YTDBudget END, 0)) YTDBudget, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * FCST ELSE FCST END, 0)) FCST, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETLAnnualBudget.FCSTBudget ELSE ETLAnnualBudget.FCSTBudget END, 0)) FCSTBudget " +
"FROM ETL " +
"INNER JOIN ETLAnnualBudget ON ETLAnnualBudget.PropertyPeriodId = ETL.PropertyPeriodId AND ETLAnnualBudget.AcctCatCd22 = ETL.AcctCatCd22 " +
"AND IFNULL(ETLAnnualBudget.AssetTypeCD, '''') = IFNULL(ETL.AssetTypeCD, '''') AND IFNULL(ETLAnnualBudget.BUCatCD12, '''') = IFNULL(ETL.BUCatCD12, '''') " +
"RIGHT OUTER JOIN (SELECT DISTINCT LEVEL1CD,LEVEL1DESC,LEVEL2CD,LEVEL2DESC,LEVEL3CD,LEVEL3DESC,ACCTCATCD22,ACCTCATCD22DESC " +
"FROM SEMANTIC.OPIS_REPORT_STRUCTURE ) LUP " +
"ON ETL.AcctCatCd22=LUP.AcctCatCd22 " +
"GROUP BY LUP.Level1CD, LUP.Level1Desc, LUP.Level2CD, LUP.Level2Desc, LUP.Level3CD, LUP.Level3Desc, " +
"LUP.AcctCatCd22, LUP.AcctCatCD22Desc, ETL.AssetTypeCD, ETL.AssetTypeDesc, ETL.BUCatCD12, ETL.BUCatCD12Desc "
rs = snowflake.execute({ sqlText: sql }) ;
cnt = cnt + 1
}
return “Done”;
';
Hi Sir, i know you are expert in dbt. do you mind i ask one more questions? do you know how can i do while loop in macro? thanks so much for the help. really appreciate it.
Here is the scenario ,the macro will have two parameter which are begin month and end month.
inside macro, i want to do following step:
1 truncate existing table
2. do insert into this table on month level.
for example, if date range between begin month and end month is 8 months. then do 8 times insert, 1 for each month. i am wondering if we have any while loop in macro like
while(NO_OF_MON > 0 )
do insert into tables
NO_OF_MON+=1;
end
Thanks,
Alex
Hi Sir, i know you are expert in dbt. do you mind i ask one more questions? do you know how can i do while loop in macro? thanks so much for the help. really appreciate it.
Here is the scenario ,the macro will have two parameter which are begin month and end month.
inside macro, i want to do following step:
1 truncate existing table
2. do insert into this table on month level.
for example, if date range between begin month and end month is 8 months. then do 8 times insert, 1 for each month. i am wondering if we have any while loop in macro like
while(NO_OF_MON > 0 )
do insert into tables
NO_OF_MON+=1;
end
Thanks,
Alex
Hey Alex, did you manage to find a way to pass the name of a macro ( which holds the procedure source) via dbt run-operation command?
Whether DB procedure can be called from DBT? Kindly share some sample code to call procedures?