TimescaleDB

7 min

The TimescaleDB connector writes Manufacturing Connect Edge (MCe) DeviceHub messages into a TimescaleDB table. TimescaleDB is a time-series extension for PostgreSQL, so this connector uses the PostgreSQL wire protocol and driver.

The target table can be a TimescaleDB hypertable. The connector also exposes an Execute Command hook for ad-hoc SQL queries, and streams the result back one message per row on the response topic you supply.

Supported communication

Outbound (write data from MCe to the database): Yes

Inbound (read data from the database to MCe): No

Execute Command (ad-hoc SQL query): Yes

Compatibility

The following product and version are validated. Other products and versions may work.

Product	Version
TimescaleDB	2.x on PostgreSQL 12 and later

Connector configuration

Field	Type	Description	Required	Default
Name	String	Name of this connector instance.	Yes	-
Hostname	String	DNS name or IP address of the TimescaleDB host. The host must be reachable from the MCe system. Length is 3 to 255 characters.	Yes	-
Port	Integer	Port that the TimescaleDB host listens on. Range is 1 to 65535.	Yes	5432
SSL Mode	Dropdown	Controls certificate validation: disable skips TLS entirely, require encrypts but skips verification, verify-ca validates the CA certificate, and verify-full also validates the hostname.	No	disable
CA Certificate	String	CA certificate for TLS. Applies only when SSL Mode is not disable.	No	-
Certificate	String	Client certificate for TLS. Applies only when SSL Mode is not disable.	No	-
Private key	String (Secret)	Private key for the client certificate. Applies only when SSL Mode is not disable.	No	-
Username	String	Database user that the connector authenticates as. Required when SSL Mode is disable.	Conditional	-
Password	String (Secret)	Password for the database user.	No	-
Database	String	Name of the target database.	Yes	-
Table	String	Target table that the connector writes into. The table must exist in the selected database unless you enable Create table.	Yes	-
Enable JSONPath	Boolean	Read Mapping values that start with $ as JSONPath expressions, such as $.factory.line.machine.sensors.temperature. Dotted paths, quoted keys, and array indexes are supported. Other values are unchanged, and no match writes NULL.	No	Disabled
Show Mapping	Object	Key-value pairs mapping incoming message field names to TimescaleDB column names, in the form {"to": "from"}, where to is a column in the table and from is a field in the payload. Leave it empty to use the source field names as they are.	No	-
Create table	Boolean	Create the target table if it does not exist, using the default schema below. The database user needs permission to create a table.	No	Disabled
Commit timeout	Integer	Time in milliseconds before an open transaction is committed. Range is 10 to 65535.	No	1000
Max transaction size	Integer	Number of messages that triggers a commit, regardless of the commit timeout. Range is 1 to 65535.	No	100
Bulk insert count	Integer	Number of messages the connector buffers before it performs a bulk insert. Range is 0 to 65535.	No	0
Parallel Publish Count	Integer	Number of messages the connector publishes at the same time. Range is 1 to 4000.	No	1

Advanced parameters

These parameters tune the connection pool. Leave them empty unless you are tuning for a known load.

Field	Type	Description	Required	Default
Max pool connections	Integer	Maximum number of connections in the pool. Range is 0 to 1000. A value of 0 uses the driver default, which is the greater of four and the CPU count. The server's max_connections setting is the upper bound, and the PostgreSQL default for that is 100. Above approximately 500 connections, consider putting PgBouncer in front of the server.	No	-
Min pool connections	Integer	Minimum number of idle connections the pool keeps open. Range is 0 to 1000. A value of 0 uses the driver default of 0. When you set both, this value must not exceed Max pool connections.	No	-
Max connection lifetime (seconds)	Integer	Maximum lifetime of a pooled connection. The connector recycles a connection on its next release once it passes this age. Range is 0 to 86400, which is 24 hours. A value of 0 uses the driver default of 3600, which is one hour.	No	-
Max connection idle time (seconds)	Integer	How long a pooled connection can sit idle before the connector closes it. Range is 0 to 86400, which is 24 hours. A value of 0 uses the driver default of 1800, which is 30 minutes.	No	-

Additional attributes

Field	Type	Description	Required	Default
Throttling limit	Integer	Maximum outbound messages per second. A value of 0 disables throttling.	No	0
Persistent storage	Boolean	Buffer undelivered messages on disk so they survive a restart or a lost connection. When disabled, the connector holds messages in memory only and loses them if it stops.	No	Disabled
Persistent storage size limit	Integer	Maximum disk space in GB used for buffered messages. Minimum is 1.	Yes	1
Queue Mode	Dropdown	Order in which the connector sends buffered messages after a connection is restored. LIFO sends the newest data first. FIFO preserves the original order.	No	lifo
Enable Logging	Boolean	**	No	Disabled

Default table schema

When you enable Create table and the target table does not exist, the connector creates it with the following schema.

CREATE TABLE IF NOT EXISTS "TABLE_NAME"
(
    id BIGSERIAL NOT NULL,
    record_uuid UUID NOT NULL,
    arrived_at  TIMESTAMP WITH TIME ZONE NOT NULL,
    device_id   VARCHAR(64)    NULL,
    register_id VARCHAR(64)    NULL,
    tag_name    VARCHAR(64)    NULL,
    datatype    VARCHAR(32)    NULL,
    value       VARCHAR NULL,
    success     BOOLEAN        NULL,
    PRIMARY KEY (id)
);

To write into a table with different columns, create the table yourself and use Mapping to map payload fields to your column names.

Execute Command

The connector runs ad-hoc SQL queries through the Analytics Execute Command processor. It streams the result back one message per row, on the response topic supplied in the request.

Add a Connector

Manage Connectors