第八章YDB函数

一、如何区分Spark 的SQL解析层与YDB的SQL解析层
      对于YDB系统来说，我们将索引与Spark集成在了一起，但是YDB层的SQL解析与SPARK
 层的SQL解析是分别处理，大家可以注意下架构图里面的那两个箭头，注意下Spark层与ydb层SQL解析的位置。

1.从架构角度




2.从SQL角度看



 

二、YDB的SQL解析层注意事项
      YDB层的作用是直接对接索引的，可以极大的提升数据检索以及统计的效率，功能简单，支持的函数并不多，目前函数仅仅内置count、avg、max、min、sum四个聚合函数。

1.YDB SQL解析层能做什么？

l充分利用索引进行过滤筛选。

l充分利用列存储机制，减少不需要列的返回。

l利用聚合函数count、avg、max、min、sum进行必要的聚合，提升性能

l针对维值不高的列可以结合group by +聚合函数 减少返回给spark层的数量，从而提升性能。

2.YDB的SQL解析层支持的过滤条件写法

l等值匹配：
如 qq="165162897"

l支持 in操作，
如：indexnum in (1,2,3)

l>,<,>=,<=,区间查询的写法
clickcount >="10" and clickcount <="11"

l对于带有范围的过滤筛选，使用下面的方式能提升查询效率
indexnum like "({0 TO 11}) "     不包含边界值
indexnum like "([10 TO 11] ) "   包含边界值

l不等于的写法
label<>"l_14" and label<>"l_15"

l过滤条件可以进行and与or的组合
indexnum="1" or indexnum="2" or (clickcount >="5" and clickcount <="11")

 

 

3.YDB SQL解析层的限制

l不可以进行SQL嵌套

l不可以进行多表关联

l不可以进行UNION操作

l除了count、avg、max、min、sum外，其他函数一概不支持。

l不支持自定义udf、udaf、udtf的函数的写法。

l不支持列与列之间的运算，大小比较，只支持上面的过滤写法。
 

4.YDB SQL解析层支持的写法示例
 

1.查看数据例子
      /*ydb.pushdown('->')*/
       select indexnum,label from ydbexample where ydbpartion='20151011' limit 0,100

/*('<-')pushdown.ydb*/
 

2.对某个列进行排序
       /*ydb.pushdown('->')*/
      select indexnum,label from ydbexample where ydbpartion='20151011'
 order byindexnum desc limit 0,100

/*('<-')pushdown.ydb*/
 

3.对表进行简单的count统计
/*ydb.pushdown('->')*/
select count(*),count(indexnum) fromydbexample
whereydbpartion='20151011' limit 0,100

/*('<-')pushdown.ydb*/
 
注意下count(*)与count(ddwuid)的区别，前者不管列的值是否是null，计数都会加1，而且因为不需要读取字段的值，性能很高，后者需要判断改字段的值是否为null，如果是null则不会进行累加计数，所以大部分场景都推荐使用count(*)

4.简单的统计函数
/*ydb.pushdown('->')*/
select sum(clickcount),avg(clickcount),max(clickcount),min(clickcount)
 from ydbexample where  ydbpartion='20151011' limit 100

/*('<-')pushdown.ydb*/
 

5.分类汇总的写法group by
/*ydb.pushdown('->')*/
select label,count(*),sum(clickcount) from ydbexample whereydbpartion='20151011' group
 by label limit 100

/*('<-')pushdown.ydb*/
 

l多个列进行gourp by 分类汇总
/*ydb.pushdown('->')*/
select label,userage,count(*),sum(clickcount)
 from ydbexample where ydbpartion='20151011' group by label,userage
 limit 100

/*('<-')pushdown.ydb*/
 

6.对分类汇总的结果进行排序
/*ydb.pushdown('->')*/
select label,userage,count(*),sum(clickcount) from ydbexample
 where ydbpartion='20151011' group by label,userage
 order byuserage limit 100

/*('<-')pushdown.ydb*/
 
 
/*ydb.pushdown('->')*/
select label,userage,count(*),sum(clickcount) as amt from
 ydbexample where ydbpartion='20151011' group by label,userage
 order by amt desc,count(*) desc limit 100

/*('<-')pushdown.ydb*/
 

7.多列排序的写法
/*ydb.pushdown('->')*/
select label,indexnum from ydbexample where ydbpartion='20151011' order by label desc,indexnum asc limit 1000

/*('<-')pushdown.ydb*/
 
 
/*ydb.pushdown('->')*/
select label,indexnum,count(label) from ydbexample where ydbpartion='20151011' group by label,indexnum order by label asc,indexnum
 asc limit 1000

/*('<-')pushdown.ydb*/
 
 
/*ydb.pushdown('->')*/
select label,indexnum,count(label) from ydbexample where ydbpartion='20151011' group by label,indexnum order by count(label) desc,indexnum
 desc limit 1000

/*('<-')pushdown.ydb*/
 
 

三、Spark（HIVE）的SQL解析层 内置函数
Spark的SQL层支持复杂的SQL写法，很多在YDB层实现不了的功能，可以再Spark层来实现。
一、关系运算：
1. 等值比较: =
      语法：A=B
      操作类型：所有基本类型
      描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE
      举例：
      Hive>
 select 1 from lxw_dual where 1=1;
      1
2. 不等值比较: <>
      语法: A <> B
操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where 1 <> 2;

1
3.小于比较: <
      语法: A < B
操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where 1 < 2;
1
 
4. 小于等于比较: <=
语法: A <= B
操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where 1 <= 1;
1
5. 大于比较: >
语法: A > B
操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于表达式B，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where 2 > 1;
1
 
6. 大于等于比较: >=
语法: A >= B
操作类型: 所有基本类型

描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于或者等于表达式B，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where 1 >= 1;
1
 
注意：String的比较要注意(常用的时间比较可以先to_date之后再比较)
hive> select * from lxw_dual;
2011111209 00:00:00    2011111209
 
hive> select a,b,a<b,a>b,a=b from lxw_dual;
2011111209 00:00:00    2011111209     
false   true    false
 
7. 空值判断: IS NULL
语法: A IS NULL
操作类型: 所有类型
描述: 如果表达式A的值为NULL，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where null is null;
1
 
8. 非空判断: IS NOT NULL
语法: A IS NOT NULL
操作类型: 所有类型
描述: 如果表达式A的值为NULL，则为FALSE；否则为TRUE
举例：
hive> select 1 from lxw_dual where 1 is not null;
1
 
9. LIKE比较: LIKE
语法: A LIKE B
操作类型: strings

描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B   
的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。
举例：
hive> select 1 from lxw_dual where 'football' like 'foot%';
1
hive> select 1 from lxw_dual where 'football' like 'foot____';
1
注意：否定比较时候用NOT A LIKE B
hive> select 1 from lxw_dual whereNOT
'football' like 'fff%';
1
 
10. JAVA的LIKE操作: RLIKE
语法: A RLIKE B
操作类型: strings

描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合Java正则表达式B的正则语法，则为TRUE；否则为FALSE。
举例：
hive> select 1 from lxw_dual where 'footbar’ rlike '^f.*r$’;
1
注意：判断一个字符串是否全为数字：
hive>select 1 from lxw_dual where '123456' rlike '^\\d+$';
1
hive> select 1 from lxw_dual where '123456aa' rlike '^\\d+$';
 
11. REGEXP操作: REGEXP
语法: A REGEXP B
操作类型: strings
描述: 功能与RLIKE相同
举例：
hive> select 1 from lxw_dual where 'footbar' REGEXP '^f.*r$';
1
二、数学运算：
1. 加法操作: +
语法: A + B
操作类型：所有数值类型
说明：返回A与B相加的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。比如，int + int 一般结果为int类型，而int + double 一般结果为double类型
举例：
hive> select 1 + 9 from lxw_dual;
10
hive> create table lxw_dual as select 1 + 1.2 from lxw_dual;
hive> describe lxw_dual;
_c0    double
 
2. 减法操作: -
语法: A – B
操作类型：所有数值类型
说明：返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。比如，int – int 一般结果为int类型，而int – double 一般结果为double类型
举例：
hive> select 10 – 5 from lxw_dual;
5
hive> create table lxw_dual as select 5.6 – 4 from lxw_dual;
hive> describe lxw_dual;
_c0    double
 
3. 乘法操作: *
语法: A * B
操作类型：所有数值类型
说明：返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。注意，如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型
举例：
hive> select 40 * 5 fromlxw_dual;
200
 
4. 除法操作: /
语法: A / B
操作类型：所有数值类型
说明：返回A除以B的结果。结果的数值类型为double
举例：
hive> select 40 / 5 from lxw_dual;
8.0
 
注意：hive中最高精度的数据类型是double,只精确到小数点后16位，在做除法运算的时候要特别注意
hive>select ceil(28.0/6.999999999999999999999) from lxw_dual limit 1;   
结果为4
hive>select ceil(28.0/6.99999999999999) from lxw_dual limit 1;          
结果为5
 
5. 取余操作: %
语法: A % B
操作类型：所有数值类型
说明：返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。
举例：
hive> select 41 % 5 from lxw_dual;
1
hive> select 8.4 % 4 from lxw_dual;
0.40000000000000036
注意：精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度
hive> select round(8.4 % 4 , 2) from lxw_dual;
0.4
6. 位与操作: &
语法: A & B
操作类型：所有数值类型
说明：返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。
举例：
hive> select 4 & 8 from lxw_dual;
0
hive> select 6 & 4 from lxw_dual;
4
 
7. 位或操作: |
语法: A | B
操作类型：所有数值类型
说明：返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。
举例：
hive> select 4 | 8 from lxw_dual;
12
hive> select 6 | 8 from lxw_dual;
14
 
8. 位异或操作: ^
语法: A ^ B
操作类型：所有数值类型
说明：返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。
举例：
hive> select 4 ^ 8 from lxw_dual;
12
hive> select 6 ^ 4 from lxw_dual;
2
 
9．位取反操作: ~
语法: ~A
操作类型：所有数值类型
说明：返回A按位取反操作的结果。结果的数值类型等于A的类型。
举例：
hive> select ~6 from lxw_dual;
-7
hive> select ~4 from lxw_dual;
-5
三、逻辑运算：
1. 逻辑与操作: AND
语法: A AND B
操作类型：boolean
说明：如果A和B均为TRUE，则为TRUE；否则为FALSE。如果A为NULL或B为NULL，则为NULL
举例：
hive> select 1 from lxw_dual where 1=1 and 2=2;
1
 
2. 逻辑或操作: OR
语法: A OR B
操作类型：boolean
说明：如果A为TRUE，或者B为TRUE，或者A和B均为TRUE，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where 1=2 or 2=2;
1
 
3. 逻辑非操作: NOT
语法: NOT A
操作类型：boolean
说明：如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE
举例：
hive> select 1 from lxw_dual where not 1=2;
1
 
四、数值计算
1. 取整函数: round
语法: round(double a)
返回值: BIGINT
说明: 返回double类型的整数值部分 （遵循四舍五入）
举例：
hive> select round(3.1415926) from lxw_dual;
3
hive> select round(3.5) from lxw_dual;
4
hive> create table lxw_dual as select round(9542.158) from lxw_dual;
hive> describe lxw_dual;
_c0    bigint
 
2. 指定精度取整函数: round
语法: round(double a, int d)
返回值: DOUBLE
说明: 返回指定精度d的double类型
举例：
hive> select round(3.1415926,4) from lxw_dual;
3.1416
 
3. 向下取整函数: floor
语法: floor(double a)
返回值: BIGINT
说明: 返回等于或者小于该double变量的最大的整数
举例：
hive> select floor(3.1415926) from lxw_dual;
3
hive> select floor(25) from lxw_dual;
25
 
4. 向上取整函数: ceil
语法: ceil(double a)
返回值: BIGINT
说明: 返回等于或者大于该double变量的最小的整数
举例：
hive> select ceil(3.1415926) from lxw_dual;
4
hive> select ceil(46) from lxw_dual;
46
 
5. 向上取整函数: ceiling
语法: ceiling(double a)
返回值: BIGINT
说明: 与ceil功能相同
举例：
hive> select ceiling(3.1415926) from lxw_dual;
4
hive> select ceiling(46) from lxw_dual;
46
6. 取随机数函数: rand
语法: rand(),rand(int seed)
返回值: double
说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
举例：
hive> select rand() from lxw_dual;
0.5577432776034763
hive> select rand() from lxw_dual;
0.6638336467363424
hive> select rand(100) from lxw_dual;
0.7220096548596434
hive> select rand(100) from lxw_dual;
0.7220096548596434
 
7. 自然指数函数: exp
语法: exp(double a)
返回值: double
说明: 返回自然对数e的a次方
举例：
hive> select exp(2) from lxw_dual;
7.38905609893065
自然对数函数: ln
语法: ln(double a)
返回值: double
说明: 返回a的自然对数
举例：
hive> select ln(7.38905609893065) from lxw_dual;
2.0
 
8. 以10为底对数函数: log10
语法: log10(double a)
返回值: double
说明: 返回以10为底的a的对数
举例：
hive> select log10(100) from lxw_dual;
2.0
 
9. 以2为底对数函数: log2
语法: log2(double a)
返回值: double
说明: 返回以2为底的a的对数
举例：
hive> select log2(8) from lxw_dual;
3.0
 
10. 对数函数: log
语法: log(double base, double a)
返回值: double
说明: 返回以base为底的a的对数
举例：
hive> select log(4,256) from lxw_dual;
4.0
 
11. 幂运算函数: pow
语法: pow(double a, double p)
返回值: double
说明: 返回a的p次幂
举例：
hive> select pow(2,4) from lxw_dual;
16.0
 
12. 幂运算函数: power
语法: power(double a, double p)
返回值: double
说明: 返回a的p次幂,与pow功能相同
举例：
hive> select power(2,4) from lxw_dual;
16.0
 
13. 开平方函数: sqrt
语法: sqrt(double a)
返回值: double
说明: 返回a的平方根
举例：
hive> select sqrt(16) from lxw_dual;
4.0
 
14. 二进制函数: bin
语法: bin(BIGINT a)
返回值: string
说明: 返回a的二进制代码表示
举例：
hive> select bin(7) from lxw_dual;
111
 
15. 十六进制函数: hex
语法: hex(BIGINT a)
返回值: string
说明: 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示
举例：
hive> select hex(17) from lxw_dual;
11
hive> select hex(‘abc’) from lxw_dual;
616263
 
16. 反转十六进制函数: unhex
语法: unhex(string a)
返回值: string
说明: 返回该十六进制字符串所代码的字符串
举例：
hive> select unhex(‘616263’)
 from lxw_dual;
abc
hive> select unhex(‘11’)
 from lxw_dual;
-
hive> select unhex(616263) from lxw_dual;
abc
 
17. 进制转换函数: conv
语法: conv(BIGINT num, int from_base, int to_base)
返回值: string
说明: 将数值num从from_base进制转化到to_base进制
举例：
hive> select conv(17,10,16) from lxw_dual;
11
hive> select conv(17,10,2) from lxw_dual;
10001
 
18. 绝对值函数: abs
语法: abs(double a)  abs(int a)
返回值: double       int
说明: 返回数值a的绝对值
举例：
hive> select abs(-3.9) from lxw_dual;
3.9
hive> select abs(10.9) from lxw_dual;
10.9
 
19. 正取余函数: pmod
语法: pmod(int a, int b),pmod(double a, double b)
返回值: int double
说明: 返回正的a除以b的余数
举例：
hive> select pmod(9,4) from lxw_dual;
1
hive> select pmod(-9,4) from lxw_dual;
3
 
20. 正弦函数: sin
语法: sin(double a)
返回值: double
说明: 返回a的正弦值
举例：
hive> select sin(0.8) from lxw_dual;
0.7173560908995228
 
21. 反正弦函数: asin
语法: asin(double a)
返回值: double
说明: 返回a的反正弦值
举例：
hive> select asin(0.7173560908995228) from lxw_dual;
0.8
 
22. 余弦函数: cos
语法: cos(double a)
返回值: double
说明: 返回a的余弦值
举例：
hive> select cos(0.9) from lxw_dual;
0.6216099682706644
 
23. 反余弦函数: acos
语法: acos(double a)
返回值: double
说明: 返回a的反余弦值
举例：
hive> select acos(0.6216099682706644) from lxw_dual;
0.9
24. positive函数: positive
语法: positive(int a), positive(double a)
返回值: int double
说明: 返回a
举例：
hive> select positive(-10) from lxw_dual;
-10
hive> select positive(12) from lxw_dual;
12
25. negative函数: negative
语法: negative(int a), negative(double a)
返回值: int double
说明: 返回-a
举例：
hive> select negative(-5) from lxw_dual;
5
hive> select negative(8) from lxw_dual;
-8
五、日期函数
1. 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 lxw_dual;
20111208
2. 获取当前UNIX时间戳函数: unix_timestamp
语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
举例：
hive> select unix_timestamp() from lxw_dual;
1323309615
3. 日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
举例：
hive> select unix_timestamp('2011-12-07 13:01:03') from lxw_dual;
1323234063
4. 指定格式日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
举例：
hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from lxw_dual;
1323234063
5. 日期时间转日期函数: to_date
语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
举例：
hive> select to_date('2011-12-08 10:03:01') from lxw_dual;
2011-12-08
 
6. 日期转年函数: year
语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
hive> select year('2011-12-08 10:03:01') from lxw_dual;
2011
hive> select year('2012-12-08') from lxw_dual;
2012
 
7. 日期转月函数: month
语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：
hive> select month('2011-12-08 10:03:01') from lxw_dual;
12
hive> select month('2011-08-08') from lxw_dual;
8
 
8. 日期转天函数: day
语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
hive> select day('2011-12-08 10:03:01') from lxw_dual;
8
hive> select day('2011-12-24') from lxw_dual;
24
 
9. 日期转小时函数: hour
语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
hive> select hour('2011-12-08 10:03:01') from lxw_dual;
10
 
10. 日期转分钟函数: minute
语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
hive> select minute('2011-12-08 10:03:01') from lxw_dual;
3
 
11. 日期转秒函数: second
语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：
hive> select second('2011-12-08 10:03:01') from lxw_dual;
1
 
12. 日期转周函数: weekofyear
语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
hive> select weekofyear('2011-12-08 10:03:01') from lxw_dual;
49
 
13. 日期比较函数: datediff
语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例：
hive> select datediff('2012-12-08','2012-05-09') from lxw_dual;
213
 
14. 日期增加函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例：
hive> select date_add('2012-12-08',10) from lxw_dual;
2012-12-18
 
15. 日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例：
hive> select date_sub('2012-12-08',10) from lxw_dual;
2012-11-28
 
六、条件函数
1. If函数: if
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
举例：
hive> select if(1=2,100,200) from lxw_dual;
200
hive> select if(1=1,100,200) from lxw_dual;
100
 
2. 非空查找函数: COALESCE
语法: COALESCE(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
举例：
hive> select COALESCE(null,'100','50′) from lxw_dual;
100
 
3. 条件判断函数：CASE
语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
举例：
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from lxw_dual;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from lxw_dual;
tim
 
4. 条件判断函数：CASE
语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值: T
说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
举例：
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from lxw_dual;
mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from lxw_dual;
tom
 
七、字符串函数
1. 字符串长度函数：length
语法: length(string A)
返回值: int
说明：返回字符串A的长度
举例：
hive> select length('abcedfg') from lxw_dual;
7
 
2. 字符串反转函数：reverse
语法: reverse(string A)
返回值: string
说明：返回字符串A的反转结果
举例：
hive> select reverse(abcedfg’) from lxw_dual;
gfdecba
 
3. 字符串连接函数：concat
语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
举例：
hive> select concat(‘abc’,'def’,'gh’) from lxw_dual;
abcdefgh
 
4. 带分隔符字符串连接函数：concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：
hive> select concat_ws(',','abc','def','gh') from lxw_dual;
abc,def,gh
 
5. 字符串截取函数：substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明：返回字符串A从start位置到结尾的字符串
举例：
hive> select substr('abcde',3) from lxw_dual;
cde
hive> select substring('abcde',3) from lxw_dual;
cde
hive> select substr('abcde',-1) from lxw_dual; 
（和Oracle相同）
e
 
6. 字符串截取函数：substr,substring
语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明：返回字符串A从start位置开始，长度为len的字符串
举例：
hive> select substr('abcde',3,2) from lxw_dual;
cd
hive> select substring('abcde',3,2) from lxw_dual;
cd
hive>select substring('abcde',-2,2) from lxw_dual;
de
 
7. 字符串转大写函数：upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
说明：返回字符串A的大写格式
举例：
hive> select upper('abSEd') from lxw_dual;
ABSED
hive> select ucase('abSEd') from lxw_dual;
ABSED
8. 字符串转小写函数：lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
说明：返回字符串A的小写格式
举例：
hive> select lower('abSEd') from lxw_dual;
absed
hive> select lcase('abSEd') from lxw_dual;
absed
 
9. 去空格函数：trim
语法: trim(string A)
返回值: string
说明：去除字符串两边的空格
举例：
hive> select trim(' abc ') from lxw_dual;
abc
 
10. 左边去空格函数：ltrim
语法: ltrim(string A)
返回值: string
说明：去除字符串左边的空格
举例：
hive> select ltrim(' abc ') from lxw_dual;
abc
 
11. 右边去空格函数：rtrim
语法: rtrim(string A)
返回值: string
说明：去除字符串右边的空格
举例：
hive> select rtrim(' abc ') from lxw_dual;
abc
 
12. 正则表达式替换函数：regexp_replace
语法: regexp_replace(string A, string B, string C)
返回值: string
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
举例：
hive> select regexp_replace('foobar', 'oo|ar', '') from lxw_dual;
fb
 
13. 正则表达式解析函数：regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
举例：
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from lxw_dual;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from lxw_dual;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from lxw_dual;
foothebar
注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
select data_field,
      regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1)
 as aaa,
      regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1)
 as bbb,
      regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1)
 as ccc
      from pt_nginx_loginlog_st

      where pt = '2012-03-26' limit 2;
 
14. URL解析函数：parse_url
语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
举例：
hive> select parse_url('http://facebook.com/path1/p.PHP?k1=v1&k2=v2#Ref1',
 'HOST') from lxw_dual;
facebook.com
hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from lxw_dual;
v1
15. json解析函数：get_json_object
语法: get_json_object(string json_string, string path)
返回值: string
说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
举例：
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 lxw_dual;
amy
 
16. 空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：
hive> select space(10) from lxw_dual;
hive> select length(space(10)) from lxw_dual;
10
 
17. 重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串
举例：
hive> select repeat('abc',5) from lxw_dual;
abcabcabcabcabc
 
18. 首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：
hive> select ascii('abcde') from lxw_dual;
97
 
19. 左补足函数：lpad
语法: lpad(string str, int len, string pad)
返回值: string
说明：将str进行用pad进行左补足到len位
举例：
hive> select lpad('abc',10,'td') from lxw_dual;
tdtdtdtabc
注意：与GP，ORACLE不同，pad 不能默认
 
20. 右补足函数：rpad
语法: rpad(string str, int len, string pad)
返回值: string
说明：将str进行用pad进行右补足到len位
举例：
hive> select rpad('abc',10,'td') from lxw_dual;
abctdtdtdt
 
21. 分割字符串函数: split
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
举例：
hive> select split('abtcdtef','t') from lxw_dual;
["ab","cd","ef"]
 
22. 集合查找函数: find_in_set
语法: find_in_set(string str, string strList)
返回值: int
说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
举例：
hive> select find_in_set('ab','ef,ab,de') from lxw_dual;
2
hive> select find_in_set('at','ef,ab,de') from lxw_dual;
0
// select split('12304560789','0') as a from hmbbs limit 1 返回数组：["123","456","789"]
// concat_ws(',',e.a[0],e.a[1],e.a[2])返回：123,456,789转换string
hive> select find_in_set('456',concat_ws(',',e.a[0],e.a[1],e.a[2])) from (select split('12304560789','0') as a from hmbbs limit 1)e;
八、集合统计函数
1. 个数统计函数: count
语法: count(*), count(expr), count(DISTINCT expr[, expr_.])
返回值: int
说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数
举例：
hive> select count(*) from lxw_dual;
20
hive> select count(distinct t) from lxw_dual;
10
 
2. 总和统计函数: sum
语法: sum(col), sum(DISTINCT col)
返回值: double
说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
举例：
hive> select sum(t) from lxw_dual;
100
hive> select sum(distinct t) from lxw_dual;
70
 
3. 平均值统计函数: avg
语法: avg(col), avg(DISTINCT col)
返回值: double
说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值
举例：
hive> select avg(t) from lxw_dual;
50
hive> select avg (distinct t) from lxw_dual;
30
 
4. 最小值统计函数: min
语法: min(col)
返回值: double
说明: 统计结果集中col字段的最小值
举例：
hive> select min(t) from lxw_dual;
20
 
5. 最大值统计函数: max
语法: maxcol)
返回值: double
说明: 统计结果集中col字段的最大值
举例：
hive> select max(t) from lxw_dual;
120
 
6. 非空集合总体变量函数: var_pop
语法: var_pop(col)
返回值: double
说明: 统计结果集中col非空集合的总体变量（忽略null）
举例：
 
7. 非空集合样本变量函数: var_samp
语法: var_samp (col)
返回值: double
说明: 统计结果集中col非空集合的样本变量（忽略null）
举例：
 
8. 总体标准偏离函数: stddev_pop
语法: stddev_pop(col)
返回值: double
说明: 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同
举例：
 
9. 样本标准偏离函数: stddev_samp
语法: stddev_samp (col)
返回值: double
说明: 该函数计算样本标准偏离
举例：
 
10．中位数函数: percentile
语法: percentile(BIGINT col, p)
返回值: double
说明: 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型
举例：
 
11. 中位数函数: percentile
语法: percentile(BIGINT col, array(p1 [, p2]…))
返回值: array<double>
说明: 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array<double>，其中为对应的百分位数。
举例：
select percentile(score,<0.2,0.4>) from lxw_dual； 取0.2，0.4位置的数据
 
12. 近似中位数函数: percentile_approx
语法: percentile_approx(DOUBLE col, p [, B])
返回值: double
说明: 求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数
举例：
13. 近似中位数函数: percentile_approx
语法: percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])
返回值: array<double>
说明: 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array<double>，其中为对应的百分位数。
举例：
 
14. 直方图: histogram_numeric
语法: histogram_numeric(col, b)
返回值: array<struct {‘x’,‘y’}>
说明: 以b为基准计算col的直方图信息。
举例：
hive> select histogram_numeric(100,5) fromlxw_dual;
[{"x":100.0,"y":1.0}]
 
九、复合类型构建操作
1. Map类型构建: map
语法: map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型
举例：
hive> Create table lxw_test as select map('100','tom','200','mary') as t from lxw_dual;
hive> describe lxw_test;
t      map<string,string>
hive> select t from lxw_test;
{"100":"tom","200":"mary"}
 
2. Struct类型构建: struct
语法: struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类型
举例：
hive> create table lxw_test as select struct('tom','mary','tim') as t from lxw_dual;
hive> describe lxw_test;
t      struct<col1:string,col2:string,col3:string>
hive> select t from lxw_test;
{"col1":"tom","col2":"mary","col3":"tim"}
 
3. array类型构建: array
语法: array(val1, val2, …)
说明：根据输入的参数构建数组array类型
举例：
hive> create table lxw_test as select array("tom","mary","tim") as t from lxw_dual;
hive> describe lxw_test;
t      array<string>
hive> select t from lxw_test;
["tom","mary","tim"]
 
十、复杂类型访问操作
1. array类型访问: A[n]
语法: A[n]
操作类型: A为array类型，n为int类型
说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'
举例：
hive> create table lxw_test as select array("tom","mary","tim") as t from lxw_dual;
hive> select t[0],t[1],t[2] from lxw_test;
tom    mary   
tim
 
2. map类型访问: M[key]
语法: M[key]
操作类型: M为map类型，key为map中的key值
说明：返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'
举例：
hive> Create table lxw_test as select map('100','tom','200','mary') as t from lxw_dual;
hive> select t['200'],t['100'] from lxw_test;
mary   tom
 
3. struct类型访问: S.x
语法: S.x
操作类型: S为struct类型
说明：返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段
举例：
hive> create table lxw_test as select struct('tom','mary','tim') as t from lxw_dual;
hive> describe lxw_test;
t      struct<col1:string,col2:string,col3:string>
hive> select t.col1,t.col3 from lxw_test;
tom    tim
 
十一、复杂类型长度统计函数
Map类型长度函数: size(Map<K.V>)
语法: size(Map<K.V>)
返回值: int
说明: 返回map类型的长度
举例：
hive> select size(map('100','tom','101','mary')) from lxw_dual;
2
 
array类型长度函数: size(Array<T>)
语法: size(Array<T>)
返回值: int
说明: 返回array类型的长度
举例：
hive> select size(array('100','101','102','103')) from lxw_dual;
4
 
类型转换函数
类型转换函数: cast
语法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
说明: 返回array类型的长度
举例：
hive> select cast(1 as bigint) from lxw_dual;
1
 



 
 

四、Spark(Hive)的
 SQL解析层 自定义函数以及拓展第三方函数

1.自定义函数如何配置
Hive可以允许用户编写自己定义的函数UDF，来在查询中使用。Hive中有3种UDF：
      UDF： 操作单个数据行，产生单个数据行；
      UDAF： 操作多个数据行，产生一个数据行。
      UDTF： 操作一个数据行，产生多个数据行一个表作为输出。
自定义函数遵循Hive的udf、udtf、udaf的写法规范，一个自定义函数写好后，我们需要分如下几个步骤让自定义函数生效。

1)将编写好的自定义函数导出为jar包，并放到/lib目录

2)编写conf下的init.sql文件，注册函数。

注册写法为如下
create function
xxxx as 'xxx类型' 如
create function Ytest as 'cn.Net.ycloud.ydb.handle.fun.Ytest'
注意不能像hive那样注册临时函数。
(create temporary function my_lower as 'com.example.hive.udf.Lower';)

3)重启YDB。
 

2.Spark中的聚合函数UDAF性能问题
      目前的Spark兼容Hive的UDAF函数，但是性能并不好，并没有使用到UDAF里面的combine功能，也就是说，数据并没有先在本地进行一次local combine.
      这个问题除了性能慢外，还有一个最大的隐患就是，数据倾斜
      我们进行数据的group by，或者只使用UDAF函数，不进行group by，如果某一个group 的数据特别多，假设占据90%以上的数据，那么Spark目前就会将这个group的数据发送给同一个进程（其他group的按照hash的方式发送给其他进程），这样这个进程由于处理的数据特别多，会成为性能瓶颈，影响整体的响应时间，甚至出现OOM的现象。
      针对这种问题延云的建议
      多嵌套一层group by，先用一个没有严重数据倾斜的列，比如说rand(int seed),进行
 group by，进行一次聚合后，在抛给真正要进行group by的列进行最终聚合。
      最终解决办法
      我们猜测Spark一直没有很好的支持UDAF的问题的根源在于Hive的UDAF特别难以进行序列化，而Spark本身提供了Spark的UDAF的写法，延云YDB的下一步有可能会支持Spark本身的UDAF的写法，届时这个UDAF的问题会从根本上给予解决。
 
 

3.UDF编写示例

lHive官方示例
packagecom.example.hive.udf;
 
importorg.apache.Hadoop.hive.ql.exec.UDF;
importorg.apache.hadoop.io.Text;
 
publicfinalclassLower
 extendsUDF {
 public Text evaluate(finalText
 s) {
   if (s == null) { returnnull;
 }
   return newText(s.toString().toLowerCase());
 }
}
 
create function my_lower as 'com.example.hive.udf.Lower';
 
hive> select my_lower(title), sum(freq) from titles group by my_lower(title);
 
...
 
Ended Job = job_200906231019_0006
OK
cmo 13.0
vp 7.0
 
 

l以我们的YDB内置的MortonHashDistance函数为例
 
package cn.net.ycloud.ydb.handle.fun;
 
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.lucene.util.GeoUtils;
import org.apache.lucene.util.SloppyMath;
 
 
publicclassYMortonHashDistanceExampelextends
 GenericUDF{
        
privatetransient
 PrimitiveObjectInspector[]converters;
 
        
@Override
        
public ObjectInspector initialize(ObjectInspector[]arguments)throws
 UDFArgumentException {
              
   converters=new
 PrimitiveObjectInspector[arguments.length];
              
    for (inti
 = 0;i <arguments.length;i++)
 {
 
              
        if (!(arguments[i]instanceof
 PrimitiveObjectInspector)) {
              
            thrownew
 UDFArgumentTypeException(1,
              
                "Primitive Type is expected but " +arguments[i].getTypeName()
              
                + "\" is found");
              
          }
              
            
              
            converters[i]
 = (PrimitiveObjectInspector) arguments[i];
              
            
              
    
              
    }
          
return PrimitiveObjectInspectorFactory.javaDoubleObjectInspector;
        
}
 
        
@Override
        
public Object evaluate(DeferredObject[]arguments)throws
 HiveException {
              

          
        Object lon=converters[1].getPrimitiveJavaObject(arguments[1].get());
          
        Object lat=converters[2].getPrimitiveJavaObject(arguments[2].get());
          
        if(lon==null||lat==null)
          
        {
          
                returnnull;
          
        }
          
        
          
        Object geoo=converters[0].getPrimitiveJavaObject(arguments[0].get());
          
        if(geoo==null)
          
        {
          
                returnnull;
          
        }
          
        longgeo=Utils.parseLong(String.valueOf(geoo));
        
        
          
return 1000l*SloppyMath.haversin(GeoUtils.mortonUnhashLat(geo),
 GeoUtils.mortonUnhashLon(geo),
 Utils.parseDouble(String.valueOf(lat)),
 Utils.parseDouble(String.valueOf(lon)));
        
}
 
        
@Override
        
public String getDisplayString(String[]children)
 {
          
StringBuilder sb =new
 StringBuilder();
          
sb.append("YMortonHashDistance("+this.getClass().getSimpleName()).append(")");
          
returnsb.toString();
        
}
 
}
 

l当然了HIVE也支持旧版UDF接口的写法
 
import org.apache.hadoop.hive.ql.exec.UDF;
 
publicfinalclass
 Addextends UDF {
      public
 Integer evaluate(Integera, Integerb)
 {
            
if (null
 == a ||null
 ==b) {
            
       returnnull;
            
}
            
returna
 +b;
      }
 
      public
 Double evaluate(Doublea, Doubleb)
 {
            
if (a
 == null ||b
 ==null)
            
       returnnull;
            
returna
 +b;
      }
 
}
 

4.UDAF编写示例
--hive新版udaf写法--
 
 
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory.ObjectInspectorOptions;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
 
publicclass
 TotalNumOfLettersEvaluatorextends GenericUDAFEvaluator {
 
       PrimitiveObjectInspector
inputOI;
       ObjectInspector
outputOI;
       PrimitiveObjectInspector
integerOI;
 
       inttotal
 = 0;
 
       @Override
       public
 ObjectInspector init(Modem, ObjectInspector[]parameters)
              
        throws HiveException {
 
              
assert (parameters.length
 == 1);
              
super.init(m,parameters);
 
              
//init
 input object inspectors
              
if (m
 == Mode.PARTIAL1 ||m
 == Mode.COMPLETE) {
              
        inputOI = (PrimitiveObjectInspector)parameters[0];
              
} else {
              
        integerOI = (PrimitiveObjectInspector)parameters[0];
              
}
 
              
//init
 output object inspectors
              
outputOI = ObjectInspectorFactory.getReflectionObjectInspector(
              
                Integer.class, ObjectInspectorOptions.JAVA);
              
returnoutputOI;
 
       }
 
       /**
       * class for storing the current sum of letters
       */
       staticclass
 LetterSumAggimplementsAggregationBuffer
 {
              
intsum
 = 0;
 
              
void add(intnum)
 {
              
        sum +=num;
              
}
       }
 
       @Override
       publicAggregationBuffer
 getNewAggregationBuffer()throws HiveException {
              
LetterSumAgg result =new
 LetterSumAgg();
              
returnresult;
       }
 
       @Override
       publicvoid
 reset(AggregationBufferagg)throws
 HiveException {
              
LetterSumAgg myagg =new
 LetterSumAgg();
       }
 
       privatebooleanwarned
 =false;
 
       @Override
       publicvoid
 iterate(AggregationBufferagg,
 Object[]parameters)
              
        throws HiveException {
              
assert (parameters.length
 == 1);
              
if (parameters[0]
 != null) {
              
        LetterSumAgg myagg = (LetterSumAgg)agg;
              
        Object p1 = ((PrimitiveObjectInspector)inputOI)
              
                       .getPrimitiveJavaObject(parameters[0]);
              
        myagg.add(String.valueOf(p1).length());
              
}
       }
 
       @Override
       public
 Object terminatePartial(AggregationBufferagg)throws
 HiveException {
              
LetterSumAgg myagg = (LetterSumAgg)agg;
              
total +=myagg.sum;
              
returntotal;
       }
 
       @Override
       publicvoid
 merge(AggregationBufferagg,
 Objectpartial)
              
        throws HiveException {
              
if (partial
 != null) {
 
              
        LetterSumAgg myagg1 = (LetterSumAgg)agg;
 
              
        Integer partialSum = (Integer)integerOI
              
                       .getPrimitiveJavaObject(partial);
 
              
        LetterSumAgg myagg2 =new
 LetterSumAgg();
 
              
        myagg2.add(partialSum);
              
        myagg1.add(myagg2.sum);
              
}
       }
 
       @Override
       public
 Object terminate(AggregationBufferagg)throws
 HiveException {
              
LetterSumAgg myagg = (LetterSumAgg)agg;
              
total =myagg.sum;
              
returnmyagg.sum;
       }
 
}
 
--旧版写法--
 
 
package test;
 
import org.apache.hadoop.hive.ql.exec.UDAF; 
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator; 
import org.apache.hadoop.io.Text; 
import java.util.HashMap; 
import java.util.Map; 
public class helloUDAF extends UDAF { 
   public static class Evaluator implements UDAFEvaluator 
   { 

      //存放不同学生的总分 

      private static Map<String,Integer> ret; 

 
      public Evaluator() 

      { 

      super(); 

          init(); 

      } 

 
      //初始化 

      public void init() 

      { 

     ret = new HashMap<String,Integer>(); 

      } 

 
      //map阶段，遍历所有记录 

      public boolean iterate(String strStudent,int nScore) 

      {  

        if(ret.containsKey(strStudent)) 

        { 

           int nValue = ret.get(strStudent); 

           nValue +=nScore; 

           ret.put(strStudent,nValue); 

        } 

        else 

        { 

          ret.put(strStudent,nScore); 

        } 

        return true; 

      } 

     
      //返回最终结果  

      public Map<String,Integer> terminate() 

      { 

        return ret; 

      } 

 
      //combiner阶段，本例不需要 

      public Map<String,Integer> terminatePartial()  

      { 

         return ret; 

      } 

 
      //reduce阶段 

      public boolean merge(Map<String,Integer> other) 

      { 

           for (Map.Entry<String, Integer> e : other.entrySet()) { 
               ret.put(e.getKey(),e.getValue()); 

           } 

           return true; 

      } 

   }    

} 
 

5.Hive UDTF示例
UDTF(User-Defined Table-Generating Functions) 用来解决输入一行输出多行(On-to-many maping) 的需求。
编写自己需要的UDTF
     继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF,实现initialize, process, close三个方法。
UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）。
 
初始化完成后，会调用process方法,真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。
 
最后close()方法调用，对需要清理的方法进行清理。
 
 
下面是我写的一个用来切分”key:value;key:value;”这种字符串，返回结果为key, value两个字段。供参考：
 
import java.util.ArrayList;
 
 import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
 import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
 import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
 import org.apache.hadoop.hive.ql.metadata.HiveException;
 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
 import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
 import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
 import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
 
 public class ExplodeMap extends GenericUDTF{
 
    @Override
    public void close() throws HiveException {
        // TODO Auto-generated method stub   

    }
 
    @Override
    public StructObjectInspector initialize(ObjectInspector[] args)
            throws UDFArgumentException {
        if (args.length != 1) {
            throw new UDFArgumentLengthException("ExplodeMap takes only one argument");
        }
        if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {
            throw new UDFArgumentException("ExplodeMap takes string as a parameter");
        }
 
        ArrayList<String> fieldNames = new ArrayList<String>();
        ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
        fieldNames.add("col1");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        fieldNames.add("col2");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
 
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);
    }
 
    @Override
    public void process(Object[] args) throws HiveException {
        String input = args[0].toString();
        String[] test = input.split(";");
        for(int i=0; i<test.length; i++) {
            try {
                String[] result = test[i].split(":");
                forward(result);
            } catch (Exception e) {
                continue;
            }
        }
    }
 }
 
 
 

第二章 YDB基础

一、YDB是什么？
      
YDB全称延云YDB，是一个基于Hadoop分布式架构下的实时的、多维的、交互式的查询、统计、分析引擎，具有万亿数据规模下的秒级性能表现，并具备企业级的稳定可靠表现。
      
YDB是一个细粒度的索引，精确粒度的索引。数据即时导入，索引即时生成，通过索引高效定位到相关数据。YDB与Spark深度集成，Spark对YDB检索结果集直接分析计算，同样场景让Spark性能加快百倍。







二、为探索性分析与即席分析而设计

YDB的即席分析（Ad Hoc）概念的解释
1：当场，就是当场去查询，现场 随意、即兴 查询。
2：响应时间要求为几秒才能称为即席。
即席分析与普通分析的区别
1.普通的应用分析是定制开发的，大多是预先计算好的。
2.即席分析是用户在使用时临时生产的分析，查询条件事先未知，系统无法预先优化这些查询，在现场没法预先准备，所以即席查询的性能也是评估数据仓库的一个重要指标。

三、YDB适合的行业





四、企业级特性：稳定，可靠，易用

 

五、哪些用户适合使用YDB？


1.传统关系型数据，已经无法容纳更多的数据，查询效率严重受到影响的用户。

2.目前在使用SOLR、ES做全文检索，觉得solr与ES提供的分析功能太少，无法完成复杂的业务逻辑，或者数据量变多后SOLR与ES变得不稳定，在掉片与均衡中不断恶性循环，不能自动恢复服务，运维人员需经常半夜起来重启集群的情况。

3.基于对海量数据的分析，但是苦于现有的离线计算平台的速度和响应时间无满足业务要求的用户。

4.需要对用户画像行为类数据做多维定向分析的用户。

5.需要对大量的UGC（User Generate Content）数据进行检索的用户。

6.当你需要在大数据集上面进行快速的，交互式的查询时。

7.当你需要进行数据分析，而不只是简单的键值对存储时。

8.当你想要分析实时产生的数据时。

六、什么情况下不需要用YDB?

1.当数据量可以在MySQL中很轻松的处理时

2.当批量数据集成对你来说已经足够好的情况下

3.当你只需要执行固定的查询时


 

七、在公安系统的-典型的场景







 
 

八、现有大数据技术痛点分析


 

九、主要功能概述


功能



概述



检索过滤



等值匹配,支持 in操作,>,<,>=,<=
and与or的嵌套组合


统计分析



单/多列group by,max,min,sum,avg,count,distinct



复杂SQL



自定义udf,udaf,udtf,SQL多层嵌套,union,join



模糊查询



全文检索,临近搜索,相似文本(文章)搜索, like。


数据类型



string,int,long,float,double
支持一列多值适合一条记录多个标签的存储与检索


中文分词



内嵌二元分词，号码分词，IK词库分词，以及YDB的多元分词。
也可自定义或拓展第三方分词。



十、卓越的性能

1.稽查布控场景性能



2.卓越的检索与分析性能
与Spark txt性能对比（提升倍数）




与ORACLE性能对比



 

3.卓越的排序性能
      
按照时间逆序排序可以说是很多日志系统的硬指标。在延云YDB系统中，我们改变了传统的暴力排序方式，通过索引技术，可以超快对数据进行单列排序，不需要全表暴力扫描，这个技术我们称之为BlockSort，目前支持tlong、tdouble、tint、tfloat四种数据类型。
      
由于BlockSort是借助搜索的索引来实现的，所以采用BlockSort的排序，不需要暴力扫描，性能有大幅度的提升。
      
BlockSort的排序，并非是预计算的方式，可以进行全表进行排序，也可以基于任意的过滤筛选条件进行过滤排序。
      
详细测试地址：http://blog.csdn.net/qq_33160722/article/details/54447022
      
300亿条数据的排序 演示视频  http://blog.csdn.net/qq_33160722/article/details/54834896
 
测试结果(时间单位为秒)

amtint列筛选


筛选后条数


排序方式


YDB
BlockSort


Spark


无筛选


100亿


降序


3.3


1118


升序


3.6


1085


100 TO 900


80亿


降序


1.5


1093


升序


1.3


1070


100 TO 600


50亿


降序


1.53


1104


升序


1.38


867


100 TO 200


10亿


降序


7.00


1115


升序


1.11


1131


100 TO 110


1亿


降序


2.1


1160


升序


3.44


1114


100 TO 101


0.1亿


降序


10.67


1089


升序


7.0


1110

 
 

          
YDB介绍
什么是延云YDB
基于spark排序的一种更廉价的实现方案-附基于spark的性能测试
大索引技术大数据的未来
YDB技术原理
YDB技术原理
那些年我们在spark SQL上踩过的坑
YDB跟Solr Cloud、ElasticSearch区别
YDB安装部署
YDB依赖的硬件环境详解
YDB依赖的操作系统环境详解
YDB依赖的软件环境详解
YDB基于原版Hadoop的部署
YDB基于HDP版本的安装部署
如何升级YDB
YDB使用
使用YDB进行开发-入门篇
使用YDB进行开发-函数篇
使用YDB进行开发-运维篇
使用YDB进行开发-高级篇
YDB业务场景
YDB多维检索与探索性分析
延云YDB车辆分析
延云YDB之OLAP分析指数分析，人群画像
大数据
大数据分析与处理
大数据技术生态圈
世界因大数据而 改变


				
转载于:https://blog.51cto.com/12600738/1905658