drop table if exists date_serde_regex;
drop table date_serde_lb;
drop table date_serde_ls;
drop table date_serde_c;
drop table date_serde_lbc;
drop table date_serde_orc;


--
-- RegexSerDe
--
create table date_serde_regex (
  ORIGIN_CITY_NAME string,
  DEST_CITY_NAME string,
  FL_DATE date,
  ARR_DELAY float,
  FL_NUM int
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties (
  "input.regex" = "([^
]*)
([^
]*)
([^
]*)
([^
]*)
([0-9]*)"
)
stored as textfile;

load data local inpath '../../data/files/flights_tiny.txt.1' overwrite into table date_serde_regex;

select * from date_serde_regex;
select fl_date, count(*) from date_serde_regex group by fl_date;

--
-- LazyBinary
--
create table date_serde_lb (
  c1 date,
  c2 int
);
alter table date_serde_lb set serde 'org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe';

insert overwrite table date_serde_lb 
  select fl_date, fl_num from date_serde_regex limit 1;

select * from date_serde_lb;
select c1, sum(c2) from date_serde_lb group by c1;

--
-- LazySimple
--
create table date_serde_ls (
  c1 date,
  c2 int
);
alter table date_serde_ls set serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe';

insert overwrite table date_serde_ls 
  select c1, c2 from date_serde_lb limit 1;

select * from date_serde_ls;
select c1, sum(c2) from date_serde_ls group by c1;

--
-- Columnar
--
create table date_serde_c (
  c1 date,
  c2 int
) stored as rcfile;
alter table date_serde_c set serde 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe';

insert overwrite table date_serde_c 
  select c1, c2 from date_serde_ls limit 1;

select * from date_serde_c;
select c1, sum(c2) from date_serde_c group by c1;

--
-- LazyBinaryColumnar
--
create table date_serde_lbc (
  c1 date,
  c2 int
) stored as rcfile;
alter table date_serde_lbc set serde 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';

insert overwrite table date_serde_lbc 
  select c1, c2 from date_serde_c limit 1;

select * from date_serde_lbc;
select c1, sum(c2) from date_serde_lbc group by c1;

--
-- ORC
--
create table date_serde_orc (
  c1 date,
  c2 int
) stored as orc;
alter table date_serde_orc set serde 'org.apache.hadoop.hive.ql.io.orc.OrcSerde';

insert overwrite table date_serde_orc 
  select c1, c2 from date_serde_lbc limit 1;

select * from date_serde_orc;
select c1, sum(c2) from date_serde_orc group by c1;



drop table date_serde_regex;
drop table date_serde_lb;
drop table date_serde_ls;
drop table date_serde_c;
drop table date_serde_lbc;
drop table date_serde_orc;