HiveQL练习（hive3.x）

2024-04-14 21:36
文章标签 练习 hiveql hive3

本文主要是介绍HiveQL练习（hive3.x），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

零、准备工作

1. Hive环境安装

参见搭建Hive 3.x环境（CentOS 9 + Hadoop3.x）

2. 准备数据

在虚拟机HOME目录创建如下文件内容：

cd /root
vi emp.csv

内容如下：

7369,SMITH,CLERK,7902,1980/12/17,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20
7839,KING,PRESIDENT,,1981/11/17,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,,20
7900,JAMES,CLERK,7698,1981/12/3,950,,30
7902,FORD,ANALYST,7566,1981/12/3,3000,,20
7934,MILLER,CLERK,7782,1982/1/23,1300,,10

vi dept.csv

内容如下：

10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

3. 进入Hive CLI

在hadoop1上执行下面命令启动Hadoop集群

start-all.sh
mapred --daemon start historyserver

在hadoop2上启动MetaStore服务

nohup hive --service metastore &

在hadoop2或者hadoop3上进入Hive CLI

hive

一、HQL基本练习

1. 上传数据

创建HDFS目录/hivedata

hive> dfs -mkdir /hivedata;

上传数据到/hive_test

hive> dfs -put /root/emp.csv /hivedata;
hive> dfs -put /root/dept.csv /hivedata;

验证数据是否上传成功

hive> dfs -ls /hivedata;

在这里插入图片描述

Hive Cli可以执行如下几类命令，需要注意的是命令要使用;作为结束符：

  • 执行Shell命令

在Hive中，你可以使用!符号来执行Shell命令。例如，你可以在Hive中执行ls命令来列出Linux根目录下的文件。

hive > !ls;
  • 执行HDFS命令

在Hive中，你也可以直接使用HDFS命令来操作HDFS上的文件。例如，你可以使用dfs -mkdir命令来创建一个新的目录。

hive > dfs -mkdir /jsuttest;
  • 执行HQL

在Hive中，你可以执行HQL语句。

hive > show databases;

2. 数据库的基本操作

hive> show databases --查看数据库;
hive> create database tmp_db location '/home/hive/db/tmp_db' --创建数据库时指定位置;
hive> desc database tmp_db --查看一下数据库的信息;#查看一下数据库的HDFS目录;
hive> dfs -ls -R /home/hive/db/;
hive> drop database tmp_db --删除数据库;hive> create database test_db;
hive> use test_db;
hive> select current_database()  --查看当前使用的数据库;
#观察HDFS的目录变化;
hive> dfs -ls -R /user/hive;

3. 内部表的基本操作

hive> create table emp> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int) > row format delimited fields terminated by ',';
hive> desc emp;
hive> desc formatted emp;
hive> load data inpath '/hivedata/emp.csv' into table emp; #观察HDFS目录变化;
hive> dfs -ls -R /user/hive/warehouse;
hive> dfs -ls -R /hivedata;hive> select * from emp;
hive> drop table emp  --删除表;#观察HDFS目录变化;
hive> dfs -ls -R /user/hive/warehouse;

4. 外部表的基本操作

hive> dfs -mkdir -p /hivedata/emp.tb;
hive> dfs -put /root/emp.csv /hivedata/emp.tb;
hive> create external table emp> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int) > row format delimited fields terminated by ','> location '/hivedata/emp.tb';hive> desc formatted emp;#观察HDFS目录变化;
hive> dfs -ls -R /user/hive/warehouse;
hive> dfs -ls -R /hivedata/emp.tb;hive> select * from emp;
hive> drop table emp  --删除表;#观察HDFS目录变化;
hive> dfs -ls -R /hivedata/emp.tb;

5. 分区表的基本操作

hive> create external table emp_ext> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int) > row format delimited fields terminated by ','> location '/hivedata/emp.tb';
hive> create table emp_partition> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int)> partitioned by (deptno int)> row format delimited fields terminated by ',';
hive> desc emp_partition  --查看表的信息;
hive> insert into table emp_partition partition(deptno=10) > select empno,ename,job,mgr,hiredate,sal,comm from emp_ext where deptno=10  --插入一个分区的数据;
hive> insert into table emp_partition partition(deptno=20) > select empno,ename,job,mgr,hiredate,sal,comm from emp_ext where deptno=20;
hive> select * from emp_partition;
hive> dfs -ls -R /user/hive;
hive> dfs -cat /user/hive/warehouse/test_db.db/emp_partition/deptno=10/000000_0;
hive> show partitions emp_partition  --查看分区个数;
hive> desc formatted emp_partition;#hdfs新建一个目录;
hive> dfs -mkdir /user/hive/warehouse/test_db.db/emp_partition/deptno=30;  
hive> show partitions emp_partition  --查看分区;
hive> msck repair table emp_partition  --修复表;
hive> show partitions emp_partition   --查看分区;

6. 桶表

hive> create table emp_bucket> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int)> clustered by(ename) into 2 buckets;
hive> insert into emp_bucket select * from emp_ext;
#观察HDFS目录变化;
hive> dfs -ls -R /user/hive;
hive> select * from emp_bucket;

7. 其他

hive> alter table emp_ext rename to emp_new;
hive> alter table emp_new set tblproperties('EXTERNAL'='FALSE');
hive> desc formatted emp_new;
hive> drop table emp_new;
hive> dfs -ls -R /hivedata;

二、查询分析

1. 数据准备

hive> create table emp> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int) > row format delimited fields terminated by ',';hive> load data local inpath '/root/emp.csv' into table emp;hive> create table dept(deptno int,dname string,loc string) > row format delimited fields terminated by ',';  
hive> load data local inpath '/root/dept.csv' into table dept;

2. 简单查询

1）全表和特定列查询

hive> select * from emp --查询所有列;
hive> select empno, ename from emp --查询指定列;
hive> select distinct mgr from emp --查询有哪些管理者;

2）列别名

重命名一个列，紧跟列名，也可以在列名和别名之间使用关键字 AS

hive> select ename AS name, deptno dn from emp;

3）算术运算

+ - * 、 % & | ^ ~

hive> select empno,ename,sal*12 from emp --计算年薪;

4）LIMIT语句

hive> select * from emp limit 2;

5）常用函数

hive> select count(*) cnt from emp  --求总行数（count）;
hive> select max(sal) max_sal from emp  --求工资的最大值（max）;
hive> select min(sal) min_sal from emp   --求工资的最小值（min）;
hive> select sum(sal) sum_sal from emp  --求工资的总和（sum）;
hive> select avg(sal) avg_sal from emp  --求工资的平均值（avg）;
hive> select empno,ename,sal,comm, sal*12+nvl(comm, 0) from emp  --注意NULL的处理;
hive> select empno,ename,job,sal,> case job when 'PRESIDENT' then sal+1000> when 'MANAGER' then sal+800> else sal+400> end> from emp  --加薪 PRESIDENT+1000，MANAGER+800，其他+400;

6）Where子句

hive> select * from emp where sal >1000;

7）Like 和 RLike

  • 使用 LIKE 运算选择类似的值, 选择条件可以包含字符或数字:

    • % 代表零个或多个字符(任意个字符)。
    • _ 代表一个字符**。**

  • RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。

hive> select * from emp where ename LIKE 'A%'  --查找名字以 A 开头的员工信息;
hive> select * from emp where ename LIKE '_A%' --查找名字中第二个字母为 A 的员工信息;
hive> select * from emp where ename RLIKE '[A]'  --查找名字中带有 A 的员工信息;

8）比较运算符（Between/In/ Is Null）

等于小于小于等于大于大于等于不等NULL****安全的等于
=<<=>>=<> !=<=>

  • A [NOT] BETWEEN B AND C ：A是否属于[B,C]区间，A,B,C任一为NULL, 结果为NULL

  • A IS [NOT] NULL：A是否为空

  • A IN(数值 1, 数值 2)：A是否是列表中的值

  • A [NOT] LIKE B：A是否与B相似

  • A RLIKE B, A REGEXP B：B是基于Java的正则表达式

hive> select * from emp where sal =5000  --查询出薪水等于 5000 的所有员工;
hive> select * from emp where sal between 500 and 1000 --查询工资在 500 到 1000 的员工信息;
hive> select * from emp where comm is null  --查询 bonus 为空的员工信息;
hive> select * from emp where sal IN (1500, 5000) --查询工资是 1500 或 5000 的员工信息;

9) 逻辑运算符（And/Or/Not）

hive> select * from emp where sal>1000 and deptno=30   --查询薪水大于 1000，部门是 30;
hive> select * from emp where sal>1000 or deptno=30   --查询薪水大于 1000，或者部门是 30;
hive> select * from emp where deptno not IN(30, 20)   --查询除了 20 部门和 30 部门以外的员工信息;

10) 分组

  • Group By 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
hive> select deptno, avg(sal) avg_sal from emp group by deptno  --计算 emp 表每个部门的平均工资;
hive> select deptno, job, max(e.sal) max_sal from emp e group by deptno, job   --计算 emp 每个部门中每个岗位的最高薪水;
  • Having 语句 与 where 不同: where 后面不能写分组函数，而 having 后面可以使用分组函数；having 只用于 group by 分组统计语句。
hive> select deptno, avg(sal) from emp group by deptno   --求每个部门的平均工资;
hive> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000   --求每个部门的平均薪水大于 2000 的部门;

11) Join语句

  • 内连接

  • 左（右）外连接

  • 全外连接： full join

  • 多表连接

hive> --根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称;
hive> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno  --内连接;
hive> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno  --左连接;

12）排序

  • 全局排序(Order By)
    • Order By：全局排序，只有一个 Reducer；
    • 排序方式：ASC（ascend）: 升序（默认）DESC（descend）: 降序
hive> select * from emp order by sal desc --按工资降序排列; 
hive> select ename, deptno, sal from emp order by deptno, sal -- 多个列排序;
  • 每个Reduce内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序

hive> set mapreduce.job.reduces=3   --设置 reduce 个数;
hive> set mapreduce.job.reduces     --查看设置 reduce 个数;
hive> select * from emp sort by deptno desc   --根据部门编号降序查看员工信息;
hive> insert overwrite local directory '/home/hadoop/data/sortby-result'> select * from emp sort by deptno desc   --将查询结果导入到文件中（按照部门编号降序排序）;
  • 分区（Distribute By）
    • Distribute By： 在有些情况下，需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。
    • 对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果
hive> set mapreduce.job.reduces=3;
hive> insert overwrite local directory '/home/hadoop/data/distribute-result' > select * from emp distribute by deptno sort by empno desc   --先按照部门编号分区，再按照员工编号降序排序;#查看输出目录;
hive> !ls /home/hadoop/data/distribute-result;
  • Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

hive> select * from emp cluster by deptno;
hive> select * from emp distribute by deptno sort by deptno;

这篇关于HiveQL练习（hive3.x）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/904080。

相关文章

RabbitMQ练习（AMQP 0-9-1 Overview）

RabbitMQ练习（AMQP 0-9-1 Overview）

1、What is AMQP 0-9-1 AMQP 0-9-1（高级消息队列协议）是一种网络协议，它允许遵从该协议的客户端（Publisher或者Consumer）应用程序与遵从该协议的消息中间件代理（Broker，如RabbitMQ）进行通信。 AMQP 0-9-1模型的核心概念包括消息发布者（producers/publisher）、消息（messages）、交换机（exchanges）、
阅读更多...
【Rust练习】12.枚举

【Rust练习】12.枚举

练习题来自：https://practice-zh.course.rs/compound-types/enum.html 1 // 修复错误enum Number {Zero,One,Two,}enum Number1 {Zero = 0,One,Two,}// C语言风格的枚举定义enum Number2 {Zero = 0.0,One = 1.0,Two = 2.0,}fn m
阅读更多...
MySql 事务练习

MySql 事务练习

事务（transaction） -- 事务 transaction-- 事务是一组操作的集合，是一个不可分割的工作单位，事务会将所有的操作作为一个整体一起向系统提交或撤销请求-- 事务的操作要么同时成功，要么同时失败-- MySql的事务默认是自动提交的，当执行一个DML语句，MySql会立即自动隐式提交事务-- 常见案例：银行转账-- 逻辑：A给B转账1000：1.查询
阅读更多...
html css jquery选项卡 代码练习小项目

html css jquery选项卡 代码练习小项目

在学习 html 和 css jquery 结合使用的时候 做好是能尝试做一些简单的小功能，来提高自己的 逻辑能力，熟悉代码的编写语法 下面分享一段代码 使用html css jquery选项卡 代码练习 <div class="box"><dl class="tab"><dd class="active">手机</dd><dd>家电</dd><dd>服装</dd><dd>数码</dd><dd
阅读更多...
014.Python爬虫系列_解析练习

014.Python爬虫系列_解析练习

我 的 个 人 主 页：👉👉 失心疯的个人主页 👈👈 入 门 教 程 推 荐 ：👉👉 Python零基础入门教程合集 👈👈 虚 拟 环 境 搭 建 ：👉👉 Python项目虚拟环境(超详细讲解) 👈👈 PyQt5 系 列 教 程：👉👉 Python GUI(PyQt5)文章合集 👈👈 Oracle数据库教程：👉👉 Oracle数据库文章合集 👈👈 优
阅读更多...
如何快速练习键盘盲打

如何快速练习键盘盲打

盲打是指在不看键盘的情况下进行打字，这样可以显著提高打字速度和效率。以下是一些练习盲打的方法： 熟悉键盘布局：首先，你需要熟悉键盘上的字母和符号的位置。可以通过键盘图或者键盘贴纸来帮助记忆。 使用在线打字练习工具：有许多在线的打字练习网站，如Typing.com、10FastFingers等，它们提供了不同难度的练习和测试。 练习基本键位：先从学习手指放在键盘上的“家位”开始，通常是左手的
阅读更多...
anaconda3下的python编程练习-csv翻译器

anaconda3下的python编程练习-csv翻译器

相关理解和命令 一、环境配置1、conda命令2、pip命令3、python命令 二、开发思路三、开发步骤 一、环境配置 1、conda命令 镜像源配置 conda config --show channels //查看镜像源conda config --remove-key channels //删除添加源，恢复默认源#添加镜像源conda config --ad
阅读更多...
推荐练习键盘盲打的网站

推荐练习键盘盲打的网站

对于初学者来说，以下是一些推荐的在线打字练习网站： 打字侠：这是一个专业的在线打字练习平台，提供科学合理的课程设置和个性化学习计划，适合各个水平的用户。它还提供实时反馈和数据分析，帮助你提升打字速度和准确度。 dazidazi.com：这个网站提供了基础的打字练习，适合初学者从零开始学习打字。 Type.fun打字星球：提供了丰富的盲打课程和科学的打字课程设计，还有诗词歌赋、经典名著等多样
阅读更多...
综合DHCP、ACL、NAT、Telnet和PPPoE进行网络设计练习

综合DHCP、ACL、NAT、Telnet和PPPoE进行网络设计练习

描述：企业内网和运营商网络如上图所示。 公网IP段：12.1.1.0/24。 内网IP段：192.168.1.0/24。 公网口PPPOE 拨号采用CHAP认证，用户名:admin 密码:Admin@123 财务PC 配置静态IP：192.168.1.8 R1使用模拟器中的AR201型号，作为交换路由一体机，下图的WAN口为E0/0/8口，可以在该接口下配置IP地址。 可以通过
阅读更多...
JAVA学习-练习试用Java实现“删除有序数组中的重复项”

JAVA学习-练习试用Java实现“删除有序数组中的重复项”

问题： 给你一个有序数组 nums ，请你 原地 删除重复出现的元素，使每个元素 只出现一次 ，返回删除后数组的新长度。 不要使用额外的数组空间，你必须在 原地 修改输入数组 并在使用 O(1) 额外空间的条件下完成。 说明: 为什么返回数值是整数，但输出的答案是数组呢? 请注意，输入数组是以「引用」方式传递的，这意味着在函数里修改输入数组对于调用者是可见的。 你可以想象内部操作如下
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2