Unexpected behavior from Redshift Lambda UDF

Question

I have a Redshift UDF that invokes a Lambda which decrypts data via KMS. When I check my Lambda logs and invoke the Lambda manually from the console, it decrypts successfully but for some reason the UDF returns Null. This worked in a development environment, in production the biggest difference is that we are using Redshift serverless.

Any ideas of what else I can investigate? The Lambda logs indicate that it is returning the correct values, so I presume it's related to the UDF.

For more context, the UDF accepts a param for encrypted values and a param for the public key - it's used in an insert statement. This issue appears on all data types

```
insert into test_table (
col
)
select udf_lambda(Col, key)
from staging
```

rePost-User-1510293 · Answer

Dear Customer,

Greetings from AWS!

Thank you for contacting AWS!

I understand that while using a Lambda Function as UDF in Redshift serverless, you are unable to retrive any return data from Lambda and only see a NULL return. The Lambda funtion however, when invoked standalone, it works and logs correct output. Please correct me if my understanding varies from your concern.

Kindly note, in order to assist you better, I have performed a replication of similar use case in my Test serverless environment.

• Step 1: Create a table in Redshift cluster and insert some sample values.

- CREATE TABLE orderdetails ( orderNumber int, productCode varchar, quantityOrdered int, priceEach int );

- insert into orderdetails(orderNumber,productCode,quantityOrdered,priceEach) values (100,'P18',30,136), (100,'P19',50,55), (100,'P20',22,75), (100,'P24',49,35), (101,'P18',25,108), (101,'P25',26,167), (101,'P24',45,32), (101,'P19',46,44), (102,'P18',39,95), (102,'P2',41,43), (103,'P10',26,214);

• Create a Python lambda function in Account A with the below sample code. -> Python : 3.12
	
        import json
	def lambda_handler(event, context):
	    ret = dict()
	    res = []
	    for argument in event['arguments']:
	        quantity = argument[0]
	        price = argument[1]
	        res.append(json.dumps(quantity * price))	       
	    ret['success'] = True
	    ret['results'] = res           
	    return json.dumps(ret)

•	Create the below lambda UDF in the Redshift cluster [3].

- CREATE OR REPLACE EXTERNAL FUNCTION sampleudf (quantity int, price int) RETURNS varchar 
STABLE LAMBDA 'samplelambdafucntion' 
IAM_ROLE 'arn:aws:iam::AccountA:role/RoleA';

•	created a target table and performed INSERT INTO SELECT
- create table from_udf (ordernumber int, quantityordered int, priceeach int, totalprice varchar(20));

- insert into from_udf select ordernumber, quantityordered, priceeach, sampleudf(quantityordered, priceeach) as totalprice from orderdetails ;

Finally I could see the data in my target table. Kindly note Lambda UDFs are Scaler in nature, meaning, these functions can return only one Output. The output itself could be varying in nature. For example in my use case, from Lambda I return a dictionary which contains a list of elements.

I hope this answers your query.

Thank you and have a great day ahead!

MilindOke · Answer

Since the manual run for Lambda function works fine then most likely it can be permissions issue, on the execution role, or the KMS key access probably. Try looking for any logs and audit that can help you debug in CloudWatch or CloudTrail.

Unexpected behavior from Redshift Lambda UDF

Relevant content