Upsert (Update or Insert) documents using various data types (supply _id)
This example shows how to update or insert document for different datatype fields. _id is optional. If _id column is supplied then it does UPSERT action (Update or Insert) if _id not supplied then does only insert (auto generate new _id) Some fields can accept value as Raw JSON (e.g. nested, object, geo_point, geo_shape). Object field type can also accept value by nested field (e.g. [object_field.field1] ). Look at the Result column in the output to see if document was created or updated.
Standard SQL query example
This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.
UPSERT INTO datatype_test (
_id,
binary_field,
boolean_field,
byte_field,
date_field,
double_field,
float_field,
geo_point_field, --raw
--OR--
--"geo_point_field.lat",
--"geo_point_field.lon",
geo_shape_field, --raw
--OR--
--"geo_shape_field.type",
--"geo_shape_field.coordinates",
integer_field,
ip_field,
keyword_field,
long_field,
nested_field, --raw
object_field, --raw
--OR--
--"object_field.field1",
--"object_field.field2",
short_field,
text_field
)
VALUES(
2, -- _id (Optional - if not supplied then it inserts with auto-generated _id)
'SGVsbG8gd29ybGQ=', --binary_field --base64 value of "Hello world"
false, --bool
117, --byte_field
'2012-12-31T23:59:59.123', --date_field
1.123456789, --double_field
1.123456789, --float_field
--raw JSON must be in one line
'{ "lat": 40.7128, "lon": -74.0060 }', --geo_point_field
--OR--
-- 40.7128, -74.0060,
'{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }', --geo_shape_field
--OR--
--'polygon',
--'[[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]]',
123, --integer_field
'127.0.0.1', --ip_field
'thhi is text', --keyword_field
1234567890, --long_field
--raw JSON must be in one line
'[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]', --nested_field
'{"field1":"A","field2":"B"}', --object_field (Raw Value)
--OR--
--'object field keyword 1', --object_field.field1
--123, --object_field.field2
1, --short_field
'text field ' --text_field
)Using OPENQUERY in SQL Server
SELECT * FROM OPENQUERY([LS_TO_ELASTICSEARCH_IN_GATEWAY], 'UPSERT INTO datatype_test (
_id,
binary_field,
boolean_field,
byte_field,
date_field,
double_field,
float_field,
geo_point_field, --raw
--OR--
--"geo_point_field.lat",
--"geo_point_field.lon",
geo_shape_field, --raw
--OR--
--"geo_shape_field.type",
--"geo_shape_field.coordinates",
integer_field,
ip_field,
keyword_field,
long_field,
nested_field, --raw
object_field, --raw
--OR--
--"object_field.field1",
--"object_field.field2",
short_field,
text_field
)
VALUES(
2, -- _id (Optional - if not supplied then it inserts with auto-generated _id)
''SGVsbG8gd29ybGQ='', --binary_field --base64 value of "Hello world"
false, --bool
117, --byte_field
''2012-12-31T23:59:59.123'', --date_field
1.123456789, --double_field
1.123456789, --float_field
--raw JSON must be in one line
''{ "lat": 40.7128, "lon": -74.0060 }'', --geo_point_field
--OR--
-- 40.7128, -74.0060,
''{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }'', --geo_shape_field
--OR--
--''polygon'',
--''[[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]]'',
123, --integer_field
''127.0.0.1'', --ip_field
''thhi is text'', --keyword_field
1234567890, --long_field
--raw JSON must be in one line
''[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]'', --nested_field
''{"field1":"A","field2":"B"}'', --object_field (Raw Value)
--OR--
--''object field keyword 1'', --object_field.field1
--123, --object_field.field2
1, --short_field
''text field '' --text_field
)')Using EXEC in SQL Server (handling larger SQL text)
The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements.
This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).
Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY] syntax.
DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO datatype_test (
_id,
binary_field,
boolean_field,
byte_field,
date_field,
double_field,
float_field,
geo_point_field, --raw
--OR--
--"geo_point_field.lat",
--"geo_point_field.lon",
geo_shape_field, --raw
--OR--
--"geo_shape_field.type",
--"geo_shape_field.coordinates",
integer_field,
ip_field,
keyword_field,
long_field,
nested_field, --raw
object_field, --raw
--OR--
--"object_field.field1",
--"object_field.field2",
short_field,
text_field
)
VALUES(
2, -- _id (Optional - if not supplied then it inserts with auto-generated _id)
''SGVsbG8gd29ybGQ='', --binary_field --base64 value of "Hello world"
false, --bool
117, --byte_field
''2012-12-31T23:59:59.123'', --date_field
1.123456789, --double_field
1.123456789, --float_field
--raw JSON must be in one line
''{ "lat": 40.7128, "lon": -74.0060 }'', --geo_point_field
--OR--
-- 40.7128, -74.0060,
''{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }'', --geo_shape_field
--OR--
--''polygon'',
--''[[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]]'',
123, --integer_field
''127.0.0.1'', --ip_field
''thhi is text'', --keyword_field
1234567890, --long_field
--raw JSON must be in one line
''[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]'', --nested_field
''{"field1":"A","field2":"B"}'', --object_field (Raw Value)
--OR--
--''object field keyword 1'', --object_field.field1
--123, --object_field.field2
1, --short_field
''text field '' --text_field
)'
EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY]