Hive 基本操作（一）DDL操作_hive表ddl操作

1、DDL 操作

掌握 Hive 的各种基本操作：创建库和表。

1.1、库操作

1.1.1、创建库

语法结构：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
	[COMMENT database_comment]
	[LOCATION hdfs_path]
	[WITH DBPROPERTIES (property_name=property_value, ...)];
1
2
3
4

创建库的使用方式：
（1）创建普通库：

create database dbname;
1

（2）创建库的时候检查存与否（常用）：

create databse if not exists dbname;
1

（3）创建库的时候带注释：

create database if not exists dbname comment 'create my db named dbname';
1

（4）创建带属性的库

# 语法结构
create database if not exists dbname with dbproperties ('a'='aaa','b'='bbb');
# 事例
create database if not exists myhive with dbproperties ('a'='aaa','b'='bbb');
1
2
3
4

1.1.2、查看库

（1）查看有哪些数据库：

show databases;
1

（2）显示数据库的详细属性信息：

# 语法
desc database [extended] dbname;
# 示例
desc database extended myhive;
1
2
3
4

（3）查看正在使用哪个库：

select current_database();
1

（4）查看创建库的详细语句：

show create database mydb;
1

1.1.3、删除库

删除库的使用方式：
（1）普通删除库：

drop database dbname;
1

（2）删除库的时候检查存与否（常用）：

drop database if exists dbname;
1

默认情况下，hive 不允许删除包含表的数据库，有两种解决办法：
（1）手动删除库下所有表，然后删除库；
（2）使用 cascade 关键字（常用）：

drop database if exists dbname cascade;
# 默认情况下就是 restrict
drop database if exists myhive; # <====> drop database if exists myhive restrict;
1
2
3

1.1.4、切换库

切换库操作：

# 语法
use database_name;
# 实例
use myhive;
1
2
3
4

1.2、表操作

1.2.1、创建表

（1）建表语句：
语法结构：

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];
1
2
3
4
5
6
7
8
9

（2）建表语句相关解释：
A、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

B、EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），如果不存在，则会自动创建该目录。Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。

在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。（经典面试问题）
外部表和内部表的选择：
A、如果数据已经存储在 HDFS 上了，然后需要使用 Hive 去进行分析，并且该份数据还有可能要使用其他的计算引擎做计算之用，请使用外部表；
B、如果一份数据仅仅只是使用 Hive 做统计分析，那么可以使用内部表。
不管使用内部表和外部表，表的数据存储路径都是可以通过 location 关键字指定的！！！！！！
推荐方式：
A、创建内部表的时候，最好别指定 location，就存储在默认的仓库路径；
B、如果要指定外部路径，那么请创建该表为外部表。

C、PARTITIONED BY 在 Hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入 partition 分区概念。个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下，分区是以字段的形式在表结构中存在，通过 desc table 命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
分区建表分为 2 种：
一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录；
一种是多分区，表文件夹下出现多文件夹嵌套模式。

D、LIKE 允许用户复制现有的表结构，但是不复制数据。示例（创建一张 tableA 空表复制 tableB 的结构）：

create table tableA like tableB;
1

E、COMMENT 可以为表与字段增加描述。

F、ROW FORMAT ：

DELIMITED [FIELDS TERMINATED BY char]
		  [COLLECTION ITEMS TERMINATED BY char]
		  [MAP KEYS TERMINATED BY char]
		  [LINES TERMINATED BY char]
SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]
1
2
3
4
5

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

G、STORED AS TEXTFILE | SEQUENCEFILE | RCFILE：
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE，默认也是 textFile 格式，可以通过执行命令 set hive.default.fileformat，进行查看，如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

(1) 默认格式 TextFile，数据不做压缩，磁盘开销大，数据解析开销大。可结合 gzip、bzip2 使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive 不会对数据进行切分，从而无法对数据进行并行操作。
(2) SequenceFile 是 Hadoop API 提供的一种二进制文件支持，文件内容是以序列化的 kv 对象来组织的，其具有使用方便、可分割、可压缩的特点。 SequenceFile 支持三种压缩选择：NONE，RECORD，BLOCK。Record 压缩率低，一般建议使用 BLOCK 压缩。
(3) RCFILE 是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个 record 在一个块上，避免读一个记录需要读取多个 block。其次，块数据列式存储，有利于数据压缩和快速的列存取。相比 TEXTFILE 和 SEQUENCEFILE，RCFILE 由于列式存储方式，数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次写入、多次读取，因此，整体来看，RCFILE 相比其余两种格式具有较明显的优势。

H、CLUSTERED BY：
对于每一个表（table）或者分区，Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由（经典面试题）：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map Join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN 的数据量。
（2）使取样（Sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

I、LOCATION：
指定数据文件存放的 HDFS 目录，不管内部表还是外表，都可以指定。不指定就在默认的仓库路径。
最佳实践：
如果创建内部表请不要指定 location；
如果创建表时要指定 location，请创建外部表。

（3）Hive 建表示例：

CREATE TABLE IF NOT EXISTS page_view (
	viewTime INT, 
	userid BIGINT, 
	page_url STRING, 
	referrer_url STRING, 
	ip STRING COMMENT 'IP Address of the User'
) COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 31 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/myhive';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1.2.1.1、创建内部表

实例：

create table mytable (
	id int, 
	name string
) 
row format delimited fields terminated by ',' 
stored as textfile;
1
2
3
4
5
6

1.2.1.2、创建外部表

实例：

create external table mytable2 (
	id int, 
	name string
) 
row format delimited fields terminated by ',' 
location '/user/hive/warehouse/mytable2';
1
2
3
4
5
6

1.2.1.3、创建分区表

实例：

create table mytable3 (
	id int, 
	name string
) 
partitioned by(sex string)
row format delimited fields terminated by ',' 
stored as textfile;
1
2
3
4
5
6
7

插入数据

# 插入男分区数据
load data local inpath '/home/hadoop/hivedata/mingxing.txt' 
overwrite into table mytable3 partition(sex='boy');
# 插入女分区数据
load data local inpath '/home/hadoop/hivedata/mingxing.txt' 
overwrite into table mytable3 partition(sex='girl');
1
2
3
4
5
6

查询表分区：

show partitions mytable3;
1

1.2.1.3、创建分桶表

实例：

create table stu_buck (
	Sno int,
	Sname string,
	Sex string,
	Sage int,
	Sdept string
)
clustered by(Sno) sorted by(Sno DESC) into 4 buckets
row format delimited fields terminated by ',';
1
2
3
4
5
6
7
8
9

1.2.1.4、使用 like 关键字拷贝表

# 不管老表 mytable 是内部表还是外部表，new_table 都是内部表
create table new_table like mytable;
# 不管老表 mytable 是内部表还是外部表，如果加 external 关键字，new_table 都是外部表
create external table if not exists new_table like mytable;
1
2
3
4

1.2.2、查看表结构信息

（1）普通查看表结构：

# 语法
desc tb_name;
# 实例
desc page_view;
1
2
3
4

（2）详细查看表结构信息（推荐）：

# 语法
desc formatted tb_name;
# 实例
desc formatted page_view;
1
2
3
4

hive> desc formatted page_view;
OK
col_name data_type comment
#col_name data_type comment
viewtime int
userid bigint
page_url string
referrer_url string
ip string IP Address of the User
#Partition Information
#col_name data_type comment
dt string
country string
#Detailed Table Information
Database: mydb
Owner: hadoop
CreateTime: Wed Jan 24 20:32:08 CST 2020
LastAccessTime: UNKNOWN
Retention: 0
Location: hdfs://myha01/myhive
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {“BASIC_STATS”:“true”}
comment This is the page view table
numFiles 0
numPartitions 0
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1516797128
#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: 31
Bucket Columns: [userid]
Sort Columns: [Order(col:viewtime, order:1)]
Storage Desc Params:
colelction.delim ,
field.delim \t
line.delim \n
mapkey.delim :
serialization.format \t
Time taken: 0.175 seconds, Fetched: 46 row(s)

Hive 使用一个 Inputformat 对象将输入流分割成记录；使用一个 Outputformat 对象将记录格式化为输出流，使用序列化/反序列化器 SerDe 做记录的解析（记录和列的转换）。它们的默认值分别是：
Inputformat：org.apache.hadoop.mapred.TextInputFormat
Outputformat：org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
SerDe：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

1.2.3、修改表

1.2.3.1、重命名表

语法结构：

ALTER TABLE table_name RENAME TO new_table_name;
1

示例：

1.2.3.2、修改表属性

语法结构：

ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties: (property_name = property_value, property_name = property_value, ... )
1
2

实例：

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = 'my new students table');
1

不支持修改表名，和表的数据存储目录。

1.2.3.3、修改 SerDe 信息

语法结构：

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];

ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;

serde_properties:(property_name = property_value, property_name = property_value, ... )
1
2
3
4
5

实例：

# 更改列分隔符
ALTER TABLE student SET SERDEPROPERTIES ('field.delim' = '-');
1
2

1.2.3.4、增加/删除/改变/替换列

语法结构：

# 添加列
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...]);
# 改变列
ALTER TABLE name CHANGE c_name new_name new_type [FIRST|AFTER c_name];
# 替换列
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...]);
1
2
3
4
5
6

注意：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段

ADD 示例：

CHANGE 示例：

REPLACE 示例：

1.2.3.5、增加/删除分区

增加分区语法结构：

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

partition_spec: PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)
1
2
3

删除分区语法结构：

ALTER TABLE table_name DROP partition_spec, partition_spec, ...
1

添加分区示例：

# 不指定分区路径，默认路径
ALTER TABLE student_p ADD partition(part='a') partition(part='b');

# 指定分区路径
ALTER TABLE student_p ADD IF NOT EXISTS partition(part='bb') 
location '/myhive_bb' partition(part='cc') location '/myhive_cc';
1
2
3
4
5
6

修改分区路径示例：

ALTER TABLE student_p partition (part='bb') SET location '/myhive_bbbbb';
1

删除分区示例：

ALTER TABLE student_p DROP if exists partition(part='aa');
ALTER TABLE student_p DROP if exists partition(part='aa') if exists partition(part='bb');
1
2

最后补充：
（1）防止分区被删除：alter table student_p partition (part=‘aa’) enable no_drop;
（2）防止分区被查询：alter table student_p partition (part=‘aa’) enable offline;
enable 和 disable 是反向操作。

1.2.4、删除表

语法结构：

DROP TABLE [IF EXISTS] table_name;
1

实例：

drop table if exists mytable;
1

1.2.5、清空表(分区)

语法结构：

TRUNCATE TABLE table_name [PARTITION partition_spec];
1

实例：

# 清空表 student
truncate table student;
# 清空表 student 中的分区 city=’beijing’
truncate table student_ptn partition(city=’beijing’);
1
2
3
4

1.3、其他辅助命令

2、学习内容

上节学习内容：Hive 三种连接方式（CLI、HiveServer2/beeline（常用）、Web UI）及基本命令使用入门
下节学习内容：Hive 基本操作（二）DML操作