hive与oracle,Hive与Oracle表关联语句对比

hive (default)> select * from a full outer join b on a.a1 = b.b1;

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks not specified. Estimated from input data size: 1

In order to change the average load for a reducer (in bytes):

set hive.exec.reducers.bytes.per.reducer=

In order to limit the maximum number of reducers:

set hive.exec.reducers.max=

In order to set a constant number of reducers:

set mapred.reduce.tasks=

Starting Job = job_201404251509_0134, Tracking URL = http://IP:50030/jobdetails.jsp?jobid=job_201404251509_0134

Kill Command = /home/Hadoop/package/hadoop-1.0.4/libexec/../bin/hadoop job  -kill job_201404251509_0134

Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1

2014-04-29 09:24:12,572 Stage-1 map = 0%,  reduce = 0%

2014-04-29 09:24:18,599 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 1.52 sec

2014-04-29 09:24:19,607 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 1.52 sec

2014-04-29 09:24:20,613 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 1.52 sec

2014-04-29 09:24:21,620 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.53 sec

2014-04-29 09:24:22,627 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.53 sec

2014-04-29 09:24:23,633 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.53 sec

2014-04-29 09:24:24,640 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.53 sec

2014-04-29 09:24:25,646 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.53 sec

2014-04-29 09:24:26,653 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.53 sec

2014-04-29 09:24:27,660 Stage-1 map = 100%,  reduce = 17%, Cumulative CPU 5.53 sec

2014-04-29 09:24:28,666 Stage-1 map = 100%,  reduce = 17%, Cumulative CPU 5.53 sec

2014-04-29 09:24:29,674 Stage-1 map = 100%,  reduce = 17%, Cumulative CPU 5.53 sec

2014-04-29 09:24:30,683 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

2014-04-29 09:24:31,690 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

2014-04-29 09:24:32,698 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

2014-04-29 09:24:33,705 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

2014-04-29 09:24:34,712 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

2014-04-29 09:24:35,720 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

2014-04-29 09:24:36,727 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec

MapReduce Total cumulative CPU time: 8 seconds 900 msec

Ended Job = job_201404251509_0134

MapReduce Jobs Launched:

Job 0: Map: 2  Reduce: 1  Cumulative CPU: 8.9 sec  HDFS Read: 422 HDFS Write: 36 SUCCESS

Total MapReduce CPU Time Spent: 8 seconds 900 msec

OK

a1      a2      b1      b2

1      X      1      X

2      Y      2      Y

3      Z      NULL    NULL

NULL    NULL    4      Z

Time taken: 33.788 seconds, Fetched: 4 row(s)

七.笛卡尔积

select * from a,b;

注意ORACLE中不能使用JOIN来做笛卡尔积，因为ORACLE中JOIN关键字后面必须有ON和关联条件。

结果如下图所示：

HIVE：

select * from a join b;

执行过程及结果如下图所示：

hive (default)> select * from a join b;

Total MapReduce jobs = 1

setting HADOOP_USER_NAME        hadoop

Execution log at: /tmp/hadoop/.log

2014-04-29 09:26:25    Starting to launch local task to process map join;      maximum memory = 1908932608

2014-04-29 09:26:25    Processing rows:        1      Hashtable size: 1      Memory usage:  110968200      rate:  0.058

2014-04-29 09:26:25    Dump the hashtable into file: file:/tmp/hadoop/hive_2014-04-29_09-26-23_444_3902086956553551117/-local-10002/HashTable-Stage-3/MapJoin-mapfile30--.hashtable

2014-04-29 09:26:25    Upload 1 File to: file:/tmp/hadoop/hive_2014-04-29_09-26-23_444_3902086956553551117/-local-10002/HashTable-Stage-3/MapJoin-mapfile30--.hashtable File size: 361

2014-04-29 09:26:25    End of local task; Time Taken: 0.34 sec.

Execution completed successfully

Mapred Local Task Succeeded . Convert the Join into MapJoin

Mapred Local Task Succeeded . Convert the Join into MapJoin

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_201404251509_0135, Tracking URL = http://IP:50030/jobdetails.jsp?jobid=job_201404251509_0135

Kill Command = /home/hadoop/package/hadoop-1.0.4/libexec/../bin/hadoop job  -kill job_201404251509_0135

Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0

2014-04-29 09:26:36,279 Stage-3 map = 0%,  reduce = 0%

2014-04-29 09:26:42,306 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.41 sec

2014-04-29 09:26:43,312 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.41 sec

2014-04-29 09:26:44,318 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.41 sec

2014-04-29 09:26:45,325 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.41 sec

2014-04-29 09:26:46,331 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.41 sec

2014-04-29 09:26:47,337 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.41 sec

2014-04-29 09:26:48,344 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 1.41 sec

MapReduce Total cumulative CPU time: 1 seconds 410 msec

Ended Job = job_201404251509_0135

MapReduce Jobs Launched:

Job 0: Map: 1  Cumulative CPU: 1.41 sec  HDFS Read: 211 HDFS Write: 72 SUCCESS

Total MapReduce CPU Time Spent: 1 seconds 410 msec

OK

a1      a2      b1      b2

1      X      1      X

2      Y      1      X

3      Z      1      X

1      X      2      Y

2      Y      2      Y

3      Z      2      Y

1      X      4      Z

2      Y      4      Z

3      Z      4      Z

Time taken: 24.97 seconds, Fetched: 9 row(s)