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 (EEP 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.