2과제.pdf

-- hive가 된 버전

-- 1번
create database sexy;

CREATE EXTERNAL TABLE sexy_table(
  id integer,
  user_id integer,
  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'
LOCATION 's3://wsi-athena-test/data/

--2번
MSCK REPAIR TABLE sexy_table;

--3번
SELECT * FROM "testing"."sexy_table" WHERE year = '2025' AND month = '03' AND day = '27' limit 10;

-- hive가 안된 버전

-- 1번
CREATE EXTERNAL TABLE sexy_table(
  id integer,
  user_id integer,
  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'
LOCATION 's3://wsi-athena-test/data/'

--2번
ALTER TABLE sexy_table ADD PARTITION (year='2025', month='01', day='01') location 's3://wsi-athena-test/data/2025/01/01/'

--3번
MSCK REPAIR TABLE sexy_table;

--3번
SELECT * FROM "testing"."sexy_table" WHERE year = '2025' AND month = '03' AND day = '27' limit 10;

-- hive가 안된 버전 파티션 프로젝션

-- 1번
CREATE EXTERNAL TABLE sexy_table (
  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://wsi-athena-test/data/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.year.type'='integer',
  'projection.year.range'='2025,2025',
  '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://wsi-athena-test/data/${year}/${month}/${day}/'
);

-- or

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}/'
);

-- 2번
MSCK REPAIR TABLE sexy_table;

--4번
SELECT * FROM "testing"."sexy_table" WHERE year = '2025' AND month = '03' AND day = '27' limit 10;