# Spark-SQL: Ensure SQL query gets translated to ES query

**URL:** <https://discuss.elastic.co/t/spark-sql-ensure-sql-query-gets-translated-to-es-query/40661>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [February 1, 2016, 7:57pm UTC](https://discuss.elastic.co/t/spark-sql-ensure-sql-query-gets-translated-to-es-query/40661 "2016-02-01T19:57:46Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Robbie](https://avatars.discourse-cdn.com/v4/letter/r/2acd7d/32.png) [@Robbie](https://discuss.elastic.co/u/Robbie)\
**Post date:** [February 1, 2016, 7:57pm UTC](https://discuss.elastic.co/t/spark-sql-ensure-sql-query-gets-translated-to-es-query/40661/1 "2016-02-01T19:57:46Z")

</div>

Hi,  
I am attempting to get started with the spark elasticsearch connector, and I notice that my SQL query never gets translated to ES search query with pushdown enabled.

Could you let me know what is wrong in the following set of steps? I registered the dataFrame through Spark's DataSource, but still dont see the query getting executed on ElasticSearch.

`SparkConf conf = new SparkConf().setAppName("Simple Application"); Map<String,String> dataFrameOptions = new HashMap<String,String>(); dataFrameOptions.put("es.resource", "myindex/account"); dataFrameOptions.put("es.nodes","192.168.224.94"); dataFrameOptions.put("es.port","9200"); dataFrameOptions.put("es.index.auto.create","no"); dataFrameOptions.put("es.nodes.discovery","false"); dataFrameOptions.put("pushdown","true"); dataFrameOptions.put("double.filtering","false"); JavaSparkContext sc = new JavaSparkContext(conf); SQLContext sqlContext = new org.apache.spark.sql.SQLContext(sc); DataFrame myEsDump = sqlContext.read().format("org.elasticsearch.spark.sql").options(dataFrameOptions).load("myindex/account"); myEsDump.registerTempTable("allAccounts"); DataFrame accounts = sqlContext.sql("SELECT name FROM allAccounts WHERE name = 'Name-888'");`

Here are the versions that I am using

`<dependency> <!-- Spark dependency --> <groupId>org.apache.spark</groupId> <artifactId>spark-core_2.10</artifactId> <version>1.6.0</version> </dependency> <dependency> <groupId>org.elasticsearch</groupId> <artifactId>elasticsearch-spark_2.10</artifactId> <version>2.2.0-rc1</version> </dependency>`

Also, in the logs I see something as below, however, I dont see the query being run on ElasticSearch ( I have search/fetch slow logs enabled for 0s)

`16/02/01 11:49:23 DEBUG DataSource: Pushing down filters [EqualTo(name,Name-888)] 16/02/01 11:49:23 TRACE DataSource: Transformed filters into DSL $filterString`

---

<div class="post-metadata">

**Author:** ![Robbie](https://avatars.discourse-cdn.com/v4/letter/r/2acd7d/32.png) [@Robbie](https://discuss.elastic.co/u/Robbie)\
**Post date:** [February 2, 2016, 12:13am UTC](https://discuss.elastic.co/t/spark-sql-ensure-sql-query-gets-translated-to-es-query/40661/2 "2016-02-02T00:13:30Z")

</div>

Turns out that it is issuing scan/scroll search commands. However, my index\_search and index\_fetch logging is not showing it, which led me to an incorrect presumption. Packet tracing revealed that the queries are being sent to ES.

---

<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:** [February 21, 2016, 5:25pm UTC](https://discuss.elastic.co/t/spark-sql-ensure-sql-query-gets-translated-to-es-query/40661/3 "2016-02-21T17:25:20Z")

</div>

Fwiw, 2.2 GA actually fixed the logs messages and on TRACE/DEBUG mode one sees the actual query from both Spark and ES.

---

<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/spark-sql-ensure-sql-query-gets-translated-to-es-query/40661/4 "2017-07-06T13:26:13Z")

</div>