HIVE 数据仓库工具之第二部分（数据库相关操作）
一、Hive 对数据库的操作
1.1 创建数据库
Hive安装成功之后会存在一个默认的数据库default，为了方便管理不同业务数据，需要单独创建新的数据库
1.1.1 创建数据库语法
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
SCHEMA 和 DATABASE:两者含义相同，用途一样。
IF NOT EXISTS:创建数据库时若有同名数据库存在，缺少该子句将抛出错误信息。
COMMENT:表示为数据库添加描述信息。
LOCATION:存放数据库数据目录。
WITH DBPROPERTIES：表示为数据库添加描述信息，如创建时间、作者等信息。
1.1.3 示例
创建名为weather的数据库，具体操作如图所示。
create database if not exists weather comment "天气数据库" location "/user/hive/warehouse/mydb" with dbproperties('creator'='langbo','date'='2024-08-28');
通过describe命令查看已建数据库的详情，具体操作如图所示。
hive> describe database extended weather;
1.2 使用数据库
在Hive中可以根据不同的业务创建不同数据库，如果想对某个业务的数据进行操作，首先需要使用或者进入该数据库。
1.2.1 使用数据库语法
USE database_name;
使用数据库子句含义解释如下：
USE:如果想使用哪个数据库，直接使用USE关键字即可。
1.2.2 示例
使用已经创建的数据库weather，具体操作如下：
hive> user weather;
查看当前使用的是哪个数据库
hive> select current_database();
1.3 修改数据库
如果创建数据库时某些信息设置的不正确，还可以对已创建的数据库进行修改。
1.3.1 修改数据库的语法
ALTER (DATABASE|SCHEMA) database_name
SET DBPROPERTIES (property_name=property_value, ...);
修改数据库子句含义解释如下：
ALTER:如果想修改哪个数据库，直接使用ALTER关键字即可。
SET DBPROPERTIES:表示可以修改数据库键值对描述信息。
1.3.2 示例
修改weather数据库的创建日期，具体操作如下。
hive> alter database weather set DBPROPERTIES('date'='2024-08-29');
备注：Hive2.3.8以下版本只支持修改数据库描述信息，不支持修改包括数据库名称和数据存储路径等信息。
1.4 删除数据库
如果某个数据库中的数据是过期或者无用数据，可以直接删除该数据库即可。
1.4.1 删除数据库的语法
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
删除数据库子句含义解释如下：
IF EXISTS:如果不加此选项，删除不存在的库会报错。
RESTRICT:表示如果数据库中包含有表，删除数据库操作将失败。
CASCADE:表示如果想删除包含有表的数据库，需要加上该关键字。
1.4.2 示例
在weather数据库中创建一个temperature表，具体操作如下：
hive> create table if not exists temperature (id string, year string, temperature int);
删除包含temperature表的weather数据库，需要加上cascade关键字否则会报错，具体操作如下：
hive> drop database if exists weather cascade;
二、Hive 对数据表的操作
2.1 创建表
2.1.1 第一种建表方式
Hive的第一种建表方式，我们就采用类关系型数据库的建表标准方式。
- 语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
Hive建表子句含义解释如下：
EXTERNAL:加上该子句，表示创建外部表。
ROW FORMAT:可以指定表存储中各列的分隔符，默认为\001。
STORED AS:可以指定数据存储格式，默认值为TEXTFILE。
LOCATION:指定hive表中的数据在hdfs上的存储路径。
- 示例
在weather数据库中创建一个temperature表，该表存储的是美国各个气象站每年的气温值，具体操作如下：
create table if not exists temperature
(id string comment '气象站id',year string comment '年',temperature int comment '气温')
comment '天气表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE ;
在weather数据库中创建一个station表，该表存储的是美国各气象站的详细信息，具体操作如图7-13所示。
create table if not exists station
(id string comment '气象站id',latitude string comment '纬度',
longitude string comment '经度',elevation string comment '海拔',
state string comment '各州编码')
comment '气象站表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE ;
2.1.2 第二种建表方式
还可以使用select语句查询已有表来创建新表temperature2，具体操作如图7-14所示。
create table temperature2 as select * from temperature ;
hive> create table temperature2 as select * from temperature;
2.1.3 第三种建表方式
另外Hive还支持使用like语句创建新表temperature3，具体操作如图7-15所示。
hive> create table temperature3 like temperature;
2.2 查看表
2.2.1 查看所有表
使用show命令查看所有已经创建的表，具体操作如下：
hive> show tables;
2.2.2 查看特定表
通过匹配表达式查看特定表集合，具体操作如下：
hive> show tables 'temperature';
2.2.3 查看表字段信息
通过describe命令查看temperature表字段信息，具体操作如下：
hive> describe temperature;
2.3 修改表
如果建表时某些信息设置的不正确或者有遗漏，还可以对已创建的表进行修改。
- 语法
ALTER TABLE name RENAME TO new_name
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
修改表子句的含义解释如下：
Rename To语句:表示修改表名称。
REPLACE语句:表示删除表中原有的字段，替换为现有的字段。
ADD语句:表示为表增加新字段。
- 示例
使用Rename To语句将temperature2的表名修改为temperature_2，具体操作如下：
hive> ALTER TABLE temperature2 RENAME TO temperature_2;
使用REPLACE语句替换temperature_2表中的所有字段，具体操作如下：
hive> ALTER TABLE temperature_2 REPLACE COLUMNS (stationID string, year string, temperature int);
使用ADD语句为temperature_2表增加新字段name，具体操作如下：
hive> ALTER TABLE temperature_2 ADD COLUMNS (name string comment '气象站名称');
2.4 删除表
如果某个表中的数据是过期或者无用数据，可以直接删除该表即可。
- 语法
drop table if exists table_name;
删除表子句含义解释如下：
drop:表示删除表的关键字。
if exists:表示如果表存在才删除，如果不存在可以不做任何操作。
- 示例
使用drop语句删除temperature_2表，具体操作如下：
hive> drop table if exists temperature_2;
三、Hive 数据相关操作
3.1 数据导入
数据导入Hive表的方式有很多种。可以使用load data操作，通过把文件复制或移动到表的目录中，从而把数据导入Hive的表中。也可以用Insert 语句把数据从一个Hive表填充到另外一个表，或者在新建表的时候使用CTAS（CREATE TABLE …AS SELECT）结构。
3.1.1 第一种导入方式
直接通过Insert语句将数据插入表，具体操作如下：
hive> insert into temperature values ('03013', '2021', 38);
由图可以看出，通过Insert方式往表插入数据，底层执行的是MapReduce作业。
3.1.2 第二种导入方式
- 准备数据集
现在准备一个名为temperature.log文件，里面包含美国各个气象站每年的气温数据，数据的第一列为气象站ID，第二列为年份，第三列为气温值。具体样本数据如下所示：
03103,1980,41
03103,1981,98
03103,1982,70
03103,1983,74
03103,1984,77
再准备一个名为station.log文件，里面包含美国各个气象站的详细信息，数据的第一列为气象站ID，第二列为纬度，第三列为经度，第四列为海拔，第五列为州编码。具体样本数据如下所示：
03013,38.0700,102.6881,1129.0,CO
03016,39.5264,107.7264,1685.5,CO
03017,39.8328,104.6575,1650.2,CO
03024,35.6950,101.3950,930.9,TX
03026,39.2447,102.2842,1277.7,CO
然后将temperature.log和station.log文件上传至hadoop1节点的/usr/local/data/tmp目录下。
- 数据上传至HDFS
首先在HDFS中创建/weather目录，然后将temperature.log和station.log文件上传至该目录下，具体操作如下：
[root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs -mkdir /weather
[root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs -put /usr/local/data/tmp/temperature.log /weather
[root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs -put /usr/local/data/tmp/station.log /weathe
- 数据导入
通过load data命令将HDFS中的temperature.log和station.log文件分别加载到temperature和station表，具体操作如下：
hive> load data inpath '/weather/temperature.log' overwrite into table temperature;
hive> load data inpath '/weather/station.log' overwrite into table station;
如果将本地文件加载到Hive表中，可以使用如下语句：
hive> load data local inpath '/usr/local/data/tmp/temperature.log' overwrite into table tempera
hive> load data local inpath '/usr/local/data/tmp/station.log' overwrite into table station;
3.1.3 第三种导入方式
通过like子句创建表temperature2，然后使用select子句查询temperature表数据并插入temperature2，具体操作如下：
hive> create table temperature2 like temperature;
3.1.4 第四种导入方式
- 建表
通过create方式创建表temperature3，并指定表的HDFS路径，具体操作如图7-27所示。
create table if not exists temperature4
(id string comment '气象站id',year string comment '年',temperature int comment '气温')
comment '天气表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
location '/user/hive/warehouse/mydb/temperature4';
- 加载数据
通过HDFS的put命令，将本地文件上传至temperature3表的location位置即可完成表的数据加载，具体操作如下：
[root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs -put /usr/local/data/tmp/temperature.log /user/hive/warehouse/mydb/temperature4
- 表查询
通过select语句查询temperature3表中的数据，具体操作如下：
hive> select * from temperature4;
3.2数据导出
数据导出Hive表的方式也有很多种。可以使用Insert语句把数据从Hive表中导出到本地文件系统或者HDFS，也可以使用CTAS结构将一个Hive表中的数据导出到另外一个表。
3.2.1 第一种导出方式
使用INSERT OVERWRITE LOCAL DIRECTORY语句将temperature表中的数据导出到本地文件系统，具体操作如下：
hive> INSERT OVERWRITE LOCAL DIRECTORY '/usr/local/data/tmp/temperature.log.20240830' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature;
备注：LOCAL关键字表示将数据导出到本地文件系统，如果去掉LOCAL表示将数据导出到HDFS。
3.2.2 第二种导出方式
使用CTAS结构把Hive查询的结果导出到一个新创建的表，具体操作如下：
hive> create table temperature5 as select * from temperature;
3.2.3 第三种导出方式
在bash中，直接通过hive -e命令将temperature表中的数据导出到本地文件系统，具体操作如下：
[root@hadoop1 ~]# /usr/local/hive/bin/hive -e "select * from weather.temperature" >> /usr/local/data/tmp/temperature.log.2024083002
也可以先将Hive查询语句封装到temperature.sql文件，具体操作如下：
[root@hadoop1 hive]# vim /usr/local/hive/temperature.sql
输入如下内容： select * from weather.temperature;
然后在bash中，通过hive -f命令将temperature表中的数据导出到本地文件系统，具体操作如下：
[root@hadoop1 hive]# /usr/local/hive/bin/hive -f /usr/local/hive/temperature.sql >> /usr/local/data/tmp/temperature.log.2024083002
3.3 数据备份与恢复
Hive自带了数据的备份和恢复命令，不止数据，包括表结构也可以一同导出。
3.3.1 数据备份
通过export命令对temperature表中的数据进行备份，具体操作如下：
hive> export table temperature to '/user/hive/warehouse/mydb/backup/temperature';
Hive执行export命令就是将表结构存储在_metadata文件，并且直接将Hive数据文件复制到备份目录。
3.3.2 数据恢复
通过import命令恢复temperature表中的备份，具体操作如下：
hive> import table temperature_new from '/user/hive/warehouse/mydb/backup/temperature';
四、Hive 查询相关操作
前面已经将数据加载到Hive表中，接下来我们使用select语句的各种形式从Hive中检索数据。
4.1 查询显示所有字段
使用select语句查询显示temperature表的所有字段，具体操作如下：
hive> select * from temperature limit 3;
4.2 查询显示部分字段
使用select语句查询显示temperature表的部分字段，具体操作如下：
hive> select year,temperature from temperature limit 3;
4.3 where条件查询
使用where语句对temperature表进行过滤，查询显示气温值小于50的记录，具体操作如下：
hive> select * from temperature where temperature < 50;
4.4 distinct去重查询
使用distinct语句对temperature表中的气温字段进行去重查询，具体操作如下：
hive> select distinct temperature from temperature;
4.5 group by分组查询
使用group by语句对temperature表按气象站ID来分组统计每个气象站的平均气温，具体操作如下：
hive> select id from temperature group by id ;
4.6 order by全局排序
使用order by语句对temperature表按照气温值进行全局排序，具体操作如下：
hive> select * from temperature order by temperature;
备注：order by语句会对表进行全局排序，因此底层作业只运行一个reduce任务。当表的数据规模较大时，全局排序需要运行的时间比较长。
4.7 sort by局部排序
使用sort by语句对temperature表进行局部排序，为了便于观察局部排序效果，可以将reduce任务的并行度设置为3，同时将局部排序后的结果输出到HDFS，具体操作如下：
hive>
hive> set mapreduce.job.reduces=3;
hive> INSERT OVERWRITE DIRECTORY '/weather/temperature' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature sort by temperature;
使用HDFS命令查看局部排序结果，可以看出每个输出文件结果局部有序，查看结果：
4.8 distribute by分区查询
使用distribute by语句对temperature表中的数据按照气象站ID进行分区，具体操作如下：
hive> set mapreduce.job.reduces=3;
hive> INSERT OVERWRITE DIRECTORY '/weather/temperature' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature DISTRIBUTE BY id;
使用HDFS命令查看分区文件，可以看出数据按照气象站ID分配到不同的分区，具体结果如下：
4.9 cluster by分区排序
cluster by兼具distribute by和sort by功能。当distribute by和sort by指定的字段相同时，即可使用cluster by替换。使用cluster by语句对temperature表中的数据按照气象站ID进行分区和排序，具体操作如下：
hive> set mapreduce.job.reduces=3;
hive> INSERT OVERWRITE DIRECTORY '/weather/temperature' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature cluster by id;
五、Hive 表连接相关操作
和直接使用MapReduce相比，使用Hive简化了多表连接操作，极大地降低开发成本。
5.1 等值连接
使用join子句实现temperature和station表的等值连接，如图操作如下：
hive> select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t join station s on (t.id==s.id) limit 3;
备注：表的等值连接是内连接的子集。
5.2 内连接
使用inner join子句实现temperature和station表的内连接，具体操作如下：
hive> select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t inner join station s on (t.id==s.id) limit 3;
备注：与等值连接相比，表的内连接的条件可以相等，也可以不相等。
5.3 左连接
使用left join子句实现temperature和station表的左连接，具体操作如下：
hive> select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t left join station s on (t.id==s.id) limit 3;
备注：使用左连接会显示左表temperature的所有数据，如果右表station通过外键与左表temperature有匹配数据就显示对应字段的数据，否则右表字段都显示为\N。
5.4 右连接
使用right join子句实现temperature和station表的右连接，具体操作如下：
hive> select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t right join station s on (t.id==s.id) limit 3;
·备注·：使用右连接会显示右表station的所有数据，如果左表temperature通过外键与右表station有匹配数据就显示对应字段的数据，否则右表字段都显示为\N。
5.5 全连接
使用full join子句实现temperature和station表的全连接，具体操作如下：
hive> select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t fulljoin station s on (t.id==s.id) limit 3;
备注：使用全连接会显示temperature和station表的所有数据，两个表通过id键进行关联，如果temperature表无匹配的数据则对应字段显示为\N，反之，station表对应的字段显示为\N。
六、Hive 内部表和外部表相关操作
在Hive中创建表时，默认情况下创建的是内部表（managed table），此时Hive负责管理数据，Hive会将数据移入它的仓库目录。另外一种选择是创建一个外部表（external table），这会让Hive到仓库目录以外的位置访问数据。
6.1 内部表
Hive建表时如果不使用external关键字，默认创建的就是内部表，内部表创建方式：
create table if not exists managed_temperature
(id string,year string,temperature int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/mydb/managed_temperature';
使用load命令将数据加载到managed_temperature内部表时，Hive会把HDFS中的/weather/temperature.log文件移到LOCATION指定的仓库目录下，具体操作如下：
hive> load data inpath '/weather/temperature.log' overwrite into table managed_temperature;
使用drop命令删除managed_temperature内部表时，它的元数据和数据会被一起彻底删除，内部表删除操作如下：
hive> drop table managed_temperature;
6.2 外部表
使用external关键字创建的Hive表为外部表，此时数据的创建和删除由用户自己控制而非Hive，Hive只负责元数据的管理，建表方式如下：
create external table if not exists external_temperature
(id string,year string,temperature int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/mydb/external_temperature';
使用load命令将数据加载到external_temperature外部表时，Hive知道数据并不由自己管理，因此不会将数据移到LOCATION指定的仓库目录，加载操作如下：
hive> load data inpath '/weather/temperature.log' overwrite into table external_temperature;
使用drop命令删除external_temperature外部表时，Hive不会删除实际数据，而只会删除元数据，删除操作如下：
hive> drop table external_temperature
七、Hive 分区与分桶相关操作
Hive把表组织成分区（partition），它是一种根据分区列的值对表进行划分的机制，使用分区可以加快数据分片的查询速度。表或者分区可以进一步分为桶，它会为数据提供额外的结构从而获得更高的查询效率。
7.1 分区
Hive创建partition_temperature表时，可以使用PARTITIONED BY子句来定义分区，并使用year字段作为分区列，具体操作如下：
create table if not exists partition_temperature
(id string,temperature int)
PARTITIONED BY (year string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/mydb/partition_temperature';
使用insert子句将temperature表中的数据加载到partition_temperature分区表中，并以year字段作为分区列动态创建分区，具体操作如下：
hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
hive> set hive.exec.max.dynamic.partitions.pernode=10000;
hive> set hive.exec.max.dynamic.partitions=10000;
hive> set hive.exec.max.created.files=10000;
hive> INSERT OVERWRITE TABLE partition_temperature PARTITION(year) SELECT id,temperature,year FROM temperature ;
可以使用show partitions命令查看Hive表中有哪些分区，partition_temperature表的分区结果如下：
在select语句中使用where限定分区列year的值，这样Hive会对查询结果进行修剪，从而只扫描相关的分区，具体操作如下：
hive> select id,temperature from partition_temperature where year='1980';
对于partition_temperature表限定到特定日期的查询，Hive的数据处理会变得非常高效，因为它们只需要扫描查询year=’2011’分区中的文件。
7.1 分桶
Hive创建bucket_temperature表时，可以使用clustered by子句来指定划分桶所用的列和要划分的桶的个数，具体操作如下：
create table bucket_temperature(id string,year string,temperature int)
clustered by(id)
into 3 buckets;
在这里，我们使用气象站id来确定如何划分桶（Hive对id值进行哈希并将结果除以桶的个数取余数），这样任何一个桶里都会有一个随机的天气集合。
使用insert子句将temperature表中的数据加载到bucket_temperature分桶表中，具体操作如下：
hive> insert overwrite table bucket_temperture select * from temperature;
物理上，每个桶就是表目录里面的一个文件。事实上，桶对应于MapReduce的输出文件分区，一个作业产生的桶和reduce任务个数相同。通过dfs命令查看bucket_temperature表目录下面的桶文件，具体操作如下：
[root@hadoop1 hive]# hdfs dfs -ls /user/hive/warehouse/mydb/bucket_temperature;
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。使用TABLESAMPLE子句对bucket_temperature表进行抽样来满足这个需求，具体操作如下：
hive> select * from bucket_temperature TABLESAMPLE(BUCKET 2 OUT OF 3 on id);
图中语句表示从3个桶中的第二个桶中获取所有的天气记录，因为查询只需要读取和TABLESAMPLE子句匹配的桶，所以取样分桶表是非常高效的操作。
807
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
