For general information on how to use identity provider plugins, see Options for providing IAM credentials (p. 429).
Authentication using the ADFS identity provider plugin
Following is an example of using the Active Directory Federation Service (ADFS) identity provider plugin to authenticate a user connecting to an Amazon Redshift database.
>>> con = redshift_connector.connect(
iam=True, database='dev',
host='my-testing-cluster.abc.us-east-2.redshift.amazonaws.com', cluster_identifier='my-testing-cluster',
credentials_provider='AdfsCredentialsProvider', user='[email protected]',
password='Hunter2',
idp_host='myadfshostname.com' )
Authentication using the Azure identity provider plugin
Following is an example of authentication using the Azure identity provider plugin. You can create values for a client_id and client_secret for an Azure Enterprise application as shown following.
>>> con = redshift_connector.connect(
iam=True, database='dev',
host='my-testing-cluster.abc.us-east-2.redshift.amazonaws.com', cluster_identifier='my-testing-cluster',
credentials_provider='AzureCredentialsProvider', user='[email protected]',
password='Hunter2',
idp_tenant='my_idp_tenant', client_id='my_client_id',
client_secret='my_client_secret',
preferred_role='arn:aws:iam:123:role/DataScientist' )
Configuring connections in Amazon Redshift
Authentication using Azure Browser identity provider plugin
Following is an example of using the Azure Browser identity provider plugin to authenticate a user connecting to an Amazon Redshift database.
The password parameter is required. However, you don't have to enter this parameter because multi-factor authentication occurs in the browser.
>>>con = redshift_connector.connect(
iam=True, database='dev',
host='my-testing-cluster.abc.us-east-2.redshift.amazonaws.com', cluster_identifier='my-testing-cluster',
credentials_provider='BrowserAzureCredentialsProvider', user='[email protected]',
password='',
idp_tenant='my_idp_tenant', client_id='my_client_id',
client_secret='my_client_secret', )
Authentication using the Okta identity provider plugin
Following is an example of authentication using the Okta identity provider plugin. You can obtain the values for idp_host, app_id and app_name through the Okta application.
>>> con = redshift_connector.connect(
iam=True, database='dev',
host='my-testing-cluster.abc.us-east-2.redshift.amazonaws.com', cluster_identifier='my-testing-cluster',
credentials_provider='OktaCredentialsProvider', user='[email protected]',
password='hunter2', idp_host='my_idp_host', app_id='my_first_appetizer', app_name='dinner_party' )
Authentication using JumpCloud with a generic SAML browser identity provider plugin Following is an example of using JumpCloud with a generic SAML browser identity provider plugin for authentication.
The password parameter is required. However, you don't have to enter this parameter because multi-factor authentication occurs in the browser.
>>> con = redshift_connector.connect(
iam=True, database='dev',
host='my-testing-cluster.abc.us-east-2.redshift.amazonaws.com', cluster_identifier='my-testing-cluster',
credentials_provider='BrowserSamlCredentialsProvider', user='[email protected]',
password='',
login_url='https://sso.jumpcloud.com/saml2/plustwo_melody' )
Examples of using the Amazon Redshift Python connector
Following are examples of how to use the Amazon Redshift Python connector.
Configuring connections in Amazon Redshift
Topics
• Connecting to an Amazon Redshift cluster using AWS credentials (p. 190)
• Querying a table (p. 190)
• Retrieving the query result set (p. 190)
• Enabling autocommit (p. 190)
• Using COPY to copy data from and UNLOAD to write data to an Amazon S3 bucket (p. 190)
Connecting to an Amazon Redshift cluster using AWS credentials
To connect to an Amazon Redshift cluster using your AWS credentials, run the following command.
>>> conn = redshift_connector.connect(
host='examplecluster.abc123xyz789.us-west-1.redshift.amazonaws.com', database='dev',
user='awsuser', password='my_password' )
Querying a table
To select all rows from the table book, run the following command.
>>> cursor.execute("select * from book")
Retrieving the query result set
To retrieve the query result set, run the following command.
>>> result: tuple = cursor.fetchall() print(result)
>> (['One Hundred Years of Solitude', 'Gabriel García Márquez'], ['A Brief History of Time', 'Stephen Hawking'])
Enabling autocommit
The autocommit property is off by default, following the Python Database API Specification. You can use the following commands to turn on the autocommit property of the connection. First, you perform a rollback command to make sure that a transaction is not in progress.
>>> con.rollback() con.autocommit = True con.run("VACUUM") con.autocommit = False
Using COPY to copy data from and UNLOAD to write data to an Amazon S3 bucket
The following example shows how to copy data from an Amazon S3 bucket into a table and then unload from the table into the S3 bucket.
A text file named category_csv.txt containing the following data is uploaded to an S3 bucket.
>>> 12,Shows,Musicals,Musical theatre 13,Shows,Plays,"All ""non-musical"" theatre"
14,Shows,Opera,"All opera, light, and ""rock"" opera"
Configuring connections in Amazon Redshift
15,Concerts,Classical,"All symphony, concerto, and choir concerts"
Following is an example of the Python code, which first connects to the Amazon Redshift database. It then creates a table called category and copies the CSV data from the S3 bucket into the table.
>>> with redshift_connector.connect(...) as conn:
with conn.cursor() as cursor:
cursor.execute("create table category (catid int, cargroup varchar, catname varchar, catdesc varchar)")
cursor.execute("copy category from 's3://testing/category_csv.txt' iam_role 'arn:aws:iam::123:role/RedshiftCopyUnload' csv;")
cursor.execute("select * from category") print(cursor.fetchall())
cursor.execute("unload ('select * from category') to 's3://testing/
unloaded_category_csv.txt' iam_role 'arn:aws:iam::123:role/RedshiftCopyUnload' csv;") print('done')
>>> ([12, 'Shows', 'Musicals', 'Musical theatre'], [13, 'Shows', 'Plays', 'All "non-musical" theatre'], [14, 'Shows', 'Opera', 'All opera, light, and "rock" opera'], [15, 'Concerts', 'Classical', 'All symphony, concerto, and choir concerts'])
done
The data is unloaded into the file unloaded_category_csv.text0000_part00 in the S3 bucket.
>>> 12,Shows,Musicals,Musical theatre 13,Shows,Plays,"All ""non-musical"" theatre"
14,Shows,Opera,"All opera, light, and ""rock"" opera"
15,Concerts,Classical,"All symphony, concerto, and choir concerts"