开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第6天，点击查看活动详情
第五章 DDL表操作
5.1 查看表
hive> show tables;
在当前的库当中，查看其他的库的表
hive> show tables in myhive2;
条件查询
hive> show tables "stud*";
格式化查看
hive> desc formatted student;
新版本的一些操作：
hive (mydb)> show databases;
OK
database_name
default
mydb
myhivedb
Time taken: 0.013 seconds, Fetched: 3 row(s)
hive (mydb)> use myhivedb;
OK
Time taken: 0.015 seconds
hive (myhivedb)> show tables;
OK
tab_name
student
Time taken: 0.017 seconds, Fetched: 1 row(s)
hive (myhivedb)> show tables in default;
OK
tab_name
Time taken: 0.019 seconds
hive (myhivedb)> show tables in mydb;
OK
tab_name
student
Time taken: 0.021 seconds, Fetched: 1 row(s)
hive (myhivedb)> show tables "stu";
OK
tab_name
Time taken: 0.019 seconds
hive (myhivedb)> show tables "stu*";
OK
tab_name
student
Time taken: 0.022 seconds, Fetched: 1 row(s)
hive (myhivedb)> desc formatted student;
OK
col_name data_type comment
# col_name data_type comment
id int
name string
sex string
age int
department string
# Detailed Table Information
Database: myhivedb
OwnerType: USER
Owner: root
CreateTime: Fri Sep 24 18:34:21 CST 2021
LastAccessTime: UNKNOWN
Retention: 0
Location: hdfs://hadoop10/user/hive/warehouse/myhivedb.db/student
Table Type: MANAGED_TABLE
Table Parameters:
bucketing_version 2
numFiles 1
numRows 0
rawDataSize 0
totalSize 483
transient_lastDdlTime 1632479669
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.053 seconds, Fetched: 35 row(s)
hive (myhivedb)>
5.2 创建表
5.2.1 创建表理论
1、建表语句语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
[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]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[ [ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
2、相关名词解释
1、CREATE TABLE：创建一个指定名字的表。
2、EXTERNAL：关键字可以让用户创建一个外部表
在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。这是一道非常经典的面试题！！
3、COMMENT：可以为表与字段增加描述
4、PARTITIONED BY：创建分区表
在 Hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入 partition 概念。
5、CLUSTERED BY:创建分桶表
对于每一个表（table）或者分区，Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
6、SORTED BY： 对桶中的一个或多个列排序，用的也不多。
LIKE：允许用户复制现有的表结构，但是不复制数据。
7、ROW FORMAT ：用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列， 用户在指定表的列的同时也会指定自定义的 SerDe ，Hive 通过 SerDe 确定表具体的列的数据。
注： SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。
8、STORED AS TEXTFILE | SEQUENCEFILE | RCFILE :
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE，默认也是 textFile 格式，可以通过执行命令 set hive.default.fileformat，进行查看，如果数据需要压缩，使用 STORED AS SEQUENCEFILE。RCFILE 是一种行列存储相结合的存储方式。
常用存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE （列式存储格式文件）
9、LOCATION：指定数据文件存放的 HDFS 目录，不管内部表还是外表，都可以指定。不指定就在默认的仓库路径。
10、AS： 后面可以跟查询语句，根据查询结果创建表
11、LIKE： 复制表结构，但是不复制表数据
数据类型案例：
id info city
1 "zhangsan",22,AA beijing
2 22,33,44 shanghai
中间的参数可以抽象成下面的数据类型
"zhangsan",22,AA : struct<name:string,age:int,department:string>
22,33,44 : array<int>
fileformat相关操作：
hive (myhivedb2)> set hive.default.fileformat;
hive.default.fileformat=TextFile
hive (myhivedb2)> set hive.default.fileformat=SEQUENCEFILE;
hive (myhivedb2)> set hive.default.fileformat;
hive.default.fileformat=SEQUENCEFILE
hive (myhivedb2)> set hive.default.fileformat=TextFile;
hive (myhivedb2)> set hive.default.fileformat;
hive.default.fileformat=TextFile
3、给上面的内容总结一下，其实创建表主要就是以下几个方面的内容
1、创建内部表
2、创建外部表
3、创建分区表
4、创建分桶表
5、从查询的结构创建新表 CTAS
6、通过like复制已有的表的结构创建新表
5.2.2 创建表具体操作
1、创建内部表
create table student(id int, name string, sex string, age int, department string) row format delimited fields terminated by ",";
元数据库中显示的是 MANAGED_TABLE 。管理表就是内部表
往student表中放数据：
load data local inpath "/home/data/student.txt" into table student;
2、创建外部表
external
内部表和外部表的区别：
删除表的时候，内部表会删除元数据信息和真实数据信息，外部表只会删除描述信息
内部表和外部表的选择：
1、如果数据已经存储在hdfs上，然后使用hive去进行分析，并且还有可能有其他的计算引擎使用到这份数据，那么请你创建外部表。
2、如果一份数据仅仅是hive使用来进行分析，可以创建内部表。
推荐：
1、创建内部表的时候，建议大家用默认的路径
2、创建外部表的时候，指定location的路径。
// 没有指定外部路径, 表的数据目录存储在默认的仓库路径中
create external table student_ext_1(id int, name string, sex string, age int, department string) row format delimited fields terminated by ",";
// 指定一个不存在的外部路径: 创建表的时候，会自动给你创建表目录
create external table student_ext_2(id int, name string, sex string, age int, department string) row format delimited fields terminated by "," location "/student_ext_2";
// 指定一个已经存在的目录: 并且有数据
//在linux中执行
hadoop fs -mkdir -p /student_ext_3
hadoop fs -put /home/data/student.txt /student_ext_3
//在hive命令行中执行
create external table student_ext_3(id int, name string, sex string, age int, department string) row format delimited fields terminated by "," location "/student_ext_3";
3、创建分区表
// 创建只有一个分区字段的分区表：
create table student_ptn(id int, name string, sex string, age int, department string) partitioned by (city string comment "partitioned field") row format delimited fields terminated by ",";
表类型 ： Table Type: MANAGED_TABLE
load data local inpath "/home/data/student.txt" into table student_ptn; XXXXXXX
hive> load data local inpath "/home/data/student.txt" into table student_ptn;
FAILED: SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned
老的版本报上面的错。
注意：1.x、2.x会报错如上面。
但是3.x会给数据加载到某个默认的分区里面，分区的名字：city=__HIVE_DEFAULT_PARTITION__
路径如下：
/user/hive/warehouse/myhivedb2.db/student_ptn/city=__HIVE_DEFAULT_PARTITION__
// 把数据导入到一个不存在的分区，它会自动创建该分区
load data local inpath "/home/data/student.txt" into table student_ptn partition(city="beijing"); √√√√ 。注意 版本区别
//可以给一个文件夹下面的多份数据直接加载到某个分区中。当然不是分区表，也可以。
load data local inpath "/home/data/input" into table student_ptn partition(city="shanghai"); √√√√ 。注意 版本区别
// 把数据导入到一个已经存在的分区
alter table student_ptn add partition (city="chongqing");
load data local inpath "/home/data/student.txt" into table student_ptn partition(city="chongqing");
// 创建有多个分区字段的分区表：
create table student_ptn_date(id int, name string, sex string, age int, department string) partitioned by (city string comment "partitioned field", dt string) row format delimited fields terminated by ",";
// 往分区中导入数据:
load data local inpath "/home/data/student.txt" into table student_ptn_date partition(city="beijing"); //报错
load data local inpath "/home/data/student.txt" into table student_ptn_date partition(city="beijing", dt='2021-11-26'); //正确
问题是：city分区和dt分区是并列关系还是父子级别关系？
平级并列关系 7
父子目录关系 8
// 不能在导入数据的时候指定多个分区定义
load data local inpath "/home/data/student.txt" into table student_ptn_date partition(city="beijing", dt='2021-12-14') partition (city="beijing" , dt='2021-12-13'); XXXXXX
// 添加分区
alter table student_ptn_date add partition(city="beijing", dt='2012-12-14') partition (city="beijing" , dt='2021-12-13'); √√√√√√√√
alter table student_ptn_date add partition(city="chongqing", dt='2012-12-14') partition (city="chongqing" , dt='2021-12-13'); √√√√√√√√
// 查询一个分区表有那些分区
show partitions student_ptn;
show partitions student_ptn_date;
show partitions student;
4、创建分桶表
// 创建一个分桶表
create table student_bucket (id int, name string, sex string, age int, department string) clustered by (department) sorted by (age desc, id asc) into 3 buckets row format delimited fields terminated by ",";
5、从查询的结构创建新表
CTAS： Create Table ... As Select ... 帮查询的结果另存为一张新表
create table ... as select ....
查询例子：
select department, count(*) as total from student group by department;
完整的CTAS语句：
create table dpt_count as select department, count(*) as total from student group by department;
6、通过like复制已有的表的结构创建新表
hive> create table studentlike like student;
5.3 修改表
5.3.1 修改表理论
1、重命名表
语法结构：
ALTER TABLE table_name RENAME TO new_table_name;
2、修改表属性
语法结构：
ALTER TABLE table_name SET TBLPROPERTIES table_properties;
3、修改表的注释
语法结构：
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);
4、增加分区
语法结构：
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
5、删除分区
语法结构：
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
[IGNORE PROTECTION] [PURGE]; -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)
5.3.2 修改表具体操作示例
1、修改表名
alter table studentlike rename to studentss;
2、修改字段
添加字段：
alter table student add columns (city string, dt string);
删除字段：
alter table student drop columns (city); XXXXXXXX
替换字段：
alter table student replace columns (id int, name string, sex string, age int);
改变列的定义：
alter table student change id newid string comment "new id";
改变列的顺序：
alter table student change sex sex string first;
alter table student change name name string after sex;
上面两行语句操作如下：
hive (myhivedb)> desc student;
OK
col_name data_type comment
newid string new id
name string
sex string
age int
Time taken: 0.027 seconds, Fetched: 4 row(s)
hive (myhivedb)> alter table student change sex sex string first;
OK
Time taken: 0.069 seconds
hive (myhivedb)> desc student;
OK
col_name data_type comment
sex string
newid string new id
name string
age int
Time taken: 0.026 seconds, Fetched: 4 row(s)
hive (myhivedb)> alter table student change name name string after sex;
OK
Time taken: 0.06 seconds
hive (myhivedb)> desc student;
OK
col_name data_type comment
sex string
name string
newid string new id
age int
Time taken: 0.033 seconds, Fetched: 4 row(s)
3、修改分区
添加分区：
alter table student_ptn add partition(city='tiajin') partition(city='shanghai');
删除分区：
alter table student_ptn drop partition(city='tiajin');
alter table student_ptn drop partition(city='tiajin'),partition(city='shanghai');
修改分区的数据目录：
alter table student_ptn partition(city="beijing") set location "/stu_beijing"; XXXX 在hive1.2.1版本中，这个不可以。 在hive2.3.8版本中可以这些写。在hive3.1.2版本中也可以这些写。
alter table student_ptn partition(city="beijing") set location "hdfs://hadoop10:8020/stu_beijing"; √√√√√√√
5.4 删除表
语法结构：
DROP TABLE [IF EXISTS] table_name [PURGE]; -- (Note: PURGE available in Hive 0.14.0 and later)
示例：
hive (myhivedb)> drop table student2;
OK
Time taken: 0.081 seconds
5.5 清空表
语法结构：
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
示例：
hive (myhivedb)> truncate table student;
等价于
hadoop fs -rm -r /user/hive/warehouse/myhivedb.db/student/*
5.6 其他辅助命令
查看数据库列表 ：
show databases;
show databases like 'stu*';
查看数据表
show tables;
show tables in db_name;
查看 hive 函数列表
show functions;
查看表的详细信息（元数据信息）
desc table_name;
desc extended table_name;
desc formatted table_name;
查看表的创建的时候的详细信息
hive> show create table student;
声明：
文章中代码及相关语句为自己根据相应理解编写，文章中出现的相关图片为自己实践中的截图和相关技术对应的图片，若有相关异议，请联系删除。感谢。转载请注明出处，感谢。
落叶飘雪
