# SparkSQL and ElasticSearch not inferring JSON Schema correctly, possible bugs? **URL:** \ **Category:** Elasticsearch\ **Created:** [February 11, 2015, 1:18am UTC](https://discuss.elastic.co/t/sparksql-and-elasticsearch-not-inferring-json-schema-correctly-possible-bugs/22080 "2015-02-11T01:18:57Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Aris\_V](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aris_v/32/915_2.png) [@Aris\_V](https://discuss.elastic.co/u/Aris_V)\ **Post date:** [February 11, 2015, 1:18am UTC](https://discuss.elastic.co/t/sparksql-and-elasticsearch-not-inferring-json-schema-correctly-possible-bugs/22080/1 "2015-02-11T01:18:57Z")
I'm using ElasticSearch with elasticsearch-spark-BUILD-SNAPSHOT and Spark/SparkSQL 1.2.0, from Costin Leau's advice. I want to query ElasticSearch for a bunch of JSON documents from within SparkSQL, and then use a SQL query to simply query for a column, which is actually a JSON key -- normal things that SparkSQL does using the SQLContext.jsonFile(filePath) facility. The difference I am using the ElasticSearch container. The big problem: when I do something like SELECT jsonKeyA from tempTable; I actually get the WRONG KEY out of the JSON documents! I discovered that if I have JSON keys physically in the order D, C, B, A in the json documents, the elastic search connector discovers those keys BUT then sorts them alphabetically as A,B,C,D - so when I SELECT A from tempTable, I actually get column D (because the physical JSONs had key D in the first position). This only happens when reading from elasticsearch and SparkSQL. It gets much worse: When a key is missing from one of the documents and that key should be NULL, the whole application actually crashes and gives me a java.lang.IndexOutOfBoundsException -- the schema that is inferred is totally screwed up. In the above example with physical JSONs containing keys in the order D,C,B,A, if one of the JSON documents is missing the key/column I am querying for, I get that java.lang.IndexOutOfBoundsException exception. I am using the BUILD-SNAPSHOT because otherwise I couldn't build the elasticsearch-spark project, Costin said so. Any clues here? Any fixes? Aris -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/d866e547-edf6-416f-92bb-8c61aac17d43%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d866e547-edf6-416f-92bb-8c61aac17d43%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Aris\_V](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aris_v/32/915_2.png) [@Aris\_V](https://discuss.elastic.co/u/Aris_V)\ **Post date:** [February 11, 2015, 9:45pm UTC](https://discuss.elastic.co/t/sparksql-and-elasticsearch-not-inferring-json-schema-correctly-possible-bugs/22080/2 "2015-02-11T21:45:03Z")
Costin Leua saw this on the Spark User Mailing List, and I have filed this as a bug in github: > > > I'm using ElasticSearch with elasticsearch-spark-BUILD-SNAPSHOT and Spark/SparkS…QL 1.2.0, from Costin Leau's advice. > > I want to query ElasticSearch for a bunch of JSON documents from within SparkSQL, and then use a SQL query to simply query for a column, which is actually a JSON key -- normal things that SparkSQL does using the SQLContext.jsonFile(filePath) facility. The difference I am using the ElasticSearch container. > > The big problem: when I do something like > > SELECT jsonKeyA from tempTable; > > I actually get the WRONG KEY out of the JSON documents! I discovered that if I have JSON keys physically in the order D, C, B, A in the json documents, the elastic search connector discovers those keys BUT then sorts them alphabetically as A,B,C,D - so when I SELECT A from tempTable, I actually get column D (because the physical JSONs had key D in the first position). This only happens when reading from elasticsearch and SparkSQL. > > It gets much worse: When a key is missing from one of the documents and that key should be NULL, the whole application actually crashes and gives me a java.lang.IndexOutOfBoundsException -- the schema that is inferred is totally screwed up. > > In the above example with physical JSONs containing keys in the order D,C,B,A, if one of the JSON documents is missing the key/column I am querying for, I get that java.lang.IndexOutOfBoundsException exception. > > I am using the BUILD-SNAPSHOT because otherwise I couldn't build the elasticsearch-spark project, Costin said so. > > Any clues here? Any fixes? On Tuesday, February 10, 2015 at 5:18:57 PM UTC-8, Aris V wrote: > I'm using Elasticsearch with elasticsearch-spark-BUILD-SNAPSHOT and > Spark/SparkSQL 1.2.0, from Costin Leau's advice. > > I want to query Elasticsearch for a bunch of JSON documents from within > SparkSQL, and then use a SQL query to simply query for a column, which is > actually a JSON key -- normal things that SparkSQL does using the > SQLContext.jsonFile(filePath) facility. The difference I am using the > Elasticsearch container. > > The big problem: when I do something like > > SELECT jsonKeyA from tempTable; > > I actually get the WRONG KEY out of the JSON documents! I discovered that > if I have JSON keys physically in the order D, C, B, A in the json > documents, the Elasticsearch connector discovers those keys BUT then sorts > them alphabetically as A,B,C,D - so when I SELECT A from tempTable, I > actually get column D (because the physical JSONs had key D in the first > position). This only happens when reading from elasticsearch and SparkSQL. > > It gets much worse: When a key is missing from one of the documents and > that key should be NULL, the whole application actually crashes and gives > me a java.lang.IndexOutOfBoundsException -- the schema that is inferred is > totally screwed up. > > In the above example with physical JSONs containing keys in the order > D,C,B,A, if one of the JSON documents is missing the key/column I am > querying for, I get that java.lang.IndexOutOfBoundsException exception. > > I am using the BUILD-SNAPSHOT because otherwise I couldn't build the > elasticsearch-spark project, Costin said so. > > Any clues here? Any fixes? > > Aris -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/ebb742a1-17d5-4c04-8c5c-221361699fde%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ebb742a1-17d5-4c04-8c5c-221361699fde%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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, 12:33am UTC](https://discuss.elastic.co/t/sparksql-and-elasticsearch-not-inferring-json-schema-correctly-possible-bugs/22080/3 "2017-07-06T00:33:17Z")