技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

Hive-QL

 收录于 Hadoop
   约 2120 字   预计阅读 5 分钟 
https://bing.ee123.net/img/rand?artid=145952015

Hive QL

1. 引言

Hive QL是Hive支持的类似SQL的查询语言。Hive QL可以分成DDL、DML和UDF。DDL可以创建数据库和表，进行数据库和表的删除；DML可以进行数据的添加、查询；UDF还支持用户自定义查询函数。

1.1 启动Hive

命令：

start-dfs.sh && stop-dfs.sh
nohup hive --service metastore &
nohup hive --service hiveserver2 &
beeline -u "jdbc:hive2://172.18.0.2:10000" -n root

1.2 docker-compose.yml更新

由于Hive的默认引擎MapReduce在执行Hive QL时报错了，所以下面将配置Hive使用Tez引擎。

配置Tez引擎的hadoop3.3.6_ubuntu24.04镜像的tag更新为1.1，只需要删除原来的两个容器，再将 docker-compose.yml文件中hadoop服务的image改为mengsui/hadoop3.3.6_ubuntu24.04:1.1。

2.配置Hive Tez引擎

Hive的默认引擎是MapReduce，在未来的版本将会启用，并且查询效率不行。

2.1 下载并解压Tez

命令：

cd ~
wget https://dlcdn.apache.org/tez/0.10.4/apache-tez-0.10.4-bin.tar.gz
tar -zxvf apache-tez-0.10.4-bin.tar.gz -C .
mv apache-tez-0.10.4-bin tez-0.10.4

2.3 上传Tez到HDFS

确保HDFS启动，输入命令：

cd ~/tez-0.10.4/share
hadoop fs -mkdir /user/tez
hadoop fs -put tez.tar.gz /user/tez/tez.tar.gz

2.4 复制tez的jar包到hive的lib下

编辑复制脚本：

cd ~
vim copy_tez_lib.sh

按i编辑，写入如下内容，按ESC输入:wq!保存，用 bash copy_tez_lib.sh 运行。

#!/bin/bash

tez_lib1=/root/tez-0.10.4
tez_lib2=/root/tez-0.10.4/lib

for jar in $(ls $tez_lib1 | grep jar); do
        cp $tez_lib1/$jar $HIVE_HOME/lib/
done    

for jar in $(ls $tez_lib2 | grep jar); do
        cp $tez_lib2/$jar $HIVE_HOME/lib/
done

2.5 配置Tez和Hive

2.5.1 tez-site.xml

创建配置文件：

cd ~/hadoop-3.3.6/etc/hadoop
cp ~/tez-0.10.4/conf/tez-default-template.xml tez-site.xml

用 vim tez-site.xml 编辑配置文件，编辑tez.lib.uris。

https://i-blog.csdnimg.cn/direct/ab56deef5d9d44d9824bc35844bc87a1.png#pic_center

编辑tez.history.logging.service.class，然后按ESC输入:wq!保存。

https://i-blog.csdnimg.cn/direct/b8f4dd1ee60046a3a16ef7372872e85c.png

2.5.2 hive-site.xml

用 cd ~/hive-4.0.1/conf && vim hive-site.xml 编辑hive-site.xml，编辑hive.execution.engine，然后按ESC输入:wq!保存。

https://i-blog.csdnimg.cn/direct/b03923c1f74c421681cf4ab2947f5747.png

2.6 处理Slf4j多重绑定

命令：

cd ~/hive-4.0.1/lib
mkdir backup
mv log4j-slf4j-impl-2.18.0.jar backup/
mv slf4j-reload4j-1.7.36.jar backup/

3. 操作数据库

创建数据库： create database name; 。

https://i-blog.csdnimg.cn/direct/5f962870c64b4e60b916e49858e245e8.png#pic_center

查看数据库信息： describe database name; 或 desc database name; 。

https://i-blog.csdnimg.cn/direct/2e9701cbc95d46bab9a5914ccb875f2c.png#pic_center

https://i-blog.csdnimg.cn/direct/4ec7a1d61fca45ebbd188bfc06b11355.png

改变数据库： use name; 。

https://i-blog.csdnimg.cn/direct/167c9c67375146afa657605bc616bac0.png#pic_center

删除数据库： drop database name;

https://i-blog.csdnimg.cn/direct/0c68f1a1b0d54fe0a8b50bed89e3c42f.png#pic_center

展示所有的数据库： show databases;

https://i-blog.csdnimg.cn/direct/567020db10b74975ad2430af0a59138f.png#pic_center

4. 创建表

创建表：

create [external] table [if not exists] name
[(col_name data_type [comment col_comment], ...)]
[comment table_comment]
[partitioned by (col_name data_type [comment col_comment], ...)]
[clustered by (col_name, col_name, ...) [sorted by (col_name [asc|desc], ...)] into num_buckets buckets]
[row format row_format]
[stored as file_format]
[location hdfs_path]

external关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径location。Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表则只删除元数据，不删除数据。

如果文件数据是纯文本，可以使用sorted as textfile。如果数据需要压缩，使用sorted as sequence。

有分区的表可以在创建时使用partitioned by语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行clustered by操作，将若干个列放入一个桶中。也可以利用sort by对数据进行排序。

create table emp (ename string, deptid int, degree int) row format delimited fields terminated by '|'; 。

https://i-blog.csdnimg.cn/direct/6318121d4d98434d8dd644fd6b9e22c0.png

create table dept (deptid int, dname string) row format delimited fields terminated by '|'; 。

https://i-blog.csdnimg.cn/direct/1ab0e42f656c455b8fd88de9fac8a760.png

展示所有表： show tables; 或 show tables in database_name 。

https://i-blog.csdnimg.cn/direct/2b085e98776b433cb5a699a06c8b231a.png

https://i-blog.csdnimg.cn/direct/a323f07a78de4ba481a27b7cf61dca59.png

查看表结构： describe database_name.table_name; 或 desc database_name.table_name ;

https://i-blog.csdnimg.cn/direct/928857709440435f834d3e7c6213f2ea.png

5. 加载数据

加载数据： load data [local] inpath 'path' [overwrite] into table name; 。

local表示本机目录，不加则是HDFS上的目录。overwrite表示删除目标目录，没有则保留，但会覆盖同名旧目录。

进行两次 load data local inpath '/root/hive-4.0.1/examples/files/dept.txt' into table dept; 后，通过 select * from dept; 查看表，可以发现不带overwrite时数据重复写入了两次。

https://i-blog.csdnimg.cn/direct/e6b8ddc79f804c1abca7714b5df619ec.png

https://i-blog.csdnimg.cn/direct/6b0be016f009421b9d7504288936c45a.png

使用overwrite加载数据，可以发现之前的重复数据被删除了。

https://i-blog.csdnimg.cn/direct/e4045b82062742f694df14e3098e0581.png

https://i-blog.csdnimg.cn/direct/a0e8db3136e747868bc97cddc9627a06.png

从其他表中追加导入数据： insert into table_name select * from table_name 。

先创建 create table dept2 (deptid int, dname string) row format delimited fields terminated by '|'; ，然后通过 insert into dept2 select * from dept; 将dept的数据插入到dept2中。

https://i-blog.csdnimg.cn/direct/c3d681937e6e4d2fb796603eaf637f84.png

https://i-blog.csdnimg.cn/direct/8af13f3d9aed4ad7ace8c57f13080a46.png

select * from dept2;

https://i-blog.csdnimg.cn/direct/514f7eeef74140c1aa59c6ce6d2b555a.png

insert into dept2 select * from dept; ， select * from dept2;

https://i-blog.csdnimg.cn/direct/be45218ab64b45efa0e34651e52937fd.png

从其他表中覆盖式导入数据： insert overwrite table name select * from table_name;

insert overwrite table dept2 select * from dept; ， select * from dept2; overwrite会删除原来的数据。

https://i-blog.csdnimg.cn/direct/b8412401bd6849168fe8179f9f163785.png

创建表的同时导入数据： create table name as select * from table_name;

create table dept3 as select * from dept2; ， select * from dept3;

https://i-blog.csdnimg.cn/direct/82a0efb7d7844a9587402de45dc1ae5a.png

导出数据： insert overwrite local directory 'path' select * from table_name;

insert overwrite local directory '/root/dept' select * from dept;

https://i-blog.csdnimg.cn/direct/384c643fd1e546afbb017fab69a7dd47.png

https://i-blog.csdnimg.cn/direct/29112d10941e400e89916d8d340532e4.png#pic_center

6. Hive QL的高级操作(类似SQL操作)

load data local inpath '/root/hive-4.0.1/examples/files/emp.txt' into table emp;

6.1 select

select： select *[/column_name,column_name,···] from table_name;

select ename,degree from emp;

https://i-blog.csdnimg.cn/direct/905560da86554a4084b19b233302562c.png

https://i-blog.csdnimg.cn/direct/31eb7e260bd947969c2e9a8b7e8f9d9b.png

6.2 函数

select upper(ename),deptid,degree from emp;

https://i-blog.csdnimg.cn/direct/0105416ea59142d086cf7f96c599362b.png

6.3 统计函数

select count(*),max(degree),min(degree),avg(degree),sum(degree) from emp;

https://i-blog.csdnimg.cn/direct/f0af8a5edf064ee28ccdad3ed7c6f758.png

https://i-blog.csdnimg.cn/direct/4ef0702a959c4a06999eed12f80d4b15.png

6.4 distinct去除重复值

select distinct deptid from emp;

https://i-blog.csdnimg.cn/direct/3f5fdd87eed548b19dd16670b82b9028.png

https://i-blog.csdnimg.cn/direct/17204c02206a47d190c4e90eca090418.png

6.5 limit限制返回条数

select * from emp limit 3;

https://i-blog.csdnimg.cn/direct/1fc0806494ee4943a895fc0b90f7ea31.png

6.6 为列名取别名

select upper(ename) as empname,deptid as empdeptid from emp limit 3;

https://i-blog.csdnimg.cn/direct/fd1734bfc4b949cfa76f52038b69f449.png

6.7 case when then多路分支

select ename,case when degree < 3 then 'follower'
when degree > 5 then 'leader'
else 'middle'
end as newdegree from emp;

https://i-blog.csdnimg.cn/direct/f994ea3b494f4392b7903e85b0fff075.png

https://i-blog.csdnimg.cn/direct/095333c4890645cca4dc2f96bb8ff6fc.png

6.8 where查找

select * from emp where deptid=31;

https://i-blog.csdnimg.cn/direct/049cc670a9bb4589b2d4e357b15a56ed.png

6.9 like模糊查询

select * from emp where ename like '%o%';

%表示字母o前后可以是任意个字符。

https://i-blog.csdnimg.cn/direct/9e7bcfb8f4754e9d8089b011031666c5.png

6.10 group by分组统计

select deptid,avg(degree) from emp group by deptid;

https://i-blog.csdnimg.cn/direct/75e9942d38d64f0f97737dd308fd3023.png

https://i-blog.csdnimg.cn/direct/c3b24866c966496b961eb70f4acbb321.png

6.11 having过滤分组统计结果

select deptid,avg(degree) from emp group by deptid having avg(degree)>3;

https://i-blog.csdnimg.cn/direct/128303124abc4eb5a0c20fcff3d48216.png

https://i-blog.csdnimg.cn/direct/9e35ab3e53554c7ea54793a60629691c.png

6.12 inner join内联接

select e.*,d.* from emp e join dept d on e.deptid=d.deptid;

https://i-blog.csdnimg.cn/direct/590386560a8645ca9e5c6e4876e0138a.png

https://i-blog.csdnimg.cn/direct/89f6dc8d944b474ebd61240cd5ad104b.png

https://i-blog.csdnimg.cn/direct/e445120b8d0b4363893a6b02cee25e36.png

https://i-blog.csdnimg.cn/direct/bc8e0297db8a4cbf8076f14c78035cff.png

6.13 left outer join和right outer join外联接

select e.*,d.* from emp e left outer join dept d on e.deptid=d.deptid;

left outer join显示左表全部数据，如果右表没有数据与之对应，则显示NULL。

https://i-blog.csdnimg.cn/direct/d3fdfd5dd9bf4eb7952b6d7d65e48cd5.png

https://i-blog.csdnimg.cn/direct/79288b11a96c4c1bbb37a8f878b827fe.png

https://i-blog.csdnimg.cn/direct/f69cd4d5d9d3451bbeea2e5b3d5097f6.png

https://i-blog.csdnimg.cn/direct/a6242a2d3694461d88835a28ec630196.png

select e.*,d.* from emp e right outer join dept d on e.deptid=d.deptid;

right outer join显示右表的全部数据，如果左表没有数据与之对应，则显示NULL。

https://i-blog.csdnimg.cn/direct/4ae93823d9cd43a08ab1ef6660eb4fbb.png

https://i-blog.csdnimg.cn/direct/4d24a32a32c54f919ff5a2138ecc089c.png

https://i-blog.csdnimg.cn/direct/b5ade420bc8944838261228b01bf4948.png

https://i-blog.csdnimg.cn/direct/89b9844b07e8401698665da8b50fa56f.png

6.14 full outer join外部链接

select e.*,d.* from emp e full outer join dept d on e.deptid=d.deptid;

full outer join显示左右表的全部数据，如果左表或者右表中没有对应数据，则显示NULL。

https://i-blog.csdnimg.cn/direct/3ad31742d2124b3b8f99b20f6ac0b188.png

https://i-blog.csdnimg.cn/direct/a8c3dc0e366f4dc39752352bcd1d09ef.png

https://i-blog.csdnimg.cn/direct/ed8870ebff78437f88c195122ac1c669.png

https://i-blog.csdnimg.cn/direct/51e6cd85255b4b089f8073b3d9c179ee.png

6.15 order by排序

select * from emp order by deptid [desc];

https://i-blog.csdnimg.cn/direct/793fc1779aba49919014141d6d595101.png

https://i-blog.csdnimg.cn/direct/0f047a8e368245769769d1da6d0273e3.png

https://i-blog.csdnimg.cn/direct/68c9d2ff82f747b4b2e0040c529da922.png

https://i-blog.csdnimg.cn/direct/d1bc33bdd9894bd1b4fa193bda989fb3.png

参考

吴章勇 杨强著 大数据Hadoop3.X分布式处理实战

更新于 2025-03-03
 数据仓库, Hive, Hadoop
返回 | 主页