CODEWITHSUNDEEP
pythonsqlamazon-web-servicesamazon-redshiftuser-defined-functions
Shivam
Shivam

Reputation: 23

how to use select query inside a python udf for redshift?

I tried uploading modules to redshift through S3 but it always says no module found. please help

CREATE or replace FUNCTION olus_layer(subs_no varchar)
RETURNS varchar volatile AS 
$$ 
import plpydbapi
dbconn = plpydbapi.connect()
cursor = dbconn.cursor()
cursor.execute("SELECT count(*) from busobj_group.olus_usage_detail")
d=cursor.fetchall()
dbconn.close()
return d 
$$ 
LANGUAGE plpythonu;

–

Upvotes: 1

Views: 3855

Answers (3)

Kamlesh Gallani
Kamlesh Gallani

Reputation: 771

Amazon has recently announced the support for Stored Procedures in Redshift. Unlike a user-defined function (UDF), a stored procedure can incorporate data definition language (DDL) and data manipulation language (DML) in addition to SELECT queries. Along with that, it also supports looping and conditional expressions, to control logical flow.

https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-overview.html

Upvotes: 1

AlexYes
AlexYes

Reputation: 4208

It can't connect to DB inside UDF, Python functions are scalar in Redshift, meaning it takes one or more values and returns only one output value.

However, if you want to execute a function against a set of rows try to use LISTAGG function to build an array of values or objects (if you need multiple properties) into a large string (beware of string size limitation), pass it to UDF as parameter and parse/loop inside the function.

Upvotes: 1

Jon Scott
Jon Scott

Reputation: 4354

You cannot do this in Redshift. so you will need to find another approach.

1) see here for udf constraints http://docs.aws.amazon.com/redshift/latest/dg/udf-constraints.html

2) see here http://docs.aws.amazon.com/redshift/latest/dg/udf-python-language-support.html especially this part:

Important Amazon Redshift blocks all network access and write access to the file system through UDFs.

This means that even if you try to get around the restriction, it won't work!

If you don't know an alternative way to get what you need, you should ask a new question specifying exactly what your challenge is and what you have tried, (leave this question ans answer here for future reference by others)

Upvotes: 2

Related Questions