一、hive函数

1.内置运算符与内置函数

　　　　函数分类：

　　　　

pyspark查询hive建表信息_hive

查看函数信息：

DESC FUNCTION concat;

　　　常用的分析函数之rank() row_number()，参考：

　　　常用20个内置函数：

　　　　　　

常用函数：https://www.iteblog.com/archives/2258.html

完整参考官方手册：

窗口函数推荐教程：http://www.aboutyun.com/thread-22652-1-1.html

结合相关实例：

开窗函数OVER用法介绍：

　　　　　　　　　　　　　　　　

窗口函数理解与实践：

窗口函数与分析函数用法：


PARTITON BY 与 ORDER BY语句非常重要：

　　　　　　窗口函数和聚合函数不同的地方在于聚合函数每个分组只产生一条记录，而窗口函数则是每条记录都会生成一条记录

SQL 窗口查询引入了三个新的概念：窗口分区、窗口帧、以及窗口函数。

PARTITION 语句会按照一个或多个指定字段，将查询结果集拆分到不同的 窗口分区 中，并可按照一定规则排序。
如果没有 PARTITION BY，则整个结果集将作为单个窗口分区；
如果没有 ORDER BY，我们则无法定义窗口帧，进而整个分区将作为单个窗口帧进行处理。

　行列转换：

　　　　lateral view的用法参考maxcomputer常用SQL小结

2.自定义函数

分类

　　　　　　UDF  作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）

　　　　　　UDAF（用户定义聚集函数）：接收多个输入数据行，并产生一个输出数据行。（count，max）

自定义UDF：

1.新建工程

　　　　　　这里选择IDEA中建立普通的maven工程，如果不使用maven，则导入hive安装包中Lib下除掉php、perl等的jar

　　　　

pyspark查询hive建表信息_窗口函数_02

 

2.引入依赖

<dependency>
　　<groupId>org.apache.hive</groupId>
　　<artifactId>hive-exec</artifactId>
　　<version>1.2.1</version>
</dependency>

// 推荐保持和hadoop版本一致

3.定义继承于UDF的类，编写evaluate()方法（必须是Public）：

public class ToLowerCaseUDF extends UDF {
    
    public String evaluate(String src) {
        if (src == null) {
            return "";
        }
        return src.toLowerCase();
    }
}

若需要添加函数说明，使得可以通过DESC查看，可以添加以下注解（_FUNC_会替换为函数名）

@Description(name = "deprecation",
        value = "_FUNC_(date, price) - from the input date string(yyyyMMdd), " + 
                "returns the deprecation price by computing price and "
                + "the depreciation rate of the second-hand car.",
        extended = "Example:\n" +
                    " > SELECT _FUNC_(date_string, price) FROM src;")
public class TestUDF extends UDF {

4.打成jar包

使用maven的package打包，如果不使用IDEA的打包，可以切换到项目根目录，手动命令打包：

mvn clean package

5.上传jar包

　　　　　　这里就使用rz上传了

6.使用UDF

　　　　　　　临时：

　　　　　　　　　　不过这个临时函数, 其生命周期和hive的这个交互session保持一致, 一旦退出, 这个临时函数就消失了.

0: jdbc:hive2://localhost:10000> add JAR /home/hadoop/hiveUDF.jar;

// 在hive中上传jar到hive的classpath

create temporary function toprovince as 'com.jiangbei.ToLowerCaseUDF';

//定义一个函数与UDF对应（as后接类的全路径名），这里手误，函数名应该命令为tolowercase

0: jdbc:hive2://localhost:10000> SELECT toprovince("HELLO");
+--------+--+
|  _c0   |
+--------+--+
| hello  |
+--------+--+
1 row selected (0.613 seconds)
0: jdbc:hive2://localhost:10000>
hive> DROP TEMPORARY FUNCTION IF EXISTS deprecation;

//删除函数

　　　　　　永久：

1. 把自定义函数的jar上传到hdfs中.
　　　　　　　　　　　　hdfs dfs -put lower.jar 'hdfs:///path/to/hive_func';
　　　　　　　　2. 创建永久函数
　　　　　　　　　　　　hive> create function xxoo_lower as 'test.ql.LowerUDF' using jar 'hdfs:///path/to/hive_func/lower.jar'
　　　　　　　　3. 验证
　　　　　　　　　　　　hive> select xxoo_lower("Hello World");
　　　　　　　　　　　　hive> show functions;

　　　　　　　　4.删除

　　　　　　　　　　　　hive> drop function xxoo_lower;

　　//补充：处理JSON的内置函数：jason:

 

hive> select  
get_json_object(‘{“store”:{“fruit”:\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],  “bicycle”:{“price”:19.95,”color”:”red”}}, “email”:”amy@only_for_json_udf_test.net”,   “owner”:”amy” } ‘,’$.owner’) 
from dual;

 

3.Transform　　　

　Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能

　　　　适合实现Hive中没有的功能又不想写UDF的情况

　　　　实例：

CREATE TABLE u_data_new (
  movieid INT,
  rating INT,
  weekday INT,
  userid INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

add FILE weekday_mapper.py;

INSERT OVERWRITE TABLE u_data_new
SELECT
  TRANSFORM (movieid, rating, unixtime,userid)
  USING 'python weekday_mapper.py'
  AS (movieid, rating, weekday,userid)
FROM u_data;
#!/bin/python
import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  movieid, rating, unixtime,userid = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print '\t'.join([movieid, rating, str(weekday),userid])