You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by "Ahmad, Sami" <Sa...@dot.state.fl.us> on 2019/05/24 15:35:23 UTC
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:
<EOF>
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," ...
"OUTER" ...
"(" ...
"MATCH_RECOGNIZE" ...
"." ...
<IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<BRACKET_QUOTED_IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
"TABLESAMPLE" ...
"WHERE" ...
"GROUP" ...
"HAVING" ...
"WINDOW" ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"MINUS" ...
"." <IDENTIFIER> ...
"." <QUOTED_IDENTIFIER> ...
"." <BACK_QUOTED_IDENTIFIER> ...
"." <BRACKET_QUOTED_IDENTIFIER> ...
"." <UNICODE_QUOTED_IDENTIFIER> ...
"." "*" ...
"[" ...
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
Posted by "Ahmad, Sami" <Sa...@dot.state.fl.us>.
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:vvovyk@gmail.com]
Sent: Tuesday, May 28, 2019 1:19 PM
To: user <us...@drill.apache.org>
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 <Sa...@dot.state.fl.us>
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 <kf...@mapr.com>
> 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.igs@gmail.com]
> Sent: Monday, May 27, 2019 11:02 AM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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
> <Sa...@dot.state.fl.us>
> 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.yelchiyeva@gmail.com]
> > Sent: Friday, May 24, 2019 4:34 PM
> > To: user@drill.apache.org
> > Cc: Khurram Faraaz <kf...@mapr.com>
> > 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
> > > <Sa...@dot.state.fl.us>
> 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 <kf...@mapr.com>; 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:
> > > <EOF>
> > > "ORDER" ...
> > > "LIMIT" ...
> > > "OFFSET" ...
> > > "FETCH" ...
> > > "NATURAL" ...
> > > "JOIN" ...
> > > "INNER" ...
> > > "LEFT" ...
> > > "RIGHT" ...
> > > "FULL" ...
> > > "CROSS" ...
> > > "," ...
> > > "OUTER" ...
> > > "(" ...
> > > "MATCH_RECOGNIZE" ...
> > > "." ...
> > > <IDENTIFIER> ...
> > > <QUOTED_IDENTIFIER> ...
> > > <BACK_QUOTED_IDENTIFIER> ...
> > > <BRACKET_QUOTED_IDENTIFIER> ...
> > > <UNICODE_QUOTED_IDENTIFIER> ...
> > > "TABLESAMPLE" ...
> > > "WHERE" ...
> > > "GROUP" ...
> > > "HAVING" ...
> > > "WINDOW" ...
> > > "UNION" ...
> > > "INTERSECT" ...
> > > "EXCEPT" ...
> > > "MINUS" ...
> > > "." <IDENTIFIER> ...
> > > "." <QUOTED_IDENTIFIER> ...
> > > "." <BACK_QUOTED_IDENTIFIER> ...
> > > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > > "." "*" ...
> > > "[" ...
> > >
> > >
> > > 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
Posted by Paul Rogers <pa...@yahoo.com.INVALID>.
Hi Ahmad,
Just to pop in here... Drill has Maven build rules for the CDH platform, but they may not have been updated for recent releases. CDH upgraded lots of Hadoop components in the CDH 6 release. Drill tends to be built with the earlier Apache versions, such as those in CDH 5. Creating a consistent build of Hadoop components is a non-trivial task.
Most of the Drill team does not actually have access to the latest CDH or HDP releases. Drill is an open source project and is always looking for people who can port Drill to various commercial distributions.
Would be great if the Drill community could help out in this area. Has anyone built Drill against the latest CDH who can contribute their work back to Drill? Anyone done tests to learn which CDH 5.x or 6.x versions Drill does work with?
For CDH, one challenge will be that Drill is not installed via Cloudera Manager (CM), so some manual work will be needed to copy the required config and/or files from the CM deployment to Drill's own config directory. Would be great if someone who has done that could write up the steps and share them with the community.
Thanks,
- Paul
On Tuesday, May 28, 2019, 11:54:41 AM PDT, Ahmad, Sami <Sa...@dot.state.fl.us> wrote:
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:vvovyk@gmail.com]
Sent: Tuesday, May 28, 2019 1:19 PM
To: user <us...@drill.apache.org>
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 <Sa...@dot.state.fl.us>
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 <kf...@mapr.com>
> 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.igs@gmail.com]
> Sent: Monday, May 27, 2019 11:02 AM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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
> <Sa...@dot.state.fl.us>
> 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.yelchiyeva@gmail.com]
> > Sent: Friday, May 24, 2019 4:34 PM
> > To: user@drill.apache.org
> > Cc: Khurram Faraaz <kf...@mapr.com>
> > 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
> > > <Sa...@dot.state.fl.us>
> 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 <kf...@mapr.com>; 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:
> > > <EOF>
> > > "ORDER" ...
> > > "LIMIT" ...
> > > "OFFSET" ...
> > > "FETCH" ...
> > > "NATURAL" ...
> > > "JOIN" ...
> > > "INNER" ...
> > > "LEFT" ...
> > > "RIGHT" ...
> > > "FULL" ...
> > > "CROSS" ...
> > > "," ...
> > > "OUTER" ...
> > > "(" ...
> > > "MATCH_RECOGNIZE" ...
> > > "." ...
> > > <IDENTIFIER> ...
> > > <QUOTED_IDENTIFIER> ...
> > > <BACK_QUOTED_IDENTIFIER> ...
> > > <BRACKET_QUOTED_IDENTIFIER> ...
> > > <UNICODE_QUOTED_IDENTIFIER> ...
> > > "TABLESAMPLE" ...
> > > "WHERE" ...
> > > "GROUP" ...
> > > "HAVING" ...
> > > "WINDOW" ...
> > > "UNION" ...
> > > "INTERSECT" ...
> > > "EXCEPT" ...
> > > "MINUS" ...
> > > "." <IDENTIFIER> ...
> > > "." <QUOTED_IDENTIFIER> ...
> > > "." <BACK_QUOTED_IDENTIFIER> ...
> > > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > > "." "*" ...
> > > "[" ...
> > >
> > >
> > > 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
Posted by "Ahmad, Sami" <Sa...@dot.state.fl.us>.
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:vvovyk@gmail.com]
Sent: Tuesday, May 28, 2019 1:19 PM
To: user <us...@drill.apache.org>
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 <Sa...@dot.state.fl.us>
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 <kf...@mapr.com>
> 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.igs@gmail.com]
> Sent: Monday, May 27, 2019 11:02 AM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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
> <Sa...@dot.state.fl.us>
> 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.yelchiyeva@gmail.com]
> > Sent: Friday, May 24, 2019 4:34 PM
> > To: user@drill.apache.org
> > Cc: Khurram Faraaz <kf...@mapr.com>
> > 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
> > > <Sa...@dot.state.fl.us>
> 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 <kf...@mapr.com>; 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:
> > > <EOF>
> > > "ORDER" ...
> > > "LIMIT" ...
> > > "OFFSET" ...
> > > "FETCH" ...
> > > "NATURAL" ...
> > > "JOIN" ...
> > > "INNER" ...
> > > "LEFT" ...
> > > "RIGHT" ...
> > > "FULL" ...
> > > "CROSS" ...
> > > "," ...
> > > "OUTER" ...
> > > "(" ...
> > > "MATCH_RECOGNIZE" ...
> > > "." ...
> > > <IDENTIFIER> ...
> > > <QUOTED_IDENTIFIER> ...
> > > <BACK_QUOTED_IDENTIFIER> ...
> > > <BRACKET_QUOTED_IDENTIFIER> ...
> > > <UNICODE_QUOTED_IDENTIFIER> ...
> > > "TABLESAMPLE" ...
> > > "WHERE" ...
> > > "GROUP" ...
> > > "HAVING" ...
> > > "WINDOW" ...
> > > "UNION" ...
> > > "INTERSECT" ...
> > > "EXCEPT" ...
> > > "MINUS" ...
> > > "." <IDENTIFIER> ...
> > > "." <QUOTED_IDENTIFIER> ...
> > > "." <BACK_QUOTED_IDENTIFIER> ...
> > > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > > "." "*" ...
> > > "[" ...
> > >
> > >
> > > 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
Posted by Vova Vysotskyi <vv...@gmail.com>.
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 <Sa...@dot.state.fl.us>
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 <kf...@mapr.com>
> 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.igs@gmail.com]
> Sent: Monday, May 27, 2019 11:02 AM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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 <Sa...@dot.state.fl.us>
> 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.yelchiyeva@gmail.com]
> > Sent: Friday, May 24, 2019 4:34 PM
> > To: user@drill.apache.org
> > Cc: Khurram Faraaz <kf...@mapr.com>
> > 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 <Sa...@dot.state.fl.us>
> 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 <kf...@mapr.com>; 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:
> > > <EOF>
> > > "ORDER" ...
> > > "LIMIT" ...
> > > "OFFSET" ...
> > > "FETCH" ...
> > > "NATURAL" ...
> > > "JOIN" ...
> > > "INNER" ...
> > > "LEFT" ...
> > > "RIGHT" ...
> > > "FULL" ...
> > > "CROSS" ...
> > > "," ...
> > > "OUTER" ...
> > > "(" ...
> > > "MATCH_RECOGNIZE" ...
> > > "." ...
> > > <IDENTIFIER> ...
> > > <QUOTED_IDENTIFIER> ...
> > > <BACK_QUOTED_IDENTIFIER> ...
> > > <BRACKET_QUOTED_IDENTIFIER> ...
> > > <UNICODE_QUOTED_IDENTIFIER> ...
> > > "TABLESAMPLE" ...
> > > "WHERE" ...
> > > "GROUP" ...
> > > "HAVING" ...
> > > "WINDOW" ...
> > > "UNION" ...
> > > "INTERSECT" ...
> > > "EXCEPT" ...
> > > "MINUS" ...
> > > "." <IDENTIFIER> ...
> > > "." <QUOTED_IDENTIFIER> ...
> > > "." <BACK_QUOTED_IDENTIFIER> ...
> > > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > > "." "*" ...
> > > "[" ...
> > >
> > >
> > > 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
Posted by "Ahmad, Sami" <Sa...@dot.state.fl.us>.
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 <kf...@mapr.com>
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.igs@gmail.com]
Sent: Monday, May 27, 2019 11:02 AM
To: user@drill.apache.org
Cc: Khurram Faraaz <kf...@mapr.com>
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 <Sa...@dot.state.fl.us> 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.yelchiyeva@gmail.com]
> Sent: Friday, May 24, 2019 4:34 PM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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 <Sa...@dot.state.fl.us> 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 <kf...@mapr.com>; 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:
> > <EOF>
> > "ORDER" ...
> > "LIMIT" ...
> > "OFFSET" ...
> > "FETCH" ...
> > "NATURAL" ...
> > "JOIN" ...
> > "INNER" ...
> > "LEFT" ...
> > "RIGHT" ...
> > "FULL" ...
> > "CROSS" ...
> > "," ...
> > "OUTER" ...
> > "(" ...
> > "MATCH_RECOGNIZE" ...
> > "." ...
> > <IDENTIFIER> ...
> > <QUOTED_IDENTIFIER> ...
> > <BACK_QUOTED_IDENTIFIER> ...
> > <BRACKET_QUOTED_IDENTIFIER> ...
> > <UNICODE_QUOTED_IDENTIFIER> ...
> > "TABLESAMPLE" ...
> > "WHERE" ...
> > "GROUP" ...
> > "HAVING" ...
> > "WINDOW" ...
> > "UNION" ...
> > "INTERSECT" ...
> > "EXCEPT" ...
> > "MINUS" ...
> > "." <IDENTIFIER> ...
> > "." <QUOTED_IDENTIFIER> ...
> > "." <BACK_QUOTED_IDENTIFIER> ...
> > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > "." "*" ...
> > "[" ...
> >
> >
> > 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
Posted by "Ahmad, Sami" <Sa...@dot.state.fl.us>.
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.igs@gmail.com]
Sent: Monday, May 27, 2019 11:02 AM
To: user@drill.apache.org
Cc: Khurram Faraaz <kf...@mapr.com>
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 <Sa...@dot.state.fl.us> 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.yelchiyeva@gmail.com]
> Sent: Friday, May 24, 2019 4:34 PM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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 <Sa...@dot.state.fl.us> 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 <kf...@mapr.com>; 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:
> > <EOF>
> > "ORDER" ...
> > "LIMIT" ...
> > "OFFSET" ...
> > "FETCH" ...
> > "NATURAL" ...
> > "JOIN" ...
> > "INNER" ...
> > "LEFT" ...
> > "RIGHT" ...
> > "FULL" ...
> > "CROSS" ...
> > "," ...
> > "OUTER" ...
> > "(" ...
> > "MATCH_RECOGNIZE" ...
> > "." ...
> > <IDENTIFIER> ...
> > <QUOTED_IDENTIFIER> ...
> > <BACK_QUOTED_IDENTIFIER> ...
> > <BRACKET_QUOTED_IDENTIFIER> ...
> > <UNICODE_QUOTED_IDENTIFIER> ...
> > "TABLESAMPLE" ...
> > "WHERE" ...
> > "GROUP" ...
> > "HAVING" ...
> > "WINDOW" ...
> > "UNION" ...
> > "INTERSECT" ...
> > "EXCEPT" ...
> > "MINUS" ...
> > "." <IDENTIFIER> ...
> > "." <QUOTED_IDENTIFIER> ...
> > "." <BACK_QUOTED_IDENTIFIER> ...
> > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > "." "*" ...
> > "[" ...
> >
> >
> > 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
Posted by Igor Guzenko <ih...@gmail.com>.
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://github.com/apache/drill/blob/master/contrib/storage-hive/core/src/test/java/org/apache/drill/exec/impersonation/hive/TestStorageBasedHiveAuthorization.java
.
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 <Sa...@dot.state.fl.us> 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.yelchiyeva@gmail.com]
> Sent: Friday, May 24, 2019 4:34 PM
> To: user@drill.apache.org
> Cc: Khurram Faraaz <kf...@mapr.com>
> 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 <Sa...@dot.state.fl.us> 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 <kf...@mapr.com>; 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:
> > <EOF>
> > "ORDER" ...
> > "LIMIT" ...
> > "OFFSET" ...
> > "FETCH" ...
> > "NATURAL" ...
> > "JOIN" ...
> > "INNER" ...
> > "LEFT" ...
> > "RIGHT" ...
> > "FULL" ...
> > "CROSS" ...
> > "," ...
> > "OUTER" ...
> > "(" ...
> > "MATCH_RECOGNIZE" ...
> > "." ...
> > <IDENTIFIER> ...
> > <QUOTED_IDENTIFIER> ...
> > <BACK_QUOTED_IDENTIFIER> ...
> > <BRACKET_QUOTED_IDENTIFIER> ...
> > <UNICODE_QUOTED_IDENTIFIER> ...
> > "TABLESAMPLE" ...
> > "WHERE" ...
> > "GROUP" ...
> > "HAVING" ...
> > "WINDOW" ...
> > "UNION" ...
> > "INTERSECT" ...
> > "EXCEPT" ...
> > "MINUS" ...
> > "." <IDENTIFIER> ...
> > "." <QUOTED_IDENTIFIER> ...
> > "." <BACK_QUOTED_IDENTIFIER> ...
> > "." <BRACKET_QUOTED_IDENTIFIER> ...
> > "." <UNICODE_QUOTED_IDENTIFIER> ...
> > "." "*" ...
> > "[" ...
> >
> >
> > 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
Posted by "Ahmad, Sami" <Sa...@dot.state.fl.us>.
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.yelchiyeva@gmail.com]
Sent: Friday, May 24, 2019 4:34 PM
To: user@drill.apache.org
Cc: Khurram Faraaz <kf...@mapr.com>
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 <Sa...@dot.state.fl.us> 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 <kf...@mapr.com>; 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:
> <EOF>
> "ORDER" ...
> "LIMIT" ...
> "OFFSET" ...
> "FETCH" ...
> "NATURAL" ...
> "JOIN" ...
> "INNER" ...
> "LEFT" ...
> "RIGHT" ...
> "FULL" ...
> "CROSS" ...
> "," ...
> "OUTER" ...
> "(" ...
> "MATCH_RECOGNIZE" ...
> "." ...
> <IDENTIFIER> ...
> <QUOTED_IDENTIFIER> ...
> <BACK_QUOTED_IDENTIFIER> ...
> <BRACKET_QUOTED_IDENTIFIER> ...
> <UNICODE_QUOTED_IDENTIFIER> ...
> "TABLESAMPLE" ...
> "WHERE" ...
> "GROUP" ...
> "HAVING" ...
> "WINDOW" ...
> "UNION" ...
> "INTERSECT" ...
> "EXCEPT" ...
> "MINUS" ...
> "." <IDENTIFIER> ...
> "." <QUOTED_IDENTIFIER> ...
> "." <BACK_QUOTED_IDENTIFIER> ...
> "." <BRACKET_QUOTED_IDENTIFIER> ...
> "." <UNICODE_QUOTED_IDENTIFIER> ...
> "." "*" ...
> "[" ...
>
>
> 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
Posted by Arina Yelchiyeva <ar...@gmail.com>.
Default is keyword, use it back-ticks (select * from hive.`default`.test).
> On May 24, 2019, at 10:59 PM, Ahmad, Sami <Sa...@dot.state.fl.us> 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 <kf...@mapr.com>; 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:
> <EOF>
> "ORDER" ...
> "LIMIT" ...
> "OFFSET" ...
> "FETCH" ...
> "NATURAL" ...
> "JOIN" ...
> "INNER" ...
> "LEFT" ...
> "RIGHT" ...
> "FULL" ...
> "CROSS" ...
> "," ...
> "OUTER" ...
> "(" ...
> "MATCH_RECOGNIZE" ...
> "." ...
> <IDENTIFIER> ...
> <QUOTED_IDENTIFIER> ...
> <BACK_QUOTED_IDENTIFIER> ...
> <BRACKET_QUOTED_IDENTIFIER> ...
> <UNICODE_QUOTED_IDENTIFIER> ...
> "TABLESAMPLE" ...
> "WHERE" ...
> "GROUP" ...
> "HAVING" ...
> "WINDOW" ...
> "UNION" ...
> "INTERSECT" ...
> "EXCEPT" ...
> "MINUS" ...
> "." <IDENTIFIER> ...
> "." <QUOTED_IDENTIFIER> ...
> "." <BACK_QUOTED_IDENTIFIER> ...
> "." <BRACKET_QUOTED_IDENTIFIER> ...
> "." <UNICODE_QUOTED_IDENTIFIER> ...
> "." "*" ...
> "[" ...
>
>
> 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
Posted by "Ahmad, Sami" <Sa...@dot.state.fl.us>.
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 <kf...@mapr.com>; 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:
<EOF>
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"NATURAL" ...
"JOIN" ...
"INNER" ...
"LEFT" ...
"RIGHT" ...
"FULL" ...
"CROSS" ...
"," ...
"OUTER" ...
"(" ...
"MATCH_RECOGNIZE" ...
"." ...
<IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<BRACKET_QUOTED_IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
"TABLESAMPLE" ...
"WHERE" ...
"GROUP" ...
"HAVING" ...
"WINDOW" ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"MINUS" ...
"." <IDENTIFIER> ...
"." <QUOTED_IDENTIFIER> ...
"." <BACK_QUOTED_IDENTIFIER> ...
"." <BRACKET_QUOTED_IDENTIFIER> ...
"." <UNICODE_QUOTED_IDENTIFIER> ...
"." "*" ...
"[" ...
SQL Query select id from hive.default.`test`
^
[Error Id: 96ad1704-8b95-42b7-913c-b62d3d2c5782 ] (state=,code=0)
apache drill (hive.default)>