hive sql 执行顺序（如何用sql来写动态sql，本文主要是hiveql）

本文目录

• 如何用sql来写动态sql，本文主要是hiveql
• 如何在Java中执行Hive命令或HiveQL
• HiveSQL核心技能之窗口计算
• 一招教你使用Hive处理文本数据
• HiveQL collect_list保持顺序小记
• 记录几个Hive常用的排序函数

如何用sql来写动态sql，本文主要是hiveql

动态SQL语句在编译时，并不知道SQL语句的内容，SQL语句的内容“不确定”，只有在运行时，才建立、解析并执行SQL语句。利用动态SQL，在存储过程中，可以动态创建表、视图、触发器等。
动态SQL主要用在以下两种场景：
编译时，无法确定SQL语句的内容
静态SQL不支持的SQL语句，就比如上面代码中的create
我们可以看到，静态SQL在编译时就已经提前检查了SQL正确性，以及涉及的数据库对象和对应的权限关系，而动态SQL则需要在运行的时候才能判断，所以，静态SQL的效率高于动态SQL。说了这么多概念的东西，我们现在就来实际看看如何编写动态SQL，以及如何运行动态SQL。

如何在Java中执行Hive命令或HiveQL

Java在1.5过后提供了ProcessBuilder根据运行时环境启动一扒明个Process调用执行运行时环境下的命令或应用程序（1.5以前使用Runtime），关于ProcessBuilder请参考Java相关文档。调用代码如下：
String sql="show tables; select * from test_tb limit 10";
List《String》 command = new ArrayList《String》();
command.add("hive");
command.add("-e");
command.add(sql);
List《String》 results = new ArrayList《世迅String》();
ProcessBuilder hiveProcessBuilder = new ProcessBuilder(command);
hiveProcess = hiveProcessBuilder.start();
BufferedReader br = new BufferedReader(new InputStreamReader(
hiveProcess.getInputStream()));
String data = null;
while ((data = br.readLine()) != null) {
results.add(data);
}
其中command可以是其它Hive命令，搜此此不一定是HiveQL。

HiveSQL核心技能之窗口计算

目标：
1、掌握 sum()、avg()等用于累计计算的聚合函数，学会对行数的限制（移动计算）；
2、掌握 row_number(),rank()、dense_rank()用于排序的函数；
3、掌握 ntile()用于分组查询的函数；
4、掌握 lag()、lead()偏移分析函数

窗口函数（window function）：
与聚合函数类似，但是窗口函数是每一行数据都生成一个结果，聚合函数可以将多行数据按照规定聚合为一行，一般来说聚合后的行数要少于聚合前的行数，但是有时我们想要既显示聚合前的数据，又要显示聚合后的数据，这时便引入了窗口函数， 窗口函数是在 select 时执行的，位于 order by 之前 。

在日常工作中，经常遇到 计算截止某月或某天的累计数值 ，在Excel可以通过函数来实现，
在HiveSQL里，可以利用窗口函数实现。

1）2018年每月的支付总额和当年累计支付总额

2）对2017年和2018年公司的支付总额按月度累计进行分析，按年度进行汇总

说明：1、over中的 partition by 起到分组的作用；
2、order by 按照什么顺序进行累加，升序ASC、降序DESC，默认升序
3、正确的分组是非常重要的，partition by 后面的字段是需要累计计算的区域，需要仔细理解

（计算三日留存、七日留存、三十日留存等方式可以使用这个函数。）

3）对2018年每个月的近三个月进行移动的求平均支付金额

用法：这三个函数的作用都是返回相应规则的排序序号，由于排序函数不是二次聚合计算，因此不一定要使用子查询

4）2019年1月，用户购买商品品类数量的排名

5）选出2019年支付金额排名在第10、20、30名的用户

6）将2019年1月的支付用户，按照支付金额分成5组

7）选出2019年退款金额排名前10%的用户

说明：Lag和Lead分析函数可以在同一次查询中取出同一字段的 前N行数据(Lag)和后N行的数据(Lead) 作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。
当然，这种操作可以用表的 自连接实现 ，但是Lag和Lead与 left join、 right join等自连接相比，效率更高，SQL语句更简洁。

8）支付时间间隔超过100天的用户数(这一次购买距离下一次购买的时间？，注意datediff函数是日期大的在前面)

9）每个城市，不同性别，2018年支付金额最高的TOP3用户

步骤总结：
1、首先筛选出每个用户和每个用户总的消费金额；
2、对两个表进行连接提取需要的字段；
3、对连接后的表进行二次聚合计算，计算出不同城市、性别的金额排名；
4、对二次聚合计算的表进行条件筛选提取

10）每个手机品牌退款金额前25%的用户

步骤总结：
1、首先筛选出每个用户和每个用户的总退款金额；
2、对两个表进行连接提取需要的字段；
3、对连接后的表进行按手机品牌内分组；
4、对分组后的表进行条件筛选提取

一招教你使用Hive处理文本数据

我在学习了几个月的大数据之后，终于接到了老板派来的活啦！有核心技术在手，感觉走路都轻快了许多呢。这个需求呢实际上非常简单且明确。

现在老板需要我对招聘市场不同岗位的核心技能点做一个调研，现在我们手上大概有一些数据，数据是一些招聘相关的数据，在数据中有一个字段为岗位描述，顾名思义，找过工作的同学都能知道岗位描述是啥意思，尤其是像你们没学大数据的同学，可能已经翻看了无数工作机会还没有找到工作吧吼吼，我学习完大数据之后可是立马找到工作了呢。

岗位描述实际就是一段话，表示这个岗位需要应聘者具备某些能力或者技能来足以胜任这份工作。有了这个数据，我初步的调研方案是启睁山这样的：

针对性的分析这个字段的数据，把其中所有的关键词给取出来，然后按照每一条数据对应的岗位，根据岗位进行分组计数，那样的话我就可以得出每一个岗位对应的每一个关键词出现的次数了，然后当然出现次数最多的那几个关键词就是该岗位的核心技能关键词啦。计划非常完美。
现在的我满脑子都是将任务完美完成，然后得到老板赏识，升职加薪，迎娶白富美的桥段。可是万事俱备，只欠东风。有一个非常重要的问题就是如何将一堆文本（岗位描述）转换成一个个的词？也就是我们常说的分词，今天我们就来介绍一下如何完美地完成这个任务。干货时间到。

首先这次任务咱们使用Hive来进行数据处理和分析，通过查询Hive文档发现Hive内置函数无法实现分词，但是Hive提供UDF支持用户自定义函数来实现更多的功能。开发UDF的过程大致分为（使用Java语言）：

创建一个用于编写UDF的Maven项目导入相关大数据依赖，其中最重要的是hive-exec和hadoop-common创建一个类并且继承自UDF类重写类中的evaluate()方悄中法，并且在方法中定义逻辑对Maven项目进行打包，将jar包上传至HDFS中在Hive中添加方法关联该jar包中的UDF类，之后就可以使用该方法实现想要的功能了需要注意的是，继承自UDF类，evaluate()方法的输入是一条数据，输出也是一条数据，可以想象一下就是在Hive中一条数据进来，经过转换后返回一条转换后的数据，这与我们常用的lower()/upper()函数类似。在Hive中，还有其他形式的自定义函数类，比如UDAF、UDTF，其中UDAF是多行输入返回一行，例如聚合函数sum()/早困count()，UDTF是一行输入返回多行，例如爆炸函数explode()。具体详情同学们自行搜索学习吧。

下面开始编写分词的UDF，首先当然是导入相关依赖了，实现分词的库有很多，我选择的是java常用的IK分词器，依赖的名称是ikanalyzer。

之后我们可以定义相关的黑名单词和偏好词，因为我们要最终得到我们想要的关键词，那么们最好把一些没什么用的词去掉，例如“我的”，“岗位”，“很好”等等这一系列词存放成黑名单，因为最后我们不想把得出来的关键词是这种没有意义的词。当然偏好词也有必要，因为分词工具都是通过一定的语料和算法来进行分词的，它有时候也会将一些词分错，例如“机器学习”这个词，可能分词工具会将它分为“机器”“学习”这两个词，但是很明显对于岗位来说，这明显就是一个技能词，所以我们将这种专有名词设置成偏好词让分词器下次遇到这些词的时候不要分错。

将停止词放入stopword.dic中，将偏好词放入extword.dic中，每一个词占一行即可，然后在IKAnalyzer.cfg.xml中配置这两个文件的路径，之后IK分词器就会自动的加载我们自定义的停止词和偏好词了。

接下来编写主类UDF。

UDF的大致意思就是对传入的字符串做分词，分词后在通过特殊符号“\001”来对每一个词进行拼接，最终返回一个拼接好的字符串。

根据开发UDF的步骤，将UDF打成jar包并上传到HDFS，并在Hive中创建方法关联该jar包。

总之使用HiveSQL一通十三招将所有的任务完成，SQL在这就不给大伙儿解释了，最终我们由原始数据，得到了最终我们想要的数据。
从我们对数据的处理和分析结果来看，算法岗位（job_tag）对关键字（sub）“算法”、“c++”、“机器学习”这些技术都有相当大的需求，因为在算法岗位描述中，“算法”这个词出现6366次，“c++”出现3706次，“机器学习”出现3385次，都是出现次数最高的几个关键词。

今天我的分享就到这啦，你学会了吗？

HiveQL collect_list保持顺序小记

有以下Hive表的定义：

这张表是我们业务里话题推荐分值表的简化版本。category_id代表分类ID，topic_id是话题ID，score是评分值。rank代表每个分和誉歼类下话题分值的排名，用开窗函数计虚戚算出来的：
row_number() over(partition by t.category_id order by t.score desc)

在对外提供推荐结果时，我们会将每个唤冲小组下排名前1000的话题ID取出，拼成一个逗号分隔的字符串，处理之后送入HBase供调用方查询。拼合的SQL语句如下：

看起来没什么问题？但实际上是错误的。输出结果中总会有一些category_id对应的列表顺序异常，比如本来排名正数与排名倒数的两批ID调换了位置，即rank变成了 n-3, n-2, n-1, n, 5, 6, 7, ..., n-4, 1, 2, 3, 4 。

产生这个问题的根本原因自然在MapReduce，如果启动了多于一个mapper/reducer来处理数据，select出来的数据顺序就几乎肯定与原始顺序不同了。考虑把mapper数固定成1比较麻烦（见 我之前写的那篇Hive调优文章 ），也不现实，所以要迂回地解决问题：把rank加进来再进行一次排序，拼接完之后把rank去掉。如下：

这里将rank放在了topic_id之前，用冒号分隔，然后用sort_array函数对collect_list之后的结果进行排序（只支持升序）。特别注意，rank必须要在高位补足够的0对齐，因为排序的是字符串而不是数字，如果不补0的话，按字典序排序就会变成 1, 10, 11, 12, 13, 2, 3, 4... ，又不对了。
将排序的结果拼起来之后，用regexp_replace函数替换掉冒号及其前面的数字，大功告成。

顺便看一下Hive源码中collect_list和collect_set函数对应的逻辑吧。

记录几个Hive常用的排序函数

如果是规整的json字符串，可以先使用Hive函数get_json_object取出dySub 后面的数字，再做sum。另外也可以使用Hive函数regexp_extract，使用正则表达式抽取出dySub 后面的数字。具体可以搜索一下"lxw的大数据田地 hive函数大全"，里面乱散笑有每种函数的详细用法。 hivesql sql — 获取指定hive表或指定文件中所有hive表的DDL，如果有按天的分区则默认执行最近7天的分掘冲区DDL。同时，table支持符合sql语法的正则表达式，如果有多个表匹配，则提示用户选择(使用file则哗含自动关闭该交互功能)。