>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 <anurag.gul...@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.ku...@gmail.com [mailto:ashwanth.ku...@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 <anurag.gul...@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
