Query Redshift Serverless

[pruthvirajksuresh]

Hello world,

i am trying to make a connection to serverless redshift from a lambda with the following code.
lambda role has admin access(FYI :- only for testing purposes)

import redshift_connector

conn = redshift_connector.connect(
    iam=True,
    database="good_database",
    host="1234567890.us-west-2.redshift-serverless.amazonaws.com",
    region='us-west-2'
)

def lambda_handler(event, context):
  
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)

but its returning me the following error

[ERROR] ProgrammingError: This feature is not yet available
Traceback (most recent call last):
File "/var/lang/lib/python3.9/importlib/init.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "", line 1030, in _gcd_import
File "", line 1007, in _find_and_load
File "", line 986, in _find_and_load_unlocked
File "", line 680, in _load_unlocked
File "", line 850, in exec_module
File "", line 228, in _call_with_frames_removed
File "/var/task/redshift.py", line 4, in
conn = redshift_connector.connect(
File "/var/task/redshift_connector/init.py", line 361, in connect
IamHelper.set_iam_properties(info)
File "/var/task/redshift_connector/iam_helper.py", line 38, in set_iam_properties
raise ProgrammingError("This feature is not yet available")

i tried to look into this file https://github.com/aws/amazon-redshift-python-driver/blob/9ef850f5a97d55a2a2d523b6428bb8966576c9ec/redshift_connector/iam_helper.py#L24
for more understanding to see if cluster id id is missing or profile name is missing , but got stuck on debugging , any suggestions would helpful .

thank you ,
raj

[Brooke-white]

Hey raj, we anticipate releasing a new version of redshift-connector with support for IAM authentication with serverless shortly :) I'll provide an update here once available.

[pruthvirajksuresh]

@Brooke-white thank you

[Brooke-white]

@pruthvirajksuresh we've just released v2.0.908 which includes Redshift serverless support. Please use this version, or later, in order to use Redshift serverless with IAM authentication. Thank you for your patience!

[pruthvirajksuresh]

@Brooke-white thank you for the update , but i am still facing the same issue . any suggestion would be helpfull

below is the python code

import redshift_connector

conn = redshift_connector.connect(
    iam=True,
    database="dev",
    host="12345678901.us-west-2.redshift-serverless.amazonaws.com",
    region='us-west-2'
)

def lambda_handler(event, context):
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)

the error

{
  "errorMessage": "Parameter validation failed:\nMissing required parameter in input: \"workgroupName\"",
  "errorType": "ParamValidationError",
  "requestId": "",
  "stackTrace": [
    "  File \"/var/lang/lib/python3.9/importlib/__init__.py\", line 127, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n",
    "  File \"<frozen importlib._bootstrap>\", line 1030, in _gcd_import\n",
    "  File \"<frozen importlib._bootstrap>\", line 1007, in _find_and_load\n",
    "  File \"<frozen importlib._bootstrap>\", line 986, in _find_and_load_unlocked\n",
    "  File \"<frozen importlib._bootstrap>\", line 680, in _load_unlocked\n",
    "  File \"<frozen importlib._bootstrap_external>\", line 850, in exec_module\n",
    "  File \"<frozen importlib._bootstrap>\", line 228, in _call_with_frames_removed\n",
    "  File \"/var/task/redshift.py\", line 3, in <module>\n    conn = redshift_connector.connect(\n",
    "  File \"/var/task/redshift_connector/__init__.py\", line 332, in connect\n    IamHelper.set_iam_properties(info)\n",
    "  File \"/var/task/redshift_connector/iam_helper.py\", line 117, in set_iam_properties\n    IamHelper.set_iam_credentials(info)\n",
    "  File \"/var/task/redshift_connector/iam_helper.py\", line 177, in set_iam_credentials\n    IamHelper.set_cluster_credentials(provider, info)\n",
    "  File \"/var/task/redshift_connector/iam_helper.py\", line 364, in set_cluster_credentials\n    raise e\n",
    "  File \"/var/task/redshift_connector/iam_helper.py\", line 319, in set_cluster_credentials\n    client.get_credentials(**get_cred_args),\n",
    "  File \"/var/task/botocore/client.py\", line 508, in _api_call\n    return self._make_api_call(operation_name, kwargs)\n",
    "  File \"/var/task/botocore/client.py\", line 878, in _make_api_call\n    request_dict = self._convert_to_request_dict(\n",
    "  File \"/var/task/botocore/client.py\", line 939, in _convert_to_request_dict\n    request_dict = self._serializer.serialize_to_request(\n",
    "  File \"/var/task/botocore/validate.py\", line 381, in serialize_to_request\n    raise ParamValidationError(report=report.generate_report())\n"
  ]
}

and when i add the workgroupName i get a different error

import redshift_connector

conn = redshift_connector.connect(
    iam=True,
    database="dev",
    host="1234567890.us-west-2.redshift-serverless.amazonaws.com",
    workgroupName="redshift",
    region='us-west-2'
)

def lambda_handler(event, context):
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)

error after adding wrokgroupname

{
  "errorMessage": "connect() got an unexpected keyword argument 'workgroupName'",
  "errorType": "TypeError",
  "requestId": "",
  "stackTrace": [
    "  File \"/var/lang/lib/python3.9/importlib/__init__.py\", line 127, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n",
    "  File \"<frozen importlib._bootstrap>\", line 1030, in _gcd_import\n",
    "  File \"<frozen importlib._bootstrap>\", line 1007, in _find_and_load\n",
    "  File \"<frozen importlib._bootstrap>\", line 986, in _find_and_load_unlocked\n",
    "  File \"<frozen importlib._bootstrap>\", line 680, in _load_unlocked\n",
    "  File \"<frozen importlib._bootstrap_external>\", line 850, in exec_module\n",
    "  File \"<frozen importlib._bootstrap>\", line 228, in _call_with_frames_removed\n",
    "  File \"/var/task/redshift.py\", line 3, in <module>\n    conn = redshift_connector.connect(\n"
  ]
}

the workgroup is wrong the error message and i corrected it "WorkgroupName"
then got the following error

{
  "errorMessage": "connect() got an unexpected keyword argument 'WorkgroupName'",
  "errorType": "TypeError",
  "requestId": "",
  "stackTrace": [
    "  File \"/var/lang/lib/python3.9/importlib/__init__.py\", line 127, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n",
    "  File \"<frozen importlib._bootstrap>\", line 1030, in _gcd_import\n",
    "  File \"<frozen importlib._bootstrap>\", line 1007, in _find_and_load\n",
    "  File \"<frozen importlib._bootstrap>\", line 986, in _find_and_load_unlocked\n",
    "  File \"<frozen importlib._bootstrap>\", line 680, in _load_unlocked\n",
    "  File \"<frozen importlib._bootstrap_external>\", line 850, in exec_module\n",
    "  File \"<frozen importlib._bootstrap>\", line 228, in _call_with_frames_removed\n",
    "  File \"/var/task/redshift.py\", line 3, in <module>\n    conn = redshift_connector.connect(\n"
  ]
}

[Brooke-white]

Hey @pruthvirajksuresh,

The first error you provided originates from boto3/botocore, not redshift-connector. This is why you see the second, workgroupName is unexpected error.

Typically your serverless host name will have the workgroup present within it. Take for example: 'mytest_workgroup.123456789012.us-west-1.redshift-serverless.amazonaws.com'. where mytest_workgroup is the name of the workgroup. You can find the name of the workgroup using the AWS console or AWS CLI.

[pruthvirajksuresh]

@Brooke-white thank you for the response . i did exactly what you mentioned and still got the same error.

Best,
Raj

[Brooke-white]

@pruthvirajksuresh -- could you provide a code snippit showing how you're establishing a connection?

[pruthvirajksuresh]

@Brooke-white hi, it tired the following 3 code , but none of them were successful

thank you .

trail one
import redshift_connector

def lambda_handler(event, context):
    conn = redshift_connector.connect(
        iam=True,
        database="dev",
        host="redshift.123456789012.us-west-2.redshift-serverless.amazonaws.com",
        region="us-west-2",
    )
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)


trail two 

import redshift_connector

def lambda_handler(event, context):
    conn = redshift_connector.connect(
        iam=True,
        database="dev",
        host="123456789012.us-west-2.redshift-serverless.amazonaws.com",
        region="us-west-2",
    )
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)

trail three 

import redshift_connector

def lambda_handler(event, context):
    conn = redshift_connector.connect(
        iam=True,
        database="dev",
        host="123456789012.us-west-2.redshift-serverless.amazonaws.com",
        WorkgroupName="redshift",
        region="us-west-2",
    )
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)


trail four

import redshift_connector

def lambda_handler(event, context):
    conn = redshift_connector.connect(
        iam=True,
        database="dev",
        WorkgroupName="redshift.123456789012.us-west-2.redshift-serverless.amazonaws.com",
        region="us-west-2",
    )
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM public.usertable;")
        result = cursor.fetchall()
    print(result)


trail 5 [this one works using boto3 library]

import redshift_connector
def lambda_handler(event, context):
    client = boto3.client("redshift-data")
    response = client.execute_statement(WorkgroupName="redshift", Database="dev", Sql="SELECT * FROM public.usertable")
    print(response)

[Brooke-white]

Hi @pruthvirajksuresh ,

there are usage issues with provided trails

1. When using iam=True you must provide AWS credentials via profile, direct, or IdP. Please review our connection tutorial. Passing iam=True without these gives no path for connection as credentials are not given to connect method. This is incorrect usage, regardless of Redshift serverless.
2. WorkgroupName is not a connection parameter for redshift_connector. serverless_work_group is a connection parameter, but it does not expect being passed the entire serverless endpoint as done in trail 4, it expects workgroup. You can find value of workgroup in Redshift Console

Can you modify trail 1 by passing AWS credentials, either directly or via profile connection arg? This trail is the closet to correct.

We have an example for redshift serverless connection in our connection tutorial I also suggest to review connection parameter docs for more details about these parameters and their usage.