You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mich Talebzadeh <mi...@gmail.com> on 2016/07/31 09:18:14 UTC
Analytical function works in Spark SQL but not in Hive 2 QL
This query works OK in Spark 1.6.1.
Find the row for the last (datetime) entry in table where description
contains 'XYZ'
scala> sqltext = """
| select *
| from (select transactiondate, transactiondescription, debitamount
| , rank() over (order by transactiondate desc) r
| from ll_18740868 where transactiondescription like '%XYZ%'
| ) inner
| where r=1
| """
scala> HiveContext.sql(sqltext).show
+---------------+----------------------+-----------+---+
|transactiondate|transactiondescription|debitamount| r|
+---------------+----------------------+-----------+---+
| 2015-12-15| XYZ LTD CD 4636 | 10.95| 1|
+---------------+----------------------+-----------+---+
However, the same SQL query on the same table in Hive 2 fails:
hive> select *
> from (select transactiondate, transactiondescription, debitamount
> from (select transactiondate, transactiondescription, debitamount
> , rank() over (order by transactiondate desc) r
> from ll_18740868 where transactiondescription like '%XYZ%'
> ) inner
> where r=1
> ;
FailedPredicateException(identifier,{useSQL11ReservedKeywordsForIdentifier()}?)
at
org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:11833)
at
org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:47987)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.subQuerySource(HiveParser_FromClauseParser.java:5520)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource0(HiveParser_FromClauseParser.java:3918)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:3818)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:1909)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1546)
at
org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:48001)
at
org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:42252)
at
org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:42138)
at
org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:41154)
at
org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:41024)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.subQuerySource(HiveParser_FromClauseParser.java:5492)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource0(HiveParser_FromClauseParser.java:3918)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:3818)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:1909)
at
org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1546)
at
org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:48001)
at
org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:42252)
at
org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:42138)
at
org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:41154)
at
org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:41024)
at
org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1653)
at
org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1137)
at
org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:204)
at
org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:446)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:319)
at
org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1255)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1301)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1184)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1172)
at
org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:233)
at
org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:184)
at
org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:400)
at
org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:778)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:717)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:645)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
*FAILED: ParseException line 6:7 Failed to recognize predicate 'inner'.
Failed rule: 'identifier' in subquery source*
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
Re: Analytical function works in Spark SQL but not in Hive 2 QL
Posted by Mich Talebzadeh <mi...@gmail.com>.
Sorted it was reserved word issue inner.
hive> select *
> from (select transactiondate, transactiondescription, debitamount
> , rank() over (order by transactiondate desc) r
> from accounts.ll_18740868 where transactiondescription like
'%HARRODS%'
> ) RS
> where r=1
> ;
Query ID = hduser_20160731104724_f8e5f426-770a-49fc-a4a5-f0f645c06e8c
Total jobs = 1
Launching Job 1 out of 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
Starting Spark Job = 7727d5df-ccf9-4f98-8563-1cdec2634d99
Query Hive on Spark job[0] stages:
0
1
Status: Running (Hive on Spark job[0])
Job Progress Format
CurrentTime StageId_StageAttemptId:
SucceededTasksCount(+RunningTasksCount-FailedTasksCount)/TotalTasksCount
[StageCost]
2016-07-31 10:48:28,726 Stage-0_0: 0/1 Stage-1_0: 0/1
2016-07-31 10:48:31,750 Stage-0_0: 0/1 Stage-1_0: 0/1
2016-07-31 10:48:32,758 Stage-0_0: 0(+1)/1 Stage-1_0: 0/1
2016-07-31 10:48:34,772 Stage-0_0: 1/1 Finished Stage-1_0: 0(+1)/1
2016-07-31 10:48:35,780 Stage-0_0: 1/1 Finished Stage-1_0: 1/1 Finished
Status: Finished successfully in 10.10 seconds
OK
2015-12-15 HARRODS LTD CD 4636 10.95 1
Time taken: 46.546 seconds, Fetched: 1 row(s)
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 31 July 2016 at 10:18, Mich Talebzadeh <mi...@gmail.com> wrote:
>
> This query works OK in Spark 1.6.1.
>
> Find the row for the last (datetime) entry in table where description
> contains 'XYZ'
>
> scala> sqltext = """
> | select *
> | from (select transactiondate, transactiondescription, debitamount
> | , rank() over (order by transactiondate desc) r
> | from ll_18740868 where transactiondescription like '%XYZ%'
> | ) inner
> | where r=1
> | """
>
> scala> HiveContext.sql(sqltext).show
> +---------------+----------------------+-----------+---+
> |transactiondate|transactiondescription|debitamount| r|
> +---------------+----------------------+-----------+---+
> | 2015-12-15| XYZ LTD CD 4636 | 10.95| 1|
> +---------------+----------------------+-----------+---+
>
> However, the same SQL query on the same table in Hive 2 fails:
>
> hive> select *
> > from (select transactiondate, transactiondescription, debitamount
> > from (select transactiondate, transactiondescription, debitamount
> > , rank() over (order by transactiondate desc) r
> > from ll_18740868 where transactiondescription like '%XYZ%'
> > ) inner
> > where r=1
> > ;
>
> FailedPredicateException(identifier,{useSQL11ReservedKeywordsForIdentifier()}?)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:11833)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:47987)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.subQuerySource(HiveParser_FromClauseParser.java:5520)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource0(HiveParser_FromClauseParser.java:3918)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:3818)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:1909)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1546)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:48001)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:42252)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:42138)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:41154)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:41024)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.subQuerySource(HiveParser_FromClauseParser.java:5492)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource0(HiveParser_FromClauseParser.java:3918)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:3818)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:1909)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1546)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:48001)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:42252)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:42138)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:41154)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:41024)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1653)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1137)
> at
> org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:204)
> at
> org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:446)
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:319)
> at
> org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1255)
> at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1301)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1184)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1172)
> at
> org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:233)
> at
> org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:184)
> at
> org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:400)
> at
> org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:778)
> at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:717)
> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:645)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
> at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
> *FAILED: ParseException line 6:7 Failed to recognize predicate 'inner'.
> Failed rule: 'identifier' in subquery source*
>
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>