# Unable to use es.index.read.missing.as.empty with spark sql
**URL:** https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [February 17, 2018, 8:40pm UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326 "2018-02-17T20:40:12Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@bjet007](https://discuss.elastic.co/u/bjet007)
#### Post date: [February 17, 2018, 8:40pm UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326/1 "2018-02-17T20:40:13Z")
I'm trying to read data with spark in elasticsearch on indexe that could not exist, since my index has a date pattern. Since it's expected in my situation I only want an empty dataset. Is there a way to do it?
I'm using Spark 2.2.1 and elasticsearch 5.6.7 and I did try setting "es.index.read.missing.as.empty" to yes and provide my StructType without any luck.
Here is a sample of my code:
```
val elasticSearchSchema = new StructType()
.add("name", StringType)
.add("client", StringType)
.add("timestamp", TimestampType)
val keepOnlySessionWithinPeriod =
col("timestamp")
.geq(lit(Timestamp.from(queryStartDate)))
.and(col("timestamp")
.lt(lit(Timestamp.from(startPeriod))))
val loadFromElastic = sparkSession
.read
.option("es.index.read.missing.as.empty",true)
.schema(ReadSchema.elasticSearchSchema)
.format("org.elasticsearch.spark.sql")
.load(s"my-index-2018-02-01/mytype")
.filter(keepOnlySessionWithinPeriod)
```
Thanks
---
### Author:  [@Srinath\_C](https://discuss.elastic.co/u/Srinath_C)
#### Post date: [February 28, 2018, 11:59am UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326/2 "2018-02-28T11:59:23Z")
> [@bjet007](#):
>
> es.index.read.missing.as.empty
Facing the same issue. The flag "es.index.read.missing.as.empty" doesn't seem to work.
In my case, path has two indices one exists and one doesn't.
---
### Author:  [@james.baiera](https://discuss.elastic.co/u/james.baiera)
#### Post date: [March 1, 2018, 10:00pm UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326/3 "2018-03-01T22:00:19Z")
I see that you've found it, but this is a known issue: [https://github.com/elastic/elasticsearch-hadoop/issues/1055](https://github.com/elastic/elasticsearch-hadoop/issues/1055)
---
### Author:  [@bjet007](https://discuss.elastic.co/u/bjet007)
#### Post date: [March 2, 2018, 11:04am UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326/4 "2018-03-02T11:04:40Z")
With spark SQL, the behaviour is an exception and not an empty result and in my specific case, i don't query multiple indices. Since my indices are time base, I know how my index are named, I just don't know if it exist.
Right now I'm getting an exception and the spark process terminate. What I'm expecting is to get the empty result so that I can continue my process and re-insert data in elastic like I can do with basic http request with the flags ignore\_unavailable and allow\_no\_indices.
I've been able to dig up to the fact that even if we provide a schema the Datasource classe try to load the mapping. See line [DefaultSource.scala](https://github.com/elastic/elasticsearch-hadoop/blob/829fd8c40f1812f321e15226b3b16ec62fb5618a/spark/sql-20/src/main/scala/org/elasticsearch/spark/sql/DefaultSource.scala#L220).
I did try to fix it, but i had 1 other test failing and getting 1 integration running was nearly impossible. I had to wait 30 minutes+ to test my code and for now I've been able to use my global alias to search my data.
---
### Author:  [@janna](https://discuss.elastic.co/u/janna)
#### Post date: [March 9, 2018, 10:51am UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326/5 "2018-03-09T10:51:21Z")
any timeline on when this issue will be addressed? Bit of a dealbreaker for us.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 6, 2018, 10:57am UTC](https://discuss.elastic.co/t/unable-to-use-es-index-read-missing-as-empty-with-spark-sql/120326/6 "2018-04-06T10:57:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.