topic Re: How do you use a UDF Snowflake function in SAS EG in SAS Programming

How do you use a UDF Snowflake function in SAS EG

dstratm — Thu, 30 Jun 2022 13:39:41 GMT

I’m using the below code to connect to Snowflake via SAS Fusion without error. However, there’s a function on the database that is needed but SAS is producing an error. When connecting to Snowflake via the web, this function works. Are there any suggestions?

Function:

OCDP_SECURITY.DPAAS_DETOKEN_NAME(FIRST_NAME) AS FIRST_NAME

Error:

ERROR: CLI prepare error: SQL compilation error: Unknown user-defined function OCDP_SECURITY.DPAAS_DETOKEN_NAME

SQL statement: SELECT OCDP_SECURITY.DPAAS_DETOKEN_NAME(FIRST_NAME) AS FIRST_NAME FROM OCDP_PRD_CDCM_DB.CDCM.PATIENT.

%LET SERVER= xxxx.com;

%LET UID= xxxxx;

%LET PRIV_KEY_FILE_UNENCR= /hpsasocd/idpwd/&sysuserid./rsa_key.prv;

LIBNAME SNOW_PT ODBC COMPLETE="

DRIVER={/usr/lib64/snowflake/odbc/lib/libSnowflake.so};

AUTHENTICATOR= SNOWFLAKE_JWT;

SERVER= &SERVER;

UID= &UID;

PRIV_KEY_FILE= &PRIV_KEY_FILE_UNENCR;

PRIV_KEY_FILE_PWD=;

Warehouse= OCDP_PRD_QUERY_WH;

Role=AR_PRD_ROLE;

PROC SQL;

CONNECT USING SNOW_PT AS SF;

CREATE TABLE TEST_PASSTHRU AS SELECT *

FROM CONNECTION TO SF

(SELECT *

FROM OCDP_PRD_CDCM_DB.CDCM.TENANT

);

QUIT;

Re: How do you use a UDF Snowflake function in SAS EG

Tom — Thu, 30 Jun 2022 15:00:48 GMT

How are you calling that function?

Is it being called for you when you try to run that last query?

Is that query pulling from a view that is calling the function?

Does SNOWFLAKE have some type of "search path" for UDF? Are you sure you have configured your Session properly to find it? Does the snowflake account you are connecting with have permission to use it?

Re: How do you use a UDF Snowflake function in SAS EG

dstratm — Wed, 06 Jul 2022 16:00:14 GMT

The function is being called when running the below query. When submitted to Snowflake via web, there's no issues. If I run the same query as pass-through via SAS, I get error.

select ocdp_security.dpaas_detoken_name(FIRST_NAME)
from cdcm.patient (TABLE)

Under the Information_Schema view is where the function resides

Re: How do you use a UDF Snowflake function in SAS EG

SASKiwi — Wed, 06 Jul 2022 19:58:34 GMT

Please post the complete SAS log of your program, including the error.