create a view in HIVE merge two tables

Question

How can I create a view to merge three tables The workflow is like initially one table was created in mysql now this table has been divided to 3 tables and kept in hive so for that I need to create a view Initially in mysql one table for eg the table name is Initialtable. This Initialtable consists of col1,col2,col3,col4,col5 now this table has been divided to 3 tables in hive and I need to merge these tables using a view 1)table1 2)table2 3)table3

Now this table1 consists of col1,col3,col5 table2 consists of col1,col2,col3 table3 consists of col1,col5

Now I have to create a view so that I can merge these table1,table2,table3 for that I will put the non used columns in table1,table2,table3 as null

like create view v1 select col1,col2 as null,col3,col4,col5 from table1 union select col1,col2,col3,col4 as null,col5 as null from table 2 union col1,col2 as null,col3 as null, col4 as null,col5 from table 3

can someone provide a proper syntax to gain this output in hive

Please provide sample data and desired results. It is quite unclear what your data is and what you mean by merge. — GMB

Prabhat Ratnala Prabhat Ratnala · Accepted Answer · 2020-06-20T16:28:31

Assuming table1, table2, table3 are the three tables which were split and the columns are as below: table1: col1,col3,col5
table2: col1,col2,col3
table3: col1,col4,col3

and col1 is the primary key across all the three tables. You can create a view as below:

CREATE OR replace VIEW initialtable AS
  SELECT DISTINCT a.col1,
                  b.col2,
                  a.col3,
                  c.col4,
                  a.col5
  FROM   TABLE1 AS a
         join TABLE2 AS b
           ON ( a.col1 = b.col1 )
         join TABLE3 AS c
           ON ( c.col1 = a.col1 )

create a view in HIVE merge two tables

1 Answers