Hive使用总结

阅读 393
收藏 12
2018-11-14
原文链接:www.jianshu.com

Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的sql查询功能,它专为OLAP设计,可以执行复杂的分析操作。本文总结了Hive的常用操作。

基础类型

Hive支持的数据类型如下:

数据类型 长度 例子
TINYINT 1byte有符号整数 20
SMALLINT 2byte有符号整数 20
INT 4byte有符号整数 20
BIGINT 8byte有符号整数 20
BOOLEAN 布尔类型,true或者false TRUE
FLOAT 单精度浮点数 3.14159
DOUBLE 双精度浮点数 3.14159
STRING 字符序列 'now is the time'
TIMESTAMP 整数,浮点数或者字符串 1327882394
BINARY 字节数组

Hive同时也支持如下算术运算符:

运算符 类型 描述
A+B 数值 A和B相加
A-B 数值 A减去B
A*B 数值 A和B相乘
A/B 数值 A除以B。如果能整除,那么返回商数。
A%B 数值 A除以B的余数。
A&B 数值 A和B按位取与。
A|B 数值 A和B按位取或
A^B 数值 A和B按位取异或。
~A 数值 A按位取反。

类型转换

语法

CAST(expr as <type>) 

例如

select CAST('1' as BIGINT);

创建表

语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]

例如

CREATE TABLE IF NOT EXISTS `employee`(
user_id int COMMENT 'employee id',
name string COMMENT 'employee name'
)COMMENT 'employee info'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;

执行结果

创建表.png

删除表

语法

DROP TABLE [IF EXISTS] table_name;

例如

DROP TABLE IF EXISTS employee;

执行结果

删除表.png

加载数据

在Hive中,一般使用LOAD DATA插入数据,数据来源一般为两种,一种是从本地文件系统,第二种是从hadoop文件系统。

语法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

例如

LOAD DATA LOCAL INPATH '/Users/ted/Desktop/hivetest/employee' OVERWRITE INTO TABLE employee;
LoadData.png

注意:文件的分隔符要和建表语句定义的一致,不然会出现异常的null数值。

新增数据

Hive新增数据有两种语法:insert into和insert overwrite。

语法

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

insert into 只是简单的插入,不考虑原始表的数据,直接追加到表中。

insert overwrite 会覆盖已经存在的数据,会先现将原始表的数据remove,再插入新数据。

例如

CREATE TABLE `employee_backup` as
SELECT * FROM employee;
CreateAs.png
INSERT INTO TABLE employee_backup
SELECT * FROM employee WHERE user_id=10;
InsertInto.png
INSERT OVERWRITE TABLE employee_backup
SELECT * FROM employee WHERE user_id!=10;
InsertOverwrite.png

表连接

原始数据


原始数据.png
等值连接

join即等值连接,只有某个值在两个表中同时存在才会被检索出来。

例如

select * from employee a join work_list b on a.user_id=b.user_id;
等值连接.png
左外连接

left outer join即左外连接,左边表中的值无论是否存在右表中,都会输出,但是右表中的记录只有在左表中存在时才会输出。

例如

select * from employee a left outer join work_list b on a.user_id=b.user_id;
左外连接.png
右外连接

right outer join即右外连接,右边表中的值无论是否存在左表中,都会输出,但是左表中的记录只有在右边中存在时才会输出。

例如

select * from employee a right outer join work_list b on a.user_id=b.user_id;
右外连接.png
左半连接

left semi join即左半连接,类似于存在判断。

例如

select * from employee a left semi join work_list b on a.user_id=b.user_id;
左半连接.png

逻辑类似于

select * from employee where user_id in (select user_id from work_list);

条件函数

IF

语法

IF(boolean condition, T trueValue, T falseValue)

当条件condition为TRUE时,返回trueValue;否则返回falseValue。

例如

select *,IF(name='ted',true,false) from employee;
if.png
NVL

语法

NVL(T v1,T v2)

如果v1为空那么显示v2值,如果v1的值不为空,则显示v1本来的值。

例如

select NVL(null,user_id) from employee where user_id=10;
nvl.png
COALESCE

语法

COALESCE(T v1, T v2, …)

返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL。

例如

select COALESCE(null,user_id,name) from employee where user_id=10;
coalesce.png
CASE

语法

CASE  [ expression ] 
  WHEN condition1 THEN result1 
  WHEN condition2 THEN result2 
  ... 
  WHEN conditionn THEN resultn 
  ELSE result 
END

如果expression满足某个condition,就返回对应result,不然返回ELSE里面的result。

例如

select work_id,work_time,
CASE
WHEN work_time>=60 THEN 'A'
WHEN work_time>=30 THEN 'B'
ELSE 'C'
END as grade
from work_list;
case.png

日期函数

名称 返回值类型 描述 例如
unix_timestamp() bigint 获得当前时间戳 unix_timestamp()
unix_timestamp(string date) bigint 获得date表示的时间戳 unix_timestamp('2018-08-08 21:00:00')
from_unixtime(int unixtime) string 将时间戳转换为日期时间字符串 from_unixtime(1533762000)
to_date(string timestamp) bigint 返回日期字符串 select to_date('2018-08-08 21:00:00')
year(string date) int 返回年 year('2018-08-08 21:59:58')
month(string date) int 返回月 month('2018-08-08 21:59:58')
day(string date) int 返回日 day('2018-08-08 21:59:58')
dayofmonth(string date) int 返回日 dayofmonth('2018-08-08 21:59:58')
hour(string date) int 返回小时 hour('2018-08-08 21:59:58')
minute(string date) int 返回分钟 minute('2018-08-08 21:59:58')
second(string date) int 返回秒 second('2018-08-08 21:59:58')
weekofyear(string date) int 返回周 weekofyear('2018-08-08 21:59:58')
datediff(string endDate, string startDate) int 返回天数差 datediff('2018-08-11 21:59:58','2018-08-08 12:59:58')
date_add(string startdate, int days) string startDate加days天数 date_add('2018-08-08 21:59:58',5)
date_sub(string startdate, int days) string startDate减days天数 date_sub('2018-08-08 21:59:58',5)

字符串转换

名称 返回值类型 描述 例如
length(string s) int 返回字符串长度 length('hello world')
reverse(string s) string 反转字符串 reverse('hello world')
concat(string a, string b...) string 合并字符串 concat('hello','+','world')
substr(string s, int start) string 返回子串 substr('hello world',7)
upper(string s) string 转换为大写 upper('hello')
lower(string s) string 转换为小写 lower('HELLO')
trim(string s) string 去除空格符 trim(' world ')

开窗函数

普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。

因此,普通的聚合函数每组只返回一个值,而开窗函数则可为窗口中的每行都返回一个值。

insert overwrite table statistics_base_orders
select id,order_id,billno,warehouse_id,warehouse_type,logis_company_id,logis_company_code,from
(select *,row_number() over (partition by order_id,billno order by db_update_time desc) as rows
from statistics_base_orders) a
where rows=1

WITH查询

Hive 可以通过with查询来提高查询性能。先通过with语法将重复使用的数据查询到内存,后面其它查询可以直接使用。

with tmp as (select * from employee where name='ted')
select * from tmp;

参考

  1. Hive保留关键字

  2. Hive聚合函数

  3. 《Hive编程指南》

评论