HQL笔记

最新推荐文章于 2024-10-23 11:51:33 发布

林子茗

最新推荐文章于 2024-10-23 11:51:33 发布

阅读量908

点赞数 1

分类专栏： hive sql 文章标签： sql hive

本文链接：https://blog.csdn.net/linhaitao1010/article/details/125715696

版权

sql 同时被 2 个专栏收录

4 篇文章

订阅专栏

hive

3 篇文章

订阅专栏

HQL

基本操作

查看所有表 show tables

查看函数的功能

desc function extended function_name;

分区表查询语句原则

原则：  只要从分区表中查询数据，一定要加分区字段！即使是全表查询也要加分区字段
因为如果忘记加分区字段，若是数据量很大的大表，则资源会被这个任务全部占用，可能会造成生产事故！！！

Hive函数

1、判断类

if(条件表达式，为true执行，为false执行 )

nvl(值，默认值)：当值不为null返回值，否则返回默认值

coalese(值1，值2…): 返回第一个不为null的值

isnull(值)：值 is null等价

isnotnull(值)：值 is not null

类似java中的多重if -else

case

when 条件判断1 then xxxx

when 条件判断2 then xxxx

…

else xxx

end

类似java中的switch-case

case 列

when 值1 then xxxx

when 值2 then xxxx

…

else xxx

end

2.构造类

可以使用不同类型的构造器构造对应的类型:

基本数据类型: bigint(整数)

复杂集合类型: array(值1,值2,…) , 打印 [] , 取值 array[index]

map(key1,value1,key2,value2,…), 打印 {}, 取值 map[key]

struct(val1,val2,val3…), 打印{}, 取值 struct.属性名。默认由系统提供col1…coln的属性名

named_struct(name1,val1,name2,val2,…): 自己提供属性名

cast(值 as 类型)：将值转换为指定的类型

3.时间转换类

date_format(日期，样式)：日期格式化

date_add|sub(日期，整数)：加减日期

datediff(日期1，日期2)：两个日期做差，前-后

next_day(日期，周几)：返回当前日期后的第一个周几

秒和时间转换:

from_unixtimestamp, to_unix_timestamp

毫秒和时间转换

from_utc_timestamp , to_utc_timestamp

4.窗口函数

4.1场景

①动态地改变函数的执行范围

传统的函数执行范围是固定的:

UDF: 当前行

UDTF: 当前行

UDAF: 当前组

②跨行取值及排名

跨行取值: lead，lag, first_valuue,last_value

排名函数： rank,row_number,dense_rank,ntile等

4.2 有哪些窗口函数

不是所有的函数都可以开窗。

（1）标准的聚合函数:

sum,count,avg,max,min

（2）跨行取值的函数

LEAD：取窗口中指定列，当前行之后N行的值
LAG：取窗口中指定列，当前行之前N行的值
FIRST_VALUE: 传入两个参数，第一个是列名，第二个默认为FLASE，如果为true,代表自动跳过NULL值。取窗口中的指定列第一行的值。

LAST_VALUE: 传入两个参数，第一个是列名，第二个默认为FLASE，如果为true,代表自动跳过NULL值。取窗口中指定列的最后一行的值。

（3) 排名函数

RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE

4.3 和分组的区别

分组的函数，是UDAF，将数据由多变少

窗口函数，是不改变返回的行数，输入N行，输出也是N行

4.4 格式

函数() over(partition by xxx order by xxx [window子句] )

window子句: rows|range between 上边界 and 下边界

边界怎么写: 由方向和范围组成

方向： preceding 向前 following 向后

范围: 不带符号的整数常量或关键字 unbounded(无边无界)

特殊情况:

①排名函数和 lead,lag 无需写window子句

②当有order by但是没写 window子句，此时有默认的窗口

range between unbounded preceding and current row

③没有order by也没有window子句，此时有默认的窗口

row between unbounded preceding and unbounded following

4.5 range和rows的区别

rows: 物理视图。固定行的范围，不会扩展

range: 逻辑视图。可能会对行的范围进行扩展。

当有order by但是没写 window子句，此时有默认的窗口 :range between unbounded preceding and current row

昨天同学的意思: 范围应该是上无边界到当前行及和当前行排序序号一样的行(稍大)

5.行转列函数

多行1列转 1行1列的函数称为行转列函数,本质上来说行转列函数是UDAF函数。

collect_set: 将多行1列，使用集合封装所有列，去重，返回数组

collect_list: 将多行1列，使用集合封装所有列，不去重，返回数组

6.列转行

explode 炸裂

/*
 explode(a) - separates the elements of array a into multiple rows,
 or the elements of a map into multiple rows and columns
 */
desc function extended  explode;
-- explode可以用来对array和map进行炸裂

--  Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'col2'
select explode(`array`(1,2,3)), 'abc' col2;

-- join 操作

select
    col1,col2
from (
         select explode(`array`(1, 2, 3)) col1
     ) tmp
join
    (
        select 'abc' col2
        ) tmp2;

-- 简写实现以上效果?  lateral view

select
        col1,col2
from
        (
        select 'abc' col2
        ) tmp2
lateral view explode(`array`(1, 2, 3)) tmp as col1;

HQL执行顺序

          -- 正确的顺序:  BD: 先找到分区数据所在的目录
          --            E:   先过滤，再Join
          --            C:   lateral view 本质就是join
          --            A: 对join后的数据进行函数运算
          --  lateral view 后面不是不能有语句?  不成立!
-- 加explain可查看执行顺序 
explain   select
                 -- A
               common.ar,
               common.ba brand,
               common.ch channel,
               common.is_new,
               common.md model,
               common.mid mid_id,
               common.os operate_system,
               common.uid user_id,
               common.vc version_code,

                  -- page
                   page.during_time,
                   page.item page_item,
                   page.item_type page_item_type,
                   page.last_page_id,
                   page.page_id,
                   page.source_type,

               ele.action_id,
               ele.item action_item,
               ele.item_type action_item_type,
               date_format(from_utc_timestamp(ele.ts,'Asia/Shanghai'),'yyyy-MM-dd') date_id,
              date_format(from_utc_timestamp(ele.ts,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') action_time
          -- B
            from ods_log_inc
                -- C
            lateral view explode(actions) tmp as ele
          -- D
            where dt='2020-06-14'
            -- 过滤出动作日志 E
            and size(actions) > 0

7.容器函数


map_keys(map)	Returns an unordered array containing the keys of the input map.
array_contains(array, value)	Returns TRUE if the array contains value.

Bug 解决

/*
 Caused by: java.lang.NullPointerException
	at java.lang.System.arraycopy(Native Method)
	at org.apache.hadoop.hive.ql.exec.vector.mapjoin.VectorMapJoinOuterStringOperator.process(VectorMapJoinOuterStringOperator.java:156)

 检查SQL，看SQL语法有没有问题!
        进一步推断，是否是当前版本的bug？
            同一个SQL，切换不同的引擎进行执行！如果MR引擎可以执行，Spark引擎无法执行，Hive On Spark当前版本的一个bug!
            如果MR引擎也无法执行，Spark也无法执行，可能是SQL语法或配置的错误!


        第一种： hive on spark出错，当前这条sql使用mr引擎跑! 运行结束后，再切换回引擎!
                或者
                将当前这条sql语句直接放在脚本的最后一个位置！ 运行结束后，不用切换引擎!

        第二种:  考验能力和经验，尝试去解决bug！
                    mapJoin 一般是用于 大表和小表进行Join! 将小表复制发送到大表所在的Task!
                    尝试不让Hive使用mapJoin来查询当前这条sql!
                         set hive.auto.convert.join=false;

 */