Hadoop/Hive - Split a single row into multiple rows and store to a new table

Question

Currently, I solve my initial problem with this topic: Hadoop/Hive - Split a single row into multiple rows and store to a new table.

Does anyone have a clue how to create a new table with the grouped subs?

ID  Subs
1   deep-learning, machine-learning, python
2   java, c++, python, javascript

with the code below I get the return I'm looking for but could not figure out how to save the output into a new table

use demoDB 
Select id_main , topic_tag from demoTable
lateral view explode (split(topic_tag , ',')) topic_tag as topic

Thanks Nico

Answer 1

In Hive, you can use create ... as select ...:

create table newtable as
select id_main, topic_tag 
from demoTable
lateral view explode (split(topic_tag , ',')) topic_tag as topic

This creates a new table and initiates its content from the resultset of the query. If the new table exists already, then use insert ... select instead:

insert into newtable (id_main, topic_tag)
select id_main, topic_tag 
from demoTable
lateral view explode (split(topic_tag , ',')) topic_tag as topic