SQL
      Back to home
      1. Alleantia Knowledge Base
      2. Stream Data - IIoT Apps
      3. SQL

      How to connect Alleantia IoT Edge to SQL service?

      Learn how you can use machine data with an SQL DBs through Alleantia Software.

      It is possible to configure a SQL database as a destination of all data, events and alarms, taken from Alleantia IoT Edge. In particular:

      • MySQL,
      • Microsoft SQL Server,
      • PostgreSql,
      • Oracle Sb

      In the Configuration / Cloud Services area, select 'SQL'. Then, select the type of database to connect to IoT SCADA application, from the dropdown menu in the configuration window (Database type)

      "database-sqlserver"

       

      Note: Alleantia is compatible with UTF8 encoding. For custom encoding contact the support

      "sql-connect"

      Then, on your Database, create a compatible structure with IoT SCADA application. To do this, click “Download script” to download the script of the creation of a new database on your SQL platform, that contains the data from the Alleantia application.

      This is the example of a script compatible with SQL Server.

      The Script creates different tables:

      • LOG_DATA (used for sending data read by the machines connected to Alleantia ISC Software)
      • VAR_EVENTS & CONFIG_VAR_EVENTS (contain the events customized within ISC)
      • ALARMS (contains the alarms customized within ISC )
      • CONFIG_DEVICES (contains the devices configured within ISC)
      • CONFIG_VERSIONS (contains informations about the ISC Software version).

      CREATE TABLE LOG_DATA_suffix (SN VARCHAR(255), LOG_TIMESTAMP DATETIME2 NOT NULL, LOG_TIMESTAMP_UTC DATETIME2 NOT NULL, DEVICE_ID INT NOT NULL, VARIABLE_ID INT NOT NULL,  NUMERIC_VALUE FLOAT, BOOLEAN_VALUE BIT, STRING_VALUE VARCHAR(255), QUALITY BIT NOT NULL DEFAULT 0, ALARM_PK BIGINT, EVENT_PK BIGINT, PRIMARY KEY(SN, LOG_TIMESTAMP_UTC, DEVICE_ID, VARIABLE_ID));

                                                                         

                                                                       LOG_DATA

      "sql-log-data"

       

      CREATE TABLE  VAR_EVENTS_suffix (SN VARCHAR(255), PK BIGINT, TIMESTAMP_UTC DATETIME NOT NULL, DELAYED_TIMESTAMP_UTC DATETIME NOT NULL, ID SMALLINT NOT NULL, STATE BIT NOT NULL, NUMERIC_VALUE FLOAT, BOOLEAN_VALUE BIT, STRING_VALUE VARCHAR(255), QUALITY BIT NOT NULL DEFAULT 0, PRIMARY KEY(SN, PK));

       

                                                                         VAR_EVENTS

      var_events

       

      CREATE TABLE CONFIG_VAR_EVENTS_suffix (SN VARCHAR(255), CONFIG_VERSION INT NOT NULL, ID INT NOT NULL, DESCRIPTION VARCHAR(255), EVT_TYPE VARCHAR(255), EVT_CONDITION VARCHAR(255), SNAPSHOT_IDS VARCHAR(4000), PRIMARY KEY(SN, CONFIG_VERSION, ID));

                                                           TABLE CONFIG_VAR_EVENTS

      table_config

       

      CREATE TABLE ALARMS_suffix (SN VARCHAR(255), ALARM_PK BIGINT, TIMESTAMP DATETIME NOT NULL, EVENT_ID BIGINT, STATE BIT, MSG VARCHAR(511), DETAIL VARCHAR(8000), PRIMARY KEY(SN, ALARM_PK)); 

                                                                  TABLE ALARMS

      table_alarms

       

      CREATE TABLE CONFIG_DEVICES_suffix (SN VARCHAR(255), CONFIG_VERSION INT NOT NULL, DEVICE_ID INT NOT NULL, DEVICE_SUPPLIER VARCHAR(255), DEVICE_MODEL VARCHAR(255), DEVICE_VERSION VARCHAR(255), PRIMARY KEY(SN, CONFIG_VERSION, DEVICE_ID));

                                                                TABLE CONFIG_DEVICES

      table_config_devices

       

      CREATE TABLE CONFIG_VERSIONS_suffix (SN VARCHAR(255), CONFIG_VERSION INT NOT NULL, START_DATE_UTC DATETIME NOT NULL, PRIMARY KEY(SN, CONFIG_VERSION));

                                                                    TABLE CONFIG_VERSIONS

      table_config_versions

       

      After having entered the connection parameters to the database (IP address, Port, Database name, Tables suffix, Username, Password), check the connection by clicking “Connection test”. the message “Connection Success” will be shown.

      Click “Copy”, the configurations of variables and devices, configured in IoT SCADA application will be forwarded to SQL database. Please NOTE - If you add or remove devices in Alleantia ISC Software, the structure of the tables remains the same so you won't need to re-create the script. Just click on Copy again to syncronize the changes with the DB.

      The time interval for data forwarding can be set independently of the logging time of the variables. Set it in h, m, and s fields.

      Select the desired variables to send by clicking on the "Telemetry variables" button:

      service sql

      You also do this with Custom alarms and events

       

      Check “Send only changed variables in the time interval” box to forward only the variables, which did not change their value during the fixed forwarding time, to SQL platform.

      In addition, a line will appear, indicate there the number of periods (1 period=forwarding time), after this number of periods the variables will be forwarded to the database, even if their value remained the same as the last entry.

      enable sql service

      After that, the service is completely configured, so check the “Enable SQL service” box to enable the service.

      Click Save to apply the changes.

      IoT SCADA software also sends registered data to more SQL platforms at the same time. The maximum number of SQL platforms to forward data depends on the purchased license. 

      If you need support don't hesistate to contact us