Insert documents using various data types
This example shows how to insert values for different datatype fields. 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] )
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.
INSERT INTO my_index (
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(
'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], 'INSERT INTO my_index (
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(
''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) = 'INSERT INTO my_index (
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(
''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]