CODEWITHSUNDEEP
hivehiveql
Rohit Gb
Rohit Gb

Reputation: 13

Hive array specifying multiple delimiter in collection

I have dataset contains two arrays, both arrays separated by different delimiter.. Ex: 14-20-50-60 is 1st array seperated by - 12#2#333#4 is 2nd array seperated by #..

While creating table how do we specify delimiter in Collection items terminated by '' ?

input 14-20-50-60,12#2#333#4 

create table test(first array<string>, second array<string>)
row format delimited
fields terminated by ','
collection items terminated by '-' (How to specify two delimiters in the collection)

Upvotes: 1

Views: 973

Answers (1)

Ramesh
Ramesh

Reputation: 1484

You cannot use multiple delimiters for the collection items. You can achieve what you are trying to do as below though. I have used the SPLIT function to create the array using different delimiters.

Data

14-20-50-60,12#2#333#4

SQL - CREATE TABLE

create external table test1(first string, second string)
row format delimited
fields terminated by ','
LOCATION  '/user/cloudera/ramesh/test1';

SQL - SELECT 

WITH v_test_array AS
(SELECT split(first, "-") AS first_array,  
        split(second, "#") AS second_array  
   FROM test1)
SELECT first_array[0], second_array[0]
  FROM v_test_array;

OUTPUT

14  12

Hope this helps.

Upvotes: 1

Related Questions