topic Re: Create Scalar Function in Redshift Using Pass-Through in SAS Programming

Create Scalar Function in Redshift Using Pass-Through

tonyballard72 — Fri, 12 Mar 2021 17:13:49 GMT

Hello,

Please help! I am trying to create the function below as a UDF in Redshift using SAS Pass-Through. This is the same setup I have used in the past to create other functions as well as pass-through some SQL queries. I believe SAS is not liking the whitespace that is required for the Python function. My log with the error is below:

1 %studio_hide_wrapper;
82 %studio_show_wrapper;
84 %studio_restore_wrapper;
85
86 proc sql;
87 connect using schema1 as dbcon;
88
89 EXECUTE(
90 create or replace function schema1.f_py_greater (a float, b float)
91 returns float
92 stable
93 as $$
94 if a > b:
95 return a
96 return b
97 $$ language plpythonu) by dbcon;
ERROR: CLI execute error: [SAS][ODBC Redshift Wire Protocol driver][Redshift]ERROR: Failed to compile udf(Detail

-----------------------------------------------
error: Failed to compile udf
code: 10000
context: File
"/rds/bin/padb.1.0.00000/data/udf/45/7410258963/fb9915f88af178c5b64300a52e5010c3d26bb3b1/0.py", line 2
if a > b: return
a return b
^
SyntaxError: invalid syntax
query: 0
location: cg_addins.cpp:257

process: padbmaster [pid=78392]
------------------------
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
98
99 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.03 seconds

Any help is appreciated. Thank you!

Re: Create Scalar Function in Redshift Using Pass-Through

SASKiwi — Fri, 12 Mar 2021 22:06:16 GMT

Do you have access to a genuine Redshift SQL client to test this in? In my experience if it works OK in a genuine SQL-specific client it will work in SAS passthru. This holds true with Oracle and SQL Server databases I've used recently.

Re: Create Scalar Function in Redshift Using Pass-Through

tonyballard72 — Fri, 12 Mar 2021 22:40:10 GMT

Yes, I do. If I create the function in SQL client we have, using the same language within my EXECUTE() statement, it creates just fine. I am just confused as to why the code fails when I am using pass-through in SAS. Ideally, I would prefer not to have to use multiple clients in the future just to create some functions in Redshift.

Re: Create Scalar Function in Redshift Using Pass-Through

Tom — Fri, 12 Mar 2021 23:03:39 GMT

What is the $$ syntax in the Redshift code? Is it possible that Redshift is treating end of line characters as having meaning in parsing the syntax of your function definition? It doubt that SAS will preserve and end of line characters in strings it passes to the remote database.

Personally I am not sure there is much loss in functionality to not be able to create functions from SAS. In general you will probably just be wanting to just query the data from SAS so you can analyze it. Most of the database I query don't allow me to create objects in them anyway.

Re: Create Scalar Function in Redshift Using Pass-Through

SASKiwi — Sat, 13 Mar 2021 22:59:08 GMT

I'm guessing that the setup for the Redshift client is different to the SAS passthru version. Maybe some additional settings are required? Perhaps a Redshift DBA can help? Might be worth opening a track with SAS Tech Support too.