hive —— 数据类型

hive —— 数据类型

详细请看
hive官方手册：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-TimestampstimestampTimestamps

hive的数据类型有五大类型:

• primitive_type ：原始类型
• array_type ：数组
• map_type ：map
• struct_type ：结构体
• union_type ：联合体

一、primitive_type原始类型

以下是hive的原始数据类型及格式

类型	描述	示例
TINYINT	一字节整数， -128 ~ 127	12
SMALLINT	二字节整数，-32768 ~ 32767	255
INT/INTEGER	4字节整数 -2,147,483,648 ~ 2,147,483,647	2555
BIGINT	4字节整数，-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	-250 000 000 000
FLOAT	4字节单精度小数	3.1415
DOUBLE	8字节双精度小数	3.141529
DECIMAL	任意数字	10
STRING	字符串	“abc”
VARCHAR	字符串，字符串长度只能为1~65355	“abc”
CHAR	字符串，字符串长度只能为1~255	“abc”
TIMESTAMP	时间戳，格式为yyyy-mm-dd HH:mm:ss	2019-2-28 13:25:25
DATE	日期，格式为yyyy-mm-dd	2019-2-28
BOOLEAN	布尔类型	TRUE/FALSE
BINARY	字节序列

DECIMAL

这里我们对DECIMAL类型做两点说明：
1）DECIMAL(9,8)代表最多9位数字，后8位是小数。此时也就是说，小数点前最多有1位数字，如果超过一位则会变成null。
2）如果不指定参数，那么默认是DECIMAL(10,0)，即没有小数位，此时0.82会变成1。

注：大于 BIGINT 的整型文字必须使用 Decimal(38,0) 处理。
例子：

select CAST(18446744073709001000BD AS DECIMAL(38,0)) from my_table limit 1;

日期类型

1、Timestamps 时间戳

时间戳是数据库常用的存放日期的形式之一，表示从 UTC 时间’1970-01-01 00:00:00’开始到现在的秒数。

2001-09-09 09:46:40 ~ 2286-11-21 01:46:40 之间的时间戳，都是10位数。

因此，我们这个时代使用的时间戳一般都是10位。如果遇到13位的时间戳，则为毫秒数。

时间戳

当从文件中load数据到Timestamps类型时，文本文件中的时间戳必须使用：yyyy-mm-dd hh:mm:ss[.f…] 格式，不然数据会变成NULL。精度只能到纳秒，即yyyy-mm-dd hh:mm:ss.ssssss，
小数点后六位，超过纳秒也会变成NULL。

hive支持从以下类型转换成Timestamps类型：
CAST(<timestamp/date> AS <varchar/char/string> [FORMAT < template>])
CAST(<varchar/char/string> AS <timestamp/date> [FORMAT < template>])

整数数字类型：解释为以秒为单位的 UNIX 时间戳
浮点数字类型：解释为具有十进制精度的 UNIX 时间戳（以秒为单位）
字符串：符合 JDBC 的 java.sql.Timestamp 格式“YYYY-MM-DD HH:MM:SS.ffffffffff”（9 位小数精度）

还有hive自带的udf转换：
1、String to Timestamps：
unix_timestamps(string date, string format)
注：如果format为空，date的格式必须为yyyy-MM-dd HH:mm:ss。

2、Timestamps to String：
from_timestamps(int/bigint timestamp ,string format)
注：前面的int/bigint最大只能是10位数，即到秒。如果是13位（毫秒），则需/1000，或者将前面10位分割出来再转换。

2、date类型

DATE 值描述了特定的年/月/日，格式为 YYYY-MM-DD。 例如，日期“2013-01-01”。 日期类型没有时间组件。 Date 类型支持的值范围是 0000-01-01 到 9999-12-31，这取决于原始 Java Date 类型的支持。

转换函数：

cast(date as timestamp)	基于本地时区，生成对应于日期值的年/月/日的午夜的时间戳值。
cast(string as date)	如果字符串的格式为“YYYY-MM-DD”，则返回对应于该年/月/日的日期值。如果字符串值与此格式不匹配，则返回 NULL。
cast(date as date)	相同的日期值
cast(timestamp as date)	时间戳的年/月/日是根据本地时区确定的，并作为日期值返回。
cast(date as string)	日期表示的年/月/日被格式化为“YYYY-MM-DD”形式的字符串。

二、array_type 数组类型

格式：ARRAY(数据类型)
要配合：collection items terminated by ‘分割符’;

例如：

--数据： a,d：e: f,g

create table [if no exists] example1 
(
Field1 VARCHAR,
Field2 ARRAY(VARCHAR),
Field3 VARCHAR
)
row format delimited fields terminated by ','  --定义字段间的分割符
collection items terminated by ':'  --定义数组数据间的分割符

--查询
select Field1,Field2[0] from example1 ;

select Field1,Field2 from example1 where array_contains(Field2 ,'d');

select Field1,size(Field2) from example1 ;

三、map_type 字典类型

格式：map< 原始数据类型，数据类型 >
要配合：collection items terminated by ‘分割符’ 和map keys terminated by '分割符’

例如：

--数据： a,d:d1;e:e1;f:f1,g

create table [if no exists] example2 
(
Field1 VARCHAR,
Field2 MAP(VARCHAR,VARCHAR),
Field3 VARCHAR
)
row format delimited fields terminated by ','  --定义字段间的分割符
collection items terminated by ';'  --定义集合间的分割符
map keys terminated by ':'  --定义key value之间的分割符
--查询
-- 取map字段的指定key的值
select Field1,Field2['d'] as father from example2 ;
-- 取map字段的所有key
-- 得到的是数组
select Field1,map_keys(Field2) as relation from example2 ;
-- 取map字段的所有value
-- 得到的是数组
select Field1,map_values(Field2) from example2 ;

select Field1,tmp 
from 
(select id,name,Field2['d'] as tmp from t_person) example2 
where tmp is not null;

四 struct_type 结构体

格式：struct<名字：数据类型,名字：数据类型,…>
配合：collection items terminated by '分割符’

例如

1,zhangsan,18:male:beijing
2,lisi,28:female:shanghai

create table t_person_struct(id int,name string,info struct<age:int,sex:string,addr:string>)
row format delimited fields terminated by ','
collection items terminated by ':';

select id,name,info.age from t_person_struct;

五、union_type

• uniontype可以理解为泛型
• 同一时刻同一地点只有联合体中的一个元素生效
• uniontype中的元素共享内存
• 可以通过create_union内置函数创建uniontype：create_union(tag, val1, val2) tag是数字，0开始，必须小于后面参数的个数
• 插入uniontype数据，通过这种方式只能插入只有一个元素的uniontype，包含多个会提示跟表中的字段类型不一致，这个是坑的地方
• 默认tag和数据使用B分隔，列数据使用C分隔，map中key和value使用D分隔（ ^ B ascii码 2，^ C ascii码 3，^D ascii码 4，分隔符是通过vim查看到的【vim中输入这些特殊字符 ctrl + v + 相应的字母】）
• 在uniontype字段中自定义分隔符的含义变了：

分隔代码	含义
collection items terminated by ‘,’	tag和数据之间使用英文逗号分隔
map keys terminated by ‘:’	复合类型数据元素之间使用英文冒号分隔
–	map中的key和value暂时没找到可以配置的代码

例如：

# 创建表
create table union_testnew(
  foo uniontype<int, double, string, array<string>, map<string, string>>
)
row format delimited
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile;

# 数据准备
[root@master wadeyu]# vim union_test.log 
  1 0,1
  2 1,3.0
  3 2,world
  4 3,wade:tom:polly
  5 4,k1^Dv1:k2^Dv2
  
# 导入数据
hive (badou)> load data local inpath './union_test.log' overwrite into table union_testnew;

# 查询数据
hive (badou)> select * from union_testnew;
OK
union_testnew.foo
{0:1}
{1:3.0}
{2:"world"}
{3:["wade","tom","polly"]}
{4:{"k1":"v1","k2":"v2"}}
Time taken: 0.225 seconds, Fetched: 5 row(s)

六、数据类型的转换