About the Simba Apache Drill ODBC Connector

About Apache Drill

Apache Drill is a low latency distributed query engine capable of querying large datasets from multiple data sources using SQL.

The data sources that Drill supports include file systems (local or distributed) and HBase. Drill also integrates seamlessly with the Hive metastore to complement existing Hive environments with low latency queries. Unlike traditional RDBMS or SQL-on-Hadoop solutions that require centralized schema definitions, Drill can query self-describing data as well as complex or multi-structured data that is commonly seen in big data systems. Moreover, Drill does not require a fully structured schema and can support semi-structured or nested data types such as JSON.

Apache Drill processes the data in record batches and discovers the schema during the processing of each record batch. Thus, Drill has the capability to support changing schemas over the lifetime of a query. Drill reconfigures its operators and handles these situations to ensure that data is not lost.

Apache Drill can support multiple Hive, HBase, and DFS data sources, including CSV, TSV, Parquet, and JSON files. Connections between data sources and Drill can be configured through Drill’s web UI.

Note:

For information about connecting Apache Drill to data sources, see the Apache Drill documentation: http://drill.apache.org/docs/.

About the Driver

The Simba Apache Drill ODBC Connector lets organizations connect their BI tools to Drill. Drill provides an ANSI SQL query layer and also exposes the metadata information through an ANSI SQL standard metadata database called INFORMATION_SCHEMA. The Simba Apache Drill ODBC Connector leverages INFORMATION_SCHEMA to expose Drill’s metadata to BI tools as needed.

The driver complies with the ODBC 3.80 data standard, including important functionality such as Unicode and 32- and 64-bit support for high-performance computing environments on all platforms.

ODBC is one of the most established and widely supported APIs for connecting to and working with databases. At the heart of the technology is the ODBC connector, which connects an application to the database. For more information about ODBC, see: https://insightsoftware.com/blog/what-is-odbc/ For complete information about the ODBC specification, see the ODBC API Reference from the Microsoft documentation: https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/odbc-api-reference.

The Simba Apache Drill ODBC Connector is available for Microsoft® Windows®, Linux, and macOS platforms.

Note:

This is the most up-to-date version of the Installation and Configuration Guide, for use with version 1.6.0 of the connector. If you are using an older version of the connector, certain features may not be available and certain settings may behave in unexpected ways. Please consult the PDF version of the Installation and Configuration Guide that was installed with your connector.

The Simba Apache Drill ODBC Connector with SQL Connector Installation and Configuration Guide is suitable for users who are looking to access data residing within Drill from their desktop environment. Application developers may also find the information helpful. Refer to your application for details on connecting via ODBC.

About SQLParse Methods

The SOQL_FIRST and SQL_FIRST parse methods may lead to different behavior for similar queries. This occurs when the connector switches between the two modes trying to find a query language that can support the inputted query. Behavior will be consistent when the query language the connector decides to use remains consistent, however changes to the query may cause it to fail in one of the languages. For example, when using SOQL_FIRST mode your issue a query that is executable as SOQL. In a subsequent transaction, you slightly modify the query and it becomes invalid in SOQL, but is valid in SQL. This results in the first query being executed using SOQL and the second query being executed using SQL. This can cause slight differences between the two result sets since the behavior is not the same for all SOQL and SQL queries.

One instance where this occurs is comparisons involving null values, because SOQL and SQL handle comparisons against null differently. SQL returns an unknown state if a comparison operator (such as = or >) is used with null, and the results contain zero rows. However, SOQL allows such a comparison and returns results.

Using the SOQL_FIRST mode, you issue the query SELECT Name FROM Account WHERE NumberOfEmployees = NULL. This query is valid SOQL and the returned values contain all non-null values as specified by SOQL. Next you issue the query SELECT Account.Name FROM Account, Contact WHERE Account.Id = Contact.AccountId AND Account.NumberOfEmployees = NULL. This query is not valid SOQL but is valid SQL. It returns zero values as specified by the SQL specification. The first query may have lead you to believe that the second query would also return results, but difference in query language used means second query returns no results.

If you require consistent behavior in these types of instances, use either SOQL_ONLY or the SQL_ONLY mode.