Apache Drill Data Types & Operators

Apache Drill uses the ANSI standard SQL to query data. We can use SQL to query data from different data stores such as Hive, HBase, file systems and get the data. Apache Drill decides the form of data during run time. It provides different types of functions and operators to query the nested data.

Apache Drill supports different data types to perform different types of operations. In the below section we will see the Apache Drill data types, data, time and timestamp, operators, etc.


Apache Drill Data Types

The following is the list of Apache Drill data types with their descriptions.

SQL Data Type Description
BIGINT It is an 8-byte signed integer. The range of BIGINT is between -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
BINARY It is a byte string that is of Variable-length.
BOOLEAN It represents the True or the False value.
DATE It represents the date value in the format of YYYY-MM-DD.
DECIMAL(p,s), or DEC(p,s), NUMERIC(p,s)* It represents the 38-digit precision number, in which the precision is represented with p, and the scale is represented with s.
FLOAT It is a 4-byte floating-point number
DOUBLE, DOUBLE PRECISION It is an 8-byte floating-point number that is precision and scalable.
INTEGER or INT It is a 4-byte signed integer. The range of INTEGER or INT is range from 2,147,483,648 to 2,147,483,647.
INTERVAL** It represents the daytime or the year-month interval.
SMALLINT*** It is a 2-byte signed integer. The range of SMALLINT is between 32,768 to 32,767.
TIME It represents the 24-hour based time that comes before or after January 1, 2001, in the format of HH:mm: ss.
TIMESTAMP It represents the JDBC timestamp in the format of year, month, date hour, minute, second, and optional milliseconds and represented as yyyy-MM-dd HH:mm:ss.SSS.
CHARACTER VARYING, CHARACTER, CHAR,** or VARCHAR It is a representation of UTF8-encoded variable-length string which has the default limit of 1 character and max limit goes till 2,147,483,647.

Apache Drill Date, Time, and Timestamp

Apache Drill provides support for Date, Time, and Timestamp data types. We can create a query for date and time data, we just need to cast the text data into date and time that should match the SQL query format.

The below example shows the date, time, timestamp, and interval literals support.

SELECT date '2009-3-26',
       time '12:23:34',
       timestamp '2009-3-26 12:23:34.456',
       interval '1' year, interval '2' day,
       date_add(date '2009-3-26', interval '1 10:20:30' day TO second),
       date_add(date '2011-3-26', 1)
FROM (VALUES (1));


We will start the Apache Drill in embedded mode and run the above query to see the casting of Date, Time, and Timestamp literals.

dril_date_time_timestamp_query_cloudduggu

The above output is generated once the query is executed successfully.


Apache Drill Operators

Apache Drill provides support for different types of operators using them we can perform various types of operations such as Logical Operators, Comparison Operators, Pattern Matching Operators, Math Operators, Subquery Operators, and String Concatenate Operator.

Let us see each Drill operator in the below section.


Logical Operators

The following is the list of Apache Drill logical operators that we can be used in the Drill queries to do the logical operations.

  • AND
  • BETWEEN
  • IN
  • LIKE
  • NOT
  • OR

Comparison Operators

The following is the list of Apache Drill Comparison operators that can be used in the Drill query to perform the comparison operation.

  • <
  • >
  • <=
  • >=
  • =
  • <>
  • IS NULL
  • IS NOT NULL
  • IS FALSE
  • IS NOT FALSE
  • IS TRUE
  • IS NOT TRUE

Math Operators

Apache Drill math operators are used to perform mathematical operations. The below is the list of math operators supported in Drill.

  • + Addition
  • - Subtraction
  • * Multiplication
  • / Division