Skip to main content

Configuration

About 9 min

In order for IOTA Vue to access data on Databricks - the IOTA Databricks Driver must be configured and deployed on
network
which has access to Databricks. The IOTAVue uses distributed "mesh-like" data source topology:

Users are free to define what Region and Site mean for their unique deployment.

NameExampleDescription
Regionus-east-1geographical territory, similar to availability regions
SitesiteBspecific plant or a building

To simplify the installation process, the IOTA Software provides a container image with the latest driver version. The
user is only required to select/create a region and site. Then for the selected driver type, click add a new server.
The container contains all necessary driver's configurations with public and private keys for secure communication
with the company's IOTA Vue NATs message bus.

To Access the Data Sources menu, click at the top right corner on the "hamburger" icon, then select "Data Sources".

open_datasources
open_datasources

Add Region

Click on the "Add" icon to add a new site, then in the popup dialog - enter the site name. Make sure the SEEQ checkbox
is selected. Each site can contain multiple driver types. For each checked driver type - the instance pane will contain
selected driver tabs to which connection instances can be added.

There are two input fields:

  • Site name
  • Channel name (optional)

The site name is a user-friendly name, and the channel name can contain abbreviations. If the channel name is not
provided, it is automatically assigned to the site name on the "Apply" button click.

ds region
ds region

Add Site

Click on the "Add" icon to add a new site, then in the popup dialog - enter the site name. Make sure the Generic SQL
checkbox
is selected. Each site can contain multiple driver types. For each checked driver type - the instance pane will contain
selected driver tabs to which connection instances can be added.

There are three input fields:

  • Site name
  • Driver type
  • Channel name (optional)

The site name is a user-friendly name, and the channel name can contain abbreviations. If the channel name is not
provided, it is automatically assigned to the site name on the "Apply" button click.

ds site
ds site

Add connection

The Databricks Driver container is automatically available in the IOTA Vue data sources section configured for specific
Region\Site.

Tips

The installer already contains all required configurations for specific Region\Site connectivity.

To add a new Databricks server connection navigate to data sources menu, then:

  1. Select region of interest
  2. Select the site of interest
  3. Select the tab named "GenericSQL" within the selected site.
  4. At the top right corner - click on the "Add" icon.

The Databricks Driver's connection dialog will appear.

databricks instance
databricks instance
  1. Provide a name for the Connection to the database. All search results will be prefixed with this name
  2. Specify the database type.
  3. Specify the connection settings:
    1. Host should be like this: adb-3437785601138938.18.azuredatabricks.net
    2. Endpoint like this: /sql/1.0/warehouses/1d9459d7889d9b35
    3. Select Authentication type
      1. If Auth type is PAT - put your PAT token in Token field
      2. If Auth type is M2M - fill Client ID, Client Secret, and OAuth Endpoint fields
  4. Optional step, set a timeout variable. The variable uses for timeout exceptions between the Databricks Driver and
    database server.
  5. Specify the Database schema settings.
  6. Please check the "Use in Quick Search" checkbox if the Connection should be used in the quick search sidebar. If not
    checked, the configured connection will only be available in Advanced search.
  7. Click on the "Apply" to save changes.

If Connection has the "Use in Quick Search" checkbox selected, it becomes available in a quick search sidebar.

Driver Configuration

On every startup the Databricks Driver reads its configuration from the environment variables.

Tips

The Databricks Driver comes with a pre-configured environment. Therefore, manual configuration is not required.

Configuration parameters

Environment nameRequiredDefault ValueData TypeDescription
DRIVERID⚫""stringDriver Id - used for logging purposes only
LOG_LEVEL⚫ERRORstringDebug verbose level: ERROR, DEBUG, INFO
NATS_HOST🟢nullstringNATs server url with port
SEED_KEY_PATH🟢nullstringNATs NKey public key string. Private key must be defined in file: "privatekey.nk"
RQS_TOPIC🟢nullstringNATs topic on which driver listens for requests
HEALTH_TOPIC⚫""stringNATs topic for sending driver's health
HEALTH_SCHEDULING⚫10000intHow often in milliseconds to send health data. Default: 10000 = 10 sec
LOGS_TOPIC⚫""stringNATs topic for sending driver's logs
METRICS_TOPIC⚫""stringNATs topic for sending driver's metrics
METRICS_SCHEDULING⚫30000intHow often in milliseconds to send metrics data. Default: 30000 = 30 sec
ENABLE_METRICS⚫trueboolEnables metrics reporting

Database schema settings

This section provides instructions on how to configure the generic driver for data ingestion from SQL-like databases.

Settings are the JSON object described bellow

{
  "tableSettings": [
    {
      "Id": string,
      "Name": string,
      "TimeColumn": string,
      "TimeUnit": string,
      "TimeLocation": string,
      "TimeStringFormat": string,
      "IntervalForSnapshotsInMin": int,
      "DataColumns": []string,
      "GroupBy": []string,
      "LastGroupAsTagName": bool,
      "TagProperties": JSON,
      "SchemaName": string
    }
  ],
  "datasetTags": [
    {
      "name": string,
      "query": string,
      "parameters": JSON
    }
  ],
  "writeDataTags": [
    {
      "name": string,
      "query": string,
      "parameters": JSON
    }
  ]
}

tableSettings

This section describes the DB tables structure.

  • Id, Name - (required) table name
  • SchemaName - name of the schema stores table
  • TimeColumn - (required) name of the column stores timestamp or datetime string
  • TimeUnit - units of the time
  • TimeLocation - a time location used by its name (e.g. "America/New_York"). TimeLocation is used to define a timezone
    used by your DB, by default it is UTC. You can find a comprehensive list of time zone names in the IANA Time Zone
    Database, which Go's time package uses.
  • TimeStringFormat - format of the datetime string. Used when the TimeColumn stores datetime as string. The format is
    described here Golang datetime string format
  • IntervalForSnapshotsInMin - minutes to narrow scanning a table, it is used for tabular data, if you know that your
    columns is updated every day, you can specify it in this property, and the driver will build a query like give me a
    snapshot in this range: now()-interval. Instead of give me snapshot order by time DESC, which scans the whole table.
    This option will significantly improve the speed of the query.
  • DataColumns - (required) list of the columns names stores tag values
  • GroupBy - list of the columns names stores asset tree. Used with Asset-Like and Metadata-Value tables
  • LastGroupAsTagName - (required) flag represents is last element in the GroupBy list is tag name column
  • TagProperties - map of the key-value pairs where key is one of ["uom", "description", "upbnd", "lobnd"] and value is
    column name related to key tag property
    • uom - units of the tag measurement
    • description - tag description
    • upbnd - tag upper bound
    • lobnd - tag lower bound

datasetTags

This section describes the custom queries to DB. This queries will be displayed in tag search.

  • name - unique query name
  • query - query string. May contains parameters placeholders
  • parameters - map of the key-value pairs where key is the name of query parameter and value is the parameter value

See example of the configuration

writeDataTags

This section describes the custom INSERT|UPDATE|DELETE queries to DB.

  • name - unique query name
  • query - query string. May contains parameters placeholders
  • parameters - map of the key-value pairs where key is the name of query parameter and value is the parameter value
    The detailed configuration is described in the section Write data Tags
    See example of the configuration

Reading Data from Tabular Tables

In statistics, tabular data refers to data organized in a table format with rows and columns. Consider the following
example of tabular data:

IDcompressor_poweroptimizerrelative_humiditytemperaturewet_bulbts
00.0029228062.3518159679.6413428770.299202912022-11-03 12:19
10.0029228062.3881034579.6386206970.307109572022-11-03 12:20
20.0029228062.437112779.6341396270.317065172022-11-03 12:21
30.0029228062.4861219579.6296585470.327020762022-11-03 12:22

Each column in this table represents a TAG. To configure the driver to read this data, use the following
configuration:

{
  "Id": "tabular_data",
  "Name": "tabular_data",
  "TimeColumn": "ts",
  "DataColumns": [
    "compressor_power",
    "optimizer",
    "relative_humidity",
    "temperature",
    "wet_bulb"
  ],
  "GroupBy": null,
  "LastGroupAsTagName": false
}

Configuration Details:

Config ItemDescription
IdUnique ID used internally by the driver
NameName of the database's table
TimeColumnSpecifies the column containing the time series
DataColumnsArray of columns to convert into tags
GroupByShould be empty or null for tabular data
LastGroupAsTagNameShould be false for tabular data

This configuration allows the driver to effectively handle and read tabular data from SQL-like databases.

Reading Data from Asset-like Tables

Additionally, data can be stored in the following table format:

IDtssiteidmachineidlineidtagvalue
1'2022-11-03 21:27:47'Site 1MachineALine 1Temperature-47.43
2'2022-11-03 21:27:47'Site 1MachineALine 1Pressure25.50
3'2022-11-03 21:27:47'Site 1MachineALine 1Power-14.03
4'2022-11-03 21:27:47'Site 1MachineBLine 1Temperature79.61
5'2022-11-03 21:27:47'Site 1MachineBLine 1Pressure-27.63
6'2022-11-03 21:27:47'Site 1MachineBLine 1Power99.65
7'2022-11-03 21:27:47'Site 1MachineCLine 1Temperature32.08
8'2022-11-03 21:27:47'Site 1MachineCLine 1Pressure79.92
9'2022-11-03 21:27:47'Site 1MachineCLine 1Power162.36
10'2022-11-03 21:27:47'Site 2MachineALine 1Temperature91.21
11'2022-11-03 21:28:47'Site 2MachineBPower38.12
12'2022-11-03 21:41:47'Site 1MachineBPressure-27.70

This table structure can be represented as an asset tree:

  • Site1
    • Line 1
      • MachineA
        • Temperature
        • Pressure
        • Power
      • MachineB
        • Temperature
        • Pressure
        • Power
      • MachineC
        • Temperature
        • Pressure
        • Power
  • Site2
    • Line 1
      • MachineA
        • Temperature
      • MachineB
        • Power

The configuration for processing such table data is as follows:

{
  "ID": "asset_tree_data",
  "Name": "asset_tree_data",
  "TimeColumn": "ts",
  "DataColumns": [
    "value"
  ],
  "GroupBy": [
    "siteid",
    "lineid",
    "machineid",
    "tag"
  ],
  "LastGroupAsTagName": true
}

Configuration Details:

Config ItemDescription
IdUnique ID used internally by the driver
NameName of the database's table
TimeColumnSpecifies the column containing the time series
DataColumnsArray of columns to convert into tags
GroupByAn array that defines the hierarchy for creating the asset tree structure
LastGroupAsTagNameShould be true, as we want to show the 'value' column as the value of the last tag in the hierarchy

Reading data From Metadata and Value Data

Sometimes data is stored in a dimension table and a fact table. This is a popular model choice because it allows
flexibility in the metadata (the dimension table) while allowing the value table (the fact table) to grow.

Metadata (Dimension Table)

siteIdlineIdmachineIdtagtagIdunitinterpolationvaluetype
Site 1MachineALine 1TemperatureSite 1.Line 1.MachineA.Temperature123slinearnumeric
Site 1MachineALine 1PressureSite 1.Line 1.MachineA.Pressure123megadonut/coffeelinearnumeric
Site 1MachineALine 1PowerSite 1.Line 1.MachineA.Power123ft/slinearnumeric
Site 1MachineALine 1State_sSite 1.Line 1.MachineA.State_s123steplinearstring
Site 1MachineALine 2TemperatureSite 1.Line 2.MachineA.Temperature123degClinearnumeric

Data (Fact Table)

TStagIdvaluetypeValue_SValue_D
2022-11-04T20:17:49Site 1.Line 1.MachineA.Temperature123numeric36.76654
2022-11-04T20:17:49Site 1.Line 1.MachineA.Pressure123numeric-214.179
2022-11-04T20:17:49Site 1.Line 1.MachineA.Power123numeric-33.6876
2022-11-04T20:17:49Site 1.Line 1.MachineA.State_s123stringstrval
2022-11-04T20:17:49Site 1.Line 2.MachineA.Temperature123numeric41.31567
2022-11-04T20:17:49Site 1.Line 2.MachineA.Pressure123numeric-87.6572
2022-11-04T20:17:49Site 1.Line 2.MachineA.Power123numeric14.04372
2022-11-04T20:17:49Site 1.Line 2.MachineA.State_s123stringstrval

The configuration for processing data from both tables is as follows:

{
  "Id": "DataAndMetadata",
  "Name": "Data",
  "TimeColumn": "TS",
  "TimeUnit": "",
  "LastGroupAsTagName": true,
  "DataColumns": [
    "ValueD"
  ],
  "GroupBy": [],
  "ColumnId": "tagId",
  "MetadataQuery": {
    "TableName": "Metadata",
    "Columns": [
      "siteId",
      "lineId",
      "machineId",
      "tag",
      "unit",
      "valuetype"
    ],
    "UniqueKeyColumnName": "tagId",
    "TagPropertiesMap": {
      "Datatype": "valuetype"
    }
  }
}

Configuration Details:

Config ItemDescription
MetadataQueryA section related to mapping metadata with actual data
TableNameName of the metadata table
ColumnsArray of columns to convert into asset and tags
UniqueKeyColumnNameA key how to join two tables
TagPropertiesMapA mapping how to fill tag structure. See available options below.

Mapping fields for TagPropertiesMap:

KeyDescription
DatatypeMap the data type of the tag to the column. If it's not specified, the type of the tag will be string.
UomUnit of measure for a tag; if not specified, the uom of a tag will be empty.
UpbndUpper limit
LobndLower boundary

Virtual Tags (Datasets)

There are two options for executing custom SQL queries:

  1. Using a Query tag from Tags and Writing SQL Query Manually: This involves using the "Query from Tags" feature to
    manually write your SQL query.

  2. Using Virtual Tags with Hardcoded SQL: This option involves utilizing virtual tags that contain pre-defined SQL
    queries.

For example, let's define a virtual tag in the configuration, named "DST2," with the following settings:

{
  "name": "dst2",
  "query": "SELECT * FROM tabular_data WHERE temperature > {{temp}} LIMIT 10",
  "parameters": {
    "temp": 10.0
  }
}
Config ItemDescription
NameThe name of the virtual tag.
QueryThe SQL query that will be executed. It can contain specific arguments.
ParametersA map of parameters that can be injected into the query. If this map is empty, the argument will be injected from the UI settings.

Write Data Tags

{
  "writeDataTags": [
    {
      "name": "addRecord",
      "query": "INSERT INTO tabular_data (tsCol, valueCol) VALUES ({{tsCol}}, {{value}})",
      "parameters": {
        "timeColumn": "tsCol",
        "tsCol": 0,
        "value": 0
      }
    }
  ]
}

Configuration example

Configure driver to connect to the database with tabular table tabular_data and asset-tree table asset_tree_data.
Additionally define virtual tag to receive temperature above 10 degrees.

{
  "tableSettings": [
    {
      "Id": "tabular_data",
      "Name": "tabular_data",
      "TimeColumn": "ts",
      "TimeUnit": "",
      "DataColumns": [
        "compressor_power",
        "optimizer",
        "relative_humidity",
        "temperature",
        "wet_bulb"
      ],
      "GroupBy": null,
      "LastGroupAsTagName": false
    },
    {
      "ID": "asset_tree_data",
      "Name": "asset_tree_data",
      "TimeColumn": "ts",
      "DataColumns": [
        "value"
      ],
      "GroupBy": [
        "siteid",
        "machineid",
        "lineid",
        "tag"
      ],
      "LastGroupAsTagName": true,
      "TagProperties": {
        "uom": "units"
      }
    }
  ],
  "datasetTags": [
    {
      "name": "GetTemperatureAbove10degrees",
      "query": "SELECT * FROM tabular_data WHERE temperature > {{temp}} LIMIT 10",
      "parameters": {
        "temp": 10.0
      }
    }
  ],
  "writeDataTags": [
    {
      "name": "addRecord",
      "query": "INSERT INTO tabular_data (tsCol, valueCol) VALUES ({{tsCol}}, {{value}})",
      "parameters": {
        "timeColumn": "tsCol",
        "tsCol": 0,
        "value": 0
      }
    }
  ]
}

Golang datetime string format

UnitGolang LayoutExamplesNote
Year0621, 81, 01
Year20062021, 1981, 0001
MonthJanuaryJanuary, February, December
MonthJanJan, Feb, Dec
Month11, 2, 12
Month0101, 02, 12
DayMondayMonday, Wednesday, Sunday
DayMonMon, Wed, Sun
Day21, 2, 11, 31
Day0201, 02, 11, 31zero padded day of the month
Day_2⎵1, ⎵2, 11, 31space padded day of the month
Day002001, 002, 011, 031, 145, 365zero padded day of the year
Day__2⎵⎵1, ⎵⎵2, ⎵11, ⎵31, 365, 366space padded day of the year
Part of dayPMAM, PM
Part of daypmam, pm
Hour 24h1500, 01, 12, 23
Hour 12h31, 2, 12
Hour 12h0301, 02, 12
Minute40, 4 ,10, 35
Minute0400, 04 ,10, 35
Second50, 5, 25
Second0500, 05, 25
10-1 to 10-9 s.0 .000000000.1, .199000000Trailing zeros included
10-1 to 10-9 s.9 .999999999.1, .199Trailing zeros omitted
Time zoneMSTUTC, MST, CET
Time zoneZ07Z, +08, -05 Z is for UTC
Time zoneZ0700Z, +0800, -0500 Z is for UTC
Time zoneZ070000Z, +080000, -050000 Z is for UTC
Time zoneZ07:00Z, +08:00, -05:00 Z is for UTC
Time zoneZ07:00:00Z, +08:00:00, -05:00:00 Z is for UTC
Time zone-07+00, +08, -05
Time zone-0700+0000, +0800, -0500
Time zone-070000+000000, +080000, -050000
Time zone-07:00+00:00, +08:00, -05:00
Time zone-07:00:00+00:00:00, +08:00:00, -05:00:00
IOTASoftware Licensed | Copyright © 2021-present