Incremental data and merge problem validation

1. Building base tables and incremental test data

pwd/root/cat1001, Gongshaocheng  1002  Cat1002, Lidachao1003, Chenjianzhong

[Email protected] delta_merge]# HDFs Dfs-mkdir/user/merge_delta
[Email protected] delta_merge]# HDFs dfs-mkdir/user/merge_delta/base
[Email protected] delta_merge]# HDFs Dfs-mkdir/user/merge_delta/delta
[Email protected] delta_merge]# HDFs dfs-put base.txt/user/merge_delta/base
[Email protected] delta_merge]# HDFs dfs-put Delta.txt/user/merge_delta/delta

2. Create a test table

Hive> Create external Table base (ID string, namestring)    > ROW FORMAT delimited fields TERMINATED by','> Location"/user/merge_delta/base/"; Oktime taken:0.304secondshive>Select*From Base;ok1001Gongshaocheng1002Lidachaotime taken:0.875Seconds, fetched:2row (s) Hive> Create external Table Delta (ID string, namestring)    > ROW FORMAT delimited fields TERMINATED by','> Location"/user/merge_delta/delta/"; Oktime taken:0.134secondshive>Select*From Delta;ok1002Lidachao1003Chenjianzhongtime taken:0.321Seconds, fetched:2Row (s)

3. Test:

A. Full outer join syntax:

Select join Delta on base. id = Delta. ID;

The results are as follows:

1001     gongshaocheng    null    null1002    Lidachao     1002     Lidachaonull    NULL    1003    Chenjianzhong

The final answer we want is:

1001 Gongshaocheng-Represents a record that remains unchanged

1002 Lidachao--represents the latest revised record

1003 Chenjianzhong--on behalf of new records

B.coalesce function:

Select COALESCE (base. ID, Delta. ID  join Delta on base. id = Delta. ID where (delta. ID is NULL and base. ID is not NUll) OR (delta.  ID is not NULL and base. ID is not NUll) OR (delta.  ID is not NULL and base. ID is NULL);

Results:

1001 1002 1003

The above verifies that for primary key columns, we can take the COALESCE function so that the primary key column in the result set always has a value

C.if function

Select if (Delta. ID  join Delta on base. id = Delta. ID where (delta. ID is NULL and base. ID is not NUll) OR (delta.  ID is not NULL and base. ID is not NUll) OR (delta.  ID is not NULL and base. ID is NULL);

Results:

Gongshaochenglidachaochenjianzhong

The above verifies that for normal columns, if the data is unmodified (Delta.id is NULL), the value of the base table is directly used, otherwise the data of the delta tables is directly used

Finally, we get the HQL statement we want:

Select COALESCE (base. ID, Delta. ID ),if(delta.  IDjoin Delta on base. id = Delta. ID where (delta. ID is NULL and base. ID is not NUll) OR (delta.  ID is not NULL and base. ID is not NUll) OR (delta.  ID is not NULL and base. ID is NULL);

The results are as follows:

Hive>Select> Coalesce (base.ID, Delta.ID),    >if(Delta.IDis NULL, base.name,delta.name)> From Base full outerJoinDelta on base.ID= Delta.ID> Where (delta.IDis NULL and base.IDis not NUll) OR (Delta.IDis not NULL and base.IDis not NUll) OR (Delta.IDis not NULL and base.IDis NUll); Query ID= root_20151230235050_befa6322-f78f-4166-8bbd-4fde04a1a9b1total Jobs=1Launching Job1Out of1Number of reduce tasks not specified. Estimated from input data size:1In order to change the average load forA reducer (inchbytes): 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 Job= job_1451024710809_0005, Tracking URL = http://node1.clouderachina.com:8088/proxy/application_1451024710809_0005/Kill Command =/opt/cloudera/parcels/cdh-5.4.7-1. Cdh5.4.7. P0.3/lib/hadoop/bin/hadoop Job-KillJob_1451024710809_0005hadoop Job Information forstage-1: Number of Mappers:2; Number of reducers:1 -- A- -  at:Wuyi:Geneva,904stage-1Map =0%, reduce =0% -- A- -  at:Wuyi: -,245stage-1Map = -%, reduce =0%, Cumulative CPU2.69sec -- A- -  at:Wuyi: A,685stage-1Map = -%, reduce = -%, Cumulative CPU5.16secmapreduce Total Cumulative CPU Time:5Seconds themsecended Job=job_1451024710809_0005mapreduce Jobs launched:stage-stage-1: Map:2Reduce:1Cumulative CPU:5.16SEC HDFS Read:12293HDFS Write: thesuccesstotal MapReduce CPU time Spent:5Seconds theMsecok1001Gongshaocheng1002Lidachao1003Chenjianzhongtime taken:31.376Seconds, fetched:3Row (s)

Note: All of the above HQL require only one Mr Job. This is the essence of this solution!

Finally, the HQL is further optimized: in order to maintain a logical clarity, the WHERE clause was added to discuss the distribution of the three cases of full OUTER join, but in fact two or merges are the complete works, in fact the WHERE clause is superfluous. The final hql are:

Select COALESCE (base. ID, Delta. ID ),if(delta.  IDjoin Delta on base. id = Delta. ID;

Incremental data and merge problem validation