hive基础概念总结(1)

1.关于内部表和外部表

表（内部表）数据存放在统一的/user/hive/warehouse目录下； drop表时会将表的数据及表的元信息全部清空。

示例代码如下：

CREATE TABLE page_view(viewTime INT, userid BIGINT,  
     page_url STRING, referrer_url STRING,  
     ip STRING COMMENT 'IP Address of the User')  
 COMMENT 'This is the page view table'  
 PARTITIONED BY(dt STRING, country STRING)  
 ROW FORMAT DELIMITED  
 FIELDS TERMINATED BY '\001'  
STORED AS SEQUENCEFILE;  

外部表：表数据可以在hdfs的任意目录，没有统一约束； drop时只清除表的元信息，表的数据文件不会改变。

CREATE EXTERNAL TABLE tab_ip_ext(id int, name string,  
     ip STRING,  
     country STRING)  
 ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
 STORED AS TEXTFILE  
 LOCATION '/external/hive';

其实就是加一个external关键字，还需要用Location关键字指定自定义的数据存放位置。

把ip.data这个文件上传到这个外部表中：

load data local inpath '/home/hadoop/hivetestdata/ip.txt'  into table tab_ip_ext;

 

2.hive分区表（静态分区和动态分区）

hive中创建分区表没有什么复杂的分区类型(范围分区、列表分区、hash分区、混合分区等)。分区列也不是表中的一个实际的字段，而是一个或者多个伪列。意思是说在表的数据文件中实际上并不保存分区列的信息与数据。

为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。

分区是表的部分列的集合，可以为频繁使用的数据建立分区，这样查找分区中的数据时就不需要扫描全表，这对于提高查找效率很有帮助。

示例代码如下：

create table partition_test
(member_id string,
name string)
partitioned by (
stat_date string,
province string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;

这个例子中创建了stat_date和province两个字段作为分区列。通常情况下需要先预先创建好分区，然后才能使用该分区。
alter table partition_test add partition (stat_date='20110728',province='zhejiang');

每一个分区都会有一个独立的文件夹，下面是该分区所有的数据文件。在这个例子中stat_date是主层次，province是副层次，所有stat_date='20110728'，而province不同的分区都会在/user/hive/warehouse/partition_test/stat_date=20110728 下面，而stat_date不同的分区都会在/user/hive/warehouse/partition_test/ 下面。

向partition_test的分区中插入测试数据，

示例代码如下：

create table partition_test_input
(stat_date string,
member_id string,
name string,
province string)
COMMENT 'This is the partion test table'  
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
STORED AS TEXTFILE
;

load data local inpath '/home/hadoop/partion/partition_test_input.txt'  into table partition_test_input;

insert overwrite table partition_test partition(stat_date='20110728',province='henan')
select member_id,name from partition_test_input where stat_date='20110728' and province='henan';

还可以同时向多个分区插入数据，

from partition_test_input

insert overwrite table partition_test partition (stat_date='20110526',province='liaoning')
select member_id,name where stat_date='20110526' and province='liaoning'
insert overwrite table partition_test partition (stat_date='20110728',province='sichuan')
select member_id,name where stat_date='20110728' and province='sichuan'
insert overwrite table partition_test partition (stat_date='20110728',province='heilongjiang')
select member_id,name where stat_date='20110728' and province='heilongjiang';

特别要注意，在其他数据库中，一般向分区表中插入数据时系统会校验数据是否符合该分区，如果不符合会报错。而在hive中，向某个分区中插入什么样的数据完全是由人来控制的，因为分区键是伪列，不实际存储在文件中，所以要确保数据列和表机构一一对应。

 

 

下面介绍一下动态分区，因为按照上面的方法向分区表中插入数据，如果源数据量很大，那么针对一个分区就要写一个insert，非常麻烦。况且在之前的版本中，必须先手动创建好所有的分区后才能插入，这就更麻烦了，你必须先要知道源数据中都有什么样的数据才能创建分区。
使用动态分区可以很好的解决上述问题。动态分区可以根据查询得到的数据自动匹配到相应的分区中去。 
使用动态分区要先设置hive.exec.dynamic.partition参数值为true，默认值为false，即不允许使用

set hive.exec.dynamic.partition=true;

 

动态分区的使用方法很简单，假设我想向stat_date='20110728'这个分区下面插入数据，至于province插入到哪个子分区下面让数据库自己来判断，那可以这样写：

insert overwrite table partition_test partition(stat_date='20110728',province)
select member_id,name,province from partition_test_input where stat_date='20110728';

stat_date叫做静态分区列，province叫做动态分区列。select子句中需要把动态分区列按照分区的顺序写出来，静态分区列不用写出来。这样stat_date='20110728'的所有数据，会根据province的不同分别插入到/user/hive/warehouse/partition_test/stat_date=20110728/下面的不同的子文件夹下，如果源数据对应的province子分区不存在，则会自动创建，非常方便，而且避免了人工控制插入数据与分区的映射关系存在的潜在风险。

 

动态分区可以允许所有的分区列都是动态分区列，但是要首先设置一个参数hive.exec.dynamic.partition.mode 

它的默认值是strick，即不允许分区列全部是动态的，这是为了防止用户有可能原意是只在子分区内进行动态建分区，但是由于疏忽忘记为主分区列指定值了，这将导致一个dml语句在短时间内创建大量的新的分区（对应大量新的文件夹），对系统性能带来影响。

set hive.exec.dynamic.partition.mode=nostrick;

 

再介绍3个参数：
hive.exec.max.dynamic.partitions.pernode （缺省值100）：每一个mapreduce job允许创建的分区的最大数量，如果超过了这个数量就会报错
hive.exec.max.dynamic.partitions （缺省值1000）：一个dml语句允许创建的所有分区的最大数量
hive.exec.max.created.files （缺省值100000）：所有的mapreduce job允许创建的文件的最大数量

 

为了让分区列的值相同的数据尽量在同一个mapreduce中，这样每一个mapreduce可以尽量少的产生新的文件夹，可以借助distribute by的功能，将分区列值相同的数据放到一起。

insert overwrite table partition_test partition(stat_date,province)
select member_id,name,stat_date,province from partition_test_input distribute by stat_date,province;

为了方便测试，可以对于分区表执行删除操作，

ALTER TABLE partition_test DROP IF EXISTS PARTITION (stat_date='20110728',province='heilongjiang');

 

3.hive 高级查询

Join表连接

两个表m,n之间按照on条件连接，m中的一条记录和n中的一条记录组成一条新记录。

join等值连接（内连接），只有某个值在m和n中同时存在时。

left outer join左外连接，左边表中的值无论是否在b中存在时，都输出；右边表中的值，只有在左边表中存在时才输出。

right outer join和left outer join相反。

left semi join类似exists。即查找a表中的数据，是否在b表中存在，找出存在的数据。

mapjoin：在map端完成join操作，不需要用reduce，基于内存做join，属于优化操作。

For semi join，

 示例代码如下：

select s.id,s.num from
(select id,num from m)s
left semi join
(select id,num from n)t
on s.id=t.id;

For map join(map side join)，

在map端把小表加载到内存中，然后读取大表，和内存中的小表完成连接操作。其中使用了分布式缓存技术。

优点 
不消耗集群的reduce资源（reduce相对紧缺）。 
减少了reduce操作，加快程序执行。 
降低网络负载。

缺点 
占用部分内存，所以加载到内存中的表不能过大，因为每个计算节点都会加载一次。 
生成较多的小文件。

执行流程 
 
从大表读取数据，执行where条件。把小表加载到内存中，每读取大表中的一条数据，都要和内存中的小表数据进行比较。

第一种方式，自动方式 

配置以下参数 
hive自动根据sql语句，选择使用common join或者map join

set hive.auto.convert.join=true;

hive.mapjoin.smalltable.filesize默认值是25mb

第二种方式，手动指定

 select /*+mapjoin(n)*/ m.col, m.col2, n.col3 from m join n on m.col=n.col;

 注意：/*+mapjoin(n)*/不能省略，只需替换表名n值即可

示例代码如下：

select c.city,p.province 
from
(select province,city from city)c
join
(select province from province)p
on c.province=p.province;
*********************************
mapjoin手动方式
select /*+mapjoin(p)*/ c.city,p.province 
from
(select province,city from city)c
join
(select province from province)p
on c.province=p.province;

简单总结一下，map join的使用场景：  

 1: 有一个极小的表<1000行

 2: 需要做不等值join操作（a.x < b.y 或者 a.x like b.y等）

这种操作如果直接使用join的话语法不支持不等于操作，hive语法解析会直接抛出错误

如果把不等于写到where里会造成笛卡尔积，数据异常增大，速度会很慢。甚至会任务无法跑成功

根据mapjoin的计算原理，MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配。这种情况下即使笛卡尔积也不会对任务运行速度造成太大的效率影响

而且hive的where条件本身就是在map阶段进行的操作，所以在where里写入不等值比对的话，也不会造成额外负担。

如此看来，使用MAPJOIN开发的程序仅仅使用map一个过程就可以完成不等值join操作，效率还会有很大的提升。

 示例代码如下：

select /*+ MAPJOIN(a) */
a.start_level, b.*
from dim_level a
join (select * from test) b
where b.xx>=a.start_level and b.xx<end_level;

 

4.hive 排序比较

order by/sort by/cluster by

distribute 分散数据 ，distribute by col – 按照col列把数据分散到不同的reduce

Sort排序 ，sort by col – 按照col列把数据排序

示例代码如下：

select col1,col2 from M distribute by col1 sort by col1 asc,col2 desc

两者结合出现，确保每个reduce的输出都是有序的。

 

distribute by与group by对比 

都是按key值划分数据 
都使用reduce操作 
**唯一不同的是**distribute by只是单纯的分散数据，而group by把相同key的数据聚集到一起，后续必须是聚合操作。

 

order by与sort by 对比 
order by是全局排序 
sort by只是确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。

 

cluster by

把有相同值的数据聚集到一起，并排序。

效果等价于distribute by col sort by col 

cluster by col <==> distribute by col sort by col