Hive——DDL之创建表（内外部表，分区表，分桶表）
- DDL之创建表命令
- 1.内部表（管理表）
- 1）普通命令创建
- 2）根据查询结果创建
- 3）根据已经存在的表创建表结构
- 2.外部表
- 1）使用命令创建
- 2）使用desc命令查看表
- 3) drop掉外部表，查看HDFS上表数据
- 4）内部表和外部表互相转换
- 3.分区表
- 1）普通命令创建分区表
- 2）静态分区插入
- 3）动态分区插入
- 4）查看HDFS的组织形式
- 4.分桶表
- 1）普通命令创建分桶表
- 2) 查询分桶表内容
- 3） 查看HDFS上的组织形式
- 分区表和分桶表的区别
- 1）分区表对应的是HDFS上的一个目录下，按照分区结果的多个目录；分桶表对应的是HDFS上的一个目录下，按照分桶结果的多个文件。
- 2）分区表和分桶表都可以提高检索的速度以减轻Hive没有主键带来的弊端，但是分区表更偏向于相同数据的分组，比如时间，班级，部门号这种相同的数据，而如果按照id这种唯一分区则没有意义，这时候就需要使用分桶表
DDL之创建表命令
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]1.内部表（管理表）
hive默认创建的表（不加external关键字）都是内部表（管理表），Hive默认情况下会把这类表的数据存储在hive-site.xml文件中的hive.metastore.warehouse.dir配置项所定义的目录下，如下，即/user/hive/warehouse\ 目录下，当我们删除**内部表（管理表）**的时候，Hive会将这个表的元数据和表数据一并删除，内部表不适合在使用共享数据时使用。
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>1）普通命令创建
CREATE table student(id int,name string)
row format delimited fields terminated by '\t';使用desc命令查看表属性
desc formatted student;可以看到 Table Type 是 MANAGED_TABLE
2）根据查询结果创建
查询的结果会插入到新创建的表中，查出来哪些字段就会在新表里创建哪些字段并插入对应的数据
CREATE table student2 as select * from student;3）根据已经存在的表创建表结构
会创建出一个相同表结构的新表，但是是空表，没有数据。
create table student3 like student;2.外部表
在创建表时，加上了external关键字，就是外部表。外部表和内部标的区别在于：
1)概念本质上
内部表数据自己的管理的 自己对数据有删除的权利的
在进行表删除的时候 数据和元数据一并删除的
外部表只是对hdfs的一个目录的数据进行关联 仅仅有使用权 没有删除的权利
外部表在进行删除的时候 只删除元数据 原始数据（表中的数据）是不会删除的
2)应用场景上
外部表一般用于存储原始数据 公共数据
内部表一般用于存储某一个模块的中间结果数据
3)存储目录上
外部表一般在进行建表时候需要手动指定表的数据目录为共享资源目录
用location关键字指定
1）使用命令创建
指定external关键字，并使用location关键字指定数据文件所在目录
create external table stu_external(
id int,
name string)
row format delimited fields terminated by '\t'
location '/student';2）使用desc命令查看表
desc formatted stu_external;可以看到 Table Type 是 EXTERNAL_TABLE
3) drop掉外部表，查看HDFS上表数据
drop table stu_external;查看HDFS，发现之前指定的 '\student’下数据文件还在
4）内部表和外部表互相转换
内部表—>外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');外部表—>内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');3.分区表
分区表对应HDFS上一个目录，而分区表的分区就是这个目录下的分目录，把一个很大的数据集分成多个小目录，这样在使用where关键字指定分区字段的时候可以提高效率
1）普通命令创建分区表
create table student_ptn(id int, name string, sex string, age int, department
string) partitioned by(city string) row format delimited fields terminated by
','2）静态分区插入
insert命令
insert into table student_ptn partition (city = 'beijing') select id,name,sex,age,department from students;load data命令
load data local inpath "/home/hadoop/testdata/students.txt" into table
student_ptn partition(city="shenzhen");3）动态分区插入
假如我们有非常多的分区，一个个手动指定是非常耗费时间的，所以我们可以采用动态分区插入的方式，让hive帮我们完成指定分区的过程
在使用动态分区之前，需要设置一些参数
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;创建一个新的分区表
create table student_ptn2(id int, name string, sex string, department
string) partitioned by(age int) row format delimited fields terminated by
',';使用insert命令动态插入
（值得注意的是：使用insert命令动态插入的时候，查询语句select查询的字段名需要放到最后与分区字段名对应）
insert into table student_ptn2 partition (age) select id,name,sex,department,age from
students;4）查看HDFS的组织形式
4.分桶表
分区表对应HDFS上的多个目录，而分桶表是对应HDFS上一个目录的多个文件。
1）普通命令创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';2) 查询分桶表内容
Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。可以看到表内容被分成了四块，这四块对应了我们建表语句的 into 4 buckets
3） 查看HDFS上的组织形式
可以在该表目录下看到四个数据文件，也就是开头说到的，分桶表对应的是该目录下的多个文件，而分区表对应的是多个目录
分区表和分桶表的区别
1）分区表对应的是HDFS上的一个目录下，按照分区结果的多个目录；分桶表对应的是HDFS上的一个目录下，按照分桶结果的多个文件。
2）分区表和分桶表都可以提高检索的速度以减轻Hive没有主键带来的弊端，但是分区表更偏向于相同数据的分组，比如时间，班级，部门号这种相同的数据，而如果按照id这种唯一分区则没有意义，这时候就需要使用分桶表
