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. 1.
    It takes in your AWS IAM credentials.
  2. 2.
    It uses those credentials to get temporary database credentials.
  3. 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/
Last modified 2mo ago
Copy link
Edit on GitHub