Splitting data into multiple columns in Hive

Question

How can I split a data string into 3 separate columns in a Hive table?

Example input data: 116:151:1. Split as gid, sid, rid.

Required output:

gid    sid     rid
116    151     1

Answer 1

Use the split() function. You can read about it (and all other Hive functions) in the documentation.

Query:

select split("116:151:1", '\\:')[0] as gid
     , split("116:151:1", '\\:')[1] as sid
     , split("116:151:1", '\\:')[2] as rid
from database.table

Output:

gid    sid    rid
116    151    1

You'll want to replace "116:151:1" with the name of the column in your table.