hive 查询语法 shell参数自定义函数教程

Hive 查询语法

4.1. SELECT

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">SELECT [ALL | DISTINCT] select_expr, select_expr, ...<br></br>FROM table_reference<br></br>[WHERE where_condition]<br></br>[GROUP BY col_list [HAVING condition]]<br></br>[CLUSTER BY col_list<br></br>| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]<br></br>]<br></br>[LIMIT number]

order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
cluster by(字段) 除了具有distribute by的功能外，还会对该字段进行排序.

因此，如果distribute 和sort字段是同一个时，此时，cluster by = distribute by + sort by

4.2. 查询语法

全表查询

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score;

选择特定列

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,c_id from score;

列别名

1）重命名一个列。 2）便于计算。 3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id as myid ,c_id from score;

4.3. 常用函数

求总行数（count）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select count(1) from score;

求分数的最大值（max）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select max(s_score) from score;

求分数的最小值（min）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select min(s_score) from score;

求分数的总和（sum）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select sum(s_score) from score;

求分数的平均值（avg）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select avg(s_score) from score;

4.4. LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score limit 3;

4.5. WHERE语句

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

查询出分数大于60的数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score > 60;

比较运算符

操作符支持的数据类型描述A=B基本数据类型如果A等于B则返回TRUE，反之返回FALSEA<=>B基本数据类型如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULLA<>B, A!=B基本数据类型A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSEA<B基本数据类型A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSEA<=B基本数据类型A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSEA>B基本数据类型A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSEA>=B基本数据类型A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSEA [NOT] BETWEEN B AND C基本数据类型如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。A IS NULL所有数据类型如果A等于NULL，则返回TRUE，反之返回FALSEA IS NOT NULL所有数据类型如果A不等于NULL，则返回TRUE，反之返回FALSEIN(数值1, 数值2)所有数据类型使用 IN运算显示列表中的值A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。A RLIKE B, A REGEXP BSTRING类型 B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。- 查询分数等于80的所有的数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score = 80;

查询分数在80到100的所有数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score between 80 and 100;

查询成绩为空的所有数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score is null;

查询成绩是80和90的数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score in(80,90);

4.6. LIKE 和 RLIKE

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

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">% 代表零个或多个字符(任意个字符)。<br></br>_ 代表一个字符。

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

查找以8开头的所有成绩

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">    select * from score where s_score like '8%';

查找第二个数值为9的所有成绩数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score like '_9%';

查找s\_id中含1的数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_id rlike '[1]';  #  like '%1%'

4.7. 逻辑运算符

操作符含义AND逻辑并OR逻辑或NOT逻辑否- 查询成绩大于80，并且s\_id是01的数据

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score >80 and s_id = '01';

查询成绩大于80，或者s\_id 是01的数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_score > 80 or s_id = '01';

查询s\_id 不是 01和02的学生

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score where s_id not in ('01','02');

4.8. 分组

GROUP BY 语句

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

计算每个学生的平均分数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,avg(s_score) from score group by s_id;

计算每个学生最高成绩

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,max(s_score) from score group by s_id;

HAVING 语句

having与where不同点

where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
where后面不能写分组函数，而having后面可以使用分组函数。
having只用于group by分组统计语句。

案例实操：

求每个学生的平均分数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,avg(s_score) from score group by s_id;

求每个学生平均分数大于85的人

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

4.9. JOIN 语句

4.9.1. 等值 JOIN

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

案例操作: 查询分数对应的姓名

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s.s_id,s.s_score,stu.s_name,stu.s_birth  from score s  join student stu on s.s_id = stu.s_id;

4.9.2. 表的别名

好处

使用别名可以简化查询。
使用表名前缀可以提高执行效率。

案例实操

合并老师与课程表

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from techer t join course c on t.t_id = c.t_id;

4.9.3. 内连接

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

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from techer t inner join course c on t.t_id = c.t_id;

4.9.4. 左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。查询老师对应的课程

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from techer t left join course c on t.t_id = c.t_id;

4.9.5. 右外连接

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

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from teacher t right join course c on t.t_id = c.t_id;

4.9.6. 多表连接

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

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from teacher t<br></br>left join course c<br></br>on t.t_id = c.t_id<br></br>left join score s<br></br>on s.c_id = c.c_id<br></br>left join student stu<br></br>on s.s_id = stu.s_id;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表techer和表course进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。

4.10. 排序

4.10.1. 全局排序

Order By：全局排序，一个reduce

使用 ORDER BY 子句排序 ASC（ascend）: 升序（默认） DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾。
案例实操

查询学生的成绩，并按照分数降序排列

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

查询学生的成绩，并按照分数升序排列

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score asc;

4.10.2. 按照别名排序

按照分数的平均值排序

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,avg(s_score) avg from score group by s_id order by avg;

4.10.3. 多个列排序

按照学生id和平均成绩进行排序

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

4.10.4. 每个MapReduce内部排序（Sort By）局部排序

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。

设置reduce个数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">set mapreduce.job.reduces=3;

查看设置reduce个数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">set mapreduce.job.reduces;

查询成绩按照成绩降序排列

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score sort by s_score;

将查询结果导入到文件中（按照成绩降序排列）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

4.10.5. 分区排序（DISTRIBUTE BY）

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操：先按照学生id进行分区，再按照学生成绩进行排序。

设置reduce的个数，将我们对应的s\_id划分到对应的reduce当中去

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">set mapreduce.job.reduces=7;

通过distribute by 进行数据的分区

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">insert overwrite local directory '/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

4.10.6. CLUSTER BY

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

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

以下两种写法等价

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select * from score cluster by s_id;<br></br>select * from score distribute by s_id sort by s_id;

5.Hive Shell参数

5.1 Hive命令行

语法结构

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">bin/hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S]

说明：

1、 -i 从文件初始化HQL。

2、 -e从命令行执行指定的HQL

3、 -f 执行HQL脚本

4、 -v 输出执行的HQL语句到控制台

5、 -p connect to Hive Server on port number

6、 -hiveconf x=y Use this to set hive/hadoop configuration variables. 设置hive运行时候的参数配置

5.2 Hive参数配置方式

开发Hive应用时，不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率，或帮助定位问题。

对于一般参数，有以下三种设定方式：

配置文件
命令行参数
参数声明

配置文件：Hive的配置文件包括

用户自定义配置文件：$HIVE\_CONF\_DIR/hive-site.xml
默认配置文件： $HIVE\_CONF\_DIR/hive-default.xml

用户自定义配置会覆盖默认配置。

另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。

配置文件的设定对本机启动的所有Hive进程都有效。

命令行参数：启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：

<pre class="md-fences mock-cm md-end-block">bin/hive -hiveconf hive.root.logger=INFO,console

这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

参数声明：可以在HQL中使用SET关键字设定参数，例如：

<pre class="md-fences mock-cm md-end-block">set mapred.reduce.tasks=100;

这一设定的作用域也是session级的。

上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。

参数声明 > 命令行参数 > 配置文件参数（hive）

Hive 函数

6.1. 内置函数

内容较多，见《Hive官方文档》

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

查看系统自带的函数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">hive> show functions;

显示自带的函数的用法

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">hive> desc function upper;

详细显示自带的函数的用法

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">hive> desc function extended upper;

4:常用内置函数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">#字符串连接函数： concat <br></br>  select concat('abc','def’,'gh');<br></br>#带分隔符字符串连接函数： concat_ws <br></br>  select concat_ws(',','abc','def','gh');<br></br>#cast类型转换<br></br>  select cast(1.5 as int);<br></br>#get_json_object(json 解析函数，用来处理json，必须是json格式)<br></br>   select get_json_object('{"name":"jack","age":"20"}','$.name');<br></br>#URL解析函数<br></br>   select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');<br></br>#explode：把map集合中每个键值对或数组中的每个元素都单独生成一行的形式<br></br>

6.2. 自定义函数

\####6.2.1 概述:

Hive 自带了一些函数，比如：max/min等，当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数(UDF).
根据用户自定义函数类别分为以下三种：

UDF（User-Defined-Function）

  
  

- 一进一出

UDAF（User-Defined Aggregation Function）

  
  

- 聚集函数，多进一出
- 类似于：`count`/`max`/`min`

UDTF（User-Defined Table-Generating Functions）

  
  

- 一进多出
- 如 `lateral` `view` `explore()`

编程步骤：

继承org.apache.hadoop.hive.ql.UDF
需要实现evaluate函数；evaluate函数支持重载；

注意事项

UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
UDF中常用Text/LongWritable等类型，不推荐使用java类型；

\####6.2.2 UDF 开发实例

Step 1 创建 Maven 工程

<pre class="md-fences md-end-block ty-contain-cm modeLoaded"><dependencies><br></br>    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec --><br></br>    <dependency><br></br>        <groupId>org.apache.hive</groupId><br></br>        <artifactId>hive-exec</artifactId><br></br>        <version>2.7.5</version><br></br>    </dependency><br></br>    <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common --><br></br>    <dependency><br></br>        <groupId>org.apache.hadoop</groupId><br></br>        <artifactId>hadoop-common</artifactId><br></br>        <version>2.7.5</version><br></br>    </dependency><br></br></dependencies><br></br><br></br>    <build><br></br>        <plugins><br></br>            <plugin><br></br>                <groupId>org.apache.maven.plugins</groupId><br></br>                <artifactId>maven-compiler-plugin</artifactId><br></br>                <version>3.0</version><br></br>                <configuration><br></br>                    <source>1.8</source><br></br>                    <target>1.8</target><br></br>                    <encoding>UTF-8</encoding><br></br>                </configuration><br></br>            </plugin><br></br>        </plugins><br></br>    </build>

Step 2 开发 Java 类集成 UDF

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">public class MyUDF  extends UDF{<br></br>    public Text evaluate(final Text str){<br></br>        String tmp_str = str.toString();<br></br>        if(str != null && !tmp_str.equals("")){<br></br>          String str_ret =   tmp_str.substring(0, 1).toUpperCase() + tmp_str.substring(1);<br></br>          return  new Text(str_ret);<br></br>        }<br></br>        return  new Text("");<br></br>    }<br></br>}<br></br>

Step 3 项目打包，并上传到hive的lib目录下

Step 4 添加jar包

重命名我们的jar包名称

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">cd /export/servers/apache-hive-2.7.5-bin/lib<br></br>mv original-day_10_hive_udf-1.0-SNAPSHOT.jar my_upper.jar

hive的客户端添加我们的jar包

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">add jar /export/servers/apache-hive-2.7.5-bin/lib/my_upper.jar;

Step 5 设置函数与我们的自定义函数关联

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">create temporary function my_upper as 'cn.itcast.udf.ItcastUDF';

Step 6 使用自定义函数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded">select my_upper('abc');

hive 查询语法 shell参数 自定义函数教程