By using AWS re:Post, you agree to the Terms of Use
/Pass Redshift INOUT OUT parameter to AWS lambda invocation (boto3 client)/

Pass Redshift INOUT OUT parameter to AWS lambda invocation (boto3 client)

0

Is it possible to get a Redshift procedure inout (or out) parameter from a lambda invocation?

def lambda_handler(event, context):
    # This is my variable to get set by the Redshift proc
    out_param = "0"
    response = rs_client.execute_statement(
        ClusterIdentifier=cluster,
        Database=database,
        DbUser='my_user',
        Parameters=[
            {
                "name": "in_param1",
                "value": "this is a parameter"
            },
            {
                "name": "out_param1",
                "value": out_param
            }
        ],
        Sql="call my_schema.xxx_out_param(:in_param1, :out_param1)",
        StatementName='Test out parameter'
    )
    
    # Some code here checks that the statement has FINISHED status
    
    # The value printed out below is always zero and never the value that the proc sets the inout param to
    print(f"Value of OUT PARAM is: {out_param}")

The printed value of out_param is always 0 (zero) - its original value. The proc that I call simply assigns a hardcoded non-zero value. I have tested the proc in isolation to insert the value into a table and it works.

I cannot use a function for this. I am using existing code that derives an Id and need to obtain that Id that to trigger further processing. This is not ideal but I am working within existing system constraints.

1 Answers
0

As you my already know, Redshift Data API is asynchronous, and you get the unique identifier of the SQL statement after running using ‘execute_statement’.

I tested this with a stored procedure test_sp2 that you can find in the below documentation [+] https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html Stored procedure being test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256)) It has both INOUT and OUT parameters

Used below to execute the stored procedure response = client.execute_statement( ClusterIdentifier=‘<clustername>’, Database='<databasename’>, DbUser=‘user’1, Sql= 'call test_sp2(2,\'2019\')', StatementName='testsql' )

You can get ID with the below statement

IDval=response['Id']
print(IDval)
<ID value> — id value is returned

Once you have confirmed that the stored procedure is completed, you can use ‘get_statement_result’ to fetch the temporarily cached result of an SQL statement

[+] https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/redshift-data.html#RedshiftDataAPIService.Client.get_statement_result

response_result = client.get_statement_result(
... Id=‘<ID value from previous step>
... )

This response would contain results of the SQL statement. To retrieve the results, you can try below

print(response_result['Records'])
[{'stringValue': '2019+2019+2019+2019'}, {'stringValue': '2'}]

These would be the INOUT/OUT parameters of the stored procedure. To fetch them individually, we can use print(response_result['Records'][0][1]) and print(response_result['Records'][0][1])

answered a month 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