datetime hive 数据类型 hive日期数据类型

目录

1.1基本类型

1.1.1数字类型

1.1.2字符串类型

1.1.3 日期与时间戳

1.2 复杂类型

---

hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低，可以通过类似SQL语句实现快速MapReduce统计，使MapReduce变得更加简单，而不必开发专门的MapReduce应用程序。hive是十分适合数据仓库的统计分析和Windows注册表文件。

最近会给大家分享下自己之前整理的hive笔记, 如有错误还望指出;

hive官网: https://hive.apache.org/

wiki上的hive指南: https://cwiki.apache.org/confluence/display/Hive/GettingStarted

 

一. 数据类型

1.1基本类型

原生数据类型	大小	范围	示例
tinyint	1byte	-128 ~ 127	100Y
smallint	2byte	-32,768 ~ 32,767	100S
int	4byte	-2,147,483,648 ~ 2,147,483,647	100
bigint	8byte	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	100L
float	4byte	单精度浮点数	3.1415926
double	8byte	双精度浮点数	3.1415926
decimal	-	高精度浮点数	DECIMAL(9,8)
boolean	-	布尔型，TRUE/FALSE	true
binary  (hive 0.8.0以上)	-	二进制类型	-
string	-	-	'abc'
varchar	-	1-65535	'abc'
char	-	1-255	'abc'
date		yyyy-MM-dd	2020-07-04
timestamps  (hive 0.8.0以上)		yyyy-MM-dd HH:mm:ss.fffffffff	2020-07-04 12:36:25.111
复合数据类型	大小	范围	示例
arrays			array_col array<STRING>
maps			map_col map<STRING,STRING>
structs			struct_col struct<name:string,country:string>
union			union_col map<STRING,array<STRING>>

 

1.1.1数字类型

数字类型	简介
整数类型	-2,147,483,648 ~ 2,147,483,647之间的整数类型默认是INT型，除非指定了格式100Y、100S、100L会自动转换为TINYINT、SMALLINT、BIGINT
浮点数类型	浮点数默认会当作DOUBLE型； Hive中的DECIMAL基于Java中的BigDecimal，DECIMAL不指定精度时默认为DECIMAL(10,0)； 2.5  字符串  new BigDecimal("2.5")

 

1.1.2字符串类型

字符串类型	简介
string	类型可以用单引号（'）或双引号（"）定义;
varchar	varchar类型由长度定义，范围为1-65355; 如果存入的字符串长度超过了定义的长度，超出部分会被截断。尾部的空格也会作为字符串的一部分，影响字符串的比较; 如果存入的字符串长度没超过了定义的长度, 则按字符串长度定义;
char	char是固定长度的，最大长度255，而且尾部的空格不影响字符串的比较;

三种类型对尾部空格的区别，参考如下例子，每个字段都插入同样的字符并且在尾部有不同的空格:

-- 构建建char_a表
create table char_a (
c1 char(4),
c2 char(5),
str1 string,
str2 string,
var1 varchar(4),
var2 varchar(6));
-- 插入数据
insert into char_a values('ccc ','ccc  ','ccc ','ccc    ','ccc ','ccc   ');
-- 查询
select c1=c2, str1=str2, var1=var2 from char_a;

  

 

1.1.3 日期与时间戳

日期与时间戳类型	简介
timestamp	timestamp表示UTC时间，可以是以秒为单位的整数；带精度的浮点数，最大精确到小数点后9位，纳秒级；java.sql.Timestamp格式的字符串 YYYY-MM-DD hh:mm:ss.fffffffff
Date	hive中的Date只支持YYYY-MM-DD格式的日期，其余写法都是错误的，如需带上时分秒，请使用timestamp。

示例：

-- 建带有timestamp格式字段的表
create table time_dual(time timestamp);
-- 插入一个数据（一个数据占一个文件）
insert into table time_dual values('2020-07-07 10:30:49.223');
-- 建带有date格式字段的表
create table date_dual(d date);
-- 插入一个数据（一个数据占一个文件）
-- 虽然命令没报错，但却没有将数据写入文件。
insert into table date_dual values('2020-07-13 12:18:48.807');
-- 将日期覆盖插入的date_dual（将目录下的所有都删掉，再写入）
insert overwrite table date_dual values('2020-07-21');

1) 获取当前timestamp和当前日期的函数:

-- 获取当前timestamp的函数 - 返回值是timestamp：
select current_timestamp();
-- 获取当前日期的函数  - 返回值是date：
select current_date();

  

 

2) 格式化 timestamp/date/string 为字符串：

-- 返回值：字符串  
date_format(date/timestamp/string ts, string fmt)

 示例:

-- 创建dual表后插入数据测试
create table dual(id int);
insert into dual values(1);
-- 当前日期格式化
select date_format(current_date(),'yyyy-MM-dd HH:mm:ss') from dual;
-- 当前时间格式化
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss') from dual;
-- 字符串格式化
select date_format('2020-07-30 14:23:40', 'yyyy-MM-dd') from dual;

  

 

3) Unix时间戳：

• 从1970-01-01 00:00:00 UTC到指定时间的秒数，例如：1530752400

示例:  [ 当前时间或时间字符串 ]  转  [ Unix时间戳 ]

--获取当前timestamp的Unix时间戳
select unix_timestamp(current_timestamp);
--获取指定字符串的Unix时间戳
select unix_timestamp('2020-07-05 09:00:00');

  

[ Unix时间戳 ]  转 [ 时间字符串 ]

-- unixtime：从1970-01-01 00:00:00 UTC到指定时间的秒数
-- format：目标转换格式
-- 返回值: string
-- 说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
from_unixtime(bigint unixtime[, string format])

示例：

-- 转换成 yyyy-MM-dd HH:mm:ss
select from_unixtime(1530755469);
-- 转换成指定格式的字符串
select from_unixtime(1530755469, "yyyy-MM-dd");

  

 

1.2 复杂类型

复杂数据类型	简介
STRUCT	STRUCT类似于java的类变量使用，Hive中定义的struct类型也可以使用点来访问。从文件加载数据时，文件里的数据分隔符要和建表指定的一致。 struct(val1, val2, val3, ...) ，只有字段值； named_struct(name1, val1, name2, val2, ...)，带有字段名和字段值；
ARRAY	ARRAY表示一组相同数据类型的集合，下标从零开始，可以用下标访问。如：arr[0]
MAP	MAP是一组键值对的组合，可以通过KEY访问VALUE，键值之间同样要在创建表时指定分隔符。 如：map_col['name'] Hive除了支持STRUCT、ARRAY、MAP这些原生集合类型，还支持集合的组合，不支持集合里再组合多个集合。

1）创建带有复合结构的表

-- ROW FORMAT DELIMITED FIELDS TERMINATED BY ','    意为hive使用字符','作为列分隔符 
-- COLLECTION ITEMS TERMINATED BY '-'               意为hive使用字符 '-' 作为集合元素间分隔符(一个字段各个item的分隔符)
-- MAP KEYS TERMINATED BY ':'                       意为hive使用字符作为map的键和值之间分隔符
CREATE TABLE complex
(
id int,
struct_col struct<name:string,country:string>,
array_col array<STRING>,
map_col map<STRING,STRING>,
union_col map<STRING,array<STRING>>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';

2）插入数据

insert overwrite table complex
select
id,
NAMED_STRUCT('name','Vincent','country','cn') as struct_col,
array('99','21','33') as array_col,
map('english','hello') as map_col,
map('english',array('99','21','33')) as union_col
from dual;
-- 将数据从hdfs上下载下来并查看插入的数据
hadoop fs -ls /user/hive/warehouse/complex
hadoop fs -get /user/hive/warehouse/complex/000000_0 ./
cat 000000_0

  

 

3）用HQL查询

-- 查询struct
select struct_col.name from complex;
-- 查询数组第一个元素
select array_col[0] from complex;
-- 查询map中key对应的value值
select map_col['english'] from complex;
-- 查询复杂结构map<STRING,array<STRING>>中key对应的value值
select union_col['english'] from complex;
-- 查询复杂结构map<STRING,array<STRING>>中key对应的value值（数组）中的第一个元素
select union_col['english'][0] from complex;