Updating int partitions to yyyy/mm/dd/hh in Athena

Question

I currently have a datalake setup in s3 with integers in a table setup. Right now I am having trouble querying days of data between months at this point and have been having to pass in individual year, month, and day in the where statement.

where
  (year = 2022 and month = 8 and day = 31) or 
  (year = 2022 and month = 9 and day = 1)

I want to be able to pass in between date('yyyy/mm/dd') and date('yyyy/mm/dd') instead to make my queries work better.

I have setup another database where I am trying to build a better way of doing this by using projections and dates. I have built this table with some input from the Kinesis docs. Link

CREATE EXTERNAL TABLE `vehicles`(
  `objectid` bigint, 
  `trip_id` string, 
  `vehicle_id` string, 
  `route_id` string, 
  `direction_id` bigint, 
  `timestamp` bigint, 
  `delay` bigint, 
  `delay_type` string, 
  `headsign` string, 
  `route_short_name` string, 
  `route_long_name` string, 
  `longitude` double, 
  `latitude` double)
PARTITIONED BY ( 
  `agency` string,
  `year` int,
  `month` int,
  `day` int,
  `hour` int)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://transitnode/vehicles/'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='transit_node_testing5', 
  'areColumnsQuoted'='false', 
  'averageRecordSize'='115', 
  'classification'='csv', 
  'columnsOrdered'='true', 
  'compressionType'='none', 
  'delimiter'=',', 
  'exclusions'='[\"s3://transitnode/athena/*\"]', 
  'objectCount'='1640', 
  'recordCount'='144257', 
  'sizeKey'='17310018', 
  
    'projection.enabled' = 'true',

    'projection.agency.type' = 'injected',
    'projection.hour.type' = 'int',
    'projection.hour.interval' = '1',
    'projection.hour.unit' = 'HOURS',

    'projection.day.type' = 'int',
    'projection.day.interval' = '1',
    'projection.day.unit' = 'DAYS',
    
    'projection.month.type' = 'int',
    'projection.month.interval' = '1',
    'projection.month.unit' = 'MONTHS',
        
    'projection.year.type' = 'int',
    'projection.year.interval' = '1',
    'projection.year.unit' = 'YEARS',

    'storage.location.template' = 's3://bucket/vehicles/${agency}/${year}/${month}/${day}/${hour}/',
    
    'skip.header.line.count'='1')

John Rotenstein · Answer

The easiest way would be to create a View that includes a timestamp field constructed from the year/moth/day/hour fields. You can then use standard date/timestamp comparisons on the field.

Alternatively, you could convert it into a text field (typically yyyy-mm-dd hh:min) -- this allows easy comparisons, but does not support math (eg adding 7 days).

It seems that the only way in Amazon Athena to convert the individual int fields to a string or date field is to use cast().

Here's an example:

with data as (
  select
    2022 as year,
    8 as month,
    31 as day
)

select
  *,
  concat(cast(year as varchar), '-' , cast(month as varchar) , '-', cast(day as varchar)) as date_string,
  date(concat(cast(year as varchar), '-' , cast(month as varchar) , '-', cast(day as varchar))) as date_field
from data

Updating int partitions to yyyy/mm/dd/hh in Athena

1 Answers