MapR-DB Format Plugin for Drill

Drill supports access to MapR-DB tables through the maprdb format plugin. The maprdb format enables you to query tables like you would query files in a file system because MapR-DB and MapR-FS share the same namespace. You can query tables stored across multiple directories. You can select from any table in any directory the same way you would select from files in MapR-FS, using the same syntax. Instead of including the path of a file, you include the table path in the query. The user running the query must have read permission to access the MapR table.

MapR-DB stores binary and JSON tables. Binary tables differ from JSON tables in that they store a multi-dimensional map in which both keys and values are a sequence of bytes, and JSON tables store OJAI documents.

JSON tables support rich data types, including complex and repeated types, that enable database servers to evaluate filter conditions for optimized query execution. Binary tables can pose performance limitations because the table columns do not contain the necessary type information.

NOTE: MapR-DB is a case sensitive data source. To ensure that your queries return results, use the case that corresponds to the column names in the JSON tables and views that you query. For example, if you query the “age” column in a JSON table, the query must reference the column as “age” and cannot reference the column as “AGE” or the query will not return results. If you have a dataset where a column name has mixed cases, such as “age” and “AGE,” cleanse the data set so column names consist of one case and then reimport the data into the table to ensure a complete result set when you query the column.

Configuring the MapR-DB Format Plugin

When you install the mapr-drill package, the maprdb format is automatically defined within the default dfs storage plugin configuration to make MapR-DB a consumable data source for Drill. You can access the dfs storage plugin configuration from the Storage tab in the Drill Web Console.

The following sections describe the types of tables that MapR-DB supports, provide examples of Drill queries on each type of table, and show you how to load data into a JSON table from JSON files.