The SQL Relational Model

Drill takes on a daunting challenge: to infer schemas from a variety of file formats and map those schemas into the SQL relational model. We use the term schema inference for this process. (Drill itself does not use this term; it is borrowed from Apache Spark.) Making Drill work for you entails understanding the strengths (and limitations) of Drill’s schema inference mechanism. The relational model describes a set of relations (tables) comprising a fixed set of domains (columns). Each column is described by a name and a type. (Columns often also have a defined position, as we will see shortly for CSV files.)

Drill uses JSON as its reference model. Although JSON can represent relational data, it also can represent additional data structures such as arrays and maps. Drill extends the SQL relational model to include these types. Because SQL itself does not support these extended types, Drill provides a number of techniques to bridge the gap between JSON and SQL.

Relational databases use a schema-on-write approach to store data in a predefined format. The big data world uses schema-on-read, but in two distinct ways.

Hive and Impala define a schema separately from the data, with the schema stored in the Hive metastore. When data is ambiguous, the Hive schema instructs each tool how to interpret the data. The cost, however, is that the user must maintain this schema, which becomes a complex task for large deployments and is awkward during data exploration. Drill can work with Hive data and the Hive metastore when available, but it can also work without them.

To work without a predefined schema, Drill takes a pragmatic approach: the schema is defined as a negotiation between the query and the file. For example, the same JSON file can be thought of as having typed fields or text fields. The same CSV file can be thought of as containing a single column (an array of VARCHAR) or as a set of columns. Yesterday’s version of a JSON file might have had four fields per record; today’s has six after an enhancement to the source of the data.

Data Life Cycle: Data Exploration to Production

When you start a new data project, you are presented with a collection of files, perhaps in a variety of formats. Drill’s schema inference rules allow you to do data exploration quickly and easily, directly on the source data. Because that data might have an ambiguous structure, part of the exploration is to identify the issues and workarounds.

As you work, you can capture what you learn in SQL views. With views, you can rename columns, clean up formats, convert data types, and more.

Then, as a project moves into production, Drill encourages you to extract, transform, and load (ETL) your data into partitioned Parquet files. Parquet carries a clear schema, avoiding schema ambiguities. Parquet is also fast to read, especially if a query reads just a subset of columns. Partitioning data reduces the number of files that must be read to produce a result, further speeding up the query. (Think of partitioning as big data’s answer to a table index.)

SELECT * FROM cp.`employee.json` LIMIT 20

SELECT * FROM cp.`tpch/partsupp.parquet` LIMIT 20

Schema Inference

Whether during data exploration or in a production system, Drill uses the same schema inference steps. Inference starts with the type of storage system, then proceeds to the type of file and to how to split up a file for reading. As the file is read, Drill must infer the type of each column, then determine how to combine schemas from different files and blocks within a file. Much of this is automatic, especially with Parquet files.

When doing data exploration with other kinds of files, proper inference often requires that you either set things up correctly within Drill, or write your query to resolve ambiguities. The next few sections walk through the overall process so that you know how the pieces work and how to resolve issues when things don’t go as expected.

Further, because Drill is distributed, each fragment of a query (parallel execution path) makes its own independent decision about the schema. That is, fragments do not communicate to negotiate a shared view of the schema. Instead, each fragment infers the schema from the first record that it reads (or from metadata stored in the particular file that it reads for CSV or Parquet files).

The key issue to keep in mind when using Drill is this: Drill cannot predict the future. That is, Drill cannot choose types based on what might appear in the file in the future. Further, when running a distributed query, one scan operator cannot predict what another scan will infer about the schema.

Storage Plug-ins

Drill is extensible. It accesses your data via a storage plug-in. By now you are familiar with storage plug-ins for distributed filesystems HBase, Hive, and so on. Here, we focus on the dfs plug-in that provides access to files on your local system, in HDFS, in MapR–FS, in Amazon S3, and so on.

Elsewhere in this book, for convenience, we refer to data sources such as HDFS, the local filesystem, Amazon S3, and MapR as different storage plug-ins. Truth be told, they are all just different configurations of the same dfs plug-in. Internally, dfs uses the HDFS client to work with the other storage formats. This means that Drill can work with any format for which an HDFS client implementation is available, including Isilon, Alluxio, Microsoft ADSL, and many more. Apache Drill does not test these add-on formats, but if they adhere to the HDFS standard, they have a good chance of working in Drill.

Storage Configurations

A storage plug-in is a piece of Java code, built into Drill or delivered as a JAR. To use a storage plug-in, you must define a storage configuration for the plug-in.

Confusingly, the Drill UI uses the terms “storage” and “storage plug-in” for the storage configuration. Some people use the term “storage plug-in configuration” for the same idea. Just remember that if you edit it in the Drill Web Console, it is a “configuration.” If you write code in Java, it is a “plug-in.”

Each storage plug-in can have multiple configurations. For example, you might have different storage configurations for the dfs plug-in depending on your needs. You create the storage configuration in the Drill web console. When run as a daemon, Drill stores the configurations in ZooKeeper so that they are visible to all Drillbits in your cluster. When run embedded in SQLLine (or in unit tests), the configurations are saved to local disk and are generally discarded at the end of the run.

Each storage configuration is given a name when you create the configuration. This is the name you use in queries. Each configuration also has a type, which connects the configuration to the plug-in implementation in code. For example, the dfs storage configuration that Drill ships with maps to the file storage plug-in, as shown here:

{
 "type": "file",

You can use the Web Console to create a second storage configuration for the file plug-in using the Storage tab. Drill provides no default; it is handy to copy and paste an existing JSON definition.

The default dfs configuration points to your local filesystem and is handy for learning Drill. Change this to point to HDFS or Amazon S3 for a production system.

There is nothing magic about the dfs name, by the way. Feel free to instead create a local configuration for your local filesystem, and an hdfs or s3 configuration for your distributed storage.

Here is how to create the example local storage configuration we’ll use in this section:

1. Start Drill, as explained earlier.

2. Start the Drill web console, as explained earlier.

3. Click the Storage tab.

4. Find the default dfs plug-in, and then click Update.

5. Select all the JSON text and copy it to the clipboard.

6. Click Back.

7. In the New Storage Plugin area, enter the name local, and then click Create.

8. Paste the JSON into the editor.

9. Replace the contents of the workspaces object with the following:

   "workspaces": {},

10. Click Update.

Workspaces

File storage configurations provide several properties to customize behavior. The storage configuration by itself refers to the root of the target filesystem. The local configuration you just defined points to the root of your local filesystem: that’s what the file:// in the configuration means.

The GitHub repositiory for this book contains a data directory that contains the files used in this section. Suppose your download these files to your home directory in /Users/arina/drillbook. You can query the files using the following:

SELECT * FROM `local`.`/Users/arina/drillbook/data/drill/cust.csv`

The backticks are sometimes optional, but they are required if a name contains a character that is not a valid symbol character in SQL or if the name is the same as a SQL keyword. The simplest rule is to always enclose schema, table, and column names with backticks.

Using an absolute path is often unwieldy, however. You might have test and production filesets. If you share local queries with others, they will need to edit the file paths to work for their machines before running the queries. And data might move around in HDFS, for various reasons.

To avoid this issue, Drill defines the idea of a workspace, which is just a named subdirectory. So, you might define a data workspace for the book’s data directory:

1. In the Drill Web Console, click the Storage tab.

2. Find the local configuration that you created earlier, and click Update.

3. Edit the JSON to revise the workspaces element as shown here:

   "workspaces": {
      "data": {
          "location": "/Users/arina/drillbook",
          "writable": true,
          "defaultInputFormat": null,
          "allowAccessOutsideWorkspace": false
       }
   }
   

   Replace the path in the preceding example with the location where you stored the book files.

4. Click Update.

Then, you can reference the workspace in a query:

SELECT * FROM `local`.`data`.`cust.csv`

In SQL terms, the configuration (with optional workspace) is like a database (MySQL) or schema (Oracle). The end result is that, either way, Drill now knows the location of the file that you want to query.

Querying Directories

If you have multiple files in a directory, you can use the directory name as a table name in a query. Drill will query all files within that directory. Suppose that you have a directory cust containing multiple customer CSV files. Here’s how to query all of the files within that directory:

SELECT * FROM `local`.`data`.`cust`

For this to work, the files must be of the same type (all CSV, for example). However, you cannot query the workspace directly. That is, both of the following are invalid:

SELECT * FROM `local`.`data`  -- Not legal
SELECT * FROM `local`.`data`.`/` -- Not legal

Thus, you need to define the workspace to point one level above your data directory.

Workspaces are particularly useful when working with partitioned data, as explained in “Partitioning Data Directories”.

Default Schema

Drill supports the SQL USE command to declare a default schema. To try this, start a local Drill:

cd $DRILL_HOME
bin/sqlline -u jdbc:drill:drillbit=localhost

Now you can use local as your default schema:

USE `local`;
SELECT * FROM `/Users/arina/drillbook/data/cust.csv`;

Both storage configurations and workspaces can become the default schema:

USE `local`.`data`;
SELECT * FROM `cust.csv`;

The default workspace is a session property: it remains in effect as long as you are connected to Drill and must be defined on each new connection. If you have authentication enabled, the Web Console is also stateful and USE (along with ALTER SESSION) will work. Without authentication, the Web Console is stateless and the USE statement is in effect only for the single request.

Format Plug-ins and Format Configuration

Storage plug-ins have two parts: the plug-in (code) and configuration (JSON). Format plug-ins follow this same pattern. In Drill, file formats are extensible; we describe how to write your own format plug-in in Chapter 12.

Storage configurations appear in the Storage section of the UI. However, format plug-ins appear as part of some dfs-based storage plug-in: be it local, hdfs, s3, or similar. (Format plug-ins are supported only for the dfs storage plug-in, other plug-ins do not support format plug-ins.)

Format configurations allows us to fine-tune file formats for different types of data files, as we discuss in a few moments. You will see the default set of format configurations when you use the Drill Web Console to update a storage configuration as we did previously. If you delete a format configuration from a storage configuration, that file type won’t be available within that configuration. Similarly, if you write or obtain a new format plug-in, you must add a format configuration to your storage configuration in order to use the new format.

Drill ships with a default set of format configurations in the default dfs storage configuration. Here is one example:

"csv": {
   "type": "text",
   "extensions": [
      "csv"
    ],
    "delimiter": ","
},

The name csv is purely for documentation; Drill does not use it. The important bits are what follows. The type names the underlying format plug-in—text, in this case. The text format plug-in is actually quite flexible and is used to define the psv and tsv formats as well. What makes this a CSV format are the property values. The extensions field identifies the file suffix (extension) used for this format. The delimiter field specifies that this is a comma-separated file.

You can customize these as needed, often by creating a custom storage configuration to hold your custom format configurations. Just be sure to give each a unique name and to map each to a distinct file suffix (more on this shortly).

Drill uses the file suffix to associate the file with a format configuration. It then uses the format configuration to locate the underlying format plug-in.

Format Inference

We can now assemble the all of this information to describe how Drill picks a format configuration for each file. Suppose that you have this query:

SELECT * FROM `local`.`data`.`cust.csv`

Drill proceeds according to the following steps:

1. Look up the schema local to find the storage configuration.

2. Use the type field in the storage configuration to find the storage plug-in implementation.

3. Use the next name, data, to find a workspace within the configuration.

4. Use the workspace to find the working directory for the query.

5. Use the working directory to find the target file(s).

6. Use the file suffix (in the query or by scanning a directory of files) to find the associated format configuration.

7. Use the type of the format configuration to find the format plug-in implementation. The format plug-in provides the reader to use to deserialize the data, as discussed in a few moments.

Drill now has all the information it needs to perform the file scan: the filesystem, the directory that contains the file, the name of the file to scan, and the format of the file.

Knowing this format inference process can be handy when things go wrong. (Drill’s error messages are, to put it kindly, cryptic, so you must often figure out the problem yourself.)

File Format Variations

Files come in many varied formats. CSV files are notorious for having many standards (with or without headers, quoted or nonquoted strings, allowing multiline field values or not, etc.). As we saw, CSV files themselves are one variation of a more general “text” format with other variations such as pipe-separated values (PSV), tab-separated values (TSV), and so on.

The format configurations allow you to fine-tune the scan to your exact file format. This works well as long as each format variation has its own distinct file suffix: .csv for CSV files with a header, .psv for pipe-separated values, .tsv for tab-separated values, and so on.

However, because CSV has so much variation, you might find a situation in which some of your CSV files have headers whereas others do not, yet both variations end with .csv. To handle this, store the different file formats in different directories and define a storage configuration (not just workspace) for each. So, you might pull your store sales files into a stores directory and define a stores storage configuration for that directory. Then, in the stores configuration, you can configure the .csv extension to refer to a CSV format configuration that includes headers.

The general rule is that if files have distinct formats that require distinct format configurations, you must give them distinct suffixes. If you cannot do that, you must store them in separate directories, referenced by distinct storage configurations.

Schema Inference for Delimited Data

Schema inference is the process of creating a relational schema for each file, or file block, as Drill reads it. Schema inference is only as good as the data itself. If the data contains sufficient hints, Drill can do an excellent job of inferring the schema. But certain data patterns that we will encounter later in the chapter can run into the limitations of schema inference. The general rule of thumb is that schema inference cannot predict the future; it can only react to data as it is read.

As noted earlier, Drill infers schemas in two main ways:

• From the data itself (specifically, from the first record of the file or file block)

• From metadata stored in the same file as the data, as in Parquet

We will explore schema inference from the simplest to the most complex cases using some of the file formats most commonly used with Drill.

All of the sample files used here reside in the book’s GitHub repository. We begin by setting the GitHub repository’s data folder as the default schema:

USE `local`.`data`;

"csvh": {
    "type": "text",
    "extensions": [
    "csvh"
    ],
    "extractHeader": true,
    "delimiter": ","
}

custId,name,balance,status
123,Fred,456.78
125,Betty,98.76,VIP
128,Barney,1.23,PAST DUE,30

SELECT * FROM `csvh/cust.csvh`;

+---------+---------+----------+-----------+
| custId  |  name   | balance  |  status   |
+---------+---------+----------+-----------+
| 123     | Fred    | 456.78   |           |
| 125     | Betty   | 98.76    | VIP       |
| 128     | Barney  | 1.23     | PAST DUE  |
+---------+---------+----------+-----------+

123,Fred,456.78,

SELECT CustId, Name, Address FROM `csvh/cust.csvh`;

+---------+---------+----------+
| CustId  |  Name   | Address  |
+---------+---------+----------+
| 123     | Fred    |          |
| 125     | Betty   |          |
| 128     | Barney  |          |
+---------+---------+----------+

SELECT typeof(custId) AS custId_type FROM `csvh/cust.csvh` LIMIT 1;

+--------------+
| custId_type  |
+--------------+
| VARCHAR      |
+--------------+

SELECT typeof(custId) as custId_type, typeof(address) AS addr_type
FROM `csvh/cust.csvh` LIMIT 1;

+--------------+------------+
| custId_type  | addr_type  |
+--------------+------------+
| VARCHAR      | VARCHAR    |
+--------------+------------+

SELECT CAST(custID AS INT) AS custId, name,
    CAST(balance AS FLOAT) AS balance
FROM `csvh/cust.csvh`;

+---------+---------+----------+
| custId  |  name   | balance  |
+---------+---------+----------+
| 123     | Fred    | 456.78   |
| 125     | Betty   | 98.76    |
| 128     | Barney  | 1.23     |
+---------+---------+----------+

SELECT sqlTypeOf(custId) As custId_type,
       modeOf(custId) AS custId_mode,
       sqlTypeOf(balance) AS bal_type,
       modeOf(balance) AS bal_mode
FROM (
  SELECT CAST(custID AS INT) AS custId,
         name, CAST(balance AS FLOAT) AS balance
  FROM `csvh/cust.csvh`)
LIMIT 1;

+--------------+--------------+-----------+-----------+
| custId_type  | custId_mode  | bal_type  | bal_mode  |
+--------------+--------------+-----------+-----------+
| INTEGER      | NOT NULL     | FLOAT     | NOT NULL  |
+--------------+--------------+-----------+-----------+

SELECT AVG(custId) AS avgId, SUM(balance) totalBal
FROM (
  SELECT CAST(custID AS INT) AS custId,
         name, CAST(balance AS FLOAT) AS balance
  FROM `csvh/cust.csvh`)

+---------------------+--------------------+
|        avgId        |      totalBal      |
+---------------------+--------------------+
| 125.33333333333333  | 556.7700009346008  |
+---------------------+--------------------+

Schema Inference for JSON

The Drill project website notes that Drill uses JSON as its native data model. That is, Drill can support (a subset of) the same structures that JSON does:

• Scalar types

• Null values (unlike JSON, Drill’s nulls are typed)

• Lists

• Maps

In practice JSON is much more expressive than Drill, for the simple reason that JSON can express arbitrary tree-structured data, but Drill must coerce all input into an extended relational structure.

{"column1": 10, "column2": "foo"}

SELECT * from `json/quoted.json`;

+----------+----------+
| column1  | column2  |
+----------+----------+
| 10       | foo      |
+----------+----------+

{column1: 10, column2: "foo"}

SELECT * from `json/unquoted.json`;

+----------+----------+
| column1  | column2  |
+----------+----------+
| 10       | foo      |
+----------+----------+

{a: 10, A: 20}

SELECT * FROM `json/ambig.json`;

+-----+
|  a  |
+-----+
| 20  |
+-----+

Ambiguous Numeric Schemas

JSON has only one numeric type: number. But Drill has multiple numeric types, and chooses a type for the field based on the first record. In general, if the first value contains a decimal point, Drill chooses DOUBLE; otherwise, it chooses BIGINT.

Because Drill is more strict than JSON, the “can’t predict the future” rule can surprise the unwary. Suppose we have the following JSON file (json/int-float.json):

{a: 10}
{a: 10.1}

A query against this table fails:

SELECT * FROM `json/int-float.json`;
Error: INTERNAL_ERROR ERROR: You tried to write a Float8 type when
  you are using a ValueWriter of type NullableBigIntWriterImpl.

The problem is that Drill infers the type BIGINT from the first value and then fails because the second value is not a valid BIGINT. You cannot fix this problem in the query because the problem occurs during read, long before your query statements take effect.

Drill provides a solution, albeit one that’s a bit awkward. Like many SQL engines, Drill lets you customize various settings at runtime. You can make a change for just the current login session using ALTER SESSION. Or, if you have admin rights, you can change the setting for all users in all sessions using ALTER SYSTEM. Because changing a system setting might cause queries to behave differently, be very cautious before making such system-level changes. Here, we’ll make all the changes for just the one session; the options will go back to their default values in the next session.

If your file has pathological cases like this, you can read all numbers as DOUBLE:

ALTER SESSION SET `store.json.read_numbers_as_double` = true;

The query will now work fine because Drill is given a hint to use the DOUBLE type for numbers, so there is no conflict. The awkward bit is that you must remember to set the option before each query of the troublesome file, and you must remember to reset it afterward:

ALTER SESSION RESET `store.json.read_numbers_as_double`;

Some query tools don’t provide a convenient way to issue such statements. Further, you cannot encapsulate such statements in a view. As a result, it is much better to avoid creating such ambiguous files in the first place.

{a: 10}
{a: "20"}

SELECT * FROM `json/int-str.json`;
Error: INTERNAL_ERROR ERROR: You tried to write a VARCHAR type when you are using 
a ValueWriter of type NullableFloat8WriterImpl.

ALTER SESSION SET `store.json.all_text_mode` = true;
SELECT * FROM `json/int-str.json`;

+-----+
|  a  |
+-----+
| 10  |
| 20  |
+-----+

SELECT typeof(a) AS a_type FROM `json/int-str.json` LIMIT 1;

+----------+
|  a_type  |
+----------+
| VARCHAR  |
+----------+

SELECT CAST(a AS BIGINT) AS a FROM `json/int-str.json`;
+-----+
|  a  |
+-----+
| 10  |
| 20  |
+-----+

{custId: 123, name: "Fred", balance: 123.45}
{custId: 125, name: "Barney"}

SELECT * FROM `json/missing2.json`;

+---------+---------+----------+
| custId  |  name   | balance  |
+---------+---------+----------+
| 123     | Fred    | 123.45   |
| 125     | Barney  | null     |
+---------+---------+----------+

{a: 0}
{a: 1}
{a: 2, b: "hello there!"}

SELECT a, b FROM `json/missing3.json` WHERE b IS NOT NULL ORDER BY a;

+----+---------------+
| a  |       b       |
+----+---------------+
| 2  | hello there!  |
+----+---------------+

SELECT a, b FROM `gen/70kmissing.json`
WHERE b IS NOT NULL ORDER BY a;

Error: UNSUPPORTED_OPERATION ERROR:
  Schema changes not supported in External Sort.
  Please enable Union type.
Previous schema BatchSchema [fields=[[`a` (BIGINT:OPTIONAL)],
  [`b` (INT:OPTIONAL)]], selectionVector=NONE]
Incoming schema BatchSchema [fields=[[`a` (BIGINT:OPTIONAL)],
  [`b` (FLOAT8:OPTIONAL)]], selectionVector=NONE]

SELECT a, b,
       sqlTypeOf(b) AS b_type, modeof(b) AS b_mode
FROM `gen/70kmissing.json`
WHERE mod(a, 70000) = 1;

+--------+-------+----------+-----------+
|   a    |   b   |  b_type  |  b_mode   |
+--------+-------+----------+-----------+
| 1      | null  | INTEGER  | NULLABLE  |
| 70001  | 10.5  | DOUBLE   | NULLABLE  |
+--------+-------+----------+-----------+

SELECT a, CAST(b AS DOUBLE) AS b
FROM `gen/70kmissing.json`
WHERE b IS NOT NULL ORDER BY a;

+--------+-------+
|   a    |   b   |
+--------+-------+
| 70001  | 10.5  |
+--------+-------+

{a: null}
{a: null}

SELECT a FROM `json/all-null.json`;

+-------+
|   a   |
+-------+
| null  |
| null  |
+-------+

SELECT sqlTypeOf(a) AS a_type, modeOf(a) AS a_mode 
FROM `json/all-null.json` LIMIT 1;

+----------+-----------+
|  a_type  |  a_mode   |
+----------+-----------+
| INTEGER  | NULLABLE  |
+----------+-----------+

ALTER SESSION SET `store.format` = 'json';
CREATE TABLE `out/json-null` AS SELECT * FROM `json/null2.json`;

{
  "custId" : 123,
  "name" : "Fred",
  "balance" : 123.45
} {
  "custId" : 125,
  "name" : "Barney"
}

JSON Lists in Drill

JSON provides a list type (json/int-list.json):

{a: [10, 20, 30]}

Querying this data produces the following results:

SELECT * FROM `json/int-list.json`;

+-------------+
|      a      |
+-------------+
| [10,20,30]  |
+-------------+

SELECT sqlTypeOf(a) AS a_type, modeOf(a) AS a_mode
FROM `json/int-list.json`;

+---------+---------+
| a_type  | a_mode  |
+---------+---------+
| BIGINT  | ARRAY   |
+---------+---------+

In JSON, a list is not a list of numbers or list of strings, it is simply a list of values, and the values can be of any type. That is, the following is perfectly fine JSON (json/mixed-list.json):

{a: [10, "foo", {b: 30}, ["fred", "barney"]]}

Again, Drill must pull a relational structure out of the JSON data, and it cannot do so for heterogeneous lists.¹

Sometimes, clever programmers use JSON arrays as a record format: just list the values in order without the overhead of name/value pairs when using objects. However, Drill cannot read a JSON array that contains mixed scalar types (json/scalar-list.json):

{a: [ 123, "Fred", 123.45 ] }

Here’s what happens when you query the file:

SELECT * FROM `json/scalar-list.json`;
Error: UNSUPPORTED_OPERATION ERROR: In a list of type BIGINT,
 encountered a value of type VARCHAR. Drill does not support lists of different types.

The workaround is to use all_text_mode:

ALTER SESSION SET `store.json.all_text_mode` = true;
SELECT * FROM `json/scalar-list.json`;

+--------------------------+
|            a             |
+--------------------------+
| ["123","Fred","123.45"]  |
+--------------------------+

Now you can pull out the fields and cast them to the proper type:

SELECT * FROM (
  SELECT CAST(a[0] AS INT) AS custId,
         a[1] AS name,
         CAST(a[2] AS DOUBLE) AS balance
  FROM `json/scalar-list.json`);

+---------+-------+----------+
| custId  | name  | balance  |
+---------+-------+----------+
| 123     | Fred  | 123.45   |
+---------+-------+----------+

ALTER SESSION RESET `store.json.all_text_mode`;

The general rule in Drill is that if a column is represented by a JSON list, all the values in the list must be of the same type. This allows Drill to infer the type of the list as one of the types discussed earlier, but with repeated cardinality. Using all_text_mode is a workaround, but it has the fiddly issues discussed earlier.

JSON lists can include null values (json/int-null-list.json):

{a: [10, null, 30]}

However, Drill does not allow null values in lists. JSON data with nulls in lists will cause the query to fail:

SELECT * FROM `json/int-null-list.json`;
Error: UNSUPPORTED_OPERATION ERROR:
  Null values are not supported in lists by default.
  Please set `store.json.all_text_mode` to true
  to read lists containing nulls. Be advised that this will treat
  JSON null values as a string containing the word 'null'.

Here, the message helpfully explains the workaround:

ALTER SESSION SET `store.json.all_text_mode` = true;
SELECT * FROM `json/int-null-list.json`;

+---------------------+
|          a          |
+---------------------+
| ["10","null","30"]  |
+---------------------+

ALTER SESSION RESET `store.json.all_text_mode`;

In this case, there is no good way to convert the text fields to numbers short of turning them into top-level fields.

JSON allows a list to be null. Just as Drill does not differentiate between missing and null scalar values in JSON, it does not differentiate between missing, empty, and null lists. Consider this file (json/null-list.json):

{a: 1, b: [10, 20]}
{a: 2, b: null}
{a: 3, b: []}
{a: 4}

If you query the file, you get these results:

SELECT * FROM `json/null-list.json`;

+----+----------+
| a  |    b     |
+----+----------+
| 1  | [10,20]  |
| 2  | []       |
| 3  | []       |
| 4  | []       |
+----+----------+

The “can’t predict the future” rule applies to lists too. Here’s another example (json/empty-str-list.json):

{a: []}
{a: ["Fred", "Barney"]}

A query on this file will work if all of the data fits into one batch. But if the empty array fills one batch, Drill must guess a type for that batch. It will guess array of INT and then will fail when the second batch tries to convert VARCHAR values to INT values. For example:

SELECT * FROM `gen/70Kempty.json`;
Error: UNSUPPORTED_OPERATION ERROR: In a list of type INT, 
encountered a value of type VARCHAR. 
Drill does not support lists of different types.

The workaround is our old friend, all text mode:

ALTER SESSION SET `store.json.all_text_mode` = true;
SELECT * FROM `gen/70Kempty.json` WHERE a=70001;

+--------+--------------------+
|   a    |         b          |
+--------+--------------------+
| 70001  | ["Fred","Barney"]  |
+--------+--------------------+

Drill allows lists to contain other lists, a so-called repeated list. A list can also contain maps, which is a repeated map. In these cases, the use of all-text mode will not work to handle long runs of empty lists. Drill will guess text, but when the first non-null value appears and is an array or a map, Drill reports an error. The rule is that if the array is nonscalar, it must begin with a non-null value, or Drill will be unable to resolve the ambiguity. Another alternative is to do an ETL of the data into Parquet, which provides metadata to resolve the ambiguity.

JSON Summary

In summary, the following rules apply to JSON:

• JSON keys become Drill column names.

• JSON types map to specific Drill types.

• Drill can handle columns with list or object values, with certain restrictions.

• JSON object keys are case sensitive, but Drill column names are case insensitive.

To get the best results with Drill, take care to produce JSON that maps cleanly to a relational representation by doing the following:

• Avoid null values in the first record.

• For each column, avoid missing values early in the file.

• Lists must be homogeneous with no null values.

• Avoid empty lists in the first record.

• Ensure that floating-point numbers always include a decimal point.

• Avoid including some column in one set of files, but excluding it from another set.

A workaround for some but not all of these issues is to enable all text mode for the JSON table in the JSON format plug-in configuration, which will read all scalar columns as nullable VARCHAR and all scalar arrays as repeated VARCHAR. The query can then perform its own type conversions as demonstrated earlier.

Schema Evolution in Parquet

Suppose that we have a directory, parquetDir, that contains two Parquet files: file1 and file2. file1 has a single column a of type Integer. file2 has two columns: the same column a, but also column b of type String.

As explained for JSON, if we do an explicit SELECT:

SELECT a, b FROM `parquetDir` ORDER BY b

Drill will fail for the same reasons explained earlier. From this we can see that we have to plan schema evolution carefully, even for Parquet. After we have a collection of Parquet files, we must plan how to handle the situation when columns are added to or removed from the files from which we create our Parquet files. Our ETL process that creates Parquet files should be rerun to re-create older files when we find the need to insert or remove columns with a type other than nullable INT. (Or, we should reprocess the older files to add or remove the columns in question.)

Defining a Table Workspace

The partition directories and files within the directories are essentially a table. Hive, in fact, defines the entire structure as a table and automatically works out how to scan the data. Because it uses no external schema, Drill is not aware of the table concept. The closest we can get in Drill is to simulate tables using directory structures. Suppose that you want to define multiple partitioned tables: sales, returns, promotions, and so on. You begin by defining a top-level directory with subdirectories for each table:

retail
|- sales
|- returns
|- promotions

Each of the child directories is partitioned, say by date. Next, you declare a workspace, and then you can reference your tables within that schema:

SELECT * FROM `retail`.`sales` LIMIT 10

When you use a directory name (only) in a query, Drill assumes that you want to scan all files in that directory and all its children. If you use a directory pattern (with wildcards), Drill assumes that you want all files in the selected directories.

Drill requires that all files in a single FROM element be of the same type and have the same suffix. In this case, perhaps all the sales files are in Parquet format.

If the directories contain multiple file types, you can use wildcards to pick out a single type.

Capturing Schema Mapping in Views

You have seen various ways in which you can use session options and query constructs to work around ambiguities in various kinds of data files. After you work out the proper solutions, it makes sense that you would like to reuse them for future queries. Views are Drill’s solution to such reuse.

You will have noticed that several of the examples are written in such a way that the grunt work is handled by a subquery. This is not an accident: the subquery can be reused as a view.

Unfortunately, Drill cannot also encapsulate session options in a view. This means that if you must use all text mode, say, to handle schema ambiguities, the users of your view must also set all text mode before using the view and remember to reset the mode afterward.

This is important if you want to run such queries in a business intelligence tool such as Tableau. In such a tool, you might not have the luxury of turning session options on and off around each query, especially if the queries are automated as part of a dashboard or other structure. Your best bet in such cases is to convert the data to Parquet and then query the converted data.

Running Challenging Queries in Scripts

Another handy approach is to run challenging queries (those that require specific session options) in a script. You can then use SQLLine to run the script, which sets up the required session options and then runs the query.

SQLLine has a number of command-line options that you can see using the -h option:

$DRILL_HOME/bin/sqlline -h

Here’s the partial output:

Usage: java sqlline.SqlLine
...
 --showHeader=[true/false]   show column names in query results
 --numberFormat=[pattern]    format numbers using DecimalFormat
                             pattern
 --maxWidth=MAXWIDTH         the maximum width of the terminal
 --silent=[true/false]       be more silent
 --outputformat=[table/vertical/csv/tsv] 
                             format mode for result display
 --run=/path/to/file         run one script and then exit

What if you wanted to run a single query to sum the sales for 2017? You might create a script file such as the following:

USE `local`.`data`;
SELECT SUM(CAST(amount AS DOUBLE)) FROM `sales/2017-*`;

You would do so like this (assuming a single Drillbit on localhost):

$DRILL_HOME/bin/sqlline 
   -u jdbc:drill:drillbit=localhost 
   --run=/full/path/to/countSales.sql 
   --silent=true 
   --outputformat=csv 
   --numberFormat=##,###.00

Here’s what the results look like:

'ok','summary'
'true','Default schema changed to [local.data]'
'EXPR$0'
'10,914.00'

Although not perfect, you could apply a bit of text editing to pull out the lines that you want, ignoring the unwanted output from the USE command. You can extend this technique to add ALTER SESSION commands and to use scripting to pass parameters into the query. You can do something similar in Java using the Drill JDBC driver, or in Python as shown in Chapter 7.