Create table in AWS Redshift via redshift-data api

Question

I have a Redshift cluster created via terraform. When the cluster is up a lambda function gets invoked in which I am trying to create table in Redshift via redshift-data api. The code doesn't throw any error and there are no errors in cloudwatch logs however I cannot see the table in the database.

The role attached to lambda function has following permissions so I assume that it has the privileges to execute the query

"redshift-data:ExecuteStatement",
"redshift-data:CancelStatement",
"redshift-data:ListStatements",
"redshift-data:GetStatementResult",
"redshift-data:DescribeStatement",
"redshift-data:ListDatabases",
"redshift-data:ListSchemas",
"redshift-data:ListTables",
"redshift-data:DescribeTable"

Below is the code in the lambda function.

import json
import boto3

EVENT_TYPE = "REDSHIFT-EVENT-2000"


def hello(event=None, context=None):
    print(event)
    if event is not None:
        message = event['Records'][0]['Sns']['Message']
        print(message)
        if message is not None and EVENT_TYPE in message:
            # convert the str to python dictionary
            message_dict = json.loads(message)
            print(message)
            # get the cluster details
            cluster_name = message_dict.get('Resource', None)
            print(cluster_name)
            if cluster_name is not None:
                client_red = boto3.client('redshift',region_name = 'us-east-2')
                clusters = client_red.describe_clusters(ClusterIdentifier=cluster_name)
                cluster_detail = clusters.get('Clusters')[0]
                print(cluster_detail)
                db_name = cluster_detail.get('DBName')
                user_name = cluster_detail.get('MasterUsername')
                db_endpoint = cluster_detail.get('Endpoint')
                print(db_endpoint)
                db_address = db_endpoint.get('Address')
                db_port = db_endpoint.get('Port')

                print(db_name)
                print(db_address)
                print(db_port)
                print(user_name)

                client_data = boto3.client('redshift-data' , region_name = 'us-east-2')

                response = client_data.execute_statement(
                    ClusterIdentifier = cluster_name,
                    Database = db_name,
                    DbUser = user_name,
                    Sql = 'CREATE TABLE TEST (key LONG);'
                )

                print(response)

Could someone please help me as I am not able to figure out what exactly the issue is.Thank you

Bill Weiner Bill Weiner · Accepted Answer · 2021-04-13T04:32:16

I expect you need to COMMIT the actions. Add COMMIT; to your SQL.

If not you can look at stl_query to see if the query ran or if it isn't being launched.

Create table in AWS Redshift via redshift-data api

1 Answers