Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-5931

Drill queries against hive metastore tables return 0 rows rather than error

    XMLWordPrintableJSON

    Details

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

      linux
      drill 1.11
      hive 2.3.0

    • Flags:
      Important

      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              | bob@b.com          | 2020-03-01   | M               | M                       | 12           |
      +---------------------+---------------------+--------------------+-------------------------+-------------------------+---------------+-------------------+--------------------+--------------+-----------------+-------------------------+--------------+
      1 rows selected (0.156 seconds)
      0: jdbc:hive2://localhost:10000/default>
      
      
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              tooptoop4 tooptoop4
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: