Saturday, December 23, 2017

Hive Data Type Conversion and Truncation

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> create table datatypedemo(i tinyint, s string, c char(5));
No rows affected (0.461 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> desc datatypedemo;
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| i         | tinyint    |          |
| s         | string     |          |
| c         | char(5)    |          |
+-----------+------------+----------+--+
3 rows selected (0.085 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into datatypedemo values(1,'abcdefg','abc');
No rows affected (15.008 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into datatypedemo values(2,'abc','abcdefg');
No rows affected (16.438 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into datatypedemo values(3,'abcdefgh','abcdefgh');
No rows affected (16.619 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into datatypedemo values(400,'abcdefghi','abcdefghi');
No rows affected (15.477 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from datatypedemo;
+-----------------+-----------------+-----------------+--+
| datatypedemo.i  | datatypedemo.s  | datatypedemo.c  |
+-----------------+-----------------+-----------------+--+
| 1               | abcdefg         | abc             |
| 2               | abc             | abcde           |
| 3               | abcdefgh        | abcde           |
| NULL            | abcdefghi       | abcde           |
+-----------------+-----------------+-----------------+--+
4 rows selected (0.869 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column i i int;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column s s varchar(5);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column c c string;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from datatypedemo;

+-----------------+-----------------+-----------------+--+
| datatypedemo.i  | datatypedemo.s  | datatypedemo.c  |
+-----------------+-----------------+-----------------+--+
| 1               | abcde           | abc             |
| 2               | abc             | abcde           |
| 3               | abcde           | abcde           |
| NULL            | abcde           | abcde           |
+-----------------+-----------------+-----------------+--+
4 rows selected (0.114 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into datatypedemo values (500,'abcdefg','abcdefg');
No rows affected (15.542 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from datatypedemo;
+-----------------+-----------------+-----------------+--+
| datatypedemo.i  | datatypedemo.s  | datatypedemo.c  |
+-----------------+-----------------+-----------------+--+
| 1               | abcde           | abc             |
| 2               | abc             | abcde           |
| 3               | abcde           | abcde           |
| NULL            | abcde           | abcde           |
| 500             | abcde           | abcdefg         |
+-----------------+-----------------+-----------------+--+
5 rows selected (0.124 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column i i tinyint;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column c c char(5);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from datatypedemo;
+-----------------+-----------------+-----------------+--+
| datatypedemo.i  | datatypedemo.s  | datatypedemo.c  |
+-----------------+-----------------+-----------------+--+
| 1               | abcdefg         | abc             |
| 2               | abc             | abcde           |
| 3               | abcdefgh        | abcde           |
| NULL            | abcdefghi       | abcde           |
| NULL            | abcde           | abcde           |
+-----------------+-----------------+-----------------+--+
5 rows selected (0.136 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column i i int;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column c c char(10);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from datatypedemo;
+-----------------+-----------------+-----------------+--+
| datatypedemo.i  | datatypedemo.s  | datatypedemo.c  |
+-----------------+-----------------+-----------------+--+
| 1               | abcdefg         | abc             |
| 2               | abc             | abcde           |
| 3               | abcdefgh        | abcde           |
| NULL            | abcdefghi       | abcde           |
| 500             | abcde           | abcdefg         |
+-----------------+-----------------+-----------------+--+
5 rows selected (0.115 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column c c char(256);
Error: Error while compiling statement: FAILED: RuntimeException Char length 256 out of allowed range [1, 255] (state=42000,code=40000)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table datatypedemo change column c c varchar(65536);
Error: Error while compiling statement: FAILED: RuntimeException Varchar length 65536 out of allowed range [1, 65535] (state=42000,code=40000)

Refernce URLs:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-char