How to create a hive table from json data

user_6626531 0
Viewed 340

I've seen some tables that have use one line of json code for one data table. The below json isn't in that form. Instead the entire file is a json file and it's basically a dictionary of dictionaries. Does anyone know how to make this work?

json

{
  "aa": {
    "a": "A",
    "b": "B",
    "c": "C",
    "d": [
      {
        "d_1": "D-1",
        "d_2": "D-2"
      }
    ],
    "e": "E"
  },
  "bb": {
    "a": "AA",
    "b": "BB",
    "c": "CC",
    "d": [
      {
        "d_1": "DD-11",
        "d_2": "DD-22"
      }
    ],
    "e": "EE"
  }
}

hive table

drop table if exists test_json_letters;
create table test_json_letters 
(
my_array ARRAY<struct<
    a:string,
    b:string,
    c:string,
    d:array<struct<
        d_1:string,
        d_2:string
    >>
    e:string
    >>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 'path/to/table';

The output should be

aa,A,B,C,D-1,D-2
bb,AA,BB,CC,DD-11,DD-22

or

A,B,C,D-1,D-2
AA,BB,CC,DD-11,DD-22

Either way is fine with me. Also, I'm not 100% how the D section should look. I'd also be okay with [{d_1": "D-1","d_2": "D-2"}] instead of what I have above. I'm totally new to json and hive tables so I'm flexible.

The error is Display all 560 possibilities? (y or n) after `a:string, so there's some formatting issues but I'm not sure what they are.

hive hadoop json
1 Answers

I would try as follow

your data

{"aa": {"a": "A","b": "B","c": "C","d": [{"d_1": "D-1","d_2": "D-2"}],"e": "E"},"bb": {"a": "AA","b": "BB","c": "CC","d": [{"d_1": "DD-11","d_2": "DD-22"}],"e": "EE"}}

the create table statement

CREATE TABLE my_table(aa struct<
    a:string,
    b:string,
    c:string,
    d:array<struct<
        d_1:string,
        d_2:string>>,
    e:string>)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/user/training/json';

querying the table

SELECT * FROM my_table;
+----------------------------------------------------+--+
|                    my_table.aa                     |
+----------------------------------------------------+--+
| {"a":"A","b":"B","c":"C","d":[{"d_1":"D-1","d_2":"D-2"}],"e":"E"} |
+----------------------------------------------------+--+
SELECT aa.a FROM my_table;
+----+--+
| a  |
+----+--+
| A  |
+----+--+
SELECT aa.d FROM my_table;
+------------------------------+--+
|              d               |
+------------------------------+--+
| [{"d_1":"D-1","d_2":"D-2"}]  |
+------------------------------+--+
SELECT aa.d.d_1 FROM my_table;
+----------+--+
|   d_1    |
+----------+--+
| ["D-1"]  |
+----------+--+

Another good example with a more complex structure would be

data: file2.json

{ "purchaseid": { "ticketnumber": "23546852222", "location": "vizag", "Travelerhistory": { "trav": { "fname": "ramu", "lname": "gogi", "travelingarea": { "destination": { "stationid": "KAJKL", "stationname": "hyd" } }, "food": { "foodpref": [{ "foodcode": "CK567", "foodcodeSegment": "NOVEG" }, { "foodcode": "MM98", "foodcodeSegment": "VEG" } ] } } } } }

the create table statement

CREATE TABLE my_table(
purchaseid STRUCT<ticketnumber:STRING,location:STRING,
  Travelerhistory:STRUCT<
    trav:STRUCT<fname:STRING,lname:STRING,
        travelingarea:STRUCT< destination :STRUCT<stationid:string,stationname:string>>,
    food :STRUCT<foodpref:ARRAY<STRUCT<foodcode:string,foodcodeSegment:string>>>
    >>>)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' 
LOCATION '/user/training/json2/';

querying the table

select purchaseid.ticketnumber from my_table;
select purchaseid.travelerhistory.trav.fname from my_table;
select purchaseid.travelerhistory.trav.lname from my_table;

and so on
user_85714980
Related