Hive使用总结

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编程指南》

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容