<jupyter_start><jupyter_text>Querying Athena from EMR Studio1. Install the [pyathena](https://github.com/laughingman7743/PyAthena/) library._If this is the first time installing the library on the cluster, you'll need to restart your Kernel._<jupyter_code>!/emr/notebook-env/bin/pip install pyathena<jupyter_output>Defaulting to user installation because normal site-packages is not writeable
Requirement already satisfied: pyathena in /home/emr-notebook/.local/lib/python3.7/site-packages (2.3.0)
Requirement already satisfied: botocore>=1.5.52 in /home/emr-notebook/.local/lib/python3.7/site-packages (from pyathena) (1.21.54)
Requirement already satisfied: boto3>=1.4.4 in /home/emr-notebook/.local/lib/python3.7/site-packages (from pyathena) (1.18.54)
Requirement already satisfied: tenacity>=4.1.0 in /mnt/notebook-env/lib/python3.7/site-packages (from pyathena) (8.0.0)
Requirement already satisfied: jmespath<1.0.0,>=0.7.1 in /home/emr-notebook/.local/lib/python3.7/site-packages (from boto3>=1.4.4->pyathena) (0.10.0)
Requirement already satisfied: s3transfer<0.6.0,>=0.5.0 in /home/emr-notebook/.local/lib/python3.7/site-packages (from boto3>=1.4.4->pyathena) (0.5.0)
Requirement already satisfied: urllib3<1.27,>=1.25.4 in /mnt/notebook-env/lib/python3.7/site-packages (from botocore>=1.5.52->pyathena) (1[...]<jupyter_text>2. Connect to Athena and query!<jupyter_code>from pyathena import connect

cursor = connect(
    s3_staging_dir="s3://<BUCKET>/queries/",
    region_name="us-west-2"
).cursor()

cursor.execute("SELECT * FROM hudi_trips LIMIT 10")

for row in cursor:
    print(row)<jupyter_output>('20210923175137', '20210923175137_2_2', '258241be-2f7c-4bad-99f7-f65a2a1bc032', 'americas-brazil-sao_paulo', '658db9b2-5081-49b1-b6e7-6ff1b1d92448-0_2-155-196_20210923175137.parquet', 0.7004607721204296, 0.9911546157239198, 'driver-204', 0.14602911545960373, 0.430070203188727, 89.89115113672493, 'rider-204', 1631914555102, '258241be-2f7c-4bad-99f7-f65a2a1bc032', 'americas-brazil-sao_paulo')
('20210923175441', '20210923175441_1_1', '130b2891-b8e8-45cb-86c5-9ba17bf67e9c', 'americas-brazil-sao_paulo', '658db9b2-5081-49b1-b6e7-6ff1b1d92448-0_1-189-243_20210923175441.parquet', 0.4789745387904072, 0.14781856144057215, 'driver-022', 0.10509642405359532, 0.07682825311613706, 30.429177017810616, 'rider-022', 1632072813847, '130b2891-b8e8-45cb-86c5-9ba17bf67e9c', 'americas-brazil-sao_paulo')
('20210923175137', '20210923175137_2_7', 'e8c22378-8cf4-480a-bbcb-2d727e72bbe9', 'americas-brazil-sao_paulo', '658db9b2-5081-49b1-b6e7-6ff1b1d92448-0_2-155-196_20210923175137.parquet', 0.011933855867048981,[...]<jupyter_text>You can also query from different databases if you want.<jupyter_code>cursor.execute("SHOW TABLES FROM intro_data_proc")
print(cursor.fetchall())<jupyter_output>[('dea_pain_pills_parquet',), ('dea_pain_pills_tsv',), ('dea_pain_pills_tsv_gz',)]<jupyter_text>Using SparkSQLIf your EMR Cluster is using the [Glue Data Catalog](https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-spark-glue.html), you can also query your data on S3 with SparkSQL - just switch to the PySpark kernel and use the `%%sql` magic.<jupyter_code>%%sql

SHOW TABLES from intro_data_proc<jupyter_output>Starting Spark application
{'fraction_non_alphanumeric': 0.14365152919369786, 'fraction_numerical': 0.19802286067346309, 'mean_word_length': 6.243847874720358, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 26, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '91709', 'license': 'CC0-1.0', 'n_tokens_mistral': 1709, 'n_tokens_neox': 1369, 'n_words': 208, 'path': 'emr/studio/notebooks/emr-studio-athena.ipynb', 'repo_name': 'dacort/demo-code', 'size': '3237'}