AWS Glue + Athena

Glue example extraction from RDS and query with Athena.

Create the infrastructure

Create the Terraform variables file:

cp config/template.tfvars .auto.tfvars

Apply Terraform:

terraform init
terraform apply

Once ready, enter the Glue Studio and test the connector to the RDS database.

Connect to the database using the EC2 jump server:

psql "postgresql://demouser:p4ssw0rd@<host>:5432/demodb"

Apply the prepare-database.sql script to generate data.

First, run the crawler to feed the database catalog.

aws glue start-crawler --name 'rds-aurora-crawler'

Connect to the AWS Glue Studio and go to the Jobs blade. Create a new Job:

Enter JSON for the output format, and fill it in the required information.

Save the job. File auto-generated-script-example.py is reference of what Glue will generate.

Run the ETL job and check the output files in S3:

{"favoriteFood":"Pasta","sex":"M","id":2,"birthday":"1998-03-15","name":"John"}

Athena needs an S3 data source, so querying the existing datasource is not possible since it runs over Aurora.

Use Glue again to prepare an Athena table with an S3 source:

Create a new database on Glue.
Create a new crawler that will read the S3 JSON data and feed the new Glue database.
Run the crawler.
Go to Athena and add a query result location on S3.

The table should be automatically created and you should now be able to run a query against the S3 data:

SELECT* FROM "crawler-s3"."transform" WHERE favoritefood='Lasagna';

Done 🏆 Athena will run your queries over S3 using SQL:

Delete the manually created Glue Jobs, Crawlers, Database, Table, S3, CloudWatch Logs.

Run terraform destroy -auto-approve to remove the infrastructure.