Amazon Redshift
Amazon Redshift is a fully managed, petabyte-scale data warehouse service. Deepnote integrates with Redshift seamlessly to bring the cloud data warehouse to your notebook.
What can you do with the Redshift integration?
The Redshift integration allows you to query databases and explore the structure of your warehouse. Think "SQL editor" but with all the modern productivity boosters that come with Deepnote's notebook. For example:
- Write native SQL and Python in the same notebook
- Search your entire warehouse instantly via the integrated schema explorer
- Get intelligent autocomplete for columns, tables, and databases
- Interactively explore data without writing any additional code
How to connect to Redshift
Authenticating using an IAM role
Using an IAM role instead of a username and password to connect to your Redshift is preferred for enhanced security and making it easier to manage. IAM roles offer temporary credentials that automatically rotate, reducing the risk of long-term credential exposure. This approach also simplifies permissions management, allowing for more granular access control to Redshift resources based on the principle of least privilege. Moreover, it eliminates the need to store static credentials, reducing the potential for security breaches.
In the "Authentication" section, select the IAM role option in the dropdown menu.
Enter the Amazon Resource Name (ARN) of the AWS role you wish to use for this integration. This role should be configured with the necessary permissions to access the Redshift clusters that Deepnote will interact with.
To allow Deepnote to access your Redshift cluster, you will need to update the role's trust policy. Copy the generated trust policy and update your role's trust relationship in the AWS IAM console.
The Deepntote integration will create a database user called IAMA:deepnote
that will be used to perform your queries.
Connecting to Redshift with Python
Simple as installing the connector.
pip install redshift_connector
import redshift_connector
conn = redshift_connector.connect(
host='your_host',
database='your_db',
port=5439,
user='your_user',
password='your_password'
)
# Create a Cursor object
cursor = conn.cursor()
# Query a table using the Cursor
cursor.execute("select * from book")
#Retrieve the query result set
result: tuple = cursor.fetchall()
print(result)
(['One Hundred Years of Solitude', 'Gabriel García Márquez'], ['A Brief History of Time', 'Stephen Hawking'])
Using Redshift in Deepnote
Now that you're connected to Redshift, you can do the following:
-
Click the newly created integration in the Integrations section to open the schema browser. Click here to learn more about the schema browser.
-
Create an SQL block that points to your warehouse and query your data. Autocomplete for columns, tables, and databases will be at your fingertips as you type. Click here to learn more about SQL blocks.
-
Explore column distributions as well as sorting and filtering capabilities on the results set. All results are displayed as an interactive pandas DataFrame. Click here to learn about interactive DataFrame output.
-
Pipe the results of your query into a chart block for rapid interactive data visualization. Click here to learn more about chart blocks.