hive SQL

hivesql
一.实际生产常用hsql语句函数。
1.sysdate(-1) 因为hive 中表数据都是分区数据，所以一般都是取一定时间得数据所以这个必须知道自己所在公司用啥函数。这个函数些公司再hive中自己封装分区获取函数。（某东就是用sysdate(-1),代表当前减去1天）
    select * from app.app_zhonghuadadi where dt = sysdate(-1) --dt就是分区字段。
2.开窗函数，实际生产中就开创函数遍布每天得工作。
    select sum() over（partition by cate_id ) from app.app_zhonghuadadi where dt = sysdate(-1)
    --本代码,计算出app.app_zhonghuadadi表中分区是sysdate(-1)中数据,但是数据是经过开窗函数过滤后得。最后结果数据是每个品类下数据得条数。和group by 类似。
    一：开窗函数中的区间
    partition by cate_id order by desc rows between A and B
    A:  unbounded preceding 从头开始
        3 preceding 不算当前的前三行
        current row 当前行
    B:  current row 当前行
        3 following 不算当前行，往后的三行
        unbounded following 最本组的最后一行
    二：开窗函数
        select  max(case when score_name = 'yuwen' then score else 0 end) as yuwen_max,
                max(case when score_name = 'shuxue' then score else 0 end) as shuxue_max
        from app.score group by student -- 每个学生语文的最高成绩和数学的最高成绩
3.开窗函数，实际生产中就开创函数遍布每天得工作。
    select row_number() over（partition by cate_id order by createtime) from app.app_zhonghuadadi where dt = sysdate(-1)
    --本代码,计算出app.app_zhonghuadadi表中分区是sysdate(-1)中数据,但是数据是经过开窗函数过滤后得。
    --最后结果是每个cate_id(分类)后。数据按照createtime(创建时间)来排序展示数据。
    --同理参考另外2个排序函数rank()、dense_rank()
    --同理真是生产中分组取topN得代码也是在这个得基础上整理出来得。因为有排名了。直接指定取多少名就ok了。
4.连接函数,left join 重中之重。为了性能考虑，一定要把小表放在 left join 左边
select a.*,b.xxx,b.sss from
    (select * from app.app_zhonghuadadi where sysdate(-1)) a
        left join
    (select * from app.app_zhonghua where sysdate(-1)) b
        on a.sku = b.sku
    --一定记住left join 最后的展示得数据是左边表的数据全部展示在结果中，如果只想展示两个表都有的数据就不用left join 用下面的语句
select a.*,b.xxx,b.sss from
    (select * from app.app_zhonghuadadi where sysdate(-1)) a
    ,
    (select * from app.app_zhonghua where sysdate(-1)) b
        where a.sku = b.sku
    --只展示两个表中都存在的数据
5.大小写转换和指定字符替换。lower(regexp_replace(v_key_new,' ','')))
    select regexp_replace('Big D a ta',' ','') --意思就是把'Big D a ta'中得空格去掉。此函数就是需要输入三个参数，第一个时字段，
        --第二个时需要替换得东西，最后是需要替换成得东西。
    select lower('Big D a ta') --大写变成小写
    select upper('Big D a ta') --小写变成大写
6.字符串得拼接语法
    select CONCAT('Big', 'Data') --计算结果就是BigData    
7.jason数据解析，这部分属于数据加工了。因为大数据得得数据有部分时埋点数据过来得。上游数据加工者可能并没有把所有你所需要得信息都加工出来，但是一般会有
    一个扩展字段，里面存有得时原始得json数据，这时候可以自己加工。
    数据为：[{"name":"苹果","price":"5000"},{"name":"hauwei","price":"10000"}]
    SELECT get_json_object(xjson,"$.[0]") FROM person; 会得到数据：{"name":"苹果","price":"5000"}
    SELECT get_json_object(xjson,"$.[0].price") FROM person;会得到数据 5000
    --实际需要中可以和其他函数结合使用。最终达到自己得目的。
8.数据表中数据有部分数据得格式时map类型，这时候取当中数据就很方便
    如果数据为{"column1":"1","column2":"２","column3":"３"}
    select ext_columns['column1'] from app.a where dt=sysdate(-1) 表为app.a，字段为ext_columns
    --取到得结果就是1
9.多行转一行
    例如数据为：
    "水果" "苹果"
    "水果" "香蕉"
    "水果" "鸭梨"
    "水果" "橘子"
    ==>变更为：
    "水果" "苹果","香蕉","鸭梨","橘子"
    select A,concat_ws(',',collect_set(B)) as C from aa group by A
    --表为aa，A时第一个字段，B是第二个字段。 

10.null 得相关数据。
    select * from app.a where id ！=100
    --这个sql得是意思大家应该都知道，就是取id不等于100得数据，但是如果id 是null 他是不在结果中得。
    --因此需要如下写：
    select * from app.a where id ！=100 or id is null

    select nvl(expr1, expr2)--如果expr1是null 取到得值就是expr2，如果不是null 取到得是啥就是啥。
11.case when 实际工作中主力军
    select * ，
    case when a <0 then 0
        when a <100 then 10
        when a< 1000 then 100
        else 1000 end as p
    from app.a
    --就是如果a小于0 p字段就是0，如果a小于100且大于等于0 p字段就是10，依次类推。
12.正则表达式
    正则表达式 一般和regexp_replace综合使用。但是实在没办法后才用正则。因为这玩意实在不好弄。
    举例如下：select regexp_extract('["4873748","666"]','([0-9]+)',0)
    --结果就是4873748 正则只是提一下，因为正则需要自己在平时工作中自己取学习，总结,单独学习，他可以解决很多组合。
13.数据截取substr
    select substr（string A，int start，int len） 就是截取A 从下角标start开始，截取len个字符
14.时间戳
    select unix_timestamp()    --结果是一串数字，1587726497 时间戳 当前时间得时间戳
    select unix_timestamp('2030-06-29 00:00:00');
    select from_unixtime(1587726497) 返回 timestamp --结果是2020-04-24 19:08:17
15.数据切割
    select split(A,',') 把A字段按照","进行切割
16.一行变多行
    select skus, skuname from app.a lateral view explode(split(skuname, ',')) test_skuname as skuname;
    --就是把skuname 字段中数据进行逗号split,然后。没切一个出来就多一行。他们得skus是一样得。  
17.判断含有得函数
    select sku_name regexp('.*iphone6sp.*') = true then 'iphone6sp' else sku_name end as sku_name
    --意思就是选择名字中只要含有iphone6sp 名字就变更为iphone6sp 如果不含有就是 原来得名字
18.对大量数据进行全局排序怎么排
    set reduce = 3;
    select * from app.sku_ord where dt = sysdate(-1) order by price 全局排序 但是只有一个reduce
    select * from app.sku_ord where dt = sysdate(-1) distribute by (case when price<10 then 0 when price<100 then 1 else 2) sort by price

    取样数据
    select * from dev.ppes_huishou_quanyi_ceshi sort by round()-0.5
二.hive的UDF函数
    1.实现evalute方法，实现ascii转换为字符，这里我写了一个简单得例子。如下：
    2.内置函数：str_to_map函数
---------------------------------------------------------
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class AsciiToString extends UDF {
/**
*实现evalute方法，实现ascii转换为字符
*/
public String evaluate(String str){
if(null != str){
String line = str.toString();
StringBuffer sb = new StringBuffer();
String[] chars = line.split(",");
for(int i = 0; i < chars.length; i++){
sb.append((char) Integer.parseInt(chars[i]));
}
return sb.toString();
}else{
return null;
}
}

}
-----------------------------------------------------------
    2.函数写好后，打包成jar,然后给运维，让他们去给你加到hive 中，如果自己着急用，就自己传到虚拟机上，你每次用这个函数得时候，就add jar 一次，就是麻烦点。
三.生产中hive的参数set
1.以上是我生产过程中的实际设定参数。
    set hive.hadoop.supports.splittable.combineinputformat = true;
    set mapreduce.input.fileinputformat.split.maxsize = 256000000;
    set mapreduce.input.fileinputformat.split.minsize.per.node = 256000000;
    set mapreduce.input.fileinputformat.split.minsize.per.rack = 256000000;
    set hive.exec.reducers.bytes.per.reducer = 5120000000;
    set hive.merge.mapfiles = true;
    set hive.merge.mapredfiles = true;
    set hive.merge.size.per.task = 256000000;
    set hive.merge.smallfiles.avgsize = 256000000;
    set hive.exec.parallel.thread.number = 8;
    set hive.exec.dynamic.partition.mode = nonstrict;
    set mapred.output.compress = true;
    set hive.exec.compress.output = true;
    set hive.exec.compress.intermediate=true;
    set hive.auto.convert.join = true;
    set hive.exec.parallel = true;
    SET hive.default.fileformat=Orc;
set hive.exec.max.dynamic.partitions=100000;
set mapred.min.split.size=1;
set mapred.min.split.size.per.node=1;
set mapred.min.split.size.per.rack=1;
    set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;
    set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
    set hive.map.aggr=true;
    set hive.groupby.skewindata=true;
    set hive.optimize.skewjoin.compiletime=true;
    set hive.auto.convert.join=true;
    set hive.mapjoin.smalltable.filesize =2500000 ;

    ----
    谓词下推!set hive.optimize...... = true!效果一样 默认就是true
    select * from tmp.duanwenguo as a left join b on a.id = b.id where a.a > 19
    select * from (select * from tmp.duanwenguo where a>19) as a left join b on a.id = b.id

    大小表优化
    select /*+MAPJOIN(a)*/ a.key，b.value from a join b on a.key = b.key
    大大表优化

    in 语法可以替换为join 最后得解决办法是替换为 semi join(半连接)

    使用vectorization技术进行聚合得批处理增量操作。

    多重模式
    from tmp.aaa insert overwrite table aaaaaa select a,b where
    insert overwrite table aasss select ...

    开启推测执行（把任务慢得数据任务 进行备份任务同时计算 但是浪费资源）




2.下面介绍一下一般都有啥，解释一下。
HIVE的性能调优主要遵循下面三个原则：
（1）增大作业的并行程度；
（2）保证任务执行时由足够的资源；
（3）满足前两条原则的前提下，尽可能的为shuffle阶段提供资源.
相关参数：
a、作业并发参数
set hive.exec.parallel = true；--该参数控制在同一个sql中的不同的job是否可以同时运行,默认为false；
set hive.exec.parallel.thread.number=16； --对于同一个sql来说可以同时运行的job的最大值，该参数默认为8.即此时最大可以同时运行8个job；
b、资源相关参数
mapreduce.map.memory.mb: --一个 Map Task 可使用的内存上限（单位:MB），默认为 1024。如果 Map Task 实际使用的资源量超过该值，则会被强制杀死。
mapreduce.reduce.memory.mb:-- 一个Reduce Task可使用的资源上限（单位:MB）,默认为 1024。如果 Reduce Task 实际使用的资源量超过该值，则会被强制杀死。
mapreduce.map.cpu.vcores: --每个 Maptask 可用的最多 cpu core 数目, 默认值: 1
mapreduce.reduce.cpu.vcores: --每个 Reducetask 可用最多 cpu core 数目默认值: 1
mapreduce.map.java.opts: --Map Task 的JVM参数，你可以在此配置默认的 javaheap size 参数.
mapreduce.reduce.java.opts: --Reduce Task的 JVM 参数，你可以在此配置默认的java heap size 参数.
set yarn.app.mapreduce.am.resource.mb=5120; --MR ApplicationMaster占用的内存量
set yarn.app.mapreduce.am.command-opts=-Xmx4096m; --AM JVM 内存
set yarn.app.mapreduce.am.resource.cpu-vcores=8; --MR ApplicationMaster占用的虚拟CPU个数
c、避免shuffle数据量过大
set hive.groupby.skewindata=true; --如果是group by过程出现倾斜 应该设置为true
set hive.skewjoin.key=100000; --这个是join的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置
set hive.optimize.skewjoin=true;--如果是join 过程出现倾斜 应该设置为true
set hive.map.aggr=true; --map端聚合是否开启，默认开启
hive.auto.convert.join：--根据输入文件的大小决定是否将普通join转换为mapjoin的一种优化，默认不开启false；
d、自动合并输入的小文件
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; --执行Map前进行小文件合并.
set hive.hadoop.supports.splittable.combineinputformat = true;
set mapred.max.split.size=256000000; ---每个Mapper任务处理的数据量的最大值.(这个值决定了合并后文件的大小)
set mapred.min.split.size.per.node=256000000; ---一个节点上split片的最小值(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.rack=256000000; ---一个交换机下split片的最小值(这个值决定了多个交换机上的文件是否需要合并)
e、自动合并输出的小文件
HIVE自动合并输出的小文件的主要优化手段为：
set hive.merge.mapfiles = true：--在只有map的作业结束时合并小文件，
set hive.merge.mapredfiles = true：--在Map-Reduce的任务结束时合并小文件，默认为False；
set hive.merge.size.per.task = 256000000; --合并后每个文件的大小，默认256000000
set hive.merge.smallfiles.avgsize=256000000; --当输出文件的平均大小小于该值时并且（mapfiles和mapredfiles为true），HIVE将会启动一个独立的map-reduce任务进行输出文件的merge。
set hive.merge.orcfile.stripe.level=false; --当这个参数设置为true,orc文件进行stripe Level级别的合并,当设置为false,orc文件进行文件级别的合并
实际生产中 我用过这个案例
set mapreduce.job.reduces=5;
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.merge.size.per.task = 256000000;
set hive.merge.smallfiles.avgsize=128000000;
set hive.merge.orcfile.stripe.level=false;
四.建表数据的格式
    1.首先生产过程中表需要提前建好，字段格式肯定是按照你的实际需求进行指定，是double 还是 string 需要你们的业务需求，你自己来指定的。
    第二个就是一般都是列式存储，压缩格式是snappy 建表的时候自己一定要指定好。这个就是一般的建表语句。大家肯定都会，如果不会可以联系我。
    比如某东 。你的表如果不是这种格式，最后上线就驳回。
    2.如何查看自己表的压缩格式
    desc formatted  app.a --查看自己表的格式。
五.如何查看自己表是否有小文件。
    dfs -count -h   hdfs:XXXXXXX/app_ppes_price_model_res/星号 --星号因该是*
    --首先查看自己表在hdfs上的位置，然后按照上面的语句进行查询。就可以看到下面有几个分区，分区下面有几个文件，大小是多少。在平均算一下每个文件的大小，小于128M 就是小文件。
六.hive和ES的对接。
    1.很多情况，小公司，外包公司都是hive数据推送到ES 怎么推送。
    大公司一般都有自己的工具，直接配置一下信息就ok。但是小公司一般需要你自己来完成，这时候可以参考ES的镜像工具。
    在hive生成ES的镜像表，然后把数据推送到ES的镜像表中，数据就会到ES中。这个知识点其实是ES的。但是在这里指出来，大家也有遇到就知道了。
    可以直接取ES网站上取看他的具体说明。如果有不懂得可以咨询我。哈哈   



行转列：
select dt,categoryType,count(DISTINCT user_log_acct) as uv,count(*) as pv  from (
select  dt, user_log_acct , get_json_object(skuname,'$.categoryType') as categoryType ,request_ts
from  dev.dev_log_m14_m_click_ppes 
lateral view explode(split(regexp_replace(regexp_replace(event_par,'\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) test_skuname as skuname
where  dt>='2022-09-30' and event_id='huishou_1632724658787|53'
group by dt,user_log_acct,get_json_object(skuname,'$.categoryType'),request_ts
) as t group by dt,categoryType
order by dt,categoryType