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 <[email protected]>
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:[email protected]]
> Sent: Tuesday, May 14, 2019 3:10 PM
> To: [email protected]
> Cc: [email protected]
> Subject: Re: WELCOME to [email protected]
>
> 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 <[email protected]>
> 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:[email protected]]
> > Sent: Tuesday, May 14, 2019 2:25 PM
> > To: [email protected]
> > Cc: [email protected]
> > Subject: RE: WELCOME to [email protected]
> >
> > 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:[email protected]]
> > Sent: Tuesday, May 14, 2019 2:18 PM
> > To: [email protected]
> > Cc: [email protected]
> > Subject: Re: WELCOME to [email protected]
> >
> > 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
> > <[email protected]>
> > 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: [email protected] [mailto:[email protected]]
> > > Sent: Tuesday, May 14, 2019 10:55 AM
> > > To: Ahmad, Sami <[email protected]>
> > > Subject: WELCOME to [email protected]
> > >
> > > EXTERNAL SENDER: Use caution with links and attachments.
> > >
> > >
> > > Hi! This is the ezmlm program. I'm managing the
> > > [email protected] mailing list.
> > >
> > > I'm working for my owner, who can be reached at
> > > [email protected].
> > >
> > > Acknowledgment: I have added the address
> > >
> > > [email protected]
> > >
> > > to the user mailing list.
> > >
> > > Welcome to [email protected]!
> > >
> > > 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:
> > > <[email protected]>
> > >
> > > To remove your address from the list, send a message to:
> > > <[email protected]>
> > >
> > > Send mail to the following for info and FAQ for this list:
> > > <[email protected]>
> > > <[email protected]>
> > >
> > > Similar addresses exist for the digest list:
> > > <[email protected]>
> > > <[email protected]>
> > >
> > > To get messages 123 through 145 (a maximum of 100 per request), mail:
> > > <[email protected]>
> > >
> > > To get an index with subject and author for messages 123-456 , mail:
> > > <[email protected]>
> > >
> > > 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:
> > > <[email protected]>
> > >
> > > 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
> > > "[email protected]", just add a hyphen and your address (with '='
> > > instead of '@') after the command word:
> > > <[email protected]>
> > >
> > > To stop subscription for this address, mail:
> > > <[email protected]>
> > >
> > > 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 [email protected].
> > > Please be patient, my owner is a lot slower than I am ;-)
> > >
> > > --- Enclosed is a copy of the request I received.
> > >
> > > Return-Path: <[email protected]>
> > > 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 <user-sc.1557845487.ongognfnibknechabfkd-Sami.Ahmad=
> > > [email protected]>; 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 <user-sc.1557845487.ongognfnibknechabfkd-Sami.Ahmad=
> > > [email protected]>;
> > > 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 <user-sc.1557845487.ongognfnibknechabfkd-Sami.Ahmad=
> > > [email protected]>; 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" <[email protected]>
> > > To: "user-sc.1557845487.ongognfnibknechabfkd-Sami.Ahmad=
> > > [email protected]"
> > > <user-sc.1557845487.ongognfnibknechabfkd-Sami.Ahmad=
> > > [email protected]>
> > > Subject: RE: confirm subscribe to [email protected]
> > > Thread-Topic: confirm subscribe to [email protected]
> > > Thread-Index: AQHVCmSDlyDdP1lRT0SYTvGULj1vtqZqtS8Q
> > > Date: Tue, 14 May 2019 14:55:03 +0000
> > > Message-ID:
> > > <
> > > bl0pr0901mb3154e9e2d60af474e6fa961689...@bl0pr0901mb3154.namprd09.pr
> > > od
> > > .outlook.com
> > > >
> > > References: <[email protected]>
> > > In-Reply-To: <[email protected]>
> > > 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=
> > > [email protected];
> > > 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
> > >
> > >
> >
>
