hadoop - 如何在 hive 中使用结构数组

Question

我有一个下面给出的 json 文件，我想通过使用 jsonserde 创建一个外部表来使用 hive 访问它。

{
  "ResponseCode": "1",
  "Response": "Data Found",
  "data": [
    {
      "Season": "RABI",
      "Sector": "HORTICULTURE",
      "Category": "Fruits",
      "Crop": "Mango",
      "QueryType": "Cultural Practices",
      "QueryText": "how to control flower drop in mango ?",
      "KCCAns": "recommended for spray to planofix 5-7 mili/pump",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "BALAYAPALLE                                       ",
      "SubmitDate": "11/1/2016 9:05:27 AM"
    },
    {
      "Season": "RABI",
      "Sector": "AGRICULTURE",
      "Category": "Others",
      "Crop": "Others",
      "QueryType": "Weed Management",
      "QueryText": "weed management in eucalyptus",
      "KCCAns": "RECOMMENDED TO SPRAY GLYPOSATE @ 2 LITRES PER ACRE",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "MARRIPADU                                         ",
      "SubmitDate": "11/1/2016 9:07:04 AM"
    },
    {
      "Season": "RABI",
      "Sector": "HORTICULTURE",
      "Category": "Vegetables",
      "Crop": "Bhindi(Okra/Ladysfinger)",
      "QueryType": "\tPlant Protection\t",
      "QueryText": "OKRA MITE DAMAGE",
      "KCCAns": "RECOMMENDED TO SPRAY DICOFOL 1 LITER /200 LITRES OF WATER /ACRE",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "KOVUR                                             ",
      "SubmitDate": "11/1/2016 3:11:59 PM"
    },
    {
      "Season": "RABI",
      "Sector": "AGRICULTURE",
      "Category": "Cereals",
      "Crop": "Paddy (Dhan)",
      "QueryType": "Weed Management",
      "QueryText": "paddy weed management at 15-20 days crop",
      "KCCAns": "RECOMMENDED TO SPRAY CYHALOPOP-P- BUTYL  250 ML PER  ACRE",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "BALAYAPALLE                                       ",
      "SubmitDate": "11/3/2016 2:11:17 PM"
    },
    {
      "Season": "KHARIF",
      "Sector": "AGRICULTURE",
      "Category": "Others",
      "Crop": "Others",
      "QueryType": "Weather",
      "QueryText": "WEATHER  REPORT",
      "KCCAns": "WEATHER  REPORT   CONTACT  18004253141 ,08912543031",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "KALIGIRI                                          ",
      "SubmitDate": "11/3/2016 5:22:22 PM"
    },
    {
      "Season": "RABI",
      "Sector": "AGRICULTURE",
      "Category": "Pulses",
      "Crop": "Pigeon pea (red gram/arhar/tur)",
      "QueryType": "Nutrient Management",
      "QueryText": "RED GRAM NUTRIENT MANAGEMENT",
      "KCCAns": "RECOMMENDED TO SPRAY 13-045 ONE KG PER ACRE",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "ANUMASAMUDRAMPETA                                 ",
      "SubmitDate": "11/3/2016 7:25:10 PM"
    },
    {
      "Season": "RABI",
      "Sector": "AGRICULTURE",
      "Category": "Pulses",
      "Crop": "Pigeon pea (red gram/arhar/tur)",
      "QueryType": "Nutrient Management",
      "QueryText": "RED GRAM NUTRIENT MANAGEMENT",
      "KCCAns": "RECOMMENDED TO SPRAY 13-0-45 ONE  KG/ACRE",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "ANUMASAMUDRAMPETA                                 ",
      "SubmitDate": "11/3/2016 7:30:02 PM"
    },
    {
      "Season": "RABI",
      "Sector": "HORTICULTURE",
      "Category": "Fruits",
      "Crop": "Citrus",
      "QueryType": "\tPlant Protection\t",
      "QueryText": "CITRUS SUCKING PEST",
      "KCCAns": "RECOMMENDED FOR DIMETHOATE  400 ML / ACRE 200 LITER WATER",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "KODAVALUR                                         ",
      "SubmitDate": "11/4/2016 8:48:03 AM"
    },
    {
      "Season": "RABI",
      "Sector": "HORTICULTURE",
      "Category": "Fruits",
      "Crop": "Citrus",
      "QueryType": "\tPlant Protection\t",
      "QueryText": "CITRUS SUCKING PEST",
      "KCCAns": "RECOMMENDED FOR DIMETHOATE  400 ML / ACRE 200 LITER WATER",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "KODAVALUR                                         ",
      "SubmitDate": "11/4/2016 8:48:04 AM"
    },
    {
      "Season": "KHARIF",
      "Sector": "AGRICULTURE",
      "Category": "Others",
      "Crop": "Others",
      "QueryType": "Weather",
      "QueryText": "weather report",
      "KCCAns": "Considerable cloudiness with a shower or thunderstorm around this afternoon",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "VINJAMUR                                          ",
      "SubmitDate": "11/4/2016 3:14:03 PM"
    },
    {
      "Season": "KHARIF",
      "Sector": "AGRICULTURE",
      "Category": "Others",
      "Crop": "Others",
      "QueryType": "Weather",
      "QueryText": "WEATHER RTEPORT",
      "KCCAns": "Considerable cloudiness with a shower or thunderstorm around this afternoon",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "MARRIPADU                                         ",
      "SubmitDate": "11/4/2016 5:18:50 PM"
    },
    {
      "Season": "KHARIF",
      "Sector": "AGRICULTURE",
      "Category": "Others",
      "Crop": "Others",
      "QueryType": "\tPlant Protection\t",
      "QueryText": "BENGAL GRAM BEST VARIETY",
      "KCCAns": "BENGAL GRAM BEST VARIETY  : J.G-11, LAM SENAGA (LBEG-7),ICCV-10",
      "StateName": "ANDHRA PRADESH",
      "DistrictName": "NELLORE",
      "BlockName": "PELLAKUR                                          ",
      "SubmitDate": "11/4/2016 7:57:44 PM"
    }
  ]
}

我已经创建了下表来访问上面的 json 数据。

create EXTERNAL table kccdata1 (
    Response string,
    data array<struct<
    Season:STRING,
    Sector:STRING,
    Category:STRING,
    Crop:STRING,
    QueryType:STRING,
    QueryText:string,
    KCCAns:string,
    StateName:string,
    DistrictName:string,
    BlockName:string,
    SubmitDate:string>>
) 
row format serde "org.apache.hive.hcatalog.data.JsonSerDe" 
location "/user/hduser/jsondata";

此表已将每个结构作为每个记录的值。我可以使用下面的查询来访问其中的元素。

select data[0].querytype from kccdata1;
select data.season[1] from kccdata1;

但如果我想找到像 crop=others 的所有部门这样的东西，我无法使用此表来完成。如何编写这样的查询？

Answer 1

侧 View 外内联

• Inline (Built-in Table-Generating Functions (UDTF))
• LateralView

---

select  k.response
       ,d.* 

from    kccdata1 k 
        lateral view outer inline (k.data) d
;

---

+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+
| k.response | d.season |   d.sector   | d.category |             d.crop              |     d.querytype     |               d.querytext                |                                  d.kccans                                   |  d.statename   | d.districtname |    d.blockname     |     d.submitdate     |
+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+
| Data Found | RABI     | HORTICULTURE | Fruits     | Mango                           | Cultural Practices  | how to control flower drop in mango ?    | recommended for spray to planofix 5-7 mili/pump                             | ANDHRA PRADESH | NELLORE        | BALAYAPALLE        | 11/1/2016 9:05:27 AM |
| Data Found | RABI     | AGRICULTURE  | Others     | Others                          | Weed Management     | weed management in eucalyptus            | RECOMMENDED TO SPRAY GLYPOSATE @ 2 LITRES PER ACRE                          | ANDHRA PRADESH | NELLORE        | MARRIPADU          | 11/1/2016 9:07:04 AM |
| Data Found | RABI     | HORTICULTURE | Vegetables | Bhindi(Okra/Ladysfinger)        | Plant Protection    | OKRA MITE DAMAGE                         | RECOMMENDED TO SPRAY DICOFOL 1 LITER /200 LITRES OF WATER /ACRE             | ANDHRA PRADESH | NELLORE        | KOVUR              | 11/1/2016 3:11:59 PM |
| Data Found | RABI     | AGRICULTURE  | Cereals    | Paddy (Dhan)                    | Weed Management     | paddy weed management at 15-20 days crop | RECOMMENDED TO SPRAY CYHALOPOP-P- BUTYL 250 ML PER ACRE                     | ANDHRA PRADESH | NELLORE        | BALAYAPALLE        | 11/3/2016 2:11:17 PM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Weather             | WEATHER REPORT                           | WEATHER REPORT CONTACT 18004253141 ,08912543031                             | ANDHRA PRADESH | NELLORE        | KALIGIRI           | 11/3/2016 5:22:22 PM |
| Data Found | RABI     | AGRICULTURE  | Pulses     | Pigeon pea (red gram/arhar/tur) | Nutrient Management | RED GRAM NUTRIENT MANAGEMENT             | RECOMMENDED TO SPRAY 13-045 ONE KG PER ACRE                                 | ANDHRA PRADESH | NELLORE        | ANUMASAMUDRAMPETA  | 11/3/2016 7:25:10 PM |
| Data Found | RABI     | AGRICULTURE  | Pulses     | Pigeon pea (red gram/arhar/tur) | Nutrient Management | RED GRAM NUTRIENT MANAGEMENT             | RECOMMENDED TO SPRAY 13-0-45 ONE KG/ACRE                                    | ANDHRA PRADESH | NELLORE        | ANUMASAMUDRAMPETA  | 11/3/2016 7:30:02 PM |
| Data Found | RABI     | HORTICULTURE | Fruits     | Citrus                          | Plant Protection    | CITRUS SUCKING PEST                      | RECOMMENDED FOR DIMETHOATE 400 ML / ACRE 200 LITER WATER                    | ANDHRA PRADESH | NELLORE        | KODAVALUR          | 11/4/2016 8:48:03 AM |
| Data Found | RABI     | HORTICULTURE | Fruits     | Citrus                          | Plant Protection    | CITRUS SUCKING PEST                      | RECOMMENDED FOR DIMETHOATE 400 ML / ACRE 200 LITER WATER                    | ANDHRA PRADESH | NELLORE        | KODAVALUR          | 11/4/2016 8:48:04 AM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Weather             | weather report                           | Considerable cloudiness with a shower or thunderstorm around this afternoon | ANDHRA PRADESH | NELLORE        | VINJAMUR           | 11/4/2016 3:14:03 PM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Weather             | WEATHER RTEPORT                          | Considerable cloudiness with a shower or thunderstorm around this afternoon | ANDHRA PRADESH | NELLORE        | MARRIPADU          | 11/4/2016 5:18:50 PM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Plant Protection    | BENGAL GRAM BEST VARIETY                 | BENGAL GRAM BEST VARIETY : J.G-11, LAM SENAGA (LBEG-7),ICCV-10              | ANDHRA PRADESH | NELLORE        | PELLAKUR           | 11/4/2016 7:57:44 PM |
+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+