ElasticSearch Connector

Create a new index (i.e. Table)    [Read more...]

Create a new index (i.e. Create a new table). To trow error if table exists you can set ContineOnErrorForStatusCode=0

SELECT * FROM create_index WITH(Name='my_new_index_name', ContineOnErrorForStatusCode=1)

Delete an exising index (i.e. Table)    [Read more...]

Delete an exising index. It it exists it will show status code 400

SELECT * FROM delete_index WITH(Name='my_index_name', ContineOn404Error=1 )

Generic API Call for ElasticSearch    [Read more...]

When EndPoint not defined and you like to call some API use this way. Below example shows how to call CREATE INDEX API generic way. See other generic API call examples.

SELECT * FROM generic_request 
WITH(Url='/my_index_name'
	, RequestMethod='PUT'
--	, Body='{}'
--	, Headers='X-Hdr1:aaa || x-HDR2: bbb'
	, Meta='acknowledged:bool'
	)

List indexes    [Read more...]

Lists indexes

SELECT * FROM Indexes

Get index metadata    [Read more...]

Gets index metadata

SELECT * FROM get_index_metadata WITH (Index='my_index_name')

Read ElasticSearch documents from Index (all or with filter)    [Read more...]

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)    [Read more...]

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    [Read more...]

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/6.8/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    [Read more...]

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/6.8/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" } }')

Count ElasticSearch index documents using ElasticSearch Query language    [Read more...]

Below example shows how to get just count of documents from Index (single, multiple or all index). Optionally you can supply expression to filter. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/6.8/query-dsl-match-query.html

SELECT * FROM count WITH(Index='MyIndexOrAliasName') --//get count of documents in index / alias named MyIndexOrAliasName
SELECT * FROM count WITH(Index='*') --//get count of documents in all indices (total distinct _id found across all indices + alias) 
SELECT * FROM count WITH(Index='MyIndex1,MyIndex2,MyAlias1,MyAlias2')--//get count of documents in indices named MyIndex1, MyIndex2 and Alias named MyAlias1,MyAlias2
SELECT * FROM count WITH(Index='MyIndexOrAliasName', Query='{"match": { "comment" : "TV" } }') --//get count of documents in MyIndex where comment field contains word "TV"

Count ElasticSearch alias documents using ElasticSearch Query language    [Read more...]

Below example shows how to get just count of documents from Alias (single, multiple or all alias). Optionally you can supply expression to filter. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/6.8/query-dsl-match-query.html

SELECT * FROM count WITH(Index='MyIndexOrAliasName') --//get count of documents in index / alias named MyIndexOrAliasName
SELECT * FROM count WITH(Index='*') --//get count of documents in all indices (total distinct _id found across all indices + alias)
SELECT * FROM count WITH(Index='MyIndexOrAlias1,MyIndexOrAlias2') --//get count of documents in MyIndex1 and MyIndex2
SELECT * FROM count WITH(Index='MyIndex', Query='{"match": { "comment" : "TV" } }') --//get count of documents in Index named MyIndex where comment field contains word "TV"
SELECT * FROM count WITH(Index='MyAlias', Query='{"match": { "comment" : "TV" } }') --//get count of documents in Alias named MyAlias where comment field contains word "TV"

Using JSON Array / Value functions    [Read more...]

Below example shows how to select specific elements from value array or use JSON PATH expression to extract from document array

SELECT _id
        , JSON_ARRAY_FIRST(colors) as first_color
        , JSON_ARRAY_LAST(colors) as last_color
        , JSON_ARRAY_NTH(colors,3) as third_color
        , JSON_VALUE(locationList,'$.locationList[0].country') as first_preferred_country
        , JSON_VALUE(locationList,'$.locationList[?(@country=='India')].capital as capital_of_india
FROM shop WHERE _Id='1'

Insert documents into index with _id autogenerated    [Read more...]

When you dont supply _id column value, ElasticSearch will generate it automatically for you.

INSERT INTO MyIndex([MyCol1], [MyCol2] ) VALUES (100, 'A1')

Insert documents into index with your own _id    [Read more...]

Inserts documents into index with _id column. _id is string datatype so can be

INSERT INTO MyIndex(_id, [MyCol1], [MyCol2] ) VALUES ('A1234', 100, 'A1')

Insert documents using nested attribute and raw fragments (JSON sub-documents, arrays)    [Read more...]

This example produces JSON document like this {"_id": "some_auto_generated_id" , "Location": { "City" : "Atlanta" , "ZipCode" : "30060" },"ColorsArray ": ["Red", "Blue", "Green"],"SomeNestedDoc": { "Col1" : "aaa" , "Col2" : "bbb" , "Col2" : "ccc" }} . Notice that how Column name with Dot translated into nested Columns (i.e. City, ZipCode) and Prefix raw:: allowed to treat value as array or sub document.

INSERT INTO MyIndexName ([Location.City], [Location.ZipCode], [raw::ColorsArray], [raw::SomeNestedDoc] )
VALUES ('A1234', 'Atlanta', '30060', '["red","green","blue"]', '{"Col1":"aaa","Col2":"bbb","Col3":"ccc"}' )

Insert raw document (_rawdoc_ usage)    [Read more...]

This example shows how to insert document(s) in a raw format. When you use column name _rawdoc_ then its treated as RAW body. Notice that we use @ before string literal in value. This allow to use escape sequence (in this case \n for new line).

INSERT INTO shop(_RAWDOC_) 
VALUES(@'{"create":{"_index":"shop","_id":"1"}}\n{"name":"record-1","colors":["yellow","orange"]}\n{"create":{"_index":"shop","_id":"2"}}\n{"name":"record-2","colors":["red","blue"]}\n')

Update documents in index    [Read more...]

Updates documents in index

UPDATE MyIndex
  SET Col1 = 'NewValue-1', Col2 = 'NewValue-2'
  WHERE _Id = 'A1234'

Update raw document (_rawdoc_ usage)    [Read more...]

This example shows how to update document(s) in a raw format. When you use column name _rawdoc_ then its treated as RAW body. Notice that we use @ before string literal in value. This allow to use escape sequence (in this case \n for new line).

UPDATE shop SET _rawdoc_ = @'{"update": {"_index": "shop", "_id": "1"}}\n{ "doc": {"colors":["yellow","orange"] } }\n{"update": {"_index": "shop", "_id": "2"}}\n{ "doc": {"colors":["yellow","blue"] } }\n'

Update array or sub document    [Read more...]

This example shows how to update Array / nested Sub-document by adding raw:: prefix infront of column name to treat column as json fragment

UPDATE MyIndex
  SET name = 'abcd', [raw::colors]='["yellow","red"]', [raw::location]='{x:10, y:20}' 
  WHERE _id='1'

Delete documents from index    [Read more...]

Deletes documents from index

DELETE MyIndex WHERE _id = 'A1234'