当前位置：首页 > news >正文

【Hive】【HiveQL】【大数据技术基础】作业三数据仓库Hive的使用

news 2024/11/14 8:48:10

作业三基于Hadoop的数据仓库Hive 学习指南

文件准备

在 Linux 系统中创建一个名为 student 的文件，并将其存储在 /home/hadoop/examples/ 目录下，你可以按照以下步骤操作：

步骤 1: 确保目录存在

首先，你需要确保 /home/hadoop/examples/ 这个目录存在。如果不确定，你可以使用以下命令来创建它：
mkdir -p /home/hadoop/examples/
mkdir -p 命令会创建所需的目录，如果上级目录不存在，也会一并创建，而不会报错。

步骤 2: 创建文件

然后，你可以使用 touch 命令来创建一个名为 student 的空文件：
touch /home/hadoop/examples/student
步骤 3: 编辑文件

创建文件后，你可以使用文本编辑器来编辑文件并添加内容。这里以 vim为例：
vim /home/hadoop/examples/student
在 vim 编辑器中，按 i 键进入插入模式，然后输入以下内容：

注意：Hive 默认使用控制字符 \t（制表符）作为字段分隔符。在 Vim 中，制表符（Tab）通常由键盘上的 Tab 键直接输入。
1    xiapi
2    xiaoxue
3    qingqing
输入完毕后，按 Esc 键退出插入模式。

步骤 4: 验证文件

在 vim 的正常模式下，输入以下命令来保存更改并退出：
:wq
步骤 5: 验证文件

最后，你可以使用 cat 命令来查看文件内容，确保一切正常：
cat /home/hadoop/examples/student

一、启动Hive

在启动Hive之前，请确保Hadoop集群已经启动。可以使用以下命令来启动Hadoop和Hive：

start-all.sh # 启动Hadoop集群
hive # 启动Hive

注意，我们这里已经配置了PATH，所以，不要把start-all.sh和hive命令的路径加上。

如果你的PATH环境变量没有配置，需要指定命令的完整路径。例如：

cd /usr/local/hadoop
./sbin/start-all.shcd /usr/local/hive
./bin/hive

使用MySQL作为元数据库时，可以使用以下命令登录并查看数据库：

show databases;

show databases;

（1）在启动Hive时，有可能会出现Hive metastore database is not initialized的错误。

出错原因：以前曾经安装了Hive或MySQL，重新安装Hive和MySQL以后，导致版本、配置不一致。
解决方法是，使用schematool工具。Hive现在包含一个用于 Hive Metastore 架构操控的脱机工具，名为 schematool.此工具可用于初始化当前 Hive 版本的 Metastore 架构。此外，其还可处理从较旧版本到新版本的架构升级。

可以通过在终端执行以下命令来解决：
schematool -dbType mysql -initSchema
执行此命令后，再次启动Hive应该就不会出现错误了。

（2）在启动Hive时，有可能会出现NameNode处于安全模式导致的Hive启动问题

可以连接数据库，后启动hive:

如何退出Hive

启动进入Hive的交互式执行环境以后，会出现如下命令提示符：

hive>

可以在里面输入SQL语句。

如果要退出Hive交互式执行环境，可以输入如下命令：

exit;

注意：不要漏了分号“ ; ”

二、Hive的常用HiveQL操作

HiveQL 是 Hive 数据仓库的 SQL 方言。

基本数据类型

首先，我们简单叙述一下HiveQL的基本数据类型。

Hive支持基本数据类型和复杂类型。基本数据类型主要有数值类型(INT、FLOAT、DOUBLE ) 、布尔型和字符串。复杂类型有三种:ARRAY、MAP 和 STRUCT。

a.基本数据类型

TINYINT: 1个字节
SMALLINT: 2个字节
INT: 4个字节
BIGINT: 8个字节
BOOLEAN: TRUE/FALSE
FLOAT: 4个字节，单精度浮点型
DOUBLE: 8个字节，双精度浮点型STRING 字符串

b.复杂数据类型

ARRAY: 有序字段
MAP: 无序字段
STRUCT: 一组命名的字段

常用的HiveQL操作命令

Hive常用的HiveQL操作命令主要包括：数据定义、数据操作。接下来详细介绍一下这些命令即用法（想要了解更多请参照《Hive编程指南》一书）。

数据定义

数据定义操作主要用于创建、修改和删除数据库、表、视图、函数和索引。

以下是一些常用的数据定义命令：

数据库

创建数据库：

create database if not exists hive;

查看数据库：

show databases;

查看Hive中以h开头数据库

show databases like 'h.*';

查看hive数据库位置等信息：

describe database 数据库名字;

如：

修改数据库属性：

alter database hive set dbproperties;

如alter database hive set dbproperties('day' =' 20241021'); ：

切换数据库（切换到hive数据库下）：

use hive;

删除不含表的数据库：

drop database if exists hive;

这个命令成功执行了，它删除了名为 hive 的数据库，如果该数据库存在的话。

删除数据库hive和它中的表：

drop database if exists hive cascade;

这个命令用于删除名为 hive 的数据库，并且使用 CASCADE 选项，表示级联删除数据库及其包含的所有对象（如表、视图等）。

注意，除 dbproperties属性外，数据库的元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置，没有办法删除或重置数据库属性。

表

Hive支持创建内部表、外部表和分区表。以下是一些创建表的示例：

创建内部表：

create table if not exists hive.usr(name string comment 'username',pwd string comment 'password',address struct<street:string,city:string,state:string,zip:int>,identify map<int,tinyint> comment 'number,sex'
) comment 'description of the table'
tblproperties('creator'='me','time'='2024.10.21');

创建外部表：

create external table if not exists usr2(name string,pwd string,address struct<street:string,city:string,state:string,zip:int>,identify map<int,tinyint>
)
row format delimited fields terminated by ','
location '/usr/local/hive/warehouse/hive.db/usr';

创建分区表：

create table if not exists usr3(name string,pwd string,address struct<street:string,city:string,state:string,zip:int>,identify map<int,tinyint>
)
partitioned by(city string,state string);

创建表的副本（复制usr表的表模式）：

create table if not exists hive.usr1 like hive.usr;

显示Hive数据库中的表：

show tables in hive;

查看Hive中hive数据库中以u开头的表（前面use hive）：

show tables in 'u.*';

描述表结构：

describe hive.usr;

重命名表：

alter table usr rename to custom;

根据 DESCRIBE FORMATTED usr3; 命令的输出，可见表 usr3 是一个分区表，其分区键为 city 和 state。这意味着你只能为这两个列添加分区信息。

输出中的“# Partition Information”部分显示了分区列及其数据类型：

city：字符串类型（string）
state：字符串类型（string）

增加分区（为表usr3增加一个分区字段）：

alter table usr3 add if not exists partition (city='Beijing', state='Bejing');

修改列信息（修改 custom 表中的列信息，将 pwd 列改名为 password 并将其移动到 address 列之后。）：

alter table custom change column pwd password string after address;

【遇到的问题】：错误信息 "FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions: address, password" 。在 Hive 中，当使用 ALTER TABLE ... CHANGE COLUMN 语句时，你需要确保新列的数据类型与原列的数据类型相同，或者至少是兼容的。

【解决方法】：不需要改变列的顺序，你可以省略 AFTER address 部分，只改列名。

alter table custom change column pwd password string;

修改列信息（usr1中将字段name改为username）：

alter table usr1 change column name username string;

这条命令的作用是：

ALTER TABLE usr1：指定要修改的表名为 usr1。
CHANGE COLUMN name username：将现有的 name 列改名为 username。
string：指定新列 username 的数据类型为字符串。如果你的 name 列原本就是字符串类型，这个部分是必须的，以确保数据类型的一致性。

切换到hive数据库下：

use hive;

删除表：

drop table if exists usr1;

删除数据库和它中的表：

drop database if exists hive cascade;

数据操作

数据操作主要实现的是将数据装载到表中（或是从表中导出），并进行相应查询操作，对熟悉SQL语言的用户应该不会陌生。

以下是一些常用的数据操作命令：

向表中装载数据

这里我们以只有两个属性的简单表为例来介绍。首先创建表stu和course，stu有两个属性id与name，course有两个属性cid与sid。

create database if not exists hive;       #创建数据库

create table if not exists hive.stu(id int,name string)
row format delimited fields terminated by'\t';create table if not exists hive.course(cid int,sid int) 
row format delimited fields terminated by'\t';

向表中装载数据有两种方法：从文件中导入和通过查询语句插入。

a. 从文件中导入数据：

假如这个表中的记录存储于文件student中，该文件的存储路径为/home/hadoop/examples/student，内容如下：

student：

1 xiapi

2 xiaoxue

3 qingqing

下面我们把这个文件中的数据装载到表stu中，操作如下：

use hive;

load data local inpath '/home/hadoop/examples/student' overwrite into table stu;

b. 通过查询语句插入数据：

使用如下命令，创建stu1表，它和stu表属性相同，我们要把从stu表中查询得到的数据插入到stu1中：

create table stu1 as select id,name from stu;

上面是创建表，并直接向新表插入数据；

若表已经存在，向表中插入数据需执行以下命令：

insert overwrite table stu1 select id,name from stu;

这里关键字overwrite的作用是替换掉表stu1（或分区）中原有数据，，并用新查询的结果替换它。如果换成into关键字，是直接追加到原有内容后。

从表中导出数据

a.可以简单拷贝文件或文件夹：

hadoop fs -cp source_path target_path;

这里的 /source_path 是 Hive 表数据在 HDFS 上的路径，而 /target_path 是你希望拷贝数据到的本地文件系统路径。

例如，如果你的 stu 表的数据存储在 /user/hive/examples 目录下，并且你想要将这个目录下的所有文件拷贝到本地的 /usr/local/hadoop/tmp 目录，你可以执行：

hadoop fs -cp /user/hive/examples /usr/local/hadoop/tmp

b.写入临时文件：

另一种方法是使用 Hive 的 INSERT OVERWRITE LOCAL DIRECTORY 命令将查询结果直接写入本地文件系统。这个命令会将查询结果以文件的形式存储到指定的本地目录。例如：

insert overwrite local directory '/usr/local/hadoop/tmp' select id, name from stu;

查询操作

和SQL的查询完全一样，这里不再赘述。主要使用select…from…where…等语句，再结合关键字group by、having、like、rlike等操作。

这里我们简单介绍一下SQL中没有的case…when…then…句式、join操作和子查询操作。

case…when…then…句式和if条件语句类似，用于处理单个列的查询结果，语句如下：

SELECT id, name,
CASEWHEN id = 1 THEN 'first'WHEN id = 2 THEN 'second'ELSE 'third'
END
FROM stu;

这条语句的作用是：

从 stu 表中选择 id 和 name 列。
对于每一行数据，根据 id 的值，使用 CASE WHEN THEN 表达式来决定返回 'first'、'second' 还是 'third'。
如果 id 是 1，那么返回 'first'。
如果 id 是 2，那么返回 'second'。
对于所有其他情况，返回 'third'。

连接

连接（join）是将两个表中在共同数据项上相互匹配的那些行合并起来, HiveQL 的连接分为内连接、左向外连接、右向外连接、全外连接和半连接 5 种。

a. 内连接(等值连接)

内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

首先，我们先把以下内容插入到course表中（自行完成）。

1 3

2 1

3 1

INSERT INTO TABLE course VALUES (1, 3), (2, 1), (3, 1);

下面, 查询stu和course表中学号相同的所有行，命令如下：

select stu.*, course.* from stu join course on(stu .id=course .sid);

执行结果如下：

b. 左连接

左连接的结果集包括“LEFT OUTER”子句中指定的左表的所有行, 而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行, 则在相关联的结果集中右表的所有选择列均为空值，命令如下：

select stu.*, course.* from stu left outer join course on(stu .id=course .sid);

执行结果如下：

c. 右连接

右连接是左向外连接的反向连接,将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。命令如下：

select stu.*, course.* from stu right outer join course on(stu .id=course .sid);

执行结果如下：

d. 全连接

全连接返回左表和右表中的所有行。当某行在另一表中没有匹配行时,则另一个表的选择列表包含空值。如果表之间有匹配行,则整个结果集包含基表的数据值。命令如下：

select stu.*, course.* from stu full outer join course on(stu .id=course .sid);

执行结果如下：

e. 半连接

半连接是 Hive 所特有的, Hive 不支持 in 操作,但是拥有替代的方案; left semi join, 称为半连接, 需要注意的是连接的表不能在查询的列中,只能出现在 on 子句中。命令如下：

select stu.* from stu left semi join course on(stu .id=course .sid);

执行结果如下：

子查询

标准 SQL 的子查询支持嵌套的 select 子句,HiveQL 对子查询的支持很有限,只能在from 引导的子句中出现子查询。

注意，在定义或是操作表时，不要忘记指定所需数据库。

三、Hive简单编程实践

下面我们以词频统计算法为例，来介绍怎么在具体应用中使用Hive。词频统计算法又是最能体现MapReduce思想的算法之一，这里我们可以对比它在MapReduce中的实现，来说明使用Hive后的优势。

与传统的MapReduce实现相比，使用Hive的优势在于不需要编写Java代码，只需要使用HiveQL即可。

MapReduce实现词频统计的代码可以通过下载Hadoop源码后，在 $HADOOP_HOME/share/hadoop/mapreduce/hadoop-mapreduce-examples-2.7.1.jar 包中找到(wordcount类)，wordcount类由63行Java代码编写而成。

下面首先简单介绍一下怎么使用MapReduce中wordcount类来统计单词出现的次数（HiveQL 脚本实现），具体步骤如下：

1）创建input目录，output目录会自动生成。其中input为输入目录，output目录为输出目录。命令如下：

cd /home/hadoop

mkdir input

mkdir output

2）然后，在input文件夹中创建两个测试文件file1.txt和file2.txt，命令如下：

cd /usr/local/hadoop/input
echo "hello world"> file1.txt
echo "hello hadoop"> file2.txt

hdfs dfs -rm /user/hadoop/input

这个命令用于删除 HDFS 上的 /user/hadoop/input 路径。如果该路径是一个文件，它将被删除。如果是一个目录，需要加上 -r 选项才能删除。因为-rm 命令默认情况下只能删除空目录或单个文件。如果 /user/hadoop/input是非空目录，则这个命令将会失败。

hdfs dfs -rm /user/hadoop/output

类似于上一个命令，这个命令用于删除 HDFS 上的 /user/hadoop/output 路径。

hdfs dfs -rm -r /user/hadoop/input

这个命令用于递归地删除 /user/hadoop/input 目录及其包含的所有文件和子目录。
可以使用 -r 或 --recursive 选项来递归地删除指定路径下的所有文件和子目录。

hdfs dfs -rm -r /user/hadoop/output

类似于上一个命令，这个命令用于递归地删除 /user/hadoop/output 目录及其包含的所有文件和子目录。

hdfs dfs -mkdir /user/hadoop/input

这个命令用于在 HDFS 上创建一个新的目录 /user/hadoop/input。
-mkdir 命令用于创建新目录，什么时候需要加上-p?

hadoop fs -copyFromLocal /usr/local/hadoop/input/file1.txt /user/hadoop/input

这个命令用于将本地文件系统上的文件 /home/hadoop/hadoop/input/file1.txt 复制到 HDFS 上的 /user/hadoop/input 目录。

hadoop fs -copyFromLocal /usr/local/hadoop/input/file2.txt /user/hadoop/input

类似于第六个命令，这个命令用于将 /home/hadoop/hadoop/input/file2.txt 文件复制到 HDFS 上的 /user/hadoop/input 目录。

hdfs dfs -ls /user/hadoop/input

这个命令用于列出 HDFS 上 /user/hadoop/input 目录的内容，显示目录中的文件和子目录。

3）执行如下hadoop命令：

cd /usr/local

hadoop jar /usr/local/hadoop/share/hadoop/mapreduce/hadoop-mapreduce-examples-2.7.1.jar wordcount input output

4）我们可以到output文件夹中查看结果，结果如下：

hdfs dfs -ls /user/hadoop/output

hdfs dfs -cat /user/hadoop/output/*

hdfs dfs -cat /user/hadoop/output/part-r-00000

下面我们通过HiveQL实现词频统计功能，此时只要编写下面7行代码，而且不需要进行编译生成jar来执行。HiveQL实现命令如下：

记得先进入hive

drop table if exists docs;
create table docs(line string);
load data inpath 'input' overwrite into table docs;drop table if exists word_count;
create table word_count as
select word, count(1) as count from
(select explode(split(line,' ')) as word from docs) w
group by word
order by word;

执行上述命令后，可以使用以下命令查看结果：