Connect with SQL Magic and IAM Credentials

​​
​​
Tags: #redshift #database #snippet
Author: Caleb Keller​
Input
ipython-sql
boto3
psycopg2
sqlalchemy-redshift
If you're running in NaaS, you can execute the below to install the necessary libraries.
1
!pip install -q ipython-sql boto3 psycopg2-binary sqlalchemy-redshift
Copied!
When using the ipython-sql library of SQL Magics I like to put the %reload_ext sql at the top. This loads the extension if it isn't already loaded or reloads it. Reload instead of load helps it not error out if you so something like run all cells.
1
%reload_ext sql
2
​
3
import boto3
4
import psycopg2
5
import getpass
6
import pandas as pd
7
from urllib import parse
Copied!
Model
The SQL Magic, powered by SQL ALchemy, needs the connection string in a specific format. The below function does several things:
1.It takes in your AWS IAM credentials.
2.It uses those credentials to get temporary database credentials.
3.It creates a SQL alchemy connection string from those credentials.
1
def rs_connect(dbname, dbhost, clusterid, dbport, dbuser, region_name='us-east-1'):
2
​
3
    ''' Connect to redshift using AIM credentials'''
4
    aaki = getpass.getpass('aws_access_key_id')
5
    asak = getpass.getpass('aws_secret_access_key')
6
    aws_session = boto3.session.Session(aws_access_key_id=aaki, aws_secret_access_key=asak, region_name=region_name)
7
    aaki = ''; asak = ''
8
    
9
    aws_rs = aws_session.client('redshift')
10
    response = aws_rs.get_cluster_credentials(DbUser=dbuser, DbName=dbname, ClusterIdentifier=clusterid, AutoCreate=False)
11
    
12
    ''' Convert those credentials into Database user credentials '''
13
    dbuser = response['DbUser']
14
    dbpwd = response['DbPassword']
15
    
16
    ''' Generate the SQLAlchemy Connection string '''
17
    connectionString = 'redshift+psycopg2://{username}:{password}@{host}:{port}/{db}?sslmode=prefer'.format(username=parse.quote_plus(dbuser), password=parse.quote_plus(dbpwd), host=dbhost, port=dbport, db=dbname)
18
​
19
    dbuser = None; dbpwd = None; conn_str = None; response = None;
20
    
21
    return connectionString
Copied!
Output
Run the below and replace the parameters with your own server's information.
1
connectionString = rs_connect('database_name', 'host', 'cluster_id', 5439, 'database_user')
Copied!
1
%sql $connectionString
Copied!
1
%%sql
2
​
3
select
4
    your,
5
    sql,
6
    goes,
7
    here
8
from
9
  your.brain
Copied!
Article from the author here: here.
For more on SQL magics read up on them with the below links:
https://towardsdatascience.com/jupyter-magics-with-sql-921370099589
https://blog.dominodatalab.com/lesser-known-ways-of-using-notebooks/

Redshift

Remoteok

Last modified 2mo ago

Copy link

Edit on GitHub

Contents

Input

Model

Output