Dbt: materialization support for storedprocedure or udf similar to view

[BalaMuralibi]

Hi

Not sure this is right place to ask this question, I am trying to use dbt for devops setup for snowflake https://medium.com/hashmapinc/doing-devops-for-snowflake-with-dbt-in-azure-db5c6249e721 based on this blog recommendation , and i mainly see materialization works for both table and views.
Does anyone know if materialization can support stored procedures and udf's also ? Please help.

if anyone had templates for stored procedures please provide the link.

Thanks in advance.

Regards,
Bala

[venkatra]

Hi Bala

I am the author of the blog you mentioned. The good news is that it is possible for udfs, procedures etc. I have custom implemented for a client. I have added these support in my git repo (which is also referenced in the blog)

dbt-hacks

Here is a sample code impl
`
{ config(materialized='procedure_definition') }}

create or replace procedure "TEST_DB"."SOME_SCHEMA".SAMPLE_STORE_PROC()
returns varchar not null
language javascript
as
$$
...

$$;

`

[BalaMuralibi]

Hi Venkat,

First of all kudos for your blog post :) I will download code and replay for stored procedures .
Is there any other channel that i can contact for your help if needed ? or i can ask over on this forum.

Thank you!

[venkatra]

You can ask here; i will try to answer if possible

[drewbanin]

Hey @venkatra - this is _awesome_! Thanks for sharing that link.

I'm super open to supporting Stored Procedures and UDFs in a first-class way. I think the materializations in dbt-hacks (great name) are a super good starting point :)

Thanks for making this issue @BalaMuralibi - really cool discussion!

[BalaMuralibi]

Hi Venkat,

Could you Please help with following error while deploying changes to snowflake, Deployment was working fine last time when i tested , it is failing now with error in attached log [same error today when i tried to just deploy code from [https://github.com/venkatra/dbt_hacks/tree/PERSISTENT_TABLE_MATERIALIZATION] . is it something with dbt ? or python version issue
tasklog_7.log

Between, did you try with multiple database schema to deploy model scripts , i tried to follow this https://docs.getdbt.com/docs/using-custom-databases , but couldn't get to done exactly the way i want , if i have to add custom schema it always creating _.sql.
Is there any work around for this ?

[venkatra]

I see the below in the log
2019-11-25T15:43:39.8860237Z ./deploy_persistent_models.sh: line 10: **./configs.sh: No such file or directory**

Looks like you are sourcing this file, I am wondering if this is a file from your environment ?

I am guessing because of the above error, the variable is not set hence you are getting the error below
2019-11-25T15:43:46.8188952Z 'NoneType' object has no attribute 'lower'
Hence I would go by looking at how you had executed first.

_Regarding : "multiple database schema"_
I am not clear what you mean. In my client we did not go the way of seperate schema for each developer. All the developers where using the same schema ex: 'CUSTOMER'. I followed the suggesion in
https://docs.getdbt.com/docs/using-custom-schemas

and it worked

[BalaMuralibi]

Thank you very much for your reply, the error is due to empty table schema in target snowflake database, it was created by error.
Regarding Multiple table schema, i have adjusted get_custom_schema in macros and it works now.

I will have to try more scenarios , such as deleting table/view under models is throwing error now when IdentifyGitBuildCommitItems.py script try to fetch .sql file that is deleted , may be i should change it to allow .py script to find only add/edit commit types. did you came across this case?

Regards,
Bala

[venkatra]

I did not have logic for detecting if a table script got dropped from the
build and taking appropriate action
.

I left it for the user to handle manually, as there could be potential
reasons on why the table scripts was dropped from git (Restructuring may
be).

On Tue, Nov 26, 2019 at 11:10 AM BalaMuralibi [e-mail]
wrote:

Thank you very much for your reply, the error is due to empty table schema
in target snowflake database, it was created by error.
Regarding Multiple table schema, i have adjusted get_custom_schema in
macros and it works now.

I will have to try more scenarios , such as deleting table/view under
models is throwing error now when IdentifyGitBuildCommitItems.py script try
to fetch file that is deleted , may be i should change it to allow .py
script to find only add/edit commit types. did you come across this
scenario ?

Regards,
Bala

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/fishtown-analytics/dbt/issues/1919?email_source=notifications&email_token=AAYAPHM2EE3TMENFOSF3E5DQVVC7JA5CNFSM4JM2LPI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEFGRUMI#issuecomment-558701105,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAYAPHLS26RBPYOQGQP37JDQVVC7JANCNFSM4JM2LPIQ
.

--

Venkatesh Sekar,*Cell: 647-292-7171 / 732-705-7037 *
Email : venkatesh.[email protected]

Linked In http://ca.linkedin.com/pub/venkatesh-sekar/1/7b7/636

[BalaMuralibi]

Hi venkat,

Sorry for bothering you much , I have created materialization for table [based on persistent_table from dbt_hacks repo] , In my case i don't want to take backup of table. I want to create table if not exits , if table exist i check for add /drop columns and data type changes. it is working fine when table doesn't exist , but fails when i modify to add /drop column. Could you please point me if possible where i made error https://github.com/BalaMuralibi/dbtblog/blob/master/plugins/snowflake/dbt/include/snowflake/macros/materializations/table_definition.sql. I see following error.

Thank you!

D:Playgroundsnowflake>dbt run -m TEST
Running with dbt=0.15.0
Error sending message, disabling tracking
Found 5 models, 0 tests, 0 snapshots, 0 analyses, 136 macros, 0 operations, 0 seed files, 0 sources

17:38:16 | Concurrency: 1 threads (target='dev')
17:38:16 |
17:38:16 | 1 of 1 START table_definition model PUBLIC.TEST...................... [RUN]
Unhandled error while executing model.snowflakeproj.TEST
'NoneType' object has no attribute 'status'
17:38:28 | 1 of 1 ERROR creating table_definition model PUBLIC.TEST............. [ERROR in 12.43s]
17:38:29 |
17:38:29 | Finished running 1 table_definition model in 18.07s.

Completed with 1 error and 0 warnings:

'NoneType' object has no attribute 'status'

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

[venkatra]

Hi

If you do not want to retain a copy of the table, just set the config flag 'retain_previous_version_flg' to false. like below

{{
config(materialized='persistent_table'
,retain_previous_version_flg=false
,migrate_data_over_flg=false
)
}}

[BalaMuralibi]

I have done it slightly different without using any flags in materialization, its working as expected.
Thanks.

[alihasan01]

Hi @venkatra / @BalaMuralibi ,

I am also doing devOps for snowflake and found 'dbt_hacks' package very promising. I have implemented CI/CD pipeline for creating, updating tables in snowflake.
Now I also have the same question how to achieve stored procedures deployment using this materialization: materialized='procedure_definition'
Where should I define my procedure in model folder or somewhere else?

I have tested as explained by @venkatra above as:

{ config(materialized='procedure_definition') }
create or replace procedure "DEMO_DB"."PUBLIC".SAMPLE_STORE_PROC()
returns varchar not null
language javascript
as
$$

$$;
but encountered with syntax error:

2020-01-28 10:25:38,160014 (MainThread): Database Error in model SAMPLE_STORE_PROC (modelsexampleSAMPLE_STORE_PROC.sql)
2020-01-28 10:25:38,162378 (MainThread): 001003 (42000): SQL compilation error:
2020-01-28 10:25:38,163399 (MainThread): syntax error line 2 at position 7 unexpected '{'.
2020-01-28 10:25:38,169398 (MainThread): syntax error line 2 at position 53 unexpected '}'.
2020-01-28 10:25:38,170396 (MainThread): compiled SQL at targetrunmy_new_projectexampleSAMPLE_STORE_PROC.sql

Any help would be highly appreciated :)

[venkatra]

Hi

I kept the procedures under a subfolder 'procedure' in the models directory

models -> public -> procedures -> some_stored_procedure.sql

This is how my file is like :
`
{{ config(materialized='procedure_definition') }}

create or replace procedure "DEMO_DB"."PUBLIC".SAMPLE_STORE_PROC()
returns varchar not null
language javascript
as
$$

$$;
`

Note that there are 2 curly braces instead of 1 curly brace as in your example

[BalaMuralibi]

Hi @venkatra / @BalaMuralibi ,

I am also doing devOps for snowflake and found 'dbt_hacks' package very promising. I have implemented CI/CD pipeline for creating, updating tables in snowflake.
Now I also have the same question how to achieve stored procedures deployment using this materialization: materialized='procedure_definition'
Where should I define my procedure in model folder or somewhere else?

I have tested as explained by @venkatra above as:

{ config(materialized='procedure_definition') }
create or replace procedure "DEMO_DB"."PUBLIC".SAMPLE_STORE_PROC()
returns varchar not null
language javascript
as
$$

$$;
but encountered with syntax error:

2020-01-28 10:25:38,160014 (MainThread): Database Error in model SAMPLE_STORE_PROC (modelsexampleSAMPLE_STORE_PROC.sql)
2020-01-28 10:25:38,162378 (MainThread): 001003 (42000): SQL compilation error:
2020-01-28 10:25:38,163399 (MainThread): syntax error line 2 at position 7 unexpected '{'.
2020-01-28 10:25:38,169398 (MainThread): syntax error line 2 at position 53 unexpected '}'.
2020-01-28 10:25:38,170396 (MainThread): compiled SQL at targetrunmy_new_projectexampleSAMPLE_STORE_PROC.sql

Any help would be highly appreciated :)

Hello, You can keep proc under models folder.
As @venkatra mentioned { missing in beginning of materialization config.

I had tested with following syntax and worked
{{
config(materialized='procedure_definition'
,schema ='STAGING'
)
}}

CREATE or replace PROCEDURE "{{ database }}"."{{ schema }}"."TESTPROC" ()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText:
INSERT INTO STAGING.TestTable ("column") SELECT 'value 1' AS "column" ;
});
return 'Done.';
$$

[alihasan01]

Thanks for your reply.

I have updated my procedure sql file as suggested by you but I am getting error:

Invalid return value from materialization, "relations" not found, got keys: ['procedure']

I am using this materialization: https://github.com/venkatra/dbt_hacks/blob/master/plugins/snowflake/dbt/include/snowflake/macros/materializations/procedure_definition_materialization.sql

Any idea what I am doing wrong? thanks

[venkatra]

I cant exactly pinpoint on this, as i did not face this issue.

Here are some observations

• This could be returned as "As to , such that the total CAD not matching
  with the invoice amount." ; but if you look at the database (ex: SHOW
  PROCEDURE in case of snowflake). you could find that the procedure gets
  created.
  The reason that the error is thrown is that materialization
  implementation is not truly compliant with DBT materialization format.

Remember this is a hack.

On Tue, Jan 28, 2020 at 10:53 AM Ali hasan [e-mail] wrote:

Thanks for your reply.

I have updated my procedure sql file as suggested by you but I am getting
error:

Invalid return value from materialization, "relations" not found, got
keys: ['procedure']

I am using this materialization:
https://github.com/venkatra/dbt_hacks/blob/master/plugins/snowflake/dbt/include/snowflake/macros/materializations/procedure_definition_materialization.sql

Any idea what I am doing wrong? thanks

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/fishtown-analytics/dbt/issues/1919?email_source=notifications&email_token=AAYAPHIQESVPEJWH35R5FK3RABPHRA5CNFSM4JM2LPI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKECKGY#issuecomment-579347739,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAYAPHNGQ7D4QWZALKWLBJ3RABPHRANCNFSM4JM2LPIQ
.

--

Venkatesh Sekar,*Cell: 647-292-7171 / 732-705-7037 *
Email : venkatesh.[email protected]

Linked In http://ca.linkedin.com/pub/venkatesh-sekar/1/7b7/636

[BalaMuralibi]

Thanks for your reply.

I have updated my procedure sql file as suggested by you but I am getting error:

Invalid return value from materialization, "relations" not found, got keys: ['procedure']

I am using this materialization: https://github.com/venkatra/dbt_hacks/blob/master/plugins/snowflake/dbt/include/snowflake/macros/materializations/procedure_definition_materialization.sql

Any idea what I am doing wrong? thanks

Just try with this code in procedure materialization , i have tested this month back when i validate to see feasibility to use dbt for snowflake dw ci/cd.
{%- materialization procedure_definition, adapter='snowflake' -%}

{%- set identifier = model['alias'] -%}
{%- set current_relation = adapter.get_relation(database=database, schema=schema, identifier=identifier) -%}
{%- set target_relation = api.Relation.create( identifier=identifier, schema=schema, database=database) -%}

{%- set has_transactional_hooks = (hooks | selectattr('transaction', 'equalto', True) | list | length) > 0 %}

{{ run_hooks(pre_hooks, inside_transaction=False) }}

-- BEGIN happens here:
{{ run_hooks(pre_hooks, inside_transaction=True) }}

-- build model

{% call statement('main') -%}
  {{ create_stmt_fromfile(sql) }}
{%- endcall %}

{{ run_hooks(post_hooks, inside_transaction=True) }}

  {{ adapter.commit() }}

{{ run_hooks(post_hooks, inside_transaction=False) }}
{{ return({'relations': [target_relation]}) }}

{%- endmaterialization -%}

[alihasan01]

Thanks @BalaMuralibi

I had figured out the above issue but unfortunately stuck in another one.
Extending this discussion to conclude it and some other may also get help from here.

I created procedure with dbt that successfully created but when I call it in snowflake by statement "call STPROC1();" it gave me syntax error:
"JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in STPROC1 at 'VAR RS = SNOWFLAKE.EXECUTE( { SQLTEXT:' position 4
"

I created same procedure in snowflake worksheet and call it successfully with no syntax error.

I am not sure why it is prompting syntax error. Do you have any idea? Thanks again.

I have attached dbt model in text file below
procedure.txt

[alihasan01]

Ok Syntax error raised due to uppercase of "SNOWFLAKE.EXECUTE" statement, that actually happened when you compile/run your dbt model it will create models in target folder with uppercase. "SNOWFLAKE.EXECUTE" should be compiled in lowercase format by dbt to execute your desired JavaScript with in procedure.

[venkatra]

Hmm is this in the materialization ?

odd i didnt face this issue at my current implementation

On Wed, Jan 29, 2020 at 10:42 AM Ali hasan [e-mail] wrote:

Ok Syntax error raised due to uppercase of "SNOWFLAKE.EXECUTE" statement,
that actually happened when you compile/run your dbt model it will create
models in target folder with uppercase. "SNOWFLAKE.EXECUTE" should be
compiled in lowercase format by dbt to execute your desired JavaScript with
in procedure.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/fishtown-analytics/dbt/issues/1919?email_source=notifications&email_token=AAYAPHISS6YDLICEP3SAVR3RAGWZDA5CNFSM4JM2LPI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKH4QMQ#issuecomment-579848242,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAYAPHNTRYSMV2Z323353D3RAGWZDANCNFSM4JM2LPIQ
.

--

Venkatesh Sekar,*Cell: 647-292-7171 / 732-705-7037 *
Email : venkatesh.[email protected]

Linked In http://ca.linkedin.com/pub/venkatesh-sekar/1/7b7/636

[alihasan01]

It was my bad I put {{ sql.upper() }} in snowfalke_helper_macros.sql that causing problem in JavaScript of procedure. We must have to use {{ sql }} instead of {{ sql.upper() }} for procedures as otherwise it uppercase all JavaScript code in procedure that will cause syntax error.

[drewbanin]

Hey y'all - I'm going to close this issue out with a #wontfix label, as it's not something we plan to support natively in dbt in the near future. Please do feel free to continue to coordinate this functionality in this thread, or in another forum like Discourse (discourse.getdbt.com) or Slack (slack.getdbt.com) if you so choose!

Thanks!

[anwer056]

HI Venkat, hope you are doing good.

While execute DBT_Run task in release pipeline i am getting below error.

Command used:
export SNOWSQL_ACCOUNT=$(ENV_SNOWSQL_ACCOUNT)
export SNOWSQL_USER=$(ENV_SNOWSQL_USER)
export DBT_PASSWORD=$(ENV_DBT_PASSWORD)
export SNOWSQL_ROLE=$(ENV_SNOWSQL_ROLE)
export SNOWSQL_DATABASE=$(ENV_SNOWSQL_DATABASE)
export SNOWSQL_WAREHOUSE=$(ENV_SNOWSQL_WAREHOUSE)
export DBT_PROFILES_DIR=./
chmod 750 ./deploy_persistent_models.sh
./deploy_persistent_models.sh

Error:
2020-10-13T06:12:26.6356893Z chmod: cannot access './deploy_persistent_models.sh': No such file or directory
2020-10-13T06:12:26.6599167Z /d/a/_temp/ca32c0f5-f437-4dd6-98a2-2d2339d9dd92.sh: line 9: ./deploy_persistent_models.sh: No such file or directory
tasklog_9 (1).log

I have forked your git repository and hence all the files are present in my repository but still getting this error. Could you please help me?