hive

hive基础知识

数据管理

1、hive的表本质就是hadoop的目录/文件，hive默认表存放路径一般都是在你的工作目录里面，按照表名做文件夹分开，如果有分区表的话，分区值是子文件夹，可以直接在其他的M/R job里直接应用这部分数据。
2、hive支持4个数据模型：

• 数据表
  Table(内部表)：create table
  External Table(外部表)：create external table
  两者的差别：外部表删除了，可以通过create table的方式恢复
• 分区表
  Partition ： 以一或多字段名字为文件夹做分区，一般用时间、区域做分区字段
  partition是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理
• 分桶表
  Bucket：hive会针对某一列进行桶的组织，通常对列值做hash

分库：一张表分成多张表
例如：分32库，userid % 32 = 余数num（桶号）
表名：table_online_broadcast
分库后：
table_online_broadcast_0
table_online_broadcast_1
table_online_broadcast_2
table_online_broadcast_3
……
table_online_broadcast_30
table_online_broadcast_31

3、优化：
Table—-》Partition—-》Bucket
Table—-》Bucket

• 优化查询： 两个分区表做join会自动激活map端(map-side join)
• 方便采样

数据类型

• 原生类型：TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING、BINARY（Hive 0.8.0以上才可用）、TIMESTAMP（Hive 0.8.0以上才可用）
• 复合类型：Arrays、Maps、Structs、Union

hive的优化

1、hive的优化：
set dfs.block.szie : 作业会通过input 的目录产生一个或者多个map任务
可以改这些参数改map的个数

set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

以下只是一个参考不一定生效：

set mapred.map.tasks=10;

为了让任务加快，有些任务可以考虑设置combiners

Map端聚合hive.map.aggr=true

2、Reduce优化：
reduce个数计算公式

(hive.exec.reducers.bytes.per.reducer设置分母)
reduce个数=InputFileSize / bytes per reducer

调整reduce的个数，设置reduce处理的数据量：

set mapred.reduce.tasks=10

如果两个参数hive.exec.reducers.bytes.per.reducer和mapred.reduce.tasks同时设置的话，以后者为准
注意：一个reduce：没有group by、order by 、笛卡尔积

两个表聚合join的时候，使用on
3、分区裁剪（partition）
Where中的分区条件，会提前生效，不必特意做子查询，直接Join和GroupBy
4、笛卡尔积
join的时候不加on条件或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积
5、Map join
类似一种检索配置文件的形式
/+ MAPJOIN(tablelist) /，tablelist必须是小表，不要超过1G，或者50万条记录
6、Union all
先做union all再做join或group by等操作可以有效减少MR过程，尽管是多个Select，最终只有一个mr
7、Multi-Count Distinct
这个参数hive做负载均衡，解决数据倾斜问题

set hive.groupby.skewindata=true; 

并行执行

同步执行hive的多个阶段，hive在执行过程，将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行的，这样可能使得整个job的执行时间缩短

set hive.exec.parallel=true

数据倾斜

1、大小表关联
/+ MAPJOIN(a) /，a必须是小表，不要超过1G，或者50万条记录
A join(B) ,假设A是小表，B是大表，hive默认把join左边的数据放到内存中，右边的数据做类似流数据,此时复杂度为O(n)
/+ STREAMTABLE(a) / : a是大表
原因：Hive在进行join时，按照join的key进行分发，而在join左边的表的数据会首先读入内存，如果左边表的key相对分散，读入内存的数据会比较小，join任务执行会比较快；而如果左边的表key比较集中，而这张表的数据量很大，那么数据倾斜就会比较严重，而如果这张表是小表，则还是应该把这张表放在join左边。
思路：将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率。使用map join让小的维度表先进内存。
方法：small_table join big_table
2、大大表关联
原因：日志中有一部分的userid是空或者是0的情况，导致在用user_id进行hash分桶的时候，会将日志中userid为0或者空的数据分到一起，导致了过大的斜率。
思路：把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。
方法：on case when (x.uid= ‘-’ or x.uid= ‘0‘ or x.uidis null) then concat(‘dp_hive_search’,rand()) else x.uidend = f.user_id;
3、大大表关联（业务削减）
案例：

Select * from dw_logt join dw_usert1 on t.user_id=t1.user_id

现象：两个表都上千万，跑起来很悬
思路：当天登陆的用户其实很少
方法：

select/*+MAPJOIN(t12)*/ *
    from dw_logt11
    join (
        select/*+MAPJOIN(t)*/ t1.*
        from (
            select user_idfrom dw_loggroup by user_id
        ) t
        join dw_usert1
        on t.user_id=t1.user_id
) t12
on t11.user_id=t12.user_id

4、聚合时存在大量特殊值
count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。

select cast(count(distinct user_id)+1 as bigint) as user_cnt
     from tab_a
     where user_idis not null and user_id<> ''

cast函数作用是转换类型，把里面的数据类型转换为bigint类型
5、空间换时间
案例：

Select day,count(distinct session_id),count(distinct user_id) from log a group by day

问题：同一个reduce上进行distinct操作时压力很大
方法：

select day,
    count(case when type='session' then 1 else null end) as session_cnt,
    count(case when type='user' then 1 else null end) as user_cnt
from (
    select day,session_id,type
    from (
        select day,session_id,'session' as type
        from log
        union all
        select day user_id,'user' as type
        from log
    )
    group by day,session_id,type
) t1
group by day

hive的使用

1、创建表
comment可以基于列级别，也可以在表级别
PARTITIONED关键字定义的分区列和数据列是不同的，分区列实际上不存储数据
CLUSTERED BY 指定列进行分桶，以及创建多少桶
在这种分隔符情况下，指定了字段是如何结束，集合项（数组和map）如何结束，以及map的key是如何结束的
STORED AS SEQUENCEFILE表示这个数以二进制格式进行存储数据在hdfs上

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                page_url STRING, referrer_url STRING,
                friends ARRAY<BIGINT>, properties MAP<STRING, STRING>,
                ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '1'
        COLLECTION ITEMS TERMINATED BY '2'
        MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

2、浏览表和分区

SHOW TABLES;
SHOW TABLES 'page.*';
SHOW PARTITIONS partition_name;
DESCRIBE EXTENDED page_view;  #列出表的列和表的其他属性。这会打印很多信息，且输出的风格不是很友好，通常用于调试。
DESCRIBE EXTENDED page_view PARTITION (ds='2016-08-08'); #列出列和分区的所有属性。这也会打印出许多信息，通常也是用于调试。

3、修改表

ALTER TABLE old_table_name RENAME TO new_table_name; #对已有的表进行重命名。如果表的新名字存在，则报错
ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');#对已有表增加列

4、删除表和分区

DROP TABLE table_name;
ALTER TABLE table_name DROP PARTITION (dt='2016-08-08')

5、加载数据

• 从本地加载数据

    LOAD DATA LOCAL INPATH /tmp/pv_2016-06-08_us.txt INTO TABLE page_view PARTITION(date='2016-06-08', country='US')

• 从hdfs加载数据

 LOAD DATA INPATH '/user/data/pv_2016-06-08_us.txt' INTO TABLE page_view PARTITION(date='2016-06-08', country='US')

• 创建一个“外部表”来指向一个特定的HDFS路径

                page_url STRING, referrer_url STRING,
                ip STRING COMMENT 'IP Address of the User',
                country STRING COMMENT 'country of origination')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12'
STORED AS TEXTFILE
LOCATION '/user/data/staging/page_view';

hadoop dfs -put /tmp/pv_2016-06-08.txt /user/data/staging/page_view

FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2016-06-08', country='US')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip
WHERE pvs.country = 'US';

6、连接

• 外链接
  LEFT OUTER JOIN：左保留
  RIGHT OUTER JOIN：右保留
  FULL OUTER JOIN：两端都保留
  一般为了提高查询效率，把小表放左边可以先读入内存中，大表放右边从磁盘遍历读取
  7、聚合

INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;

8、多表/文件插入

FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum
    SELECT pv_users.gender, count_distinct(pv_users.userid)
    GROUP BY pv_users.gender

INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum'
    SELECT pv_users.age, count_distinct(pv_users.userid)
    GROUP BY pv_users.age;

9、插入到本地文件

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum'
SELECT pv_gender_sum.*
FROM pv_gender_sum;