如何使用Python Boto3在Redshift中获取列名 [英] How to get the column names in redshift using Python boto3

问题描述

我想使用python boto3在redshift中获取列名

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

1. 创建的Redshift集群
2. 将数据插入其中
3. 配置的秘密管理器
4. 配置SageMaker Notebook

打开Jupyter 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

如何获取ip地址，请访问 https://ipinfo.info/html/ip_checker.php

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

传递您的redshiftcluster主机名 xx.xx.us-east-1.redshift.amazonaws.com ，或者您可以在集群页面本身中看到

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:无法连接到服务器:连接超时服务器是否在主机"x.xx.xx..xx"上运行?并接受端口5439上的TCP/IP连接是什么?

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?

推荐答案

我修复了代码，并添加了以上规则

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")

这篇关于如何使用Python Boto3在Redshift中获取列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！