大数据之 Hadoop-12-2-Hive 表操作、查询及实战

一、Hive表操作

1、内部表

Hive中默认创建的普通表被称为管理表或内部表。内部表的数据由Hive进行管理,默认存储于数据仓库目录 /user/hive/warehouse 中,可在 Hive配置文件 hive-site.xml 中对数据仓库目录进行更改(配置属性 hive.metastore.warehouse.dir ) 。

删除内部表时,表数据和元数据将一起被删除

表操作

先启动hadoop

[root@centos01 ~]# su hadoop
[hadoop@centos01 root]$ cd /opt/modules/hadoop-3.1.3/sbin
[hadoop@centos01 sbin]$ start-all.sh

在每次执行hql语句之前 都要先执行下面这句话,可以提高执行效率,具体原因请看下边的相关文章。

set hive.exec.mode.local.auto=true;
普通表操作
$ hive
# 创建测试数据库
# 指定位置
> create database test_db LOCATION '/input/db_test.db';
# 使用默认位置(/user/hive/warehouse)
> create database test_db;  
> USE test_db
> CREATE TABLE student(id INT, name STRING);
> DESC student;
> INSERT INTO student VALUES(1000,'Lily');
> SELECT * FROM student;

在hadoop中查看创建的表:

[hadoop@centos01 ~]$ hadoop fs -ls -R /input/
drwxr-xr-x   - hadoop supergroup          0 2021-09-14 08:33 /input/db_hive.db
drwxr-xr-x   - hadoop supergroup          0 2021-09-15 08:00 /input/db_test.db
drwxr-xr-x   - hadoop supergroup          0 2021-09-15 08:00 /input/db_test.db/student
-rw-r--r--   2 hadoop supergroup         83 2021-07-10 08:58 /input/wc.txt
将本地文件导入 Hive

我们可以将本地文件的数据直接导入到Hive表中,但是本地文件中数据的格式需要在创建表的时候指定。
(1)新建学生成绩表score,其中学号 sno 为整型,姓名name 为字符串,得分score 为整型,并指定以Tab键作为字段分隔符:

hive> CREATE TABLE score(
    > sno INT,
    > name STRING,
    > score INT)
    > row format delimited fields terminated by '\t';

(2)在本地目录 /home/hadoop 中新建 score.txt 文件,并写入以下内容,列之间用Tab键 隔开:

1001 zhangsan 98
1002 lisi 92
1003 wangwu 87

(3)执行以下命令,将score.txt中的数据导入到表score 中:

hive> LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' INTO TABLE score;

(4)查询表score的所有数据:

hive> SELECT * FROM score;
OK
1   lisi    20
2   wangwu  21
3   wenying 30
4   wangchao    30
Time taken: 12.241 seconds, Fetched: 4 row(s)

(5)查看HDFS 数据仓库中对应的数据文件,可以看到,score.txt已被上传到了文件夹score中,如图所示。

[hadoop@centos01 ~]$ hadoop fs -ls -R /input/
drwxr-xr-x   - hadoop supergroup          0 2021-09-15 08:18 /input/db_test.db
drwxr-xr-x   - hadoop supergroup          0 2021-09-15 08:27 /input/db_test.db/score
-rwxr-xr-x   2 hadoop supergroup         49 2021-09-15 08:27 /input/db_test.db/score/score.txt
drwxr-xr-x   - hadoop supergroup          0 2021-09-15 08:13 /input/db_test.db/student
-rwxr-xr-x   2 hadoop supergroup         10 2021-09-15 08:12 /input/db_test.db/student/000000_0

查看 score.txt 的内容:

[hadoop@centos01 ~]$ hadoop fs -cat /input/db_test.db/score/score.txt
2021-09-15 08:34:56,716 INFO sasl.SaslDataTransferClient: SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false
1   lisi    20
2   wangwu  21
3   wenying 30
4   wangchao    30
[hadoop@centos01 ~]$ 

删除表数据:

> DROP TABLE IF EXISTS test_db.student;

注意: Hive LOAD 语句只是将数据复制或移动到数据仓库中 Hive表对应的位置,不会在加载数据的时候做任何转换工作。因此,如果手动将数据复制到表的相应位置与执行LOAD加载操作所产生的效果是一样的。

2、外部表

除了默认的内部表以外,Hive也可以使用关键字“EXTERNAL”创建外部表。外部表的数据可以存储于数据仓库以外的位置,因此Hive并非认为其完全拥有这份数据
外部表在创建的时候可以关联 HDFS 中已经存在的数据,也可以手动添加数据。删除外部表不会删除表数据,但是元数据将被删除
(1)创建外部表时,如果不指定LOCATION 关键字,则默认将表创建于数据仓库目录中。例如,执行以下命令,在数据库 test_db中创建外部表emp:

hive> CREATE EXTERNAL TABLE test_ db.emp(id INT, name STRING);
Time taken: 0.299seconds

3、分区表

Hive可以使用关键字PARTITIONED BY对一张表进行分区操作。可以根据某一列的值将表分为多个分区,每一个分区对应数据仓库中的一个目录(相当于根据列的值将表数据进行分目录存储)。当查询数据的时候,根据WHERE条件Hive只查询指定的分区而不需要全表扫描,从而可以加快数据的查询速度。在HDFS文件系统中,分区实际上只是在表目录下嵌套的子目录。
Hive中的分区好比关系型数据库中的索引。例如,有一张数据量非常大的学生表“student”,现需要查询年龄age等于18的所有数据。在关系型数据库中,需要对年龄age列建立索引,当查询时,数据库会先从索引列中找到匹配的数据,然后再根据匹配数据查询一整行数据。如果不建立索引则将先扫描每一行数据,然后取出该行数据的字段age进行对比。在 Hive中,创建表的时候可以将列age设置为分区列,然后向表“student”添加或导入数据时需要指定分区值(即数据所属的分区),例如设置分区值为age=18,则会在表目录下生成一个名为age=18的子目录,年龄等于18的所有数据应当存储于该目录下。当查询age=18时,只查询指定的分区即可。

“student”表分区后的目录结构如图所示。
file

(1) 创建分区表

在数据库test db 中创建分区表“student”,表“student”包含四列: id(学号)、name(姓名)、age(年龄)和gender(性别),将年龄age作为分区列。命令如下

hive> CREATE TABLE test_db.student(
>id INT,
>name STRING,
>>gender STRING)
>PARTITIONED BY (age INT)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

需要注意的是,创建表时指定的表的列中不应该包含分区列,分区列需要使用关键字 PARTITIONED BY 在后面单独指定。Hive将把分区列排在普通列之后。

(2)导入数据。

在本地目录/home/hadoop中创建数据文件 file1.txt,并写入以下数据(注意,列之间用Tab键隔开):

1 zhangsan male
2 zhanghua female
3 wanglulu female

然后将数据文件file1.txt导入表“student”中,同时指定分区值 age=17
命令如下:

hive> LOAD DATA LOCAL INPATH '/home/hadoop/file1.txt'
>INTO TABLE test_db.student
> PARTITION(age=17);

4、分桶表

在Hive中,可以将表或者分区进一步细分成桶,桶是对数据进行更细粒度的划分,以便获得更高的查询效率。桶在数据存储上与分区不同的是,一个分区会存储为一个目录,数据文件存储于该目录中,而一个桶将存储为一个文件,数据内容存储于该文件中

file

分桶表创建

(1)创建分桶表。
创建用户表“user_info”,并根据user_id进行分桶,桶的数量为6,
命令如下:

hive>CREATE TABLE user info (user id INT,name STRING
> CLUSTERED BY (user id)
> INTO 6 BUCKETS
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

二、Hive查询

Hive SQL 查询语句基本和 MySQL查询语句一样,所以这里暂时略过。

三、实战

案例1:HBase与Hive整合

我们已经知道,HBase数据库没有类SQL 的查询方式,因此在实际的业务中操作和计算数据非常不方便。而 Hive支持标准的SQL语法(HiveQL),若将Hive与 HBase整合,则可以通过HiveQL直接对HBase的表进行读写操作,让HBase支持JOIN、GROUP等SQL查询语法,完成复杂的数据分析。甚至可以通过连接和联合将对HBase表的访问与Hive表的访问结合起来进行统计与分析。

Hive与 HBase整合的实现是利用两者本身对外的API接口互相通信来完成的,其具体工作由Hive安装主目录下的lib文件夹中的 hive-hbase-handler-x.y.z.jar 工具类来实现。

Hive与 HBase整合的核心是将Hive中的表与HBase中的表进行绑定,绑定的关键是HBase中的表如何与Hive中的表在列级别上建立映射关系。

 例如,HBase 中有一张表 hbase_table, 该表数据模型为:
 ![file](http://digtime.cn/uploads/images/202109/16/1/jfDOl6TAge.png)

 则对应的Hive表的数据模型如下图所示:
 ![file](http://digtime.cn/uploads/images/202109/16/1/AZd7AQjlwJ.png)

下面具体讲解 Hive 如何与HBase进行整合。

需要的各组件安装版本说明:

    [root@centos01 modules]# ls -l
总用量 8
drwxr-xr-x. 10 root   root    184 9月  13 08:09 apache-hive-2.3.9-bin
drwxr-xr-x. 12 hadoop hadoop  210 9月  12 00:31 hadoop-3.1.3
drwxr-xr-x.  8 root   root    193 9月  16 08:37 hbase-2.2.7
drwxr-xr-x.  7 hadoop hadoop  245 4月   2 2019 jdk1.8.0_211
drwxr-xr-x.  7 root   root    101 7月  29 23:49 kafka_2.12-2.5.0
drwxr-xr-x.  9 kaiyi  kaiyi  4096 12月 19 2017 sqoop-1.4.7
drwxr-xr-x.  8 root   root    161 7月  17 13:42 zookeeper-3.5.9

(1)前提条件
在整合之前,应先安装好Hive,并确保Hive能正常使用。Hive可安装于HBase集群的任意一个节点上。

# 1、启动Hadoop
[root@centos01 ~]# su hadoop
[hadoop@centos01 root]$ cd /opt/modules/hadoop-3.1.3/sbin
[hadoop@centos01 sbin]$ start-all.sh

# 2、启动hive(无需启动)

(2)启动HBase与Zookeeper

$ cd /opt/modules/hbase-2.2.7
$ bin/start-hbase.sh

在启动HBase的时候,Zookeeper也会同时启动。

(3)修改Hive配置文件 hive-site.xml。
修改 $HIVE_HOME/conf 下的配置文件 hive-site.xml,添加Hive的 HBase和 ZooKeeper 依赖包,内容如下:

<!--配置ZooKeeper集群的访问地址-->
<property>
<name>hive.zookeeper.quorum</name>
<value>centos01:2181, centos02:2181, centos03:2181</value>
</property>
<!--配置依赖的HBase、 ZooKeeper的jar文件-->
<property>
<name>hive.aux.jars.path</name>
<value>
file:///opt/modules/hbase-2.2.7/lib/hbase-common-2.2.7.jar,
file:///opt/modules/hbase-2.2.7/lib/hbase-client-2.2.7.jar,
file:///opt/modules/hbase-2.2.7/lib/hbase-server-2.2.7.jar,
file:///opt/modules/hbase-2.2.7/lib/hbase-hadoop2-compat-2.2.7.jar,
file:///opt/modules/hbase-2.2.7/lib/netty-all-4.0.23.Final.jar,
file:///opt/modules/hbase-2.2.7/lib/hbase-protocol-2.2.7.jar,
file:///opt/modules/zookeeper-3.5.9/zookeeper-3.5.9.jar
</value>
</property>

上述配置中首先指定了ZooKeeper集群的访问地址,若ZooKeeper集群端口统一为2181,此配置项可以省略,因为Hive默认将本地节点的2181端口作为ZooKeeper集群的访问地址。
然后指定了HBase、Hive和 ZooKeeper安装目录下的lib文件夹中的相关jar文件,Hive在启动的时候会将上述配置的本地jar文件加入到ClassPath 中。
在Hive2.3中,Hive安装主目录下的lib文件夹中实际上已经存在了上述jar文件,但是版本不同,为了防止产生兼容性问题,需要引用HBase与ZooKeeper 中的jar文件。
到此,Hive 与 HBase整合完毕。

Hive 中操作HBase 的方式主要有两种,下面分别介绍:

Hive 创建表的同时创建HBase表

(1) 在 Hive中创建学生表“hive_student”:

$ hive
hive> show databases;
hive > USE test_db;
hive> CREATE TABLE hive_student(id INT, name STRING)
        > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
        > WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:name")
        > TBLPROPERTIES ("hbase.table.name" = "hive_student");

上述创建命令中的参数含义如下。

  • STORED BY:指定用于Hive与HBase通信的工具类 HBaseStorageHandler
  • WITH SERDEPROPERTIES:指定HBase表与Hive表对应的列。此处":key.cf1:name"中的key指的是HBase表的rowkey列,对应Hive表的id列; cf1:name指的是HBase表中的列族cfl和cfl中的列name,对应Hive表的name列。Hive列与HBase列的对应不是通过列名称对应的,而是通过列的顺序。
  • TBLPROPERTIES:指定HBase表的属性信息。参数值“hive_student”代表HBase的表名。

在创建表的时候报这样的错误:

java.lang.IllegalAccessError: tried to access method com.google.common.base.Stopwatch.<init>()V from class org.apache.hadoop.hbase.zookeeper.MetaTableLocator

原因:
hive和HBase的两个guava.jar版本不一致
两个位置分别位于下面两个目录:

# apache-hive-2.3.9-bin
- /opt/modules/apache-hive-2.3.9-bin/lib  (guava-27.0-jre.jar)

# hbase-2.2.7
- /opt/modules/hbase-2.2.7/lib (guava-11.0.2.jar)

解决办法:
删除低版本的那个,将高版本的复制到低版本目录下,即可以看到hive(guava-27.0-jre.jar)的比 HBase (guava-11.0.2.jar) 里边的 guava.jar 版本高,则需要删掉HBase 里边的,然后将hive里边的 guava-27.0-jre.jar 拷贝到HBase对应的目录即可。

(2)创建成功后,新开一个XShell窗口,在 HBase Shell中查看创建的表:

$ cd /opt/modules/hbase-2.2.7
$ [root@centos01 hbase-2.2.7]# bin/hbase shell
hbase(main):001:0> list
TABLE
hive_student

(3)在 Hive中向表“hive_student”添加一条数据(底层将开启MapReduce任务执行):

hive> INSERT INTO hive student VALUES(1, 'zhangsan');

添加成功后,查看Hive数据仓库对应HDFS目录的表数据,发现数据为空,命令如下:

$ hadoop fs -ls -R /user/hive/warehouse/test_db.db/hive_student

然后查看HBase中的表“hive student”的数据:

hbase (main):003:0>scan 'hive student'

column=cf1:name, timestamp=1536138928227, value=zhangsan
1 row(s) in 1.9100 seconds

从上述查询信息可以看到,在 Hive中成功地将一条数据添加到了HBase表中。
(4) 修改HBase表数据。

修改HBase表“hive_student”,将姓名zhangsan改为lisi:

到此,我们可以得出一个结论:Hive与HBase整合后,实际上是将HBase作为Hive的数据源,数据存储在HBase中(实际上存储在由HRegionServer管理的HDFS中)而不是Hive的数据仓库中

Hive 创建外部表关联已存在的HBase表

赞略。

从上述两种操作方式可以得出以下结论:

  • 在Hive中创建的HBase映射表的数据都只存在于HBase中,Hive的数据仓库中不存在数据。
  • HBase是Hive的数据源,Hive相当于HBase的一个客户端工具,可以对HBase数据进行查询与统计。
  • 若HBase集群停止,Hive将查询不到HBase中的数据。
  • 通过HBase的put语句添加一条数据比Hive的 insert语句效率要高,因为Hive 的 insert语句需要开启MapReduce任务执行数据添加操作。

案例2:Hive分析搜狗用户搜索日志

测试数据可以从搜狗实验室进行下载(地址: http://www.sogou.com/labs/resource/q.php)。搜狗实验室提供约一个月的 Sogou搜索引擎部分网页查询需求及用户单击情况的网页查询日志数据集合。该数据共分成了三部分:迷你版(样例数据,376KB)、精简版(一天数据,63 MB)和完整版( 1.9 GB)。此处下载精简版数据进行操作演示。
数据的清洗格式优化这里略掉。

file

创建表语句:

hive> CREATE TABLE activelog(
> time STRING,
> user id STRING,
> keyword STRING, 
> page_ rank INT,
> click order INT,
> url STRING)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ',';

创建成功后,将文件 SogouQ.reduced的数据导入到表“activelog”中:

hive> LOAD DATA LOCAL INPATH  '/home/hadoop/SogouQ.reduced'  INTO TABLE activelog;

数据分析:
(1)查询前10条数据,查询语句如下:

hive> SELECT* FROM activelog LIMIT 10;

(2)查询前10个访问量最高的用户ID及访问数量,并按照访问量降序排列。查询语句及结果如下:

hive> SELECT user id, COUNT(*) As num
> FROM activelog
>  GROUP BY user id
> ORDER BY num DESC
> LIMIT10;
OK
user id
num
11579135515147154 4316383499980790535 385
7822241147182134 37090075555806407433512385969593715146226519493440787543 223

(3)分析链接排名与用户单击的相关性。
下面的语句以链接排名(page_rank)进行分组(并排除分组后的不规范数据,排名为空或0),查询链接排名及其单击数量,然后将结果按照链接排名升序显示,最终取前10条数据。

hive> SELECT page rank, COUNT(*) AS num FROM activelog
>GROUP BY page_rank
> HAVING page rank IS NOT NULL
> AND page rank<>0
>ORDER BY page rank
> LIMIT 10;

相关文章:
Hive优化(提高hive运行速度)

为者常成,行者常至