| set hive.mapred.mode=nonstrict; |
| set hive.explain.user=false; |
| SET hive.vectorized.execution.enabled=true; |
| set hive.fetch.task.conversion=none; |
| |
| DROP TABLE IF EXISTS DECIMAL_PRECISION_txt; |
| DROP TABLE IF EXISTS DECIMAL_PRECISION; |
| DROP TABLE IF EXISTS DECIMAL_PRECISION_txt_small; |
| |
| CREATE TABLE DECIMAL_PRECISION_txt(`dec` decimal(20,10)) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY ' ' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/kv8.txt' INTO TABLE DECIMAL_PRECISION_txt; |
| |
| CREATE TABLE DECIMAL_PRECISION(`dec` decimal(20,10)) |
| STORED AS ORC; |
| |
| INSERT OVERWRITE TABLE DECIMAL_PRECISION SELECT * FROM DECIMAL_PRECISION_txt; |
| |
| SELECT * FROM DECIMAL_PRECISION ORDER BY `dec`; |
| |
| SELECT `dec`, `dec` + 1, `dec` - 1 FROM DECIMAL_PRECISION ORDER BY `dec`; |
| SELECT `dec`, `dec` * 2, `dec` / 3 FROM DECIMAL_PRECISION ORDER BY `dec`; |
| SELECT `dec`, `dec` / 9 FROM DECIMAL_PRECISION ORDER BY `dec`; |
| SELECT `dec`, `dec` / 27 FROM DECIMAL_PRECISION ORDER BY `dec`; |
| SELECT `dec`, `dec` * `dec` FROM DECIMAL_PRECISION ORDER BY `dec`; |
| |
| EXPLAIN VECTORIZATION DETAIL SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION; |
| SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION; |
| |
| SELECT `dec` * cast('12345678901234567890.12345678' as decimal(38,18)) FROM DECIMAL_PRECISION LIMIT 1; |
| SELECT * from DECIMAL_PRECISION WHERE `dec` > cast('1234567890123456789012345678.12345678' as decimal(38,18)) LIMIT 1; |
| SELECT `dec` * 12345678901234567890.12345678 FROM DECIMAL_PRECISION LIMIT 1; |
| |
| SELECT MIN(cast('12345678901234567890.12345678' as decimal(38,18))) FROM DECIMAL_PRECISION; |
| SELECT COUNT(cast('12345678901234567890.12345678' as decimal(38,18))) FROM DECIMAL_PRECISION; |
| |
| -- DECIMAL_64 |
| |
| CREATE TABLE DECIMAL_PRECISION_txt_small(`dec` decimal(20,10)) |
| ROW FORMAT DELIMITED |
| FIELDS TERMINATED BY ' ' |
| STORED AS TEXTFILE; |
| |
| LOAD DATA LOCAL INPATH '../../data/files/kv8.txt' INTO TABLE DECIMAL_PRECISION_txt_small; |
| |
| SELECT `dec`, `dec` + 1, `dec` - 1 FROM DECIMAL_PRECISION_txt_small ORDER BY `dec`; |
| SELECT `dec`, `dec` * 2, `dec` / 3 FROM DECIMAL_PRECISION_txt_small ORDER BY `dec`; |
| SELECT `dec`, `dec` / 9 FROM DECIMAL_PRECISION_txt_small ORDER BY `dec`; |
| SELECT `dec`, `dec` / 27 FROM DECIMAL_PRECISION_txt_small ORDER BY `dec`; |
| SELECT `dec`, `dec` * `dec` FROM DECIMAL_PRECISION_txt_small ORDER BY `dec`; |
| |
| EXPLAIN VECTORIZATION DETAIL SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION_txt_small; |
| SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION_txt_small; |
| |
| SELECT `dec` * cast('12345678901234567890.12345678' as decimal(38,18)) FROM DECIMAL_PRECISION_txt_small LIMIT 1; |
| SELECT * from DECIMAL_PRECISION_txt_small WHERE `dec` > cast('1234567890123456789012345678.12345678' as decimal(38,18)) LIMIT 1; |
| SELECT `dec` * 12345678901234567890.12345678 FROM DECIMAL_PRECISION_txt_small LIMIT 1; |
| |
| SELECT MIN(cast('12345678901234567890.12345678' as decimal(38,18))) FROM DECIMAL_PRECISION_txt_small; |
| SELECT COUNT(cast('12345678901234567890.12345678' as decimal(38,18))) FROM DECIMAL_PRECISION_txt_small; |
| |
| DROP TABLE DECIMAL_PRECISION_txt; |
| DROP TABLE DECIMAL_PRECISION; |
| DROP TABLE DECIMAL_PRECISION_txt_small; |