Integrate Ignition with Tiger Cloud

Ignition is a SCADA system used to connect data, design industrial systems, and monitor automated processes in real time.

This page shows you how to connect Ignition to your Tiger Cloud service to stream industrial data into Tiger Cloud.

Prerequisites for this integration guide

To follow these steps, you'll need:

• A Tiger Cloud service.

  These steps use Tiger Cloud, but the same approach applies to a self-hosted TimescaleDB instance.

• Your connection details.

• An Ignition instance with admin access

Create a Tiger Cloud service connection in Ignition

1. Sign in to the Ignition Gateway

   Navigate your browser to the Ignition Gateway screen and sign in with an admin account. Open Connections > Database Connections and select Create Database Connection +.

2. Select the PostgreSQL driver

   Select PostgreSQL as the driver (included in every standard Ignition install).

3. Configure the database connection

   1. Give the database connection a name.
   2. Add the username, password, and connection url using your Tiger Cloud service connection details.
   3. Select Create Database Connection.
4. Verify the connection

   Verify that the database connection was created and status is valid. If any errors appear, consult the Ignition manual.

Set up your Tiger Cloud service as a historian

1. Create a historian

   On the Ignition Gateway, select Services > Historians and Create Historian +.

2. Select SQL Historian

   Select SQL Historian.

3. Name the historian and disable partitioning

   Add a name for the historian, and select the Tiger Cloud service database connected in section 1 as the Data Source. Disable Partitioning.

   If you don’t disable partitioning during the initial setup, and actively use the historian beyond the partition length, Ignition will create multiple history tables. This will make setting up Tiger Cloud optimizations more difficult and reduce the convenience of having all historical data in one table.

4. Verify the historian is running

   Verify the new historian is enabled and running.

5. Start data flowing into the historian

   Before proceeding to the next section, make sure there is data flowing into the historian. This can be done by creating any tag and setting History Enabled to True. That will guarantee that the historian table is created.

Ignition will automatically create the table in Tiger Cloud service, but will not set it up as a hypertable. The following instructions can be performed within Ignition's SQL Editor, the Tiger Console, or any other SQL editor.

Optimize for Ignition workloads

1. Convert the auto-created historian table to a hypertable

   SELECT create_hypertable(
      'sqlth_1_data','t_stamp',
      migrate_data => 'true',
      chunk_time_interval => 86400000
   );

   Chunk size must be defined in milliseconds. Recommended chunk size should aim for about 1-20 million records per chunk. That's about 1 day (86,400,000ms) for 10,000 tags/min or 1 hour (3,600,000ms) for 300,000 tags/min.

2. Tell TimescaleDB what the current time is

   Ignition stores t_stamp as a Unix epoch timestamp in milliseconds, not as a PostgreSQL timestamp. Automated jobs such as columnstore and retention policies work out the age of each chunk relative to the current time, so you must tell TimescaleDB how to read "now" for this integer column with set_integer_now_func. The function must return milliseconds to match t_stamp:

   CREATE OR REPLACE FUNCTION unix_now_ms() RETURNS BIGINT
     LANGUAGE SQL STABLE AS $$ SELECT (extract(epoch from now()) * 1000)::BIGINT $$;
   
   SELECT set_integer_now_func('sqlth_1_data', 'unix_now_ms');

3. Optional: enable the columnstore and add a policy

   Because t_stamp is an integer column, the policy after argument is a number of milliseconds rather than an INTERVAL. For example, 604800000 is 7 days:

   ALTER TABLE sqlth_1_data SET (
   timescaledb.enable_columnstore = true,
   timescaledb.segmentby = 'tagid',
   timescaledb.orderby = 't_stamp DESC'
   );
   SELECT add_columnstore_policy('sqlth_1_data', after => 604800000);

4. Optional: add retention policy

   As with the columnstore policy, drop_after is a number of milliseconds. For example, 31536000000 is 365 days:

   SELECT add_retention_policy('sqlth_1_data', drop_after => 31536000000);

You have successfully integrated Ignition with Tiger Cloud.