SQL Module

In the context of Azure IoT Edge, the SQL module (also known as Azure Stream Analytics module) allows you to run Stream Analytics jobs on your IoT Edge devices. Azure Stream Analytics is a real-time analytics service designed to help you analyze and visualise streaming data in real time.

This SQL module is essentially a container that you deploy to your IoT Edge devices. Inside this container, you can write SQL-like queries to process data coming from your devices. For example, you could filter out certain readings, calculate averages, detect anomalies, and so forth.

This capability is powerful as it allows a portion of your data processing workload to be handled at the edge, closer to the data sources, which can dramatically reduce the volume of data that needs to be transmitted to the cloud, reduce latency for decision making, and can work in environments with limited or intermittent connectivity.

The SQL module communicates with other modules on your IoT Edge device through the IoT Edge Hub. It takes as input data from other modules, processes it according to the Stream Analytics job defined, and then outputs the results either to other local modules or to the cloud for further processing or storage.

SQL manifest

Creating a manifest for an SQL Module and configuring a template for it in Azure IoT Central involves defining the Modbus module in the manifest, setting up its Docker image and settings, and routing its messages. Then, in IoT Central, a template reflecting the Modbus module's capabilities is created and linked with the IoT Edge device. Finally, the manifest is applied to start deployment.

Step 1: Create an SQL Module Manifest

The manifest is a JSON document that describes which modules to deploy to your IoT Edge device and how to configure them. Here is a sample manifest that deploys an SQL module:

{
  "modulesContent": {
    "$edgeAgent": {
      "properties.desired": {
        "modules": {
          "factsSqlModule": {
            "settings": {
              "image": "facts.example.io/iotedge/sql-publisher:latest-amd64",
              "createOptions": "{\"Cmd\":[\"--verbose\"],\"HostConfig\":{\"LogConfig\":{\"Type\":\"json-file\",\"Config\":{\"max-size\":\"10m\",\"max-file\":\"25\"}}}}"
            },
            "type": "docker",
            "status": "running",
            "restartPolicy": "always",
            "version": "1.0"
          },
          // Additional modules go here
        },
        // Additional configuration goes here
      }
    },
    // Additional module configurations go here
  }
}

• "image": "facts.example.io/iotedge/sql-publisher:latest-amd64": This specifies the Docker image to use for the module. In this case, it's the latest AMD64 version of the SQL Publisher module from the facts.example.io registry.

• "createOptions": This field contains a JSON string that specifies additional options for creating the Docker container. It includes several sub-fields:

  • "Cmd":["--verbose"]: This is the command that will be run when the Docker container starts. The --verbose option is used, which typically means that the module will output more detailed logs.

  • "HostConfig": This field contains additional configuration for the Docker host:

    • "LogConfig": This field contains configuration for the Docker logs:
      • "Config":{"max-size":"10m","max-file":"25"}: This sets the maximum size of the log file to 10 megabytes and the maximum number of log files to 25.
      • "Type":"json-file": This sets the format of the log files to JSON.

• "type": "docker": This specifies that the module is a Docker module.

• "status": "running": This sets the desired status of the module to "running", meaning that it should be started as soon as it's deployed.

• "restartPolicy": "always": This sets the restart policy for the module to "always", meaning that it should be restarted whenever it stops, regardless of the reason for stopping.

Step 2: Upload the Manifest to Azure IoT Central

Now you can upload this manifest to Azure IoT Central:

1. In your Azure IoT Central application, go to "Device Templates", and select the appropriate template for your IoT Edge device.
2. Go to "Edge Manifests".
3. Click "+ New" to add a new manifest.
4. Enter a name for the manifest, and paste the manifest JSON in the "Manifest" field.
5. Click "Create".

Step 3: Deploy the SQL Module

Finally, you can deploy your SQL module to your IoT Edge device:

1. In your Azure IoT Central application, go to Devices, and select your IoT Edge device.
2. Go to Configuration.
3. In the Configuration Manifests section, select the manifest you created in the previous step.
4. Click Save.

info

An Azure IoT Edge device can host multiple modules, each serving a distinct function or process. It's important to understand that we don't deploy these modules individually; rather, we deploy a complete manifest. This manifest is a comprehensive description of all the modules to be installed, their configurations, and the inter-module communication routes. When the manifest is deployed, all of the outlined modules are deployed and configured together on the IoT Edge device.

Your IoT Edge device will now start deploying the SQL module according to the manifest. This might take a few minutes. After the deployment is complete, the SQL module will start running on your IoT Edge device, and you can start interacting with it through Azure IoT Central.

Step 4: Create SQL module configuration

1. Create a JSON file that will contain all the necessary configuration information required by the SQL module. For example, you can create a file named configuration.json.

2. Populate the configuration file with the basic settings for SQL change tracking queries in the SQL module.

{
    "ChangeTrackingQueries": {
        "<identifier>": {
            "Sql": {
                "ConnectionString": "<Your SQL ConnectionString>",
                "Schema": "<Your SQL Schema, default: dbo>",
                "Table": "<Your SQL Table>",
                "IsolationLevel": "<Optional Isolation level, default: ReadCommitted>",
                "WhereClause": "<Optional where clause for specific changes>",
                "InitialWhereClause": "<Optional where clause for specific changes if no version is present>",
                "ColumnSelection": "<Optional list of specific columns, default: all columns>",
                "ColumnTracking": "<Optional columns for column tracking>",
                "PollingInterval": "<Interval in milliseconds when the SQL module should poll and execute the query for new changes>"
            },
            "Message": {
                "GroupRecords": {
                    "Enable": "<Optional, enable if records should be grouped into 1 message>",
                    "BatchSize": "<Optional, define a batch size if group records are enabled>"
                },
                "AdditionalProperties": {
                    "<Your Custom Property Name>": "<Your Custom Property Value>"
                }
            }
        }
    }
}

You need to replace the placeholders (<...>) with your actual values. If a property is marked as optional and you do not need it, you can simply remove it from your configuration. Make sure that the <identifier> used is unique for each configuration you create. This identifier will also be used as a key to store the last_synchronization_version.

note

Please note that an individual object {} should be used for the final configuration file, not an array [].

Field	Required	Description
Identifier	Yes	Unique identifier of this configuration, which is also used as a key to store the last_synchronisation_version
Sql.ConnectionString	Yes	SQL ConnectionString
Sql.Schema	No	SQL Schema. Default: dbo
Sql.Table	Yes	SQL Table
Sql.IsolationLevel	No	Optionally define the Isolation level of the query. Default: ReadCommitted
Sql.WhereClause	No	Optionally define a where clause to filter specific changes
Sql.InitialWhereClause	No	Optionally define a where clause to filter specific changes if no version is present. If not defined use Sql.WhereClause
Sql.ColumnSelection	No	Default all columns are returned (SELECT * FROM …), optionally a list of specific columns is defined.
Sql.ColumnTracking	No	Optionally define columns for column tracking. Column tracking enables applications to obtain the data for only the columns that have changed instead of the whole row
Sql.PollingInterval	Yes	Interval defined in milliseconds that specifies when the SQL module should poll and execute the query for new changes. When query execution is triggered by the interval and the SQL module is still running the previous query, then the current execution should be skipped.
Message.GroupRecords.Enable	No	If enabled, records in the result set of the query execution are grouped into 1 message
Message.GroupRecords.BatchSize	No	If group records are enabled, then the records in the result set of the query execution are grouped into separate messages with a maximum batch. Eg. when a query execution results in 100 records and the batchSize is set to 25, then this will result in 4 messages towards the iothub.
Message.AdditionalProperties	No	These static properties are added to the message body

Step 5: Add the configuration to Facts

1. Log in to Inimco.facts. You will be taken to the Manage Devices screen.
2. Navigate to Devices.
3. Select a device that you need to add the configuration to.
4. Navigate to the Modules tab.
5. Click the ellipsis (...) next to the module that you need to add the configuration to.
6. Select Edit configuration.
7. Add the configuration file to the Configuration OpcPublisher.

note

Please note that an individual object {} should be used for the final configuration file, not an array [].

8. Click Save.
9. Click the ellipsis (...) next to the module you added the configuration to.
10. Select Publish configuration.

Please note that the exact steps and the specifics of the manifest might differ based on your specific use case and the specifics of your SQL module.