Hive查询操作

1．查询操作语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

  FROM table_reference

  [WHERE where_condition]

  [GROUP BY col_list]

  [ORDER BY col_list]

  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]

  ]

 [LIMIT number]

 

2．基本查询操作

（1）全表和特定列查询

select * from tablename;      --全表查询

select col1,col2 from tablename; --选择特定列查询

注意事项

# SQL 语言大小写不敏感。

# SQL 可以写在一行或者多行

# 关键字不能被缩写也不能分行

# 各子句一般要分行写。

# 使用缩进提高语句的可读性。

（2）列别名

列别名: 通过对列重命名来提高SQL语句的可读性

select ename AS name, deptno dn from emp; -- 查询名称和部门

（3）算术运算符

#  + - * / 加减乘除四则运算

# &(取余) &(按位与) |(按位取或) ^(按位异或) ~(按位取反)

# 示例:

select sal +10000 from emp; --将所有员工的薪水加10000显示

（4）聚合函数

聚合函数是hive内置函数，聚合函数对一组值执行计算，并返回单个值。

select count(*) cnt from emp;    -- 求总行数(count)

select max(sal) max_sal from emp;  --最大值（max）

select min(sal) min_sal from emp;  --最小值（min）

select sum(sal) sum_sal from emp;  --求和（sum）

select avg(sal) avg_sal from emp;  --求平均值

（5）limit语句

LIMIT子句用于限制返回的行数。

select * from emp limit 5; --只返回5条结果

（6）表别名

好处

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; --合并员工表和部门表

 

3．where语句

1）使用WHERE 子句，将不满足条件的行过滤掉。

2）WHERE 子句紧随 FROM 子句。

（1）使用比较运算符

# 比较运算符

<, <=, =, >=, >, !=(<>)  --常规比较运算符

is null, in not null --空或非空

A [NOT] BETWEEN B AND C  -- 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。

in(值1,值2) --使用 IN运算显示列表中的值

A [NOT] LIKE B  --A与正则表达式B匹配，则返回TRUE；反之返回FALSE。

A RLIKE B, A REGEXP B --B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。

# 实操:

select * from emp where sal =5000; --查询薪水为5000的员工

select * from emp where sal between 500 and 1000; --查询工资在500到1000的员工信息

select * from emp where comm is null; --查询comm为空的所有员工信息

select * from emp where sal IN (1500, 5000);  --查询工资是1500或5000的员工信息

（2）Like和RLike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

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

# 实操:

select * from emp where sal LIKE '2%'; --查找以2开头薪水的员工信息

select * from emp where sal LIKE '_2%'; --查找第二个数值为2的薪水的员工信息

select * from emp where sal RLIKE '[2]'; --查找薪水中含有2的员工信息

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

操作符   含义

AND       逻辑与

OR       逻辑或

NOT       逻辑非

hive (default)> select * from emp where sal>1000 and deptno=30; --查询薪水大于1000，部门是30

hive (default)> select * from emp where sal>1000 or deptno=30; --查询薪水大于1000，或者部门是30

hive (default)> select * from emp where deptno not IN(30, 20); --查询除了20部门和30部门以外的员工信息

 

4．分组

（1）Group By语句

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

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; --计算emp表每个部门的平均工资

（2）Having语句

having与where不同点

（1）where后面不能写分组函数，而having后面可以使用分组函数。

（2）having只用于group by分组统计语句。

 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;  --求平均薪水大于2000的部门

 

5．Join语句

等值Join: Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

内连接： 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

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

左外连接： JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接： JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

满外连接： 将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno

 = d.deptno;

多表连接:

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

SELECT e.ename, d.dname, 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总是按照从左到右的顺序执行的。

优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

 

6．笛卡尔积

笛卡尔集会在下面条件下产生

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

缺点: 非常消耗资源,除非极其特殊情况,否则不要在生产中使用

# 实例

select empno, dname from emp, dept;

 

7．Sort By，Order By，Cluster By，Distrbute By的含义?

order by：会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）。只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

sort by：不是全局排序，其在数据进入reducer前完成排序。

distribute by：按照指定的字段对数据进行划分输出到不同的reduce中。distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前.

cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能。如sort by和 distribute by 的字段相同,那么cluster by与二者等价,但是cluster by不支持降序操作, 即不能使用DESC操作

 

8．分桶查询

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。

对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径；分桶针对的是数据文件。

创建分桶表

首先要设置属性:

set hive.enforce.bucketing=true; # 建表前需要设置强制分桶,分桶才会生效

create table stu_buck(id int, name string)

clustered by(id)

into 4 buckets

row format delimited fields terminated by '\t'; --创建分桶表

分桶规则：

Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。

Hive可以通过对表进行抽样来满足这个需求。

#查询表stu_buck中的数据。

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开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。

-- 例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

-- 注意：x的值必须小于等于y的值

 

9．空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。

它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。

select comm,nvl(comm, -1) from emp; --如果员工的comm为NULL，则用-1代替

 

10．CASE WHEN

简单Case函数 :

   # 格式

　　　　case 列名

　　　　when 条件值1 then 选择项1

　　　　when 条件值2 then 选项2.......

　　　　else 默认值 end

　　　　# 实例　　　　

　　　　select

　　　　case 　job_level

　　　　when '1' then '水平1'

　　　　when '2' then '水平2'

　　　　when '3' then '水平3'

　　　　else '其他' end

　　　　from dbo.employe

 

Case搜索函数:

    #格式说明

　　　　case

　　　　when 列名= 条件值1 then 选择项1

　　　　when 列名=条件值2 then 选项2.......

　　　　else 默认值 end

 

   # 实例

　　　　update employee

　　　　set e_wage =

　　　　case

　　　　when job_level = '1' then e_wage*1.97

　　　　when job_level = '2' then e_wage*1.07

　　　　when job_level = '3' then e_wage*1.06

　　　　else e_wage*1.05 end

 

11．行转列

相关函数说明:

函数名称             函数作用

CONCAT(string A/col, string B/col…) 返回输入字符串连接后的结果，支持任意个输入字符串

CONCAT_WS(separator, str1, str2,…) 它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间

COLLECT_SET(col) 函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

COLLECT_LIST(col) 函数只接受基本数据类型，它的主要作用是将某字段的值进行汇总(不去重)，产生array类型字段。

 

实操:

数据准备:

name constellation blood_type

孙悟空   白羊座    A

大海   射手座    A

宋宋   白羊座    B

猪八戒   白羊座    A

凤姐   射手座  A

select

    t1.base,

    concat_ws('|', collect_set(t1.name)) name

from

    (select

        name,

        concat(constellation, ",", blood_type) base

    from

        person_info) t1

group by

    t1.base; --将星座和血型一样的人归类到一起

 

结果如下:

射手座,A            大海|凤姐

白羊座,A            孙悟空|猪八戒

白羊座,B            宋宋

12．列转行

函数说明:

**EXPLODE(col)：**将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

 

实操

数据准备:

movie          category

《疑犯追踪》 悬疑,动作,科幻,剧情

《Lie to me》 悬疑,警匪,动作,心理,剧情

《战狼2》    战争,动作,灾难

 

select

    movie,

    category_name

from

    movie_info lateral view explode(category) table_tmp as category_name; --将电影分类中的数组数据展开

 

结果如下:

《疑犯追踪》      悬疑

《疑犯追踪》      动作

《疑犯追踪》      科幻

《疑犯追踪》      剧情

《Lie to me》   悬疑

《Lie to me》   警匪

《Lie to me》   动作

《Lie to me》   心理

《Lie to me》   剧情

《战狼2》        战争

《战狼2》        动作

《战狼2》        灾难

 

13．窗口函数（开窗函数)

相关函数说明

函数名称    函数说明

OVER() 指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW 当前行

n PRECEDING 往前n行数据

n FOLLOWING 往后n行数据

UNBOUNDED 起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

LAG(col,n,default_val) 往前第n行数据

LEAD(col,n, default_val) 往后第n行数据

NTILE(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

（1）实际操作

数据准备:

name orderdate cost

jack 2017-01-01 10

tony 2017-01-02 15

jack 2017-02-03 23

tony 2017-01-04 29

jack 2017-01-05 46

jack 2017-04-06 42

tony 2017-01-07 50

jack 2017-01-08 55

mart 2017-04-08 62

mart 2017-04-09 68

neil 2017-05-10 12

mart 2017-04-11 75

neil 2017-06-12 80

mart 2017-04-13 94

select name,count(*) over ()

from business

where substring(orderdate,1,7) = '2017-04'

group by name; --查询在2017年4月份购买过的顾客及总人数

 

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business; --查询顾客的购买明细及月购买总额

 

# 上述的场景, 将每个顾客的cost按照日期进行累加

select name,orderdate,cost,

sum(cost) over() as sample1,--所有行相加

sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加

sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加

sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合

sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合

sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行

sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行

from business;

-- rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

 

select name,orderdate,cost,

lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2

from business; --查看顾客上次的购买时间

 

select * from (

    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted

    from business

) t

where sorted = 1; --查询前20%时间的订单信息

 

（2）RANK函数

函数说明        

函数名称     函数作用

RANK()         排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

 

14．自定义函数

Hive 自带了一些函数，比如：max/min等，但是数量有限，用户可以通过自定义UDF来方便的扩展。

适用场景: 当Hive提供的内置函数无法满足业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

分类:

用户自定义函数类别分为以下三种：

种类 特征

UDF（User-Defined-Function) 一进一出

UDAF（User-Defined Aggregation Function） 聚集函数，多进一出 类似于：count/max/min

UDTF（User-Defined Table-Generating Functions） 一进多出

（1）自定义UDF函数

编程步骤：

（1）继承org.apache.hadoop.hive.ql.exec.UDF

（2）需要实现evaluate函数；evaluate函数支持重载；

（3）在hive的命令行窗口创建函数

     a）添加jar add jar linux_jar_path

     b）创建function   create [temporary] function [dbname.]function_name AS class_name;

（4）在hive的命令行窗口删除函数   

drop [temporary] function [if exists] [dbname.]function_name;

 

创建一个Maven工程Hive

导入依赖

<dependencies>

<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-exec</artifactId>

<version>1.2.1</version>

</dependency>

</dependencies>

 

创建一个类继承UDF,重写evaluate方法

package com.atguigu.hive;

import org.apache.hadoop.hive.ql.exec.UDF;

 

public class Lower extends UDF {

 

public String evaluate (final String s) {

 

if (s == null) {

return null;

}

 

return s.toLowerCase();

}

}

 

打成jar包上传到服务器

/opt/module/jars/udf.jar

 

将jar包添加到hive的classpath

hive (default)> add jar /opt/module/datas/udf.jar;

 

创建临时函数与开发好的java class关联

hive (default)> create temporary function mylower as "com.atguigu.hive.Lower";

 

在hql中使用自定义的函数

hive (default)> select ename, mylower(ename) lowername from emp;

 

（2）自定义UDTF函数

编程步骤：

1)继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF,实现initialize, process, close三个方法。

2)UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息加粗样式（返回个数，类型）。

3)初始化完成后，会调用process方法,真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。

4)最后close()方法调用，对需要清理的方法进行清理。

 

操作实例:

切分”key:value;key:value;”这种字符串，返回结果为key, value两个字段

 import java.util.ArrayList;

 import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

 import org.apache.hadoop.hive.ql.exec.UDFArgumentException;

 import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;

 import org.apache.hadoop.hive.ql.metadata.HiveException;

 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;

 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;

 import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;

 import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

 

 public class ExplodeMap extends GenericUDTF{

 

     @Override

     public void close() throws HiveException {

     }

 

     @Override

     public StructObjectInspector initialize(ObjectInspector[] args)

             throws UDFArgumentException {

         if (args.length != 1) {

             throw new UDFArgumentLengthException("ExplodeMap takes only one argument");

         }

         if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {

             throw new UDFArgumentException("ExplodeMap takes string as a parameter");

         }

 

         ArrayList<String> fieldNames = new ArrayList<String>();

         ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();

         fieldNames.add("col1");

         fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

         fieldNames.add("col2");

         fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

 

         return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);

     }

 

     @Override

     public void process(Object[] args) throws HiveException {

         String input = args[0].toString();

         String[] test = input.split(";");

         for(int i=0; i<test.length; i++) {

             try {

                 String[] result = test[i].split(":");

                 forward(result);

             } catch (Exception e) {

                 continue;

             }

         }

     }

 }

 

 

使用方式

UDTF有两种使用方法，一种直接放到select后面，一种和lateral view一起使用。

 

直接select中使用

select explode_map(properties) as (col1,col2) from src;

 

和lateral view一起使用

select src.id, mytable.col1, mytable.col2 from src lateral view explode_map(properties) mytable as col1, col2;

 

（3）自定义UDAF函数

实现步骤

引入如下两下类

import org.apache.hadoop.hive.ql.exec.UDAF

import org.apache.hadoop.hive.ql.exec.UDAFEvaluator

 

函数类需要继承UDAF类，计算类Evaluator实现UDAFEvaluator接口

Evaluator需要实现UDAFEvaluator的init、iterate、terminatePartial、merge、terminate这几个函数。

 

a）init函数实现接口UDAFEvaluator的init函数。

b）iterate接收传入的参数，并进行内部的迭代。其返回类型为boolean。

c）terminatePartial无参数，其为iterate函数遍历结束后，返回遍历得到的数据，terminatePartial类似于 hadoop的Combiner。

d）merge接收terminatePartial的返回结果，进行数据merge操作，其返回类型为boolean。

e）terminate返回最终的聚集函数结果。

 

应用实例:

计算平均数:

package hive.udaf;

import org.apache.hadoop.hive.ql.exec.UDAF;

import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;

 

public class Avg extends UDAF {

    public static class AvgState {

        private long mCount;

        private double mSum;

 

    }

 

    public static class AvgEvaluator implements UDAFEvaluator {

        AvgState state;

 

        public AvgEvaluator() {

            super();

            state = new AvgState();

            init();

        }

 

        /**

         * init函数类似于构造函数，用于UDAF的初始化

         */

        public void init() {

            state.mSum = 0;

            state.mCount = 0;

        }

 

        /**

         * iterate接收传入的参数，并进行内部的轮转。其返回类型为boolean * * @param o * @return

         */

 

        public boolean iterate(Double o) {

            if (o != null) {

                state.mSum += o;

                state.mCount++;

            }

            return true;

        }

 

        /**

         * terminatePartial无参数，其为iterate函数遍历结束后，返回轮转数据， * terminatePartial类似于hadoop的Combiner * * @return

         */

 

        public AvgState terminatePartial() {

            // combiner

            return state.mCount == 0 ? null : state;

        }

 

        /**

         * merge接收terminatePartial的返回结果，进行数据merge操作，其返回类型为boolean * * @param o * @return

         */

 

        public boolean merge(AvgState avgState) {

            if (avgState != null) {

                state.mCount += avgState.mCount;

                state.mSum += avgState.mSum;

            }

            return true;

        }

 

        /**

         * terminate返回最终的聚集函数结果 * * @return

         */

        public Double terminate() {

            return state.mCount == 0 ? null : Double.valueOf(state.mSum / state.mCount);

        }

    }

}

 

使用UDAF

将java文件编译成udaf_avg.jar

进入hive客户端添加jar包

hive>add jar /home/hadoop/udaf_avg.jar

 

3)创建临时函数

hive>create temporary function udaf_avg 'hive.udaf.Avg'

 

4)查询语句

hive>select udaf_avg(people.age) from people

 

5)销毁临时函数

hive>drop temporary function udaf_avg