Unexpected behavior from Redshift Lambda UDF


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 (
select udf_lambda(Col, key)
from staging
2 Answers

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!

answered 2 months ago
profile picture
reviewed 2 months ago

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.

profile pictureAWS
answered 2 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions