Siren Platform User Guide

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 and schema: the catalog and schema containing the table specified in the resource 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 to 10, set to 0 to avoid caching.

Search results

    No results found