Query files with SQL using Amazon Athena

Enterprise systems frequently produce file-based output — log exports, transaction extracts, inventory snapshots, inter-system file drops — that lands on an NFS or SMB file share.

With an Amazon S3 access point attached to the FSx for ONTAP volume, Amazon Athena queries the files in place. Your applications and users continue to write to the volume over NFS or SMB the way they always have, and analysts run standard SQL against that data through the access point. Because an FSx for ONTAP volume is simultaneously accessible over NFS, SMB, and the Amazon S3 API, the same file can be produced by one protocol and consumed by another without a copy.

In this tutorial, you upload a sample dataset to your FSx for ONTAP volume through an Amazon S3 access point, register it in the AWS Glue Data Catalog, and query it with Amazon Athena.

Prerequisites

Before you begin, make sure you have the following:

Step 1: Upload sample data to your FSx for ONTAP volume

This tutorial uses the NYC Taxi and Limousine Commission (TLC) Trip Record Data, a publicly available dataset of taxi trips in New York City. The data is in Apache Parquet format, a columnar format that Athena can query efficiently.

Download one month of yellow taxi trip data and upload it to your FSx for ONTAP volume through the Amazon S3 access point.

$ curl -O https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet

Upload the file to your FSx for ONTAP volume using the access point alias. Replace my-ap-alias-ext-s3alias with your access point alias.

$ aws s3 cp yellow_tripdata_2024-01.parquet \
    s3://my-ap-alias-ext-s3alias/taxi-data/yellow_tripdata_2024-01.parquet

Verify the file is accessible through the access point.

$ aws s3 ls s3://my-ap-alias-ext-s3alias/taxi-data/
2024-01-23 02:18:13   49961641 yellow_tripdata_2024-01.parquet

Step 2: Create a database in the AWS Glue Data Catalog

Create a database in the AWS Glue Data Catalog to hold the table metadata. You can create the database using the AWS Glue console, the Athena query editor, or the AWS CLI.

AWS Glue console

Athena query editor or AWS CLI

$ aws athena start-query-execution \
    --query-string "CREATE DATABASE IF NOT EXISTS fsxn_taxi_demo" \
    --work-group primary

Step 3: Register the data in the AWS Glue Data Catalog

You can register your data using either a AWS Glue crawler (recommended) or a manual CREATE EXTERNAL TABLE statement in Athena.

Option A: Use a AWS Glue crawler (recommended)

A AWS Glue crawler automatically discovers the schema of your data and creates a table in the AWS Glue Data Catalog. This is the recommended approach because the crawler infers the correct column types from the Parquet file metadata.

Option B: Create a table manually in Athena

If you already know the schema of your data, you can create the table directly in Athena using a CREATE EXTERNAL TABLE statement. Use the access point alias in the LOCATION clause.

CREATE EXTERNAL TABLE fsxn_taxi_demo.yellow_taxi_trips (
    VendorID bigint,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count bigint,
    trip_distance double,
    RatecodeID bigint,
    store_and_fwd_flag string,
    PULocationID bigint,
    DOLocationID bigint,
    payment_type bigint,
    fare_amount double,
    extra double,
    mta_tax double,
    tip_amount double,
    tolls_amount double,
    improvement_surcharge double,
    total_amount double,
    congestion_surcharge double,
    Airport_fee double
)
STORED AS PARQUET
LOCATION 's3://my-ap-alias-ext-s3alias/taxi-data/'

Step 4: Query your data

Open the Athena query editor or use the AWS CLI to run SQL queries against your FSx for ONTAP data. The following examples use the table created by the AWS Glue crawler (taxi_data). If you created the table manually, replace taxi_data with yellow_taxi_trips.

Count total trips and calculate averages

SELECT
    COUNT(*) AS total_trips,
    ROUND(AVG(trip_distance), 2) AS avg_distance_miles,
    ROUND(AVG(total_amount), 2) AS avg_total_usd,
    ROUND(AVG(passenger_count), 1) AS avg_passengers
FROM fsxn_taxi_demo.taxi_data

Example output:

Find the busiest pickup hours

SELECT
    HOUR(tpep_pickup_datetime) AS pickup_hour,
    COUNT(*) AS trip_count,
    ROUND(AVG(total_amount), 2) AS avg_fare
FROM fsxn_taxi_demo.taxi_data
GROUP BY HOUR(tpep_pickup_datetime)
ORDER BY trip_count DESC
LIMIT 5

Find the highest-revenue pickup locations

SELECT
    PULocationID AS pickup_location,
    COUNT(*) AS trip_count,
    ROUND(SUM(total_amount), 2) AS total_revenue
FROM fsxn_taxi_demo.taxi_data
GROUP BY PULocationID
ORDER BY total_revenue DESC
LIMIT 10

Considerations

Clean up

To avoid ongoing charges, delete the resources you created in this tutorial.