Drill Options for the maprdb Format Plugin

You can enable certain Drill options for the maprdb format plugin from the Options page in the Drill Web UI or from the command line using the SET and ALTER SYSTEM commands.

To enable the options from the Drill Web UI, go to http(s)://<drill-hostname-or-ip-address>:8047, and select Options in the menu bar. Alternatively, enable options from the command line using the SET or ALTER SYSTEM commands, as shown:

SET `store.hive.maprdb_json.optimize_scan_with_native_reader` = true;

You can enable the following Drill options for the maprdb format plugin:

store.hive.maprdb_json.optimize_scan_with_native_reader: Starting in Drill 1.14 (MEP 6.0), enable the store.hive.maprdb_json.optimize_scan_with_native_reader option if you want Drill to use the native Drill reader to read Hive MapR-DB JSON tables. When you enable the native Drill reader, Drill typically performs faster reads of data and applies filter pushdown optimizations.
store.hive.maprdb_json.read_timestamp_with_timezone_offset: Starting in Drill 1.16, you can enable Drill to read timestamp values with a timezone offset when the hive plugin is used and the Drill native MaprDB JSON reader is enabled through the store.hive.maprdb_json.optimize_scan_with_native_reader option.

Important: Internally, Drill stores timestamp values in UTC format, for example 2018-01-01T20:12:12.123Z. When you enable the timezone offset option, select on a table returns different timestamp values. If you filter on timestamp values when this option is enabled, you must include the new timestamp value in the filter condition. For example, look at the timestamp values when the store.hive.maprdb_json.read_timestamp_with_timezone_offset option is disabled (set to ‘false’):

select * from dfs.`/tmp/timestamp`;  
-------------------------------------------------------
_id        datestring	          datetimestamp
-------------------------------------------------------
1	2018-01-01 12:12:12.123	2018-01-01 20:12:12.123
2	9999-12-31 23:59:59.999	10000-01-01 07:59:59.999
-------------------------------------------------------

When the option is enabled (set to 'true'), you can see the difference in the timestamp values returned:

select * from dfs.`/tmp/timestamp`;  
------------------------------------------------------
_id     datestring	        datetimestamp
------------------------------------------------------
1	2018-01-01 12:12:12.123    2018-01-01 12:12:12.123
2	9999-12-31 23:59:59.999    9999-12-31 23:59:59.999
------------------------------------------------------

When the option is enabled, queries that filter on timestamp values must include the new timestamp value in the filter condition, as shown:

select * from dfs.`/tmp/timestamp` where datetimestamp=timestamp '2018-01-01 12:12:12.123';
------------------------------------------------------
_id	datestring	         datetimestamp
------------------------------------------------------
1	2018-01-01 12:12:12.123   2018-01-01 12:12:12.123
------------------------------------------------------

Notice that the WHERE clause uses the `2018-01-01 12:12:12.123` format versus the `2018-01-01 20:12:12.123` format.