Spark SQL functions
You can use several built-in Spark SQL functions to extend SQL functionality with Adobe Experience Platform Query Service. This document lists the Spark SQL functions that are supported by Query Service.
For more detailed information about the functions, including their syntax, usage, and examples, read the Spark SQL function documentation.
NOTENot all functions in the external documentation are supported.
Math and statistical operators and functions
| Operator/Function | Description |
|---|---|
% | Returns the remainder of the two numbers |
* | Multiplies the two numbers |
+ | Adds the two numbers |
- | Subtracts the two numbers |
/ | Divides the two numbers |
abs | Returns the absolute value of the input |
acos | Returns the inverse cosine value |
approx_count_distinct | Returns the estimated cardinality by HyperLogLog++ |
approx_percentile | Returns the approximate percentile value at the given percentage |
asin | Returns the inverse sine value |
atan | Returns the inverse tangent value |
atan2 | Returns the angle between the positive x-axis plane and the points given by the coordinates |
avg | Returns the average value |
cbrt | Returns the cube root |
ceil or ceiling | Returns the smallest integer not larger than the inputted value |
conv | Convert from one base to another |
corr | Returns the Pearson coefficient between the numbers |
cos | Returns the cosine value |
cosh | Returns the hyperbolic cosine value |
cot | Returns the cotangent value |
dense_rank | Returns the rank of a value in a group of values |
e | Returns Euler’s number |
exp | Returns e to the power of the value |
expm1 | Returns e to the power of the value minus 1 |
factorial | Returns the factorial of the value |
floor | Returns the largest integer not smaller than the value |
greatest | Returns the largest value of all the parameters |
hypot | Returns the hypotenuse of the two values given |
kurtosis | Returns the kurtosis value from the group |
least | Returns the smallest value of all the parameters |
ln | Returns the natural logarithm of the value |
log | Returns the logarithm of the value |
log10 | Returns the logarithm, in base 10, of the value |
log1p | Returns the logarithm of the value plus 1 |
log2 | Returns the logarithm, in base 2, of the value |
max | Returns the maximum value of the expression |
mean | Returns the mean calculated from the values |
min | Returns the minimum value of the expression |
monotonically_increasing_id | Returns monotonically increasing IDs |
negative | Returns the negated value |
percent_rank | Returns the percentage ranking of a value |
percentile | Returns the exact percentile at a given percentage |
percentile_approx | Returns the approximate percentile at a given percentage |
pi | Returns pi |
pmod | Returns the positive modulo between two values |
positive | Returns the positive value |
pow, power | Returns the first value to the power of the second value |
radians | Converts the value to radians |
rand | Returns a random number from 0 through 1 |
randn | Returns a random value |
rint | Returns the closest double value |
round | Returns the closest rounded value |
sign, signum | Returns the number’s sign |
sin | Returns sine of the value |
sinh | Returns hyperbolic sine of the value |
sqrt | Returns the square root of the value |
stddev | Returns the standard deviation of the value |
sttdev_pop | Returns the population standard deviation of the value |
stddev_samp | Returns the sample standard deviation of the value |
sum | Returns the sum of the values |
tan | Returns tangent of the value |
tanh | Returns hyperbolic tangent of the value |
var_pop | Returns the calculated population variance |
var_samp, variance | Returns the calculated sample variance |
Logical operators and functions
| Operator/Function | Description |
|---|---|
! or not | Logical not |
< | Less than |
<= | Less than or equal to |
= | Equal to |
> | Greater than |
>= | Greater than or equal to |
^ | Bitwise exclusive or |
| | Bitwise or |
~ | Bitwise not |
arrays_overlap | Returns the common elements |
assert_true | Asserts if the expression is true |
if | If the expression evaluates to true, return the second expression. Otherwise, return the third expression. |
ifnull | If the expression is null, it returns the second expression. Otherwise, it returns the first expression. |
in | Returns true if the first expression is in any of the subsequent expressions. |
isnan | Returns true if the value is not a number |
isnotnull | Returns true if the value is not null |
isnull | Returns true if the value is null |
nanvl | Returns the first expression if not a number, returns the second expression otherwise |
or | Logical or |
when | When can be used to create branch conditions for comparison |
xpath_boolean | Returns true if the XPath expression evaluates to true or if a matching node is found |
Date/time functions
| Function | Description |
|---|---|
add_months | Add months to date |
date_add | Add days to date |
date_format | Modify date format |
date_sub | Subtract days from date |
date_trunc | Returns the date truncated to the specified unit |
datediff | Returns the difference between dates in days |
day, dayofmonth | Returns the day of the month |
dayofweek | Returns the day of week (1-7) |
dayofyear | Returns the day of year |
from_unixtime | Returns date in UNIX® time |
from_utc_timestamp | Returns date in UTC time |
hour | Returns the hour of the input |
last_day | Returns the last day of the month that the date belongs to |
minute | Returns the minute of the input |
month | Returns the month of the input |
months_between | Number of months between |
next_day | Returns the first day later than the input |
quarter | Returns the quarter of the input |
second | Returns the second of the string |
to_date | Converts the string to a date. Note: The string must be in the format yyyy-mm-ddTHH24:MM:SS. |
to_timestamp | Converts the string to a timestamp. Note: The string must be in the format yyyy-mm-ddTHH24:MM:SS. |
to_unix_timestamp | Converts the string to a UNIX® timestamp |
to_utc_timestamp | Converts the string to a UTC timestamp |
trunc | Truncates the date |
unix_timestamp | Returns the UNIX® timestamp |
weekday | Day of the week (0-6) |
weekofyear | Returns the week of the year for a given date |
year | Returns the year of the string |
Arrays
| Function | Description |
|---|---|
array | Creates an array with the given elements |
array_contains | Checks if the array contains the value |
array_distinct | Removes duplicate values from the array |
array_except | Returns an array of the elements in the first array, but not the second |
array_intersect | Returns the intersection of the two arrays |
array_join | Joins two arrays together |
array_max | Returns the maximum value of the array |
array_min | Returns the minimum value of the array |
array_position | Returns the 1-based position of the element |
array_remove | Removes all elements that are equal to the element |
array_repeat | Creates an array containing the value counted times |
array_sort | Sorts the array |
array_union | Joins the array together, without any duplicates |
arrays_zip | Combines the values of given arrays with the values of the original collection at a given index |
cardinality | Return the size of the array |
element_at | Return the element at position |
explode | Separate elements of array into multiple rows, excluding null |
explode_outer | Separate elements of array into multiple rows, including null |
find_in_set | Returns the 1 based position of array |
flatten | Flattens an array of arrays |
inline | Separate array of structs into a table, excluding null |
inline_outer | Separate array of structs into a table, including null |
posexplode | Separate elements of an array into multiple rows with positions, excluding null |
reverse | Reverse elements of the array |
shuffle | Return a random permutation of the array |
slice | Subsets an array |
sort_array | Sort an array, given an order |
zip_with | Merges the two arrays into a single array, before applying a function |