Connect to Athena Tables from Python
Amazon Athena is an interactive query service that allows you to use standard SQL to view and analyze data in your organization's Tetra Data Lake S3 bucket. This page describes how to connect to your Athena tables from Python.
Before You Begin
Before you can use Python to connect to Athena tables, make sure that you do the following:
Connect to Athena Tables from Python
Below is a Python code snippet you can copy and paste into your Python environment and run. All you need to do is:
- Install the necessary Python modules.
- Replace everything in
'<>'
with the your account connection details from the Tetra Data Platform (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())
Updated 10 months ago