QueryPassthrough
This option allows you to pass exact queries to Elasticsearch.
Data Type
bool
Default Value
false
Remarks
Setting this property to True enables the connector to pass an Elasticsearch query as-is to Elasticsearch. There are two options for submitting as-is queries to Elasticsearch: SQL and Search DSL.
SQL API
Elasticsearch version 6.3 and above supports a SQL API endpoint. When set to true, this option allows you to pass SQL queries directly to the Elasticsearch SQL API. Columns will be identified based on the metadata returned in the response.
Supported SQL syntax and commands can be found in the Elasticsearch documentation.
Note: SQL functionality is limited to what is supported by Elasticsearch.
Search DSL
Alternatively, queries can be submitted using Elasticsearch's Search DSL language, which includes Query DSL. This functionality is available in all versions of Elasticsearch.
The supported query syntax is JSON using the query passthrough syntax described below.
The JSON Passthrough Query Syntax supports the following elements:
| Element Name | Function |
| index | The Elasticsearch index (or schema) to query. This is a JSON element that takes a string value. |
| type | The Elasticsearch type (or table) to query within index. This is a JSON element that takes a string value. |
| docid | The Id of the document to query within index.type. This is a JSON element that takes a string value. |
| apiendpoint | The Elasticsearch API Endpoint to query. Default value is '_search'. This is a JSON element that takes a string value. |
| requestdata | The raw Elasticsearch Search DSL that will be sent to Elasticsearch as is. The value is a JSON object that maps directly to the format required by Elasticsearch. |
The index, type, docid, and apiendpoint are used to generate the URL where the requestdata will be sent. The URL is generated using the following format: [Server]:[Port]/[index]/[type]/[docid]/[apiendpoint]. If any of the JSON passthrough elements are not specified, they will not be added to the URL.
Below is an example of a passthrough query. This example will retrieve the first 10 documents from megacorp.employee that contain a last_name of 'smith'. The results will be ordered by first_name in descending order.
{
"index": "megacorp",
"type": "employee",
"requestdata":
{
"from": 0,
"size": 10,
"query": {"bool":{"must":{"term":{"last_name":"smith"}}}},
"sort": {"first_name":{"order":"desc"}}
}
}
When using QueryPassthrough queries, the metadata is determined by the data returned in the response. RowScanDepth identifies the depth of the records that will be scanned to determine the metadata (columns and types). Since the metadata is based on the response data, passthrough queries may display different metadata than a similar query performed using the SQL syntax (where the metadata is retrieved directly from Elasticsearch).