Comparison between Hive and Oracle table join statements

When migrating an Oracle stored procedure to the HIVE platform, you will inevitably encounter Syntax problems related to table Association.

This article compares various table Association syntaxes of ORALCE and HIVE in detail, including internal Association, left and right association, full outer Association, and Cartesian product.

1. Create a table

ORACLE:

Create table
(
A1 number (10 ),
A2 varchar2 (50)
);

Create table B
(
B1 number (10 ),
B2 varchar2 (50)
);

HIVE:

Create table if not exists (
A1 STRING,
A2 STRING)
COMMENT 'table'
ROW FORMAT DELIMITED
Fields terminated by '|'
Lines terminated by '\ N'
STORED AS TEXTFILE
TBLPROPERTIES ('created _ at' = '2017-04-28 ', 'creator' = 'henry ');

Ii. insert data

ORACLE:

Insert into a (a1, a2) values (1, 'x ');
Insert into a (a1, a2) values (2, 'y ');
Insert into a (a1, a2) values (3, 'z ');

Insert into B (b1, b2) values (1, 'x ');
Insert into B (b1, b2) values (2, 'y ');
Insert into B (b1, b2) values (4, 'z ');

HIVE:

Hive (default)> load data local inpath './data1 'into table;
Copying data from file:/home/Hadoop/roger/SQL/renguihe/data
Copying file:/home/hadoop/roger/SQL/renguihe/data
Loading data to table default.
Table default. a stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 12, raw_data_size: 0]
OK
Time taken: 1.961 seconds
Hive (default)> load data local inpath './data1 'into table B;
Copying data from file:/home/hadoop/roger/SQL/renguihe/data
Copying file:/home/hadoop/roger/SQL/renguihe/data
Loading data to table default. B
Table default. B stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 12, raw_data_size: 0]
OK
Time taken: 0.392 seconds

The content of the data1 data file is:

1 | X
2 | Y
3 | Z

Data2 data file content:

1 | X
2 | Y
4 | Z

Iii. equijoin

ORACLE:

Select * from a, B where a. a1 = B. b1;

Or:

Select * from a join B on a. a1 = B. b1;

The result is shown in:

 

HIVE:

Select * from a join B on a. a1 = B. b1;

Note that the where clause cannot be used in HIVE to indicate association conditions.

Shows the execution process and result:

Hive (default)> select * from a join B on a. a1 = B. b1;
Total MapReduce jobs = 1
Setting HADOOP_USER_NAME hadoop
Execution log at:/tmp/hadoop/. log
09:13:27 Starting to launch local task to process map join; maximum memory = 1908932608
09:13:27 Processing rows: 3 Hashtable size: 3 Memory usage: 110981704 rate: 0.058
09:13:27 Dump the hashtable into file:/tmp/hadoop/hive_2014-04-29_09-13-25_273_8486588204512196396/-local-10002/HashTable-Stage-3/MapJoin-mapfile00 --. hashtable
09:13:27 Upload 1 File to: file:/tmp/hadoop/hive_2014-04-29_09-13-25_273_8486588204512196396/-local-10002/HashTable-Stage-3/MapJoin-mapfile00 --. hashtable File size: 438
09:13:27 End of local task; Time Taken: 0.339 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_201401161509_0131, Tracking URL = <a href = "http: // IP: 50030/jobdetails. jsp? Jobid = job_201401_1509_01_kill "target =" _ blank "> http: // <span style =" color: rgb (0, 0, 0); "> IP </span>: 50030/jobdetails. jsp? Jobid = job_201401161509_0131
Kill </a> Command =/home/hadoop/package/hadoop-1.0.4/libexec/../bin/hadoop job-kill job_201401_1509_0131
Hadoop job information for Stage-3: number of mappers: 1; number of concurrent CERs: 0
09:13:39, 979 Stage-3 map = 0%, reduce = 0%
09:13:46, 025 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.59 sec
09:13:47, 034 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.59 sec
09:13:48, 044 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.59 sec
09:13:49, 052 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.59 sec
09:13:50, 061 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.59 sec
09:13:51, 069 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.59 sec
09:13:52, 077 Stage-3 map = 100%, reduce = 100%, Cumulative CPU 1.59 sec
MapReduce Total cumulative CPU time: 1 secondds 590 msec
Ended Job = job_201401_1509_0131
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.59 sec HDFS Read: 211 HDFS Write: 16 SUCCESS
Total MapReduce CPU Time Spent: 1 secondds 590 msec
OK
A1 a2 b1 b2
1X1 X
2 Y 2 Y

For more details, please continue to read the highlights on the next page:

1 2 3 4 Next Page