External SQL Datasources

Trendz can connect to external SQL databases and combine their data with ThingsBoard device and asset data in a single visualization. This reduces load on ThingsBoard, increases report build speed, and enables integration with data that lives outside ThingsBoard — such as ERP exports, maintenance logs, or billing records.

Trendz treats external SQL tables as read-only. It never writes, updates, or deletes rows in the external database.

Trendz bundles JDBC drivers for the databases above. Custom or third-party drivers are not supported.

Trendz can read a table from an external SQL database if it meets these structural requirements:

1. One column contains a UUID that matches a ThingsBoard Device or Asset ID.
2. One column contains a BIGINT timestamp in milliseconds.
3. All other columns can be of any type.

  External SQL table
  ┌──────────────┬─────────────────────┬──────────────────────┐
  │ building_id  │ ts                  │ heat_consumption     │
  │  (UUID)      │  (BIGINT, ms)       │  (BIGINT)            │
  ├──────────────┼─────────────────────┼──────────────────────┤
  │ <TB asset ID>│ 1700000000000       │ 4200                 │
  └──────────────┴─────────────────────┴──────────────────────┘
         │
         └──── must match a ThingsBoard Device/Asset ID

Before referencing external tables in views, register the database connection in Trendz settings. You can register any number of datasources — there is no limit.

1. Go to Settings, then the System tab.

2. Under the External Data Source section, click Add new data source.

3. Fill in the connection form:

   • URL — JDBC connection string
   • Database Type — PostgreSQL, MySQL, MSSQL, or Oracle
   • Login and Password

4. Click Save. Trendz tests the connection and shows a success or error message.

Go to Settings → System → External Data Source, then click Add new data source. Fill in the connection details and click Save.

• Go to Settings → System → External Data Source, then click See all data sources.

A table lists all registered datasources with their Data Source URL, Database type, and Actions.

Click See all data sources to open the list. All registered datasources with URL, database type, and action menu.

• In the datasource list, click the three-dot menu under Actions and select Delete.
• Confirm the deletion — Trendz shows which business entities will be affected.

Open the datasource list. Select Delete from the three-dot action menu. The datasource is removed.

• In the datasource list, click the three-dot menu under Actions and select Edit.
• Update the URL, database type, login, or password.
• Click Save and confirm — Trendz shows which entities will be affected and tests the connection.

Select Edit from the three-dot action menu. Edit the connection details and click Save. Confirm which entities are affected. Trendz tests the updated connection and reports success or an error.

To use external SQL tables in views, create a Business Entity that describes the table structure and links it to ThingsBoard entities. There is no limit on the number of external business entities you can create.

The example below maps the following PostgreSQL table:

CREATE TABLE IF NOT EXISTS building_consumption (
    id UUID PRIMARY KEY,
    building_id UUID NOT NULL,
    heat_consumption BIGINT NOT NULL,
    energy_consumption BIGINT NOT NULL,
    ts BIGINT NOT NULL
);

1. Go to the Business Entities section and click Add Business Entity.

2. Fill in the form:

   • Name — any display name (e.g. Building Consumption Data)
   • Entity Type — set to External
   • Table Name — the exact table name (e.g. building_consumption; case-sensitive)
   • Datasource — select the datasource registered in the previous section

3. Click Save.

Go to Business Entities and click Add Business Entity. Enter the name, table name, set entity type to External, and choose the datasource.

This field tells Trendz which column holds the ThingsBoard Device/Asset ID.

1. Go to the Fields tab and click Add field.

2. Fill in the form:

   • Name — display name (e.g. Building ID)
   • Field Type — String
   • Query Type — Attribute
   • Key — the exact column name containing the ThingsBoard entity ID (e.g. building_id; case-sensitive)

3. Enable Use Field as Business Entity Key.

Go to the Fields tab and click Add field. Set the field type, query type, key column name, and enable Use Field as Business Entity Key.

This field tells Trendz which column contains the row timestamp.

1. Click Add field.

2. Fill in the form:

   • Name — display name (e.g. Timestamp)
   • Field Type — Numeric
   • Query Type — Telemetry
   • Key — the exact column name containing the BIGINT millisecond timestamp (e.g. ts; case-sensitive)

3. Enable Use Field as Telemetry Timestamp.

Click Add field to add the timestamp field. Set the field type to Numeric, query type to Telemetry, and enable Use Field as Telemetry Timestamp.

Repeat this step for each data column you want to expose in views.

1. Click Add field.

2. Fill in the form:

   • Name — display name (e.g. Heat Consumption or Energy Consumption)
   • Field Type — the appropriate type for the column values
   • Query Type — Telemetry
   • Key — the exact column name (e.g. heat_consumption or energy_consumption; case-sensitive)

3. To remove a field, click the delete button in its top-right corner.

Click Add field. Configure the heat_consumption field. Configure the energy_consumption field.

Create a relation that connects the external business entity to a ThingsBoard asset or device profile. Trendz uses this link to filter external rows by ThingsBoard entity access permissions.

1. Go to the Relations tab and click Add relation.

2. Fill in the form:

   • Name — any label (e.g. External)
   • Direction — TO
   • Selected Business Entity — the ThingsBoard business entity whose IDs appear in the key column from Step 2 (e.g. EM building)
   • Enabled — check this box

3. Click Save.

Go to the Relations tab and click Add relation. Set the relation direction to TO, choose the linked ThingsBoard business entity, and enable it. Click Save.

Build a minimal table view to confirm the external business entity is wired up correctly.

1. Go to the home page, click Create View, and choose the Table view type.

2. Drag the ThingsBoard business entity name field (e.g. EM building) into the column section.

3. Drag any field from the external business entity (e.g. energy_consumption) into the column section.

4. Select a date range that contains data.

5. Click Build Report.

If the view builds successfully, the external business entity is set up correctly. If errors appear, review Steps 1–5 for misconfigured column names or missing key/timestamp fields.

Click Create View and choose Table. Drag the ThingsBoard business entity name field into the column section. Drag an external business entity field into the column section. Select a date range that contains data. Click Build Report. A successful result confirms the external entity is configured correctly.

Trendz enforces ThingsBoard access permissions when querying external SQL tables. External rows are filtered by the Device/Asset IDs linked through the relation defined in Step 5 — a user sees only rows whose ID corresponds to a ThingsBoard entity they have access to.

For example, if an external table stores maintenance records for factory machines, users see only the records for machines assigned to them in ThingsBoard.