tooptoop4 created DRILL-5931:
--------------------------------
Summary: Drill queries against hive metastore tables return 0 rows
rather than error
Key: DRILL-5931
URL: https://issues.apache.org/jira/browse/DRILL-5931
Project: Apache Drill
Issue Type: Improvement
Components: Storage - Hive
Affects Versions: 1.11.0
Environment: linux
drill 1.11
hive 2.3.0
Reporter: tooptoop4
Priority: Critical
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.
{code:sql}
[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 |
[email protected] | 2020-03-01 | M | M
| 12 |
+---------------------+---------------------+--------------------+-------------------------+-------------------------+---------------+-------------------+--------------------+--------------+-----------------+-------------------------+--------------+
1 rows selected (0.156 seconds)
0: jdbc:hive2://localhost:10000/default>
{code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
