Search code examples
mysqlhivesqoop

New columns added to mysql reflect the same in Hive

I have table in mysql with 4 columns and 3 rows today. I have imported that data directly as a hive table.

the table test:

id name sal loc
1  sam  123 hyd
2  ram  234 teb
3  red  456 qer

Now I want to add a new column to the existing mysql table and add a new row with values for all the 5 columns. the values for the new column for previous rows will be null.

the table test:

id name sal loc zip
1  sam  123 hyd null
2  ram  234 teb null
3  red  456 qer null
4  Tim  567 CA  1245

When we do a sqoop incremental append I am getting values only for the old columns and the new row also shows up but for only old columns.

hive table after incremental append.

id name sal loc
1  sam  123 hyd
2  ram  234 teb
3  red  456 qer
4  Tim  567 CA

Now I want the hive table to be exact as the mysql table

Can we update the hive table while we run sqoop incremental appends? If yes

How can we do that?

If no what is the best solution for my use case

Solution

  • There is no state forward solution as your ddl changed use below steps.

    1 You have to drop hive table and delete data from hdfs from below location "hive.metastore.warehouse.dir/table".

    2 you can do directly from Sqoop import along below option to create the table again.

    --create-hive-table