Reference

Endpoint Get documents from Index or Alias

Name

get_documents

Description

Gets documents from Index or Alias [API reference]

Related Tables

[Dynamic Table] , [Dynamic Table]

Parameters

Parameter Required Options
Name: Index

Label: Index

 YES
Name: Query

Label: Enter Query (JSON Format)
Option Value
All Records {"match_all": { } }
Record where comment or name contains TV word {"query_string": {"query": "comment:TV OR name:TV"} }
Record with comment field (attribute exists) {"query_string": {"query": "_exists_:comment"} }
Name: CursorTimeout

Label: CursorTimeout (Minutes)

Enter this value carefully. Do not enter very long timeout else it will hold cursor longer on server memory space
Option Value
1 1
2 2
5 5
10 10
60 60
180 180
Name: PageSize

Label: How many Record to fetch per request

Do not enter very large number or too small number to avoid performance issues.
Name: Alias

Label: Alias (Deprecated - Use Index instead)

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
_id DT_WSTR nvarchar(150) 150
_score DT_R8 float
[$parent.Pivot_Path$] DT_WSTR nvarchar(4000) 4000
[$parent.Pivot_Path$].lat DT_R8 float
[$parent.Pivot_Path$].lon DT_R8 float
[$parent.Pivot_Path$].type DT_WSTR nvarchar(30) 30
[$parent.Pivot_Path$].coordinates DT_TEXT varchar(MAX)
If the column you are looking for is missing, consider customizing ElasticSearch Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use ElasticSearch Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - ElasticSearch
Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch
ElasticSearch
[Dynamic Table]
There are no parameters to configure.
SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get documents from index or alias:

API Destination - ElasticSearch
Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch
ElasticSearch
[Dynamic Table]
Select
There are no parameters to configure.
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read ElasticSearch documents from Index (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Read ElasticSearch documents from Alias (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Search documents from Index using ElasticSearch Query language

Below example shows how to search on a comment field for TV word anywhere in the text for Index named MyIndexOrAliasName (it can be index name or alias name). For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

SELECT * FROM MyIndexOrAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName', Query='{"match": { "comment" : "TV" } }')
		--SELECT * FROM search WITH(Index='MyIndexName', Alias='MyAliasName', Query='{"match": { "comment" : "TV" } }')

Search documents from Alias using ElasticSearch Query language

Below example shows how to search on Alias rather than Index name. Alias is build on index (consider like a view in RDBMS). This example filtes data from Alias with some condition in the Query Text. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

SELECT * FROM MyAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName',Index='MyAliasName', Query='{"match": { "comment" : "TV" } }')

Read ElasticSearch documents from Index (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Read ElasticSearch documents from Alias (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Search documents from Index using ElasticSearch Query language

Below example shows how to search on a comment field for TV word anywhere in the text for Index named MyIndexOrAliasName (it can be index name or alias name). For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

SELECT * FROM MyIndexOrAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName', Query='{"match": { "comment" : "TV" } }')
		--SELECT * FROM search WITH(Index='MyIndexName', Alias='MyAliasName', Query='{"match": { "comment" : "TV" } }')

Search documents from Alias using ElasticSearch Query language

Below example shows how to search on Alias rather than Index name. Alias is build on index (consider like a view in RDBMS). This example filtes data from Alias with some condition in the Query Text. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

SELECT * FROM MyAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName',Index='MyAliasName', Query='{"match": { "comment" : "TV" } }')

get_documents endpoint belongs to [Dynamic Table] , [Dynamic Table] table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read ElasticSearch documents from Index (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Read ElasticSearch documents from Alias (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from Index using ElasticSearch Query language

Below example shows how to search on a comment field for TV word anywhere in the text for Index named MyIndexOrAliasName (it can be index name or alias name). For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'', Query=''{"match": { "comment" : "TV" } }'')
		--SELECT * FROM search WITH(Index=''MyIndexName'', Alias=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from Alias using ElasticSearch Query language

Below example shows how to search on Alias rather than Index name. Alias is build on index (consider like a view in RDBMS). This example filtes data from Alias with some condition in the Query Text. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'',Index=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Read ElasticSearch documents from Index (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Read ElasticSearch documents from Alias (all or with filter)

Gets documents by index name (i.e. Table name) or alias name (i.e. View name). Using WHERE clause invokes client side engine so try to avoid WHERE clause and use WITH clause QUERY attribute. Use search endpoint instead to invoke query. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from Index using ElasticSearch Query language

Below example shows how to search on a comment field for TV word anywhere in the text for Index named MyIndexOrAliasName (it can be index name or alias name). For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'', Query=''{"match": { "comment" : "TV" } }'')
		--SELECT * FROM search WITH(Index=''MyIndexName'', Alias=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from Alias using ElasticSearch Query language

Below example shows how to search on Alias rather than Index name. Alias is build on index (consider like a view in RDBMS). This example filtes data from Alias with some condition in the Query Text. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'',Index=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

get_documents endpoint belongs to [Dynamic Table] , [Dynamic Table] table(s), and can therefore be used via those table(s).