CODEWITHSUNDEEP
csvhadoophive
Deepesh Shetty
Deepesh Shetty

Reputation: 1166

Loading CSV file on Hive Table with String Array

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 ?

Upvotes: 7

Views: 7888

Answers (2)

Harkirat Singh
Harkirat Singh

Reputation: 49

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

Upvotes: 0

Chandra kant
Chandra kant

Reputation: 1574

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.

Upvotes: 13

Related Questions