文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何处理蜂巢中的结构数组

问如何处理蜂巢中的结构数组
EN

Stack Overflow用户
提问于 2017-03-23 06:10:55
回答 1查看 4.3K关注 0票数 3

下面给出了一个json文件，我希望通过使用jsonserde创建一个外部表来访问这个文件。

代码语言：javascript
复制
{
  "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数据。

代码语言：javascript
复制
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";

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

代码语言：javascript
复制
select data[0].querytype from kccdata1;
select data.season[1] from kccdata1;

但是，如果我想找到类似于crop=others的所有扇区，我无法使用这个表来完成它。我如何编写这样的查询呢？

hadoop
hive
hiveql
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-03-23 07:50:26

侧视外内联

代码语言：javascript
复制
select  k.response
       ,d.* 

from    kccdata1 k 
        lateral view outer inline (k.data) d
;
代码语言：javascript
复制
+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+
| 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 |
+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+
票数 8
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/42968470

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档