分区分类 两个维度
数量 - 单分区 多分区
精确度 - 静态分区 动态分区

往表中添加分区 
    alter table qka_table add partition (day='99990101')

hive内置函数
查看函数的具体用法 
    desc function extend concat;(即查看concat的用法)

字符串连接函数
    select count('a','b','c');

类型转换  
    select cast(1.555 as int);

获取数据范围
    select * from table where day between 9990101 and 99990103
(一般用between代替大于 小于)


获取时间戳
    unix_timestamp
    slect unix_timestamp() from qkx_table;

将yyyy-mm-dd的这种数据格式转换为毫秒数
    unix_timestamp('2009-03-20','yyyy-MM-dd') = 1237532400

时间戳转成日期
select distinct from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’) from test_date;

日期转成时间戳
select distinct unix_timestamp(‘20111207 13:01:03’) from test_date; // 默认格式为“yyyy-MM-dd HH:mm:ss“

select distinct unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’) from test_date;

yyyymmdd和yyyy-mm-dd日期之间的切换
方法1: from_unixtime+ unix_timestamp
–20181205转成2018-12-05
select from_unixtime(unix_timestamp(‘20181205’,‘yyyymmdd’),‘yyyy-mm-dd’) from dual;

–2018-12-05转成20181205
select from_unixtime(unix_timestamp(‘2018-12-05’,‘yyyy-mm-dd’),‘yyyymmdd’) from dual;

方法2: substr + concat
–20181205转成2018-12-05
select concat(substr(‘20181205’,1,4),’-’,substr(‘20181205’,5,2),’-’,substr(‘20181205’,7,2)) from dual;

–2018-12-05转成20181205
select concat(substr(‘2018-12-05’,1,4),substr(‘2018-12-05’,6,2),substr(‘2018-12-05’,9,2)) from dual;



获取当前时间的年
    year(string date)
    month(string date)
    day(string date)
    dayofmonth(string date)
    hour(string date)

当前时间加减
    date_sub()
    date_add()

最后一天
    last_day(string date)

某个时间段内
    months_between(date1,date2)

判断
    case when 

case
when a=b then c
when ...
end

拼接字符串
concat()
concat_ws()

补0
lpad(id,11,0): 如果不足11位的话 则在前面补0
rpad()

截取
substr('abcde',4,1) = 'd'

切割
split()

行转列
explode()


hive窗口函数 也叫 OLAP 函数 （online anallytical processing 联机分析处理）
    1 聚合函数
    2 专用窗口函数

多表连接 
join, left join,  right join, inner join 

union 将两张表中的数据进行去重 然后进行合并
union all 将两张表中的数据进行合并 不管有没有重复的数据

工作中主要用的就是 union union all left join

hive自定义函数 (因为有时内置函数并不能满足我们的业务要求)
挺麻烦的 需要写java 再上传什么的
创建永久的
    create function sxt_hello as 'com.vincent.UDFHello' using jar 'hdfs:///shsxt-hadoop/hive/bin/lib/demouf.jar' 
创建临时的
    create temporary function sxt_hello as 'com.vincent.UDFHello' using jar 'hdfs:///shsxt-hadoop/hive/bin/lib/demouf.jar'

这里的 ‘进’ 指的就是输入，‘出’ 指的就是输出
UDF一进一出，例如： unix_timestamp() 
UDTF多进一出 例如： sum()
UDAF一进多出 例如： split()


order by 全局排序
distribute by 分区间进行排序
sort by 分区内部进行排序
cluster by = sort by + distribute by 
group by 

hive中goupby 和 sql中是不一样的 

重点：
select id,name from table group by id;
(上面这种写法在hive中是不可行的)

查询的列要么放在group之后 要么前面加聚合
    select id,name from table group by id,name;
    select id,count(name) from table group by id;


hive分桶
原理： 是将字段值hash取模分布到不同的桶里
 
先开启参数
set hive.enforce.bucketing=true;
set mapreduce.job.reduce=3;

分桶表的建立
create external bucket_table if not exists table(
    name string
    grade int
    )
    clustered by(grade) into 3 buckets
row format delimited fields terminated by '\t'
location 'data/ods'

然后把其他的表的插入到建的表
insert overwrite table bucket_table select * from qkx;

注意： 分桶的表无法用load语句

数据取样
1 基于桶抽样
    select * from bucket_table tablesample(bucket 2 out of 3 on grade)
以上的数据是取出第二个桶到第三个桶所以的数据

要是只取第二个的话 用 (bucket 2 out of 2 on grade)

2 基于百分比取样
    select * from bucket_table tablesample(bucket 1 out of 3 on rand());


hive数据倾斜
类似于这种 
select * from a
    join b on a.id = b.id
    join c on c.name = b.name
    join d on d.no = c.no 
就很容易发生数据倾斜

！所有的倾斜原因本质：key分布不均 

导致倾斜的原因：
    大数据文件不可切分
        map 默认是128m进行切分，当碰到不可切分的大数据文件时可能会发生数据倾斜
        因为只能有一个map在执行
    方法：将数据存储格式改为 orc 或者 sqence 等列式存储 或者 bzip2等可分割的压缩算法


    多维表关联，数据膨胀
        就多张表笛卡尔积 类似于python里有时候merge很大 就炸了
    方法：一种是分别计算后合并，一种是使用一个id


    中间数据无法消除
    方法：调整reduce所执行的内存大小 使用mapreduce educe memory mb 这个参数


    业务无关的数据引发的数据倾斜
    方法：将无用的字段加工处理掉


    两表连接时引发的数据倾斜
    方法：hive.map.aggr=true开启map端聚合


hive 优化
hive优化的核心思想： 把hive sql当作mapreduce程序去优化

本地模式：如果文件在本地 就不登陆集群 去本地取比较近
hive.exec.mode.local.auto = true

并行执行：
set hive.exec.parallel=true --打开任务并行执行
set hive.exec.parallel.thread.number=16 -- 同一个sql运行最大并行度

严格模式：
开启严格模式需要修改hive.mapred.mode值为strict 
开启严格模式可以禁止3种类型的查询
防止用户执行低效率的sql查询

对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行
    对于使用了order by语句的查询，要求必须使用limit语句
    限制笛卡尔积的查询

JVM重用
    JVM重用可以使得JVM实例在同一个job中重新使用N次
    N的值可以在hadoop的mepred-site.xml 文件中进行配置
    通常在 10-20 之间 具体多少需要根据业务场景测试得出

表的优化（小表和大表）
    hive默认第一个表（左边的）是小表 然后将其存放到内存中
    然后去和第二张表进行比较
    现在优化后，小表前后无所谓。

表的优化（大表和大表）
    针对于空值，可以将空值随机设置一个不影响结果的值
        将来reduce的时候可以分区到不同的reduce 减小压力

mapside聚合
    hive.map.aggr=true
    hive.groupby.mapaggr.checkinterval=100000
    hive.groupby.skewindata=true

count(distinct)
    防止所有的数据都分到一个reduce上面
    首先使用groupby对数据进行分组 然后统计

select 
    count(distinct mac_macaddr) 
from 
    testmac 
对比于：
select count(*） from
    (select mac_macaddr from testmac group by 1) foo    

或者写成 
select count(*) from
    (select mac_macaddr from testmac group by mac_macaddr) foo  

group by 1 它的意思是按第一列分组，而不管它的名称是什么


防止笛卡尔积

行列过滤（列裁剪）
    当表关联的时候，优先使用子查询对表的数据进行过滤，
    这样前面表关联数据就是少的，减少关联的次数


工作上常用到的hive sql小技巧
1 多表关联时，多用union all，多表union all会优化成一个job
2 修改表结构时要用replace columns 使用add columns数据为空 这时因为元数据库没有更新
3 注意hive null 和 ''的区别 不管哪一种做相加时都会变成null
4 去列参数set hive.support.quoted.identifiers=none;
5 查询切记加limit
6 窗口函数

hive 日期函数总结

1 将时间戳转换为日期

select from_unixtime(时间戳，'yyyy-MM-dd')

2 将日期转换为时间戳

select unix_timestamp('2020-12-01','yyyy-MM-dd') #注意这里后面跟的是格式

3 返回 日期+时间 字段中的日期

select to_date('2020-12-21 2:11:22') #返回2020-12-21

4 结束时间减去开始时间的天数

select datediff('2020-12-21','2020-12-20') #返回1 带着 时分秒 数据也可计算 不影响天数结果

5 日期相加 相减

select date_add('2020-12-01',1),date_sub('2020-12-01',1) #返回 12-02 和 12-00

6 取出日期+时间中的 年月日 时分秒

year('2020-12-01 14:20:01') month day hour minute second weekofyear

7 获取当前时间

from_unixtime(unix_timestamp()) 

8 一般对于日期的加减 几天（月，年，小时。。） 用interval关键字

select date_add('2020-12-01',interval +1 day) > '2020-12-02'

select date_add('2020-12-01',interval +1 hour) > '2020-12-01 01:00:00'

select date_add('2020-12-01',interval -1 day) > '2020-12-00'