Hive的查询

最新推荐文章于 2024-11-07 19:50:24 发布
最新推荐文章于 2024-11-07 19:50:24 发布
阅读量624 收藏 2
点赞数
分类专栏： 大数据 hive Linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/liangzelei/article/details/80154038
版权
本文介绍Hive SQL的基本查询操作，包括全表查询、特定字段查询、算术运算、函数应用、条件筛选、分组聚合等功能，并通过实例演示如何进行高效的数据分析。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

官网

基本查询（Select…From） 

  1. 全表和特定字段查询

    1）全表查询hive (default)> select * from emp;
    2）选择特定列查询hive (default)> select empno, ename from emp;
    3）注意HQL 语言大小写不敏感。 
    HQL 可以写在一行或者多行
    关键字不能被缩写也不能分行
    各子句一般要分行写
    使用缩进提高语句的可读性
  2. 列别名
    紧跟列名，也可以在列名和别名之间加入关键字‘AS’ 
    案例实操

    查询名称和部门hive (default)> select ename AS name, deptno dn from emp;
  3. 算术运算符


    案例实操：hive (default)> select sal +1 from emp;
  4. 常用函数

    1）求总行数（count）hive (default)> select count(*) cnt from emp;
    2）求工资的最大值（max）hive (default)> select max(sal) max_sal from emp;
    3）求工资的最小值（min）hive (default)> select min(sal) min_sal from emp;
    4）求工资的总和（sum）hive (default)> select sum(sal) sum_sal from emp; 
    5）求工资的平均值（avg）hive (default)> select avg(sal) avg_sal from emp;
  5. Limit语句
    典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
    案例实操：hive (default)> select * from emp limit 5;

Where语句

使用WHERE 子句，将不满足条件的行过滤掉。WHERE 子句紧随 FROM 子句。案例实操：

查询出薪水大于1000的所有员工===》hive (default)> select * from emp where sal >1000;

  1. 比较运算符（Between/In/ Is Null）
    下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

    案例实操
    （1）查询出薪水等于5000的所有员工hive (default)> select * from emp where sal =5000;
    （2）查询工资在500到1000的员工信息hive (default)> select * from emp where sal between 500 and 1000;
    （3）查询comm为空的所有员工信息hive (default)> select * from emp where comm is null;
    （4）查询工资是1500和5000的员工信息hive (default)> select * from emp where sal IN (1500, 5000);
  2. Like和RLike

    1）使用LIKE运算选择类似的值 
    2）选择条件可以包含字符或数字:% 代表零个或多个字符(任意个字符)。_ 代表一个字符。
    3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。 
    4）案例实操

    （1）查找以2开头薪水的员工信息hive (default)> select * from emp where sal LIKE '2%';
    （2）查找第二个数值为2的薪水的员工信息hive (default)> select * from emp where sal LIKE '_2%';
    （3）查找薪水中含有2的员工信息hive (default)> select * from emp where sal RLIKE '[2]';

  3. 逻辑运算符（And/Or/Not）


    案例实操：

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

分组

  1. Group By语句
    GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
    案例实操：

    （1）计算emp表每个部门的平均工资hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
    （2）计算emp每个部门中每个岗位的最高薪水hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by
    t.deptno, t.job;
  2. Having语句
    having与where不同点
        1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
        2）where后面不能写分组函数，而having后面可以使用分组函数。
        3）having只用于group by分组统计语句。
    案例实操：

    求每个部门的平均薪水大于2000的部门求每个部门的平均工资
    hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno;
    求每个部门的平均薪水大于2000的部门
    hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno
    having avg_sal > 2000;

Join语句

  1. 等值Join
    Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
    案例操作

    根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
  2. 表的别名
    好处：使用别名可以简化查询。使用表名前缀可以提高执行效率。
    案例实操

    合并员工表和部门表hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
  3. 内连接
    内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
    hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
  4. 左外连接
    JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
    hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
  5. 右外连接
    JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
    hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
  6. 满外链接
    将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
    hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
  7. 多表连接
    注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
    0）数据准备：location.txt
    1）创建位置表
        
    create table if not exists default.location(
    loc int,
    loc_name string
    )
    row format delimited fields terminated by '\t';
    2）导入数据

    hive (default)> load data local inpath '/opt/module/datas/location.txt' into table default.location;
    3）多表连接查询

    hive (default)>SELECT e.ename, d.deptno, l. loc_name
    FROM   emp e 
    JOIN   dept d
    ON     d.deptno = e.deptno 
    JOIN   location l
    ON     d.loc = l.loc;
    大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
    注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
  8. 笛卡尔积 JOIN
    笛卡尔集会在下面条件下产生:
        1）省略连接条件
        2）连接条件无效
        3）所有表中的所有行互相连接
    案例实操
    hive (default)> select empno, deptno from emp, dept;

排序

  1. 全局排序（Order By）
    全局排序，一个MapReduce
    1）使用 ORDER BY 子句排序
        ASC（ascend）: 升序（默认）
        DESC（descend）: 降序
    2）ORDER BY 子句在SELECT语句的结尾
    3）案例实操

    （1）查询员工信息按工资升序排列hive (default)> select * from emp order by sal;
    （2）查询员工信息按工资降序排列hive (default)> select * from emp order by sal desc;
  2. 按照别名排序

    按照员工薪水的2倍排序hive (default)> select ename, sal*2 twosal from emp order by twosal;
  3. 多个列排序

    按照部门和工资升序排序hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
  4. 每个MapReduce内部排序（Sort By）
    每个MapReduce内部进行排序，对全局结果集来说不是排序
    将查询结果导入到文件中（按照部门编号降序排序）
    hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;
  5. 分区排序（Distribute By）
    类似MR中partition，进行分区，结合sort by使用。注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
    先按照部门编号分区，再按照员工编号降序排序。
    hive (default)> insert overwrite local directory '/opt/module/datas/distby-desc' select * from emp distribute by deptno sort by empno desc;
  6. Cluster By
    当distribute by和sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。
    以下两种写法等价
    select * from emp cluster by deptno;
    select * from emp distribute by deptno sort by deptno;
    注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

分桶及抽样查询

  1. 分桶表数据存储
    1）先创建分桶表，通过直接导入数据文件的方式
        
    （0）数据准备
    		student.txt
    （1）创建分桶表create table stu_buck(id int, name string)
    clustered by(id) 
    into 4 buckets
    row format delimited fields terminated by '\t';
    （2）查看表结构hive (default)> desc formatted stu_buck;
    Num Buckets:            4     
    （3）导入数据到分桶表中hive (default)> load data local inpath '/opt/module/datas/student.txt' into table stu_buck;
    （4）查看创建的分桶表中是否分成4个桶发现并没有分成4个桶。是什么原因呢？（往后面看，需要设置参数）
    2）创建分桶表时，数据通过子查询的方式导入

    （1）先建一个普通的stu表create table stu(id int, name string)
    row format delimited fields terminated by '\t';
    （2）向普通的stu表中导入数据load data local inpath '/opt/module/datas/student.txt' into table stu;
    （3）清空stu_buck表中数据    truncate table stu_buck;
    select * from stu_buck;
    （4）导入数据到分桶表，通过子查询的方式insert into table stu_buck
    select id, name from stu cluster by(id);
    （5）需要设置一个属性hive (default)>set hive.enforce.bucketing=true;
    hive (default)> set mapreduce.job.reduces=-1;
    hive (default)>insert into table stu_buck
    select id, name from stu cluster by(id);
    （6）查询分桶的数据hive (default)> select * from stu_buck;
    OK
    stu_buck.id     stu_buck.name
    1001    ss1
    1005    ss5
    1009    ss9
             .
             .
             .
  2. 分桶抽样查询
    对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
    查询上表stu_buck的数据。
    hive (default)> select * from stu_buck TABLESAMPLE(bucket 1 out of 4 on id);
    注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
    y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
    x表示从哪个bucket开始抽取。例如，table总bucket数为4，tablesample(bucket 4 out of 4)，表示总共抽取（4/4=）1个bucket的数据，抽取第4个bucket的数据。
    注意：x的值必须小于等于y的值，否则
    FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck
  3. 数据块抽样
    Hive提供了另外一种按照百分比进行抽样的方式，这种事基于行数的，按照输入路径下的数据块百分比进行的抽样。
    hive (default)> select * from stu tablesample(0.1 percent);
    提示：这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个HDFS数据块。因此，如果表的数据大小小于普通的块大小128M的话，那么将会返回所有行。
Hive查询
qq_50215015的博客
11-14 90
主要针对hive查询做出详细介绍,以及ETC表达式
Hive-查询
liberty12345678的专栏
12-04 328
1、基本查询（Select…From） 1.1、基本查询 全表查询： hive (default)> select * from emp; 选择特定列查询： hive (default)> select empno, ename from emp; 列别名：hive (default)> select ename AS name, deptno dn from emp; ...
hive高级查询(二)
sun_0128的博客
07-11 6229
Hive聚合运算 - GROUP BY GROUP BY用于分组 Hive基本内置聚合函数与GROUP BY一起使用 如果没有指定GROUP BY子句，则默认聚合整个表 除聚合函数外，所选的其他列也必须包含在GROUP BY中 GROUP BY支持使用CASE WHEN或表达式 select category, max(offervalue) from offers group by category; -- group by使用表达式 select if(category > 4000, 'GOOD
Hive 查询
Da.的博客
06-18 780
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select [WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0) SELECT [ALL | DISTINC...
01.hive查询语法--基本查询--条件查询--关联查询.mp4
04-28
01.hive查询语法--基本查询--条件查询--关联查询.mp4
hive查询语句
m0_72898512的博客
10-26 964
方式1:select * from w_1026.orders where (SELECT max(totalmoney) FROM w_1026.orders where useraddress like '%广东省%');orderStatus tinyint COMMENT ' 订单状态 -3: 用户拒收 -2: 未付款的订单 -1 ：用户取消 0: 待发货 1: 配送中 2: 用户确认收货 ',isPay tinyint COMMENT ' 是否支付 0: 未支付 1: 已支付 ',
vim-hive:Hive 查询语言的语法文件
05-29
**vim-hive: Hive 查询语言的语法文件** `vim-hive` 是一个专门为 Vim 编辑器设计的插件，其主要目的是为了提供 Hive 查询语言（HQL）的语法高亮显示功能。这个插件使得在 Vim 中编写 Hive 查询变得更加方便，通过...
如何进行hive查询优化
java_atguigu的博客
02-18 3545
hive查询优化的主要目的是提升效率,下面总结了查询中经常使用的优化点： 1.少用count(distinct ) 建议用group by 代替 distinct 。原因为count(distinct)逻辑只会有一个reducer来处理,即使设定了reduce task个数，set mapred.reduce.tasks=100也一样，所以很容易导致数据倾斜。坊间传闻，在面对大数据量时很多大厂都“明令禁止使用distinct”。 如sql语句：select count(distinct uid)...
龙战于野大数据MR原理启动hive查询表分区.docx
03-13
Hive查询表分区的MR原理启动详解 Hive是一款基于Hadoop的数据仓库工具，主要用于处理结构化和半结构化的数据。MR（MapReduce）是Hadoop中的一种编程模型，用于处理大规模数据。在Hive中，MR原理启动是指使用...
hive sql语句查询
11-25
数据库相关操作 Hive配置单元包含一个名为 default 默认的数据库. create database [if not exists] ；---创建数据库 show databases; --显示所有数据库 drop database if exists [restrict|cascade]; --删除数据库，默认情况下，hive不允许删除含有表的数据库，要先将数据库中的表清空才能drop，否则会报错 --加入cascade关键字，可以强制删除一个数据库,默认是restrict，表示有限制的 eg. hive> drop database if exists users cascade; use ; --切换数据库
HIVE 查询
zhangyuhang88的博客
08-01 197
HIVE 查询
hive的查询
qq_25534101的博客
04-13 551
查询注意事项： 1、order by ==全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。 2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。 3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列，相当于分桶操作 4、Cluster by(
Hive 之查询
hj1993的博客
02-13 1039
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。，导出到本地只会有两个文件有数据，另一个为空，只用到了两个。语句，但是只支持等值连接，不支持非等值连接，即表与表之间。必须有一列值相等作为连接条件，n 张表连接，至少要有。连接 n个表，至少需要n-1个连接条件。
【Hive】Hive 查询
懂得一千零一种,赋予你失败的方法！
03-12 2699
文章目录一、环境准备二、Hive 查询1、普通查询2、别名查询3、限定查询4、多表联合查询5、多表插入6、多目录输出文件 环境准备 Hadoop 完全分布式（一主两从即可） MySQL环境、Hive环境 一、环境准备 将 buyer_log 、buyer_favorite 导入到 /data/hive-data 下： 创建卖家行为日志表，名为 buyer_log，包含ID(id)、用户ID(buyer_id)、时间(dt)、地点(ip)、操作类型(opt_type) 5 个字段，字符类型为..
hive的数据查询
刘小生Star
07-18 1047
-------------hive的数据查询------------- -----------------查询语法---------------     select [all|distinct] select_expr,select_expr,....     from table_reference     [where where_condition]     [group by col_l...
Hive 查询（详细实操版）
最新发布
不励志成为瓦洛兰特糕手的突破手不是好码农
11-07 1646
Hive查询实例操作
hive查询语法
abandon_li的博客
08-24 3072
Hive中的查询语句 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select [WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0) SELE...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值