Query data in Aurora

Prior reading: Create an Aurora database

Purpose: This document provides detailed instructions for querying data in Aurora PostgreSQL databases.

Note: This article assumes you have either created an Aurora PostgreSQL database in an AWS-backed workspace and/or you have access to an Aurora PostgreSQL database.

Introduction

Workbench users with appropriate access to AWS-backed workspaces can create and query Aurora databases.

Prerequisites

To query an Aurora database, you'll need at least Reader access to the workspace. Users with Writer or higher access can write data and create and delete databases.

Query an Aurora database

From your cloud app, you can query an Aurora database via the Workbench CLI. There are two different ways to query:

Use psql tool

psql is the PostgreSQL interactive terminal. It's pre-intalled in JupyterLab cloud apps.

Run the following in your cloud app's terminal. Replace <resource-id> with your Aurora database's ID and update the sample SELECT statement with your query.

# Get connection string with temporary password for read-only access
CONNECTION_STRING=$(wb resource resolve \
    --id <resource-id> \
    --access-mode READ_ONLY \
    --include-password)

# Alternatively, for write access (requires writer role or higher)
CONNECTION_STRING=$(wb resource resolve \
    --id <resource-id> \
    --access-mode WRITE_READ \
    --include-password)

# Connect using psql
psql "${CONNECTION_STRING}"

# Or run a query directly
psql "${CONNECTION_STRING}" -c "SELECT * FROM <table_name> LIMIT 10;"

Use Python

With psycopg3 and SQLAlchemy, users can use Python to query an Aurora database.

Run the following in your cloud app's terminal:

python3 -m pip install SQLAlchemy "psycopg[binary,pool]"

Then, launch a Python notebook in your app and paste the following. Ensure you replace <your-database-id> with your database's ID and update the pd.read_sql() line with your specific SQL query:

import subprocess
import psycopg
from sqlalchemy import create_engine
import pandas as pd

# Get connection string from Workbench
result = subprocess.run(
    ['wb', 'resource', 'resolve',
     '--id', '<your-database-id>',
     '--access-mode', 'READ_ONLY',
     '--include-password'],
    capture_output=True,
    text=True,
    check=True
)
connection_string = result.stdout.strip()

# Create SQLAlchemy engine
def get_connection():
    return psycopg.connect(connection_string)

engine = create_engine('postgresql+psycopg://', creator=get_connection)

# Query using pandas
df = pd.read_sql("SELECT * FROM <table_name>", engine)

Important querying considerations

Keep the following in mind when querying Aurora databases in Workbench:

• Connection strings use PostgreSQL's libpq keyword/value format, as such:

  host=... port=... dbname=... user=... password=... sslmode=require
  

  This is required because IAM tokens contain special characters that break URI parsing.

• After 15 minutes, a new token will be required to establish new connections to the database. Regenerate the connection string to get a new token.

• Always use the --include-password flag to embed the IAM token in the connection string.