三、Hive数据仓库应用之Hive数据操作语言（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）_hive数据仓库应用电子版

Hive远程模式部署参考：
一、Hive数据仓库应用之Hive部署（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）

一、加载文件

加载文件的语法格式如下：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...)]
1
2

1、通过加载文件的方式向外部表external_table中加载数据

创建文件夹hive_data，用于存放加载的文件。

mkdir -p /export/data/hive_data
1

在/export/data/hive_data目录下执行“vi staff_data”命令，创建并编辑文件
staff_data，在文件中添加如下内容。

001,xiaoming,8000,music_game,late:100_unpaidleave:500,hui long guan_beijing
002,xiaohong,9000,run_gourmet,late:50_unpaidleave:0,xi er qi_beijing
003,zhangsan,10000,swim_basketball_travel,late:0_unpaidleave:300,yong feng_beijing
1
2
3

在HDFS创建目录/hive_data/staff，用于存放文件staff_data。

hdfs dfs -mkdir -p /hive_data/staff
1

将文件staff_data上传到HDFS的/hive_data/staff目录。

hdfs dfs -put /export/data/hive_data/staff_data /hive_data/staff
1

向数据库hive_database的外部表external_table加载文件staff_data。

LOAD DATA INPATH '/hive_data/staff/staff_data' OVERWRITE INTO TABLE hive_database.external_table;
1

2、向分区表加载文件

在/export/data/hive_data目录下执行“vi username_data”命令，创建并编辑文件username_data，在文件中添加如下内容。

username01,20
username02,24
username03,50
username04,33
username05,26
username06,27
1
2
3
4
5
6

实现向数据库hive_database的分区表partitioned_table加载文件username_data，将文件中的数据加载到分区表partitioned_table的二级分区province=ShanDong/city=JiNan，具体命令如下。

LOAD DATA LOCAL INPATH '/export/data/hive_data/username_data' OVERWRITE
INTO TABLE hive_database.partitioned_table 
PARTITION (province='ShanDong', city='JiNan');
1
2
3

二、基本查询

基本查询的语法格式如下：

SELECT select_expr FROM table_name [WHERE where_condition];
1

例：查询数据库hive_database中分区表partitioned_table的列username和分区字段city数据。

 SELECT username,city FROM
 hive_database.partitioned_table WHERE 
 province='ShanDong' and city='JiNan';
1
2
3

三、插入查询

Hive使用INSERT语句向数据表插入数据。插入数据的方式有多种，可以直接向数据表中插入单条数据或多条数据，也可以将查询结果集直接插入数据表或文件系统。

1、基本插入

基本插入指的是向Hive表中插入单条或多条数据的操作，语法格式如下：

INSERT INTO TABLE tablename 
[PARTITION (partcol1[=val1], partcol2[=val2] ...)] 
VALUES (value1,value2,...) [,(value1,value2,...), ...]
1
2
3

例：向数据库hive_database的分桶表clustered_table插入8条数据向数据库hive_database的分桶表clustered_table插入8条数据。

INSERT INTO TABLE hive_database.clustered_table VALUES 
("001","user01","male",20,"YanFa"),
("002","user02","woman",23,"WeiHu"),
("003","user03","woman",25,"YanFa"),
("004","user04","woman",null,"RenShi"),
("005","user05","male",28,"YanFa"),
("006","user06","male",27,"CeShi"),
("007","user07","woman",33,"ShouHou"),
("008","user08","male",32,"CeShi");
1
2
3
4
5
6
7
8
9

2、查询插入

查询插入可分为单表插入、多表插入和本地插入。

1. 单表插入是将查询的单个结果集插入到一张表中；
2. 多表插入是将查询的多个结果集插入到多张表中；
3. 本地插入是将查询的单个结果集插入到本地文件系统或HDFS文件系统，该方式的数据插入，可以看作是Hive的导出功能。

单表插入的语法格式如下：

INSERT OVERWRITE|INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] 
select_statement1 FROM from_statement; 
1
2
3

例：将分区表partitioned_table中列username和age的查询结果集数据，覆盖插入到分区表partitioned_table1的二级分区province=ShanDong/city=QingDao。

INSERT OVERWRITE TABLE partitioned_table1 
PARTITION (province='ShanDong',city='QingDao') 
IF NOT EXISTS SELECT username,age FROM partitioned_table;
1
2
3

多表插入的语法格式如下：

FROM from_statement
INSERT OVERWRITE TABLE tablename1 
[PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] 
elect_statement1
[INSERT OVERWRITE TABLE tablename2 
[PARTITION ... [IF NOT EXISTS]] 
select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] 
...;
1
2
3
4
5
6
7
8
9

例：将分桶表clustered_table中列name和age的查询结果集数据，覆盖插入到分区表partitioned_table1的二级分区province=ShanDong/city=QingDao，并且追加插入到分区表partitioned_table的二级分区province=ShanDong/city=JiNan。

FROM clustered_table
INSERT INTO TABLE partitioned_table 
PARTITION (province='ShanDong',city='JiNan') 
SELECT name,age
INSERT OVERWRITE TABLE partitioned_table1 
PARTITION (province='ShanDong',city='QingDao') 
SELECT name,age;
1
2
3
4
5
6
7

本地插入的语法格式如下：

INSERT OVERWRITE [LOCAL] DIRECTORY 'directory1'
[ROW FORMAT row_format] [STORED AS file_format]
SELECT ... FROM ...
1
2
3

例：将分桶表clustered_table数据插入到本地文件系统/export/data/hive_data/clustered_table目录。

INSERT OVERWRITE LOCAL 
DIRECTORY '/export/data/hive_data/clustered_table'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
SELECT * FROM clustered_table;
1
2
3
4
5
6
7

3、向分区表插入数据

可以分别通过动态分区、静态分区和混合分区的方式向Hive分区表中插入数据。
例：使用静态分区的方式向分区表partitioned_table中插入数据。

INSERT INTO TABLE partitioned_table 
PARTITION (province='ShanDong', city='JiNan') 
VALUES ("user22",44),("user33",55);
1
2
3

使用Hive动态分区前，需要在Hive配置文件hive-site.xml中配置如下内容，配置完成后需要重启虚拟机Node02的HiveServer2服务使配置内容生效。

<property>
     <name>hive.exec.dynamic.partition.mode</name>
     <value>nostrict</value>
</property>
1
2
3
4

例：在数据库hive_database中创建一个分区表dynamic_table，将分桶表clustered_table中列name、age和gender的查询结果集数据，以动态分区的方式插入到分区表dynamic_table。
创建一个分区表dynamic_table语法：

CREATE  TABLE IF NOT EXISTS  
hive_database.dynamic_table(
username STRING,
age INT
) PARTITIONED BY (
gender_type STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile;
1
2
3
4
5
6
7
8
9
10
11

动态分区插入：

INSERT INTO TABLE dynamic_table PARTITION (gender_type) 
SELECT name,age,gender FROM clustered_table;
1
2

查询分区表dynamic_table的分区信息。

SHOW PARTITIONS dynamic_table;
1

例：创建一个分区表dynamic_table2，使用混合分区的方式向分区表dynamic_table3中插入
4条数据。
创建一个分区表dynamic_table2语法：

CREATE  TABLE IF NOT EXISTS  
hive_database.dynamic_table2(
name STRING,
age INT
) PARTITIONED BY (
year STRING,
month STRING,
day STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile;
1
2
3
4
5
6
7
8
9
10
11
12
13

混合分区插入：

INSERT INTO TABLE dynamic_table2 
PARTITION (year='2020',month,day) VALUES 
("user01",20,"01","22"),
("user02",24,"02","23"),
("user03",50,"01","24"),
("user04",33,"03","24");
1
2
3
4
5
6

查询分区表dynamic_table2的分区信息。

SHOW PARTITIONS dynamic_table2;
1

四、数据表的导入导出（IMPORT和EXPORT）

EXPORT用于将数据表的数据以及元数据导出到HDFS的指定位置，IMPORT用于将使用EXPORT导出的内容在Hive中创建目标表。语法格式如下：
EXPORT语法

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])] 
TO 'export_target_path'
1
2

IMPORT语法

IMPORT [[EXTERNAL] TABLE new_or_original_tablename 
[PARTITION (part_column="value"[, ...])]] 
FROM 'source_path' 
[LOCATION 'import_target_path']
1
2
3
4

例：将分区表dynamic_table的分区gender_type=male导出到HDFS的/export_dir目录中。

EXPORT TABLE dynamic_table PARTITION (gender_type=“male") TO '/export_dir';
1

将HDFS目录export_dir中导出分区表dynamic_table分区gender_type=male，进行导入数据表的操作，并且重命名导入的数据表为dynamic_table3。

IMPORT EXTERNAL TABLE dynamic_table3 FROM '/export_dir';
1

参考文献：黑马程序员.Hive数据仓库应用[M].北京：清华大学出版社,2021.

后续学习链接：
四、Hive数据仓库应用之Hive数据查询语言（一）（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）