Hive系列第五章

第五章 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_nameLIKE 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
22,33,44  :   arrayfileformat相关操作：
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_TABLEload 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;

声明：
        文章中代码及相关语句为自己根据相应理解编写，文章中出现的相关图片为自己实践中的截图和相关技术对应的图片，若有相关异议，请联系删除。感谢。转载请注明出处，感谢。

微博地址： http://weibo.com/luoyepiaoxue2014 点击打开链接