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.java&data=02%7C01%7CSami. > Ahmad%40dot.state.fl.us%7C24a0511567e04ec922dd08d6e3909da1%7Cdb21de5db > c9c420c8f3f8f08f85b5ada%7C0%7C0%7C636946607590626058&sdata=AfHJUft > 2lS9W8EOniKJ9sF%2FovRY%2Bj4UTUqVDqqixML8%3D&reserved=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; > > +--+--
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.java&data=02%7C01%7CSami. > Ahmad%40dot.state.fl.us%7C24a0511567e04ec922dd08d6e3909da1%7Cdb21de5db > c9c420c8f3f8f08f85b5ada%7C0%7C0%7C636946607590626058&sdata=AfHJUft > 2lS9W8EOniKJ9sF%2FovRY%2Bj4UTUqVDqqixML8%3D&reserved=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
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.java&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C913283ea621c44fa549508d6e2b46364%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C1%7C636945661720971215&sdata=41utoQOJI01U0Dvc6bDZDLBL6UpoQHCr6sXK3AtWzCc%3D&reserved=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; > > +---
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.java&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C913283ea621c44fa549508d6e2b46364%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C1%7C636945661720971215&sdata=41utoQOJI01U0Dvc6bDZDLBL6UpoQHCr6sXK3AtWzCc%3D&reserved=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
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: 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-tai&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C6928309cb92e4f80e4be08d6e274331d%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C1%7C636945386031005672&sdata=gnCPO0T%2FsGLEjXYx3ZfWvpKDY5A3bWEMXEUxRQbqPa0%3D&reserved=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
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%3A9083&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C542d4ca08a144a749c4b08d6d943290b%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636935279803095071&sdata=EcjrW1shZUd%2B1jynpwK5%2FCkhfQsJcBZzEtNF1AAE7Vk%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C542d4ca08a144a749c4b08d6d943290b%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636935279803095071&sdata=14rp9GerK1EDqFZNVcU5LCjw3v%2B%2BAE5TqbjUJmIBxow%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C542d4ca08a144a749c4b08d6d943290b%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636935279803095071&sdata=d5uf1%2B2iNK5yl8O2BhIdcoEFU%2BgM04A5Inpp5gAz6Bs%3D&reserved=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 t
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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7Cdadaa91c78ce41c3f8c608d6dfca2cf8%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636942456772805364&sdata=3Dh77jKZ566rKsOZr0WQW4v2WOVAnYaeNWxcFRLImO0%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7Cdadaa91c78ce41c3f8c608d6dfca2cf8%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636942456772815351&sdata=owv4eRnhoSOaS4ieiorqSJTP%2Fa3CxTx1%2ByN%2BiA1EWQE%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7Cdadaa91c78ce41c3f8c608d6dfca2cf8%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636942456772815351&sdata=ZFK7f4wH4d40JBS4dXMwIRz1s%2FYaMsvkUgmbCL%2FHSKc%3D&reserved=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
problems with HBASE storage plugin
I am not able to get hbase storage plugin to work . HIVE storage plugin is working good though. From hbase I can see the tables and access them fine. The plugin in I am using is : { "type": "hbase", "config": { "hbase.zookeeper.quorum": "hadoop1,hadoop2,hadoop3", "hbase.zookeeper.property.clientPort": "2181" }, "size.calculator.enabled": false, "enabled": true } The error I get is : 2019-05-24 10:53:34,306 [2317fa90-eda8-28fe-bd33-036278de11b9:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id 2317fa90-eda8-28fe-bd33-036278de11b9 issued by anonymous: show tables 2019-05-24 10:53:34,599 [2317fa90-eda8-28fe-bd33-036278de11b9:frag:0:0] INFO o.a.d.e.s.h.HBaseConnectionManager - HBase connection 'hconnection-0x877d882' created. 2019-05-24 10:55:43,404 [2317fa90-eda8-28fe-bd33-036278de11b9:frag:0:0] WARN o.a.d.e.s.hbase.HBaseSchemaFactory - Failure while loading table names for database 'hbase'. org.apache.hadoop.hbase.MasterNotRunningException: java.io.IOException: org.apache.zookeeper.KeeperException$NoNodeException: KeeperErrorCode = NoNode for /hbase/master at org.apache.hadoop.hbase.client.ConnectionImplementation$MasterServiceStubMaker.makeStub(ConnectionImplementation.java:1179) ~[hbase-client-2.1.1.jar:2.1.1]
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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822&sdata=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D&reserved=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.apac
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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822&sdata=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D&reserved=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 tab
RE: WELCOME to user@drill.apache.org
I have put this parameter to false since we are not using sasl "hive.metastore.sasl.enabled": "false", From: Ahmad, Sami Sent: Wednesday, May 15, 2019 10:39 AM To: Khurram Faraaz ; user@drill.apache.org Subject: 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<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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822&sdata=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D&reserved=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:sa
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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822&sdata=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D&reserved=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
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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424731822&sdata=I46ULqkp5zD12nOKgkzKAVxc%2FlLYt4u46faSLO7lZbg%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=amlo2YnFi9gW4elCLwblGP4ePemYmusxjX1PZKeL7Do%3D&reserved=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&data=02%7C01%7CSami.Ahmad%40dot.state.fl.us%7C0ce58726dc0d4eaca16308d6d8bc7cd2%7Cdb21de5dbc9c420c8f3f8f08f85b5ada%7C0%7C0%7C636934701424741817&sdata=FoLsiXTaLRhfm6pi7ZNmRJkm6LDziPRjMiFnkkw9hLg%3D&reserved=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
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
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
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
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(LocalCach
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.
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 &
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
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