How to get the column names in redshift using Python boto3

Question

I want to get the column names in redshift using python boto3

1. Creaed Redshift Cluster
2. Insert Data into it
3. Configured Secrets Manager
4. Configure SageMaker Notebook

Open the Jupyter Notebook wrote the below code

import boto3
import time    
client = boto3.client('redshift-data')    
response = client.execute_statement(ClusterIdentifier = "test", Database= "dev", SecretArn= "{SECRET-ARN}",Sql= "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='dev' AND `TABLE_NAME`='dojoredshift'")

I got the response but there is no table schema inside it

Below is the code i used to connect I am getting timed out

import psycopg2
HOST = 'xx.xx.xx.xx'
PORT = 5439
USER = 'aswuser'
PASSWORD = 'Password1!'
DATABASE = 'dev'
def db_connection():
    conn = psycopg2.connect(host=HOST,port=PORT,user=USER,password=PASSWORD,database=DATABASE)
    return conn

How to get the ip address go to https://ipinfo.info/html/ip_checker.php

pass your hostname of redshiftcluster xx.xx.us-east-1.redshift.amazonaws.com or you can see in cluster page itself

I got the error while running above code

OperationalError: could not connect to server: Connection timed out Is the server running on host "x.xx.xx..xx" and accepting TCP/IP connections on port 5439?

Answer 1

I fixed with the code, and add the above the rules

import boto3
import psycopg2
 
# Credentials can be set using different methodologies. For this test,
# I ran from my local machine which I used cli command "aws configure"
# to set my Access key and secret access key
 
client = boto3.client(service_name='redshift',
                      region_name='us-east-1')
#
#Using boto3 to get the Database password instead of hardcoding it in the code
#
cluster_creds = client.get_cluster_credentials(
                         DbUser='awsuser',
                         DbName='dev',
                         ClusterIdentifier='redshift-cluster-1',
                         AutoCreate=False)
 
try:
    # Database connection below that uses the DbPassword that boto3 returned
    conn = psycopg2.connect(
                host = 'redshift-cluster-1.cvlywrhztirh.us-east-1.redshift.amazonaws.com',
                port = '5439',
                user = cluster_creds['DbUser'],
                password = cluster_creds['DbPassword'],
                database = 'dev'
                )
    # Verifies that the connection worked
    cursor = conn.cursor()
    cursor.execute("SELECT VERSION()")
    results = cursor.fetchone()
    ver = results[0]
    if (ver is None):
        print("Could not find version")
    else:
        print("The version is " + ver)
 
except:
    logger.exception('Failed to open database connection.')
    print("Failed")