hive内部表和外部表创建分区 hive中的内部表和外部表

1.内部表/外部表
管理表（内表，也叫托管表），外部表（外表）：建表时，有EXTERNAL关键字的就是外部表。在DROP TABLE时，外表的数据是不会被删除的，内表的数据会被删除，但两者对应的元数据（metadata）是都会被删除的
如果数据只是给Hive用，那么建议建立内表
如果数据还可能会给Hive以外的程序使用，或者数据本身就是Hive以外的程序建立的，Hive只是共享使用，对于这两种场景则建议建立外表

区别：
内部表数据由Hive自身管理，外部表数据由HDFS管理；
内部表数据存储的位置hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定；
删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；
对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）

外表可以指定路径：

create external table demo (
 id int,
 name string,
 salary double
 )
 row format delimited
 fields terminated by ‘\t’
 location ‘/user/wangliming/hive/user_info’;

2.分区表与动态分区
Hive在查询时通常是做全表扫描的，而一个好的分区设计可以避免全表扫描并且可以大大减少Hive的扫描数据量
最常见的分区表是按天建立分区（当然也有使用时间空间两个维度进行分区的，第一个分区是按天建立的，第二个则按地区建立）
分区本质上就是一层目录
当然，分区也不能过多，否则就会大大增加NameNode的压力（Hadoop HDFS更适合存储与处理少量的大文件而不是大量的小文件），这时候一般就会结合使用分桶机制
分区是一种特殊的列，这种列的值不在数据文件中，而是通过目录名称读取的，分区实际上正对应了HDFS中的目录

create table access_log (
 id int,
 name string,
 url string
 )
 partitioned by (dt string)
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ‘\t’ ;

退出hive
vi access_log
1 百度 http//:www.baidu.com
2 腾讯 http//:www.qq.com
3 谷歌 http//:www.geogle.com

进入hive

use wangliming;
load data local inpath ‘/home/wangliming/hive/access_log’ into table access_log partition(dt=‘20181106’);
select * from access_log where dt=‘20181106’ limit 10; – 标准查询
insert into table access_log
 partition(dt=‘20181108’)
 select * from (
 select 1,‘百度’,‘http://www.baidu.com’
 union all
 select 2,‘腾讯’,‘http://www.qq.com’
 union all
 select 3,‘谷歌’,‘http://www.baidu.com’
 )t;

查看某张表有哪些分区
 show partitions access_log;动态分区（默认关闭，使用静态分区）
 set hive.exec.dynamic.partition=true; （必须保证有一个静态分区。通常第一个）
 set hive.exec.dynamic.partition.mode=nonstrict; （所有分区都可以为动态）动态分区第一种情况（set hive.exec.dynamic.partition=true; 默认值为false）（必需起别名）
 insert into table access_log_2
 partition(dt=‘20181106’,province)
 select uid,name,url,province from (
 select 1 as uid,‘百度’ as name,‘http://www.baidu.com’ as url,‘ShanDong’ as province
 union all
 select 2 as uid,‘腾讯’ as name,‘http://www.qq.com’ as url,‘HeiBei’ as province
 union all
 select 3 as uid,‘谷歌’ as name,‘http://www.geogle.com’ as url,‘AnHui’ as province
 )t;动态分区第二种情况（set hive.exec.dynamic.partition.mode=nonstrict; 默认值为strict ）
 insert into table access_log_2
 partition(dt,province)
 select uid,name,url,dt,province from (
 select 1 as uid,‘百度’ as name,‘http://www.baidu.com’ as url,‘20181107’ as dt,‘ShanDong’ as province
 union all
 select 2 as uid,‘腾讯’ as name,‘http://www.qq.com’ as url,‘20181108’ as dt,‘HeiBei’ as province
 union all
 select 3 as uid,‘谷歌’ as name,‘http://www.geogle.com’ as url,‘20181109’ as dt,‘AnHui’ as province
 )t;

3.分桶表（详尽版）
分区不能过多，否则就会大大增加NameNode的压力（Hadoop HDFS更适合存储与处理少量的大文件而不是大量的小文件），这时候一般就会结合使用分桶机制
分桶可以使数据均匀分布，避免了创建大量小文件的情景；提高了查询效率（尤其是连接查询map side join）；另外分桶表也特别适用于抽样查询场景
桶的数量一般对应reducer的数量，因为有时候虽然没有reducer，但是最终的结果也依然会分桶
分区对应的字段实际上是目录，不会存储在数据文件中，但是分桶对应的字段就是实际的数据字段

创建一个分区分桶表

CREATE TABLE mb(
uid int,
id int,
name string,
url string)
 PARTITIONED BY (
dt string)
 CLUSTERED BY (
 uid)
 INTO 3 BUCKETS
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ‘\t’ ;

以下属性必须设置为true
set hive.enforce.bucketing=true;
否则，我们需要设置和分桶个数相匹配的Reducer数目，如set mapred.reduce.tasks=3，并且查询的时候需要添加cluster by 子句

退出hive
vi access_log_bucket
10000 1 刘十三 http://www.baidu.com/ 10001 2 张三 http://www.le.com/ 10002 3 李四 http://www.google.com/ 10003 4 王五 http://www.ip138.com/ 10004 5 赵六 http://www.qq.com/ 10005 6 小明 http://www.qq.com/ 10006 7 小红 http://www.qq.com/ 10007 8 小强 http://www.qq.com/ 10008 9 小刚 http://www.qq.com/ 10009 10 小小 http://www.qq.com/

进入hive
 use liuhongliang;
 load data local inpath ‘access_log_bucket’ overwrite into table mb partition (dt=20181107);dfs -ls /apps/hive/warehouse/liuhongliang.db/mb/dt=20181107;

下面只有一个数据文件’access_log_bucket
因为load只是简单的复制文件，不会触发MR Job，所以使用load方式导入的数据不会被自动分桶

insert overwrite table mb
 partition(dt=20181106)
 select uid, id, name, url
 from mb
 where dt=20181107;dfs -ls /apps/hive/warehouse/liuhongliang.db/mb/dt=20181106;
 dfs -cat /apps/hive/warehouse/liuhongliang.db/mb/dt=20181106/000000_0;

我们发现，虽然分区下面有三个文件（3个桶），但实际上数据依然没有分桶，所有数据还是只存储在第一个文件中

insert overwrite table mb
 partition(dt=20181105)
 select uid, id, name, url
 from mb
 where dt=20181107
 cluster by uid;dfs -ls /apps/hive/warehouse/liuhongliang.db/mb/dt=20181105;
 dfs -cat /apps/hive/warehouse/liuhongliang.db/mb/dt=20181105/000002_0;

这时，数据才会真正分桶

insert overwrite table mb
 partition(dt=20181104)
 select uid, id, name, url
 from mb
 where dt=20181105;dfs -ls /apps/hive/warehouse/liuhongliang.db/mb/dt=20181104;
 dfs -cat /apps/hive/warehouse/liuhongliang.db/mb/dt=20181104/000000_0;

使用load方式导入的数据肯定不会自动分桶，然而使用select方式导入的数据也不一定会自动分桶，所以我们应该使用cluster by来保证分桶，但如果我们不使用cluster by，就必须保证select的数据的来源必须是经过真正分桶的，或者像下面这样，数据是来源于一系列计算的最终结果的

insert overwrite table mb
 partition(dt=20181103)
 select * from (
 SELECT 10010, 1, ‘小红’, ‘http://www.baidu.com/’
 union all
 SELECT 10011, 3, ‘小强’, ‘http://www.google.com/’
 union all
 SELECT 10012, 2, ‘小明’, ‘http://www.sogou.com/’
 )t;dfs -ls /apps/hive/warehouse/liuhongliang.db/mb/dt=20181103;
 dfs -cat /apps/hive/warehouse/liuhongliang.db/mb/dt=20181103/000000_0;