# Having trouble executing isin query from Pyspark to elastic

**URL:** <https://discuss.elastic.co/t/having-trouble-executing-isin-query-from-pyspark-to-elastic/125590>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [March 26, 2018, 11:08am UTC](https://discuss.elastic.co/t/having-trouble-executing-isin-query-from-pyspark-to-elastic/125590 "2018-03-26T11:08:36Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ankit\_gupta1](https://avatars.discourse-cdn.com/v4/letter/a/b782af/32.png) [@Ankit\_gupta1](https://discuss.elastic.co/u/Ankit_gupta1)\
**Post date:** [March 26, 2018, 11:08am UTC](https://discuss.elastic.co/t/having-trouble-executing-isin-query-from-pyspark-to-elastic/125590/1 "2018-03-26T11:08:37Z")

</div>

Hi,

I've trying to implement a simple scenario to get data from elastic into my spark program.

I have two indexes  
First one is search index using this i am fetching messageIds on the basis of a matching value from one attribute

#Fetching messageIds from the search Index matching search term

q ="""{  
"query": {  
"match": {"column":"valueXXXX"}  
}  
}"""

reader = sqlContext.read.format("org.elasticsearch.spark.sql").option("es.nodes",connection\_url).option("es.query", q).  
s\_df = reader.load("seachIndex1.0/search")  
df\_messageIds = s\_df.select('message\_id').distinct().rdd.map(lambda r: r[0].encode("utf-8")).take(5000)

\*\*\*\*\ ***This index contains only MessageIds my scenario is to use this messageIds and fetch messages from otherindex**

#Fetching messages from the messageIndex  
q ="""{  
"query": {  
"match\_all": {}  
}  
}"""

reader = sqlContext.read.format("org.elasticsearch.spark.sql").option("es.nodes",connection\_url).option("es.query", q).option("pushdown","true")  
m\_df = reader.load("messageIndex1.0/message")  
filtered\_df = m\_df.filter(col('system\_id').isin(['513ed057-f40e-43f8-b675-8fb3886c7640', '68d79d86-a7e2-46de-9aaf-f89341e66fe4']) == True)  
filtered\_df.show(5000)

Issue is that in current scenario it is taking too much time to filter out messages on the basis of Ids

I tried joining two data-frames .  
I tried using is in clause  
Also i am not able to send list of MessageIds with query to elasticSearch

Also I tried pushdown feature somehow it is also not working with IN clause.

Please suggest how can i optimize this scenario

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:** [April 23, 2018, 11:08am UTC](https://discuss.elastic.co/t/having-trouble-executing-isin-query-from-pyspark-to-elastic/125590/2 "2018-04-23T11:08:47Z")

</div>

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