hive hql语句高级用法

最新推荐文章于 2024-05-31 08:45:35 发布
最新推荐文章于 2024-05-31 08:45:35 发布
阅读量3.4k 收藏 21
点赞数 1
分类专栏： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36753550/article/details/82832486
版权

一，order by ,sort by,distribute by 的用法

1）order by 对全局数据的一个排序,仅仅只有一个reduce工作，最好不用。

2）sort by  对每一个reduce 内部数据进行排序，全局结果集来说不是排序。
 set mapreduce.job.reuces = 3 ;
 会生成3个文件，每个文件内都是排好序的。

3）distribute by  分区partition 类似与MapReduce 中分区parition ,对数据进行分区，默认采用hash算法，结合sort by 进行使用
注意：distribyte by 必须要放在 sort by 前面。

4）cluster by 当distribute by 和 sort by  字段相同时候，可以使用cluster by 来代替。

5）group by 分组，按照相同的key分为一组，也是按key来分区。

6）partition by 分区，窗口函数中的子句。


distribute by与group by对比
都是按key值划分数据
都使用reduce操作
唯一不同的是distribute by只是单纯的分散数据，而group by把相同key的数据聚集到一起，后续必须是聚合操作。

order by与sort by 对比
order by是全局排序
sort by只是确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。

应用场景
map输出的文件大小不均
reduce输出文件大小不均
小文件过多
文件超大

把一个大文件放到一些小文件中
set mapred.reduce.tasks=5;-->大文件变成5个文件，相同的province到一个文件中

insert overwrite table city
selsct time,country,province,city from info
distribute by province;


把一些小文件放到一个大文件中
set mapred.reduce.tasks=1;-->合并小文件
 

insert overwrite table province partition(dt='xxxx')
selsct time,country,province from city
distribute by country;

二、窗口函数和分析函数

应用场景：
（1）用于分区排序
（2）动态Group By
（3）Top N
（4）累计计算
（5）层次查询

窗口函数

FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
LAST_VALUE： 取分组内排序后，截止到当前行，最后一个值
LEAD(col,n,DEFAULT) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) ：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）


OVER从句

1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY语句，使用一个或者多个原始数据类型的列
3、使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
4、使用窗口规范，窗口规范支持以下格式：

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

注意：rows是物理的行，而range是逻辑上的行，比如取关键字id的数据的下面2行，就用rows关键字，而限制id 的范围在 （id-2, id+2） 之间就用range.

当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

OVER从句支持以下函数， 但是并不支持和窗口一起使用它们。
Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag 函数.

分析函数

ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列,比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
CUME_DIST 小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。
具体的更多用需要参考hive的官方文档

https://cwiki.apache.org/confluence/display/Hive/LanguageManual

三、一次查询多次插入

可以执行一次查询过程，而插入到多张表中去,效率很高

from table_name
 
insert overwrite  table table_name1 partition(day='xxx')
 
select xxx  where coulmn < 200
  
insert overwrite table table_name2 partition(day='xxx')
 
select xxx  where lenght(coulmn) =0;

四、条件语句

Return Type

Name(Signature)

Description

T
    

 

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

    

Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.

如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull ，（valueTrue，valueFalseOrNull为泛型）

Tnvl(T value, T default_value)Returns default value if value is null else returns value (as of HIve 0.11).

如果value值为NULL就返回default_value,否则返回value

TCOALESCE(T v1, T v2, ...)

Returns the first v that is not NULL, or NULL if all v's are NULL.

返回第一非null的值，如果全部都为NULL就返回NULL  如：COALESCE (NULL,44,55)=44

TCASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

When a = b, returns c; when a = d, returns e; else returns f.

如果a=b就返回c,a=d就返回e，否则返回f  如CASE 4 WHEN 5  THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4

TCASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

When a = true, returns b; when c = true, returns d; else returns e.

如果a=ture就返回b,c= ture就返回d，否则返回e  如：CASE WHEN  5>0  THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN  5<0  THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
 

booleanisnull( a )Returns true if a is NULL and false otherwise.

如果a为null就返回true，否则返回false

booleanisnotnull ( a )Returns true if a is not NULL and false otherwise.

如果a为非null就返回true，否则返回false

五、join用法

查找一张表在另为一张表没有的数据，适用于增量数据与历史数据合并，并且去除重复的数据。

select a.* from a left join b on (a.tid = b.tid) where b.tid is null;

查找a表中没有b表的数据。b.tid  is  null 就是 join 后过滤掉b 表中包含的数据。

六、with as 用法

with q1 as (select * from src where key= '5'),

q2 as (select * from src s2 where key = '4')

select * from q1 union all select * from q2;

with as 可以把数据缓存在内存中，避免多次计算重复的数据，with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！

七，动态插入

set hive.exec.dynamici.partition=true; #开启动态分区，默认是falsset hive.exec.dynamic.partition.mode=nonstrict; #开启允许所有分区都是动态的，否则必须要有静态分区才能使用。 set  hive.exec.max.dynamic.partitions.pernode=100 （默认100，一般可以设置大一点，比如1000）表示每个maper或reducer可以允许创建的最大动态分区个数，默认是100，超出则会报错。
 set hive.exec.max.dynamic.partitions =1000(默认值)表示一个动态分区语句可以创建的最大动态分区个数，超出报错
 set hive.exec.max.created.files =10000(默认) 全局可以创建的最大文件个数，超出报错。

 

 

 

 

 

文艺攻城狮
关注
  • 1
    点赞
  • 踩
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql数据抽取，自动生成hive建表语句
03-11
而Hive则是一种基于Hadoop的数据仓库工具，它提供了SQL-like的查询语言（HQL）来处理分布式存储的大规模数据集。在实际业务中，将MySQL中的数据迁移到Hive进行分析和挖掘是一个常见的需求。本教程主要围绕“mysql...
生成hive建表语句
08-24
在大数据处理领域，Hive 是一个非常重要的工具，它提供了基于 Hadoop 的数据仓库功能，让用户可以使用 SQL 类似的查询语言（HQL）对大规模数据进行分析。"生成Hive建表语句"这个主题主要涉及到如何创建Hive表结构，...
SqlServer 触发器 A表中插入一行数据后动态在B表中插入A表中计算后数据
lishiming0308的专栏
08-20 4105
表t_mydata 的列定义如下： int   id  ,int   station_id, datetime data_time,float LJLL 示例数据： 13911834   1  2012-08-19 8:00:00  2039886 13944831   1  2012-08-20 8:00:00  2043351 表t_station 的列定义如下 Int   sta
HQL语句--order by（排序）
热门推荐
白净垃圾桶-
09-01 3万+
order by：对查询结果排序 升序排序 asc （默认升序） 降序排序 desc @Test public void testOrderby(){ String hql="from Commodity order by price asc"; //升序 Query query=session.createQuery(hql); List<C
hive的高级操作
最新发布
Geonaut的博客
05-31 438
一.select 查询 1.查询基本表中数据SELECT * FROM t1; 2.获取当前正在使用的数据库SELECT current_database(); 3.查询某个库中的表 方法一：SELECT * FROM db_name.table_name; 方法二：USE db_name; SELECT * FROM table_name; 4.WHERE条件SELECT * FROM sales WHERE amount > 10 AND region = 'CHINA' 5.基于分区的
HQL语法优化之group by
hanzongqing的博客
02-01 402
Hive的group by优化，Hive语法优化之分组聚合优化
【大数据之Hive】十二、Hive-HQL查询之分组、join、排序
阿宁的博客
06-15 1636
group by 通常和聚合函数一起使用，按照一个或多个列的结果进行分组，任何对每个租执行聚合操作。用group by时，select中只能用在group by中的字段和聚合函数。
hql group by
weixin_30539625的博客
03-13 376
1.在Hql中使用group by的问题 (1)使用统计函数作为输出结果 select sum(total),sum(totalup) from AD where aid=? and fid=? and vdatetime>=? and vdatetime<=? group by aid (2)使用统计函数作为查询约束条件 select uid...
深入学习Hive应用场景及架构原理
01-27
《深入学习Hive应用场景及架构原理》 Hive，源于Facebook的创新，是为了解决海量社交网络数据管理和机器学习需求而诞生的。随着互联网进入大数据时代，Hadoop成为核心，但其MapReduce操作对技术要求较高。为适应这...
hive综合应用案例-用户学历查询.zip
05-24
例如，如果我们想要统计不同学历级别的用户数量，可以编写如下HQL语句： ```sql SELECT education_level, COUNT(*) as user_count FROM user_profile GROUP BY education_level; ``` 这将返回每个学历级别的用户数...
Hadoop-2.8.0-Day08-Hive函数与HQL详解-课件与资料.zip
04-30
在大数据处理领域，Hadoop和Hive是两个非常重要的组件。Hadoop是一个开源框架，主要用于分布式存储和计算大规模数据集，而Hive则...通过实践这些课件和资料，你将能够更好地理解和应用Hive在实际工作场景中的各种功能。
hql group by用法mySQL_HQL语言order by语句与group by子句使用实例
weixin_35676939的博客
02-03 1135
order by语句查询返回的集合根据类或组件属性的任何属性进行排序。例如：from Person as p order by p.name,p.age还可以使用as或desc关键字指定升序或降序的排序规则。例如：from Person as p order by p.name asc，p.age desc如果没有指定排序规则，默认采用升序规则。group by子句返回聚集值的查询可以对持久化类或...
Hive 的 HQL 语句：distribute by与group by，order by与sort by 的区别
Yannick Jiang 的专栏
09-10 7416
转载：https://www.cnblogs.com/huxinga/p/7688376.html distribute by与group by 的区别 都是按key值划分数据 都使用reduce操作 **唯一不同的是，distribute by只是单纯的分散数据，distribute by col – 按照col列把数据分散到不同的reduce。而group by把相同key的数据聚集到一起...
四、HQL语句--order by（排序）
zqzwzd的博客
08-17 2445
order by：对查询结果排序 升序排序 asc （默认升序） 降序排序 desc @Test public void testOrderby(){ String hql="from Commodity order by price asc"; //升序 Query query=session.createQuery(hql); Li...
窗口函数 OVER(PARTITION BY ...)
wangshiqi666的博客
07-01 2854
mysql的窗口函数
窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE
Ahuuua的博客
10-01 2万+
窗口函数OVER(PARTITION BY)详细用法，PARTITION BY写法，ROWS和RANGE的区别，各种函数共功能说明
SQL查询众数与中位数
Smilecoc的博客
09-24 5255
注意：本文均为SQL Server代码，其他数据库可能需要根据对应语法进行修改 创建测试用表： # 创建学生成绩表： create table #grade( id int, name varchar(10), score int ) # 向成绩表中插入数据： insert into #grade values (1,'Kevin',85), (2,'Mary',59), (3,'Andy',60), (4,'Tony',79), (5,'Lucky',90), (6,'Bob',60), (7,'T
Hive hql语句 group by 和 having
shuyv的博客
12-19 3778
文章目录分组1.group by案例实操：2.havinghaving和where的区别：案例实操 分组 1.group by group by 语句通常会和聚合函数一起使用，按照莫格字段的内容j进行分组，然后每个分组进行聚合操作。 案例实操： score表的数据 然后我们使用hql语句： select s_id ,avg(s_score) from score group by s_id; 以s_id作为分组条件，求得平均值，，结果如下： 2.having having和where的区别： 1.w
Hive实验3：Hql中的order by与sort by
heroicpoem的专栏
08-27 714
1、概述 [sort by] 是Hql特有的语句，同时Hive也支持rdbms的 [order by]。 [sort by] 是局部排序，[order by]是全局排序。 ps:还是要回到MapReduce的本质。MapReduce是分治并行，如果数据在多个子任务中执行，则结果只能保证每个任务内排序有效，总结果的每个任务间不保证有序。 1.1 对应MapReduce解决方案 问题：如何保证整体有序...
Hive编程与HQL指南：大数据处理入门
3. **HQL语法**：学习SELECT、FROM、WHERE、GROUP BY、JOIN、HAVING、ORDER BY等基本SQL语句在HQL中的应用，以及Hive特有的扩展功能，如LATERAL VIEW、UDF（用户自定义函数）等。 4. **数据导入与导出**：掌握如何...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值