add_months
add_months(start_date, num_months) - Returns the date that is num_months after start_date.
Examples:
SELECT add_months('2016-08-31', 1);
2016-09-30
cast
cast(expr AS type) - Casts the value expr to the target data type type.
Examples:
SELECT cast('10' as int);
10
concat
concat(col1, col2, ..., colN) - Returns the concatenation of col1, col2, ..., colN.
Examples:
SELECT concat('Spark', 'SQL');
SparkSQL
SELECT concat(array(1, 2, 3), array(4, 5), array(6));
[1,2,3,4,5,6]
concat_ws
concat_ws(sep, [str | array(str)]+) - Returns the concatenation of the strings separated by sep.
Examples:
SELECT concat_ws(' ', 'Spark', 'SQL');
Spark SQL
current_date
current_date() - Returns the current date at the start of query evaluation.
current_timestamp
current_timestamp() - Returns the current timestamp at the start of query evaluation.
date
date(expr) - Casts the value expr to the target data type date.
date_add
date_add(start_date, num_days) - Returns the date that is num_days after start_date.
Examples:
SELECT date_add('2016-07-30', 1);
2016-07-31
date_format
date_format(timestamp, fmt) - Converts timestamp to a value of string in the format specified by the date format fmt.
Examples:
SELECT date_format('2016-04-08', 'y');
2016
date_sub
date_sub(start_date, num_days) - Returns the date that is num_days before start_date.
Examples:
SELECT date_sub('2016-07-30', 1);
2016-07-29
datediff
datediff(endDate, startDate) - Returns the number of days from startDate to endDate.
Examples:
SELECT datediff('2009-07-31', '2009-07-30');
1
day
day(date) - Returns the day of month of the date/timestamp.
Examples:
SELECT day('2009-07-30');
30
dayofmonth
dayofmonth(date) - Returns the day of month of the date/timestamp.
Examples:
SELECT dayofmonth('2009-07-30');
30
dayofweek
dayofweek(date) - Returns the day of the week for date/timestamp (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
Examples:
SELECT dayofweek('2009-07-30');
5
dayofyear
dayofyear(date) - Returns the day of year of the date/timestamp.
Examples:
SELECT dayofyear('2016-04-09');
100
explode
explode(expr) - Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns.
Examples:
SELECT explode(array(10, 20));
10
20
from_unixtime
from_unixtime(unix_time, format) - Returns unix_time in the specified format.
Examples:
SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
1970-01-01 00:00:00
if
if(expr1, expr2, expr3) - If expr1 evaluates to true, then returns expr2; otherwise returns expr3.
Examples:
SELECT if(1 < 2, 'a', 'b');
a
left
left(str, len) - Returns the leftmost len(len can be string type) characters from the string str,if len is less or equal than 0 the result is an empty string.
Examples:
SELECT left('Spark SQL', 3);
Spa
length
length(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.
Examples:
SELECT length('Spark SQL ');
10
map
map(key0, value0, key1, value1, ...) - Creates a map with the given key/value pairs.
Examples:
SELECT map(1.0, '2', 3.0, '4');
{1.0:"2",3.0:"4"}
nvl(expr1, expr2) - Returns expr2 if expr1 is null, or expr1 otherwise.
Examples:
SELECT nvl(NULL, array('2'));
["2"]
nvl2
nvl2(expr1, expr2, expr3) - Returns expr2 if expr1 is not null, or expr3 otherwise.
Examples:
SELECT nvl2(NULL, 2, 1);
1
rank
rank() - Computes the rank of a value in a group of values. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values will produce gaps in the sequence.
regexp_extract
regexp_extract(str, regexp[, idx]) - Extracts a group that matches regexp.
Examples:
SELECT regexp_extract('100-200', '(\d+)-(\d+)', 1);
100
regexp_replace
regexp_replace(str, regexp, rep) - Replaces all substrings of str that match regexp with rep.
Examples:
SELECT regexp_replace('100-200', '(\d+)', 'num');
num-num
replace
replace(str, search[, replace]) - Replaces all occurrences of search with replace.
Arguments:
str - a string expression
search - a string expression. If search is not found in str, str is returned unchanged.
replace - a string expression. If replace is not specified or is an empty string, nothing replaces the string that is removed from str.
Examples:
SELECT replace('ABCabc', 'abc', 'DEF');
ABCDEF
row_number
row_number() - Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition.
split
split(str, regex) - Splits str around occurrences that match regex.
Examples:
SELECT split('oneAtwoBthreeC', '[ABC]');
["one","two","three",""]
substr
substr(str, pos[, len]) - Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.
Examples:
SELECT substr('Spark SQL', 5);
k SQL
to_date
to_date(date_str[, fmt]) - Parses the date_str expression with the fmt expression to a date. Returns null with invalid input. By default, it follows casting rules to a date if the fmt is omitted.
Examples:
SELECT to_date('2009-07-30 04:17:52');
2009-07-30
SELECT to_date('2016-12-31', 'yyyy-MM-dd');
2016-12-31
to_timestamp
to_timestamp(timestamp[, fmt]) - Parses the timestamp expression with the fmt expression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted.
Examples:
SELECT to_timestamp('2016-12-31 00:12:00');
2016-12-31 00:12:00
SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
2016-12-31 00:00:00
unix_timestamp
unix_timestamp([expr[, pattern]]) - Returns the UNIX timestamp of current or specified time.
Examples:
SELECT unix_timestamp();
1476884637
SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
1460041200
weekday
weekday(date) - Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday).
Examples:
SELECT weekday('2009-07-30');
3
weekofyear
weekofyear(date) - Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.
Examples:
SELECT weekofyear('2008-02-20');
8
