Loading CSV file on Hive Table with String Array

7
votes

I am trying to insert a CSV File into Hive with one field being array of string .

Here is the CSV File :

48,Snacks that Power Up Weight Loss,Aidan B. Prince,[Health&Fitness,Travel]
99,Snacks that Power Up Weight Loss,Aidan B. Prince,[Photo,Travel]

I tried creating table something like this :

CREATE TABLE IF NOT EXISTS Article
(
ARTICLE_ID INT,
ARTICLE_NSAME STRING,
ARTICLE_AUTHOR STRING,
ARTICLE_GENRE ARRAY<STRING>
);
LOAD DATA INPATH '/tmp/pinterest/article.csv' OVERWRITE INTO TABLE Article;
select * from Article;

Here is output what I get :

article.article_id  article.article_name    article.article_author  article.article_genre
48  Snacks that Power Up Weight Loss    Aidan B. Prince ["[Health&Fitness"]
99  Snacks that Power Up Weight Loss    Aidan B. Prince ["[Photo"]

Its taking only one value in last field article_genre .

Can someone point out what wrong here ?

2
csvhadoophive
I'm not near my sandbox at the moment so I can't tell you the right answer, but I can tell you that it's treating the commas within your input rows as new columns - even within the field that you expect to be treated as an array. So in the first row you load, [Health&Fitness is stored as ARTICLE_GENRE, and the "new column" Travel] is ignored. Your fourth column is not in the format that Hive expects an array to be in. - Ben Watson

2 Answers

13
votes

Couple of stuff :
You are missing definition for delimiter for collection items.
Also , I assume you expect you select * from article statement to return like below : 

48  Snacks that Power Up Weight Loss    Aidan B. Prince ["Health&Fitness","Travel"]
99  Snacks that Power Up Weight Loss    Aidan B. Prince ["Photo","Travel"]

I can give you an example and rest you can fiddle with it . Here is my table definition : 

create table article (
  id int,
  name string,
  author string,
  genre array<string>
)
row format delimited
fields terminated by ','
collection items terminated by '|';

And here is the data :

48,Snacks that Power Up Weight Loss,Aidan B. Prince,Health&Fitness|Travel
99,Snacks that Power Up Weight Loss,Aidan B. Prince,Photo|Travel

Now do a load like :
LOAD DATA local INPATH '/path' OVERWRITE INTO TABLE article; and do select statement to check the result.

Most important point :
define delimiter for collection items and don't impose the array structure you do in normal programming.
Also, try to make the field delimiters different from collection items delimiters to avoid confusion and unexpected results.

0
votes

In order to insert array of string in Hive table , we need to take care of below point.

 1. While creating Hive table.Collection items should be terminated by "," ('colelction.delim'=',',)
 2. Data should be like that in CSV file
  48  Snacks that Power Up Weight Loss    Aidan B. Prince Health&Fitness,Travel
You can modify file  by running below SED commands in follwing order:
 - sed -i 's/\[\"//g' filename
 - sed -i 's/\"\]//g' filename
 - sed -i 's/"//g' filename