Math and Trig

Drill supports the math functions shown in the following table of math functions plus trig functions listed at the end of this section. Most math functions and all trig functions take these input types:

  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • SMALLINT*
  • DECIMAL**

* Not supported. Drill treats SMALLINT as INT when reading from Parquet.
** Drill implicitly casts DECIMAL to DOUBLE for functions that take DOUBLE.

Exceptions are the LSHIFT and RSHIFT functions, which take all types except FLOAT and DOUBLE types. DEGREES, EXP, RADIANS, and the multiple LOG functions take the input types in this list plus the DECIMAL type.

Table of Math Functions

Function Return Type Description
`&`(x, y) Same as input Returns the bitwise AND of x with y.
`|`(x, y) Same as input Returns the bitwise OR of x with y.
`^`(x, y) Same as input Returns the bitwise XOR of x with y.
ABS(x) Same as input Returns the absolute value of the input argument x.
CBRT(x) FLOAT8 Returns the cubic root of x.
CEIL(x) Same as input Returns the smallest integer not less than x.
CEILING(x) Same as input Same as CEIL.
DEGREES(x) FLOAT8 Converts x radians to degrees.
E() FLOAT8 Returns 2.718281828459045.
EXP(x) FLOAT8 Returns e (Euler’s number) to the power of x.
FLOOR(x) Same as input Returns the largest integer not greater than x.
LOG(x) FLOAT8 Returns the natural log (base e) of x.
LOG(x, y) FLOAT8 Returns log base x to the y power.
LOG10(x) FLOAT8 Returns the common log of x.
LSHIFT(x, y) Same as input Shifts the binary x by y times to the left.
MOD(x, y) FLOAT8 Returns the remainder of x divided by y.
NEGATIVE(x) Same as input Returns x as a negative number.
PI FLOAT8 Returns pi.
POW(x, y) FLOAT8 Returns the value of x to the y power.
RADIANS(x) FLOAT8 Converts x degrees to radians.
RAND FLOAT8 Returns a random number from 0-1.
ROUND(x) Same as input Rounds to the nearest integer.
ROUND(x, y) DECIMAL Rounds x to y decimal places.
RSHIFT(x, y) Same as input Shifts the binary x by y times to the right.
SIGN(x) INT Returns the sign of x.
SQRT(x) Same as input Returns the square root of x.
TRUNC(x, y) DOUBLE Truncates x to y decimal places. Specifying y is optional. Default is 0.

Math Function Examples

Examples in this section use the input2.json file. Download the input2.json file from the Drill source code page.

The following snippet of input2.json shows the relevant numeric content:

{ "integer" : 2010,
  "float"   : 17.4,
  "x": {
    "y": "kevin",
    "z": "paul"
. . .
}
{ "integer" : -2002,
  "float"   : -1.2
}
. . .
SELECT `integer` FROM dfs.`/Users/drill/input2.json`;

The output shows integer values not shown in the snippet. You can take a look at all the values in the input2.json file.

|------------|
|  integer   |
|------------|
| 2010       |
| -2002      |
| 2001       |
| 6005       |
|------------|
4 rows selected (0.113 seconds)

Bitwise Logical Function Examples

Note that backticks are required around function names which include special characters.

SELECT `&`(`integer`, 15) as last_nibble FROM dfs.`Users/drill/input2.json`
|-------------|
| last_nibble |
|-------------|
| 10          |
| 14          |
| 1           |
| 5           |
|-------------|
4 rows selected (0.354 seconds)

ABS Example

Get the absolute value of the integer key in input2.json.

SELECT ABS(`integer`) FROM dfs.`/Users/drill/input2.json`;
|------------|
|   EXPR$0   |
|------------|
| 2010       |
| 2002       |
| 2001       |
| 6005       |
|------------|
4 rows selected (0.357 seconds)

CEIL Example

Get the ceiling of float key values in input2.json. The input2.json file contains these float key values:

  • 17.4

  • -1.2

  • 1.2

  • 1.2

SELECT CEIL(`float`) FROM dfs.`/Users/drill/input2.json`;
  |------------|
  |   EXPR$0   |
  |------------|
  | 18.0       |
  | -1.0       |
  | 2.0        |
  | 2.0        |
  |------------|
  4 rows selected (0.647 seconds)

FLOOR Example

Get the floor of float key values in input2.json.

SELECT FLOOR(`float`) FROM dfs.`/Users/drill/input2.json`;
|------------|
|   EXPR$0   |
|------------|
| 17.0       |
| -2.0       |
| 1.0        |
| 1.0        |
|------------|
4 rows selected (0.11 seconds)

ROUND Examples

Open input2.json and change the first float value from 17.4 to 3.14159. Get values of the float columns in input2.json rounded as follows:

  • Rounded to the nearest integer.

  • Rounded to the fourth decimal place.

SELECT ROUND(`float`) FROM dfs.`/Users/drill/input2.json`;
    |------------|
    |   EXPR$0   |
    |------------|
    | 3.0        |
    | -1.0       |
    | 1.0        |
    | 1.0        |
    |------------|
    4 rows selected (0.061 seconds)
SELECT ROUND(`float`, 4) FROM dfs.`/Users/drill/input2.json`;
    |------------|
    |   EXPR$0   |
    |------------|
    | 3.1416     |
    | -1.2       |
    | 1.2        |
    | 1.2        |
    |------------|
    4 rows selected (0.059 seconds)

LOG Examples

Get the base 2 log of 64.

SELECT LOG(2, 64) FROM (VALUES(1));
|------------|
|   EXPR$0   |
|------------|
| 6.0        |
|------------|
1 row selected (0.069 seconds)

Get the common log of 100.

SELECT LOG10(100) FROM (VALUES(1));
|------------|
|   EXPR$0   |
|------------|
| 2.0        |
|------------|
1 row selected (0.203 seconds)

Get the natural log of 7.5.

SELECT LOG(7.5) FROM (VALUES(1));
|---------------------|
|       EXPR$0        |
|---------------------|
| 2.0149030205422647  |
|---------------------|
1 row selected (0.139 seconds)

Trig Functions

Drill supports the following trig functions, which return a FLOAT8 result.

  • SIN(x)
    Sine of angle x in radians

  • COS(x)
    Cosine of angle x in radians

  • TAN(x)
    Tangent of angle x in radians

  • ASIN(x)
    Inverse sine of angle x in radians

  • ACOS(x)
    Inverse cosine of angle x in radians

  • ATAN(x)
    Inverse tangent of angle x in radians

  • SINH()
    Hyperbolic sine of hyperbolic angle x in radians

  • COSH()
    Hyperbolic cosine of hyperbolic angle x in radians

  • TANH()
    Hyperbolic tangent of hyperbolic angle x in radians

Trig Examples

Find the sine and tangent of a 45 degree angle. First convert degrees to radians for use in the SIN() function.

SELECT RADIANS(45) AS Radians FROM (VALUES(1));
|--------------------|
|  Radians           |
|--------------------|
| 0.7853981633974483 |
|--------------------|
1 row selected (0.045 seconds)
SELECT SIN(0.7853981633974483) AS `Sine of 45 degrees` FROM (VALUES(1));
|-----------------------|
|  Sine of 45 degrees   |
|-----------------------|
|  0.7071067811865475   |
|-----------------------|
1 row selected (0.059 seconds)
SELECT TAN(0.7853981633974483) AS `Tangent of 45 degrees` from (VALUES(1));
|-----------------------|
| Tangent of 45 degrees |
|-----------------------|
| 0.9999999999999999    |
|-----------------------|