标签： 大数据

apache hive reflect函数 基因型的UDF函数

在写hsql(hive sql)的时候，我们可以利用hive自身的函数来处理很多的数据，但是有的时候内置函数没法满足我们的需求时就需要用到UDF(User-Defined Function),而有时我们的需求也很简单，在很多的jar包或者java自身的包中已经存在具体的实现逻辑或者方法。那么我们如何来调用这些已经存在且不需要自己写的函数和方法呢？

apache hive

A java class and method often exists to handle the exact function a user would like to use in hive. Rather then having to write a wrapper UDF to call this method, the majority of these methods can be called using reflect udf. Reflect uses java reflection to instantiate and call methods of objects, it can also call static functions. The method must return a primative type or a type that hive knows how to serialize.

Apache hive reflect

大致含义: 通常存在一个 Java 类和方法来处理用户想要在 Hive 中使用的确切功能。不必编写包装 UDF 来调用此方法，大多数方法都可以使用反射 UDF 调用。Reflect 使用 Java 反射来实例化和调用对象的方法；它还可以调用静态函数。该方法必须返回原始类型或 Hive 知道如何序列化的类型。

SELECT reflect("java.lang.String", "valueOf", 1),
       reflect("java.lang.String", "isEmpty"),
       reflect("java.lang.Math", "max", 2, 3),
       reflect("java.lang.Math", "min", 2, 3),
       reflect("java.lang.Math", "round", 2.5),
       reflect("java.lang.Math", "exp", 1.0),
       reflect("java.lang.Math", "floor", 1.9)
FROM src LIMIT 1;

# 输出： 1   true    3   2   3   2.7182818284590455  1.0

再比如我们在解析URL的时候，java的已存在函数是java.net.URLDecoder.decode(),在hive中如何调用？

hive> select reflect('java.net.URLDecoder','decode',cur) from pv where day_id='2016-05-01'；

注意: As of Hive 0.9.0, java_method() is a synonym for reflect(). See Misc. Functions in Hive Operators and UDFs.

参考地址: http://sishuok.com/forum/blogPost/list/6226.html

apache hive 日期函数的语法介绍和具体使用场景

无论是我们写普通SQL还是，大数据的hsql(apache hive sql)，很多的场景下都会涉及到时间、日期相关的字段处理，而这些数据库或者大数据组件都给我们提供其内置的时间、日期处理函数，以供我们更便捷的处理相关字段。

hive 日期函数

1、获取当前UNIX时间戳函数: unix_timestamp

语法:   unix_timestamp() 
返回值:   bigint
说明: 获得当前时区的UNIX时间戳

hive> select unix_timestamp() from hive_table;
1323309615

语法:unix_timestamp(string date) 
返回值:   bigint
说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。

hive>   select unix_timestamp('2011-12-07 13:01:03') from hive_table;
1323234063

语法:   unix_timestamp(string date, string pattern)
返回值:   bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

hive>   select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from hive_table;
1323234063

2、日期函数UNIX时间戳转日期函数: from_unixtime

语法:from_unixtime(bigint unixtime[, string format]) 
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

hive> select from_unixtime(1323308943,'yyyyMMdd') from hive_table;
20111208

3、日期时间转日期函数: to_date

语法:   to_date(string timestamp) 
返回值:   string
说明: 返回日期时间字段中的日期部分。

hive>   select to_date('2011-12-08 10:03:01') from hive_table;
2011-12-08

4、日期转年函数: year

语法:   year(string date) 
返回值: int
说明: 返回日期中的年。

hive>   select year('2021-06-10 10:03:01') from hive_table;
2021
hive>   select year('2020-06-10') from hive_table;
2020

5、日期转月函数: month

语法: month   (string date) 
返回值: int
说明: 返回日期中的月份

hive>   select month('2021-06-10 10:03:01') from hive_table;
6
hive>   select month('2020-10-10') from hive_table;
10

6、日期转天函数: day

语法: day   (string date) 
返回值: int
说明: 返回日期中的天。

hive>   select day('2021-06-10 10:03:01') from hive_table;
10
hive>   select day('2020-10-24') from hive_table;
24

7、日期转小时函数: hour

语法: hour   (string date) 
返回值: int
说明: 返回日期中的小时。

hive>   select hour('2021-06-10 10:03:01') from hive_table;
10

8、日期转分钟函数: minute

语法: minute   (string date) 
返回值: int
说明: 返回日期中的分钟。

hive>   select minute('2021-06-10 10:03:01') from hive_table;
3

9、日期转秒函数: second

语法: second   (string date) 
返回值: int
说明: 返回日期中的秒。

hive>   select second('2021-06-10 10:03:01') from hive_table;
1

10、日期转周函数: weekofyear

语法:   weekofyear (string date) 
返回值: int
说明: 返回日期在当前的周数。

hive>   select weekofyear('2021-12-08 10:03:01') from hive_table;
49

11、日期减少函数: date_sub

语法:   date_sub (string startdate, int days) 
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

hive>   select date_sub('2021-12-08',10) from hive_table;
2021-11-28

12、日期增加函数: date_add

语法:   date_add(string startdate, int days) 
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

hive>   select date_add('2021-12-08',10) from hive_table;
2021-12-18

13、日期比较函数: datediff

语法:   datediff(string enddate, string startdate) 
返回值: int
说明: 返回结束日期减去开始日期的天数。

hive>   select datediff('2021-12-08','2021-12-19') from hive_table;
11

hive 中使用with…as语法建立临时中间表

当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL。前mysql、 oracle、sql server、hive等均支持 with as 用法

hive

1、介绍

with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！
其最大的好处就是适当的提高代码可读性，而且如果with子句在后面要多次使用到，这可以大大的简化SQL；更重要的是：一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标。

with sub_query_name1 as (
	select 
		xx
	from
		xxx
), -- 注意这里用逗号分割
sub_query_name2 as ( --注意这里没有as，上面一个as跟多个sub_query
	select
		xx
	from
		xx
)
select --正式查询语句，与上面的with as直接没有符号分割
	xx
from
	sub_query_name1,sub_query_name2

2、注意事项

  • 整体作为一条sql查询，即with as语句后不能加分号，不然会报错。
  • with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.
  • 如果定义了with子句，但其后没有跟select查询，则会报错！
  • 前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！

3、代码示例

# 直接查询
with tmp_a as (
select f1,f2,f3 from test1
)
select f1,f2,f3 from tmp_a;
# 多表关联
with tmp_a as (
select f1,f2,f3 from test1
),
tmp_b as(
select f1,f4,f5 from test2
)
select a.f1,a.f2,a.f3,b.f4,b.f5 from
tmp_a a
left join
tmp_b b
on a.f1 = b.f1