You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Anurag Gulati <An...@aexp.com> on 2012/04/02 03:37:47 UTC
Question - Nested JSON using Hive
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.
******************************************************************************
-------------------------------------------------------------------------------
Re: Question - Nested JSON using Hive
Posted by Ashwanth Kumar <as...@googlemail.com>.
>From what I see, You are using complex data types within Array type. It is
not possible. ARRAY is like a LIST which supports only one type.
For your case try something with STRUCT may be that suits your use-case
better.
Read this link -
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
Based on that *updated schema* might be something like
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<STRING,STRING>,
bio STRING,
quotes STRING,
work STRUCT<employee: MAP<STRING,STRING>,location:
MAP<STRING,STRING>,position: MAP<STRING,STRING>,start_date :
STRING,end_date : STRING>,
education STRUCT<school: MAP<STRING,STRING>,type: STRING>,
gender STRING,
interested_in ARRAY<STRING>,
relationship_status STRING,
religion STRING,
political STRING,
email STRING,
website STRING,
timezone INT,
locale STRING,
language STRUCT<lang: ARRAY<STRING>>,
verified STRING,
updated_time STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE;
PS - I have not tested this code, but it must be very similar to this.
On Mon, Apr 2, 2012 at 2:05 PM, Anurag Gulati <An...@aexp.com>wrote:
> Great that fixed that problem … now I’m getting the error “FAILED: Parse
> Error: line 13:29 mismatched input ',' expecting > near '>' in list type”*
> ***
>
> ** **
>
> This is the updated Schema 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<STRING,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;*
> ***
>
> ** **
>
> Test 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"}
> ****
>
> ** **
>
> ** **
>
> ** **
>
> Regards,****
>
> -Anurag G.****
>
> ** **
>
> *From:* ashwanth.kumar@gmail.com [mailto:ashwanth.kumar@gmail.com] *On
> Behalf Of *Ashwanth Kumar
> *Sent:* Sunday, April 01, 2012 8:53 PM
> *To:* user@hive.apache.org
> *Subject:* Re: Question - Nested JSON using Hive****
>
> ** **
>
> You get that error because "location" is a keyword in Hive. Try to
> encapsulate it in ` char and try. ****
>
> On Mon, Apr 2, 2012 at 7:07 AM, Anurag Gulati <An...@aexp.com>
> wrote:****
>
> 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<STRING,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.
>
> ******************************************************************************
> ****
>
>
> -------------------------------------------------------------------------------
>
> ****
>
>
>
> ****
>
> ** **
>
> -- ****
>
> ** **
>
> Ashwanth Kumar / ashwanthkumar.in****
>
> ** **
>
> ** **
>
> American Express made the following annotations on Mon Apr 02 2012
> 01:35:23
> ******************************************************************************
>
> "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 Mon Apr 02 2012
> 01:35:23
> 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.
>
> ******************************************************************************
>
>
> -------------------------------------------------------------------------------
>
>
--
Ashwanth Kumar / ashwanthkumar.in
RE: Question - Nested JSON using Hive
Posted by Anurag Gulati <An...@aexp.com>.
Great that fixed that problem ... now I'm getting the error "FAILED: Parse Error: line 13:29 mismatched input ',' expecting > near '>' in list type"
This is the updated Schema 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<STRING,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;
Test Data:
{"id":"10011666","name":"Test user","first_name":"Test","last_name":"user","link":"http:\/\/www.facebook.com<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<tel: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<tel: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<http://u0040proxymail.facebook.com>","website":"www.slashdot.org<http://www.slashdot.org>\r\nwww.gizmodo.com<http://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"}
Regards,
-Anurag G.
From: ashwanth.kumar@gmail.com [mailto:ashwanth.kumar@gmail.com] On Behalf Of Ashwanth Kumar
Sent: Sunday, April 01, 2012 8:53 PM
To: user@hive.apache.org
Subject: Re: Question - Nested JSON using Hive
You get that error because "location" is a keyword in Hive. Try to encapsulate it in ` char and try.
On Mon, Apr 2, 2012 at 7:07 AM, Anurag Gulati <An...@aexp.com>> wrote:
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<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<tel: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<tel: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<http://u0040proxymail.facebook.com>","website":"www.slashdot.org<http://www.slashdot.org>\r\nwww.gizmodo.com<http://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<STRING,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.
******************************************************************************
-------------------------------------------------------------------------------
--
Ashwanth Kumar / ashwanthkumar.in<http://ashwanthkumar.in/>
American Express made the following annotations on Mon Apr 02 2012 01:35:23
******************************************************************************
"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 Mon Apr 02 2012 01:35:23
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.
******************************************************************************
-------------------------------------------------------------------------------
Re: Question - Nested JSON using Hive
Posted by Ashwanth Kumar <as...@googlemail.com>.
You get that error because "location" is a keyword in Hive. Try to
encapsulate it in ` char and try.
On Mon, Apr 2, 2012 at 7:07 AM, Anurag Gulati <An...@aexp.com>wrote:
> 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.
>
> ******************************************************************************
>
>
> -------------------------------------------------------------------------------
>
>
--
Ashwanth Kumar / ashwanthkumar.in