DROP TABLE if exists teradata_binary_table_64kb;
DROP TABLE if exists teradata_binary_table_1mb;
DROP TABLE if exists teradata_binary_table_64kb_insert;
DROP TABLE if exists teradata_binary_table_1mb_insert;


CREATE TABLE `teradata_binary_table_64kb`(
  `test_tinyint` tinyint,
  `test_smallint` smallint,
  `test_int` int,
  `test_bigint` bigint,
  `test_double` double,
  `test_decimal` decimal(15,2),
  `test_date` date,
  `test_timestamp` timestamp,
  `test_char` char(1),
  `test_varchar` varchar(40),
  `test_binary` binary
 )
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.teradata.TeradataBinarySerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileOutputFormat'
TBLPROPERTIES (
  'teradata.timestamp.precision'='0',
  'teradata.char.charset'='LATIN',
  'teradata.row.length'='64KB'
);

CREATE TABLE `teradata_binary_table_1mb`(
  `test_tinyint` tinyint,
  `test_smallint` smallint,
  `test_int` int,
  `test_bigint` bigint,
  `test_double` double,
  `test_decimal` decimal(15,2),
  `test_date` date,
  `test_timestamp` timestamp,
  `test_char` char(1),
  `test_varchar` varchar(40),
  `test_binary` binary
 )
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.teradata.TeradataBinarySerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileOutputFormat'
TBLPROPERTIES (
  'teradata.timestamp.precision'='6',
  'teradata.char.charset'='UNICODE',
  'teradata.row.length'='1MB'
);

CREATE TABLE `teradata_binary_table_64kb_insert`(
  `test_tinyint` tinyint,
  `test_decimal` decimal(15,2),
  `test_date` date,
  `test_timestamp` timestamp
 )
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.teradata.TeradataBinarySerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileOutputFormat'
TBLPROPERTIES (
  'teradata.timestamp.precision'='0',
  'teradata.char.charset'='LATIN',
  'teradata.row.length'='64KB'
);

CREATE TABLE `teradata_binary_table_1mb_insert`(
  `test_tinyint` tinyint,
  `test_int` int
 )
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.teradata.TeradataBinarySerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileOutputFormat'
TBLPROPERTIES (
  'teradata.timestamp.precision'='6',
  'teradata.char.charset'='UNICODE',
  'teradata.row.length'='1MB'
);

LOAD DATA LOCAL INPATH '../../data/files/teradata_binary_file/teradata_binary_table.deflate' OVERWRITE INTO TABLE teradata_binary_table_64kb;
LOAD DATA LOCAL INPATH '../../data/files/teradata_binary_file/td_data_with_1mb_rowsize.teradata.gz' OVERWRITE INTO TABLE teradata_binary_table_1mb;

SELECT * from teradata_binary_table_64kb;
SELECT * from teradata_binary_table_1mb;

SELECT COUNT(*) FROM teradata_binary_table_64kb;
SELECT COUNT(*) FROM teradata_binary_table_1mb;

SELECT max(date_format(test_timestamp, 'y')) FROM teradata_binary_table_64kb;
SELECT max(date_format(test_date, 'y')) FROM teradata_binary_table_64kb;
SELECT max(Floor(test_decimal)) FROM teradata_binary_table_64kb;

SELECT max(date_format(test_timestamp, 'y')) FROM teradata_binary_table_1mb;
SELECT max(date_format(test_date, 'y')) FROM teradata_binary_table_1mb;
SELECT max(Floor(test_decimal)) FROM teradata_binary_table_1mb;

SELECT test_tinyint, MAX(test_decimal) FROM teradata_binary_table_64kb GROUP BY test_tinyint;
SELECT test_tinyint, MAX(test_decimal) FROM teradata_binary_table_1mb GROUP BY test_tinyint;

INSERT OVERWRITE TABLE teradata_binary_table_64kb_insert
SELECT test_tinyint, test_decimal, test_date, test_timestamp FROM teradata_binary_table_64kb;

INSERT OVERWRITE TABLE teradata_binary_table_1mb_insert
SELECT 1, 15;

DESC FORMATTED teradata_binary_table_64kb_insert;
DESC FORMATTED teradata_binary_table_1mb_insert;

DROP TABLE if exists teradata_binary_table_64kb;
DROP TABLE if exists teradata_binary_table_1mb;
DROP TABLE if exists teradata_binary_table_64kb_insert;
DROP TABLE if exists teradata_binary_table_1mb_insert;