Define a Query

New queries can be defined by the API:

Method: POST
Path: /context-sql-sync/query
Authorization header: Bearer [access_token]
Body
{
  "name": "queryAlias",
  "supportsParameters": true,
  "supportsNamedParameters": true,
  "componentLookupStrategy": "ASSETS|TAGS|ASSETS_THEN_TAGS",
  "fieldNames": "customField1, customField2",
  "validateFieldNames": true,
  "timeZone": "string",
  "sqlString": "Select id, componentId, type, description, start_event, start_time, stop_event, stop_time, customField1, customField2 from LOGBOOK where :startDate <= start_time and start_time < :endDate;",  
  "typeMapping": [
    { "PRODUCTION": "MAPPED_TYPE" },
    { "SOURCE_TYPE": "TM_TYPE" }
  ]
}

Parameters

Description

name

An arbitrary string.

supportsParameters

A boolean value (true|false) indicating whether the SQL query contains positional parameters. The default is true.

Positional parameters are specified with question marks '?' in the SQL query. The query should contain exactly two parameters - they will be replaced with the start and end time of the requested time interval respectively.

supportsNamedParameters

A boolean value (true|false) indicating whether the SQL query contains named parameters. The default is true. If this property is set to true, it will take precedence over supportsParameters.

Named parameters are specified with the ':startDate' and ':endDate' (without the quotes) values in the SQL query - they will be replaced with the start and end time of the requested time interval respectively.

componentLookupStrategy

A value specifying how to interpret the componentId returned by the query. The allowed values are ASSETS (componentId is interpreted as the external id of an asset), TAGS (componentId is interpreted as a tag name), and ASSETS_THEN_TAGS (componentId is interpreted as an asset and, if no asset is found, then componentId is interpreted as a tag name). The default is ASSETS.

fieldNames

A comma-separated list of values, each representing the name of a column in the SQL query (see below). These columns represent custom fields to be associated with the newly created or updated context items. This property is optional and defaults to null. Bear in mind that the specified values are validated against the SQL query and the request will fail if the field names are not found in the SELECT clause.

If the fields exist on the context type associated with the item, the values of the columns specified by this property will be imported as values of the fields with the respective technical names. If they don’t exist, then the column values will be imported as general properties of the context item.

validateFieldNames

Optional. If missing or set to true, field names specified in the query will be validated. If set to false, the validation will be skipped.

timeZone

Specifies the timezone of the dates in the SQL database. The default is UTC. The list of supported timezones can be found by using the following endpoints:

<TM-URL>/hps/api/monitoring/options/timezone/available

https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

sqlString

The string of the SQL query that will be executed against the datasource to fetch the items. The query is required to return (at least) the following columns (in order): id, componentId, type, description, startEvent, startTime, endEvent, endTime. The query can contain additional columns, but they should come in after these first eight columns. The extra columns are often necessary when using custom fields.

Additional information about the individual columns can be found below:

Column	Details
id	Unique identifier for each row in the database which will be prepended by prefix (see below) during the sync process. This is used as an external identifier of the context item. So that every row with an unique identifier will result in a single context item. The id should not contain spaces. If the id is not available, it can potentially be tailored from other columns in the database. The only requirement is that it is unique and does not change between requests.
componentId	The componentId should identify an already existing asset or tag in TrendMiner (according to the currently used lookup strategy, as specified by componentLookupStrategy). For assets this should be the ExternalId, for tags - the tag name. Note To get an overview of the synced asset structure and the external ids of the different assets you can use the "/assets/browse" endpoint of the TrendMiner Assets API. The external ID of the assets is identified by the “externalId” field. Note For assets imported via CSV, the ExternalId is the asset’s path.
type	Refers back to the "Context Item Type" as defined in the ContextHub Config by the TrendMiner Admin. Values can be mapped to an existing type in TrendMiner in the API request. In case none of the existing types matches, new types can be configured by the Admin in ContextHub>Config>Context item types. The type will be looked up in typeMapping (see below) and the associated value should be the technical identifier of the type to use for the context item.
description	Additional information (as a string) that can be included for the Context Item. Can be NULL if not present.
startEvent	Corresponds to the start-state of the workflow used. This field is currently ignored and can be hardcoded to any value, but the column should be present.
startTime	The start (timestamp) of the event in "YYYY-MM-DD hh:mm:ss" format.
endEvent	Corresponds to the end-state of the workflow used. This field is currently ignored and can be hardcoded to any value, but the column should be present.
endTime	The end (timestamp) of the event in "YYYY-MM-DD hh:mm:ss" format.

An example SQL query:

SELECT
id,
componentId,
type,
description,
start_event,
start_time,
stop_event,
stop_time
FROM some_table
WHERE :startDate <= start_time and start_time < :endDate;

In addition to the required columns, the SQL query could also select:

Zero or more columns to be used as custom fields. The names of these columns should also be included as a comma-separated list in the “fieldNames” property (see above).
The lastModifiedDate. If the datasource contains a lastModifiedDate column, the query can be modified to select records based on this column (rather than the startTime).

typeMapping

A table used to map the values in the type column (see sqlString above) to actual context types in TrendMiner. Even if the type column contains a type that exists, there should be an entry in the typeMapping table that maps the type to itself, e.g.:

"typeMapping": [
  { "PRODUCTION": "PRODUCTION" }
]

Example: If the datasource contains a lastModifiedDate column, the query can be modified to select records based on their lastModifiedDate. For example:

{
...
"sqlString": "Select id, componentId, type, description, start_event, start_time, stop_event, stop_time, last_modified_date from LOGBOOK where :startDate <= last_modified_date and last_modified_date < :endDate;"...}
...
}

This way, if an item is modified, it will be selected for sync, even if its start time is untouched.

The result will contain the id of the newly created query. This id will be used later.

In this section:

Documentation Portal

Define a Query

Note

Note

Search results