hive常用的函数以及知识

1.Hive：insert into table 与 insert overwrite table 区别
不清理以上已经插入的记录，直接执行insert overwirte，并查询分析结果
两者的异同
insert into 与 insert overwrite 都可以向hive表中插入数据，但是insert into直接追加
到表中数据的尾部，而insert overwrite会重写数据，既先进行删除，再写入。如果存在分区
的情况，insert overwrite会只重写当前分区数据。
2.Hive实现自增列的两种方法。
多维数据仓库中的维度表和事实表一般都需要有一个代理键，作为这些表的主键，代理键一般由单列的自增数字序列构成。
比如在oracle的plsql中使用序列号自增的方式V_BATCHNO := SEQ_BATCHNO.NEXTVAL;
那么在hive中如何实现？
（1）用row_number()函数生成 
insert into tbl_dim    
select row_number() over (order by tbl_stg.id) + t2.sk_max, tbl_stg.*    
from tbl_stg   
cross join (select coalesce(max(sk),0) sk_max from tbl_dim) t2;   
（2）用UDFRowSequence生成代理键
add jar hdfs:///user/hive-contrib-2.0.0.jar;    
create temporary function row_sequence as 'org.apache.hadoop.hive.contrib.udf.udfrowsequence';

insert into tbl_dim    
select row_sequence() + t2.sk_max, tbl_stg.*    
from tbl_stg   
cross join (select coalesce(max(sk),0) sk_max from tbl_dim) t2; 
3.hive--ag和lead 分析函数
lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
 select id,name,lag(name,1,0) over ( order by id )  from kkk; 
 select id,name,lead(name,1,0) over ( order by id )  from kkk;
4.hive的set优化_hive优化之参数调优等
 (1)hive参数优化之默认启用本地模式启动hive本地模式参数，一般建议将其设置为true，即时刻启用：
 set hive.exec.mode.local.auto=false
 (2)设置hive执行模式：防止用户执行一些影响不好的查询
 hive.mapred.mode=nonstrict
参数hive.mapred.mode控制着hive的执行模式，如果设置为strict模式，则hive作业禁止3种类型查询：
 1)分区表没有启用分区过滤字段。
 2)order by没有指定limit限制
 3)笛卡尔积
 (3)jvm重用相关设置:hadoop默认使用派生JVM来执行map和reduce任务的
set mapred.job.reuse.jvm.num.tasks=10;
这个功能的一个缺点就是会一直占用task插槽不释放，以备重用，直到任务完成才释放。
如果在任务过程中出现数据倾斜，则可能task插槽需要等到reduce task任务完成才能释放。
(4)单个mapreduce中运行多个group by：多个group by组装到单个mapreduce任务中
hive.multigroupby.singlemr= false;
(5)聚合优化：hive.map.aggr=true
(6)参数hive.fetch.task.conversion的调优:set hive.fetch.task.conversion=more;
(7)设置队列优先级
Set mapreduce.job.queuename=bigdata；
(8)若简单查询，可以不开启mapreduce模式,开启本地模式：
set hive.exec.mode.local.auto=true;
(9)并行计算模式：
set hive.exec.parallel=true; 

5.（1）查看表占有几个文件：desc formatted t1;从而可以指定map阶段可以使用map数量去处理：
table parameters:numFiles
（2）reduce阶段：
什么情况下，数据处理只会用到一个reduce任务：
hive.exec.reducers.bytes.per.reducer默认每个reduce任务处理数据量为1G下,数据总量小于1G;
没有group by的汇总;
用了Order by;
有笛卡尔积;
优化reduce阶段的方法：
1)调整hive.exec.reducers.bytes.per.reducer参数的值；
2)调整reduce个数:　set mapred.reduce.tasks=15;

6.Hive的MapJoin机制 hive.auto.convert.join
MapJoin:Map阶段进行表之间的连接,节省了在Shuffle阶段时要进行的大量数据传输.
原理：小表作为一个完整的驱动表来进行join操作，把小表全部读入内存中，在map阶段
直接拿另外一个表的数据和内存中表数据做匹配。

7.创建表的时候，hive文件存储格式有五种：
 1)、TEXTFILE:默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理
 2)、SEQUENCEFILE:以<key,value>的形式序列化到文件中;存储方式：行存储;可分割 压缩；
 3)、RCFILE
 4)、ORCFILE(0.11以后出现)
 5)、PARQUET
SEQUENCEFILE，RCFILE，ORCFILE,PARQUET格式的表不能直接从本地文件导入数据，
数据要先导入到textfile格式的表中， 然后再从表中用insert导入SequenceFile,RCFile,ORCFile,PARQUET表中
textfile:默认格式;存储方式为行存储;磁盘开销大 数据解析开销大;但使用这种方式，hive不会对数据进行切分，
从而无法对数据进行并行操作;
refile存储方式：数据按行分块 每块按照列存储；压缩快 快速列存取；
orcfile存储方式：数据按行分块 每块按照列存储;压缩快 快速列存取;效率比rcfile高
parquet :类似于orc
性能耗时来说：ORCfile <parquet <RCFILE<textfile,ORCFILE最优

8.Hive中获取第m条记录的语法：
Mysql中可以用limit m-1, n的限制语法；表中下标从0开始，从第m条记录开始取，一共取n条记录
（1）Hive中这种语法不支持，可以用row_number() over(distribute by ... sort by ... ) rank where
 rank< (m+n+1) and rank > (m-1)实现
 (2)where a limit m
 
9.当insert overwrite时使用动态分区+并行+
--打开动态分区后，允许所有分区都是动态分区模式
set hive.exec.dynamic.partition.mode = nonstrict;
--是否启动动态分区
set hive.exec.dynamic.partition=true;
-----小文件合并参数
--设置map端输出进行合并，默认为true
--文件数目小，容易在文件存储端造成瓶颈，给HDFS带来压力，影响处理效率。对此，可以通过合并Map和Reduce的结果文件来消除这样的影响。
set hive.merge.mapfiles = true;
--设置reduce端输出进行合并，默认为false
set hive.merge.mapredfiles = true;
-- 开启并行执行
set hive.exec.parallel=true;
--关闭并发
set hive.support.concurrency=false;
--设置map内存
set mapreduce.map.memory.mb=4128;

10.分区
（1）作用：分区是在处理大型事实表时常用的方法。分区的好处在于缩小查询扫描范围，从而提高速度。
（2）分类：静态分区static partition和动态分区dynamic partition。静态分区和动态分区的区别在于导入数据时，
是手动输入分区名称，还是通过数据来判断数据分区。
（3）分区流程：
第一步：分区设置，我们修改一下hive的默认设置以支持动态分区：
--开启动态分区功能
set hive.exec.dynamic.partition=true;
--设置为非严格模式
set hive.exec.dynamic.partition.mode=nonstrict; 
--在所有执行MR的节点上，最大一共可以创建多少个动态分区，一个DML操作可以创建的最大动态分区数
set hive.exec.max.dynamic.partitions=1000
--在每个执行MR的节点上，each mapper or reducer可以创建的最大动态分区数，目前系统默认为100
set hive.exec.max.dynamic.partitions.pernode=100
--整个MR Job中，最大可以创建多少个HDFS文件。
hive.exec.max.created.fi.les=100000
第二步：创建表分区，partitioned by([分区字段] [字段类型])
第三步:使用动态分区字段做分区索引时。然后用hive的insert命令进行插入操作。注意，除了所有列外，
需要将分区的动态字段跟在后面。
 查看分区情况：
 show partitions ori_partitioned_target;
分区优化的例子： 一张300个字段，1亿数据量的表，去重并重新按照天分区 
 1）.全表去重到中间表 时 distribute by分区字段，这样就可以 将相同分区的数据放到同一个文件中 用时 15 分钟
 2）.新处理过的中间表 动态分区至原表， 用时3分钟，问题完美解决；
 注：distribute by控制map的输出在reducer中是如何划分的。
弊端：由于一些分区可能比另一个分区里的数据多，导致map task处理数据时会出现不均匀。同时每次都会删除旧分区

11.Hive 并发模型
锁模式获取背后的原理如下：
对于非分区表，锁定模式相当直观。当表正在读取时，
一个S锁被获取。而对其他操作(插入数据到表，修改表的任何属性)就要获取X锁。
--关闭并发
set hive.support.concurrency=false;

12.explode:行转列。
总结起来一句话：explode就是将hive一行中复杂的array或者map结构拆分成多行。
lateral view:它能够将一行数据拆成多行数据,以对拆分后的数据进行聚合
lateral view与explode等udtf就是天生好搭档，explode将复杂结构一行拆成多行，然后再用lateral view做各种聚合。
lateral view explode 相当于一个拆分数组的虚表，然后根据userid将其与原表进行笛卡尔积关联.
explode(x) t as ele --没有序列号表示，只有值
posexplode(x) pe as idx, ele--idx表示数据序列号，ele表示对应的值
with subquery as  (
    select split(space(datediff('2020-11-30','2020-11-01')), ' ')  as x
)
select
    date_add('2020-11-01', idx) as new_date
from
    subquery t
    lateral view
    posexplode(x) pe as idx, ele;
 
13.Hive中collect相关的函数有collect_list和collect_set。collect_list 不去重，collect_set 去重。
 column的数据类型要求是string
 列转行：
concat(a,b,...);--多个字段拼接在一起，不限制字符串。
concat_ws(',',a,b);--和concat一样，多个字符串拼接在一起。
collect_set(col) ..group by --一列中的多个数据转行，变成数组的形式，只对一个字段,只接受基本数据类型
split('a,b,c,d',',')->["a","b","c","d"]:split 字符串分割函数；返回值为一个数组
concat_ws常和collect_set和group by结合起来一起用，变成数组类型 .
行转列：（1）explode(a);将一列转为多行，a只识别为array，map，struct；可以使用split转换。
如：select  explode(split(concat_ws(',',cast(a.bk_issue as string),cast(a.sk_issue as string)),',' ))
from dws_assetdw.ISSU_MKT_ISSUE A;
或者
select  a.bk_isue1,a.sk_issue
from dws_assetdw.ISSU_MKT_ISSUE A
lateral view explode(split(concat_ws(',',cast(a.bk_issue as string),cast(a.sk_issue as string)),',' ))
a as bk_isue1;
（2）posexplode：转列时，会多出一列编号。
collect_set去除重复元素；collect_list不去除重复元素
如：--先
select user_id,
concat_ws(',',collect_list(order_id)) as order_value 
from col_lie
group by user_id
explode只识别array数组，map key_value，struct;

14.split 字符串分割函数；返回值为一个数组
split('a,b,c,d',',')->["a","b","c","d"]

15.hive上创建数据仓库
（1）Hive如何实现代理键
没有建立代理键时可能存在的问题：
   当整合多个数据源的维度时，不同数据源的业务主键重复怎么办?
   涉及维度拉链表时，同一主体多条记录，业务键重复怎么办?
 维度表中必须有一个能够唯一标识一行记录的列，
 通过该列维护维度表与事实表之间的关系，一般在维度表中业务主键符合条件可以当作维度主键。
 代理键是由数据仓库处理过程中产生的、与业务本身无关的、唯一标识维度表中
 一条记录并充当维度表主键的列，也是描述维度表与事实表关系的纽带所以在设计有代理键的维度表中，事实表中
 的关联键是代理键而不是原有的业务主键，既业务关系是靠代理键维护，这样有效避免源系统变化对数仓数据对影响。
代理建:
 1）自增：hive-contrib-xx.jar中包含一个生成记录序号的自定义函数udfrowsequence
create temporary function row_sequence as 'org.apache.hadoop.hive.contrib.udf.udfrowsequence';
insert overwrite into dim_goods_d partition(dt='20191204')
select 
    row_sequence() + ta.max_id, 
    tb.*  
from 
    tmp_s_inc as tb
cross join (
    select 
        coalesce(max(gid),0) as max_id 
    from 
        dim_goods_d 
    where dt='20191203'
) as ta;
2）row_number()函数
insert overwrite into dim_goods_d partition(dt='20191204')
select
    tb.*,
    row_number() over(order by id)+ta.max_id as gid
from 
    tmp_s_inc as tb
cross join(
    select 
        coalesce(max(gid),0) as max_id 
    from 
        dim_goods_d 
    where dt='20191203'
)ta
union all
select 
    *
from 
    dim_goods_d 
where dt='20191203'；
使用UDFRowSequence实现代理键性能可能会优于row_number()函数实现的代理键。
此外，Hive3的代理键，可以使用内置的SURROGATE_KEY用户定义函数。在将数据输入表中时自动为行生成数字ID。生成的代理键可以替换多个复合键。

16.insert overwrite 会先删除整张表数据后，再插入select的数据

17.select t.a,count(t.b) from t group by t.a having count(t.b)>=2;--子查询中一定要加上count(t.b)

18.如果从一张表中获取数据进行insert时，可以
from a
insert overwrite table b select * where XXX
insert overwrite table c select * where xxx;

19.Hive3使用代理键。

20.Hive ACID和事务表支持详解；
（1）ACID是数据库事务的四大特性：原子性、一致性、隔离性、持久性；
（2）限制：只能自动提交，无commit、rollback;仅支持orc存储格式，需要配置相应的参数打开，需要set;
表需要分桶，只允许管理表，因为hive事务的实现主要依赖于表的分桶的存储格式。目前仅支持快照隔离级别
（3）SHOW TRANSACTIONS ——展示运行的事务
SHOW COMPACTIONS——展示正在执行的压缩任务
（4）Hive只能使用其他数据仓库常用的方法，也就是增量的形式记录更新和删除（也称做读时更新）
（5）存储在事务表中的数据会被分成两种类型的文件：
 base文件，用来存放平常的数据
 delta文件，用来存储新增、更新、删除的数据。
 每一个事务处理数据的结果都会单独新建一个delta文件夹用来存储数据。

21.巧用正则表达式
select  (case
            when t.birthday regexp ('-?[0-9]+.?[0-9]*') = false then
                from_unixtime(unix_timestamp('20190101', 'yyyy-MM-dd'), 'yyyy-MM-dd')
            when '2019-01-01' regexp ('-?[0-9]+.?[0-9]*') = true then
                t.birthday
            end ) as birthday
from aa T;

22.可用来做不等值连接。
select locate('a','abcd'), locate('b', 'abcd'), locate('f', 'abcd')
结果:  1  2  0

23.hive锁介绍
hive存在两种锁，共享锁Shared (S)和互斥锁Exclusive (X）。
其中只触发s锁的操作可以并发的执行，只要有一个操作对表或者分区出发了x锁，则该表或者分区不能并发的执行作业。
查看锁：SHOW LOCKS <TABLE_NAME>;
解决锁的问题：unlock table a ;
set hive.support.concurrency=false;--hive的锁机制，可以暂时关掉，默认是true，防止锁表.

24.ive的序列化和反序列化
序列化是对象转化为字节序列的过程；反序列化是字节码恢复为对象的过程；
对<key,value>反序列化成Hive table的每一列的值；Hive可以方便的将数据加载到表中而不需要对数据进行转换，
这样在海量数据处理时，可以节省大量的时间。
在Hive的HQL语句中，select时将会用到序列化操作， insert 时会用到反序列化操作.

25.日期函数：
（1）current_date();当前时间
（2）from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss');当前时间
（3）datediff('2019-11-11','2019-11-01');--两个日期天数差
（4）date_add('2019-11-11',1);  --日期加天数
（5）date_format(date,格式)：--格式化日期
（6）date_sub('2019-05-09',4) --日期减天数
（7）add_months('2020-05-03',1);--日期加一个月
 (8)date_format('2021-08-12','yyyyMMdd')--此日期只能转这个格式，或者‘yyyy-mm-dd’。不能够select date_format(20210812,'yyyy-mm-dd')
（9）Hive中yyyymmdd和yyyy-mm-dd日期之间的切换：
方法1: from_unixtime+ unix_timestamp
--20171205转成2017-12-05 
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;
--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;
方法2: substr + concat
--20171205转成2017-12-05 
select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',substr('20171205',7,2)) from dual;
--2017-12-05转成20171205
select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),substr('2017-12-05',9,2)) from dual;
 
26.hive具体知识：
1).在小表和大表关联时，一般的join可以使用hint/*+ streamtable(a)*/来指定a就是大表。
小表作为驱动表，放入内存中缓存，where中从左往右读取，表大小从左往右一次增加。
使用map-side join（在map端执行连接过程）优化时，使用hint/*+mapjoin(a)*/(0.7版本前的优化);
或者设置属性set hive.auto.convert.join =true(0.7版本后的优化)启动优化（注：右全外连接不支持该优化）
一般可以在hive-site.xml中export HADOOP_HEAPSIZE=4192内存
注：map-side join 可在map端和内存中小表逐一匹配，省略常规连接操作所需要的reduce     
2).大多数情况下,hive会对每对join连接对象启动一个mapreduce任务，可产生多个mapreduce job,
若三个或者三个以上的表关联，如果on中每个字句都是用相同的连接键时,只会产生一个mapreduce join>
3).左半开连接left semi-join:返回左边表的记录，该记录符合右边表满足on语句条件，
查询字段只能使用左边表的字段。如同in..exists一样，不过更高效，比inner join高效。
原理:对于左表中一条指定的记录，在右边表一旦找到匹配记录就停止扫描。
4).含有sort by 的distribute by:
distribute by控制map的输出在reducer 中如何划分的，mapreduce job中专属的所有数据都是按照键值对的方式进行组织的，
默认下，mapreduce计算框架会依据map输入的兼计算相应的哈希值，然后按照得到的哈希值将键值对均匀分发到多个reducer.
使用sort by只会在每个reducer中对数据进行排序，即局部排序。所以要想避免不同reducer输出内容时避免重叠，
这两者就同时使用，distribute by语句要写在sort by前。
distribute by和group by 控制reducer 时如何接受一行行数据进行分组的。
5).cluster by:distribute by和sort by的子句列一样时，可以使用cluster代替这两者。
6).hive中哪些情况可避免进行mapreduce。
hive对于某些情况的查询可以不必使用mapreduce,即使用本地模式
如：一张表的全表查询;where语句过滤条件只用分区字段
同时可以设置默认使用本地模式，否则使用mapreduce来执行查询
home/.hiverc中设置set hive.exec.mode.local.auto= true;
7).double和float的比较可以使用cast相互转换，和钱有关避免使用float浮点数
8).并行执行：设置hive.exec.parallel值为true.
9).from中优化理念就是把大表放在右边，小表作为驱动表，若小到可以放入内存中最好。
10).大于两张表join时，若关联字段一样，只会产生一个MR;
11).默认情况下，MR计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值
将键值对均匀分发到多个reducer中去。

27.LAG、lead over() --得出的日期不能直接减去数值
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) ；

28..合并小文件
文件数目小，容易在文件存储端造成瓶颈，给HDFS带来压力，影响处理效率。对此，可以通过合并Map和Reduce的结果文件来消除这样的影响。
（1） 出现小文件情况：
动态分区插入数据，产生大量的小文件，从而导致map数量剧 增；
reduce数量越多，小文件也越多（reduce的个数和输出文件是对应的）；
数据源本身就包含大量的小文件
（2） 设置：
 用于设置合并的参数有：
 是否合并Map输出文件：hive.merge.mapfiles=true（默认值为true）
 是否合并Reduce端输出文件：hive.merge.mapredfiles=false（默认值为false）
 合并文件的大小：hive.merge.size.per.task=256*1000*1000（默认值为256000000）
小文件问题的解决方案：
 1)从小文件产生的途径就可以从源头上控制小文件数量，方法如下：
 使用Sequencefile作为表存储格式，不要用textfile，在一定程度上可以减少小文件；
 减少reduce的数量（可以使用参数进行控制）；
 少用动态分区，用时记得按distribute by分区；
 2)对于已有的小文件，我们可以通过以下几种方案解决：
 使用hadoop archive命令把小文件进行归档；
 重建表，建表时减少reduce数量；
 通过参数进行调节，设置map/reduce端的相关参数，　
 //每个Map最大输入大小(这个值决定了合并后文件的数量)  
set mapred.max.split.size=256000000;    
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)  
set mapred.min.split.size.per.node=100000000;  
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)    
set mapred.min.split.size.per.rack=100000000;  
//执行Map前进行小文件合并  
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;   
设置map输出和reduce输出进行合并的相关参数：
[java] view plain copy
//设置map端输出进行合并，默认为true  
set hive.merge.mapfiles = true  
//设置reduce端输出进行合并，默认为false  
set hive.merge.mapredfiles = true  
//设置合并文件的大小  
set hive.merge.size.per.task = 256*1000*1000  
//当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。  
set hive.merge.smallfiles.avgsize=16000000

29.用户定义的函数UDF、用户定义的聚合UDAF、用户定义的表函数UDTF的区别：
UDF（User-Defined-Function）一进一出
UDAF（User-Defined Aggregation Funcation）聚集函数，多进一出
UDTF（User-Defined Table-Generating Functions）一进多出，如lateral view explore()

30.GROUP BY操作
　　默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。进行GROUP BY操作时需要注意以下几点：
Map端部分聚合
　　事实上并不是所有的聚合操作都需要在reduce部分进行，
很多聚合操作都可以先在Map端进行部分聚合，然后reduce端得出最终结果。
　　（1）开启Map端聚合参数设置
　　　　set hive.map.aggr=true
　　（2）在Map端进行聚合操作的条目数目
　　　　set hive.grouby.mapaggr.checkinterval=100000
　　（3）有数据倾斜的时候进行负载均衡（默认是false）
　　　　set hive.groupby.skewindata = true
有数据倾斜时进行负载均衡

31.排序选择
cluster by: 对同一字段分桶并排序，不能和sort by连用；
distribute by + sort by: 分桶，保证同一字段值只存在一个结果文件当中，结合sort by 保证每个reduceTask结果有序；
sort by: 单机排序，单个reduce结果有序
order by：全局排序，缺陷是只能使用一个reduce