hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Anurag Gulati" <Anurag.Gul...@aexp.com>
Subject Question - Nested JSON using Hive
Date Mon, 02 Apr 2012 01:37:47 GMT
I've been trying to figure this out for a couple days now and I haven't gotten very far.
Looking for your guidance on the matter.

As a test, I'm trying to import Facebook Open Graph API data into Hive but am having a problem
with the syntax.

Here is a line of sample data I'm trying to import (my own personal data):

{"id":"10011666","name":"Test user","first_name":"Test","last_name":"user","link":"http:\/\/www.facebook.com\/test.user","username":"test.user","birthday":"09\/19\/1983","hometown":{"id":"103102203064024","name":"West
Chester, Pennsylvania"},"location":{"id":"","name":null},"bio":"This is my Bio. I'm a geek
that love to hack (in a good way)","quotes":"I like quotes. But I'm shortening this section
cuz it was wild!","work":[{"employer":{"id":"6185812851","name":"American Eagle"},"location":{"id":"105540216147364","name":"Phoenix,
Arizona"},"position":{"id":"133619273341785","name":"Counter Guy"},"start_date":"2012-01"},{"employer":{"id":"190876464341724","name":"Cardiac
group"},"position":{"id":"105630109469647","name":"Executive Producer"},"description":"We
create music for Artist Placement and TV\/Film.","start_date":"2002-01"},{"employer":{"id":"6185812851","name":"American
Eagle"},"location":{"id":"105540216147364","name":"Phoenix, Arizona"},"position":{"id":"116439401740213","name":"Floor
Guy"},"start_date":"2007-10","end_date":"2012-01"},{"employer":{"id":"110067355684846","name":"Saint
Joseph Hospital"},"location":{"id":"105540216147364","name":"Phoenix, Arizona"},"position":{"id":"202489236428627","name":"Pharmacy
IT Coordinator"},"start_date":"2005-10","end_date":"2007-10"},{"employer":{"id":"110067355684846","name":"Saint
Joseph Hospital"},"location":{"id":"105540216147364","name":"Phoenix, Arizona"},"position":{"id":"144703015548786","name":"Pharmacy
Tech"},"start_date":"2001-02","end_date":"2005-10"}],"sports":[{"id":"108606435830479","name":"Karate"}],"favorite_teams":[{"id":"87169796810","name":"Philadelphia
Flyers"},{"id":"93625750491","name":"Philadelphia Phillies"},{"id":"45898408995","name":"Phoenix
Suns"},{"id":"120163518021430","name":"Philadelphia Eagles"}],"favorite_athletes":[{"id":"77922840249","name":"Steve
Nash"},{"id":"105590659475179","name":"Wayne Gretzky"},{"id":"62975399193","name":"Michael
Jordan"}],"inspirational_people":[{"id":"106676942701904","name":"Gandhi"}],"education":[{"school":{"id":"109324275761313","name":"Corona
del Sol High School"},"type":"High School"},{"school":{"id":"23680344606","name":"Arizona
State University"},"type":"College"}],"gender":"male","interested_in":["female"],"relationship_status":"Single","religion":"Hinduism
(One with all things)","political":"Liberal (Left of Center)","email":"app+22c90gj.9hh9d.f7304b58ac646e08b5f0f10a73547e34\u0040proxymail.facebook.com","website":"www.slashdot.org\r\nwww.gizmodo.com","timezone":-7,"locale":"en_US","languages":[{"id":"106059522759137","name":"English"},{"id":"112969428713061","name":"Hindi"}],"verified":true,"updated_time":"2012-03-22T17:24:25+0000"}

Here is my HIVE Table Code:

ADD JAR JARs/json-serde-1.1-jar-with-dependencies.jar;
ADD JAR JARs/json-path-0.5.4.jar;
ADD JAR JARs/json-smart-1.0.6.3.jar;

CREATE TABLE USERS (
id STRING,
name STRING,
first_name STRING,
last_name STRING,
link STRING,
username STRING,
birthday STRING,
hometown MAP<STRING,STRING>,
location MAP<id STRING,name STRING>,
bio STRING,
quotes STRING,
work ARRAY<
MAP<STRING,STRING>,
MAP<STRING,STRING>,
MAP<STRING,STRING>,
start_date STRING,
end_date STRING
>,
education ARRAY<
MAP<STRING,STRING>,
STRING
>,
gender STRING,
interested_in ARRAY<
STRING
>,
relationship_status STRING,
religion STRING,
political STRING,
email STRING,
website STRING,
timezone INT,
locale STRING,
language ARRAY<
STRING,
STRING
>,
verified STRING,
updated_time STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/export/home/me/json_data.txt' OVERWRITE INTO TABLE USERS;


When I run this code I get the error:
FAILED: Parse Error: line 10:0 mismatched input 'location' expecting Identifier near ',' in
column specification

Can you please tell me what I'm doing wrong and how to fix it?

I appreciate it!!!


Regards,

Anurag G.

American Express made the following annotations on Sun Apr 01 2012 18:37:49 

****************************************************************************** 

"This message and any attachments are solely for the intended recipient and may contain confidential
or privileged information. If you are not the intended recipient, any disclosure, copying,
use, or distribution of the information included in this message and any attachments is prohibited.
If you have received this communication in error, please notify us by reply e-mail and immediately
and permanently delete this message and any attachments. Thank you." 

American Express a ajouté le commentaire suivant le Sun Apr 01 2012 18:37:49 

Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire indiqué
et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes pas le
destinataire prévu, toute divulgation, duplication, utilisation ou distribution du courrier
ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par erreur,
veuillez nous en aviser par courrier et détruire immédiatement le courrier et les pièces jointes.
Merci. 

****************************************************************************** 
-------------------------------------------------------------------------------


Mime
View raw message