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", "properties": { "ssl": true } } }
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
: 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.
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 fields 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 transforms suggestions
To get a suggestion of 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 coluns 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 } }