Connect to Athena SQL Tables by Using Python

Amazon Athena is an interactive query service that allows you to analyze data in the Tetra Data Lake or Data Lakehouse using standard SQL.

The following procedure shows how to connect to your Tetra Data Platform (TDP) organization's Athena SQL tables by using Python.

Before You Begin

Before you can use Python to connect to your organization's Athena SQL tables, make sure that you do the following:

For more information about setting up the prerequisites, see Use Third-Party Tools to Connect to Athena SQL Tables.

Use Python to Connect to Athena SQL Tables

Copy and paste the following Python code into your Python environment. Then update the following in the example code and run the script:

Install the necessary Python modules.
Replace everything in '<>' with your SQL credentials from the TDP.

# install necessary modules
!pipenv install sqlalchemy
!pipenv install PyAthena
!pipenv install PyAthena[SQLAlchemy]

# import necessary modules
from sqlalchemy.engine import create_engine
from sqlalchemy.sql.schema import Table, MetaData
from sqlalchemy.orm import sessionmaker

# define connection
conn_str = 'awsathena+rest://{aws_access_key_id}:{aws_secret_access_key}@athena.{region_name}.amazonaws.com:443/{schema_name}?s3_staging_dir={s3_staging_dir}'
engine = create_engine(
  conn_str.format(
    aws_access_key_id='<Access Key ID>', # shared across the entire org
    aws_secret_access_key='<Access Key>', # shared across the entire org
    region_name='<AWS Region>', # shared across the entire org
    schema_name='<Org Slug>'.replace('-','_'), # replace dashes from org slug to underscores,
    s3_staging_dir='<S3OutputLocation>'
  )
)
# create session
Session = sessionmaker(bind=engine)
session = Session()

# query data
file_id = '<File ID from TetraScience platform>' #
root_table = Table('<table name>', MetaData(bind=engine), autoload=True)
results = session.query(root_table).filter_by(uuid=file_id)

# print the first row
print(results.first())