Query data in Aurora

Prior reading: Aurora databases

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

Note: This article is relevant to Workbench users working on Amazon Web Services-backed workspaces. It 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. This article focuses on how to query Aurora databases. See Data resource operations for a step-by-step guide to creating an Aurora database.

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 psql tool or by using Python.

Use psql tool

psql is the PostgreSQL interactive terminal. It comes pre-installed in JupyterLab cloud apps.

Run the following in your 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, run this 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. 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.