API
Datasource management
The endpoint for datasource management is at /_siren/connector/datasources
.
Datasource creation and modification
A datasource with a specific id
can be updated by issuing a PUT
request as follows:
PUT /_siren/connector/datasource/<id> { "jdbc": { "username": "username", "password": "password", "driver": "com.db.Driver", "url": "jdbc:db://localhost:5432/default" } }
Body parameters:
jdbc
: The JDBC configuration of the datasource.
JDBC configuration parameters:
driver
: The class name of the JDBC driver.url
: the JDBC URL of the datasource.username
(optional): The username that will be passed to the JDBC driver when getting a connection.password
(optional): The password that will be passed to the JDBC driver when getting a connection.timezone
: If date and timestamp fields are stored in a timezone other than UTC, specifying this parameter will instruct the plugin to convert dates and times to/from the specified timezone when performing queries and retrieving results.
Datasource deletion
To delete a datasource, issue a DELETE
request:
DELETE /_siren/connector/datasource/<id>
Datasource listing
To list the datasources configured in the system, issue a GET
request:
GET /_siren/connector/datasource/_search
Datasource validation
To validate the connection to a datasource, issue a POST
request:
POST /_siren/connector/datasource/<id>/_validate
Virtual index management
Virtual index creation and modification
A virtual index with a specific id
can be updated by issuing a PUT
request:
PUT /_siren/connector/index/<id> { "datasource": "ds", "catalog": "catalog", "schema": "schema", "resource": "table", "key": "id" }
The ID of a virtual index must be a valid lowercase Elasticsearch index name; it is recommended to start virtual indices with a common prefix to simplify handling of permissions.
Body parameters:
datasource
: the ID of an existing datasource.resource
: the name of a table or view on the remote datasource.key
: the name of a unique column; if a virtual index has no primary key it will be possible to perform aggregations, however queries that expect a reproducible unique identifier will not be possible.catalog
andschema
: the catalog and schema containing the table specified in theresource
parameter; these are usually required only if the connection does not specify a default catalog or schema.
Virtual index deletion
To delete a virtual index, issue a DELETE
request:
DELETE /_siren/connector/index/<id>
When a virtual index is deleted, the corresponding concrete index is not deleted automatically.
Virtual index listing
To list the virtual indices configured in the system, issue a GET
request:
GET /_siren/connector/index/_search
Operations on virtual indices
The plugin supports the following operations on virtual indices:
get mapping
get field capabilities
search
msearch
get
mget
Search requests involving a mixture of virtual and normal Elasticsearch indices (for example, when using a wildcard) are not supported and will be rejected; it is however possible to issue msearch requests containing requests on normal Elasticsearch indices and virtual indices.
When creating a virtual index, the plugin will create an empty Elasticsearch index for interoperability with Search Guard and X-Pack; if an Elasticsearch index with the same name as the virtual index already exists and it is not empty, the virtual index creation will fail.
When deleting a virtual index, the corresponding Elasticsearch index will not be removed.
Type conversion
The plugin converts JDBC types to their closest Elasticsearch equivalent:
String types are handled as
keyword
fields.Boolean types are handled as
boolean
fields.Date and timestamp are handled as
date
fields.Integer types are handled as
long
fields.Floating point types are handled as
double
fields.
Complex JDBC types which are not recognized by the plugin are skipped during query processing and resultset fetching.
Supported search queries
The plugin supports the following queries:
match_all
term
terms
range
exists
prefix
wildcard
ids
bool
At this time the plugin provides no support for datasource specific full text search functions, so all these queries will work as if they were issued against`keyword` fields.
Supported aggregations
Currently the plugin provides support for the following aggregations:
Metric:
Average
Cardinality
Max
Min
Sum
Bucket:
Date histogram
Histogram
Date range
Range
Terms
Filters
Only terms aggregations can be nested inside a parent bucket aggregation.
Known Limitations
Cross backend join currently supports only integer keys.
Cross backend support has very different scalability according to the direction of the Join, a join which involves sending IDs to a remote system will be possibly hundreds of times less scalable (for example, thousands compared to millions) to one where the keys are fetched from a remote system.
Only terms aggregations can be nested inside a parent bucket aggregation.
The
missing
parameter in bucket aggregations is not supported.Scripted fields are not supported.
When issuing queries containing string comparisons, the plugin does not force a specific collation, if a table behind a virtual indices uses a case-insensitive collation, string comparisons will be case-insensitive.
Wildcards on virtual index names are not supported by any API; a wildcard search will silently ignore virtual indices.
Currently cross cluster searches on virtual indices are not supported.
Troubleshooting
Cannot reconnect to datasource by hostname after DNS update
When the Java security manager is enabled, the JVM will cache name resolutions indefinitely; if the system you’re connecting to uses round-robin DNS or the IP address of the system changes frequently, you will need to modify the following Java Security Policy properties:
networkaddress.cache.ttl
: the number of seconds to cache a successful DNS lookup. Defaults to-1
(forever).networkaddress.cache.negative.ttl
: the number of seconds to cache an unsuccessful DNS lookup. Defaults to10
, set to0
to avoid caching.