It could be related to Hive versions. You can try an older version of Drill to verify this... or upgrade to Hive 2.x which is not expected to be fully backward compatible with Hive 1.x (hence the major version change).
Also, if I would suggest you experiment with the back ticks to see if that makes a difference. On 5/15/2019 10:21:13 AM, Ahmad, Sami <sami.ah...@dot.state.fl.us> wrote: Could this be version issue? I saw on web that dril 1.13 requires hive 2.3.X I am using hive 1.2 regards From: Khurram Faraaz [mailto:kfar...@mapr.com] Sent: Tuesday, May 14, 2019 6:35 PM To: user@drill.apache.org; Ahmad, Sami Subject: Re: WELCOME to user@drill.apache.org If you are using mySQL for hive metastore, your entries in Drill's hive storage plugin should like below { "type": "hive", "configProps": { "hive.metastore.uris": "thrift://1.2.10.10:9083", "javax.jdo.option.ConnectionURL": "jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true", "javax.jdo.option.ConnectionUserName": "root", "javax.jdo.option.ConnectionPassword": "test", "hive.metastore.warehouse.dir": "/tmp/drill_hive_wh", "fs.default.name": "file:///", "hive.metastore.sasl.enabled": "true", "datanucleus.schema.autoCreateAll": "true" }, "enabled": true } On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami <>> wrote: I see bunch of hive jar files in the jars\3rdparty directory but they are different version than my hive jars . I tried copying the hive jars from my hive environment to this 3rdparty folder but it didn’t help also [root@hadoop1 3rdparty]# pwd /root/apache-drill-1.16.0/jars/3rdparty [root@hadoop1 3rdparty]# ls -ltr hive* -rw-r--r-- 1 root root 112869 Apr 24 15:05 hive-hbase-handler-2.3.2.jar -rw-r--r-- 1 root root 125727 Apr 24 15:05 hive-contrib-2.3.2.jar -rw-r--r-- 1 root root 8119261 Apr 24 15:05 hive-metastore-2.3.2.jar I copied the files below to the jars\3rdparty folder . -rw-r--r-- 1 root root 6345039 May 14 15:51 hive-metastore-1.2.1000.2.6.5.0-292.jar -rw-r--r-- 1 root root 122581 May 14 15:52 hive-contrib-1.2.1000.2.6.5.0-292.jar -rw-r--r-- 1 root root 120473 May 14 15:52 hive-hbase-handler-1.2.1000.2.6.5.0-292.jar [root@hadoop1 3rdparty]# [root@hadoop1 3rdparty]# pwd /root/apache-drill-1.16.0/jars/3rdparty You have mail in /var/spool/mail/root [root@hadoop1 3rdparty]# -----Original Message----- From: Khurram Faraaz [mailto:kfar...@mapr.com] Sent: Tuesday, May 14, 2019 3:10 PM To: user@drill.apache.org Cc: user-h...@drill.apache.org Subject: Re: WELCOME to user@drill.apache.org Looking at you Hive storage plugin, there are two entries (see below) that are missing, can you please add them with appropriate values to Hive storage plugin and give it a try ? "javax.jdo.option.ConnectionURL": "jdbc:derby:;databaseName=../sample-data/drill_hive_db;create=true", "hive.metastore.warehouse.dir": "/tmp/drill_hive_wh", Thanks, Khurram On Tue, May 14, 2019 at 11:31 AM Ahmad, Sami <>> wrote: > Same problem I have with hbase , it wont show the tables. > Hbase version is 1.1.2 > > Here is the storage plugin for hbase I am using > > { > "type": "hbase", > "config": { > "hbase.zookeeper.quorum": "hadoop1", > "hbase.zookeeper.property.clientPort": "2181" > }, > "size.calculator.enabled": false, > "enabled": true > } > > -----Original Message----- > From: Ahmad, Sami [mailto:sami.ah...@dot.state.fl.us] > Sent: Tuesday, May 14, 2019 2:25 PM > To: user@drill.apache.org > Cc: user-h...@drill.apache.org > Subject: RE: WELCOME to user@drill.apache.org > > Hi Khurram > > 1- yes the show tables command work from hive shell > > hive> show tables > > ; > OK > test > test_orc_richa1116 > test_orc_txn > tweets > users2 > xyz > Time taken: 1.385 s > > > > 2- hive version is 1.2.1 > > 3- storage plugin in shown below > > { > "type": "hive", > "configProps": { > "hive.metastore.uris": "thrift://hadoop2:9083", > "hive.metastore.sasl.enabled": "false", > "fs.default.name": "hdfs:///" > }, > "enabled": true > } > > > Regards > Sami > > -----Original Message----- > From: Khurram Faraaz [mailto:kfar...@mapr.com] > Sent: Tuesday, May 14, 2019 2:18 PM > To: user@drill.apache.org > Cc: user-h...@drill.apache.org > Subject: Re: WELCOME to user@drill.apache.org > > Hi Ahmad, > > Does the "show tables" command work for you from Hive shell ? > Can you please share you Hive version that is being used ? > > And share the content from your Hive storage plugin, which is > available on the storage tab on Drill web UI. > > Thanks, > Khurram > > On Tue, May 14, 2019 at 10:07 AM Ahmad, Sami > <>> > wrote: > > > I am not able to see any hive or hbase tables in drill , I get the > > schema fine , getting the error in the logfile shown below . > > > > Regards > > > > > > > > [root@hadoop1 bin]# ./drillbit.sh start Starting drillbit, logging > > to /root/apache-drill-1.16.0/log/drillbit.out > > > > > > [root@hadoop1 bin]# > > [root@hadoop1 bin]# ./sqlline -u jdbc:drill:zk=hadoop1:2181 Apache > > Drill 1.16.0 "Say hello to my little Drill." > > apache drill> show schemas; > > +--------------------+ > > | SCHEMA_NAME | > > +--------------------+ > > | cp.default | > > | dfs.default | > > | dfs.root | > > | dfs.tmp | > > | hbase | > > | hive.aaronm | > > | hive.default | > > | hive.es_testdb | > > | hive.guest | > > | hive.ivann | > > | hive.levic | > > | hive.nasirq | > > | hive.nipar | > > | hive.rangertst | > > | hive.richas | > > | hive.roumig | > > | hive.sami | > > | hive.sandeeps | > > | hive.sasmitap | > > | hive.sid | > > | hive.swaroopa | > > | hive.tito | > > | hive.usha | > > | information_schema | > > | sys | > > +--------------------+ > > 25 rows selected (1.869 seconds) > > apache drill> use hive; > > +------+----------------------------------+ > > | ok | summary | > > +------+----------------------------------+ > > | true | Default schema changed to [hive] | > > +------+----------------------------------+ > > 1 row selected (0.103 seconds) > > apache drill (hive)> show tables; < not="" showing=""> > > ANY HIVE TABLES. > > +--------------+------------+ > > | TABLE_SCHEMA | TABLE_NAME | > > +--------------+------------+ > > +--------------+------------+ > > No rows selected (0.601 seconds) > > apache drill (hive)> > > > > > > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor. > > java:617) > > [na:1.8.0_112] > > at java.lang.Thread.run(Thread.java:745) [na:1.8.0_112] > > Caused > > by: java.util.concurrent.ExecutionException: > > MetaException(message:Got exception: > > org.apache.thrift.TApplicationException Invalid method name: > > 'get_tables_by_type') > > at > > org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abst > > ra > > ctFuture.getDoneValue(AbstractFuture.java:502) > > ~[drill-shaded-guava-23.0.jar:23.0] > > at > > org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abst > > ra > > ctFuture.get(AbstractFuture.java:461) > > ~[drill-shaded-guava-23.0.jar:23.0] > > at > > org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abst > > ra > > ctFuture$TrustedFuture.get(AbstractFuture.java:83) > > ~[drill-shaded-guava-23.0.jar:23.0] > > at > > org.apache.drill.shaded.guava.com.google.common.util.concurrent.Unin > > te > > rruptibles.getUninterruptibly(Uninterruptibles.java:142) > > ~[drill-shaded-guava-23.0.jar:23.0] > > at > > org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Seg > > me > > nt.getAndRecordStats(LocalCache.java:2453) > > ~[drill-shaded-guava-23.0.jar:23.0] > > at > > org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Seg > > me > > nt.loadSync(LocalCache.java:2417) > > ~[drill-shaded-guava-23.0.jar:23.0] > > at o > > > > > > -----Original Message----- > > From: user-h...@drill.apache.org [mailto:user-h...@drill.apache.org] > > Sent: Tuesday, May 14, 2019 10:55 AM > > To: Ahmad, Sami <>> > > Subject: WELCOME to user@drill.apache.org > > > > EXTERNAL SENDER: Use caution with links and attachments. > > > > > > Hi! This is the ezmlm program. I'm managing the > > user@drill.apache.org mailing list. > > > > I'm working for my owner, who can be reached at > > user-ow...@drill.apache.org. > > > > Acknowledgment: I have added the address > > > > sami.ah...@dot.state.fl.us > > > > to the user mailing list. > > > > Welcome to user@drill.apache.org! > > > > Please save this message so that you know the address you are > > subscribed under, in case you later want to unsubscribe or change > > your subscription address. > > > > > > --- Administrative commands for the user list --- > > > > I can handle administrative requests automatically. Please do not > > send them to the list address! Instead, send your message to the > > correct command > > address: > > > > To subscribe to the list, send a message to: > > <>> > > > > To remove your address from the list, send a message to: > > <>> > > > > Send mail to the following for info and FAQ for this list: > > <>> > > <>> > > > > Similar addresses exist for the digest list: > > <>> > > <>> > > > > To get messages 123 through 145 (a maximum of 100 per request), mail: > > <>> > > > > To get an index with subject and author for messages 123-456 , mail: > > <>> > > > > They are always returned as sets of 100, max 2000 per request, so > > you'll actually get 100-499. > > > > To receive all messages with the same subject as message 12345, send > > a short message to: > > <>> > > > > The messages should contain one line or word of text to avoid being > > treated as sp@m, but I will ignore their content. > > Only the ADDRESS you send to is important. > > > > You can start a subscription for an alternate address, for example > > "john@host.domain", just add a hyphen and your address (with '=' > > instead of '@') after the command word: > > <>> > > > > To stop subscription for this address, mail: > > <>> > > > > In both cases, I'll send a confirmation message to that address. > > When you receive it, simply reply to it to complete your subscription. > > > > If despite following these instructions, you do not get the desired > > results, please contact my owner at user-ow...@drill.apache.org. > > Please be patient, my owner is a lot slower than I am ;-) > > > > --- Enclosed is a copy of the request I received. > > > > Return-Path: <>> > > Received: (qmail 12237 invoked by uid 99); 14 May 2019 14:55:15 > > -0000 > > Received: from pnap-us-west-generic-nat.apache.org (HELO > > spamd1-us-west.apache.org) (209.188.14.142) > > by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 14 May 2019 > > 14:55:15 > > +0000 > > Received: from localhost (localhost [127.0.0.1]) > > by spamd1-us-west.apache.org (ASF Mail Server at > > spamd1-us-west.apache.org) with ESMTP id 42BBFC5B46 > > for > > dot.state.fl...@drill.apache.org>; Tue, 14 May 2019 14:55:15 +0000 > > (UTC) > > X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org > > X-Spam-Flag: NO > > X-Spam-Score: 0.549 > > X-Spam-Level: > > X-Spam-Status: No, score=0.549 tagged_above=-999 required=6.31 > > tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, > > DKIM_VALID_EF=-0.1, KAM_INFOUSMEBIZ=0.75, > > RCVD_IN_DNSWL_NONE=-0.0001, > > SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] > > autolearn=disabled > > Authentication-Results: spamd1-us-west.apache.org (amavisd-new); > > dkim=pass (1024-bit key) header.d=dot.state.fl.us > > Received: from mx1-lw-eu.apache.org ([10.40.0.8]) > > by localhost (spamd1-us-west.apache.org [10.40.0.7]) > > (amavisd-new, port 10024) > > with ESMTP id WIGqrOYFhOGS > > for > > dot.state.fl...@drill.apache.org>; > > Tue, 14 May 2019 14:55:12 +0000 (UTC) > > Received: from GCC01-CY1-obe.outbound.protection.outlook.com ( > > mail-eopbgr830121.outbound.protection.outlook.com [40.107.83.121]) > > by mx1-lw-eu.apache.org (ASF Mail Server at > > mx1-lw-eu.apache.org) with ESMTPS id 385AA5FB42 > > for > > dot.state.fl...@drill.apache.org>; Tue, 14 May 2019 14:55:12 +0000 > > (UTC) > > DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; > > d=dot.state.fl.us; s=selector1; > > h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchan > > ge -SenderADCheck; bh=FG36ZZm24V/Sa+Cahhby4FdXeuNBBjVrqcw7MAftG3U=; > > > > > > b=kubvI7tJmYr7oLS7lzYjZwd0rTfPHJgNKouDkKCD6sH5wfmOhKO0tMsh9Z4/X9Y63O > > ym > > n5dxz6j2wwV9M1d6YOsP1EPjy3/e5zBGZP/sUGpVCBXyTMrLzY7HdBb2NlUvi3vw6TD4 > > UP > > RSsqALt8bf0dMr/TPhSoDnpHvkrXANaZM= > > Received: from BL0PR0901MB3154.namprd09.prod.outlook.com > > (20.177.243.145) by BL0PR0901MB2481.namprd09.prod.outlook.com > > (52.132.19.143) with Microsoft SMTP Server (version=TLS1_2, > > cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.1878.21; > > Tue, > > 14 May 2019 14:55:03 +0000 > > Received: from BL0PR0901MB3154.namprd09.prod.outlook.com > > ([fe80::f8d0:42f:dc63:9883]) by > > BL0PR0901MB3154.namprd09.prod.outlook.com > > ([fe80::f8d0:42f:dc63:9883%7]) with mapi id 15.20.1878.024; Tue, 14 > > May > > 2019 > > 14:55:03 +0000 > > From: "Ahmad, Sami" <>> > > To: "user-sc.1557845487.ongognfnibknechabfkd-Sami.Ahmad= > > dot.state.fl...@drill.apache.org" > > > > dot.state.fl...@drill.apache.org> > > Subject: RE: confirm subscribe to user@drill.apache.org > > Thread-Topic: confirm subscribe to user@drill.apache.org > > Thread-Index: AQHVCmSDlyDdP1lRT0SYTvGULj1vtqZqtS8Q > > Date: Tue, 14 May 2019 14:55:03 +0000 > > Message-ID: > > <> > > bl0pr0901mb3154e9e2d60af474e6fa961689...@bl0pr0901mb3154.namprd09.pr > > od > > .outlook.com > > > > > References: <>> > > In-Reply-To: <>> > > Accept-Language: en-US > > Content-Language: en-US > > X-MS-Has-Attach: > > X-MS-TNEF-Correlator: > > authentication-results: spf=none (sender IP is ) smtp.mailfrom= > > sami.ah...@dot.state.fl.us; > > x-originating-ip: [156.75.180.190] > > x-ms-publictraffictype: Email > > x-ms-office365-filtering-correlation-id: > > 891d59b5-e680-490f-e77a-08d6d87c258a > > x-ms-office365-filtering-ht: Tenant > > x-microsoft-antispam: > > > > > > BCL:0;PCL:0;RULEID:(2390118)(7020095)(4652040)(8989299)(4534185)(462 > > 72 > > 21)(201703031133081)(201702281549075)(8990200)(5600141)(711020)(4605 > > 10 4)(4618075)(2017052603328)(7193020);SRVR:BL0PR0901MB2481; > > x-ms-traffictypediagnostic: BL0PR0901MB2481: > > x-ms-exchange-purlcount: 1 > > x-microsoft-antispam-prvs: > > <> > > bl0pr0901mb2481972fb739d8b1bbfc5ea889...@bl0pr0901mb2481.namprd09.pr > > od > > .outlook.com > > > > > x-ms-oob-tlc-oobclassifiers: OLM:6108; > > x-forefront-prvs: 0037FD6480 > > x-forefront-antispam-report: > > > > > SFV:NSPM;SFS:(10019020)(346002)(366004)(39860400002)(396003)(136003)(376002)(13464003)(199004)(189003)(786003)(74482002)(186003)(222073003)(316002)(53936002)(55016002)(6506007)(86362001)(66574012)(9686003)(6306002)(53546011)(81156014)(99286004)(7696005)(5660300002)(76176011)(8676002)(8936002)(68736007)(81166006)(71200400001)(71190400001)(74316002)(229853002)(66066001)(446003)(66946007)(66476007)(14454004)(64756008)(45080400002)(476003)(256004)(478600001)(66446008)(66556008)(52536014)(966005)(486006)(11346002)(2906002)(14444005)(5024004)(72206003)(19627235002)(26005)(3846002)(25786009)(6116002)(305945005)(7736002)(6436002)(73956011)(76116006)(33656002)(102836004)(6246003)(10126004)(30470500001);DIR:OUT;SFP:1102;SCL:1;SRVR:BL0PR0901MB2481;H: > > BL0PR0901MB3154.namprd09.prod.outlook.com > > ;FPR:;SPF:None;LANG:en;PTR:InfoNoRecords;A:1;MX:1; > > received-spf: None (protection.outlook.com: dot.state.fl.us does not > > designate permitted sender hosts) > > x-ms-exchange-senderadcheck: 1 > > x-microsoft-antispam-message-info: > > > > > > k2rNJMj8gMUewNyNOmI0SUR+UCyah3rez5r2HigVsxBskiAAZWkWINZXHfqqFEclRqF4i+ > > o1zV6fLJgi21JXzH4zC2cUwLqjfAhQaVHkPf1tevLYvdIbmetRZPeLHXldcWQO2KfTn0 > > Yd > > XBGyQrv9CgAleETkSlJlU47PChYqxlDMkFyx5uwYItTRIJ+M1UMatfen4JeLkLqY38qi > > XBGyQrv9CgAleETkSlJlU47PChYqxlDMkFyx5uwYItTRIJ+Mh > > Fl0k6vhEKMbf6bUGmhHFFSvRKTlMTD3jOepQ/0KDsq3bytE/g96JFoFcNJjlQ3YeYQNj > > j5 > > 88uisRW7SMAYvlGZY5aVSoMmlQX5zDAX1C9Q4H07TfFGFgBWguBYlLrVYKQ5Lni8AEko > > zf > > 2NfQVk5h1aPk62ANi/9njwEJwGfMn1S+LDn1zPveeTa65+FFTdsT8AZHAzl/cSJPpFwc > > 0s > > 56hSVh4= > > Content-Type: text/plain; charset="us-ascii" > > Content-Transfer-Encoding: quoted-printable > > MIME-Version: 1.0 > > X-OriginatorOrg: dot.state.fl.us > > X-MS-Exchange-CrossTenant-Network-Message-Id: > > 891d59b5-e680-490f-e77a-08d6d87c258a > > X-MS-Exchange-CrossTenant-originalarrivaltime: 14 May 2019 > > 14:55:03.6058 > > (UTC) > > X-MS-Exchange-CrossTenant-fromentityheader: Hosted > > X-MS-Exchange-CrossTenant-id: db21de5d-bc9c-420c-8f3f-8f08f85b5ada > > X-MS-Exchange-CrossTenant-mailboxtype: HOSTED > > X-MS-Exchange-Transport-CrossTenantHeadersStamped: BL0PR0901MB2481 > > > > >
