HIVE 数据仓库工具之第二部分（数据库相关操作）

一、Hive 对数据库的操作

1.1 创建数据库

Hive安装乐成之后会存在一个默认的数据库default，为了方便管理不同业务数据，需要单独创建新的数据库
1.1.1 创建数据库语法

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

复制代码

SCHEMA 和 DATABASE:两者寄义雷同，用途一样。
IF NOT EXISTS:创建数据库时若有同名数据库存在，缺少该子句将抛出错误信息。
COMMENT:表现为数据库添加形貌信息。
LOCATION:存放数据库数据目录。
WITH DBPROPERTIES：表现为数据库添加形貌信息，如创建时间、作者等信息。
1.1.3 示例

创建名为weather的数据库，详细操作如图所示。

1. create database if not exists weather comment "天气数据库" location "/user/hive/warehouse/mydb" with dbproperties('creator'='langbo','date'='2024-08-28');

复制代码

通过describe命令检察已建数据库的详情，详细操作如图所示。

1. hive> describe database extended weather;

复制代码

1.2 使用数据库

在Hive中可以根据不同的业务创建不同数据库，如果想对某个业务的数据进行操作，起首需要使用或者进入该数据库。
1.2.1 使用数据库语法

1. USE database_name;

复制代码

使用数据库子句寄义表明如下：
USE:如果想使用哪个数据库，直接使用USE关键字即可。
1.2.2 示例

使用已经创建的数据库weather，详细操作如下：

1. hive> user weather;

复制代码

检察当前使用的是哪个数据库

1. hive> select current_database();

复制代码

1.3 修改数据库

如果创建数据库时某些信息设置的不正确，还可以对已创建的数据库进行修改。
1.3.1 修改数据库的语法

1. ALTER (DATABASE|SCHEMA) database_name
2. SET DBPROPERTIES (property_name=property_value, ...);  

复制代码

修改数据库子句寄义表明如下：
ALTER:如果想修改哪个数据库，直接使用ALTER关键字即可。
SET DBPROPERTIES:表现可以修改数据库键值对形貌信息。
1.3.2 示例

修改weather数据库的创建日期，详细操作如下。

1. hive> alter database weather set DBPROPERTIES('date'='2024-08-29');

复制代码

备注：Hive2.3.8以下版本只支持修改数据库形貌信息，不支持修改包括数据库名称和数据存储路径等信息。
1.4 删除数据库

如果某个数据库中的数据是过期或者无用数据，可以直接删除该数据库即可。
1.4.1 删除数据库的语法

1. DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

复制代码

删除数据库子句寄义表明如下：
IF EXISTS:如果不加此选项，删除不存在的库会报错。
RESTRICT:表现如果数据库中包含有表，删除数据库操作将失败。
CASCADE:表现如果想删除包含有表的数据库，需要加上该关键字。
1.4.2 示例

在weather数据库中创建一个temperature表，详细操作如下：

1. hive> create table if not exists temperature (id string, year string, temperature int);

复制代码

删除包含temperature表的weather数据库，需要加上cascade关键字否则会报错，详细操作如下：

1. hive> drop database if exists weather cascade;

复制代码

二、Hive 对数据表的操作

2.1 创建表

2.1.1 第一种建表方式

Hive的第一种建表方式，我们就采用类关系型数据库的建表标准方式。

• 语法
  

1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name      
2. [(col_name data_type [COMMENT col_comment], ...)]  
3. [COMMENT table_comment]   
4. [ROW FORMAT row_format]
5. [STORED AS file_format]
6. [LOCATION hdfs_path]  

复制代码

Hive建表子句寄义表明如下：
EXTERNAL:加上该子句，表现创建外部表。
ROW FORMAT:可以指定表存储中各列的分隔符，默认为\001。
STORED AS:可以指定数据存储格式，默认值为TEXTFILE。
LOCATION:指定hive表中的数据在hdfs上的存储路径。

• 示例
  在weather数据库中创建一个temperature表，该表存储的是美国各个气象站每年的气温值，详细操作如下：
  

1. create  table  if not exists temperature
2. (id string comment '气象站id',year string comment '年',temperature int comment '气温')
3. comment '天气表'
4. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
5. STORED AS  TEXTFILE ;

复制代码

在weather数据库中创建一个station表，该表存储的是美国各气象站的详细信息，详细操作如图7-13所示。

1. create  table  if not exists station
2. (id string comment '气象站id',latitude string comment '纬度',
3. longitude string comment '经度',elevation string comment '海拔',
4. state string comment '各州编码')
5. comment '气象站表'
6. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
7. STORED AS  TEXTFILE ;

复制代码

2.1.2 第二种建表方式

还可以使用select语句查询已有表来创建新表temperature2，详细操作如图7-14所示。
create table temperature2 as select * from temperature ;

1. hive> create table temperature2 as select * from temperature;

复制代码

2.1.3 第三种建表方式

别的Hive还支持使用like语句创建新表temperature3，详细操作如图7-15所示。

1. hive> create table temperature3 like temperature;

复制代码

2.2 检察表

2.2.1 检察所有表

使用show命令检察所有已经创建的表，详细操作如下：

1. hive> show tables;

复制代码

2.2.2 检察特定表

通过匹配表达式检察特定表集合，详细操作如下：

1. hive> show tables 'temperature';

复制代码

2.2.3 检察表字段信息

通过describe命令检察temperature表字段信息，详细操作如下：

1. hive> describe temperature;

复制代码

2.3 修改表

如果建表时某些信息设置的不正确或者有遗漏，还可以对已创建的表进行修改。

• 语法
  

1. ALTER TABLE name RENAME TO new_name
2. ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
3. ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])

复制代码

修改表子句的寄义表明如下：
Rename To语句:表现修改表名称。
REPLACE语句:表现删除表中原有的字段，替换为现有的字段。
ADD语句:表现为表增加新字段。

• 示例
  使用Rename To语句将temperature2的表名修改为temperature_2，详细操作如下：
  

1. hive> ALTER TABLE temperature2 RENAME TO temperature_2;

复制代码

使用REPLACE语句替换temperature_2表中的所有字段，详细操作如下：

1. hive> ALTER TABLE temperature_2 REPLACE COLUMNS (stationID string, year string, temperature int);

复制代码

使用ADD语句为temperature_2表增加新字段name，详细操作如下：

1. hive> ALTER TABLE temperature_2 ADD COLUMNS (name string comment '气象站名称');

复制代码

2.4 删除表

如果某个表中的数据是过期或者无用数据，可以直接删除该表即可。

• 语法
  

1. drop table if exists table_name;

复制代码

删除表子句寄义表明如下：
drop:表现删除表的关键字。
if exists:表现如果表存在才删除，如果不存在可以不做任何操作。

• 示例
  使用drop语句删除temperature_2表，详细操作如下：
  

1. 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语句将数据插入表，详细操作如下：

1. 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文件上传至该目录下，详细操作如下：
  

1. [root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs  -mkdir /weather
2. [root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs  -put /usr/local/data/tmp/temperature.log /weather
3. [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表，详细操作如下：
  

1. hive> load data inpath '/weather/temperature.log' overwrite into table temperature;
2. hive> load data inpath '/weather/station.log' overwrite into table station;

复制代码

如果将当地文件加载到Hive表中，可以使用如下语句：

1. hive> load data local inpath '/usr/local/data/tmp/temperature.log' overwrite into table tempera
2. hive> load data local inpath '/usr/local/data/tmp/station.log' overwrite into table station;

复制代码

3.1.3 第三种导入方式

通过like子句创建表temperature6，然后使用select子句查询temperature表数据并插入temperature6，详细操作如下：

1. hive> create table temperature6 like temperature;
2. hive> insert into temperature6  select * from temperature;

复制代码

3.1.4 第四种导入方式

• 建表
  通过create方式创建表temperature3，并指定表的HDFS路径，详细操作如图7-27所示。
  

1. create  table  if not exists temperature4
2. (id string comment '气象站id',year string comment '年',temperature int comment '气温')
3. comment '天气表'
4. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
5. STORED AS  TEXTFILE
6. location '/user/hive/warehouse/mydb/temperature4';

复制代码

• 加载数据
  通过HDFS的put命令，将当地文件上传至temperature3表的location位置即可完成表的数据加载，详细操作如下：
  

1. [root@hadoop1 ~]# /usr/local/hadoop/bin/hdfs dfs -put /usr/local/data/tmp/temperature.log /user/hive/warehouse/mydb/temperature4

复制代码

• 表查询
  通过select语句查询temperature3表中的数据，详细操作如下：
  

1. hive> select * from temperature4;

复制代码

3.2数据导出

数据导出Hive表的方式也有很多种。可以使用Insert语句把数据从Hive表中导出到当地文件体系或者HDFS，也可以使用CTAS布局将一个Hive表中的数据导出到别的一个表。
3.2.1 第一种导出方式

使用INSERT OVERWRITE LOCAL DIRECTORY语句将temperature表中的数据导出到当地文件体系，详细操作如下：

1. 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查询的结果导出到一个新创建的表，详细操作如下：

1. hive> create table temperature5 as select * from temperature;

复制代码

3.2.3 第三种导出方式

在bash中，直接通过hive -e命令将temperature表中的数据导出到当地文件体系，详细操作如下：

1. [root@hadoop1 ~]# /usr/local/hive/bin/hive -e "select * from weather.temperature" >> /usr/local/data/tmp/temperature.log.2024083002

复制代码

也可以先将Hive查询语句封装到temperature.sql文件，详细操作如下：

1. [root@hadoop1 hive]# vim /usr/local/hive/temperature.sql

复制代码

输入如下内容： select * from weather.temperature;
然后在bash中，通过hive -f命令将temperature表中的数据导出到当地文件体系，详细操作如下：

1. [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表中的数据进行备份，详细操作如下：

1. hive> export table temperature to '/user/hive/warehouse/mydb/backup/temperature';

复制代码

Hive实行export命令就是将表布局存储在_metadata文件，并且直接将Hive数据文件复制到备份目录。
3.3.2 数据恢复

通过import命令恢复temperature表中的备份，详细操作如下：

1. hive> import table temperature_new from '/user/hive/warehouse/mydb/backup/temperature';

复制代码

四、Hive 查询相关操作

前面已经将数据加载到Hive表中，接下来我们使用select语句的各种情势从Hive中检索数据。
4.1 查询显示所有字段

使用select语句查询显示temperature表的所有字段，详细操作如下：

1. hive> select * from temperature limit 3;

复制代码

4.2 查询显示部分字段

使用select语句查询显示temperature表的部分字段，详细操作如下：

1. hive> select year,temperature from temperature limit 3;

复制代码

4.3 where条件查询

使用where语句对temperature表进行过滤，查询显示气温值小于50的记载，详细操作如下：

1. hive> select * from temperature where temperature < 50;

复制代码

4.4 distinct去重查询

使用distinct语句对temperature表中的气温字段进行去重查询，详细操作如下：

1. hive> select distinct temperature from temperature;

复制代码

4.5 group by分组查询

使用group by语句对temperature表按气象站ID来分组统计每个气象站的平均气温，详细操作如下：

1. hive> select id from temperature group by id ;

复制代码

4.6 order by全局排序

使用order by语句对temperature表按照气温值进行全局排序，详细操作如下：

1. hive> select * from temperature order by temperature;

复制代码

备注：order by语句会对表进行全局排序，因此底层作业只运行一个reduce任务。当表的数据规模较大时，全局排序需要运行的时间比力长。
4.7 sort by局部排序

使用sort by语句对temperature表进行局部排序，为了便于观察局部排序结果，可以将reduce任务的并行度设置为3，同时将局部排序后的结果输出到HDFS，详细操作如下：

1. hive>
2. hive> set mapreduce.job.reduces=3;
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进行分区，详细操作如下：

1. hive> set mapreduce.job.reduces=3;
2. 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进行分区和排序，详细操作如下：

1. hive> set mapreduce.job.reduces=3;
2. 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表的等值连接，如图操作如下：

1. 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表的内连接，详细操作如下：

1. 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表的左连接，详细操作如下：

1. 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表的右连接，详细操作如下：

1. 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表的全连接，详细操作如下：

1. 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关键字，默认创建的就是内部表，内部表创建方式：

1. create  table  if not exists managed_temperature
2. (id string,year string,temperature int)
3. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
4. STORED AS  TEXTFILE
5. LOCATION '/user/hive/warehouse/mydb/managed_temperature';

复制代码

使用load命令将数据加载到managed_temperature内部表时，Hive会把HDFS中的/weather/temperature.log文件移到LOCATION指定的仓库目录下，详细操作如下：

1. hive> load data inpath '/weather/temperature.log' overwrite into table managed_temperature;

复制代码

使用drop命令删除managed_temperature内部表时，它的元数据和数据会被一起彻底删除，内部表删除操作如下：

1. hive> drop table managed_temperature;

复制代码

6.2 外部表

使用external关键字创建的Hive表为外部表，此时数据的创建和删除由用户自己控制而非Hive，Hive只负责元数据的管理，建表方式如下：

1. create external  table  if not exists external_temperature
2. (id string,year string,temperature int)
3. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
4. STORED AS  TEXTFILE
5. LOCATION '/user/hive/warehouse/mydb/external_temperature';

复制代码

使用load命令将数据加载到external_temperature外部表时，Hive知道数据并不由自己管理，因此不会将数据移到LOCATION指定的仓库目录，加载操作如下：

1. hive> load data inpath '/weather/temperature.log' overwrite into table external_temperature;

复制代码

使用drop命令删除external_temperature外部表时，Hive不会删除现实数据，而只会删除元数据，删除操作如下：

1. hive> drop table external_temperature

复制代码

七、Hive 分区与分桶相关操作

Hive把表组织身分区（partition），它是一种根据分区列的值对表进行划分的机制，使用分区可以加速数据分片的查询速度。表或者分区可以进一步分为桶，它会为数据提供额外的布局从而得到更高的查询服从。
7.1 分区

Hive创建partition_temperature表时，可以使用PARTITIONED BY子句来界说分区，并使用year字段作为分区列，详细操作如下：

1. create  table  if not exists partition_temperature
2. (id string,temperature int)
3. PARTITIONED BY (year string)
4. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
5. STORED AS  TEXTFILE
6. LOCATION '/user/hive/warehouse/mydb/partition_temperature';

复制代码

使用insert子句将temperature表中的数据加载到partition_temperature分区表中，并以year字段作为分区列动态创建分区，详细操作如下：

1. hive> set hive.exec.dynamic.partition=true;
2. hive> set hive.exec.dynamic.partition.mode=nonstrict;
3. hive> set hive.exec.max.dynamic.partitions.pernode=10000;
4. hive> set hive.exec.max.dynamic.partitions=10000;
5. hive> set hive.exec.max.created.files=10000;
6. hive> INSERT OVERWRITE TABLE partition_temperature PARTITION(year) SELECT id,temperature,year FROM temperature ;

复制代码

可以使用show partitions命令检察Hive表中有哪些分区，partition_temperature表的分区结果如下：

在select语句中使用where限定分区列year的值，如许Hive会对查询结果进行修剪，从而只扫描相关的分区，详细操作如下：

1. hive> select id,temperature from partition_temperature where year='1980';

复制代码

对于partition_temperature表限定到特定日期的查询，Hive的数据处理惩罚会变得非常高效，因为它们只需要扫描查询year=’2011’分区中的文件。
7.1 分桶

Hive创建bucket_temperature表时，可以使用clustered by子句来指定划分桶所用的列和要划分的桶的个数，详细操作如下：

1. create table bucket_temperature(id string,year string,temperature int)
2. clustered by(id)
3. into 3 buckets;

复制代码

在这里，我们使用气象站id来确定如何划分桶（Hive对id值进行哈希并将结果除以桶的个数取余数），如许任何一个桶里都会有一个随机的天气集合。
使用insert子句将temperature表中的数据加载到bucket_temperature分桶表中，详细操作如下：

1. hive> insert overwrite table bucket_temperture select * from temperature;

复制代码

物理上，每个桶就是表目录内里的一个文件。究竟上，桶对应于MapReduce的输出文件分区，一个作业产生的桶和reduce任务个数雷同。通过dfs命令检察bucket_temperature表目录下面的桶文件，详细操作如下：

1. [root@hadoop1 hive]# hdfs dfs -ls /user/hive/warehouse/mydb/bucket_temperature;

复制代码

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。使用TABLESAMPLE子句对bucket_temperature表进行抽样来满足这个需求，详细操作如下：

1. hive> select * from bucket_temperature TABLESAMPLE(BUCKET 2 OUT OF 3 on id);

复制代码

图中语句表现从3个桶中的第二个桶中获取所有的天气记载，因为查询只需要读取和TABLESAMPLE子句匹配的桶，以是取样分桶表好坏常高效的操作。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。