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;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/544151.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

SpringMVC--获取请求参数 / 域对象共享数据

目录 1. SpringMVC 获取请求参数 1.1. 通过ServletAPI获取 1.2. 控制器方法形参获取 1.3. RequestParam 1.4. RequestHeader 1.5. CookieValue 1.6. 通过POJO获取请求参数 1.7. 解决获取请求参数的乱码问题 2. 域对象共享数据 2.1. 三大域对象 2.2. 准备工作 2.3. S…

8:系统开发基础--8.5:系统设计、8.6:系统测试 、8.7:软件维护 、8.8:软件质量保证、8.9:软件文档

转上一节&#xff1a; http://t.csdnimg.cn/X0GjWhttp://t.csdnimg.cn/X0GjW 8.5&#xff1a;系统设计 考点1&#xff1a;系统设计概述 1&#xff1a;软件设计的任务与活动 体系结构设计&#xff1a;定义软件系统各主要部件之间的关系。 数据设计&#xff1a;基于E-R图确定…

免费的 ChatGPT 网站(六个)

&#x1f525;博客主页&#xff1a; 小羊失眠啦. &#x1f3a5;系列专栏&#xff1a;《C语言》 《数据结构》 《C》 《Linux》 《Cpolar》 ❤️感谢大家点赞&#x1f44d;收藏⭐评论✍️ 文章目录 一、insCode二、讯飞星火三、豆包四、文心一言五、通义千问六、360智脑 现在智能…

专题十三、预处理器

预处理器 1. 预处理器的工作原理2. 预处理指令3. 宏定义3.1 简单的宏3.2 带参数的宏3.3 # 运算符3.4 ## 运算符3.5 宏的通用属性3.6 宏定义中的圆括号3.7 创建较长的宏3.8 预定义宏3.9 C99 中新增的预定义宏3.10 空的宏参数3.11 参数个数可变的宏3.12 __func__ 标识符 4. 条件编…

Leetcode 416.分割等和子集

题目 思路 使用0-1背包的思路。 之前0-1背包是说有N个物品&#xff0c;一个最大承重重量为W的背包。每个物品都有各自的重量和value,要让放入背包中物品价值总和最大。 这道题如何对应成0-1背包&#xff0c;看下面的分析。 背包的大小&#xff1a;要想两个子集元素和相等&am…

宝塔面板Docker+Uwsgi+Nginx+SSL部署Django项目

这次为大家带来的是从零开始搭建一个django项目并将它部署到linux服务器上。大家可以按照我的步骤一步步操作&#xff0c;最终可以完成部署。 步骤1&#xff1a;在某个文件夹中创建一个django项目 安装django pip install django创建一个django项目将其命名为djangoProject …

【板栗糖GIS】如何给微软拼音输入法加上小鹤双拼

【板栗糖GIS】如何给微软拼音输入法加上小鹤双拼 用过在注册表里新建的方法&#xff0c;结果弄完没有出现小鹤双拼方案&#xff0c;想到了自己写reg表 目录 1. 新建一个txt文件 2. 把.txt的后缀名改成.reg&#xff0c;双击运行 3. 在设置中找到微软输入法-常规 1. 新建一个…

二分查找(函数法)

1.二分查找的前提 只有单调的序列才能进行二分查找&#xff1b; 一般为单调不减&#xff0c;单调不增需要像 sort() 一样修改比较函数&#xff1b; 2.binary_search( ) 函数 binary_search( ) 是算法库&#xff08;algorithm&#xff09;函数里面的&#xff0c;用于在一个已经…

【web网页制作】html+css旅游家乡山西主题网页制作(3页面)【附源码】

山西旅游网页目录 涉及知识写在前面一、网页主题二、网页效果Page1、景点介绍Page2、酒店精选|出行攻略Page3、景色欣赏 三、网页架构与技术3.1 脑海构思3.2 整体布局3.3 技术说明书 四、网页源码4.1 主页模块源码4.2 源码获取方式 作者寄语 涉及知识 山西旅游主题网页制作&am…

【大语言模型】轻松本地部署Stable Diffusion

硬件要求&#xff1a; 配备至少8GB VRAM的GPU&#xff0c;如果你的电脑只有CPU&#xff0c;请看到最后。根据部署规模&#xff0c;需要足够的CPU和RAM。 软件要求&#xff1a; Python 3.7或更高版本。支持NVIDIA GPU的PyTorch。Hugging Face的Diffusers库。Hugging Face的Tr…

Training - PyTorch Lightning 分布式训练的 global_step 参数 (accumulate_grad_batches)

欢迎关注我的CSDN&#xff1a;https://spike.blog.csdn.net/ 本文地址&#xff1a;https://blog.csdn.net/caroline_wendy/article/details/137640653 在 PyTorch Lightning 中&#xff0c;pl.Trainer 的 accumulate_grad_batches 参数允许在执行反向传播和优化器步骤之前&…

CSS常用十大选择器(理论+代码实操)

HTML代码实例 注意&#xff1a;拷贝后本地运行注意head标签中的link标签的href属性是否正确 我的目录结构&#xff1a; <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8"><title>Title</title><lin…

人机区别之一在于机器智能还不能提出问题

人机区别在于机器智能目前还不能提出问题。虽然机器智能已经可以通过程序和算法执行各种任务&#xff0c;但它们仍然无法像人类一样主动思考和提出问题。机器智能只能根据预设的指令或对特定情况的响应来进行操作&#xff0c;而无法产生自己的独立思考和主动提问。这是因为机器…

广东省道路货物运输资格证照片回执可手机线上办理

广东省道路运输资格证是从事道路运输业务、危险品道路运输人员的必要证件&#xff0c;而在办理该证件的过程中&#xff0c;驾驶员照片回执是一项必不可少的材料。随着科技的发展和移动互联网的普及&#xff0c;现在办理驾驶员照片回执已经不再需要亲自前往照相馆&#xff0c;而…

结合 react-webcam、three.js 与 electron 实现桌面人脸动捕应用

系列文章目录 React 使用 three.js 加载 gltf 3D模型 | three.js 入门React three.js 3D模型骨骼绑定React three.js 3D模型面部表情控制React three.js 实现人脸动捕与3D模型表情同步结合 react-webcam、three.js 与 electron 实现桌面人脸动捕应用 示例项目(github)&…

MES生产管理系统:私有云、公有云与本地化部署的比较分析

随着信息技术的迅猛发展&#xff0c;云计算作为一种新兴的技术服务模式&#xff0c;已经深入渗透到企业的日常运营中。在众多部署方式中&#xff0c;私有云、公有云和本地化部署是三种最为常见的选择。它们各自具有独特的特点和适用场景&#xff0c;并在不同程度上影响着企业的…

neo4j使用详解(结尾、neo4j的java driver使用模板及工具类——<可用于生产>)

Neo4j系列导航: neo4j安装及简单实践 cypher语法基础 cypher插入语法 cypher插入语法 cypher查询语法 cypher通用语法 cypher函数语法 neo4j索引及调优 neo4j java Driver等更多 1. 简介 本文主要是java使用neo4j driver操作neo4j的模板项目及非常有用的工具类,主要包括: 图…

Tesserocr 的安装步骤

Tesserocr 的安装 OCR&#xff0c;即 Optical Character Recognition&#xff0c;光学字符识别。是指通过扫描字符&#xff0c;然后通过其形状将其翻译成电子文本的过程。那么对于图形验证码来说&#xff0c;它都是一些不规则的字符&#xff0c;但是这些字符确实是由字符稍加扭…

精确运算为什么不能用double?

主要原因:就是因为double会导致数据不准确&#xff0c;不准确的原因如下所示 高于大小的比特会被自动删除&#xff0c;但是在删除的过程中还需要遵循 IEEE-754 规范&#xff0c;这就是我们理解的删除不应该会让数变小吗&#xff1f;为什么计算机的计算会变大? 如果最后一位是1…

Ubuntu20.04安装FloodLight最新版本

Ubuntu20.04安装FloodLight最新版本 网上的很多教程尝试了一下都不对&#xff0c;并且很多都是基于Ubuntu14的旧版本系统&#xff0c;其中的Python环境大多是基于2.0的&#xff0c;由于本人所使用的系统是Ubuntu20.04&#xff0c;后再油管澳大利亚某个学校的网络教学视频的帮助…