SQL Syntax for Apache Drill

This Refcard describes version 1.2 of Apache Drill released in October 2015. Apache Drill is an open-source SQL-on-Everything engine. It allows SQL queries to be executed on any kind of data source, ranging from simple CSV files to advanced SQL and NoSQL database servers.

Drill has three distinguishing features:

• Drill can access flat, relational data structures as well as data sources with non-relational structures, such as arrays, hierarchies, maps, nested tables, and complex data types. Besides being able to access these data sources, Drill can run queries that join data across multiple data sources, including non-relational and relational ones.
• Drill allows for schema-less access of data. In other words, it doesn’t need access to the schema definition of the data source. It doesn’t need to know the structure of the tables in advance, nor does it need statistical data. It goes straight for the data. The schema of the query result is therefore not known in advance. It’s built up and derived when data comes back from the data source. During the processing of all the data, the schema of the query result is continuously adapted. For example, the schema of a Hadoop file (or any data source) doesn’t have to be documented in Apache Hive to make it accessible for Drill.
• As with most SQL database servers, Drill doesn’t have its own database. It has been designed and optimized to access other data sources. There is rarely ever a need to copy data from a data source to Hadoop to make it accessible by Drill.

Apache Drill's Supported Plugins for Data Access

• CSV and TSV files
• Hadoop files with Parquet and AVRO file formats, including Amazon S3
• NoSQL databases, such as MongoDB and Apache HBase
• SQL database servers, such as MySQL and Oracle, through an ODBC/JDBC interface
• Files with JSON or BSON data structures
• SQL-on-Hadoop engines, such as Apache Hive and Impala

Note: This Refcard uses several example files (CSV and JSON files) to demonstrate several of Drill's capabilities.

The following webpage contains detailed descriptions on how to install Drill on various platforms: https://drill.apache.org/docs/install-drill-introduction/.

After installation of the software, Drill can be started by using the command-line tool SQLLine:

sqlline.bat -u "jdbc:drill:"

Depending on the platform, the result looks something like this:

DRILL_ARGS - " -u jdbc:drill:"
Calculating Drill classpath...
oct 26, 2015 9:33:46 AM org.glassfish.jersey.server.ApplicationHandler initialize
INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26...
apache drill 1.2.0
"json ain't no thang"
0: jdbc:drill:>

Drill is now ready to query data. To check if it really works, use the following query:

VALUES(CURRENT_DATE);

Result:

+---------------+
| CURRENT_DATE  |
+---------------+
| 2015-10-28    |
+---------------+

To stop Drill enter:

!quit

Create a simple text file with the following text and call it HelloWorld.csv:

HelloWorld!

Query the file:

SELECT * FROM dfs.`\MyDirectory\HelloWorld.csv`;

Result:

+------------------+
|     columns      |
+------------------+
| ["HelloWorld!"]  |
+------------------+

The unique thing about this query is its use of the FROM clause. Instead of a table name, it contains a reference to the file to be accessed. The term dfs represents one of the supported storage plugins. This particular plugin indicates that a file in the local file system is accessed. The storage plugin is followed by a file specification containing the correct directory and the file name. Note that the file specification must be specified in between backticks and not single quotes. The file contains only one line with data, so only one row is returned. Because no column names are specified, the column name columns is used.

Create a file with the following content and call it "Employees.json":

{ "number"  : "6",
  "name"    : "Manzarek",
  "initials": "R",
  "street"  : "Haseltine Lane",
  "town"    : "Phoenix" }
{ "number"  : "8",
  "name"    : "Young",
  "initials": "N",
  "street"  : "Brownstreet",
  "mobile"  : "1234567" }
{ "number"  : "15",
  "name"    : "Metheny",
  "initials": "M",
  "province": "South"
}

Query the file:

SELECT * FROM dfs.`\MyDirectory\Employees.json`;

The JSON data structure is turned into a flat table, and for the missing values in specific columns, the null value is presented. Result:

+---------+-----------+-----------+-----------------+----------+----------+-----------+
| number  |   name    | initials  |     street      |   town   |  mobile  | province  |
+---------+-----------+-----------+-----------------+----------+----------+-----------+
| 6       | Manzarek  | R         | Haseltine Lane  | Phoenix  | null     | null      |
| 8       | Young     | N         | Brownstreet     | null     | 1234567  | null      |
| 15      | Metheny   | M         | null            | null     | null     | South     |
+---------+-----------+-----------+-----------------+----------+----------+-----------+

Many data sources, such as MongoDB, Hadoop with AVRO, and JSON files, contain nested data structures. In relational terminology they would be called columns within columns. To address these nested columns, a specific syntax is introduced. Several examples are used to illustrate this syntax.

Create a file with the following content and call it "EmployeesNested.json":

{ "employee" : {
   "number"  : "6",
   "name"    : { "lastname": "Manzarek",
                 "initials": "R" },
   "address" : { "street"  : "Haseltine Lane",
                 "houseno" : "80",
                 "postcode": "1234KK",
                 "town"    : "Stratford" } }
}
{ "employee" : {
   "number"  : "8",
   "name"    : { "lastname": "Young",
                 "initials": "N" },
   "address" : { "street"  : "Brownstreet",
                 "houseno" : "80",
                 "province": "ZH",
                 "town"    : "Boston" } }
}
{ "employee" : {
   "number"  : "15",
   "name"    : { "lastname": "Metheny",
                 "initials": "M",
                 "code"    : "45" } }
}

Query the file:

SELECT emp.employee.number AS enr, emp.employee.name.lastname AS lastname
FROM   dfs.`\MyDirectory\EmployeesNested.json` AS emp;

Result:

+------+-----------+
| enr  | lastname  |
+------+-----------+
| 6    | Manzarek  |
| 8    | Young     |
| 15   | Metheny   |
+------+-----------+

To refer to the employee numbers the specification emp.employee.number is used. Emp refers to the table name specified in the FROM clause. Specifying this table name is required when dealing with nested tables, otherwise Drill may confuse columns with tables and therefore won’t run the query. Next, employee refers to the first level in the JSON document and number to the next level. To get the lastname of an employee, employee must be specified, followed by name and lastname. Assigning column names to these two result columns is not required, but does make the result easier to read.

Many data sources contain arrays (sometimes called repeating groups). Drill supports the flatten function to transform these arrays into flat, relational data structures. Create a file with the following content (each employee can work for several projects) and call it EmployeesArrays.json:

{ "number"  : "8",
  "projects": [ "ACP3", "FGTR" ]
}
{ "number"  : "15",
  "projects": [ "ACP3", "HHGT", "X456" ]
}

Query the file:

SELECT * FROM dfs.`\MyDirectory\EmployeesArrays.json`;

Result:

+---------+-------------------------+
| number  |        projects         |
+---------+-------------------------+
| 8       | ["ACP3","FGTR"]         |
| 15      | ["ACP3","HHGT","X456"]  |
+---------+-------------------------+

In each row (so for each employee), the projects column contains a set of project values. To see them as separate values, use the flatten function:

SELECT FLATTEN(projects) AS project, enr
FROM   dfs.`\MyDirectory\EnployeesArrays.json`;

In the result each row contains a separate project value and the number of the employee to which the project value belongs:

+----------+------+
| project  | enr  |
+----------+------+
| ACP3     | 8    |
| FGTR     | 8    |
| ACP3     | 15   |
| HHGT     | 15   |
| X456     | 15   |
+----------+------+

Besides flatten, Drill supports two additional functions to work with the contents of arrays: repeated_count (counts the number of values in an array) and repeated_contains (searches for a specific value in an array):

SELECT number AS enr, REPEATED_COUNTS(projects) AS NumberOfProjects,
       REPEATED_CONTAINS(projects,'FGTR') AS ContainsFGTR
FROM   dfs.`\MyDirectory\EmployeesArrays.json` AS emp;

Result:

+------+-------------------+---------------+
| enr  | NumberOfProjects  | ContainsFGTR  |
+------+-------------------+---------------+
| 8    | 2                 | true          |
| 15   | 3                 | false         |
+------+-------------------+---------------+

Individual values can be retrieved from an array:

SELECT number, projects[0], projects[1], projects[2]
FROM   dfs.`\MyDirectory\EmployeesArrays.json` AS emp;

Result:

+---------+---------+---------+---------+
| number  | EXPR$1  | EXPR$2  | EXPR$3  |
+---------+---------+---------+---------+
| 8       | ACP3    | FGTR    | null    |
| 15      | ACP3    | HHGT    | X456    |
+---------+---------+---------+---------+

The kvgen function (Key-Value Generation) transforms query maps that contain keys instead of a schema to arrays. Create a file with the following content and call it "CarOwners.json". This file contains the number of cars that each car owner owns by each manufacturer. The element cars doesn’t have a schema, but instead contains a set of keys (car manufacturer) and each key has a value (number of cars of that particular manufacturer):

{ "owner" : "1",
  "cars"  : { "Ford" : 3,
              "BMW"  : 2,
              "Ferrari" : 1 }
}
{ "owner" : "2",
  "cars"  : { "BMW" : 4,
              "GM"  : 5 }
}

Query the file:

SELECT KVGEN(cars) AS cars FROM dfs.`\MyDirectory\CarOwners.json`;

Result:

+---------------------------------------------------------------------------------+
|                                      cars                                       |
+---------------------------------------------------------------------------------+
| [{"key":"Ford","value":3},{"key":"BMW","value":2},{"key":"Ferrari","value":1}]  |
| [{"key":"BMW","value":4},{"key":"GM","value":5}]                                |
+---------------------------------------------------------------------------------+

The effect of the kvgen function is that the car data inside the cars element is transformed to an array with two elements: key and value. The result of this function can subsequently be processed by the flatten function to turn each element combination into a separate row:

SELECT owner, FLATTEN(KVGEN(cars)) AS cars FROM dfs.`\MyDirectory\CarOwners.json`;

Result:

+--------+------------------------------+
| owner  |             cars             |
+--------+------------------------------+
| 1      | {"key":"Ford","value":3}     |
| 1      | {"key":"BMW","value":2}      |
| 1      | {"key":"Ferrari","value":1}  |
| 2      | {"key":"BMW","value":4}      |
| 2      | {"key":"GM","value":5}       |
+--------+------------------------------+

Apache Drill supports all the query features to be expected from a SQL product. The next example shows how the special functions can be combined with more traditional joins and window functions. Create a file with the following content and call it "EmployeesProjects.json". Each line in this file indicates how many hours an employee has worked on a project on a specific day.

{"enr": "8", "project":"ACP3", "date":"2015-10-01", "hours":"4"}
{"enr": "8", "project":"ACP3", "date":"2015-10-04", "hours":"5"}
{"enr": "8", "project":"FGTR", "date":"2015-10-02", "hours":"2"}
{"enr":"15", "project":"ACP3", "date":"2015-10-01", "hours":"7"}
{"enr":"15", "project":"ACP3", "date":"2015-10-03", "hours":"5"}
{"enr":"15", "project":"HHGT", "date":"2015-10-01", "hours":"4"}
{"enr":"15", "project":"HHGT", "date":"2015-10-05", "hours":"2"}
{"enr":"15", "project":"HHGT", "date":"2015-10-07", "hours":"8"}
{"enr":"15", "project":"X456", "date":"2015-10-01", "hours":"6"}

Join this file with the EmployeesNested file:

SELECT CAST(proj.`date` AS DATE) AS pdate, emp.employee.number AS enr,
       emp.employee.name.lastname AS ename, proj.project, proj.hours,
       SUM(CAST(proj.hours AS INTEGER))
          OVER(PARTITION BY proj.`date`, emp.employee.number) AS sum_hours
FROM   dfs.`\Prive\ARTIKEL\DZone\Examples\EmployeesNested.json` AS emp LEFT OUTER JOIN
       dfs.`\Prive\ARTIKEL\DZone\Examples\EmployeesProjects.json` AS proj
       ON CAST(emp.employee.number AS INTEGER) = CAST(proj.enr AS INTEGER)
ORDER  BY 1, 2;

Result:

+-------------+------+-----------+----------+--------+------------+
|    pdate    | enr  |   ename   | project  | hours  | sum_hours  |
+-------------+------+-----------+----------+--------+------------+
| 2015-10-01  | 15   | Metheny   | ACP3     | 7      | 17         |
| 2015-10-01  | 15   | Metheny   | HHGT     | 4      | 17         |
| 2015-10-01  | 15   | Metheny   | X456     | 6      | 17         |
| 2015-10-01  | 8    | Young     | ACP3     | 4      | 4          |
| 2015-10-02  | 8    | Young     | FGTR     | 2      | 2          |
| 2015-10-03  | 15   | Metheny   | ACP3     | 5      | 5          |
| 2015-10-04  | 8    | Young     | ACP3     | 5      | 5          |
| 2015-10-05  | 15   | Metheny   | HHGT     | 2      | 2          |
| 2015-10-07  | 15   | Metheny   | HHGT     | 8      | 8          |
| null        | 6    | Manzarek  | null     | null   | null       |
+-------------+------+-----------+----------+--------+------------+

This section contains the definitions of the SQL statements supported by Drill related to querying.

This section contains the definitions of the data definition statements supported by Drill.

This section contains the definitions of the statements supported by Drill for working with schemas and sessions.

The following data types are supported by Drill and can be used when converting the data types of values.

This section contains descriptions of all the scalar functions supported by Drill that can be used in any expression.

The processing logic of all the scalar functions can easily be tested by using the VALUES statement. For example, with the following statement the CBRT function can be tested:

VALUES(CBRT(64));

Result:

+---------+
| EXPR$0  |
+---------+
| 4.0     |
+---------+ 

This section contains descriptions of all the aggregate and window functions supported by Drill that can be used in any expression.

The syntax definition for all the window functions in the table below is as follows:

<window_function_name> ( [ ALL ] <expression> )
   OVER ( [ PARTITION BY <expression list> ] [ ORDER BY <order list> ] )

This section contains descriptions of the functions supported by Drill for manipulating arrays and nested data.