Datasource API
In this section we present the API available to interact with datasources.
We currently supports two types of datasources:
-
JDBC to connect to any datasource providing a JDBC driver;
-
{es} to connect to an {es} remote clusters.
Datasource management
The endpoint for datasource management is at /_siren/connector/datasource
.
Datasource creation and modification
A datasource with a specific id
can be updated by issuing a PUT
request. The body of the request varies with the
type of the datasource.
JDBC datasource
PUT /_siren/connector/datasource/<id>
{
"jdbc": {
"username": "username",
"password": "password",
"driver": "com.db.Driver",
"url": "jdbc:db://localhost:5432/default",
"properties": {
"ssl": true
}
}
}
JDBC configuration parameters:
-
driver
: the class name of the JDBC driver. -
url
: the JDBC url of the datasource. -
username
: the username that will be passed to the JDBC driver when getting a connection (optional). -
password
: the password that will be passed to the JDBC driver when getting a connection (optional). -
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. -
properties
: a map of JDBC properties to be set when initializing a connection.
Elasticsearch datasource
PUT /_siren/connector/datasource/<id>
{
"elastic": {
"alias": "remotename"
}
}
Elasticsearch configuration parameters:
-
alias
: the name of the configured cluster alias in the remote cluster configuration.
Datasource retrieval
The datasource configuration can be retrieved by issuing a GET
request as follows:
GET /_siren/connector/datasource/<id>
Datasource deletion
To delete a datasource, issue a DELETE
request as follows:
DELETE /_siren/connector/datasource/<id>
Datasource listing
To list the datasources configured in the system, issue a GET
request as follows:
GET /_siren/connector/datasource/_search
Datasource validation
To validate the connection to a datasource, issue a POST
request as follows:
POST /_siren/connector/datasource/<id>/_validate
Datasource catalog metadata
To get the metadata related to a datasource connection catalog,
issue a POST
request as follows:
POST /_siren/connector/datasource/<id>/_metadata?catalog=<catalog>&schema=<schema>
The parameters are:
-catalog
: The name of the catalog,
-schema
: The name of the schema.
The parameters catalog
and schema
are optionals:
- If no catalog parameters is given, the API returns the catalog list.
- If no schema parameters is given, then the catalog parameter must be provided.
The API returns the schema list for the given catalog.
The result is a JSON document which contains the resource list for the given catalog and schema.
{
"_id": "postgres",
"found": true,
"catalogs": [
{
"name": "connector",
"schemas": [
{
"name": "public",
"resources": [
{
"name": "emojis"
},
{
"name": "Player"
},
{
"name": "Matches"
},
{
"name": "ingestion_testing"
}
]
}
]
}
]
}
Datasource field metadata
To get the field metadata related to a datasource connection resource (a table), issue a POST
request as follows:
POST /_siren/connector/datasource/<id>/_resource_metadata?catalog=<catalog>&schema=<schema>&resource=<resource>
The parameters are:
-catalog
: The name of the catalog,
-schema
: The name of the schema,
-resource
: The name of the resource (table).
The result is a JSON document which contains the columns list for the given catalog, schema and resource. It contains also the name of the primary key if it exists.
{
"_id": "postgres",
"found": true,
"columns": [
{
"name": "TEAM"
},
{
"name": "ID"
},
{
"name": "NAME"
},
{
"name": "AGE"
}
],
"single_column_primary_keys": [
{
"name": "ID"
}
]
}
Datasource query sample
This method runs a query and returns an array of results and an Elasticsearch type for each column found.
POST _siren/connector/datasource/<id>/_sample
{
"query": "SELECT * FROM events",
"row_limit": 10,
"max_text_size": 100
}
{
"_id": "valid",
"found": true,
"types": {
"location": "keyword",
"id": "long",
"occurred": "date",
"value": "long"
},
"results": [
{
"id": 0,
"occurred": 1422806400000,
"value": 1,
"location": "Manila"
},
{
"id": 1,
"occurred": 1422806460000,
"value": 5,
"location": "Los Angeles"
},
{
"id": 2,
"occurred": 1422806520000,
"value": 10,
"location": "Pompilio"
}
]
}
Datasource transform suggestions
To get a suggestion of a transform configuration that can be used by the ingestion, issue a POST
request as follows:
POST /_siren/connector/datasource/<id>/_transforms
{
"query": "SELECT * FROM events"
}
It executes the query and returns a collection of transform operations based on the columns returned by the query.
{
"_id": "postgres",
"found": true,
"transforms": [
{
"input": [
{
"source": "id"
}
],
"output": "id"
},
{
"input": [
{
"source": "occurred"
}
],
"output": "occurred"
},
{
"input": [
{
"source": "value"
}
],
"output": "value"
},
{
"input": [
{
"source": "location"
}
],
"output": "location"
}
]
}
Datasource type list
To get a list of supported connectors, issue a GET
request as follows:
GET /_siren/connector/datasource
{
"MySQL": {
"driverClassName": "com.mysql.jdbc.Driver",
"defaultURL": "jdbc:mysql://{{host}}:{{port}}{{databasename}}?useLegacyDatetimeCode=false&useCursorFetch=true",
"defaultPort": 3306,
"defaultQuery": "SELECT 1 AS N",
"disclaimer": "This is a sample connection string, see the <a target=\"_blank\" href=\"https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference.html\">MySQL Connector/J documentation</a> for further information.",
"virtualIndexSupported": true,
"ingestionSupported": true
},
"PostgreSQL": {
"driverClassName": "org.postgresql.Driver",
"defaultURL": "jdbc:postgresql://{{host}}:{{port}}{{databasename}}",
"defaultPort": 5432,
"defaultQuery": "SELECT 1 AS N",
"disclaimer": "This is a sample connection string, see the <a target=\"_blank\" href=\"https://jdbc.postgresql.org/documentation/94/connect.html\">PostgreSQL JDBC documentation</a> for further information.",
"virtualIndexSupported": true,
"ingestionSupported": true
}
}