# JSON array mapping into Hive

**URL:** <https://discuss.elastic.co/t/json-array-mapping-into-hive/71411>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [January 12, 2017, 4:47pm UTC](https://discuss.elastic.co/t/json-array-mapping-into-hive/71411 "2017-01-12T16:47:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![krishna\_chaitanya](https://avatars.discourse-cdn.com/v4/letter/k/b5a626/32.png) [@krishna\_chaitanya](https://discuss.elastic.co/u/krishna_chaitanya)\
**Post date:** [January 12, 2017, 4:47pm UTC](https://discuss.elastic.co/t/json-array-mapping-into-hive/71411/1 "2017-01-12T16:47:42Z")

</div>

I would like to read elasticsearch data into Hive, and have been following documentation [here](https://www.elastic.co/guide/en/elasticsearch/hadoop/current/hive.html#_reading_data_from_elasticsearch_2).

One of my elasticsearch fields (ACTIVITIES) is an array of JSON objects with structure like this:

```
ACTIVITIES:{
   {NAME: "..."
     ID: "..."
     TIME: "..."
   },
   {NAME: "..."
     ID: "..."
     TIME: "..."
   },
 .....
}

```

Here is the mapping of that field:

```
         "ACTIVITIES" : {
            "properties" : {
              "NAME" : {
                "type" : "text",
                "norms" : false,
                "fields" : {
                  "keyword" : {
                    "type" : "keyword"
                  }
                }
              },
              "ID" : {
                "type" : "text",
                "norms" : false,
                "fields" : {
                  "keyword" : {
                    "type" : "keyword"
                  }
                }
              },
              "TIME" : {
                "type" : "text",
                "norms" : false,
                "fields" : {
                  "keyword" : {
                    "type" : "keyword"
                  }
                }
              }
            }
          },

```

I want to map this into Hive column(s). I tried to create the column as an array of struct `array<struct<name:string,id:string,time:string>>`  
and give mapping as  
`'es.mapping.names' : 'activities.name:ACTIVITIES.NAME, activities.id:ACTIVITIES.ID, activities.time:ACTIVITIES.TIME '`

All other fields from ES are read correctly into Hive except this JSON array, which is read as **NULL**. I dont know right way to do, because I couldn't find type conversion for JSON array like this in documentation.

Please help

---

<div class="post-metadata">

**Author:** ![krishna\_chaitanya](https://avatars.discourse-cdn.com/v4/letter/k/b5a626/32.png) [@krishna\_chaitanya](https://discuss.elastic.co/u/krishna_chaitanya)\
**Post date:** [January 16, 2017, 9:39pm UTC](https://discuss.elastic.co/t/json-array-mapping-into-hive/71411/2 "2017-01-16T21:39:34Z")

</div>

Well, I do not know if this is the right way, but this seems to have worked.

```
CREATE EXTERNAL TABLE es-hive (column1 string, ..., activities array<struct<name:string,id:string, time:string>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.resource' = 'es-index-name/type-name',
                ....
               'es.query' = '?q=*',
               'es.output.json' = 'true',
               'es.mapping.names' = 'column1:some-es-field, ...., activities:ACTIVITIES');
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 13, 2017, 9:39pm UTC](https://discuss.elastic.co/t/json-array-mapping-into-hive/71411/3 "2017-02-13T21:39:42Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.