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.
NOTE
Not all functions in the external documentation are supported.
Math and statistical operators and functions math
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
Returns the absolute value of the input
Returns the inverse cosine value
Returns the estimated cardinality by HyperLogLog++
Returns the approximate percentile value at the given percentage
Returns the inverse sine value
Returns the inverse tangent value
Returns the angle between the positive x-axis plane and the points given by the coordinates
Returns the average value
Returns the cube root
Convert from one base to another
Returns the Pearson coefficient between the numbers
Returns the cosine value
Returns the hyperbolic cosine value
Returns the cotangent value
Returns the rank of a value in a group of values
Returns Euler’s number
Returns e to the power of the value
Returns e to the power of the value minus 1
Returns the factorial of the value
Returns the largest integer not smaller than the value
Returns the largest value of all the parameters
Returns the hypotenuse of the two values given
Returns the kurtosis value from the group
Returns the smallest value of all the parameters
Returns the natural logarithm of the value
Returns the logarithm of the value
Returns the logarithm, in base 10, of the value
Returns the logarithm of the value plus 1
Returns the logarithm, in base 2, of the value
Returns the maximum value of the expression
Returns the mean calculated from the values
Returns the minimum value of the expression
Returns monotonically increasing IDs
Returns the negated value
Returns the percentage ranking of a value
Returns the exact percentile at a given percentage
Returns the approximate percentile at a given percentage
Returns pi
Returns the positive modulo between two values
Returns the positive value
Converts the value to radians
Returns a random number from 0 through 1
Returns a random value
Returns the closest double value
Returns the closest rounded value
Returns sine of the value
Returns hyperbolic sine of the value
Returns the square root of the value
Returns the standard deviation of the value
Returns the population standard deviation of the value
Returns the sample standard deviation of the value
Returns the sum of the values
Returns tangent of the value
Returns hyperbolic tangent of the value
Returns the calculated population variance
Logical operators and functions logical-operators
Operator/Function
Description
Less than
Less than or equal to
Equal to
Greater than
Greater than or equal to
Bitwise exclusive or
Bitwise or
Bitwise not
Returns the common elements
Asserts if the expression is true
If the expression evaluates to true, return the second expression. Otherwise, return the third expression.
If the expression is null, it returns the second expression. Otherwise, it returns the first expression.
Returns true if the first expression is in any of the subsequent expressions.
Returns true if the value is not a number
Returns true if the value is not null
Returns true if the value is null
Returns the first expression if not a number, returns the second expression otherwise
Logical or
When can be used to create branch conditions for comparison
Returns true if the XPath expression evaluates to true or if a matching node is found
Date/time functions datetime-functions
Function
Description
Add months to date
Add days to date
Modify date format
Subtract days from date
Returns the date truncated to the specified unit
Returns the difference between dates in days
Returns the day of the month
Returns the day of week (1-7)
Returns the day of year
Returns date in UNIX® time
Returns date in UTC time
Returns the hour of the input
Returns the last day of the month that the date belongs to
Returns the minute of the input
Returns the month of the input
Number of months between
Returns the first day later than the input
Returns the quarter of the input
Returns the second of the string
Converts the string to a date. Note: The string must be in the format
yyyy-mm-ddTHH24:MM:SS.Converts the string to a timestamp. Note: The string must be in the format
yyyy-mm-ddTHH24:MM:SS.Converts the string to a UNIX® timestamp
Converts the string to a UTC timestamp
Truncates the date
Returns the UNIX® timestamp
Day of the week (0-6)
Returns the week of the year for a given date
Returns the year of the string
Arrays arrays
Function
Description
Creates an array with the given elements
Checks if the array contains the value
Removes duplicate values from the array
Returns an array of the elements in the first array, but not the second
Returns the intersection of the two arrays
Joins two arrays together
Returns the maximum value of the array
Returns the minimum value of the array
Returns the 1-based position of the element
Removes all elements that are equal to the element
Creates an array containing the value counted times
Sorts the array
Joins the array together, without any duplicates
Combines the values of given arrays with the values of the original collection at a given index
Return the size of the array
Return the element at position
Separate elements of array into multiple rows, excluding null
Separate elements of array into multiple rows, including null
Returns the 1 based position of array
Flattens an array of arrays
Separate array of structs into a table, excluding null
Separate array of structs into a table, including null
Separate elements of an array into multiple rows with positions, excluding null
Reverse elements of the array
Return a random permutation of the array
Subsets an array
Sort an array, given an order
Merges the two arrays into a single array, before applying a function
Datatype casting functions datatype-casting
Function
Description
Change the data type to bigint
Change the data type to binary
Change the data type to boolean
Change the data type to the specified type
Change the data type to date
Change the data type to decimal
Change the data type to double
Change the data type to float
Change the data type to int
Change the data type to smallint
Create a map from a string
Change the data type to string
Create a struct
Change the data type to tinyint
Conversion and formatting functions conversion
Function
Description
Return the numeric (ASCII) value
Change the argument to a base64 string
Change the argument to a binary value
Return the bit length
Return the string length
Returns the cyclic redundancy check value
Convert radians to degrees
Change the number’s format
Get data from JSON
Return the hash value
Convert the argument to a hexadecimal value
Changes the string to be title case
Pads the left side of a string
Create a map
Create a map from an array
Create a map from an array of structs
Return the md5 value
Pads the right side of a string
Removes trailing spaces
Return the SHA2 value
Return the soundex code
Separate values into rows
Returns a JSON string
Replace values within string
Remove leading and trailing characters
Convert the base64 string to binary
Convert the hexadecimal to binary
Return a UUID
Data evaluation data-evaluation
Function
Description
Return the first non-null argument
Return a list of non-unique elements
Return a set of unique elements
Concatenation
Concatenation with separator
Returns the total count for rows
Decode using a character set
Encode using a character set
Returns the first value
Indicates if a column is grouped
Returns the level of grouping
Returns a 1-based index of character occurrence
Returns a tuple from a JSON input
Returns the last value
Returns the length of the string
Returns the Levenshtein distance between strings
Concatenate a map
Return a map’s keys
Return a map’s values
Divide rows into partitions
Returns null if true
Returns value if null
Returns value if not null
Extracts part of a URL
Computes rank of a value
Extracts something that matches the regex
Replaces something that matches the regex
Returns a string that repeats
Replace all instances of a string
Create a multi-dimensional rollup
Assigns a unique row number
Returns the schema of the JSON
Splits string into an array of words
Generates an array of elements
Signed bitwise shift left
Signed bitwise shift right
Unsigned bitwise shift right
Return the size of the array
Split string
Return index of substring
Window
Parse XML nodes
Parse XML nodes for double
Parse XML nodes for float
Parse XML nodes for integer
Parse XML nodes for long
Parse XML nodes for short integer
Parse XML nodes for string
Current information current-information
Function
Description
Returns current database
Returns current date
Returns current timestamp
Higher-order functions higher-order
Function
Description
Transform elements in an array
Check if element exists
Filter the input array
Apply a binary operator to all elements
recommendation-more-help
ccf2b369-4031-483f-af63-a93b5ae5e3fb