2

I am trying to create a table by combining multiple tables, but I need to get all columns on my output table. 

Create table output_table
Select cl1, cl2, cl3, cl4
From table1
Union all
Select cl1, cl2, cl3, cl5
From table2
Union all
Select cl1, cl2, cl3, cl6
From table3
Union all
Select cl1, cl2, cl3, cl7
From table4

On this case, my output table should have contained all columns.

Is there any way to get this, please

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Saye
  • 33
  • 3
  • You should explain how you want to combine those columns or show the expected result. https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Chema Jun 12 '20 at 09:30
  • I am expecting the whole columns from each table. I mean on my output table it should contain these columns cl1, cl2, cl3, cl4, cl5, cl6, cl7 – Saye Jun 12 '20 at 12:31
  • But in column 4 you are mixing columns, and another question, what are the columns DataType? – Chema Jun 12 '20 at 12:54
  • Yea that was my problem. If the columns for each table are the same, it's easy to perform union or union all. But in my case the last column from each table is different but there data types are the same. On my task, they need me to get the whole columns on the output. On SAS there is the keyword which helps us to do this, OUTER UNION CORR. But this is not working on hive or any other SQL – Saye Jun 12 '20 at 13:28
  • If all the columns were strings then one solution would be as my answer. – Chema Jun 12 '20 at 16:05

1 Answers1

0

I would try this in Hive (I suppose all the columns are string if not you can put another value, for example if fields were int you could put 0 instead of '', or you could put null also, the final result will be the same)

CREATE TABLE output_table
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
AS
SELECT cl1, cl2, cl3, cl4, '' AS cl5, '' AS cl6, '' AS cl7
FROM table1
UNION ALL
SELECT '' AS cl1, '' AS cl2, '' AS cl3, '' AS cl4, cl5, '' AS cl6, '' AS cl7
FROM table2
UNION ALL
SELECT '' AS cl1, '' AS cl2, '' AS cl3, '' AS cl4, '' AS cl5, cl6, '' AS cl7
FROM table3
UNION ALL
SELECT '' AS cl1, '' AS cl2, '' AS cl3, '' AS cl4, '' AS cl5, '' AS cl6, cl7
FROM table4;

then you can clean up this table as follow creating another table as final table

CREATE TABLE final_table
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
AS
SELECT t.cl1, t1.cl2,t2.cl3,t3.cl4,t4.cl5,t5.cl6,t6.cl7
FROM (SELECT cl1 FROM output_table WHERE cl1 <> '') AS t,
     (SELECT cl2 FROM output_table WHERE cl2 <> '') AS t1,
     (SELECT cl3 FROM output_table WHERE cl3 <> '') AS t2,
     (SELECT cl4 FROM output_table WHERE cl4 <> '') AS t3,
     (SELECT cl5 FROM output_table WHERE cl5 <> '') AS t4,
     (SELECT cl6 FROM output_table WHERE cl6 <> '') AS t5,
     (SELECT cl7 FROM output_table WHERE cl7 <> '') AS t6;
Chema
  • 2,748
  • 2
  • 13
  • 24