hive(四) -- 内(外)部表、分区和分桶表

-- 创建内部表 加载数据
create table t_user_inner(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';
load data local inpath '/root/user.txt' into table t_user_inner;
-- 查看表信息
desc formatted t_user_inner ;
-- 创建外部表 加载数据
create external table t_user_ext(
         id int,
         uname string,
         pwd string,
         sex string,
         age int
)row format delimited fields terminated by ',';
load data local inpath '/root/user.txt' into table t_user_ext;
-- 查看表信息
desc formatted table t_user_ext;


-- 删除内部表  数据被删除了
drop  table t_user_inner;
-- 删除外部表  数据并没有被删除
drop  table t_user_ext;

-- 再次重新创建 t_user_ext 可以直接查询数据
select * from  t_user_ext;

-- 将t_user_ext 转换为内部表
alter table t_user_ext set tblproperties('EXTERNAL'='FALSE'); -- 要求KV的大小写
-- 查询表信息发现 Table Type: MANAGED_TABLE
desc formatted t_user_ext; 
--将t_user_ext 转换为外部表
alter table t_user_ext set tblproperties('EXTERNAL'='true');
-- 查询表信息发现 Table Type:EXTERNAL_TABLE
desc formatted t_user_ext;

-- 创建学生表 分区字段为年级grade
CREATE TABLE  t_student (
    sid int,
    sname string
 ) partitioned by(grade int)   -- 指定分区字段
row format delimited fields terminated by ',';
-- 注意∶分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。
select * from t_student;
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
+----------------+------------------+------------------+

stu01.txt
1,zhangsan,1
2,lisi,1
3,wangwu,1

stu02.txt
4,zhaoliu,2
5,lvqi,2
6,maba,2

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3

-- 静态分区需要用户手动加载数据 并指定分区
load  data local  inpath '/root/stu01.txt' into table t_student partition(grade=1);
load  data local  inpath '/root/stu02.txt' into table t_student partition(grade=2);
load  data local  inpath '/root/stu03.txt' into table t_student partition(grade=3);
-- 查询
select * from t_student where grade=1;
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
| 1              | zhangsan         | 1                |
| 2              | lisi             | 1                |
| 3              | wangwu           | 1                |
+----------------+------------------+------------------+

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3
10.aaa,4

load  data local  inpath '/root/stu03.txt' overwrite into table t_student partition(grade=3);

select * from t_student where grade=3;
-- 最后一条记录虽然写的是4 但是 放到了年级3分区下 效果也是年级3
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
| 7              | liuyan           | 3                |
| 8              | tangyan          | 3                |
| 9              | jinlian          | 3                |
| 10             | aaa              | 3                |
+----------------+------------------+------------------+

-- 创建学生表 分区字段为年级grade 班级clazz
CREATE TABLE  t_student02 (
    sid int,
    sname string
 ) partitioned by(grade int,clazz int)   -- 指定分区字段
row format delimited fields terminated by ',';

1年级1班
stu0101.txt  
1,zhangsan,1,1
2,lisi,1,1

1年级2班
stu0102.txt
3,wangwu,1,2

2年级1班
stu0201.txt
4,zhangsan,2,1
5,lisi,2,1
6,maba,2,1

3年级1班
stu0301.txt
7,liuyan,3,1
8,tangyan,3,1
3年级2班
9,dalang,3,2
10,jinlian,3,2

load  data local  inpath '/root/stu0101.txt' into table t_student02 partition(grade=1,clazz=1);
load  data local  inpath '/root/stu0102.txt' into table t_student02 partition(grade=1,clazz=2);
load  data local  inpath '/root/stu0201.txt' into table t_student02 partition(grade=2,clazz=1);
load  data local  inpath '/root/stu0301.txt' into table t_student02 partition(grade=3,clazz=1);
load  data local  inpath '/root/stu0302.txt' into table t_student02 partition(grade=3,clazz=2);

select * from t_student02 where grade=1 and clazz=2;
+------------------+--------------------+--------------------+--------------------+
| t_student02.sid  | t_student02.sname  | t_student02.grade  | t_student02.clazz  |
+------------------+--------------------+--------------------+--------------------+
| 7                | liuyan             | 3                  | 1                  |
| 8                | tangyan            | 3                  | 1                  |
+------------------+--------------------+--------------------+--------------------+

-- 查看分区 
show partitions t_student02;
-- 添加分区
alter table t_student02 add  partition (grade=4,clazz=1);
-- 删除分区
alter table t_student02 drop  partition (grade=4,clazz=1);

-- 临时设置 重新连接需要重新设置
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;

设置为true表示开启动态分区的功能（默认为false） 
--hive.exec.dynamic.partition=true; 

设置为nonstrict，表示允许所有分区都是动态的（默认为strict） 严格模式至少有一个静态分区
-- hive.exec.dynamic.partition.mode=nonstrict; 

每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，
如果使用默认 值100，则会报错 
--hive.exec.max.dynamic.partition.pernode=100; 

一个动态分区创建可以创建的最大动态分区个数（默认值1000） 
--hive.exec.max.dynamic.partitions=1000; 

全局可以创建的最大文件个数（默认值100000） 
--hive.exec.max.created.files=100000; 

当有空分区产生时，是否抛出异常（默认false） 
-- hive.error.on.empty.partition=false;

创建文件并上传

student.txt  

1,zhangsan,1,1
2,lisi,1,1
3,wangwu,1,2
4,zhangsan,2,1
5,lisi,2,1
6,maba,2,1
7,liuyan,3,1
8,tangyan,3,1
9,dalang,3,2
10,jinlian,3,2

-- 将文件上传到hdfs根目录
hdfs dfs -put student.txt  /stu

创建外部表指向文件(相当于临时表)  
create external table t_stu_e(
    sid int,
    sname string,
    grade int,
    clazz int
)row format delimited fields terminated by ","
location "/stu";

创建动态分区表
create  table  t_stu_d(
    sid int,
    sname string
)partitioned by (grade int,clazz int)
row format delimited fields terminated by ",";

查询外部表将数据动态存入分区表中
insert overwrite table t_stu_d partition (grade,clazz) select  *  from t_stu_e ;

select * from t_stu_d;

bucket num = hash_function(bucketing_column) mod   num_buckets
 分隔编号      哈希方法(分桶字段)              取模   分桶的个数

1.准备person.txt上传到hdfs
2.创建外部表指向person.txt
3.创建分桶表
4.查询外部表将数据加载到分桶表中

person.txt 
public class Test02 {
    public static void main(String[] args) {
        for (int i = 1; i <= 10000; i++) {
            System.out.println(i + "," + "liuyan" + (new Random().nextInt(10000) + 10000));
        }
    }
}

 hdfs dfs -mkdir /person
hdfs dfs -put person.txt /person

2.创建外部表指向person.txt
create external table  t_person_e(
     id int,
     pname string
) row format delimited fields terminated by ","
    location "/person";

select  * from t_person_e;

create table  t_person(
    id int,
    pname string
)clustered by(id) sorted by (pname) into 24 buckets
row format delimited fields terminated by ",";

insert overwrite table t_person select * from t_person_e ;

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 
-- x表示从哪个bucket开始抽取。
   例如，table总bucket数为32，tablesample(bucket 3 out of 16)
   32 / 16  = 2  代表16桶为一组  抽取 第一组的第3桶  抽取第二组的第3桶 也就是第19桶
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
    tablesample(bucket 3 out of 64)
    32/64 = 2分之一      64桶为一组  不够一组 取第三桶的 前百分之50
    
    select * from t_person tablesample(bucket 4 out of 12); 
    24/12 抽取2桶数据      12桶一组 抽取 第一组第4桶 第二组 第4桶 4+12 =16桶