Use SQL Access in AWS Opensearch python client

Question

I'd love to use SQL in AWS OpenSearch.

Is there a way how to achieve this using opensearchpy driver?

My attempts:

es = OpenSearch(
    hosts=[{'host': config.es_host, 'port': 443}],
    http_auth=aws_auth,
    use_ssl=True,
    verify_certs=True,
    max_retries=10,
    retry_on_timeout=True,
    connection_class=RequestsHttpConnection
)

es.search(index='user-sessions', body={
    'query': 'select * from user-sessions limit 20'
}

...leads to this exetion:

opensearchpy.exceptions.RequestError: RequestError(400, 'parsing_exception', 'Unknown key for a VALUE_STRING in [query].')

this one also doesn't work:

es.sql.query(body={
    'query': 'select * from user-sessions limit 20'
})

...leads to this exception:

AttributeError: 'OpenSearch' object has no attribute 'sql'

Any ideas anyone?

score 3 · Accepted Answer · edited Jun 21 '22 at 06:23

@Ikar: For AWS Opensearch version, below should work for you:

response = es.transport.perform_request(
    'POST',
    '/_plugins/_sql',
    body={'query': 'SELECT * from user-sessions LIMIT 50'}
)

print(response)

Note: Response will be an python dictionary and in datarows you will see the value of your search result.

For more information check links below:

Use SQL Access in AWS Opensearch python client

1 Answers1