I have the below tables:


name_1  id_1    value_1
avi        1    21
adi        2    X


name_2  id_2    value_2
avi        1    21
adi        2    22

metadata_info: can be read from a file or hard coded

table_1_col  table_2_col
name_1       name_2
id_1         id_2
value_1      value_2

I want the below result:

table_1_col  table_2_col  table_1.name_1  table_2.name_2  table_1.id_1  table_2.id_2  table_1.value_1  table_2.value_2  flag
name_1       name_2       avi             adi             1             1             21               21               0
name_1       name_2       avi             adi             2             2             X                21               1

I am trying to do the entire process in a shell script, and I want to run multiple Hive queries.

SELECT 'table_1' AS table_1
    ,'table_2' AS table_2
    ,'id_1' AS table_1_pk
    ,'id_2' AS table_2_pk
    ,t1.id_1 table_1_pk_value
    ,t2.id_2 AS table_2_pk_value
    ,'name_1' AS table_1_col
    ,'name_2' AS table_2_col
    ,t1.name_1 AS table_1_value
    ,t2.name_2 AS table_2_value
        WHEN t1.name_1 = t2.name_2
            THEN '0'
        WHEN t1.name_1 <> t2.name_2
            THEN '1'
        END AS flag
FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.id_1 = t2.id_2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.