[DRILL-5931] Drill queries against hive metastore tables return 0 rows rather than error

• Type: Improvement
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.11.0
• Component/s: Storage - Hive

Environment

linux
drill 1.11
hive 2.3.0

• Flags: Important
• Assignee: Unassigned
• Reporter: tooptoop4
• Created: 04/Nov/17 05:04
• Updated: 04/Nov/17 05:04

Description

Note that the hive user called 'hive' does not have a linux account so has no access to /home/ec2-user/warehouse/ file system. Rather than returning an error message, Drill pretends that the table is empty.

[ec2-user@host ~]$ cd /usr/lib/apache-drill-1.11.0
[ec2-user@host apache-drill-1.11.0]$ ./bin/drill-embedded
OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Nov 01, 2017 7:53:53 AM org.glassfish.jersey.server.ApplicationHandler initialize
INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26...
apache drill 1.11.0
"this isn't your grandfather's sql"
0: jdbc:drill:zk=local> SHOW SCHEMAS;
+---------------------------------+
|           SCHEMA_NAME           |
+---------------------------------+
| INFORMATION_SCHEMA              |
| coffeemysql.COFFEEBREAK         |
| coffeemysql.information_schema  |
| coffeemysql.mysql               |
| coffeemysql.performance_schema  |
| coffeemysql.test                |
| coffeemysql                     |
| cp.default                      |
| dfs.default                     |
| dfs.root                        |
| dfs.tmp                         |
| hive.default                    |
| sys                             |
+---------------------------------+
13 rows selected (0.317 seconds)
0: jdbc:drill:zk=local> use hive;
+-------+-----------------------------------+
|  ok   |              summary              |
+-------+-----------------------------------+
| true  | Default schema changed to [hive]  |
+-------+-----------------------------------+
1 row selected (0.16 seconds)
0: jdbc:drill:zk=local> show tables;
+---------------+-----------------------------+
| TABLE_SCHEMA  |         TABLE_NAME          |
+---------------+-----------------------------+
| hive.default  | contact                     |
| hive.default  | account                     |
+---------------+-----------------------------+
2 rows selected (0.958 seconds)
0: jdbc:drill:zk=local> select * from contact;
+-------------+-------------+------------+-----------------+-----------------+-------+-----------+--------+------+---------+-----------------+------+
| contact_id  | first_name  | last_name  | address_line_1  | address_line_2  | city  | postcode  | email  | dob  | gender  | marital_status  | tfn  |
+-------------+-------------+------------+-----------------+-----------------+-------+-----------+--------+------+---------+-----------------+------+
+-------------+-------------+------------+-----------------+-----------------+-------+-----------+--------+------+---------+-----------------+------+
No rows selected (0.104 seconds)




This is the hive plugin in the Drill browser ‘storage’ plugin
{
  "type": "hive",
  "enabled": true,
  "configProps": {
    "hive.metastore.uris": "thrift://host:9083",
    "javax.jdo.option.ConnectionURL": "jdbc:mysql://host:3306/metastore",
    "javax.jdo.option.ConnectionDriverName": "com.mysql.jdbc.Driver",
    "javax.jdo.option.ConnectionUserName": "hive",
    "javax.jdo.option.ConnectionPassword": "hive1234",
    "hive.metastore.warehouse.dir": "file:///home/ec2-user/warehouse",
    "fs.default.name": "file:///"
  }
}



Below connection with beeline proves there is data in the table:

Beeline version 2.3.0 by Apache Hive
0: jdbc:hive2://localhost:10000/default> show create table contact;
+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `contact`(                            |
|   `contact_id` varchar(50),                        |
|   `first_name` varchar(50),                        |
|   `last_name` varchar(50),                         |
|   `address_line_1` varchar(100),                   |
|   `address_line_2` varchar(100),                   |
|   `city` varchar(50),                              |
|   `postcode` varchar(10),                          |
|   `email` varchar(100),                            |
|   `dob` date,                                      |
|   `gender` varchar(1),                             |
|   `marital_status` varchar(1),                     |
|   `tfn` varchar(20))                               |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'file:/home/ec2-user/warehouse/contact'          |
| TBLPROPERTIES (                                    |
|   'transient_lastDdlTime'='1509487563')            |
+----------------------------------------------------+
23 rows selected (0.361 seconds)
0: jdbc:hive2://localhost:10000/default> select * from contact;
+---------------------+---------------------+--------------------+-------------------------+-------------------------+---------------+-------------------+--------------------+--------------+-----------------+-------------------------+--------------+
| contact.contact_id  | contact.first_name  | contact.last_name  | contact.address_line_1  | contact.address_line_2  | contact.city  | contact.postcode  |   contact.email    | contact.dob  | contact.gender  | contact.marital_status  | contact.tfn  |
+---------------------+---------------------+--------------------+-------------------------+-------------------------+---------------+-------------------+--------------------+--------------+-----------------+-------------------------+--------------+
| C1                  | Bob                 | B                  | St                      |                         | Ta            | 2100              | [e-mail]          | 2020-03-01   | M               | M                       | 12           |
+---------------------+---------------------+--------------------+-------------------------+-------------------------+---------------+-------------------+--------------------+--------------+-----------------+-------------------------+--------------+
1 rows selected (0.156 seconds)
0: jdbc:hive2://localhost:10000/default>