CODEWITHSUNDEEP
hadoophivebigdata
Meetu Agarwal
Meetu Agarwal

Reputation: 59

Apache Hive string splitting

There is this table consisting of only one column which is a string.i want to split each row using ',' and then put these in a seperate table using Apache Hive .How should i do this ?

Upvotes: 0

Views: 1722

Answers (1)

invoketheshell
invoketheshell

Reputation: 3897

A very easy way would be to:

create table database.new_table as
select split(col_value,',')[0] as column_1 
, split(col_value,',')[1] as column_2
, split(col_value,',')[2] as column_3
-- and so no till your nth column
, split(col_value,',')[10]as column_11 
from database.oldtable;

All the columns will be of type string. If you want the columns to be something other than string than cast it like below:

, select cast(split(col_value,',')[2] as double) as column_3

You can cast any data type that hive offers, int, bigint, double...

With Subquery

create table database.new_table as
select A[0] as column_1,
       A[1] as column_2
       -- and so on till your nth column
from (
    select split(col_value,',') as A 
    from database.oldtable ) x;

Upvotes: 2

Related Questions