domino logo
5.0
  • Tech Ecosystem
  • Get Started
  • Domino Cloud
  • Collaborate
  • Projects
  • Work with Data
  • Workspaces
  • Environments
  • Executions
  • Deploy Models and Apps
  • Model Monitoring
  • Organizations
  • Security and Credentials
  • Notifications
  • Search
  • Domino CLI
  • Troubleshooting
  • Get Help
domino logo
About Domino
Domino Data LabKnowledge BaseData Science BlogTraining
User Guide
>
Work with Data
>
Data Sources
>
Connect to Data Sources
>
Connect to Redshift

Connect to Redshift

This topic describes how to connect to Amazon Redshift from Domino. You must have network connectivity between Redshift and your Domino deployment.

Domino recommends that you use a Domino Data Dource to connect to a Redshift instance from Domino.

Create a Redshift data source

  1. From the navigation pane, click Data.

  2. Click Create a Data Source.

  3. In the New Data Source window, from Select Data Store, select Amazon Redshift.

    New Data Source

  4. Enter the Host, Port, and name of the Database.

  5. Enter the Data Source Name.

  6. Optional: Enter a Description to explain the purpose of the data source to others.

  7. Click Next.

  8. Enter the Username and Password to connect to Amazon Redshift. Only basic (username/password) authentication is supported. The Domino secret store backed by HashiCorp Vault securely stores the credentials.

  9. If the data source authenticates, click Next.

  10. Select who can view and use the data source in projects.

  11. Click Finish Setup.

If your users have Domino permissions to use the data source and enter their credentials, they can now use the Domino Data API to retrieve data with the connector.

See Retrieve Data for more information.

Alternate way to connect to a Redshift data source

Warning

Prerequisites

  • Domino recommends storing your database username and password as environment variables in your project. This lets you access them at runtime without including them in your code.

Python

To establish a connection to Redshift with the psycopg2 library:

import psycopg2
import os

HOST = os.environ['REDSHIFT_HOST']
PORT = 5439 # redshift default
USER = os.environ['REDSHIFT_USER']
PASSWORD = os.environ['REDSHIFT_PASSWD']
DATABASE = 'mydatabase'

def db_connection():
    conn = psycopg2.connect(
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD,
        database=DATABASE,
    )
    return conn

example_query = "SELECT * FROM my_table LIMIT 5"

conn = db_connection()
try:
    cursor = conn.cursor()
    cursor.execute(example_query)
    results = cursor.fetchall() # careful, the results could be huge
    conn.commit()
    print results
finally:
    conn.close()

# using pandas
import pandas as pd
conn = db_connection()
try:
    df = pd.read_sql(example_query, conn)
    df.to_csv('results/outfile.csv', index=False)
finally:
    conn.close()

R

To establish a connection to Redshift with the RPostgreSQL library:

install.packages("RPostgreSQL")
library(RPostgreSQL)

redshift_host <- Sys.getenv("REDSHIFT_HOST")
redshift_port <- "5439"
redshift_user <- Sys.getenv("REDSHIFT_USER")
redshift_password <- Sys.getenv("REDSHIFT_PASSWORD")
redshift_db <- "mydatabase"

drv <- dbDriver("PostgreSQL")
conn <- dbConnect(
    drv,
    host=redshift_host,
    port=redshift_port,
    user=redshift_user,
    password=redshift_password,
    dbname=redshift_db)

tryCatch({
    example_query <- "SELECT * FROM my_table LIMIT 5"
    results <- dbGetQuery(conn, example_query)
    print(results)
}, finally = {
    dbDisconnect(conn)
})
Domino Data LabKnowledge BaseData Science BlogTraining
Copyright © 2022 Domino Data Lab. All rights reserved.