Querying Data in Athena

Fresh

Amazon Athena is a serverless interactive query service that lets you analyze data stored in Amazon S3 using standard SQL. Because IMDb bulk data is delivered through AWS Data Exchange to S3, Athena provides a fast and cost-effective way to run ad-hoc queries against the full dataset without any infrastructure setup.

---

What is Amazon Athena

Athena is built on Apache Presto and supports ANSI SQL. It uses a schema-on-read approach: the actual data files stay in S3 in their original format (JSON Lines), and you define table schemas that tell Athena how to interpret them. You pay only for the data scanned per query.

For IMDb bulk data, this means you can run SQL queries against millions of title and name records without downloading any files locally.

---

Getting Started

Step 1 — Create a Database

CREATE DATABASE imdb;

Step 2 — Identify Your S3 Location

When you subscribe to an IMDb product on AWS Data Exchange and export the data to S3, the files land in a path like:

s3://your-bucket-name/imdb-data/title_essential_v2/
s3://your-bucket-name/imdb-data/name_essential_v1/
s3://your-bucket-name/imdb-data/geo_title_meter_v1/

Replace s3://your-bucket-name/imdb-data/ with your actual S3 path in all DDL statements. See the Creating Tables DDL page for full CREATE TABLE statements.

Step 3 — Create Tables

Create Athena tables using the DDL on the Creating Tables DDL page, then run the queries below.

---

Sample Queries

Query 1 — Highest-Rated Movies

Returns the top 20 movies by IMDb rating with at least 100,000 votes.

SELECT
  t.titleId,
  t.originalTitle,
  t.year,
  t.imdbRating.rating AS rating,
  t.imdbRating.numberOfVotes AS votes,
  t.runtimeMinutes
FROM title_essential_v2 t
WHERE t.titleType = 'movie'
  AND t.imdbRating.numberOfVotes >= 100000
  AND t.imdbRating.rating IS NOT NULL
ORDER BY t.imdbRating.rating DESC
LIMIT 20;

Sample Results:

titleId	originalTitle	year	rating	votes	runtimeMinutes
tt0111161	The Shawshank Redemption	1994	9.3	2800000	142
tt0068646	The Godfather	1972	9.2	1900000	175
tt0468569	The Dark Knight	2008	9.0	2700000	152

---

Query 2 — Known-For Titles for a Person

Returns the known-for titles for a specific name (Henry Fonda).

SELECT
  n.nameId,
  n.name.nameText AS personName,
  kf.titleId AS knownForTitleId
FROM name_essential_v1 n
CROSS JOIN UNNEST(n.knownFor) AS t(kf)
WHERE n.nameId = 'nm0000020';

Sample Results:

nameId	personName	knownForTitleId
nm0000020	Henry Fonda	tt0050083
nm0000020	Henry Fonda	tt0082781
nm0000020	Henry Fonda	tt0081398

---

Query 3 — Principal Cast for a Title

Returns the principal cast members for The Godfather.

SELECT
  t.titleId,
  t.originalTitle,
  cm.nameId,
  cm.billing,
  cr.name AS characterName
FROM title_essential_v2 t
CROSS JOIN UNNEST(t.principalCastMembers) AS pc(cm)
CROSS JOIN UNNEST(cm.roles) AS r(cr)
WHERE t.titleId = 'tt0068646'
ORDER BY cm.billing ASC;

Sample Results:

titleId	originalTitle	nameId	billing	characterName
tt0068646	The Godfather	nm0000008	1	Don Vito Corleone
tt0068646	The Godfather	nm0000199	2	Michael Corleone
tt0068646	The Godfather	nm0001001	3	Tom Hagen

---

Query 4 — Awards and Nominees

Returns all Academy Award wins from the title dataset.

SELECT
  t.titleId,
  t.originalTitle,
  t.year,
  aw.awardName,
  aw.category,
  aw.year AS awardYear,
  aw.winner
FROM title_essential_v2 t
CROSS JOIN UNNEST(t.awards) AS ta(aw)
WHERE aw.awardName = 'Academy Awards'
  AND aw.winner = true
ORDER BY aw.year DESC
LIMIT 20;

---

Query 5 — Episode Title Texts

Returns episode titles and their season/episode numbers for Breaking Bad.

SELECT
  ep.titleId AS episodeTitleId,
  ep.originalTitle AS episodeTitle,
  ep.episodeInfo.seasonNumber AS season,
  ep.episodeInfo.episodeNumber AS episode,
  ep.imdbRating.rating AS rating
FROM title_essential_v2 ep
WHERE ep.episodeInfo.seriesTitleId = 'tt0903747'
ORDER BY ep.episodeInfo.seasonNumber, ep.episodeInfo.episodeNumber;

Sample Results:

episodeTitleId	episodeTitle	season	episode	rating
tt0959621	Pilot	1	1	8.9
tt1054487	Cat's in the Bag	1	2	8.7
tt1054488	...And the Bag's in the River	1	3	8.8

---

Query 6 — Get the Series from an Episode

Look up the parent series title given an episode ID.

SELECT
  series.titleId AS seriesTitleId,
  series.originalTitle AS seriesTitle,
  series.seriesInfo.startYear AS startYear,
  series.seriesInfo.endYear AS endYear
FROM title_essential_v2 ep
JOIN title_essential_v2 series
  ON ep.episodeInfo.seriesTitleId = series.titleId
WHERE ep.titleId = 'tt0959621';

---

Query 7 — Most Popular Titles by Region (GeoMeter)

Returns the top 10 titles in the United States for a given date.

SELECT
  gm.rank,
  gm.titleId,
  t.originalTitle,
  t.titleType,
  t.imdbRating.rating AS rating
FROM geo_title_meter_v1 gm
JOIN title_essential_v2 t ON gm.titleId = t.titleId
WHERE gm.area = 'US'
  AND gm.date = '2026-05-08'
ORDER BY gm.rank ASC
LIMIT 10;

---

Query 8 — Most Popular Names by Region (GeoMeter)

Returns the top 10 most popular people in Germany for a given date.

SELECT
  gm.rank,
  gm.nameId,
  n.name.nameText AS personName
FROM geo_star_meter_v1 gm
JOIN name_essential_v1 n ON gm.nameId = n.nameId
WHERE gm.area = 'DE'
  AND gm.date = '2026-05-08'
ORDER BY gm.rank ASC
LIMIT 10;

---

Key SQL Patterns

CROSS JOIN UNNEST

IMDb bulk data uses nested arrays within JSON records. To query array elements in Athena, use CROSS JOIN UNNEST:

-- Expand an array field
CROSS JOIN UNNEST(t.genres) AS g(genre)

-- Access the expanded value
WHERE genre.text = 'Drama'

Struct Field Access

Access nested struct fields using dot notation:

-- Nested struct
t.imdbRating.rating
t.imdbRating.numberOfVotes
t.episodeInfo.seasonNumber

Filtering by Nested Array Values

-- Find all Drama titles
SELECT t.titleId, t.originalTitle
FROM title_essential_v2 t
CROSS JOIN UNNEST(t.genres) AS g(genre)
WHERE genre.text = 'Drama'
  AND t.titleType = 'movie'
LIMIT 20;