Apache Drill SQL Math And Trig Function

Apache Drill provides supports the following list of math functions to perform the mathematical operation. We can supply and use these functions in the query and get meaningful records.


Apache Drill Math Functions

The Apache Drill Math Function and their description are as below.

Drill Match Function Return Type Description
ABS(a) Same as input This function will return an absolute value based on the argument given through input.
CBRT(a) FLOAT8 This function will return the cubic root of a.
CEIL(a) Same as input This function will return the smallest integer which is not less than the value a.
CEILING(a) Same as input The Function works similarly to the CEIL function.
DEGREES(a) FLOAT8 The Function will convert the radians to degrees.
E() FLOAT8 The Function will return the 2.718281828459045 value.
EXP(a) FLOAT8 The Function will return the Euler’s number to the power of a.
FLOOR(a) Same as input The function will return the largest integer which is less than the a.
LOG(a) FLOAT8 The function will return the natural log (base e) of a.
LOG(a, b) FLOAT8 The function will return log base a to the b power.
LOG10(a) FLOAT8 The function will return the common log of a.
LSHIFT(a, b) Same as input This function will shift the a by b to the left.
MOD(a, b) FLOAT8 This function will return the remainder of a divided by b.
NEGATIVE(a) Same as input This function will return a negative number as a.
PI FLOAT8 This function will return the PI.
POW(a, b) FLOAT8 This function will return the value of a to power b.
RADIANS(a) FLOAT8 This function will convert the degree to radians.
RAND FLOAT8 This function will return the random number that is between 0 to 1.
ROUND(a) Same as input This function will return the rounds of the nearest integer.
ROUND(a, b) DECIMAL This function will return operate by rounding the a to b decimal places.
RSHIFT(a, b) Same as input This function will shift the a by b time on the right side.
SIGN(a) INT This function will return the sign of a.
SQRT(a) Same as input This function will return the square root of a.
TRUNC(a, b) DOUBLE This function will truncate the a to b decimal places.

Apache Drill Math Function Examples

Let us see some of the examples of Apache Drill match functions.

We will start the Apache Drill in an Embedded mode using the below command.

cloudduggu@ubuntu:~/drill$ ./bin/drill-embedded


Apache Drill SQRT Function Examples

The below SQRT function will return the square root of 2.

Command:

apache drill> SELECT SQRT(2);

Output:

drill sort function cloudduggu


Apache Drill LOG Function Examples

The below log function will return the 2 logs of 80.

Command:

apache drill> SELECT LOG(2, 80) FROM (VALUES(1));

Output:

drill log function cloudduggu


Apache Drill LOG10 Function Examples

The below log10 function will return the common logs of 150.

Command:

apache drill> SELECT LOG10(150) FROM (VALUES(1));

Output:

drill log10 function cloudduggu