CODEWITHSUNDEEP
sqlhivehiveql
sharp
sharp

Reputation: 2158

Hive - split cell into multiple column

I have referred to prior posts to split one column into two. Looks like the examples that I am referring to are sql, which may be different compared to Hive. How do I convert the orig_data below to results data?

            orig_data

name        location    code
Andrew M    NY          145-ABG
Paul C      NY          1787-ATG
Kate M      NY          3874-WV

            results

name        location    ID      per
Andrew M    NY          145     ABG
Paul C      NY          1787    ATG
Kate M      NY          3874    WV

select 
  name, location,
  left(code, charindex('-',code) as id, --not working
  right(code, charindex('-',code) as per, --not working
from 
  orig_table;

Upvotes: 1

Views: 3809

Answers (2)

linayou
linayou

Reputation: 46

here is an example:

INSERT INTO TABLE bdp.optrans_tbl
SELECT
CONCTNS.splitted_cnctns[0] AS con1,
CONCTNS.splitted_cnctns[1] AS con2,
CONCTNS.splitted_cnctns[2] AS con3
FROM
(SELECT
split(connections,',') AS splitted_cnctns FROM bdp.transact_tbl)CONCTNS;

To know more, please see http://bigdataprogrammers.com/split-one-column-into-multiple-columns-in-hive/

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use substr and instr

select 
name, location,
substr(code, 1, instr(code,'-')-1) as id, 
substr(code, instr(code,'-')+1) as per
from orig_table;

Upvotes: 1

Related Questions