Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.
This page provides a reference of all Druid SQL functions in alphabetical order. Click the linked function type for documentation on a particular function.
ABS(<NUMERIC>)
Function type: Scalar, numeric
Calculates the absolute value of a numeric expression.
ACOS(<NUMERIC>)
Function type: Scalar, numeric
Calculates the arc cosine of a numeric expression.
ANY_VALUE(<NUMERIC>)
ANY_VALUE(<BOOLEAN>)
ANY_VALUE(<CHARACTER>, <NUMERIC>)
Function type: Aggregation
Returns any value of the specified expression.
APPROX_COUNT_DISTINCT(expr)
Function type: Aggregation
Counts distinct values of a regular column or a prebuilt sketch column.
APPROX_COUNT_DISTINCT_BUILTIN(expr)
Function type: Aggregation
Counts distinct values of a string, numeric, or hyperUnique column using Druid's built-in cardinality or hyperUnique aggregators.
APPROX_COUNT_DISTINCT_DS_HLL(expr, [<NUMERIC>, <CHARACTER>])
Function type: Aggregation
Counts distinct values of an HLL sketch column or a regular column.
APPROX_COUNT_DISTINCT_DS_THETA(expr, [<NUMERIC>])
Function type: Aggregation
Counts distinct values of a Theta sketch column or a regular column.
APPROX_QUANTILE(expr, <NUMERIC>, [<NUMERIC>])
Function type: Aggregation
Deprecated in favor of APPROX_QUANTILE_DS.
APPROX_QUANTILE_DS(expr, <NUMERIC>, [<NUMERIC>])
Function type: Aggregation
Computes approximate quantiles on a Quantiles sketch column or a regular numeric column.
APPROX_QUANTILE_FIXED_BUCKETS(expr, <NUMERIC>, <NUMERIC>, <NUMERIC>, <NUMERIC>, [<CHARACTER>])
Function type: Aggregation
Computes approximate quantiles on fixed buckets histogram column or a regular numeric column.
ARRAY[expr1, expr2, ...]
Function type: Multi-value string
Constructs a SQL ARRAY literal from the expression arguments. The arguments must be of the same type.
ARRAY_AGG([DISTINCT] expr, [<NUMERIC>])
Function type: Aggregation
Returns an array of all values of the specified expression.
ARRAY_CONCAT_AGG([DISTINCT] expr, [<NUMERIC>])
Function type: Aggregation
Concatenates array inputs into a single array.
ASIN(<NUMERIC>)
Function type: Scalar, numeric
Calculates the arc sine of a numeric expression.
ATAN(<NUMERIC>)
Function type: Scalar, numeric
Calculates the arc tangent of a numeric expression.
ATAN2(<NUMERIC>, <NUMERIC>)
Function type: Scalar, numeric
Calculates the arc tangent of the two arguments.
AVG(<NUMERIC>)
Function type: Aggregation
Calculates the average of a set of values.
BIT_AND(expr)
Function type: Aggregation
Performs a bitwise AND operation on all input values.
BIT_OR(expr)
Function type: Aggregation
Performs a bitwise OR operation on all input values.
BIT_XOR(expr)
Function type: Aggregation
Performs a bitwise XOR operation on all input values.
BITWISE_AND(expr1, expr2)
Function type: Scalar, numeric
Returns the bitwise AND between the two expressions, that is, expr1 & expr2.
BITWISE_COMPLEMENT(expr)
Function type: Scalar, numeric
Returns the bitwise NOT for the expression, that is, ~expr.
BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(expr)
Function type: Scalar, numeric
Converts the bits of an IEEE 754 floating-point double value to a long.
BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(expr)
Function type: Scalar, numeric
Converts a long to the IEEE 754 floating-point double specified by the bits stored in the long.
BITWISE_OR(expr1, expr2)
Function type: Scalar, numeric
Returns the bitwise OR between the two expressions, that is, expr1 | expr2.
BITWISE_SHIFT_LEFT(expr1, expr2)
Function type: Scalar, numeric
Returns a bitwise left shift of expr1, that is, expr1 << expr2.
BITWISE_SHIFT_RIGHT(expr1, expr2)
Function type: Scalar, numeric
Returns a bitwise right shift of expr1, that is, expr1 >> expr2.
BITWISE_XOR(expr1, expr2)
Function type: Scalar, numeric
Returns the bitwise exclusive OR between the two expressions, that is, expr1 ^ expr2.
BLOOM_FILTER(expr, <NUMERIC>)
Function type: Aggregation
Computes a Bloom filter from values produced by the specified expression.
BLOOM_FILTER_TEST(expr, <STRING>)
Function type: Scalar, other
Returns true if the expression is contained in a Base64-serialized Bloom filter.
BTRIM(<CHARACTER>, [<CHARACTER>])
Function type: Scalar, string
Trims characters from both the leading and trailing ends of an expression.
CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[ ELSE resultN \] END
Function type: Scalar, other
Returns a result based on a given condition.
CAST(value AS TYPE)
Function type: Scalar, other
Converts a value into the specified data type.
CEIL(<TIMESTAMP> TO <TIME_UNIT>)
Function type: Scalar, date and time
Rounds up a timestamp by a given time unit.
CEIL(<NUMERIC>)
Function type: Scalar, numeric
Calculates the smallest integer value greater than or equal to the numeric expression.
CHAR_LENGTH(expr)
Function type: Scalar, string
Alias for LENGTH.
CHARACTER_LENGTH(expr)
Function type: Scalar, string
Alias for LENGTH.
COALESCE(expr, expr, ...)
Function type: Scalar, other
Returns the first non-null value.
CONCAT(expr, expr...)
Function type: Scalar, string
Concatenates a list of expressions.
CONTAINS_STRING(<CHARACTER>, <CHARACTER>)
Function type: Scalar, string
Finds whether a string is in a given expression, case-sensitive.
COS(<NUMERIC>)
Function type: Scalar, numeric
Calculates the trigonometric cosine of an angle expressed in radians.
COT(<NUMERIC>)
Function type: Scalar, numeric
Calculates the trigonometric cotangent of an angle expressed in radians.
COUNT([DISTINCT] expr)
COUNT(*)
Function type: Aggregation
Counts the number of rows.
CURRENT_DATE
Function type: Scalar, date and time
Returns the current date in the connection's time zone.
CURRENT_TIMESTAMP
Function type: Scalar, date and time
Returns the current timestamp in the connection's time zone.
DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)
Function type: Scalar, date and time
Rounds down a timestamp by a given time unit.
DEGREES(<NUMERIC>)
Function type: Scalar, numeric
Converts an angle from radians to degrees.
DIV(x, y)
Function type: Scalar, numeric
Returns the result of integer division of x by y.
DS_CDF(expr, splitPoint0, splitPoint1, ...)
Function type: Scalar, sketch
Returns a string representing an approximation to the Cumulative Distribution Function given the specified bin definition.
DS_GET_QUANTILE(expr, fraction)
Function type: Scalar, sketch
Returns the quantile estimate corresponding to fraction from a quantiles sketch.
DS_GET_QUANTILES(expr, fraction0, fraction1, ...)
Function type: Scalar, sketch
Returns a string representing an array of quantile estimates corresponding to a list of fractions from a quantiles sketch.
DS_HISTOGRAM(expr, splitPoint0, splitPoint1, ...)
Function type: Scalar, sketch
Returns a string representing an approximation to the histogram given the specified bin definition.
DS_HLL(expr, [lgK, tgtHllType])
Function type: Aggregation
Creates an HLL sketch on a column containing HLL sketches or a regular column.
DS_QUANTILE_SUMMARY(expr)
Function type: Scalar, sketch
Returns a string summary of a quantiles sketch.
DS_QUANTILES_SKETCH(expr, [k])
Function type: Aggregation
Creates a Quantiles sketch on a column containing Quantiles sketches or a regular column.
DS_RANK(expr, value)
Function type: Scalar, sketch
Returns an approximate rank between 0 and 1 of a given value, in which the rank signifies the fraction of the distribution less than the given value.
DS_THETA(expr, [size])
Function type: Aggregation
Creates a Theta sketch on a column containing Theta sketches or a regular column.
EARLIEST(expr)
EARLIEST(expr, maxBytesPerString)
Function type: Aggregation
Returns the value of a numeric or string expression corresponding to the earliest __time value.
EARLIEST_BY(expr, timestampExpr)
EARLIEST_BY(expr, timestampExpr, maxBytesPerString)
Function type: Aggregation
Returns the value of a numeric or string expression corresponding to the earliest time value from timestampExpr.
EXP(<NUMERIC>)
Function type: Scalar, numeric
Calculates e raised to the power of the numeric expression.
EXTRACT(<TIME_UNIT> FROM <TIMESTAMP>)
Function type: Scalar, date and time
Extracts the value of some unit of the timestamp, optionally from a certain time zone, and returns the number.
FLOOR(<TIMESTAMP> TO <TIME_UNIT>)
Function type: Scalar, date and time
Rounds down a timestamp by a given time unit.
FLOOR(<NUMERIC>)
Function type: Scalar, numeric
Calculates the largest integer value less than or equal to the numeric expression.
GREATEST([expr1, ...])
Function type: Scalar, reduction
Returns the maximum value from the provided arguments.
GROUPING(expr, expr...)
Function type: Aggregation
Returns a number for each output row of a groupBy query, indicating whether the specified dimension is included for that row.
HLL_SKETCH_ESTIMATE(expr, [round])
Function type: Scalar, sketch
Returns the distinct count estimate from an HLL sketch.
HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, [numStdDev])
Function type: Scalar, sketch
Returns the distinct count estimate and error bounds from an HLL sketch.
HLL_SKETCH_TO_STRING(expr)
Function type: Scalar, sketch
Returns a human-readable string representation of an HLL sketch.
HLL_SKETCH_UNION([lgK, tgtHllType], expr0, expr1, ...)
Function type: Scalar, sketch
Returns a union of HLL sketches.
HUMAN_READABLE_BINARY_BYTE_FORMAT(value[, precision])
Function type: Scalar, numeric
Converts an integer byte size into human-readable IEC format.
HUMAN_READABLE_DECIMAL_BYTE_FORMAT(value[, precision])
Function type: Scalar, numeric
Converts a byte size into human-readable SI format.
HUMAN_READABLE_DECIMAL_FORMAT(value[, precision])
Function type: Scalar, numeric
Converts a byte size into human-readable SI format with single-character units.
ICONTAINS_STRING(<expr>, str)
Function type: Scalar, string
Finds whether a string is in a given expression, case-insensitive.
IPV4_MATCH(address, subnet)
Function type: Scalar, IP address
Returns true if the address belongs to the subnet literal, else false.
IPV4_PARSE(address)
Function type: Scalar, IP address
Parses address into an IPv4 address stored as an integer.
IPV4_STRINGIFY(address)
Function type: Scalar, IP address
Converts address into an IPv4 address in dot-decimal notation.
LATEST(expr)
LATEST(expr, maxBytesPerString)
Function type: Aggregation
Returns the value of a numeric or string expression corresponding to the latest __time value.
LATEST_BY(expr, timestampExpr)
LATEST_BY(expr, timestampExpr, maxBytesPerString)
Function type: Aggregation
Returns the value of a numeric or string expression corresponding to the latest time value from timestampExpr.
LEAST([expr1, ...])
Function type: Scalar, reduction
Returns the minimum value from the provided arguments.
LEFT(expr, [length])
Function type: Scalar, string
Returns the leftmost number of characters from an expression.
LENGTH(expr)
Function type: Scalar, string
Returns the length of the expression in UTF-16 encoding.
LN(expr)
Function type: Scalar, numeric
Calculates the natural logarithm of the numeric expression.
LOG10(expr)
Function type: Scalar, numeric
Calculates the base-10 of the numeric expression.
LOOKUP(<CHARACTER>, <CHARACTER>)
Function type: Scalar, string
Looks up the expression in a registered query-time lookup table.
LOWER(expr)
Function type: Scalar, string
Returns the expression in lowercase.
LPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])
Function type: Scalar, string
Returns the leftmost number of characters from an expression, optionally padded with the given characters.
LTRIM(<CHARACTER>, [<CHARACTER>])
Function type: Scalar, string
Trims characters from the leading end of an expression.
MAX(expr)
Function type: Aggregation
Returns the maximum value of a set of values.
MILLIS_TO_TIMESTAMP(millis_expr)
Function type: Scalar, date and time
Converts a number of milliseconds since epoch into a timestamp.
MIN(expr)
Function type: Aggregation
Returns the minimum value of a set of values.
MOD(x, y)
Function type: Scalar, numeric
Calculates x modulo y, or the remainder of x divided by y.
MV_APPEND(arr1, expr)
Function type: Multi-value string
Adds the expression to the end of the array.
MV_CONCAT(arr1, arr2)
Function type: Multi-value string
Concatenates two arrays.
MV_CONTAINS(arr, expr)
Function type: Multi-value string
Returns true if the expression is in the array, false otherwise.
MV_FILTER_NONE(expr, arr)
Function type: Multi-value string
Filters a multi-value expression to include no values contained in the array.
MV_FILTER_ONLY(expr, arr)
Function type: Multi-value string
Filters a multi-value expression to include only values contained in the array.
MV_LENGTH(arr)
Function type: Multi-value string
Returns the length of an array expression.
MV_OFFSET(arr, long)
Function type: Multi-value string
Returns the array element at the given zero-based index.
MV_OFFSET_OF(arr, expr)
Function type: Multi-value string
Returns the zero-based index of the first occurrence of a given expression in the array.
MV_ORDINAL(arr, long)
Function type: Multi-value string
Returns the array element at the given one-based index.
MV_ORDINAL_OF(arr, expr)
Function type: Multi-value string
Returns the one-based index of the first occurrence of a given expression.
MV_OVERLAP(arr1, arr2)
Function type: Multi-value string
Returns true if the two arrays have any elements in common, false otherwise.
MV_PREPEND(expr, arr)
Function type: Multi-value string
Adds the expression to the beginning of the array.
MV_SLICE(arr, start, end)
Function type: Multi-value string
Returns a slice of the array from the zero-based start and end indexes.
MV_TO_STRING(arr, str)
Function type: Multi-value string
Joins all elements of the array together by the given delimiter.
NULLIF(value1, value2)
Function type: Scalar, other
Returns NULL if two values are equal, else returns the first value.
NVL(e1, e2)
Function type: Scalar, other
Returns e2 if e1 is null, else returns e1.
PARSE_LONG(<CHARACTER>, [<INTEGER>])
Function type: Scalar, string
Converts a string into a BIGINT with the given base or into a DECIMAL data type if the base is not specified.
POSITION(<CHARACTER> IN <CHARACTER> [FROM <INTEGER>])
Function type: Scalar, string
Returns the one-based index position of a substring within an expression, optionally starting from a given one-based index.
POWER(expr, power)
Function type: Scalar, numeric
Calculates a numerical expression raised to the specified power.
RADIANS(expr)
Function type: Scalar, numeric
Converts an angle from degrees to radians.
REGEXP_EXTRACT(<CHARACTER>, <CHARACTER>, [<INTEGER>])
Function type: Scalar, string
Applies a regular expression to the string expression and returns the _n_th match.
REGEXP_LIKE(<CHARACTER>, <CHARACTER>)
Function type: Scalar, string
Returns true or false signifying whether the regular expression finds a match in the string expression.
REPEAT(<CHARACTER>, [<INTEGER>])
Function type: Scalar, string
Repeats the string expression an integer number of times.
REPLACE(expr, pattern, replacement)
Function type: Scalar, string
Replaces a pattern with another string in the given expression.
REVERSE(expr)
Function type: Scalar, string
Reverses the given expression.
RIGHT(expr, [length])
Function type: Scalar, string
Returns the rightmost number of characters from an expression.
ROUND(expr[, digits])
Function type: Scalar, numeric
Calculates the rounded value for a numerical expression.
RPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])
Function type: Scalar, string
Returns the rightmost number of characters from an expression, optionally padded with the given characters.
RTRIM(<CHARACTER>, [<CHARACTER>])
Function type: Scalar, string
Trims characters from the trailing end of an expression.
SAFE_DIVIDE(x, y)
Function type: Scalar, numeric
Returns x divided by y, guarded on division by 0.
SIN(expr)
Function type: Scalar, numeric
Calculates the trigonometric sine of an angle expressed in radians.
SQRT(expr)
Function type: Scalar, numeric
Calculates the square root of a numeric expression.
STDDEV(expr)
Function type: Aggregation
Alias for STDDEV_SAMP.
STDDEV_POP(expr)
Function type: Aggregation
Calculates the population standard deviation of a set of values.
STDDEV_SAMP(expr)
Function type: Aggregation
Calculates the sample standard deviation of a set of values.
STRING_AGG(expr, separator, [size])
Function type: Aggregation
Collects all values of an expression into a single string.
STRING_FORMAT(pattern[, args...])
Function type: Scalar, string
Returns a string formatted in accordance to Java's String.format method.
STRING_TO_MV(str1, str2)
Function type: Multi-value string
Converts a string into an array, split by the given delimiter.
STRLEN(expr)
Function type: Scalar, string
Alias for LENGTH.
STRPOS(<CHARACTER>, <CHARACTER>)
Function type: Scalar, string
Returns the one-based index position of a substring within an expression.
SUBSTR(<CHARACTER>, <INTEGER>, [<INTEGER>])
Function type: Scalar, string
Alias for SUBSTRING.
SUBSTRING(<CHARACTER>, <INTEGER>, [<INTEGER>])
Function type: Scalar, string
Returns a substring of the expression starting at a given one-based index.
SUM(expr)
Function type: Aggregation
Calculates the sum of a set of values.
TAN(expr)
Function type: Scalar, numeric
Calculates the trigonometric tangent of an angle expressed in radians.
TDIGEST_GENERATE_SKETCH(expr, [compression])
Function type: Aggregation
Generates a T-digest sketch from values of the specified expression.
TDIGEST_QUANTILE(expr, quantileFraction, [compression])
Function type: Aggregation
Returns the quantile for the specified fraction from a T-Digest sketch constructed from values of the expression.
TEXTCAT(<CHARACTER>, <CHARACTER>)
Function type: Scalar, string
Concatenates two string expressions.
THETA_SKETCH_ESTIMATE(expr)
Function type: Scalar, sketch
Returns the distinct count estimate from a Theta sketch.
THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, errorBoundsStdDev)
Function type: Scalar, sketch
Returns the distinct count estimate and error bounds from a Theta sketch.
THETA_SKETCH_INTERSECT([size], expr0, expr1, ...)
Function type: Scalar, sketch
Returns an intersection of Theta sketches.
THETA_SKETCH_NOT([size], expr0, expr1, ...)
Function type: Scalar, sketch
Returns a set difference of Theta sketches.
THETA_SKETCH_UNION([size], expr0, expr1, ...)
Function type: Scalar, sketch
Returns a union of Theta sketches.
TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])
Function type: Scalar, date and time
Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])
Function type: Scalar, date and time
Extracts the value of some unit of the timestamp and returns the number.
TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])
Function type: Scalar, date and time
Rounds down a timestamp by a given time period, optionally from some reference time or timezone.
TIME_FORMAT(<TIMESTAMP>, [<pattern>, [<timezone>]])
Function type: Scalar, date and time
Formats a timestamp as a string.
TIME_IN_INTERVAL(<TIMESTAMP>, <CHARACTER>)
Function type: Scalar, date and time
Returns whether a timestamp is contained within a particular interval, formatted as a string.
TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])
Function type: Scalar, date and time
Parses a string into a timestamp.
TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])
Function type: Scalar, date and time
Shifts a timestamp forwards or backwards by a given number of time units.
TIMESTAMP_TO_MILLIS(<TIMESTAMP>)
Function type: Scalar, date and time
Returns the number of milliseconds since epoch for the given timestamp.
TIMESTAMPADD(<unit>, <count>, <TIMESTAMP>)
Function type: Scalar, date and time
Adds a certain amount of time to a given timestamp.
TIMESTAMPDIFF(<unit>, <TIMESTAMP>, <TIMESTAMP>)
Function type: Scalar, date and time
Takes the difference between two timestamps, returning the results in the given units.
TRIM([BOTH|LEADING|TRAILING] [<chars> FROM] expr)
Function type: Scalar, string
Trims the leading or trailing characters of an expression.
TRUNC(expr[, digits])
Function type: Scalar, numeric
Alias for TRUNCATE.
TRUNCATE(expr[, digits])
Function type: Scalar, numeric
Truncates a numerical expression to a specific number of decimal digits.
UPPER(expr)
Function type: Scalar, string
Returns the expression in uppercase.
VAR_POP(expr)
Function type: Aggregation
Calculates the population variance of a set of values.
VAR_SAMP(expr)
Function type: Aggregation
Calculates the sample variance of a set of values.
VARIANCE(expr)
Function type: Aggregation
Alias for VAR_SAMP.