Hue-hive sql常用函数详解

什么是hue

HUE是一个开源的Apache Hadoop UI系统，早期由Cloudera开发，后来贡献给开源社区。它是基于Python Web框架Django实现的。通过使用Hue我们可以通过浏览器方式操纵Hadoop集群。例如put、get、执行MapReduce Job等等

hue的查询工具

hive

hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低，可以通过类似SQL语句实现快速MapReduce统计，使MapReduce变得更加简单，而不必开发专门的MapReduce应用程序

Impala

Impala是Cloudera公司主导开发的新型查询系统，它提供SQL语义，能查询存储在Hadoop的HDFS和HBase中的PB级大数据。已有的Hive系统虽然也提供了SQL语义，但由于Hive底层执行使用的是MapReduce引擎，仍然是一个批处理过程，难以满足查询的交互性。相比之下，Impala的最大特点也是最大卖点就是它的快速。

Aggregate(聚合函数)

avg (col)

返回该组中元素的平均值或该组中列的不同值的平均值

count([DISTINCT] col)

count(*)返回检索到的行总数，包括包含空值的行。count(expr)返回所提供的表达式为非空的行数。count(DISTINCT expr[， expr])返回所提供的表达式唯一且非空的行数。可以通过hive. optimized .distinct.rewrite优化执行。

sum(col)

返回组中元素的和或组中列的不同值的和

max(col),min(col)

返回组中列的最大值,最小值

collect_set(col)

返回一组消除了重复元素的对象

Analytic(分析函数)

cume_dist()

计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置

select 
    loan_amount,
    user_id,
    cume_dist() over(order by loan_amount) as rn1
	
from  fin_tmp.app_user_y where user_id='30765911715' 

查询结果如下：

row_number () over ([partition_by_clause] order_by_clause)

返回一个递增的整数序列，从1开始。对分区by子句生成的每个组重新启动序列。输出序列为重复的输入值包含不同的值。因此，无论输入值是否重复，序列都不会包含任何重复或空白。

select 
    create_time,
    user_id,
	row_number ( ) over ( PARTITION BY user_id ORDER BY create_time进行排序，执行完这个操作后对每一条数据进行顺序标号，从1开始递增，如果标号出现相同值时给不同的序号。 DESC ) rn
from fin_tmp.app_user_y

对用户表app_user_y中的user_id字段进行分组，然后按照create_time进行排序，执行完这个操作后对每一条数据进行顺序标号，从1开始递增，如果标号出现相同值时给不同的序号。

Collection(集合函数)

array_contains(Arraya,val)

如果数组内包含val,返回True；如果fullname这一列不是数组形式，先用array()函数转化下

sort_array(Array a)

根据数组元素的自然顺序对输入数组按升序排序并返回它

create_union(tag,val1,val2,…)

用标记参数指向的值创建一个联合类型

map(key1, value1, …)

创建具有给定键/值对的映射

named_struct(name1, val1, …)

用给定的字段名和值创建结构,注意：这里的name和val只能是给定的值，用已有的列不行

struct(val1, val2, …)

创建具有给定字段值的结构,这里的val可以是已有的列，也可以是给定的值

Complex Type(复合类型)

array(val1, val2, …)

用给定的元素创建一个数组

Conditional(条件函数)

assert_true(BOOLEAN condition)

如果成立返回NULL，如果不成立抛异常

select assert_true(2>1) from fin_tmp.app_user_y limit 1 select assert_true(user_id is not null) from fin_tmp.app_user_y limit 1 

coalesce(T v1, T v2, …)

返回其参数中的第一个非空表达式，当你要在n个字段中选取某一个非空值

if(BOOLEAN testCondition, T valueTrue, T valueFalseOrNull)

当testCondition为真时返回valueTrue，否则返回valueFalseOrNull

select if (2>1,2,1) from fin_tmp.app_user_y limit 1 >>2 select if (2=1,2,1) from fin_tmp.app_user_y limit 1 >>1 

isnotnull(a),isnull(a)

如果a不为空，则返回true，否则返回false;如果a为空，则返回true，否则返回false

nullif(a, b)

如果a=b返回NULL;否则返回一个(从Hive 2.2.0开始)

nvl(T value, T default_value)

返回默认值，如果值为空，否则返回值(如Hive 0.11)

Date(日期函数)

add_months(DATE|STRING|TIMESTAMP start_date, INT num_months)

返回start_date之后的num_months的日期(从Hive 1.1.0开始)。start_date是一个字符串、日期或时间戳。num_months是一个整数。start_date的时间部分被忽略。如果start_date是一个月的最后一天，或者结果月份的天数少于start_date的day组件的天数，那么结果就是结果月份的最后一天。否则，结果具有与start_date相同的day组件

current_date

注意没有(),返回查询求值开始时的当前日期(从Hive 1.2.0开始)。同一查询中的所有current_date调用都返回相同的值

current_timestamp()

返回查询求值开始时的当前时间戳(从Hive 1.2.0开始)。同一查询中的所有current_timestamp调用都返回相同的值

datediff(STRING enddate, STRING startdate)

返回从起始日期到结束日期的天数:datediff(‘2009-03-01’， ‘2009-02-27’) = 2

date_add(DATE startdate, INT days)

添加日期日期:date_add(‘2008-12-31’， 1) = ‘2009-01-01’。T = pre 2.1.0: STRING, 2.1.0 on: DATE

date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)

将日期/时间戳/字符串转换为日期格式fmt指定的字符串值(从Hive 1.2.0开始)。支持的格式是JavaSimpleDateFormat格式——docs.oracle.com/javase/7/do…(‘2015-04-08’， ‘y’) = ‘2015’

date_sub(DATE startdate, INT days)

减去开始日期的天数:date_sub(‘2008-12-31’， 1) = ‘2008-12-30’。T = pre 2.1.0: STRING, 2.1.0 on: DATE

day(STRING date)

返回日期或时间戳字符串的日期部分:day(‘1970-11-01 00:00:00’) = 1, day(‘1970-11-01’) = 1

dayofmonth(STRING date)

返回日期或时间戳字符串的日期部分:dayofmonth(‘1970-11-01 00:00:00’) = 1, dayofmonth(‘1970-11-01’) =11

from_unixtime(BIGINT unixtime [, STRING format])

将yyyy-MM-dd HH:mm:ss格式的时间字符串转换为Unix时间戳(以秒为单位)，使用默认时区和默认地区，如果转换失败，返回0:unix_timestamp(‘2009-03-20 11:30:01’) = 1237573801

hour(STRING date)

返回时间戳的时间:hour(‘2009-07-30 12:58:59′) = 12, hour(’12:58:59’) = 12。

last_day(STRING date),minute(STRING date),month(STRING date),second(STRING date)

返回日期所属的月份的最后一天(从Hive 1.1.0开始)。日期是一个格式为’yyyy-MM-dd HH:mm:ss’或’yyyy-MM-dd’的字符串。last_day(‘1970-11-01 00:11:00’) = 1970-11-30 返回日期或时间戳字符串的分钟数:minute(‘1970-11-01 00:00:00’) = 00, minute(‘1970-11-01 00:12:00’) = 12 返回日期或时间戳字符串的月份部分:month(‘1970-11-01 00:00:00’) = 11, month(‘1970-11-01’) = 11 返回日期或时间戳字符串的分钟数:second(‘1970-11-01 00:00:01’) = 01

months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)

返回日期date1和date2之间的月数(从Hive 1.2.0开始)。如果date1晚于date2，则结果为正数。如果date1早于date2，则结果为负。如果date1和date2是一个月的相同天数或两个月的最后几天，那么结果总是一个整数。否则，UDF根据31天的月份计算结果的小数部分，并考虑date1和date2时间组件的差异。date1和date2类型可以是“yyyy-MM-dd”或“yyyy-MM-dd HH:mm:ss”格式的日期、时间戳或字符串。结果四舍五入到小数点后八位。例如:months_between(‘1997-02-28 10:30:00’， ‘1996-10-30’) = 3.94959677

next_day(STRING start_date, STRING day_of_week)

返回比start_date晚的第一个日期，并将其命名为day_of_week(从Hive 1.2.0开始)。start_date是一个字符串/日期/时间戳。day_of_week是指2个字母、3个字母或一周中某一天的全称(如Mo、tue、FRIDAY)。start_date的时间部分被忽略。例如:next_day(‘2020-03-31′,’Monday’) = 2020-04-06;及返回下周的的周一

quarter(DATE|TIMESTAMP|STRING a)

获取日期、时间戳或字符串在一年中的季度数，范围为1到4。示例:quarter(‘2015-04-08’) = 2

to_date(STRING timestamp)

返回时间戳字符串的日期部分，例如to_date(‘1970-01-01 00:00:00’)=1970-01-01

weekofyear(STRING date)

返回时间戳字符串的周数:weekofyear(‘1970-11-01 00:00:00’) = 44, weekofyear(‘1970-11-01’) = 44。

year(STRING date)

返回日期或时间戳字符串的年份部分:year(‘1970-01-01 00:00:00’) = 1970, year(‘1970-01-01’) = 1970

Mathematical(数学函数)

abs(DOUBLE a)

返回绝对值

ceil(DOUBLE a)

返回等于或大于a的最小BIGINT值

floor(DOUBLE a)

返回等于或小于a的最大BIGINT值

rand([INT seed])

返回一个从0到1均匀分布的随机数(从行到行变化)。指定种子将确保生成的随机数序列是确定的

round(DOUBLE a [, INT d])

返回四舍五入的BIGINT值a或a的四舍五入到d位小数

Misc(类型函数)

aes_decrypt(BINARY input, STRING|BINARY key)

使用AES(从Hive 1.3.0开始)解密输入。可以使用128、192或256位的密钥长度。如果安装了Java Cryptography Extension (JCE)无限强度管辖策略文件，则可以使用192和256位密钥。如果参数为NULL或键长度不属于允许的值之一，则返回值为NULL。例如:aes_decrypt(unbase64(‘y6Ss+zCYObpCbgfWfyNWTw==’)， ‘1234567890123456’) =’ ABC’。

aes_encrypt(STRING|BINARY input, STRING|BINARY key)

使用AES加密输入(从Hive 1.3.0开始)。可以使用128、192或256位的密钥长度。如果安装了Java Cryptography Extension (JCE)无限强度管辖策略文件，则可以使用192和256位密钥。如果参数为NULL或键长度不属于允许的值之一，则返回值为NULL。例如:base64(aes_encrypt(‘ABC’， ‘1234567890123456’)) =’ y6Ss+zCYObpCbgfWfyNWTw==’。

crc32(STRING|BINARY a)

为字符串或二进制参数计算循环冗余校验值并返回bigint值(从Hive 1.3.0开始)。例如:crc32(‘ABC’) = 2743272264。

current_database()

返回当前数据库名

current_user()

返回当前用户名

get_json_object(STRING json, STRING jsonPath)

支持JSONPath的有限版本($:Root object，。:子操作符，[]:下标操作符为数组，*:通配符为[]

hash(a1[, a2…])

返回参数的哈希值

md5(STRING|BINARY a)

计算字符串或二进制文件的MD5 128位校验和(从Hive 1.3.0开始)。该值以32个十六进制数字的字符串形式返回，如果参数为NULL，则返回NULL。示例:md5(‘ABC’) = ‘902fbdd2b1df0c4f70b4a5d23525e9

String(字符串函数)

ascii(STRING str)

返回str的第一个字符的数值

base64(BINARY bin)

将参数从二进制转换为以64为基数的字符串

concat(STRING|BINARY a, STRING|BINARY b…)

按顺序将作为参数传入的字符串或字节连接起来，从而返回字符串或字节。例如，concat(‘foo’， ‘bar’)的结果是’foobar’。注意，这个函数可以接受任意数量的输入字符串

substr(STRING|BINARY A, INT start [, INT len])

返回从起始位置到字符串结束的字节数组的子字符串或片段，或使用可选长度len。例如，substr(‘foobar’， 4)会返回’bar’

substring(STRING|BINARY a, INT start [, INT len])

返回从起始位置到字符串结束的字节数组的子字符串或片段，或使用可选长度len。例如，substr(‘foobar’， 4)会返回’bar’

upper(STRING a)

返回将a的所有字符转换为大写字母后得到的字符串。例如，upper(‘fOoBaR’)会导致’fOoBaR’

Data Masking(数据屏蔽函数)

mask(STRING str [, STRING upper [, STRING lower [, STRING number]]])

返回一个隐藏的str版本(从Hive 2.1.0开始)。默认情况下，大写字母转换为“X”，小写字母转换为“X”，数字转换为“n”。例如，mask(“abcd-EFGH-8765-4321”)的结果是xx- xx- nnnn-nnnn。您可以通过提供额外的参数来覆盖掩码中使用的字符:第二个参数控制大写字母的掩码字符，第三个参数控制小写字母，第四个参数控制数字。例如,mask(“abcd – efgh – 8765 – 4321”,“U”、“l”、“#”)返回llll-UUUU – # # # # # # # #

mask_first_n(STRING str [, INT n])

返回一个掩蔽版本的str与前n个值掩蔽(如Hive 2.1.0)。将大写字母转换为“X”，将小写字母转换为“X”，将数字转换为“n”。例如，mask_first_n(“1234-5678-8765-4321”，4)的结果是nnnn-5678-8765-4321

mask_last_n(STRING str [, INT n])

返回一个隐藏的str版本，最后n个值被隐藏(从Hive 2.1.0开始)。将大写字母转换为“X”，将小写字母转换为“X”，将数字转换为“n”。例如，mask_last_n(“1234-5678-8765-4321”，4)的结果是1234-5678-8765-nnnn

mask_show_first_n(STRING str [, INT n])

返回一个掩码版本的str，显示前n个未掩码的字符(从Hive 2.1.0开始)。将大写字母转换为“X”，将小写字母转换为“X”，将数字转换为“n”。例如，mask_show_first_n(“1234-5678-8765-4321”，4)的结果是1234-nnnn-nnnn-nnnn

mask_show_last_n(STRING str [, INT n])

返回一个掩码版本的str，显示最后n个未掩码的字符(从Hive 2.1.0开始)。将大写字母转换为“X”，将小写字母转换为“X”，将数字转换为“n”。例如，mask_show_last_n(“1234-5678-8765-4321”，4)的结果是nnnn-nnnn-nnnn-4321

mask_hash(STRING|CHAR|VARCHAR str)

返回一个基于str的散列值(从Hive 2.1.0开始)。散列是一致的，可用于跨表连接带屏蔽的值。这个函数对于非字符串类型返回null

原创文章首发于 软件测试微课堂 公众号