I'm love being able to use Hive SQL to effectively filter Elasticsearch. 
However, I am not sure how to best access the elasticsearch term query from SQL. I've noted the 'es.query' = '?q=term' table property, which must be specified at table creation time. Is there a way to do a term query from SQL? Does the SQL LIKE operation leverage the term index?
Hive SQL is not pushed down to ES since there are no hooks one can use. That is, ES-Hadoop is unaware of what the user wants outside the number of fields requires for a table (and thus can perform projection).
Spark SQL on the other hand does that so ES-Hadoop can and will transform the queries/operations into query DSL automatically.
If you want/have to use Hive then you need to pass the queries yourself. This is not too much of a problem considering many times the data is known before hand and one can incorporate the queries into resources/files which can then be passed along (as oppose to writing them inline).
© 2020. All Rights Reserved - Elasticsearch
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant logo are trademarks of the Apache Software Foundation in the United States and/or other countries.