Databricks - Schema Configuration

This page explains how to define the schema JSON for the Generic SQL Driver. The schema tells IOTA Vue how to map database tables and views into IOTA specific data types.

The schema defines three ways to access and work with SQL data:

• tableSettings – Maps SQL tables to time-series tags. Supports timestamped data and hierarchical asset grouping. → Go to tableSettings
• datasetTags – Parameterized queries that return tabular results. → Go to datasetTags
• writeDataTags – Executes SQL write operations (INSERT, UPDATE, DELETE) using tag values. → Go to writeDataTags

Accessing Schema Configuration

You can define or edit the schema in the Database schema tab of a Generic SQL connection.

To access it:

1. Click the hamburger icon (☰) in the top-right corner, then select Data Sources from the dropdown menu.
2. Select Region and Site for the specific driver.
3. Open the Generic SQL tab and click on the gear icon.
4. Select the Database schema tab.
5. Paste or edit the schema JSON, then click Apply.

Database Schema Tab

⚠️ Warning: Renaming Id, Name, or GroupBy columns can break dashboards by changing tag FQNs. Rename with caution.

Schema Structure

The schema is a JSON object with these main sections:

{
  "tableSettings": [ { /* table configuration objects */ } ],
  "datasetTags":   [ { /* read-only virtual queries */ } ],
  "writeDataTags": [ { /* writeback queries */ } ]
}

Section	Purpose	Link
tableSettings	Define time-series tags and asset hierarchy from tables	→ Go to Table Settings
datasetTags	Define reusable read-only queries (virtual tags)	→ Go to Dataset Tags
writeDataTags	Define SQL actions (insert, update, delete)	→ Go to Write Data Tags

Full Schema Example

{
  "tableSettings": [
    {
      "Id": "tabular_data",
      "Name": "tabular_data",
      "TimeColumn": "ts",
      "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,
      "TagPropertiesMap": { "Uom": "units" }
    }
  ],
  "datasetTags": [
    {
      "name": "GetTemperatureAbove10degrees",
      "query": "SELECT * FROM tabular_data WHERE temperature > {{temp}} LIMIT 10",
      "parameters": { "temp": 10.0 }
    }
  ],
  "writeDataTags": [
    {
      "name": "InsertNewRecord",
      "query": "INSERT INTO tabular_data (ts, temperature) VALUES ({{time}}, {{temp}})",
      "parameters": {
        "timeColumn": "ts",
        "timeFormat": "2006-01-02T15:04:05Z07:00",
        "time": 0,
        "temp": 20.0
      }
    }
  ]
}

Field References tableSettings[]

Field	Type	Required	Description
Id	string	Yes	Table settings identifier (internal use only).
Name	string	Yes	Name of the SQL table or view.
SchemaName	string	No	Name of the schema that contains the table (if applicable).
TimeColumn	string	Yes	Name of the column storing timestamps or datetime strings.
TimeUnit	string	No	Time unit for numeric timestamps (s, ms, etc.).
TimeStringFormat	string	No	Format for string timestamps. Required if TimeColumn stores strings. Uses Golang datetime format.
DataColumns	string[]	Yes	List of column names that hold tag values.
GroupBy	string[]	No	Columns defining the asset hierarchy (e.g., site → line → machine).
LastGroupAsTagName	boolean	Yes	If true, the last GroupBy element is the tag name. If false, tags come from DataColumns.
TagPropertiesMap	object	No	Maps tag properties like "Uom" or "Description" to their respective column names
IntervalForSnapshotsInMin	number	No	Time window (in minutes) used to fetch only the most recent rows for snapshot queries. Helps avoid scanning the full table, improving performance for large datasets.
ColumnId	string	No	Join key used when joining with an external MetadataTable.
MetadataTable	object	No	Definition of an external table containing tag metadata.

---

Field References datasetTags[]

Field	Type	Required	Description
name	string	Yes	Unique name of the virtual tag (displayed in Tag Search).
query	string	Yes	SQL query string. May include parameter placeholders using {{param}}.
parameters	object	No	Map of default parameter values (key = parameter name, value = default value).

---

Field References writeDataTags[]

Field	Type	Required	Description
name	string	Yes	Unique name of the write tag (displayed in Tag Search and used in scripts).
query	string	Yes	SQL statement (INSERT, UPDATE, DELETE). May include parameter placeholders using {{param}}.
parameters	object	Yes	Map of parameters used in the query. All placeholders in the query (e.g., {{temp}}, {{ts}}) must be defined here. If the query includes a timestamp, include timeColumn, and optionally timeFormat if it's is a string.

1. Table Settings — Time-Series Tags

This section defines how the SQL driver maps timestamped data from tables or views into time-series tags and asset hierarchies in IOTA Vue.

Each tableSettings entry tells the driver how to:

• Locate timestamps and value columns
• Generate tags from one or more columns
• Optionally build asset hierarchy from grouping fields
• Optionally join metadata (units, types)

Quick Start

Here's a minimal schema with two tag columns:

{
  "tableSettings": [
    {
      "Id": "tabular_data",
      "Name": "tabular_data",
      "TimeColumn": "ts",
      "DataColumns": ["temperature", "pressure"],
      "GroupBy": null,
      "LastGroupAsTagName": false
    }
  ]
}

Reference Fields – tableSettings[]

Field	Type	Required	Description
Id	string	Yes	Internal identifier for this table definition (not shown to users).
Name	string	Yes	Name of the SQL table or view.
TimeColumn	string	Yes	Name of the column containing timestamps. Used for ordering and snapshot queries.
DataColumns	string[]	Yes	Columns containing the tag values. Each column becomes a tag (unless LastGroupAsTagName is true).
GroupBy	string[] or null	No	Defines asset hierarchy. Use an array of column names or null for flat tags.
LastGroupAsTagName	boolean	Yes	true: last GroupBy column is used as tag name. false: tag names come from DataColumns.

This maps two flat tags (temperature, pressure) from the tabular_data table using column ts as the timestamp.

Info

If your timestamp column is stored as a string, set TimeStringFormat to the database’s date-time layout so the driver can parse it correctly.
See the Time Format Reference for more details.

Common Table Patterns

Wide Table Pattern (Tabular)

Each row has multiple value columns (flat structure):

ts	temperature	pressure
2025-01-01 10:00	78.4	2.1

{
  "Id": "tabular_data",
  "Name": "tabular_data",
  "TimeColumn": "ts",
  "DataColumns": ["temperature", "pressure"],
  "GroupBy": null,
  "LastGroupAsTagName": false
}

Tall Table Pattern (Asset Tree)

Each row represents a single value with a tag name in one of the columns:

ts	site	line	machine	tag	value
2025-01-01 10:00	A	L1	Mixer01	Temperature	78.4

{
  "Name": "asset_tree_data",
  "TimeColumn": "ts",
  "DataColumns": ["value"],
  "GroupBy": ["site", "line", "machine", "tag"],
  "LastGroupAsTagName": true
}

Hybrid Table Pattern (Multi-Tag Asset)

Each row holds multiple values for a single asset at a point in time:

ts	site	line	machine	Temperature	Pressure
2025-01-01 10:00	A	L1	Mixer01	78.4	2.1
2025-01-01 10:00	A	L1	Pump02	65.2	3.5
2025-01-01 10:00	B	L2	Mixer01	72.1	1.8

{
  "Name": "hybrid_tree",
  "TimeColumn": "ts",
  "DataColumns": ["Temperature", "Pressure"],
  "GroupBy": ["site", "line", "machine"],
  "LastGroupAsTagName": false
}

Resulting IOTA Vue structure:

Site A
└── Line L1
    ├── Mixer01
    │   ├── Temperature
    │   └── Pressure
    └── Pump02
        ├── Temperature
        └── Pressure
Site B
└── Line L2
    └── Mixer01
        ├── Temperature
        └── Pressure

Advanced Pattern: Metadata-Fact Tables

When your main table contains only tag IDs, and tag metadata (like name, unit, type) is stored separately, you can define a metadata join using MetadataTable.

Fact Table:

ts	tagId	value
2025-01-01 10:00	Site1.Line1.MachineA.Temp	78.4

Metadata Table:

tagId	siteid	lineid	machineid	tag	unit	valuetype
Site1.Line1.MachineA.Temp	Site1	Line1	MachineA	Temperature	degC	numeric

{
  "ColumnId": "tagId",
  "MetadataTable": {
    "Name": "meta_tags",
    "UniqueKeyColumnName": "tagId",
    "ValueTypeColumnName": "valuetype",
    "DataColumnsTypes": {
      "value": "numeric"
    }
  }
}

Reference Fields:

Field	Description
ColumnId	Column name in the main data table that contains the unique tag identifier (used for joining with metadata table)
Name	Name of the metadata table
UniqueKeyColumnName	Join key column in the metadata table (must match values in the main table's ColumnId column)
ValueTypeColumnName	Column in metadata table that defines the data type (numeric, string, etc.)
DataColumnsTypes	Mapping of data table column names to their expected data types (used to determine which column to read based on the ValueTypeColumnName)
value	Key in the DataColumnsTypes mapping that corresponds to the actual data column name in the main data table

2. Virtual Tags – Read-Only SQL Queries

Virtual Tags let you define reusable, parameterized SQL queries that return tabular datasets. They are ideal for filtering data, paginating results, or powering dashboards and scripts in IOTA Vue.

Each virtual tag includes:

• A name, shown in the Tag Search panel and used in UI bindings
• A SQL query with parameter placeholders (e.g., {{temp}})
• Optional default parameters, which can be overridden at runtime

Field References datasetTags[]

Field	Type	Required	Description
name	string	Yes	Unique name of the virtual tag (displayed in Tag Search).
query	string	Yes	SQL query string. May include parameter placeholders using {{param}}.
parameters	object	No	Map of default parameter values (key = parameter name, value = default value).

Quick Examples

Filtering Based on Threshold

{
  "name": "temperature_above",
  "query": "SELECT * FROM tabular_data WHERE temperature > {{temp}} LIMIT 10",
  "parameters": { "temp": 10.0 }
}

Pagination

{
  "name": "paginated_data",
  "query": "SELECT column1, column2 FROM tableName LIMIT {{limit}} OFFSET {{offset}}",
  "parameters": { "limit": 100, "offset": 0 }
}

Parameter Behavior

Parameters act as placeholders inside your query. You can use them to inject values from the UI or from a script.

• Default Value Provided: The default value is used unless explicitly overridden at runtime.
• No Default Value: The virtual tag relies on external input (e.g., from a user interface or API) to supply the parameter value during execution.

Example UI Integration

You can expose virtual tag parameters in the UI by:

1. Mapping the tag to a component like a table
2. Adding inputs for each parameter (e.g., threshold, limit)
3. Writing a script on a component like a button that updates the tag with the current values
4. Triggering a refresh to update the result set

Common Components:

UI Element	Example ID	Purpose
Table	tblData	Displays the tag's dataset
Input field	inLimit	Captures parameter value for limit
Input field	inOffset	Captures parameter value for offset
Button	btnApply	Triggers refresh with new parameter values

Script Example (on Button onClick):

const tblData = getComponent('tblData');
const inLimit = getComponent('inLimit');
const inOffset = getComponent('inOffset');

tblData.SetQueryParameters({
  limit: inLimit.settings.value,
  offset: inOffset.settings.value
});

tblData.snapshot(); // Refresh the table

Best Practices:

• Always include sensible default values in the schema
• Keep tag names concise and meaningful
• Limit the number of returned rows to improve UI performance
• Test queries without parameters to ensure fallbacks behave as expected

3. Write Data Tags — Trigger SQL Writes from UI

Write data tags allow you to perform SQL write actions from the UI. These are defined in writeDataTags section or your schema.

Each write tag includes:

• A name, shown in the Tag Search panel and used in UI bindings
• A SQL query with parameter placeholders (e.g. {{temp}})
• A map of parameters, which provide values for all placeholders at runtime

Field References writeDataTags[]

Field	Type	Required	Description
name	string	Yes	Unique name of the write tag (displayed in Tag Search and used in scripts).
query	string	Yes	SQL statement (INSERT, UPDATE, DELETE). May include parameter placeholders using {{param}}.
parameters	object	Yes	Map of parameters used in the query. All placeholders in the query (e.g., {{temp}}, {{ts}}) must be defined here. If the query includes a timestamp, include timeColumn, and optionally timeFormat if it's is a string.

Use Cases:

• Logging events or numeric input from users
• Sending sensor readings or calculated values
• Triggering updates via button click

Setup Summary

1. Define your write tag in the schema.
2. Map the tag to a UI component using drag-and-drop (e.g., a button).
3. Attach a script that calls "writedata" with the required parameters.
4. Trigger the script from the UI — for example, when the user clicks the button.

Example – Insert Record

{
  "name": "addRecord",
  "query": "INSERT INTO tabular_data (tsCol, valueCol) VALUES ({{ts}}, {{temp}})",
  "parameters": {
    "timeColumn": "tsCol",
    "ts": 0,
    "temp": 10.0
  }
}

This tag inserts a row into the tabular_data table with a timestamp and a numeric value. You can trigger it from a script in the UI.

Time Format Configuration

By default, timestamps are written in ISO 8601 format: 2006-01-02T15:04:05Z07:00.

If your database needs a different layout, add a timeFormat field in the tag’s parameters.
See how to build a layout string here.

Oracle example (expects YYYY.MM.DD HH24:MI:SS):

{
  "name": "addRecord",
  "query": "INSERT INTO tabular_data (tsCol, valueCol) VALUES ({{ts}}, {{temp}})",
  "parameters": {
    "timeColumn": "ts",
    "timeFormat": "2006.01.02 15:04:05",
    "ts": 0,
    "temp": 10.0
  }
}

UI Integration

You can call a writeDataTag from the UI using a script attached to a component (e.g. a button).

Example Script to trigger (onClick of Button):

let value = Math.random() * 100;

let req = {
  ts: new Date().valueOf(),  // must be in milliseconds
  value: value
};

await callDriver({}, "writedata", req, [this.channels[0].fqn]);

⚠️ Important: The timestamp value in the request must be in milliseconds. Use new Date().valueOf().

What this script does

This script sends a single test value to a SQL writeDataTag when triggered in the UI by a button click.

Step-by-step

• Generate value: A random number is generated (0–100) for demonstration purposes.

• Build the request
  The script creates an object req with:

  • ts: the current timestamp in milliseconds (used to fill {{ts}})
  • value: the generated number (used to fill {{temp}})

• Send the write request
  The callDriver() function sends this request to the driver with:

  • "writedata" as the action type
  • the req object as input parameters
  • [this.channels[0].fqn] as the list of tags to write to

this.channels[0].fqn refers to the fully qualified name (FQN) of the first tag mapped to this component.

Performance Tips

Goal	Method	Example	Impact
Faster snapshot	IntervalForSnapshotsInMin	1440 for 24h	Avoids scanning full table history
Lower load	Enable "Cache Data" in UI	Cache in-memory	Serves hot data from memory
Faster search	Keep GroupBy short	Avoid unnecessary levels	Fewer nodes = faster Tag/Asset search

Time Format Reference

Use these Golang layout patterns to specify custom time formats.

Common Patterns:

Date Time Example	Golang Layout Pattern
2024-07-27 14:30:45	2006-01-02 15:04:05
2024-07-27T14:30:45Z	2006-01-02T15:04:05Z
07/27/2024 2:30 PM	01/02/2006 3:04 PM
27.07.2024 14:30	02.01.2006 15:04

Time Format Reference Details

Unit	Golang Layout	Examples	Note
Year	06	21, 81, 01
Year	2006	2021, 1981, 0001
Month	January	January, February, December
Month	Jan	Jan, Feb, Dec
Month	1	1, 2, 12
Month	01	01, 02, 12
Day	Monday	Monday, Wednesday, Sunday
Day	Mon	Mon, Wed, Sun
Day	2	1, 2, 11, 31
Day	02	01, 02, 11, 31	zero padded day of the month
Day	_2	⎵1, ⎵2, 11, 31	space padded day of the month
Day	002	001, 002, 011, 031, 145, 365	zero padded day of the year
Day	__2	⎵⎵1, ⎵⎵2, ⎵11, ⎵31, 365, 366	space padded day of the year
Part of day	PM	AM, PM
Part of day	pm	am, pm
Hour 24h	15	00, 01, 12, 23
Hour 12h	3	1, 2, 12
Hour 12h	03	01, 02, 12
Minute	4	0, 4 ,10, 35
Minute	04	00, 04 ,10, 35
Second	5	0, 5, 25
Second	05	00, 05, 25
10-1 to 10-9 s	.0 .000000000	.1, .199000000	Trailing zeros included
10-1 to 10-9 s	.9 .999999999	.1, .199	Trailing zeros omitted
Time zone	MST	UTC, MST, CET
Time zone	Z07	Z, +08, -05 Z is for UTC
Time zone	Z0700	Z, +0800, -0500 Z is for UTC
Time zone	Z070000	Z, +080000, -050000 Z is for UTC
Time zone	Z07:00	Z, +08:00, -05:00 Z is for UTC
Time zone	Z07:00:00	Z, +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

Next Steps

1. Set up caching to optimize performance (optional)
2. Validate – preview data in Tag and Asset Search
3. Build dashboards – drag channels onto charts, tables, or other components

Need help? Contact support@iotasoft.com or your Customer Success Advisor (CSA).