Can I force Elasticseach to do a match_phrase when filtering a field?

Dremio University
1

Hello Dremio friends!

I’m connecting Dremio to Elasticsearch v. 7.10.2.

{
  "version" : {
    "number" : "7.10.2",
    "build_flavor" : "oss",
    "build_type" : "rpm",
    "build_hash" : "---------------------------------",
    "build_date" : "2021-01-13T00:42:12.435326Z",
    "build_snapshot" : false,
    "lucene_version" : "8.7.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
}

I have problems when making queries using ‘=’ or ‘IN’ to filter fields with more than one word… Can I force Elastic to use match_phrase when filtering with Dremio SQL?

Also tried turning on the option Use index/doc fields when pushing down aggregates and filters on analyzed and normalized fields (may produce unexpected results), but It really gives me unexpected results … is like Elastic is doing “OR” with the parts of the text I’m filtering. Need to search the entire phrase, like “Name Surname”. Example:

SELECT product_id
FROM  ElasticSource.my_index
WHERE product_name IN ('My Complete Product Name1','My Complete Product Name2')

That query above is mixing the returned products on results.

The only way I could make that work is using CONTAINS… but the filtering line is gerated automatically on my software and I can’t change this behavior. That command bellow works well:

SELECT product_id
FROM  ElasticSource.my_index
WHERE CONTAINS(product_name, ('"My Complete Product Name1"', '"My Complete Product Name2"')

But I need to add double quotes inside the simple quotes too.

Can someone help me on this?
Thanks!

2

Maybe this is happening because I’m connecting to ElasticSearch on Open Distro version. Apache Superset uses SQLAlchemy for connecting on ElasticSearch and it has one connector for each type of distribution.
I think Dremio should have an option to differentiate between these two versions.
https://github.com/preset-io/elasticsearch-dbapi