--! qt:dataset:src
--! qt:dataset:alltypesorc
set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
set hive.compute.query.using.stats=true;

drop table if exists acid_ivot_stage;
create table acid_ivot_stage(
    ctinyint TINYINT,
    csmallint SMALLINT,
    cint INT,
    cbigint BIGINT,
    cfloat FLOAT,
    cdouble DOUBLE,
    cstring1 STRING,
    cstring2 STRING,
    ctimestamp1 TIMESTAMP,
    ctimestamp2 TIMESTAMP,
    cboolean1 BOOLEAN,
    cboolean2 BOOLEAN) stored as orc;

LOAD DATA LOCAL INPATH "../../data/files/alltypesorc" into table acid_ivot_stage;

create table acid_ivot(
    ctinyint TINYINT,
    csmallint SMALLINT,
    cint INT,
    cbigint BIGINT,
    cfloat FLOAT,
    cdouble DOUBLE,
    cstring1 STRING,
    cstring2 STRING,
    ctimestamp1 TIMESTAMP,
    ctimestamp2 TIMESTAMP,
    cboolean1 BOOLEAN,
    cboolean2 BOOLEAN) clustered by (cint) into 1 buckets stored as orc TBLPROPERTIES ('transactional'='true');

desc formatted acid_ivot;

insert into acid_ivot select * from acid_ivot_stage;

desc formatted acid_ivot;

explain select count(*) from acid_ivot;

select count(*) from acid_ivot;

insert into table acid_ivot values
        (1, 2, 3, 4, 3.14, 2.34, 'fred', 'bob', '2014-09-01 10:34:23.111', '1944-06-06 06:00:00', true, true),
        (111, 222, 3333, 444, 13.14, 10239302.34239320, 'fred', 'bob', '2014-09-01 10:34:23.111', '1944-06-06 06:00:00', true, true);

select count(*) from acid_ivot;

drop table acid_ivot;

create table acid_ivot(
    ctinyint TINYINT,
    csmallint SMALLINT,
    cint INT,
    cbigint BIGINT,
    cfloat FLOAT,
    cdouble DOUBLE,
    cstring1 STRING,
    cstring2 STRING,
    ctimestamp1 TIMESTAMP,
    ctimestamp2 TIMESTAMP,
    cboolean1 BOOLEAN,
    cboolean2 BOOLEAN) clustered by (cint) into 1 buckets stored as orc TBLPROPERTIES ('transactional'='true');

insert into table acid_ivot values
        (1, 2, 3, 4, 3.14, 2.34, 'fred', 'bob', '2014-09-01 10:34:23.111', '1944-06-06 06:00:00', true, true),
        (111, 222, 3333, 444, 13.14, 10239302.34239320, 'fred', 'bob', '2014-09-01 10:34:23.111', '1944-06-06 06:00:00', true, true);

desc formatted acid_ivot;

explain select count(*) from acid_ivot;

select count(*) from acid_ivot;

insert into table acid_ivot values
        (1, 2, 3, 4, 3.14, 2.34, 'fred', 'bob', '2014-09-01 10:34:23.111', '1944-06-06 06:00:00', true, true),
        (111, 222, 3333, 444, 13.14, 10239302.34239320, 'fred', 'bob', '2014-09-01 10:34:23.111', '1944-06-06 06:00:00', true, true);

desc formatted acid_ivot;

explain select count(*) from acid_ivot;

select count(*) from acid_ivot;

insert into acid_ivot select * from acid_ivot_stage;

desc formatted acid_ivot;

explain select count(*) from acid_ivot;

drop table acid_ivot;

create table acid_ivot like src;

desc formatted acid_ivot;

insert overwrite table acid_ivot select * from src;

desc formatted acid_ivot;

explain select count(*) from acid_ivot;

select count(*) from acid_ivot;

CREATE TABLE sp (key STRING COMMENT 'default', value STRING COMMENT 'default')
PARTITIONED BY (ds STRING, hr STRING)
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH "../../data/files/kv1.txt"
OVERWRITE INTO TABLE sp PARTITION (ds="2008-04-08", hr="11");

desc formatted sp PARTITION (ds="2008-04-08", hr="11");

explain select count(*) from sp where ds="2008-04-08" and hr="11";

select count(*) from sp where ds="2008-04-08" and hr="11";

insert into table sp PARTITION (ds="2008-04-08", hr="11") values
        ('1', '2'), ('3', '4');

desc formatted sp PARTITION (ds="2008-04-08", hr="11");

analyze table sp PARTITION (ds="2008-04-08", hr="11") compute statistics;

desc formatted sp PARTITION (ds="2008-04-08", hr="11");

explain select count(*) from sp where ds="2008-04-08" and hr="11";

select count(*) from sp where ds="2008-04-08" and hr="11";