Query data from multiple sources in S3 on Athena?

Question

I wrote a python code that uploads data to S3 in csv format daily. I formatted the file name like this: 'some_data_YY_MM_DD.csv'. Using Athena, I want to query from the past 7 days of data. 
1. Is there a way to combine multiple csv files on S3 to use for Athena? Maybe using AWS Glue?
2. Is there a way to automatically update the dataset for Athena? I don't want to manually update the 7 files. I want it to just use the most recent 7 csv files on S3. 
Thank you.

Ismael Murillo · Accepted Answer

Hi johnkimm,

To achieve your goal of querying the most recent 7 days of data in Amazon Athena from multiple CSV files stored in S3, you can follow these steps:

Use AWS Glue to catalog your data: AWS Glue can help you create a catalog of your data in S3, making it easier for Athena to query. You can create a Glue crawler to automatically detect new files in your S3 bucket and update the metadata catalog.
Partition your data by date: By organizing your data into partitions based on the date (e.g., s3://your-bucket/prefix/year=YYYY/month=MM/day=DD/), you can efficiently query data for specific date ranges.
Use a dynamic query in Athena: Athena supports querying partitioned data, and you can write a query to select data from the past 7 days.

Here is what is needed:

Step 1: Create a Glue Crawler

Go to the AWS Glue Console and create a new crawler.
Set the crawler source type to "Data Stores" and specify your S3 bucket path (e.g., s3://your-bucket/prefix/).
Configure the crawler output to create a new database or use an existing one in the Glue Data Catalog.
Run the crawler to populate the Glue Data Catalog with metadata about your CSV files.

**Step 2: Organize Your Data by Date ** When you upload your CSV files to S3, structure them with date-based partitions. For example: bash

< s3://your-bucket/prefix/year=2024/month=05/day=21/some_data_24_05_21.csv s3://your-bucket/prefix/year=2024/month=05/day=22/some_data_24_05_22.csv ...>

This partitioning allows Athena to perform efficient queries on specific date ranges.

Step 3: Query the Data in Athena

You can write an Athena query to select data from the past 7 days using the date and timestamp functions. Here's an example query: < SELECT * FROM your_database.your_table WHERE date_parse(year || '-' || month || '-' || day, '%Y-%m-%d') >= date_add('day', -7, current_date)>

This query assumes your table has year, month, and day columns, which are automatically populated based on the partition structure.

Step 4: Automate the Glue Crawler (Optional)

To ensure the Glue catalog is always up-to-date with the latest files, you can schedule the Glue crawler to run at regular intervals (e.g., daily).

Go to the Glue Console and find your crawler.
Set up a schedule to run the crawler every day.

This will ensure that any new CSV files uploaded to S3 are automatically detected and included in the Glue Data Catalog, making them available for Athena queries.

By using AWS Glue to catalog your data and partitioning your data by date, you can easily query the most recent 7 days of data in Athena. The Glue crawler will keep your catalog updated, and partitioning will ensure efficient queries. This setup minimizes manual intervention and automates the process of keeping your Athena dataset up-to-date.

Ismael Murillo

Serge · Answer

Hello

If you want to restrict Athena to only query the files from the 7 last days, you could consider grouping those files into a specific S3 location that is regularly updated.
Your script can upload files into a input bucket, and you can have a lambda function invoked to copy the most recent files into another bucket. The lambda funtion may be invoked either daily, or triggered from S3 when your script delivers the new file.

Then use this bucket in  the CREATE TABLE statement in athena to only query the latest files

Hope this helps

Leo K · Answer

Athena considers all the files with paths starting with the prefix specified for the table as belonging to the same table. You don't need to do anything special to get it to process multiple files, except to place them in the same folder hierarchy, separated from anything else in the same bucket.

If you're intending to retain much more data in the bucket than the 7 days you want to query, the easiest solution to implement and among the least expensive to use is for you to upload the files to subfolders named after the date and setting the table in Athena to use partition projection based on that.

You need the folder structure, because unlike S3 natively, Athena requires the paths used for defining tables or partitions to use the forward slash '/' as the separator. Only having the date in the "file name" part separated by underscores won't work with projection.

Your table definition could look something like this, when the full paths to the files would be of the form (note that the bucket name will need to be in all lowercase, as usual):

`s3://MY-EXAMPLE-BUCKET/some-data/2024/05/28/some_data_2024_05_28.csv`
```
CREATE EXTERNAL TABLE IF NOT EXISTS `my_some_data` (
    some_column1 string,
    some_column2 int
)
PARTITIONED BY
(
    day string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\" )
LOCATION 's3://MY-EXAMPLE-BUCKET/some-data/'
TBLPROPERTIES
(
    "projection.enabled" = "true",
    "projection.day.type" = "date",
    "projection.day.range" = "2024/01/01,NOW",
    "projection.day.format" = "yyyy/MM/dd",
    "projection.day.interval" = "1",
    "projection.day.interval.unit" = "DAYS",
    "storage.location.template" = "s3://MY-EXAMPLE-BUCKET/some-data/${day}"
)
```

And you could query it like so to get it to consider only the files contained in the folders for the past 7 days. The partitioning will ensure that Athena won't spend time and money scanning files in folders with an earlier date:
```
select *
from my_some_data
where day>=date_format(date_add('day', -7, now()), '%Y/%m/%d')
```

Query data from multiple sources in S3 on Athena?

Add your answer

Relevant content