Soundararajan
Soundararajan

Reputation: 2204

Error when calling Lambda UDF from Redshift

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('sebder@company.com',
                   'recipient1@company.com',
                   '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":[
      [
         "sender@company.com",
         "user1@company.com,user2@company.com",
         "<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
}

Upvotes: 0

Views: 1301

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270184

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.

Upvotes: 1

Related Questions