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.
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.
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.
The following is the list of Apache Drill logical operators that we can be used in the Drill queries to do the logical operations.
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
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