Apache Drill - Hive Integration: Drill Not listing Tables

Question

I have been trying to integrate Apache Drill with Hive using Hive Storage Plugin configuration. I configured the storage plugin with all the necessary properties required. On Drill Shell, I can view the Hive Databases using:

Show Databases;

But when i try to list tables using:

Show Tables;

I get no results (No List of Tables).

Below are the steps i have followed from Apache Drill documentation and other sources:

I created a Drill Distributed Cluster by updating drill-override.conf with same cluster id on all nodes along with ZK IP with Port and then invoking drillbit.sh on each node.
Started Drill shell using drill-conf, Ensured that the Hive metastore service is active as well.

Below is configuration made in Hive Storage Plugin for Drill (from its Web-UI):

{
  "type": "hive",
  "configProps": {
    "hive.metastore.uris": "thrift://node02.cluster.com:9083",
    "javax.jdo.option.ConnectionURL": "jdbc:mysql://node02.cluster.com/hive",
    "hive.metastore.warehouse.dir": "/apps/hive/warehouse",
    "fs.default.name": "hdfs://node01.cluster.com:8020",
    "hive.metastore.sasl.enabled": "false"
  },
  "enabled": true
}

All the properties are set after referring to hive-site.xml

So, That's what all others have done to integrate Drill with Hive. Am i missing something here?

Regarding Versions- Drill: 1.14 ,Hive : 1.2 (Hive Metastore: MySQL) We also have Hive Server2 on the same nodes, is that causing any issues? I just want to integrate Drill with Hive 1.2, am i doing it right? Any pointers will be helpful, have spent nearly 2 days to get it right. Thanks for your time.

Side note: ConnectionURL is useless (or even harmful) since you specific a proper Metastore service URI -- no need to access the underlying MySQL database — Samson Scharfrichter
"when i try to list tables" > in which database? If you don't set the implicit DB with use command then you are probably defaulting to some dummy Drill-specific area. — Samson Scharfrichter
The Drill documentation is quite explicit about show tables and use drill.apache.org/docs/show-tables — Samson Scharfrichter
@SamsonScharfrichter As stated above, i can access hive databases in Drill using USE command,(say USE DUMMY_DB), which is also accessible via Hive Shell. After that, In Drill SHOW TABLES is not listing tables. — Akshay
@SamsonScharfrichter Ok, i will configure Hive Storage PLugin without ConnectionURL. I will update you if it works — Akshay

Vitalii Diravka Vitalii Diravka · Accepted Answer · 2018-10-24T19:06:31

Starting from Drill 1.13 version Drill leverages Hive client 2.3.2 version. It is recommended to use Hive 2.3 version to avoid unpredictable issues.

Regarding your setup, please remove all configProps except hive.metastore.uris. The other configs can be default (it is in HiveConf.java) or can be specified in your hive-site.xml.

Also in case of empty result after usage Show Tables; even after executing use hive, check for errors in Drill's log files. If some error is there, you can create the Jira ticket to improve the output from Drill to reflect that issue.

Apache Drill - Hive Integration: Drill Not listing Tables

1 Answers