Query Configuration

About 4 min

The MS-SQL Driver provides several approaches to query the underlying MS-SQL data source.

Queries can be defined as virtual tags within the MS SQL connection dialog
Queries can be defined as virtual tags within on-premise MS-SQL Driver service configuration json file
Queries can be explicitly defined within each IOTA Vue component's action javascript

All virtual tag configurations are defined within driver configuration parameter: settings as an object. Within settings there is a single parameter called servers which is defined as an object array. Each server object represents specific server configuration.

Server object parameters

Parameter Name	Data Type	Description
`host`	string	Server instance host name or ip address. It must match the connection dialog Host definition
`name`	string	The user defined connection instance name. It must match the connection dialog name definition.
`datasettags`	object[]	Provides support for traditional queries. Results are visualized in table format.
`timeseriestags`	object[]	Provides explicit time-series virtual tag definitions.
`tables`	object[]	Allows to generate virtual assets with attributes and tags.

Note

The server configuration requires only one of the two parameters defined: name or host. If both parameters are defined, the driver will search on name first then on host second.

Example

{
  "driverid": "mssql-demo",
  "natsurl": "mon.iota-demo.com:443",
  "reqtopic": "us-east-1.site-demo.mssql.request",
  "statstopic": "us-east-1.site-demo.stats",
  "logstopic": "us-east-1.site-demo.mssql.logs",
  "publickey": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
  "metricstopic": "us-east-1.site-demo.metrics",
  "metricsmsec": 30000,
  "healthtopic": "iota.health",
  "healthmsec": 10000,
  "enablemetrics": true,
  "debug": 2,
  "logfilecount": 10,
  "logfilesize": 2097152,
  "logscanmsec": 10000,
  "settings": {
    "servers" : [
      {
        "host": "DEMO_HOST",
        "name": "Demo connection"
        "datasettags": [],
        "timeseriestags": [],
        "tables" : []
      }
    ]
  }
}

Dataset Tags

Provides support for traditional queries. Results are visualized in a table format.

Dataset virtual tag parameters

Parameter Name	Data Type	Description
`name`	string	`Required` The virtual tag name. Used for searching in IOTA Vue search dialog
`query`	string	`Required` The user defined query. Query can contained parameters.
`parameters`	object	Optional default collection of query parameters. Default: `null`
`isgmttime`	boolean	Allows to treat MS-SQL Server `Date`, `DateTime` and `Timestamp` column types as GMT times. Default: `false`

Example

"datasettags": [
    {
      "name": "sample_query1",
      "query": "SELECT TOP (20) * FROM [DEMO].[dbo].[TEST]",
      "isgmttime": true
    },
    {
      "name": "sample_query2",
      "query": "SELECT TOP (10) * FROM [DEMO].[dbo].[TEST] WHERE [temperature]>@temp",
      "parameters":
        {
          "@temp": 20
        }
    }
]

Time-series Tags

Provides explicit time-series virtual tag definitions.

Time-series virtual tag parameters

Parameter Name	Data Type	Description
`name`	string	The virtual tag name. Used for searching in IOTA Vue search dialog
`datatype`	string	Defines virtual tag data type. The source data is casted to user provided data type. Supported types: `Double`, `Int32` , `Int64`, `String`, `Bool`
`database`	string	Database name
`schema`	string	Schema owner. Default: `dbo`
`table`	string	Table name
`conditions`	string[]	Conditions array. Single condition example: `[Country]='USA'`
`timecolumn`	string	Column name which which contains timestamps. This column provides timestamp for time-series event.
`timecolumntype`	string	Time column data type. Supported types: `DateTime`, `DateTime2`, `Timestamp`, `DateTimeOffset`, `Date`, `UtcMsec`, `UtcSec`
`isgmttime`	boolean	Allows to treat MS-SQL Server `Date`, `DateTime` and `Timestamp` column types as GMT times. Default: `false`
`datacolumn`	string	Data column name. This column provides value for time-series event.
`snapshotquery`	string	Explicit snapshot query definition. This parameter overrides parameters: `database`, `schema`, `table`, `conditions`, `timecolumn`, `datacolumn` and is used to perform Snapshot function calls. Note, in `SELECT` statement `1st column` is used as timestamp and `2nd column` is used as value. To retrieve time based snapshot, in `WHERE` clause - use parameter `@timestamp`. This parameter is injected by the driver based on `timecolumn` type
`historyquery`	string	Explicit data history query definition. This parameter overrides parameters: `database`, `schema`, `table`, `conditions`, `timecolumn`, `datacolumn` and is used to perform data history retrievals. Note, in `SELECT` statement `1st column` is used as timestamp and `2nd column` is used as value. In order to make time framed queries - in `WHERE` clause - use parameters `@starttime` and `@endtime`. These parameters are injected by the driver based on `timecolumn` type

Example

"timeseriestags": [
    {
      "name": "timeseries 1",
      "datatype": "double",
      "database": "DEMO",
      "schema": "dbo",
      "table": "TEST",
      "timecolumn": "mytime",
      "timecolumntype": "datetime",
      "isgmttime": true
      "datacolumn": "temperature",
      "snapshotquery": null,
      "historyquery": null
    },
    {
      "name": "timeseries 2",
      "datatype": "double",
      "timecolumntype": "datetime",
      "isgmttime": true
      "snapshotquery": "SELECT TOP 1 [TimeCol], [DataCol] FROM DEMO.dbo.TestTable WHERE [TimeCol]<=@timestamp ORDER BY [TimeCol] DESC",
      "historyquery": "SELECT [TimeCol], [DataCol] FROM DEMO.dbo.TestTable WHERE [TimeCol]>=@starttime AND [tag.TimeCol]<=@endtime ORDER BY [TimeCol] ASC"
    }
]

Tables

Each table configuration maps MS-SQL tabular data into IOTA Vue virtual assets with attributes or tags. When provided, the groupby parameter is used to build assets with times-series attributes, otherwise the table configuration is used to generate virtual time-series tags.

Table Configuration parameters

Parameter Name	Data Type	Description
`database`	string	Database name
`schema`	string	Schema owner. Default: `dbo`
`name`	string	Table name. At least `name` or `namequery` must be defined within each table configuration.
`namequery`	string	User provided query which allows to obtain the list of tables with identical schemas. At least `name` or `namequery` parameter must be defined within each table configuration.
`groupby`	string[]	Column collection which allows to group data columns as attributes through building the asset hierarchy based on distinct column value combinations. The column name order is important - it is used to generate the asset hierarchy. Example: "GroupBy" : [ "country", "region_name", "province_name" ] parameter definition produces the following hierarchy: `country`\`region_name`\`province_name`. When `groupby` is provided - the associated data columns are treated as attribute collection which is linked to the asset defined by the `groupby` leaf element. When the `groupby` parameter is not defined, the table configuration is used to build virtual tags instead of an assets with attributes
`groupleafastag`	boolean	Set to `true` to enable the last element in `groupby` collection to be used as the attribute instead of an asset name. The first column obtained from `datacolumns` or `datacolumnquery` serves as the data source while all other data columns are ignored. Default: `false`
`timecolumn`	string	Column name which which contains timestamps. This column provides timestamp for time-series event.
`timecolumntype`	string	Time column data type. Supported types: `DateTime`, `DateTime2`, `Timestamp`, `DateTimeOffset`, `Date`, `UtcMsec`, `UtcSec`
`isgmttime`	boolean	Allows to treat MS-SQL Server `Date`, `DateTime` and `Timestamp` column types as GMT times. Default: `false`
`datacolumns`	string[]	The list of columns which are used as virtual tag and attribute values. Each listed column name creates a unique tag/attribute. By default, virtual tag/attribute name is equal to data column name. At least `datacolumns` or `datacolumnquery` parameter must be defined within each table configuration. Note, data columns are treated as virtual asset attributes only when `groupby` parameter is defined.
`datacolumnquery`	string	User provided query which allows to obtain the list of columns within the same table which data fields used as virtual tag and attribute values. At least DataColumns or DataColumnQuery parameter must be defined within each table configuration. Note, data columns are treated as virtual asset attributes only when `groupby` parameter is defined.

Example

  "tables" : [
    {
      "database" : "CloudSync",
      "schema": "dbo",
      "name" : "bqtable1",
      "namequery" : null,
      "timecolumn" : "LCLNow",
      "timecolumntype": "datetime",
      "isgmttime": false,
      "datacolumns" : [ "CurOff1", "calc_y1" ],
      "datacolumnquery" : null
    },
    {
      "database" : "CloudSync",
      "schema": "dbo",
      "name" : null,
      "namequery" : "SELECT TABLE_NAME FROM CloudSync.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'table%'",
      "timecolumn" : "LCLNow",
      "timecolumntype": "datetime",
      "isgmttime": false,
      "datacolumns" : [ "PowerCol_kW" ],
      "datacolumnquery" : null
    },
    {
      "database" : "DEMO",
      "schema": "dbo",
      "name" : "TestTable",
      "namequery" : null,
      "timecolumn" : "GMTTIme",
      "timecolumntype": "datetime2",
      "isgmttime": true,
      "datacolumns" : null,
      "datacolumnquery": "SELECT COLUMN_NAME from DEMO.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestTable' AND DATA_TYPE='float'"
    },
    {
      "database" : "DEMO",
      "schema": "dbo",
      "name" : "TestTable",
      "groupby" : [ "AREA", "PROCCELL", "UNIT" ],
      "groupleafastag": false,
      "namequery" : null,
      "timecolumn" : "GMTTIme",
      "timecolumntype": "datetime2",
      "isgmttime": true,
      "datacolumns" : null,
      "datacolumnquery": "SELECT COLUMN_NAME from DEMO.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestTable' AND DATA_TYPE='float'"
    }
  ]

Query Configuration

Connection Dialog

Driver Configuration File

Dataset Tags

Time-series Tags

Tables