CODEWITHSUNDEEP
hadoophive
Govind
Govind

Reputation: 449

Get records from multiple Hive tables without join

I have 2 tables :

Table1 desc:

count int

Table2 desc:

count_val int

I get the fields count, count_val from the above tables and insert into the another Audit table(table3) .

Table3 desc:

count int
count_val int

I am trying to log the record count of these 2 tables into audit table for each job run.

Any of your suggestions are appreciated.Thanks!

Upvotes: 0

Views: 482

Answers (1)

Ivan Klass
Ivan Klass

Reputation: 6627

If you want just aggregations (like sums), the solution comes with the use of UNION

INSERT INTO TABLE audit 
    SELECT
        SUM(count),
        SUM(count_val)
    FROM (
            SELECT
                t1.count,
                0 as count_val
            FROM table1 t1 

            UNION ALL

            SELECT
                0 as count,
                t2.count_val
            FROM table2 t2
    ) unioned;

Otherwise join is required, because you should somehow match your lines, it's how relational algebra (the theory behind SQL) works.

==table1==
| count| 
|------|
|  12  |
|  751 |
|  167 |

===table2===
| count_val| 
|----------|
|   1991   |
|   321    |
|   489    |
|   7201   |
|   3906   |

===audit===
| count | count_val| 
|-------|----------|
|   ??? |    ???   |

Upvotes: 2

Related Questions