# Reading json data from ES to HIVE with a single string field

**URL:** <https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [November 30, 2015, 11:54am UTC](https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890 "2015-11-30T11:54:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![deepakas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/deepakas/32/697_2.png) [@deepakas](https://discuss.elastic.co/u/deepakas)\
**Post date:** [November 30, 2015, 11:54am UTC](https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890/1 "2015-11-30T11:54:20Z")

</div>

Is it possible to get the complete json from es using the following table structure. I am able to get the data when I define the schema for json data in hive table. But when I use a single field to get the json string I am getting NULL as output. I am able to use the same structure to write json data to ES.

CREATE EXTERNAL TABLE es\_test (data STRING)  
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'  
TBLPROPERTIES('es.resource' = 'esindex/test',  
'es.input.json' = 'yes','es.nodes' = 'host1:9200' ,'es.query' = '?q=\*');

---

<div class="post-metadata">

**Author:** ![IrisPanabaker](https://avatars.discourse-cdn.com/v4/letter/i/7993a0/32.png) [@IrisPanabaker](https://discuss.elastic.co/u/IrisPanabaker)\
**Post date:** [December 1, 2015, 6:28am UTC](https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890/2 "2015-12-01T06:28:13Z")

</div>

I would recommends to use JSON tools such as [http://codebeautify.org/jsonviewer](http://codebeautify.org/jsonviewer) and [http://jsonformatter.org](http://jsonformatter.org) to debug , View and validate JSON data.

---

<div class="post-metadata">

**Author:** ![deepakas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/deepakas/32/697_2.png) [@deepakas](https://discuss.elastic.co/u/deepakas)\
**Post date:** [December 1, 2015, 4:28pm UTC](https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890/3 "2015-12-01T16:28:52Z")

</div>

It is a valid json. I am not sure if it is related to having a keyword like name , start as keys in the json string. Also I have fields with Capital letters in the field name like -\> englishName. I am able to pull some of the fields from the beginning of the json when I give all the fields in my hive table.

---

<div class="post-metadata">

**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\
**Post date:** [December 8, 2015, 2:02pm UTC](https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890/4 "2015-12-08T14:02:57Z")

</div>

If you are trying to return the docs from ES in JSON format, that is not supported. It shouldn't be hard to use though.  
Your table configuration is confusing though - you define both an input (`es.input.json`) and a query (basically reading from it).  
It's recommended to split the two - you'll end up with two different table that point out to the same index sure, but a table with an associated query means the data is filtered.

P.S. Defining a query that returns everything it's not just redundant, it's useless.

---

<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:** [July 6, 2017, 1:27pm UTC](https://discuss.elastic.co/t/reading-json-data-from-es-to-hive-with-a-single-string-field/35890/5 "2017-07-06T13:27:02Z")

</div>