HIVE TYPE
boolean | true,false,null 注意不是字符串,其他插入都得true |
|
|
tinyint | 1-byte signed integer, from -128 to 128 | 小数位会去除,越界NULL |
|
smallint | 2-byte signed integer, from -32,768 to 32,767 | 小数位会去除,越界NULL |
|
int | 4-byte signed integer, from -2,147,483,648 to 2,147,483,647 | 小数位会去除,越界NULL |
|
bigint | 8-byte signed integer, from -9,223,372,036,854,775,808 to 9… | 小数位会去除,越界NULL |
|
float | 4-byte single precision floating point number32位二进制 8位 | 单精度浮点,运算快 |
|
double | 8-byte double precision floating point number64位二进制 16位 | 双精度浮点,运算比上慢 |
|
decimal | 默认(10,0)最大(38)支持所有基本运算及函数 | 可同时用科学计数法,稍慢 |
|
string | can be expressed with either single quotes (') or double quotes ("). Hive uses C-style escaping within the strings. | 推荐使用 |
|
varchar | a length specifier (between 1 and 65355) | 部分函数需转string支持 |
|
char | maximum length is fixed at 255 | 部分函数需转string支持 |
|
timestamp | UNIX timestamp with optional nanosecond precision | yyyy-mm-dd hh:mm:ss[.f...] |
|
date | 0000-01-01 to 9999-12-31 | yyyy-mm-dd |
|
binary |
|
|
|
关于decimal精度问题,
当定义精度为decimal(4,2)时
插入 11.1 得 11.10
插入 '11.1'得 11.10 (可自动封装,但注意Spark需用cast,对照表在下面)
插入 234.1 得 NULL
插入 12.198 得 12.20
插入 '12.198'得 12.20
插入 19E+1 D
插入 '19E+1' 得 19.00
插入大于38的长度数字会报错
当定义精度为decimal时 (即默认为(10,0))
插入 234.9 得235
关于int精度问题
插入 234.9 得 234
关于运算
Select cast(0.1 as decimal)* cast(0.22 as decimal) fromtest_decimal limit 1; 得0
Select cast(0.1 asdecimal(10,4)) * cast(0.22 as decimal(10,4)) from test_decimal limit 1;得0.022
Select cast(0.1 asdecimal(10,4)) * cast(0.22 as decimal(10,1)) from test_decimal limit 1;得0.02
Select cast(0.1 asdecimal(10,1)) * cast(0.22 as decimal(10,1)) from test_decimal limit 1;得0.02
Select cast(0.1 as double) *cast(0.22 as double) from test_stringlimit 1; 得0.022000000000000002
Select cast(3347 as float) *cast(0.005 as float) from test_stringlimit 1; 得16.734999
Select '0.01' * '0.022' from test_string limit 1;得 2.1999999999999998E-4
标红的结果通通不准,亲知道该怎么写了吧。
String为啥能*因为这里涉及到下面的类型隐式转换,等会大家可以认真看下下面的转换吧
关于timestamp,date,string之间转换
insert into table test_timestamp values('2016-01-01 00:00:00'); 得 2016-01-01 00:00:00
insert into table test_timestamp values('2016-01-01 00:00:00.1'); 得 2016-01-01 00:00:00.1
insert into table test_timestamp values('2016-01-01'); 得 NULL
insert into table test_datevalues('2016-01-01'); 得2016-01-01
insert into table test_datevalues('2016-01-0100:00:00'); 得2016-01-01
Allowed Implicit Conversions
| void | boolean | tinyint | smallint | int | bigint | float | double | decimal | string | varchar | timestamp | date | binary |
void to | true | true | true | true | true | true | true | true | true | true | true | true | true | true |
boolean to | false | true | false | false | false | false | false | false | false | false | false | false | false | false |
tinyint to | false | false | true | true | true | true | true | true | true | true | true | false | false | false |
smallint to | false | false | false | true | true | true | true | true | true | true | true | false | false | false |
int to | false | false | false | false | true | true | true | true | true | true | true | false | false | false |
bigint to | false | false | false | false | false | true | true | true | true | true | true | false | false | false |
float to | false | false | false | false | false | false | true | true | true | true | true | false | false | false |
double to | false | false | false | false | false | false | false | true | true | true | true | false | false | false |
decimal to | false | false | false | false | false | false | false | false | true | true | true | false | false | false |
string to | false | false | false | false | false | false | false | true | true | true | true | false | false | false |
varchar to | false | false | false | false | false | false | false | true | true | true | true | false | false | false |
timestamp to | false | false | false | false | false | false | false | false | false | true | true | true | false | false |
date to | false | false | false | false | false | false | false | false | false | true | true | false | true | false |
binary to | false | false | false | false | false | false | false | false | false | false | false | false | false | true |