Spark SQL示例用法所有函数示例权威详解二 点击这里可看全文
标量函数
标量函数是每行返回一个单值的函数,与聚合函数相对应,后者返回一组行的值。Spark SQL支持各种内置标量函数。它还支持用户定义的标量函数。
Spark SQL提供了两种函数功能来满足广泛的用户需求:内置函数和用户定义函数(UDFs)。内置函数是Spark SQL预定义的常用例程,完整的函数列表可以在内置函数API文档中找到。当系统的内置函数无法完成所需任务时,用户可以使用UDF来定义自己的函数。
内置函数 Spark SQL有一些常用的内置函数类别,用于聚合、数组/映射、日期/时间戳和JSON数据处理。本小节介绍这些函数的用法和描述。
条件函数
函数 | 描述 |
---|---|
coalesce(expr1, expr2, …) | 如果存在第一个非空参数,则返回第一个非空参数。否则,返回 null。 |
if(expr1, expr2, expr3) | 如果 expr1 为真,则返回 expr2 ;否则返回 expr3 。 |
ifnull(expr1, expr2) | 如果 expr1 为空,则返回 expr2 ;否则返回 expr1 。 |
nanvl(expr1, expr2) | 如果 expr1 不是 NaN,则返回 expr1 ;否则返回 expr2 。 |
nullif(expr1, expr2) | 如果 expr1 等于 expr2 ,则返回 null;否则返回 expr1 。 |
nvl(expr1, expr2) | 如果 expr1 为空,则返回 expr2 ;否则返回 expr1 。 |
nvl2(expr1, expr2, expr3) | 如果 expr1 不为空,则返回 expr2 ;否则返回 expr3 。 |
CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4]* [ELSE expr5] END | 如果 expr1 为真,则返回 expr2 ;如果 expr3 为真,则返回 expr4 ;否则返回 expr5 。 |
示例
-- coalesce
SELECT coalesce(NULL, 1, NULL);
+-----------------------+
|coalesce(NULL, 1, NULL)|
+-----------------------+
| 1|
+-----------------------+
-- if
SELECT if(1 < 2, 'a', 'b');
+-------------------+
|(IF((1 < 2), a, b))|
+-------------------+
| a|
+-------------------+
-- ifnull
SELECT ifnull(NULL, array('2'));
+----------------------+
|ifnull(NULL, array(2))|
+----------------------+
| [2]|
+----------------------+
-- nanvl
SELECT nanvl(cast('NaN' as double), 123);
+-------------------------------+
|nanvl(CAST(NaN AS DOUBLE), 123)|
+-------------------------------+
| 123.0|
+-------------------------------+
-- nullif
SELECT nullif(2, 2);
+------------+
|nullif(2, 2)|
+------------+
| NULL|
+------------+
-- nvl
SELECT nvl(NULL, array('2'));
+-------------------+
|nvl(NULL, array(2))|
+-------------------+
| [2]|
+-------------------+
-- nvl2
SELECT nvl2(NULL, 2, 1);
+----------------+
|nvl2(NULL, 2, 1)|
+----------------+
| 1|
+----------------+
-- when
SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 > 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 1.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
+-----------------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 > 0) THEN 2.0 ELSE 1.2 END|
+-----------------------------------------------------------+
| 2.0|
+-----------------------------------------------------------+
SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
+--------------------------------------------------+
|CASE WHEN (1 < 0) THEN 1 WHEN (2 < 0) THEN 2.0 END|
+--------------------------------------------------+
| NULL|
+--------------------------------------------------+
位运算函数
函数 | 描述 |
---|---|
expr1 & expr2 | 返回 expr1 和 expr2 的按位与结果。 |
expr1 ^ expr2 | 返回 expr1 和 expr2 的按位异或结果。 |
bit_count(expr) | 返回参数 expr 中设置为1的位数,作为无符号64位整数。如果参数为空,则返回 NULL。 |
bit_get(expr, pos) | 返回指定位置上的位(0或1)的值。位置从右到左编号,从零开始。位置参数不能为负数。 |
getbit(expr, pos) | 返回指定位置上的位(0或1)的值。位置从右到左编号,从零开始。位置参数不能为负数。 |
shiftright(base, expr) | 按位(带符号)向右移动。 |
shiftrightunsigned(base, expr) | 按位(无符号)向右移动。 |
expr1 | expr2 | 返回 expr1 和 expr2 的按位或结果。 |
~ expr | 返回 expr 的按位取反结果。 |
示例
-- &
SELECT 3 & 5;
+-------+
|(3 & 5)|
+-------+
| 1|
+-------+
-- ^
SELECT 3 ^ 5;
+-------+
|(3 ^ 5)|
+-------+
| 6|
+-------+
-- bit_count
SELECT bit_count(0);
+------------+
|bit_count(0)|
+------------+
| 0|
+------------+
-- bit_get
SELECT bit_get(11, 0);
+--------------+
|bit_get(11, 0)|
+--------------+
| 1|
+--------------+
SELECT bit_get(11, 2);
+--------------+
|bit_get(11, 2)|
+--------------+
| 0|
+--------------+
-- getbit
SELECT getbit(11, 0);
+-------------+
|getbit(11, 0)|
+-------------+
| 1|
+-------------+
SELECT getbit(11, 2);
+-------------+
|getbit(11, 2)|
+-------------+
| 0|
+-------------+
-- shiftright
SELECT shiftright(4, 1);
+----------------+
|shiftright(4, 1)|
+----------------+
| 2|
+----------------+
-- shiftrightunsigned
SELECT shiftrightunsigned(4, 1);
+------------------------+
|shiftrightunsigned(4, 1)|
+------------------------+
| 2|
+------------------------+
-- |
SELECT 3 | 5;
+-------+
|(3 | 5)|
+-------+
| 7|
+-------+
-- ~
SELECT ~ 0;
+---+
| ~0|
+---+
| -1|
+---+
CSV函数
函数 | 描述 |
---|---|
from_csv(csvStr, schema[, options]) | 使用给定的 csvStr 和 schema 返回一个 struct 值。 |
schema_of_csv(csv[, options]) | 返回 CSV 字符串的 DDL 格式中的模式。 |
to_csv(expr[, options]) | 使用给定的 struct 值返回一个 CSV 字符串。 |
示例
- from_csv
SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
+----------------+
|from_csv(1, 0.8)|
+----------------+
| {
1, 0.8}|
+----------------+
SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
+--------------------+
|from_csv(26/08/2015)|
+--------------------+
|{
2015-08-26 00:00...|
+--------------------+
-- schema_of_csv
SELECT schema_of_csv('1,abc');
+--------------------+
|schema_of_csv(1,abc)|
+--------------------+
|STRUCT<_c0: INT, ...|
+--------------------+
-- to_csv
SELECT to_csv(named_struct('a', 1, 'b', 2));
+--------------------------------+
|to_csv(named_struct(a, 1, b, 2))|
+--------------------------------+
| 1,2|
+--------------------------------+
SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
+----------------------------------------------------------------+
|to_csv(named_struct(time, to_timestamp(2015-08-26, yyyy-MM-dd)))|
+----------------------------------------------------------------+
| 26/08/2015|
+----------------------------------------------------------------+
谓词函数
函数 | 描述 |
---|---|
! expr | 逻辑非。 |
expr1 < expr2 | 如果 expr1 小于 expr2 ,则返回 true。 |
expr1 <= expr2 | 如果 expr1 小于或等于 expr2 ,则返回 true。 |
expr1 <=> expr2 | 对于非空操作数,返回与 EQUAL(=) 运算符相同的结果,但如果两个操作数都为 null,则返回 true,否则返回 false。 |
expr1 = expr2 | 如果 expr1 等于 expr2 ,则返回 true;否则返回 false。 |
expr1 == expr2 | 如果 expr1 等于 expr2 ,则返回 true;否则返回 false。 |
expr1 > expr2 | 如果 expr1 大于 expr2 ,则返回 true。 |
expr1 >= expr2 | 如果 expr1 大于或等于 expr2 ,则返回 true。 |
expr1 and expr2 | 逻辑与。 |
str ilike pattern[ ESCAPE escape] | 如果 str 不区分大小写地与 pattern 匹配,则返回 true;如果任何参数为空,则返回 null;否则返回 false。 |
expr1 in(expr2, expr3, …) | 如果 expr 等于任何一个 valN,则返回 true。 |
isnan(expr) | 如果 expr 是 NaN,则返回 true;否则返回 false。 |
isnotnull(expr) | 如果 expr 不为空,则返回 true;否则返回 false。 |
isnull(expr) | 如果 expr 为空,则返回 true;否则返回 false。 |
str like pattern[ ESCAPE escape] | 如果 str 与 pattern 匹配,则返回 true;如果任何参数为空,则返回 null;否则返回 false。 |
not expr | 逻辑非。 |
expr1 or expr2 | 逻辑或。 |
regexp(str, regexp) | 如果 str 与 regexp 匹配,则返回 true;否则返回 false。 |
regexp_like(str, regexp) | 如果 str 与 regexp 匹配,则返回 true;否则返回 false。 |
rlike(str, regexp) | 如果 str 与 regexp 匹配,则返回 true;否则返回 false。 |
示例
-- !
SELECT ! true;
+----------+
|(NOT true)|
+----------+
| false|
+----------+
SELECT ! false;
+-----------+
|(NOT false)|
+-----------+
| true|
+-----------+
SELECT ! NULL;
+----------+
|(NOT NULL)|
+----------+
| NULL|
+----------+
-- <
SELECT 1 < 2;
+-------+
|(1 < 2)|
+-------+
| true|
+-------+
SELECT 1.1 < '1';
+---------+
|(1.1 < 1)|
+---------+
| false|
+---------+
SELECT to_date('2009-07-30 04:17:52') < to_date('2009-07-30 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) < to_date(2009-07-30 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') < to_date('2009-08-01 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) < to_date(2009-08-01 04:17:52))|
+-------------------------------------------------------------+
| true|
+-------------------------------------------------------------+
SELECT 1 < NULL;
+----------+
|(1 < NULL)|
+----------+
| NULL|
+----------+
-- <=
SELECT 2 <= 2;
+--------+
|(2 <= 2)|
+--------+
| true|
+--------+
SELECT 1.0 <= '1';
+----------+
|(1.0 <= 1)|
+----------+
| true|
+----------+
SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-07-30 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) <= to_date(2009-07-30 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-08-01 04:17:52');
+--------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) <= to_date(2009-08-01 04:17:52))|
+--------------------------------------------------------------+
| true|
+--------------------------------------------------------------+
SELECT 1 <= NULL;
+-----------+
|(1 <= NULL)|
+-----------+
| NULL|
+-----------+
-- <=>
SELECT 2 <=> 2;
+---------+
|(2 <=> 2)|
+---------+
| true|
+---------+
SELECT 1 <=> '1';
+---------+
|(1 <=> 1)|
+---------+
| true|
+---------+
SELECT true <=> NULL;
+---------------+
|(true <=> NULL)|
+---------------+
| false|
+---------------+
SELECT NULL <=> NULL;
+---------------+
|(NULL <=> NULL)|
+---------------+
| true|
+---------------+
-- =
SELECT 2 = 2;
+-------+
|(2 = 2)|
+-------+
| true|
+-------+
SELECT 1 = '1';
+-------+
|(1 = 1)|
+-------+
| true|
+-------+
SELECT true = NULL;
+-------------+
|(true = NULL)|
+-------------+
| NULL|
+-------------+
SELECT NULL = NULL;
+-------------+
|(NULL = NULL)|
+-------------+
| NULL|
+-------------+
-- ==
SELECT 2 == 2;
+-------+
|(2 = 2)|
+-------+
| true|
+-------+
SELECT 1 == '1';
+-------+
|(1 = 1)|
+-------+
| true|
+-------+
SELECT true == NULL;
+-------------+
|(true = NULL)|
+-------------+
| NULL|
+-------------+
SELECT NULL == NULL;
+-------------+
|(NULL = NULL)|
+-------------+
| NULL|
+-------------+
-- >
SELECT 2 > 1;
+-------+
|(2 > 1)|
+-------+
| true|
+-------+
SELECT 2 > 1.1;
+-------+
|(2 > 1)|
+-------+
| true|
+-------+
SELECT to_date('2009-07-30 04:17:52') > to_date('2009-07-30 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) > to_date(2009-07-30 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT to_date('2009-07-30 04:17:52') > to_date('2009-08-01 04:17:52');
+-------------------------------------------------------------+
|(to_date(2009-07-30 04:17:52) > to_date(2009-08-01 04:17:52))|
+-------------------------------------------------------------+
| false|
+-------------------------------------------------------------+
SELECT 1 > NULL;
+----------+
|(1 > NULL)|
+----------+
| NULL|
+----------+
-- >=
SELECT 2 >= 1;
+--------+
|(2 >= 1)|
+--------+
| true|
+--------+
SELECT 2.0 >= '2.1';
+------------+
|(2.0 >= 2.1)|
+------------+
| false