--! qt:dataset:src

set hive.vectorized.execution.enabled=false;
set hive.mapred.mode=nonstrict;

-- SORT_QUERY_RESULTS

DROP TABLE orc_create;
DROP TABLE orc_create_complex_n1;
DROP TABLE orc_create_staging_n1;
DROP TABLE orc_create_people_staging;
DROP TABLE orc_create_people;
DROP TABLE if exists orc_create_cprl;

CREATE TABLE orc_create_staging_n1 (
  str STRING,
  mp  MAP<STRING,STRING>,
  lst ARRAY<STRING>,
  strct STRUCT<A:STRING,B:STRING>
) ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '|'
    COLLECTION ITEMS TERMINATED BY ','
    MAP KEYS TERMINATED BY ':';

DESCRIBE FORMATTED orc_create_staging_n1;

CREATE TABLE orc_create (key INT, value STRING)
   PARTITIONED BY (ds string)
   STORED AS ORC;

DESCRIBE FORMATTED orc_create;

DROP TABLE orc_create;

CREATE TABLE orc_create (key INT, value STRING)
   PARTITIONED BY (ds string);

DESCRIBE FORMATTED orc_create;

ALTER TABLE orc_create SET FILEFORMAT ORC;

DESCRIBE FORMATTED orc_create;

DROP TABLE orc_create;

set hive.default.fileformat=orc;

CREATE TABLE orc_create (key INT, value STRING)
   PARTITIONED BY (ds string);

set hive.default.fileformat=TextFile;

DESCRIBE FORMATTED orc_create;

CREATE TABLE orc_create_complex_n1 (
  str STRING,
  mp  MAP<STRING,STRING>,
  lst ARRAY<STRING>,
  strct STRUCT<A:STRING,B:STRING>
) STORED AS ORC;

DESCRIBE FORMATTED orc_create_complex_n1;

LOAD DATA LOCAL INPATH '../../data/files/orc_create.txt' OVERWRITE INTO TABLE orc_create_staging_n1;

SELECT * from orc_create_staging_n1;

INSERT OVERWRITE TABLE orc_create_complex_n1 SELECT * FROM orc_create_staging_n1;

SELECT * from orc_create_complex_n1;
SELECT str from orc_create_complex_n1;
SELECT mp from orc_create_complex_n1;
SELECT lst from orc_create_complex_n1;
SELECT strct from orc_create_complex_n1;

CREATE TABLE orc_create_people_staging (
  id int,
  first_name string,
  last_name string,
  address string,
  salary decimal(38,0),
  start_date timestamp,
  state string);

LOAD DATA LOCAL INPATH '../../data/files/orc_create_people.txt'
  OVERWRITE INTO TABLE orc_create_people_staging;

CREATE TABLE orc_create_people (
  id int,
  first_name string,
  last_name string,
  address string,
  salary decimal(38,0),
  start_date timestamp)
PARTITIONED BY (state string)
STORED AS orc;


INSERT OVERWRITE TABLE orc_create_people PARTITION (state)
  SELECT * FROM orc_create_people_staging;

SET hive.optimize.index.filter=true;
-- test predicate push down with partition pruning
SELECT COUNT(*) FROM orc_create_people where id < 10 and state = 'Ca';

-- test predicate push down
SELECT COUNT(*) FROM orc_create_people where id = 50;
SELECT COUNT(*) FROM orc_create_people where id between 10 and 20;
SELECT COUNT(*) FROM orc_create_people where id > 10 and id < 100;
SELECT COUNT(*) FROM orc_create_people where (id + 1) = 20;
SELECT COUNT(*) FROM orc_create_people where (id + 10) < 200;
SELECT COUNT(*) FROM orc_create_people where id < 30  or first_name = "Rafael";
SELECT COUNT(*) FROM orc_create_people 
   where length(substr(first_name, 1, 2)) <= 2 and last_name like '%';
SELECT COUNT(*) FROM orc_create_people where salary = 200.00;
SELECT COUNT(*) FROM orc_create_people WHERE start_date IS NULL;
SELECT COUNT(*) FROM orc_create_people WHERE YEAR(start_date) = 2014;

-- test predicate push down with partition pruning
SELECT COUNT(*) FROM orc_create_people where salary = 200.00 and state = 'Ca';

-- test predicate push down with no column projection
SELECT id, first_name, last_name, address
  FROM orc_create_people WHERE id > 90;

-- test create with lower case compression method.
CREATE TABLE orc_create_cprl (id int)
PARTITIONED BY (cdate date)
STORED AS ORC
TBLPROPERTIES (
'orc.compress'='snappy');
INSERT OVERWRITE table orc_create_cprl PARTITION (cdate = '2015-02-03')
SELECT 1 from src limit 1;
SELECT * from orc_create_cprl;

DROP TABLE orc_create;
DROP TABLE orc_create_complex_n1;
DROP TABLE orc_create_staging_n1;
DROP TABLE orc_create_people_staging;
DROP TABLE orc_create_people;
DROP TABLE orc_create_cprl;