CODEWITHSUNDEEP
splithive
vanj
vanj

Reputation: 67

Splitting data into multiple columns in Hive

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

Upvotes: 4

Views: 36422

Answers (2)

Zvonko
Zvonko

Reputation: 301

Using split + sub select solves the question:

SELECT t.vec[0] AS gid, t.vec[1] AS sid, t.vec[2] AS rid
FROM (SELECT split("116:151:1", "[:]") AS vec) AS t;

Produce this ouput in hive:

+------+------+------+--+
| gid  | sid  | rid  |
+------+------+------+--+
| 116  | 151  | 1    |
+------+------+------+--+

Greetings!

Upvotes: 0

o-90
o-90

Reputation: 17585

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.

Upvotes: 15

Related Questions