hive基础语法

作者: 何几时 | 来源:发表于2021-03-30 17:11 被阅读0次

-- 查询所有数据 
select * from hive_array; 
-- 查询loction数组中第一个元素 
select name, work_locations[0] location from hive_array; 
-- 查询location数组中元素的个数 
select name, size(work_locations) location from hive_array; 
-- 查询location数组中包含tianjin的信息 
select * from hive_array where  array_contains(work_locations,'tianjin');

Hive 查询语句

别名大坑：不同于MySQL中用 `"别名"`，HSQ的别名用

`别名`

多表查询

1.内连接查询

  隐式内连接： 
   select * from A,B where 条件; 
  显示内连接：
   select * from A inner join B on 条件;

2.外连接【比MySQL多了全外连接】

 # 左外连接：left outer join 
   select * from A left outer join B on 条件; 

 # 右外连接：right outer join 
   select * from A right outer join B on 条件;
 
 # 全外连接：full join(如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。)
   select * from A full join B on 条件;

Hive内置函数

1.字符串函数

字符串连接函数-带分隔符：==concat_ws==

语法: concat_ws(string SEP, string A, string B…)
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：

hive> select concat_ws(',','abc','def','gh’); 
abc,def,gh

字符串截取函数：==substr==,==substring==

语法:
substr(string A, int start, int len), --start是顺序
substring(string A, int start, int len) -- start可以是逆序【即负数】
返回值:
string 说明：返回字符串A从start位置开始，长度为len的字符串
举例：

hive> select substr('abcde',3,2); 
cd 
hive>select substring('abcde',-2,2);
de

2.日期函数

日期转年函数: ==year==

语法: year(string date)
说明:返回日期中的年。
举例：

hive> select year('2012-12-08’); 
2012

日期增加函数: ==date_add==

语法: date_add(string startdate, int days)
说明:返回开始日期startdate增加days天后的日期。
举例：

hive> select date_add('2012-12-08',10); 
2012-12-18

日期减少函数: ==date_sub==

语法: date_sub (string startdate, int days)
返回值: string 说明:返回开始日期startdate减少days天后的日期。
举例：

hive> select date_sub('2012-12-08',10); 
2012-11-28

3.行转列操作

行转列是指多行数据转换为一个列的字段。

Hive行转列用到的函数：
concat_ws(sep, str1,str2) --以分隔符拼接每个字符串
collect_set(col) --将某字段的值进行去重汇总，产生array类型字段

示例：

1:建表 
create table emp( deptno int, ename string ) row format delimited fields terminated by '\t’; 

2:插入数据： 
load data local inpath "/export/data/hivedatas/emp.txt" into table emp; 

3:转换 

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;

3.开窗函数

row_number,rank,dense_rank

生产中只用rank和dense_rank，row_number不合理

rank() 输出的排名 （1，2，3，3，5）

dense_rank() 输出的排名 （1，2，3，3，4）

示例：

-- 创建表，pv是访问次数
create table user_ access (
 user_id string, 
 createtime string, --day 
 pv int 
) 
row format DELIMITED FIELDS TERMINATED BY ','; 

-- 加载数据： 
load data local inpath '/export/data/hivedatas/user_access.txt' into table user_access;

--实现分组排名
  -- ROW_NUMBER()  从1开始，按照顺序，生成分组内记录的序列
  -- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位  
  -- DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

select user_id, createtime, pv, 
rank() over(partition by user_id order by pv desc) AS rn1, 
dense_rank() over(partition by user_id order by pv desc) as rn2, 
row_number() over(partition by user_id order by pv desc) as rn3 from user_access;

4.sum,avg,min,max

用于==实现分组内所有和连续累积的统计==

:arrow_right:SUM

-- 从开头unbounded preceding累加到当前行current row
select user_id,createtime,pv, 
sum(pv) over(partition by user_id order by createtime rows between unbounded preceding and current row) as pv2 
from user_access; 

-- 从前3行累加到当前行 
select user_id,createtime,pv, sum(pv) over(partition by user_id order by createtime rows between 3 preceding and current row) as pv4 
from user_access; 

-- 从前3行累加到后一行 
select user_id,createtime,pv, sum(pv) over(partition by user_id order by createtime rows between 3 preceding and 1 following) as pv5 from user_access;

:arrow_right:MAX

-- 从开头当前行的最大值
select user_id,createtime,pv, 
max(pv) over(partition by user_id order by createtime rows between unbounded preceding and current row) as pv2 
from user_access; 

-- 从前3行到当前行的最大值
select user_id,createtime,pv, max(pv) over(partition by user_id order by createtime rows between 3 preceding and current row) as pv4 
from user_access; 

-- 从前3行到后一行的最大值
select user_id,createtime,pv, max(pv) over(partition by user_id order by createtime rows between 3 preceding and 1 following) as pv5 from user_access;

Zepplin框架

Zepplin介绍

定义：

Apache Zeppelin是一款基于Web交互式框架，支持多种语言，提供了==数据分析==、==数据可视化==功能。

项目中的作用（两个）：

使用Zeppelin来连接到Spark SQL的Thrift Server，可以以更直观的方式来查看Hive中的数据。而且Zeppelin也可以以图表的方式展示数据。

总结：

使用Zeppelin来连接到Spark SQL的Thrift Server的好处有两个：

以更直观的形式来查看hive中的数据
可以以图表的方式展示数据

Zeppelin安装和使用

安装：黑马课程提供的虚拟机已安装

使用

一键启动集群中所有的组件
```
cd /export/onekey 
./start_all.sh
```
打开浏览器，输入地址访问：http://192.168.88.100:8090/

网约车日志分析项目

搭建大数据环境并连接各个组件

数仓分层：

1.原始日志数据会存放在临时存储层ODS层

2.预处理数据会放在数据仓库DW层

3.分析得到的结果数据放在应用层APP层

ods层分区表建立与数据加载

dw层的数据预处理及加载

case有两种写法，但是只用记住第二种写法

case when then

when then

...

end

app层的订单指标分析及加载

Sqoop数据导出

Superset数据可视化

:star:

hour() 返回null问题

解决办法来自：https://community.cloudera.com/t5/Support-Questions/hive-date-time-problem/td-p/139953

解决办法是，弃用date_format()，改用==from_unixtime(unix_timestamp(b,'yyyy-MM-dd HH:mm'))==

date_format()

适用于得出单独的年月日，比如hour(date_format(b,'yyyy-MM-dd')) 、year(date_format(b,'yyyy-MM-dd'))、month(date_format(b,'yyyy-MM-dd'))

不适用于时分秒函数内

from_unix_timestamp(unix_timestamp(b,'yyyy-MM-dd HH:mm'))

适用于所有日期的情况，无论是年月日时分秒，在规整时间后就可以得到年月日时分秒

网友评论

本文标题：hive基础语法

本文链接：https://www.haomeiwen.com/subject/wleghltx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！