Error when calling Lambda UDF from Redshift

Question

When calling a python lambda UDF from my Redshift stored procedure i am getting the following error. Any idea what could be wrong ?

ERROR: Invalid External Function Response Detail:    
----------------------------------------------- 
error: Invalid External Function Response code: 
8001 context: Extra rows in external function response query: 0 
location: exfunc_data.cpp:330 process: padbmaster [pid=8842] 
-----------------------------------------------

My Python Lambda UDF looks as follows.

def lambda_handler(event, context):
    #...
    result = DoJob()
    #...
    
    ret = dict()
    ret['results'] = result
    ret_json = json.dumps(ret)
    return ret_json

The above lambda function is associated to an external function in Redshift by name send_email_lambda. The permissions and invocation works without any issues. I am calling the lambda function as follows.

select send_email_lambda('[email protected]',
                   '[email protected]',
                   'sample body',
                   'sample subject);

Edit : As requested , adding the event payload passed from redshift to lambda.

{
   "user":"awsuser",
   "cluster":"arn:aws:redshift:us-central-1:dummy:cluster:redshift-test-cluster",
   "database":"sample",
   "external_function":"lambda_send_email",
   "query_id":178044,
   "request_id":"20211b87-26c8-6d6a-a256-1a8568287feb",
   "arguments":[
      [
         "[email protected]",
         "[email protected],[email protected]",
         "<html><h1>Hello Therer</h1><p>A sample email from redshift. Take care and stay safe</p></html>",
         "Redshift email lambda UDF",
         "None",
         "None",
         "text/html"
      ]
   ],
   "num_records":1
}

The number of output values should match the number of input rows. From CREATE EXTERNAL FUNCTION - Amazon Redshift: "The number of results in the response payload should be the same as the number of rows received." It looks like the multiple parameters are passed as an array of arrays. I recommend that you print(event) to debug what is being passed to the function. — John Rotenstein
– John Rotenstein, Commented Jun 8, 2021 at 22:12
@JohnRotenstein Thanks. I have added the event payload to question. You are right about the array of arrays. When the arguments from UDF are passed to lambda, it wraps them inside an array, which inturn is an array. I am not sure why — Soundararajan
– Soundararajan, Commented Jun 9, 2021 at 2:40
It looks like a UDF can be passed multiple rows of data. So, it could receive a request to send multiple emails. The code needs to loop through each of the top-level array, then extract the values from the array inside that. It looks like it then needs to return an array that is the same length as the input array. For your code, create an array with one entry and then return the dictionary inside that. — John Rotenstein
– John Rotenstein, Commented Jun 9, 2021 at 4:52
@JohnRotenstein , Thanks for your help and it indeed is the error. When returning the data i modified it to return an array within a dictionary and it worked. — Soundararajan
– Soundararajan, Commented Jun 9, 2021 at 19:04

John Rotenstein · Accepted Answer · 2021-06-10 02:22:16Z

1

It looks like a UDF can be passed multiple rows of data. So, it could receive a request to send multiple emails. The code needs to loop through each of the top-level array, then extract the values from the array inside that.

It looks like it then needs to return an array that is the same length as the input array.

For your code, create an array with one entry and then return the dictionary inside that.

answered Jun 10, 2021 at 2:22

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when calling Lambda UDF from Redshift

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related