hive

CREATE EXTERNAL TABLE test( id int, user_id int, name string, amount double, status string, country string, event_time string, date string ) partitioned by ( year string, month string, day string ) ROW FORMAT serde 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 's3://test-119030453180/data/'

MSCK REPAIR TABLE test;

non-hive

일일이 추가

hive와 동일

ALTER TABLE test ADD PARTITION (year='2025', month='01', day='01') location 's3://test-119030453180/data/2025/01/01/';

파티션 프로젝션

방법 1

CREATE EXTERNAL TABLE test( id int, user_id int, name string, amount double, status string, country string, event_time string, date string ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 's3://test-119030453180/data/' tblproperties( 'projection.enable'='true', 'projection.year.type'='integer', 'projection.month.type'='integer', 'projection.day.type'='integer', 'projection.year.FORMAT'='yyyy', 'projection.month.FORMAT'='MM', 'projection.day.FORMAT'='dd', 'projection.year.range'='2025, 2026', 'projection.month.range'='01, 12', 'projection.day.range'='01, 31', 'storage.location.template'='s3://test-119030453180/data/${year}/${month}/${day}' )

방법2(위 안되면)

CREATE EXTERNAL TABLE logs_json ( id INT, user_id INT, name STRING, amount DOUBLE, status STRING, country STRING, event_time STRING, date STRING ) PARTITIONED BY ( year STRING, month STRING, day STRING ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE LOCATION 's3://test-119030453180/data/' TBLPROPERTIES ( 'projection.enabled'='true', 'projection.year.type'='integer', 'projection.year.range'='2025,2026', 'projection.year.FORMAT'='yyyy', 'projection.month.type'='integer', 'projection.month.range'='1,12', 'projection.month.FORMAT'='MM', 'projection.day.type'='integer', 'projection.day.range'='1,31', 'projection.day.FORMAT'='dd', 'storage.location.template'='s3://test-119030453180/data/${year}/${month}/${day}/' );

방법3(안되면)

CREATE EXTERNAL TABLE test ( id INT, user_id INT, name STRING, amount DOUBLE, status STRING, country STRING, event_time STRING, date STRING ) PARTITIONED BY ( year STRING, month STRING, day STRING ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE LOCATION 's3://test-119030453180/data/' TBLPROPERTIES ( 'projection.enabled'='true', 'projection.year.type'='integer', 'projection.year.range'='2025,2026', 'projection.year.digits'='4', 'projection.month.type'='integer', 'projection.month.range'='1,12', 'projection.month.digits'='2', 'projection.day.type'='integer', 'projection.day.range'='1,31', 'projection.day.digits'='2', 'storage.location.template'='s3://test-119030453180/data/${year}/${month}/${day}/' );