Creating Tables DDL

Fresh

Use these DDL statements to create Athena tables for the IMDb bulk data datasets. Replace s3://your-bucket/your-prefix/ with the actual S3 path where your AWS Data Exchange exports are stored.

All tables use the OpenX JSON SerDe for JSON Lines parsing.

---

name_essential_v1

CREATE EXTERNAL TABLE IF NOT EXISTS name_essential_v1 (
  nameId STRING,
  remappedTo STRING,
  name STRUCT<
    nameText: STRING,
    nameTextSortable: STRING
  >,
  awards ARRAY<STRUCT<
    year: INT,
    awardName: STRING,
    category: STRING,
    titles: ARRAY<STRING>,
    winner: BOOLEAN,
    event: STRING
  >>,
  death STRUCT<
    deathStatus: STRING,
    deathDate: STRUCT<
      year: INT,
      month: INT,
      day: INT
    >,
    deathCause: STRING,
    deathLocation: STRING
  >,
  filmography STRUCT<
    cast: ARRAY<STRUCT<
      titleId: STRING,
      roles: ARRAY<STRUCT<
        character: STRING,
        characterId: STRING
      >>,
      billing: INT,
      attributes: ARRAY<STRING>
    >>,
    crew: ARRAY<STRUCT<
      titleId: STRING,
      category: STRING,
      jobs: ARRAY<STRING>,
      attributes: ARRAY<STRING>
    >>
  >,
  knownFor ARRAY<STRUCT<
    titleId: STRING,
    category: STRING
  >>,
  trademarks ARRAY<STRING>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/your-prefix/name_essential_v1/';

---

title_essential_v2

CREATE EXTERNAL TABLE IF NOT EXISTS title_essential_v2 (
  titleId STRING,
  remappedTo STRING,
  originalTitle STRING,
  titleType STRING,
  year INT,
  runtimeMinutes INT,
  isAdult BOOLEAN,
  imdbUrl STRING,
  imdbRating STRUCT<
    rating: DOUBLE,
    numberOfVotes: INT
  >,
  titleDisplay STRUCT<
    text: STRING
  >,
  akas ARRAY<STRUCT<
    country: STRING,
    language: STRING,
    title: STRING
  >>,
  awards ARRAY<STRUCT<
    year: INT,
    awardName: STRING,
    category: STRING,
    titles: ARRAY<STRING>,
    winner: BOOLEAN,
    event: STRING
  >>,
  creditsByCategory STRUCT<
    cast: STRUCT<
      cast: ARRAY<STRUCT<
        nameId: STRING,
        roles: ARRAY<STRUCT<
          character: STRING,
          characterId: STRING
        >>,
        billing: INT,
        attributes: ARRAY<STRING>
      >>
    >,
    director: STRUCT<
      crew: ARRAY<STRUCT<
        nameId: STRING,
        jobs: ARRAY<STRING>,
        attributes: ARRAY<STRING>
      >>
    >,
    writer: STRUCT<
      crew: ARRAY<STRUCT<
        nameId: STRING,
        jobs: ARRAY<STRING>,
        attributes: ARRAY<STRING>
      >>
    >,
    producer: STRUCT<
      crew: ARRAY<STRUCT<
        nameId: STRING,
        jobs: ARRAY<STRING>,
        attributes: ARRAY<STRING>
      >>
    >
  >,
  principalCastMembers ARRAY<STRUCT<
    nameId: STRING,
    roles: ARRAY<STRUCT<
      character: STRING,
      characterId: STRING
    >>,
    billing: INT
  >>,
  principalCrewMembers ARRAY<STRUCT<
    nameId: STRING,
    category: STRING,
    jobs: ARRAY<STRING>
  >>,
  certificates ARRAY<STRUCT<
    country: STRING,
    rating: STRING,
    ratingsBody: STRING,
    reason: STRING
  >>,
  color ARRAY<STRING>,
  companies STRUCT<
    production: ARRAY<STRUCT<
      companyId: STRING,
      companyName: STRING,
      country: STRING,
      attributes: ARRAY<STRING>
    >>,
    distribution: ARRAY<STRUCT<
      companyId: STRING,
      companyName: STRING,
      country: STRING,
      attributes: ARRAY<STRING>
    >>,
    specialEffects: ARRAY<STRUCT<
      companyId: STRING,
      companyName: STRING,
      country: STRING,
      attributes: ARRAY<STRING>
    >>,
    miscellaneous: ARRAY<STRUCT<
      companyId: STRING,
      companyName: STRING,
      country: STRING,
      attributes: ARRAY<STRING>
    >>
  >,
  countries ARRAY<STRING>,
  episodeInfo STRUCT<
    seriesTitleId: STRING,
    seasonNumber: INT,
    episodeNumber: INT
  >,
  seriesInfo STRUCT<
    startYear: INT,
    endYear: INT,
    episodeTitleIds: ARRAY<STRING>
  >,
  episodeTitleIds ARRAY<STRING>,
  genres ARRAY<STRUCT<
    text: STRING
  >>,
  image STRUCT<
    height: INT,
    id: STRING,
    url: STRING,
    width: INT
  >,
  keywords ARRAY<STRUCT<
    category: STRING,
    keyword: STRING,
    votes: INT
  >>,
  languages ARRAY<STRING>,
  locations STRUCT<
    scenes: ARRAY<STRUCT<
      place: STRING,
      attributes: ARRAY<STRING>
    >>
  >,
  movieConnections ARRAY<STRUCT<
    type: STRING,
    titleId: STRING,
    text: STRING
  >>,
  plot STRING,
  plotShort STRING,
  plotMedium STRING,
  plotLong STRING,
  releaseDates ARRAY<STRUCT<
    country: STRING,
    date: STRING,
    attributes: ARRAY<STRING>
  >>,
  productionStatus STRUCT<
    currentProductionStage: STRUCT<
      id: STRING,
      text: STRING
    >
  >,
  taglines ARRAY<STRING>,
  trivia ARRAY<STRUCT<
    interestScore: INT,
    isSpoiler: BOOLEAN,
    category: STRING,
    text: STRING
  >>,
  goofs ARRAY<STRUCT<
    interestScore: INT,
    isSpoiler: BOOLEAN,
    category: STRING,
    text: STRING
  >>,
  officialSiteLinks ARRAY<STRUCT<
    url: STRING,
    label: STRING
  >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/your-prefix/title_essential_v2/';

---

geo_star_meter_v1

CREATE EXTERNAL TABLE IF NOT EXISTS geo_star_meter_v1 (
  rank INT,
  nameId STRING,
  area STRING,
  date STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/your-prefix/geo_star_meter_v1/';

---

geo_title_meter_v1

CREATE EXTERNAL TABLE IF NOT EXISTS geo_title_meter_v1 (
  rank INT,
  titleId STRING,
  area STRING,
  date STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/your-prefix/geo_title_meter_v1/';

---

geo_tv_meter_v1

CREATE EXTERNAL TABLE IF NOT EXISTS geo_tv_meter_v1 (
  rank INT,
  titleId STRING,
  area STRING,
  date STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/your-prefix/geo_tv_meter_v1/';

---

geo_movie_meter_v1

CREATE EXTERNAL TABLE IF NOT EXISTS geo_movie_meter_v1 (
  rank INT,
  titleId STRING,
  area STRING,
  date STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'case.insensitive' = 'true'
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/your-prefix/geo_movie_meter_v1/';

---

Notes

• Replace s3://your-bucket/your-prefix/ with the actual S3 location from your Data Exchange export
• The ignore.malformed.json = 'true' SerDe property skips malformed lines rather than failing the query
• case.insensitive = 'true' allows field name matching regardless of case in the source JSON
• After creating tables, run MSCK REPAIR TABLE table_name; if data is partitioned, or test immediately with a SELECT * FROM table_name LIMIT 10;
• For the large title_essential_v2 table, consider partitioning by titleType or year for better query performance on filtered queries