Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> cross join


Copy link to this message
-
Re: cross join
Periya,
I am using Hive-0.8.1 and was able to get joins to work with:
1. No on clause
2. "on (true)" as the on clause to work.

I am not entirely sure why you are getting exceptions with the above
queries. Perhaps, there were some bugs in 0.7.1 that got resolved in 0.8.1?

As far as workarounds go, you could try having each table in a sub-query
and have an extra virtual column ("1 as one") and join on that virtual
column.

Mark

On Mon, Dec 3, 2012 at 9:26 AM, Periya.Data <[EMAIL PROTECTED]> wrote:

> Hi Hive users,
>     I have Hive CDH - 0.7.1. I want to know if I can do cross-join with
> this version. I am aware that Cross-Join is fixed in version 0.10  (see:
> https://issues.apache.org/jira/browse/HIVE-2549 ). Is there a way to do
> it in earlier version? Here is a simple example I am trying:
>
> Your suggestions are much appreciated.
>
> bash-3.2$ *hive -e "select * from tbl_A;"*
> Hive history
> file=/tmp/ttkera/hive_job_log_ttkera_201212031702_1010080644.txt
> OK
> 123
> 456
> 789
> Time taken: 4.108 seconds
> bash-3.2$
> bash-3.2$
> bash-3.2$* hive -e "select * from tbl_B;"*
> Hive history
> file=/tmp/ttkera/hive_job_log_ttkera_201212031703_1024384290.txt
> OK
> A
> B
> C
> D
> Time taken: 1.058 seconds
> bash-3.2$
>
>
> [from Ashish Thusoo's earlier suggestion:]
>
> bash-3.2$ *hive -e "select tbl_A.*, tbl_B.* from tbl_A join tbl_B on (1 > 1);"*
> Hive history
> file=/tmp/ttkera/hive_job_log_ttkera_201212031704_162672418.txt
> FAILED: Hive Internal Error: java.lang.NullPointerException(null)
> java.lang.NullPointerException
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1353)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.pushJoinFilters(SemanticAnalyzer.java:4727)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6013)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:6606)
>         at
> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:238)
>         at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:340)
>         at org.apache.hadoop.hive.ql.Driver.run(Driver.java:736)
>         at
> org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:209)
>         at
> org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:286)
>         at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:485)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at org.apache.hadoop.util.RunJar.main(RunJar.java:186)
>
> bash-3.2$
> bash-3.2$* hive -e "select tbl_A.*, tbl_B.* from tbl_A cross join tbl_B;"*
> Hive history
> file=/tmp/ttkera/hive_job_log_ttkera_201212031723_379116546.txt
> FAILED: Parse Error: line 1:35 mismatched input 'cross' expecting EOF near
> 'tbl_A'
>
> bash-3.2$
>
> bash-3.2$ *hive -e "select tbl_A.*, tbl_B.* from tbl_A join tbl_B;"*
> Hive history
> file=/tmp/ttkera/hive_job_log_ttkera_201212031703_1723109044.txt
> FAILED: Hive Internal Error: java.lang.NullPointerException(null)
> java.lang.NullPointerException
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinReduceSinkChild(SemanticAnalyzer.java:4443)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinOperator(SemanticAnalyzer.java:4548)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinPlan(SemanticAnalyzer.java:4707)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6014)
>         at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:6606)
>         at
> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:238)