RE: WELCOME to user@drill.apache.org
As you can see hive has the table in default database but drill cant find it
hive> select * from default.test;
OK
2 cooper
4 Peter
1 levi
3 JOHN
Time taken: 1.946 seconds, Fetched: 4 row(s)
hive> exit
>
> ;
[root@hadoop1 bin]#
[root@hadoop1 bin]#
[root@hadoop1 bin]#
[root@hadoop1 bin]# ./sqlline -u jdbc:drill:zk=hadoop1:2181
Apache Drill 1.16.0
"This isn't your grandfather's SQL."
apache drill> select * from hive.`default`.test;
>
Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object
'test' not found within 'hive.default'
[Error Id: b4c73c9b-9460-4483-8010-a1bdaf97a4d0 ] (state=,code=0)
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<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A9083=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D=0>",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A3306%2Fhive%3FcreateDatabaseIfNotExist%3Dtrue=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D=0>",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "test",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffs.default.name=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D=0>":
"file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> 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<mailto:kfar...@mapr.com>]
Sent: Tuesday, May 14, 2019 3:10 PM
To: user@drill.apache.org<mailto:user@drill.apache.org>
Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
Subject: Re: WELCOME to user@drill.apache.org<mailto: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
mailto:sami.ah...@dot.state.fl.us>>
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
> }
>
> -Origin
RE: WELCOME to user@drill.apache.org
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<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A9083=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D=0>",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A3306%2Fhive%3FcreateDatabaseIfNotExist%3Dtrue=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D=0>",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "test",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffs.default.name=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D=0>":
"file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> 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<mailto:kfar...@mapr.com>]
Sent: Tuesday, May 14, 2019 3:10 PM
To: user@drill.apache.org<mailto:user@drill.apache.org>
Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
Subject: Re: WELCOME to user@drill.apache.org<mailto: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
mailto:sami.ah...@dot.state.fl.us>>
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<mailto:sami.ah...@dot.state.fl.us>]
> Sent: Tuesday, May 14, 2019 2:25 PM
> To: user@drill.apache.org<mailto:user@drill.apache.org>
> Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
> Subject: RE: WELCOME to user@drill.apache.org<mailto:user@drill.apache.org>
>
> Hi Khurram
>
> 1- yes the show tables command work from hive shell
>
> hive> show tables
> > ;
> OK
> test
> test_orc_ri
RE: WELCOME to user@drill.apache.org
Changed it as per your instructions but still no good
{
"type": "hive",
"configProps": {
"hive.metastore.uris": "thrift://hadoop2:9083",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://hadoop2:3306/hive?createDatabaseIfNotExist=true",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "x",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name": "file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
apache drill> use hive;
+--+--+
| ok | summary |
+--+--+
| true | Default schema changed to [hive] |
+--+--+
1 row selected (0.099 seconds)
apache drill (hive)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
+--++
No rows selected (0.593 seconds)
apache drill (hive)> use hive.`default`;
+--+--+
| ok | summary |
+--+--+
| true | Default schema changed to [hive.default] |
+--+--+
1 row selected (0.084 seconds)
apache drill (hive.default)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
+--++
No rows selected (0.321 seconds)
apache drill (hive.default)>
The logfile reporting the same old error :
2019-05-15 10:36:03,735 [2323dc2c-055d-fd58-61fb-967cddc8e620:frag:0:0] WARN
o.a.d.e.s.h.s.HiveDatabaseSchema - Exception was thrown while getting table
names and type for db 'default'.
org.apache.thrift.TException: java.util.concurrent.ExecutionException:
MetaException(message:Got exception: org.apache.thrift.TApplicationException
Invalid method name: 'get_tables_by_type')
at
org.apache.drill.exec.store.hive.client.HiveMetadataCache.getTableNamesAndTypes(HiveMetadataCache.java:116)
~[drill-storage-hive-core-1.16.0.jar:1.16.0]
at
org.apache.drill.exec.store.hive.client.DrillHiveMetaStoreClient.getTableNamesAndTypes(DrillHiveMetaStoreClient.java:90)
~[drill-storage-hive-core-1.16.0.jar:1.16.0]
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<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A9083=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D=0>",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A3306%2Fhive%3FcreateDatabaseIfNotExist%3Dtrue=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D=0>",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "test",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffs.default.name=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D=0>":
"file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> 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-
RE: cant select from hive table
None of the three worked apache drill (hive.default)> use hive.`default`; +--+--+ | ok | summary | +--+--+ | true | Default schema changed to [hive.default] | +--+--+ 1 row selected (0.086 seconds) apache drill (hive.default)> select * from test; Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object 'test' not found [Error Id: f4154d1a-59c3-4204-8186-67dcfaddf401 ] (state=,code=0) apache drill (hive.default)> apache drill (hive.default)> apache drill (hive.default)> !q Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl [root@hadoop1 bin]# sqlline -u jdbc:drill:zk=hadoop1:2181 Apache Drill 1.16.0 "Think different, think Drill." apache drill> select * from hive.`test`; Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object 'test' not found within 'hive' [Error Id: 65a8acce-19b4-47f3-babb-ca1dc8c80ce9 ] (state=,code=0) apache drill> apache drill> use hive; +--+--+ | ok | summary | +--+--+ | true | Default schema changed to [hive] | +--+--+ 1 row selected (0.088 seconds) apache drill (hive)> select * from test; Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object 'test' not found [Error Id: ebf13f9c-16c9-4990-b72a-ba74e8348753 ] (state=,code=0) -Original Message- From: Igor Guzenko [mailto:ihor.huzenko@gmail.com] Sent: Monday, May 27, 2019 11:02 AM To: user@drill.apache.org Cc: Khurram Faraaz Subject: Re: cant select from hive table Hello Khurram, If Hive storage based authorization is enabled for your deployment it's may be the reason why you're getting such messages. In the test it's visible that when at storage level user don't have rights to query table files Drill shows the confusing error. Here is the test https://gcc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fdrill%2Fblob%2Fmaster%2Fcontrib%2Fstorage-hive%2Fcore%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fdrill%2Fexec%2Fimpersonation%2Fhive%2FTestStorageBasedHiveAuthorization.javadata=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C913283ea621c44fa549508d6e2b46364%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C1%7C636945661720971215sdata=41utoQOJI01U0Dvc6bDZDLBL6UpoQHCr6sXK3AtWzCc%3Dreserved=0 . If I didn't guess and security isn't enabled, then you could just try: 1) Two separate queries: use hive.`default`; select * from test; OR 2) From root schema select * from hive.`test`; OR 3) Two separate queries: use hive; select * from test; If previous queries don't work for not secured deployment it's a bug. Thanks, Igor On Mon, May 27, 2019 at 3:27 PM Ahmad, Sami wrote: > > Still not working : > > apache drill> use hive.`default`; > +--+--+ > | ok | summary | > +--+--+ > | true | Default schema changed to [hive.default] | > +--+--+ > 1 row selected (0.089 seconds) > apache drill (hive.default)> show tables; > +--++ > | TABLE_SCHEMA | TABLE_NAME | > +--++ > | hive.default | test | > +--++ > 1 row selected (0.188 seconds) > apache drill (hive.default)> select * from hive.`default`.test; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object > 'test' not found within 'hive.default' > > > [Error Id: 6b24df4c-3678-4bf4-94eb-8dd5d9910213 ] (state=,code=0) > apache drill (hive.default)> > > -Original Message- > From: Arina Yelchiyeva [mailto:arina.yelchiy...@gmail.com] > Sent: Friday, May 24, 2019 4:34 PM > To: user@drill.apache.org > Cc: Khurram Faraaz > Subject: Re: cant select from hive table > > EXTERNAL SENDER: Use caution with links and attachments. > > > Default is keyword, use it back-ticks (select * from hive.`default`.test). > > > On May 24, 2019, at 10:59 PM, Ahmad, Sami > > wrote: > > > > As you can see , the show table command sees the table but select command > > cant find it . > > > > pache drill (hive.default)> show tables; > > +--++ > > | TABLE_SCHEMA | TABLE_NAME | > > +--++ > > | hive.default | test | > > +--++ > > 1 row selected (0.113 seconds) > > apache drill (hive.default)> select id from test; > > Error: VALIDATION ERROR: From line 1, column 16 to line 1, column 19: > > Object 'test' no
RE: Apache Drill Hangout - May 28, 2019
Yes please I have a question : can we query Hbase versions from Drill ? if yes how ? Thanks Sami -Original Message- From: Volodymyr Vysotskyi [mailto:volody...@apache.org] Sent: Monday, May 27, 2019 3:23 AM To: dev ; user Subject: Apache Drill Hangout - May 28, 2019 EXTERNAL SENDER: Use caution with links and attachments. Hi Drillers, We will have our bi-weekly hangout tomorrow May 28th, at 10 AM PST (link: https://gcc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmeet.google.com%2Fyki-iqdf-taidata=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C6928309cb92e4f80e4be08d6e274331d%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C1%7C636945386031005672sdata=gnCPO0T%2FsGLEjXYx3ZfWvpKDY5A3bWEMXEUxRQbqPa0%3Dreserved=0 ). If there are any topics you would like to discuss during the hangout please respond to this email. Kind regards, Volodymyr Vysotskyi
RE: cant select from hive table
Still not working :
apache drill> use hive.`default`;
+--+--+
| ok | summary |
+--+--+
| true | Default schema changed to [hive.default] |
+--+--+
1 row selected (0.089 seconds)
apache drill (hive.default)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
| hive.default | test |
+--++
1 row selected (0.188 seconds)
apache drill (hive.default)> select * from hive.`default`.test;
Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object
'test' not found within 'hive.default'
[Error Id: 6b24df4c-3678-4bf4-94eb-8dd5d9910213 ] (state=,code=0)
apache drill (hive.default)>
-Original Message-
From: Arina Yelchiyeva [mailto:arina.yelchiy...@gmail.com]
Sent: Friday, May 24, 2019 4:34 PM
To: user@drill.apache.org
Cc: Khurram Faraaz
Subject: Re: cant select from hive table
EXTERNAL SENDER: Use caution with links and attachments.
Default is keyword, use it back-ticks (select * from hive.`default`.test).
> On May 24, 2019, at 10:59 PM, Ahmad, Sami wrote:
>
> As you can see , the show table command sees the table but select command
> cant find it .
>
> pache drill (hive.default)> show tables;
> +--++
> | TABLE_SCHEMA | TABLE_NAME |
> +--++
> | hive.default | test |
> +--++
> 1 row selected (0.113 seconds)
> apache drill (hive.default)> select id from test;
> Error: VALIDATION ERROR: From line 1, column 16 to line 1, column 19:
> Object 'test' not found
>
>
> [Error Id: 7d0eb107-38c6-4d37-90b2-baa953a3e701 ] (state=,code=0)
> apache drill (hive.default)>
>
> From: Ahmad, Sami
> Sent: Friday, May 24, 2019 11:35 AM
> To: Khurram Faraaz ; user@drill.apache.org
> Subject: cant select from hive table
>
> Hi
> My hive storage plugin is working and I can see the hive tables but I
> can’t select from them ,please advise
>
> apache drill (hive.default)> show tables;
> +--++
> | TABLE_SCHEMA | TABLE_NAME |
> +--++
> | hive.default | test |
> +--++
> 1 row selected (0.126 seconds)
> apache drill (hive.default)> select id from hive.default.`test`;
> Error: PARSE ERROR: Encountered ". default" at line 1, column 20.
> Was expecting one of:
>
>"ORDER" ...
>"LIMIT" ...
>"OFFSET" ...
>"FETCH" ...
>"NATURAL" ...
>"JOIN" ...
>"INNER" ...
>"LEFT" ...
>"RIGHT" ...
>"FULL" ...
>"CROSS" ...
>"," ...
>"OUTER" ...
>"(" ...
>"MATCH_RECOGNIZE" ...
>"." ...
> ...
> ...
> ...
> ...
> ...
>"TABLESAMPLE" ...
>"WHERE" ...
>"GROUP" ...
>"HAVING" ...
>"WINDOW" ...
>"UNION" ...
>"INTERSECT" ...
>"EXCEPT" ...
>"MINUS" ...
>"." ...
>"." ...
>"." ...
>"." ...
>"." ...
>"." "*" ...
>"[" ...
>
>
> SQL Query select id from hive.default.`test`
> ^
>
>
> [Error Id: 96ad1704-8b95-42b7-913c-b62d3d2c5782 ] (state=,code=0)
> apache drill (hive.default)>
RE: cant select from hive table
As you can see , the show table command sees the table but select command cant
find it .
pache drill (hive.default)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
| hive.default | test |
+--++
1 row selected (0.113 seconds)
apache drill (hive.default)> select id from test;
Error: VALIDATION ERROR: From line 1, column 16 to line 1, column 19: Object
'test' not found
[Error Id: 7d0eb107-38c6-4d37-90b2-baa953a3e701 ] (state=,code=0)
apache drill (hive.default)>
From: Ahmad, Sami
Sent: Friday, May 24, 2019 11:35 AM
To: Khurram Faraaz ; user@drill.apache.org
Subject: cant select from hive table
Hi
My hive storage plugin is working and I can see the hive tables but I can’t
select from them ,please advise
apache drill (hive.default)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
| hive.default | test |
+--++
1 row selected (0.126 seconds)
apache drill (hive.default)> select id from hive.default.`test`;
Error: PARSE ERROR: Encountered ". default" at line 1, column 20.
Was expecting one of:
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," ...
"OUTER" ...
"(" ...
"MATCH_RECOGNIZE" ...
"." ...
...
...
...
...
...
"TABLESAMPLE" ...
"WHERE" ...
"GROUP" ...
"HAVING" ...
"WINDOW" ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"MINUS" ...
"." ...
"." ...
"." ...
"." ...
"." ...
"." "*" ...
"[" ...
SQL Query select id from hive.default.`test`
^
[Error Id: 96ad1704-8b95-42b7-913c-b62d3d2c5782 ] (state=,code=0)
apache drill (hive.default)>
RE: WELCOME to user@drill.apache.org
I am using apache drill 1.16 , its not letting me use this plugin .. I get an
error
"Please retry: Error while creating / updating storage : null"
Why ?
Changed it as per your instructions but still no good
{
"type": "hive",
"configProps": {
"hive.metastore.uris": "thrift://hadoop2:9083",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://hadoop2:3306/hive?createDatabaseIfNotExist=true",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "x",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name": "file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
apache drill> use hive;
+--+--+
| ok | summary |
+--+--+
| true | Default schema changed to [hive] |
+--+--+
1 row selected (0.099 seconds)
apache drill (hive)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
+--++
No rows selected (0.593 seconds)
apache drill (hive)> use hive.`default`;
+--+--+
| ok | summary |
+--+--+
| true | Default schema changed to [hive.default] |
+--+--+
1 row selected (0.084 seconds)
apache drill (hive.default)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
+--++
No rows selected (0.321 seconds)
apache drill (hive.default)>
The logfile reporting the same old error :
2019-05-15 10:36:03,735 [2323dc2c-055d-fd58-61fb-967cddc8e620:frag:0:0] WARN
o.a.d.e.s.h.s.HiveDatabaseSchema - Exception was thrown while getting table
names and type for db 'default'.
org.apache.thrift.TException: java.util.concurrent.ExecutionException:
MetaException(message:Got exception: org.apache.thrift.TApplicationException
Invalid method name: 'get_tables_by_type')
at
org.apache.drill.exec.store.hive.client.HiveMetadataCache.getTableNamesAndTypes(HiveMetadataCache.java:116)
~[drill-storage-hive-core-1.16.0.jar:1.16.0]
at
org.apache.drill.exec.store.hive.client.DrillHiveMetaStoreClient.getTableNamesAndTypes(DrillHiveMetaStoreClient.java:90)
~[drill-storage-hive-core-1.16.0.jar:1.16.0]
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<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A9083data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C542d4ca08a144a749c4b08d6d943290b%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636935279803095071sdata=EcjrW1shZUd%2B1jynpwK5%2FCkhfQsJcBZzEtNF1AAE7Vk%3Dreserved=0>",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A3306%2Fhive%3FcreateDatabaseIfNotExist%3Dtruedata=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C542d4ca08a144a749c4b08d6d943290b%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636935279803095071sdata=14rp9GerK1EDqFZNVcU5LCjw3v%2B%2BAE5TqbjUJmIBxow%3Dreserved=0>",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "test",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffs.default.namedata=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C542d4ca08a144a749c4b08d6d943290b%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636935279803095071sdata=d5uf1%2B2iNK5yl8O2BhIdcoEFU%2BgM04A5Inpp5gAz6Bs%3Dreserved=0>":
"file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> 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]#
RE: cant select from hive table
Bottom line is , how can I use apache drill with Hortonworks/Cloudera if the hive release that comes with them is not supported by drill ? -Original Message- From: Vova Vysotskyi [mailto:vvo...@gmail.com] Sent: Tuesday, May 28, 2019 1:19 PM To: user Subject: Re: cant select from hive table Hi Ahmad, Could you please clarify, which version (and vendor) of Hive and Drill you are using? Apache Drill 1.16 works fine with hive 2.3.2. There are possible some issues for other versions of Hive, so it is highly recommended to use this specific version. Kind regards, Volodymyr Vysotskyi On Tue, May 28, 2019 at 8:07 PM Ahmad, Sami wrote: > Any updates/advise on this please? > I really need to demo this apache drill to management . > > regards > > -Original Message----- > From: Ahmad, Sami > Sent: Monday, May 27, 2019 11:19 AM > To: user@drill.apache.org > Cc: Khurram Faraaz > Subject: RE: cant select from hive table > > None of the three worked > > apache drill (hive.default)> use hive.`default`; > +--+--+ > | ok | summary | > +--+--+ > | true | Default schema changed to [hive.default] | > +--+--+ > 1 row selected (0.086 seconds) > apache drill (hive.default)> select * from test; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: > Object 'test' not found > > > [Error Id: f4154d1a-59c3-4204-8186-67dcfaddf401 ] (state=,code=0) > apache drill (hive.default)> apache drill (hive.default)> apache drill > (hive.default)> !q > Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl > [root@hadoop1 bin]# sqlline -u jdbc:drill:zk=hadoop1:2181 Apache Drill > 1.16.0 "Think different, think Drill." > apache drill> select * from hive.`test`; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: > Object 'test' not found within 'hive' > > > [Error Id: 65a8acce-19b4-47f3-babb-ca1dc8c80ce9 ] (state=,code=0) > apache > drill> apache drill> use hive; > +--+--+ > | ok | summary | > +--+--+ > | true | Default schema changed to [hive] | > +--+--+ > 1 row selected (0.088 seconds) > apache drill (hive)> select * from test; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: > Object 'test' not found > > > [Error Id: ebf13f9c-16c9-4990-b72a-ba74e8348753 ] (state=,code=0) > > -Original Message- > From: Igor Guzenko [mailto:ihor.huzenko@gmail.com] > Sent: Monday, May 27, 2019 11:02 AM > To: user@drill.apache.org > Cc: Khurram Faraaz > Subject: Re: cant select from hive table > > Hello Khurram, > > If Hive storage based authorization is enabled for your deployment > it's may be the reason why you're getting such messages. > In the test it's visible that when at storage level user don't have > rights to query table files Drill shows the confusing error. Here is > the test > > https://gcc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgith > ub.com%2Fapache%2Fdrill%2Fblob%2Fmaster%2Fcontrib%2Fstorage-hive%2Fcor > e%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fdrill%2Fexec%2Fimpersonation%2F > hive%2FTestStorageBasedHiveAuthorization.javadata=02%7C01%7CSami. > Ahmad%40dot.state.fl.us%7C24a0511567e04ec922dd08d6e3909da1%7Cdb21de5db > c9c420c8f3f8f08f85b5ada%7C0%7C0%7C636946607590626058sdata=AfHJUft > 2lS9W8EOniKJ9sF%2FovRY%2Bj4UTUqVDqqixML8%3Dreserved=0 > . > > If I didn't guess and security isn't enabled, then you could just try: > > 1) Two separate queries: > use hive.`default`; > select * from test; > > OR > > 2) From root schema > select * from hive.`test`; > > OR > > 3) Two separate queries: > use hive; > select * from test; > > If previous queries don't work for not secured deployment it's a bug. > > Thanks, Igor > > On Mon, May 27, 2019 at 3:27 PM Ahmad, Sami > > wrote: > > > > Still not working : > > > > apache drill> use hive.`default`; > > +--+--+ > > | ok | summary | > > +--+--+ > > | true | Default schema changed to [hive.default] | > > +--+--+ > > 1 row selected (0.089 seconds) > > apache drill (hive.default)> show tables; > > +--++ > > | TABLE_SCHEMA | TABLE_NAME | > > +--++ >
RE: cant select from hive table
Any updates/advise on this please? I really need to demo this apache drill to management . regards -Original Message- From: Ahmad, Sami Sent: Monday, May 27, 2019 11:19 AM To: user@drill.apache.org Cc: Khurram Faraaz Subject: RE: cant select from hive table None of the three worked apache drill (hive.default)> use hive.`default`; +--+--+ | ok | summary | +--+--+ | true | Default schema changed to [hive.default] | +--+--+ 1 row selected (0.086 seconds) apache drill (hive.default)> select * from test; Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object 'test' not found [Error Id: f4154d1a-59c3-4204-8186-67dcfaddf401 ] (state=,code=0) apache drill (hive.default)> apache drill (hive.default)> apache drill (hive.default)> !q Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl [root@hadoop1 bin]# sqlline -u jdbc:drill:zk=hadoop1:2181 Apache Drill 1.16.0 "Think different, think Drill." apache drill> select * from hive.`test`; Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object 'test' not found within 'hive' [Error Id: 65a8acce-19b4-47f3-babb-ca1dc8c80ce9 ] (state=,code=0) apache drill> apache drill> use hive; +--+--+ | ok | summary | +--+--+ | true | Default schema changed to [hive] | +--+--+ 1 row selected (0.088 seconds) apache drill (hive)> select * from test; Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object 'test' not found [Error Id: ebf13f9c-16c9-4990-b72a-ba74e8348753 ] (state=,code=0) -Original Message- From: Igor Guzenko [mailto:ihor.huzenko@gmail.com] Sent: Monday, May 27, 2019 11:02 AM To: user@drill.apache.org Cc: Khurram Faraaz Subject: Re: cant select from hive table Hello Khurram, If Hive storage based authorization is enabled for your deployment it's may be the reason why you're getting such messages. In the test it's visible that when at storage level user don't have rights to query table files Drill shows the confusing error. Here is the test https://gcc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fdrill%2Fblob%2Fmaster%2Fcontrib%2Fstorage-hive%2Fcore%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fdrill%2Fexec%2Fimpersonation%2Fhive%2FTestStorageBasedHiveAuthorization.javadata=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C913283ea621c44fa549508d6e2b46364%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C1%7C636945661720971215sdata=41utoQOJI01U0Dvc6bDZDLBL6UpoQHCr6sXK3AtWzCc%3Dreserved=0 . If I didn't guess and security isn't enabled, then you could just try: 1) Two separate queries: use hive.`default`; select * from test; OR 2) From root schema select * from hive.`test`; OR 3) Two separate queries: use hive; select * from test; If previous queries don't work for not secured deployment it's a bug. Thanks, Igor On Mon, May 27, 2019 at 3:27 PM Ahmad, Sami wrote: > > Still not working : > > apache drill> use hive.`default`; > +--+--+ > | ok | summary | > +--+--+ > | true | Default schema changed to [hive.default] | > +--+--+ > 1 row selected (0.089 seconds) > apache drill (hive.default)> show tables; > +--++ > | TABLE_SCHEMA | TABLE_NAME | > +--++ > | hive.default | test | > +--++ > 1 row selected (0.188 seconds) > apache drill (hive.default)> select * from hive.`default`.test; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: Object > 'test' not found within 'hive.default' > > > [Error Id: 6b24df4c-3678-4bf4-94eb-8dd5d9910213 ] (state=,code=0) > apache drill (hive.default)> > > -Original Message- > From: Arina Yelchiyeva [mailto:arina.yelchiy...@gmail.com] > Sent: Friday, May 24, 2019 4:34 PM > To: user@drill.apache.org > Cc: Khurram Faraaz > Subject: Re: cant select from hive table > > EXTERNAL SENDER: Use caution with links and attachments. > > > Default is keyword, use it back-ticks (select * from hive.`default`.test). > > > On May 24, 2019, at 10:59 PM, Ahmad, Sami > > wrote: > > > > As you can see , the show table command sees the table but select command > > cant find it . > > > > pache drill (hive.default)> show tables; > > +--++ > > | TABLE_SCHEMA | TABLE_NAME | > > +--++ > &
RE: cant select from hive table
I am using hortonworks HDP 3.0 which comes with hive 3.1.0 I am using apache drill 1.16 regards -Original Message- From: Vova Vysotskyi [mailto:vvo...@gmail.com] Sent: Tuesday, May 28, 2019 1:19 PM To: user Subject: Re: cant select from hive table Hi Ahmad, Could you please clarify, which version (and vendor) of Hive and Drill you are using? Apache Drill 1.16 works fine with hive 2.3.2. There are possible some issues for other versions of Hive, so it is highly recommended to use this specific version. Kind regards, Volodymyr Vysotskyi On Tue, May 28, 2019 at 8:07 PM Ahmad, Sami wrote: > Any updates/advise on this please? > I really need to demo this apache drill to management . > > regards > > -Original Message----- > From: Ahmad, Sami > Sent: Monday, May 27, 2019 11:19 AM > To: user@drill.apache.org > Cc: Khurram Faraaz > Subject: RE: cant select from hive table > > None of the three worked > > apache drill (hive.default)> use hive.`default`; > +--+--+ > | ok | summary | > +--+--+ > | true | Default schema changed to [hive.default] | > +--+--+ > 1 row selected (0.086 seconds) > apache drill (hive.default)> select * from test; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: > Object 'test' not found > > > [Error Id: f4154d1a-59c3-4204-8186-67dcfaddf401 ] (state=,code=0) > apache drill (hive.default)> apache drill (hive.default)> apache drill > (hive.default)> !q > Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl > [root@hadoop1 bin]# sqlline -u jdbc:drill:zk=hadoop1:2181 Apache Drill > 1.16.0 "Think different, think Drill." > apache drill> select * from hive.`test`; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: > Object 'test' not found within 'hive' > > > [Error Id: 65a8acce-19b4-47f3-babb-ca1dc8c80ce9 ] (state=,code=0) > apache > drill> apache drill> use hive; > +--+--+ > | ok | summary | > +--+--+ > | true | Default schema changed to [hive] | > +--+--+ > 1 row selected (0.088 seconds) > apache drill (hive)> select * from test; > Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 18: > Object 'test' not found > > > [Error Id: ebf13f9c-16c9-4990-b72a-ba74e8348753 ] (state=,code=0) > > -Original Message- > From: Igor Guzenko [mailto:ihor.huzenko@gmail.com] > Sent: Monday, May 27, 2019 11:02 AM > To: user@drill.apache.org > Cc: Khurram Faraaz > Subject: Re: cant select from hive table > > Hello Khurram, > > If Hive storage based authorization is enabled for your deployment > it's may be the reason why you're getting such messages. > In the test it's visible that when at storage level user don't have > rights to query table files Drill shows the confusing error. Here is > the test > > https://gcc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgith > ub.com%2Fapache%2Fdrill%2Fblob%2Fmaster%2Fcontrib%2Fstorage-hive%2Fcor > e%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fdrill%2Fexec%2Fimpersonation%2F > hive%2FTestStorageBasedHiveAuthorization.javadata=02%7C01%7CSami. > Ahmad%40dot.state.fl.us%7C24a0511567e04ec922dd08d6e3909da1%7Cdb21de5db > c9c420c8f3f8f08f85b5ada%7C0%7C0%7C636946607590626058sdata=AfHJUft > 2lS9W8EOniKJ9sF%2FovRY%2Bj4UTUqVDqqixML8%3Dreserved=0 > . > > If I didn't guess and security isn't enabled, then you could just try: > > 1) Two separate queries: > use hive.`default`; > select * from test; > > OR > > 2) From root schema > select * from hive.`test`; > > OR > > 3) Two separate queries: > use hive; > select * from test; > > If previous queries don't work for not secured deployment it's a bug. > > Thanks, Igor > > On Mon, May 27, 2019 at 3:27 PM Ahmad, Sami > > wrote: > > > > Still not working : > > > > apache drill> use hive.`default`; > > +--+--+ > > | ok | summary | > > +--+--+ > > | true | Default schema changed to [hive.default] | > > +--+--+ > > 1 row selected (0.089 seconds) > > apache drill (hive.default)> show tables; > > +--++ > > | TABLE_SCHEMA | TABLE_NAME | > > +--++ > > | hive.default | test
help needed
hi I need help in apache drill and Hbase and hive , as I could not get drill to work with either , what are my options ? Regards Sami
RE: WELCOME to user@drill.apache.org
Hi Farraz
I am not sure what to put for the following since I am not using derby db. For
hive metastore I use mysql database.
jdbc:derby:;databaseName=
-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.
> > +--
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.Abstra
> ctFuture.getDoneValue(AbstractFuture.java:502)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abstra
> ctFuture.get(AbstractFuture.java:461)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abstra
> 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.Uninte
> rruptibles.getUninterruptibly(Uninterruptibles.java:142)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segme
> nt.getAndRecordStats(LocalCache.java:2453)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segme
> 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
> maili
RE: WELCOME to user@drill.apache.org
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.Abstra
> ctFuture.getDoneValue(AbstractFuture.java:502)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abstra
> ctFuture.get(AbstractFuture.java:461)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.util.concurrent.Abstra
> 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.Uninte
> rruptibles.getUninterruptibly(Uninterruptibles.java:142)
> ~[drill-shaded-guava-23.0.jar:23.0]
> at
> org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segme
> nt.getAndRecordStats(LocalCache.java:245
RE: WELCOME to user@drill.apache.org
Here is the drill website , showing how to make a thrift connection , and I am
doing exactly the same.
I understand that "derby" is the default embedded meta store but we don’t use
it . we use remote mysql database as metastore which we connect to using
thrift .
Regards
[cid:image001.png@01D50A6A.689DF090]
-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
mailto:sami.ah...@dot.state.fl.us>>
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<mailto:user@drill.apache.org>
> Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
> Subject: RE: WELCOME to user@drill.apache.org<mailto: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<mailto:user@drill.apache.org>
> Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
> Subject: Re: WELCOME to user@drill.apache.org<mailto: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
> mailto:sami.ah...@dot.state.fl.us>>
> 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 |
&
RE: WELCOME to user@drill.apache.org
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.AbstractFuture.getDoneValue(AbstractFuture.java:502) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:461) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.util.concurrent.AbstractFuture$TrustedFuture.get(AbstractFuture.java:83) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.util.concurrent.Uninterruptibles.getUninterruptibly(Uninterruptibles.java:142) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2453) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segment.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
RE: WELCOME to user@drill.apache.org
Adding drill-dev -Original Message- From: Ahmad, Sami Sent: Tuesday, May 14, 2019 1:08 PM To: user-h...@drill.apache.org Cc: user@drill.apache.org Subject: RE: WELCOME to user@drill.apache.org 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.AbstractFuture.getDoneValue(AbstractFuture.java:502) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:461) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.util.concurrent.AbstractFuture$TrustedFuture.get(AbstractFuture.java:83) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.util.concurrent.Uninterruptibles.getUninterruptibly(Uninterruptibles.java:142) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2453) ~[drill-shaded-guava-23.0.jar:23.0] at org.apache.drill.shaded.guava.com.google.common.cache.LocalCache$Segment.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.doma
RE: WELCOME to user@drill.apache.org
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|
> &
RE: WELCOME to user@drill.apache.org
I upgraded my hive to hive3.0 and using apache drill 1.16 but now hive storage
plugin is not liking the config file
It says “please rety: Error while creating/update storage: null
Please help
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<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A9083=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D=0>",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A3306%2Fhive%3FcreateDatabaseIfNotExist%3Dtrue=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D=0>",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "test",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffs.default.name=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D=0>":
"file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> 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<mailto:kfar...@mapr.com>]
Sent: Tuesday, May 14, 2019 3:10 PM
To: user@drill.apache.org<mailto:user@drill.apache.org>
Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
Subject: Re: WELCOME to user@drill.apache.org<mailto: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
mailto:sami.ah...@dot.state.fl.us>>
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<mailto:sami.ah...@dot.state.fl.us>]
> Sent: Tuesday, May 14, 2019 2:25 PM
> To: user@drill.apache.org<mailto:user@drill.apache.org>
> Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
> Subject: RE: WELCOME to user@drill.apache.org<mailto:user@drill.apache.org>
>
> Hi Khurram
>
> 1- yes the show tables command work from hive shell
>
cant select from hive table
Hi
My hive storage plugin is working and I can see the hive tables but I can’t
select from them ,please advise
apache drill (hive.default)> show tables;
+--++
| TABLE_SCHEMA | TABLE_NAME |
+--++
| hive.default | test |
+--++
1 row selected (0.126 seconds)
apache drill (hive.default)> select id from hive.default.`test`;
Error: PARSE ERROR: Encountered ". default" at line 1, column 20.
Was expecting one of:
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," ...
"OUTER" ...
"(" ...
"MATCH_RECOGNIZE" ...
"." ...
...
...
...
...
...
"TABLESAMPLE" ...
"WHERE" ...
"GROUP" ...
"HAVING" ...
"WINDOW" ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"MINUS" ...
"." ...
"." ...
"." ...
"." ...
"." ...
"." "*" ...
"[" ...
SQL Query select id from hive.default.`test`
^
[Error Id: 96ad1704-8b95-42b7-913c-b62d3d2c5782 ] (state=,code=0)
apache drill (hive.default)>
RE: WELCOME to user@drill.apache.org
Hi Khurram the hive plugin is working now . I used the following configuration
{
"type": "hive",
"configProps": {
"hive.metastore.uris": "thrift://hadoop2:9083",
"hive.metastore.sasl.enabled": "false",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name": "hdfs:///"
},
"enabled": true
}
Thanks a lot for your help
Regards
Sami
From: Khurram Faraaz [mailto:kfar...@mapr.com]
Sent: Thursday, May 23, 2019 6:01 PM
To: Ahmad, Sami ; user@drill.apache.org
Subject: Re: WELCOME to user@drill.apache.org
The suggestion was to upgrade to Hive 2.x ( preferable Hive 2.3 ) and not Hive
3.0
You should try with drill 1.16.0 and Hive 2.3
Thanks,
Khurram
On Thu, May 23, 2019 at 2:26 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> wrote:
I upgraded my hive to hive3.0 and using apache drill 1.16 but now hive storage
plugin is not liking the config file
It says “please rety: Error while creating/update storage: null
Please help
From: Khurram Faraaz [mailto:kfar...@mapr.com<mailto:kfar...@mapr.com>]
Sent: Tuesday, May 14, 2019 6:35 PM
To: user@drill.apache.org<mailto:user@drill.apache.org>; Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>>
Subject: Re: WELCOME to user@drill.apache.org<mailto: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<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A9083=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7Cdadaa91c78ce41c3f8c608d6dfca2cf8%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636942456772805364=3Dh77jKZ566rKsOZr0WQW4v2WOVAnYaeNWxcFRLImO0%3D=0>",
"javax.jdo.option.ConnectionURL":
"jdbc:mysql://1.2.10.10:3306/hive?createDatabaseIfNotExist=true<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2F1.2.10.10%3A3306%2Fhive%3FcreateDatabaseIfNotExist%3Dtrue=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7Cdadaa91c78ce41c3f8c608d6dfca2cf8%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636942456772815351=owv4eRnhoSOaS4ieiorqSJTP%2Fa3CxTx1%2ByN%2BiA1EWQE%3D=0>",
"javax.jdo.option.ConnectionUserName": "root",
"javax.jdo.option.ConnectionPassword": "test",
"hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
"fs.default.name<https://gcc01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffs.default.name=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7Cdadaa91c78ce41c3f8c608d6dfca2cf8%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636942456772815351=ZFK7f4wH4d40JBS4dXMwIRz1s%2FYaMsvkUgmbCL%2FHSKc%3D=0>":
"file:///",
"hive.metastore.sasl.enabled": "true",
"datanucleus.schema.autoCreateAll": "true"
},
"enabled": true
}
On Tue, May 14, 2019 at 1:53 PM Ahmad, Sami
mailto:sami.ah...@dot.state.fl.us>> 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<mailto:kfar...@mapr.com>]
Sent: Tuesday, May 14, 2019 3:10 PM
To: user@drill.apache.org<mailto:user@drill.apache.org>
Cc: user-h...@drill.apache.org<mailto:user-h...@drill.apache.org>
Subject: Re: WELCOME to user@drill.apache.org<mailto: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
