Aliases do not work with Opensearch

[ilsaloving]

I am trying to use superset to query wazuh 4.9.0, which is using Opensearch 2.13.

If I query one of the indexes directly, it works:

superset-1  | 2024-09-13 18:24:13,291:DEBUG:elasticsearch:> {"query":"select distinct agent.name from wazuh-alerts-4.x-2024.09.13 LIMIT 1001","fetch_size":10000}
superset-1  | 2024-09-13 18:24:13,291:DEBUG:elasticsearch:< {
superset-1  |   "schema": [{
superset-1  |     "name": "agent.name",
superset-1  |     "type": "keyword"
superset-1  |   }],
superset-1  |   "total": 2,
superset-1  |   "datarows": [
superset-1  |     ["endpoint-1"],
superset-1  |     ["endpoint-2"]
superset-1  |   ],
superset-1  |   "size": 2,
superset-1  |   "status": 200
superset-1  | }

The docs indicate that indexes that contain a . do not work, but no longer appears to be true.

But if I try to search an alias:

superset-1  |   warnings.warn(
superset-1  | 2024-09-13 18:28:43,172:DEBUG:urllib3.connectionpool:https://wazuh-indexer:9200 "POST /_opendistro/_sql/ HTTP/1.1" 200 190
superset-1  | 2024-09-13 18:28:43,172:INFO:elasticsearch:POST https://wazuh-indexer:9200/_opendistro/_sql/ [status:200 request:1.752s]
superset-1  | 2024-09-13 18:28:43,172:DEBUG:elasticsearch:> {"query":"select distinct agent.name from wazuh-alerts LIMIT 1001","fetch_size":10000}
superset-1  | 2024-09-13 18:28:43,172:DEBUG:elasticsearch:< {
superset-1  |   "error": {
superset-1  |     "reason": "There was internal problem at backend",
superset-1  |     "details": "Index type [wazuh-alerts] does not exist",
superset-1  |     "type": "IllegalArgumentException"
superset-1  |   },
superset-1  |   "status": 500
superset-1  | }

Additionally, the OpenDistro SQL documentation indicates that I should be able to use wildcards when selecting indexes (https://opendistro.github.io/for-elasticsearch-docs/docs/sql/#rest-api), but that doesn't appear to work either.

[marcari]

I had the same issue and it is linked to this issue: opensearch-project/sql#1398

After some investigation, I solved this by adding the parameter v2=1 to the sqlalchemy uri:
odelasticsearch+https://user:pass@vpc-myes.eu-west-3.es.amazonaws.com:443/?v2=1

Adding the parameter tells the plugin to adapt to the opensearch version and removes the offending fetch_size parameter.