HIVE一些语法

2：hive详细表结构操作（增加列，删除列，修改列，移动列）
hive修改表名:
    ALTER TABLE table_name RENAME TO new_table_name;
hive表在最后增加一个字段（在分区字段之前）:
    alter table table_name add columns (now_time string comment ‘当前时间‘);
hive表字段调整位置（在某个特定的位置添加字段）:
    hive表在某个特定的位置添加一个字段，在操作上是先在表的最后添加一个字段然后再将该字段移动到那个特定的位置
    alter table table_name change now_time now_time string after id;
hive表修改列名（修改表字段类型）:
    ALTER TABLE table_name CHANGE name ename String;hive表删除列:
    假设一张student表，有id int，name string两个字段，现在要删除id字段
    alter table student replace columns(name string);
3：hive中count(*)、count(1)、count(某字段)的区别
    count(*)：所有行进行统计，包括NULL行
    count(1)：所有行进行统计，包括NULL行
    count(column)：对column中非Null进行统计    
    题外话：在mysql与oracle中
        如果在开发中确实需要用到count()聚合，那么优先考虑count(*)，因为mysql数据库本身对于count(*)做了特别的优化处理。
        有主键或联合主键的情况下，count(*)略比count(1)快一些。
        没有主键的情况下count(1)比count(*)快一些。
        count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）
4：Hive数据倾斜
    1. 什么是数据倾斜
        mapreduce中，相同key的value都给一个reduce，如果个别key的数据过多，而其他key的较少，就会出现数据倾斜。
        通俗的说，就是我们在处理的时候数据分布的不均，导致了数据大量集中在某一点。造成了数据的热点。
        其实在mapreduce分析的时候最怕的就是数据倾斜，通常会出现下面的情况：
            map阶段处理比较快，reduce阶段处理比较慢。其实reduce阶段不应该很慢，如果很慢，很大可能就是出现了数据倾斜。
            1)有的reduce很快，有的reduce很慢。
            2)Hive的执行是分阶段的，map处理数据量的差异取决于上一个stage的reduce输出，如果在map端，有的job运行很快，有的很慢，这其实也出现了数据倾斜
    2. 哪几种情况容易出现数据倾斜
        分组: group by 优于distinct
            情形：group by 维度过小，某值的数量过多
            后果：处理某值的reduce非常耗时
        去重:distinct,count(distinct xx)
            情形：某特殊值过多
            后果：处理此特殊值的reduce耗时
        连接:join
            情形1：其中一个表较小，但是key集中
            后果1：分发到某一个或几个Reduce上的数据远高于平均值
            情形2：大表与大表，但是分桶的判断字段0值或空值过多
            后果2：这些空值都由一个reduce处理，非常慢
    2.1 原因
        key分布不均匀
        业务数据本身的特性
        建表时考虑不周
        某些SQL语句本身就有数据倾斜
    2.2 表现
        任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
        单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。
    3.数据倾斜的解决方案
        3.1参数调节：
            hive.map.aggr=true
                Map端部分聚合，相当于Combiner
            hive.groupby.skewindata=true
                有数据倾斜的时候进行负载均衡，
                当选项设定为true，生成的查询计划会有两个MRJob。
                第一个MRJob中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupByKey有可能被分发到不同的Reduce中，从而达到负载均衡的目的；
                第二个MRJob再根据预处理的数据结果按照GroupByKey分布到Reduce中（这个过程可以保证相同的GroupByKey被分布到同一个Reduce中），最后完成最终的聚合操作。
        3.2 SQL语句调节：
            如何Join：
                关于驱动表的选取，选用join key分布最均匀的表作为驱动表
                做好列裁剪和filter操作，以达到两表做join的时候，数据量相对变小的效果。
            大小表Join：
                使用map join让小的维度表（1000条以下的记录条数） 先进内存。在map端完成reduce.
            大表Join大表：
                把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。
            count distinct大量相同特殊值
                count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。
                如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。
            group by维度过小：
                采用sum() group by的方式来替换count(distinct)完成计算。
            特殊情况特殊处理：
                在业务逻辑优化效果的不大情况下，有些时候是可以将倾斜的数据单独拿出来处理。最后union回去。
    4. 典型的业务场景
        4.1空值产生的数据倾斜
        场景：如日志中，常会有信息丢失的问题，比如日志中的 user_id，如果取其中的 user_id 和 用户表中的user_id 关联，会碰到数据倾斜的问题。
        解决方法1：user_id为空的不参与关联
            select * from log a
                join users b
                on a.user_id is not null
            and a.user_id = b.user_id
            union all
            select * from log a
                where a.user_id is null;    
        解决方法2 ：赋与空值分新的key值
            select *
            from log a
            left outer join users b
            on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;
        结论：方法2比方法1效率更好，不但io少了，而且作业数也少了。解决方法1中 log读取两次，jobs是2。解决方法2 job数是1 。这个优化适合无效 id (比如 -99 , ’’, null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。
        4.2 不同数据类型关联产生数据倾斜
        场景：用户表中user_id字段为int，log表中user_id字段既有string类型也有int类型。
        当按照user_id进行两个表的Join操作时，默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。
        解决方法：把数字类型转换成字符串类型
        select * from users a
        left outer join logs b
        on a.usr_id = cast(b.user_id as string)
        4.3 小表不小不大，怎么用 map join 解决倾斜问题
        使用 map join 解决小表(记录数少)关联大表的数据倾斜问题，这个方法使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理。
        以下例子:
        select * from log a
        left outer join users b
        on a.user_id = b.user_id;
        users 表有 600w+ 的记录，把 users 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。
        假如，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。
        所以下面这个方法能解决很多场景下的数据倾斜问题。解决方法：
        select /*+mapjoin(x)*/* from log a
        left outer join (
            select  /*+mapjoin(c)*/d.*
            from ( select distinct user_id from log ) c
            join users d
            on c.user_id = d.user_id
            ) x
        on a.user_id = b.user_id;
    5. 总结
    使map的输出数据更均匀的分布到reduce中去，是我们的最终目标。由于Hash算法的局限性，按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。在此给出较为通用的步骤：
        采样log表，哪些user_id比较倾斜，得到一个结果表tmp1。由于对计算框架来说，所有的数据过来，他都是不知道数据分布情况的，所以采样是并不可少的。
        数据的分布符合社会学统计规则，贫富不均。倾斜的key不会太多，就像一个社会的富人不多，奇特的人不多一样。所以tmp1记录数会很少。把tmp1和users做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。
        map读入users和log，假如记录来自log,则检查user_id是否在tmp2里，如果是，输出到本地文件a,否则生成<user_id,value>的key,value对，假如记录来自member,生成<user_id,value>的key,value对，进入reduce阶段。
        最终把a文件，把Stage3 reduce阶段输出的文件合并起写到hdfs。
    如果确认业务需要这样倾斜的逻辑，考虑以下的优化方案：
        对于join，在判断小表不大于1G的情况下，使用map join
        对于group by或distinct，设定 hive.groupby.skewindata=true
        尽量使用上述的SQL语句调节进行优化