create table:
CREATE EXTERNAL TABLE IF NOT EXISTS host_log_syslog (
TIME string,
host string,
ident string,
message string,
ec2_instance_id string,
)
COMMENT 'host_log_syslog table'
PARTITIONED BY (
log_id string,
YEAR string,
MONTH string,
DAY string,
HOUR string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://example-host-log/host'
TBLPROPERTIES(
'projection.enabled' = 'true',
"projection.log_id.type" = "injected",
"projection.year.type" = 'date',
"projection.year.range" = 'NOW-1YEARS,NOW',
"projection.year.format" = 'yyyy',
"projection.year.interval" = '1' ,
"projection.year.interval.unit" = 'YEARS',
'projection.month.type'='integer',
'projection.month.range'='1,12',
'projection.month.interval'='1',
'projection.month.digits'='2',
'projection.day.type'='integer',
'projection.day.range'='1,31',
'projection.day.interval'='1',
'projection.day.digits'='2',
'projection.hour.type'='integer',
'projection.hour.range'='0,23',
'projection.hour.interval'='1',
'projection.hour.digits'='2',
'storage.location.template' = 's3://example-host-log/host/log_id=${log_id}/year=${year}/month=${month}/day=${day}/hour=${hour}'
);