如何从json数据创建配置单元表

ldxq2e6h 于 2021-05-31 发布在 Hadoop

关注(0)|答案(1)|浏览(280)

我见过一些表对一个数据表使用一行json代码。下面的json不是那种格式。相反，整个文件是一个json文件，基本上是一个字典字典。有人知道怎么做吗？
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表

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';

输出应该是

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

或

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

无论哪种方式我都可以。另外，我不是100%的d部分应该如何看。我也会同意的 [{d_1": "D-1","d_2": "D-2"}] 而不是我上面所说的。我对json和hive表完全陌生，所以我很灵活。
错误是 Display all 560 possibilities? (y or n) 在'a:string之后，所以有一些格式问题，但我不确定它们是什么。

hadoop Hive JSON

来源：https://stackoverflow.com/questions/61687816/how-to-create-a-hive-table-from-json-data

1条答案

按热度按时间

htrmnn0y1#

我会尽量做到
你的数据

{"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"}}

create table语句

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';

查询表

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"]  |
+----------+--+

另一个结构更复杂的好例子是
数据：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" } ] } } } } }

create table语句

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/';

查询表

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

赞(0）回复(0）举报 2021-06-01

我来回答

如何从json数据创建配置单元表

1条答案

相关问题

热门标签

最新问答