# Best practice elasticsearch index schema for Spark SQL

**URL:** <https://discuss.elastic.co/t/best-practice-elasticsearch-index-schema-for-spark-sql/38011>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [December 27, 2015, 10:43pm UTC](https://discuss.elastic.co/t/best-practice-elasticsearch-index-schema-for-spark-sql/38011 "2015-12-27T22:43:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ebuildy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ebuildy/32/6070_2.png) [@ebuildy](https://discuss.elastic.co/u/ebuildy)\
**Post date:** [December 27, 2015, 10:43pm UTC](https://discuss.elastic.co/t/best-practice-elasticsearch-index-schema-for-spark-sql/38011/1 "2015-12-27T22:43:36Z")

</div>

Hello,

I am using elasticsearch with SparkSQL in order to query my data from Tableau, it's working for very simple index structure, but as soon as I add nested fields I got always exception such as:

> Field 'product' not found; typically this occurs with arrays which are not mapped as single value

Hence my question, is here a best practice for define data schema? (just as avoid nested array maybe..) what exactly means this kind of error?

I am using Spark 1.5.2, with ES 2.1 and Hue notebooks:

```
CREATE TEMPORARY TABLE events_all USING org.elasticsearch.spark.sql OPTIONS (nodes "elasticsearch", path "events/events", read.field.include "event.*");

SELECT COUNT(*) FROM events_all

```

Will error:

org.elasticsearch.hadoop.EsHadoopIllegalStateException: Field 'product' not found; typically this occurs with arrays which are not mapped as single value  
at org.elasticsearch.spark.sql.RowValueReader$class.rowColumns(RowValueReader.scala:33)  
at org.elasticsearch.spark.sql.ScalaRowValueReader.rowColumns(ScalaEsRowValueReader.scala:13)  
at org.elasticsearch.spark.sql.ScalaRowValueReader.createMap(ScalaEsRowValueReader.scala:49)  
at org.elasticsearch.hadoop.serialization.ScrollReader.map(ScrollReader.java:645)  
at org.elasticsearch.hadoop.serialization.ScrollReader.read(ScrollReader.java:588)  
at org.elasticsearch.hadoop.serialization.ScrollReader.map(ScrollReader.java:661)  
at org.elasticsearch.hadoop.serialization.ScrollReader.read(ScrollReader.java:588)  
at org.elasticsearch.hadoop.serialization.ScrollReader.readHitAsMap(ScrollReader.java:383)  
at org.elasticsearch.hadoop.serialization.ScrollReader.readHit(ScrollReader.java:318)  
at org.elasticsearch.hadoop.serialization.ScrollReader.read(ScrollReader.java:213)  
at org.elasticsearch.hadoop.serialization.ScrollReader.read(ScrollReader.java:186)  
at org.elasticsearch.hadoop.rest.RestRepository.scroll(RestRepository.java:438)  
at org.elasticsearch.hadoop.rest.ScrollQuery.hasNext(ScrollQuery.java:86)  
at org.elasticsearch.spark.rdd.AbstractEsRDDIterator.hasNext(AbstractEsRDDIterator.scala:43)  
at scala.collection.Iterator$$anon$11.hasNext(Iterator.scala:327)

---

<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:** [January 9, 2016, 1:06pm UTC](https://discuss.elastic.co/t/best-practice-elasticsearch-index-schema-for-spark-sql/38011/2 "2016-01-09T13:06:12Z")

</div>

Hi,

Support for field arrays has been introduced two milestone versions ago in ES-Hadoop 2.2 and in rc1 also documented. Can you please review [this section of the docs](https://www.elastic.co/guide/en/elasticsearch/hadoop/master/mapping.html#mapping-multi-values) and report back if the issue persists?

Thanks,

---

<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:26pm UTC](https://discuss.elastic.co/t/best-practice-elasticsearch-index-schema-for-spark-sql/38011/3 "2017-07-06T13:26:43Z")

</div>