ODBC Configuration Reference
You can use various configuration options to control the behavior of the MapR
Drill ODBC Driver. You can use these options in a connection string or in the
odbc.ini configuration file for the Mac OS X version or the driver.
Note
If you use a connection string to connect to your data source, set these configuration properties in the connection string instead of the .odbc.ini file.
Configuration Options
The following table provides a list of the configuration options and a brief description. Subsequent sections describe options in more detail:
| Property | Valid Values | Brief Description |
|---|---|---|
| Catalog | DRILL | The name of the synthetic catalog under which all of the schemas/databases are organized. |
| ConnectionType | Direct or ZooKeeper | Direct connects to a Drill server using Host and Port properties. ZooKeeper connects to a ZooKeeper cluster using ZKQuorum and ZKClusterID properties. |
| Driver | MapR Drill ODBC Driver | The name of the installed driver. |
| Host | <host name> | If the ConnectionType property is set to Direct, set the host name of the Drill server using the Host property. |
| Port | 31010 | If the ConnectionType property is set to Direct, set the e TCP port on which the Drill server is listening. |
| Schema | <schema name> | The name of the database schema or storage plugin name to use when the query does not explicitly specify the schema or storage plugin. |
| ZKClusterID | drillbits1 | If the ConnectionType property is set to ZooKeeper, then set ZKClusterID to the name of the Drillbit cluster to use. |
| ZKQuorum | <IP address>,<IP address> . . . | If the ConnectionType property is set to ZooKeeper, then use ZKQuorum to indicate the server(s) in your ZooKeeper cluster. Separate multiple servers using a comma (,). |
| AuthenticationType | No Authentication or Basic Authentication | Basic Authentication enables impersonation. |
| UID | <user name> | If AuthenticationType is Basic Authentication, set the UID to a user name. |
| PWD | <password> | If AuthenticationType is Basic Authentication, set the PWD to a password. |
| AdvancedProperties | {<property>;<property>; . . .} | Separate advanced properties using a semi-colon (;) and then surround all advanced properties in a connection string using braces { and }. |
| DisableAsync | 0 or 1 | Disables asynchronous ODBC connection and enables a synchronous connection. A change in state occurs during driver initialization and is propagated to all driver DSNs. |
Catalog
This value defaults to DRILL and cannot be changed. The driver adds a synthetic catalog named DRILL under which all of the schemas and databases are organized. The driver maps the ODBC schema to the DRILL catalog.
Connection Type
ODBC can connect directly to a Drillbit or to a ZooKeeper Quorum. Select your connection type based on your environment and Drillbit configuration as described in the following table:
| Environment | Connection Type |
|---|---|
| Drillbit is running in embedded mode. | Direct to Drillbit |
| Drillbit is registered with the ZooKeeper in a testing environment. | ZooKeeper Quorum or Direct to Drillbit |
| Drillbit is registered with the ZooKeeper in a production environment. | ZooKeeper Quorum |
Host Name and Port Number
When using ZooKeeper to connect to Drill, do not use the IP address in the connection string. Make sure the client system can resolve the actual hostname(s) by pinging the hostnames first.
ZKClusterID and ZKQuorum
The default cluster ID is drillbits1. Check the drill-override.conf in the Drill installation /conf directory. Use the cluster-id and zk.connect values for ZKClusterID and ZKQuorum.
Connection to Zookeeper Quorum
When you choose to connect to a ZooKeeper Quorum, the ODBC driver connects to the ZooKeeper Quorum to get a list of available Drillbits in the specified cluster. Then, the ODBC driver submits a query after selecting a Drillbit. All Drillbits in the cluster process the query and the Drillbit that received the query returns the query results.
In a production environment, you should connect to a ZooKeeper Quorum for a more reliable connection. If one Drillbit is not available, another Drillbit that is registered with the ZooKeeper quorum can accept the query.
Direct Connection to Drillbit
When you choose to connect directly to a Drillbit, the ODBC driver connects to the Drillbit and submits a query. If you connect directly to Drillbit that is not part of a cluster, the Drillbit that you connect to processes the query. If you connect directly to a Drillbit that is part of a cluster, all Drillbits in the cluster process the query. In either case, the Drillbit that the ODBC driver connected to returns the query results.
Schema
The name of a schema, or storage plugin, from the default schema list of the data sources that you have configured to use with Drill. Queries on other schemas can still be issued by explicitly specifying the schema in the query.
Views that you create using the Drill Explorer do not appear under the schema associated with the data source type. Instead, the views can be accessed from the file-based schema that you selected when saving the view.
The driver supports the following schema types:
- HBase
- Distributed File System (DFS), supporting the following file formats:
- Parquet
- JSON
- CSV
- TSV
- Parquet
- Hive
Advanced Properties
The Advanced Properties field allows you to customize the DSN.
Separate advanced properties using a semi-colon (;).
For example, the following Advanced Properties string excludes the schemas
named test and abc; sets the timeout to 30 seconds; and sets the time zone
to Coordinated Universal:
Time:HandshakeTimeout=30;QueryTimeout=30;
TimestampTZDisplayTimezone=utc;ExcludedSchemas=test,abc
The following table lists and describes the advanced properties that you can set when using the MapR Drill ODBC Driver.
| Property Name | Default Value | Description |
|---|---|---|
| HandshakeTimeout | 5 | An integer value representing the number of seconds that the driver waits before aborting an attempt to connect to a data source. When set to a value of 0, the driver does not abort connection attempts. |
| QueryTimeout | 180 | An integer value representing the number of seconds for the driver to wait before automatically stopping a query. When set to a value of 0, the driver does not stop queries automatically. |
| TimestampTZDisplayTimezone | local | Two values are possible: local—Timestamps are dependent on the time zone of the user. utc—Timestamps appear in Coordinated Universal Time (UTC). |
| ExcludedSchemas | sys, INFORMATION_SCHEMA | The value of ExcludedSchemas is a list of schemas that do not appear in client applications such as Drill Explorer, Tableau, and Excel. Separate schemas in the list using a comma (,). |
| CastAnyToVarchar | true | Casts the ANY data type returned from SQL column calls into type “VARCHAR”. |
| NumberOfPrefetchBuffers | 5 | The size of the record batch queue in the driver. When set to a value below 1, the value defaults to 1. |
Connection String Examples
If you want to connect to a Drill data source from an application that does not require a DSN, you can use an ODBC connection string. The following is an example connection string for the Direct connection type:
DRIVER=MapR Drill ODBC Driver;AdvancedProperties={HandshakeTimeout=0;QueryTimeout=0;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;};Catalog=DRILL;Schema=hivestg;ConnectionType=Direct;Host=192.168.202.147;Port=31010
The following is an example connection string for the Zookeeper connection type:
DRIVER=MapR Drill ODBC Driver;AdvancedProperties={HandshakeTimeout=0;QueryTimeout=0;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys, INFORMATION_SCHEMA;};Catalog=DRILL;Schema=;ConnectionType=ZooKeeper;ZKQuorum=192.168.39.43:5181;ZKClusterID=drillbits1
Logging Options
Configure logging to troubleshoot issues. To configure logging, click the Logging Options button on the ODBC DSN Setup dialog and then set a log level and a log path.
If logging is enabled, the MapR Drill ODBC driver logs events in following log files in the log path that you configure:
- driver.log: A log of driver events
- drillclient.log: A log of the Drill client events.
Logging Levels
The following log levels are available:
- OFF: Disables logging.
- FATAL: Logs severe error events that may cause the driver to stop running.
- ERROR: Logs error events that may allow the driver to continue running.
- WARNING: Logs events about potentially harmful situations.
- INFO: Logs high-level events about driver processes.
- DEBUG: Logs detailed events that may help to debug issues.
- TRACE: Logs finer-grained events than the DEBUG level.
Enabling Logging
To enable logging:
- Open the mapr.drillodbc.ini configuration file in a text editor.
- Set the LogLevel key to the desired level of information to include in log files. For
example:
LogLevel=2 - Set the LogPath key to the full path to the folder where you want to save log files. For example:
LogPath=/localhome/employee/Documents - Save the mapr.drillodbc.ini configuration file.
The Simba ODBC Driver for Apache Drill produces two log files at the location you specify using the Log Path field:
- driver.log provides a log of driver activities
- drillclient.log provides a log of Drill client activities
Disabling Logging
To disable logging:
- Open the mapr.drillodbc.ini configuration file in a text editor.
- Set the LogLevel key to 0
- Save the mapr.drillodbc.ini configuration file.