An Azure service that is used to deploy cloud workloads to run on internet of things (IoT) edge devices via standard containers.
Have a problem when connect external datasource to Azure SQL Edge
Егор Карташов
6
Reputation points
Hi everyone! See example (tutorial-set-up-iot-edge-modules) I have several issues when I try to establish connection between modules in my Azure IoT Edge device:
- My config of IoT Edge is: OPC Publisher and SQL Edge with only one route there - "FROM /messages/modules/OPCPublisher/* INTO BrokeredEndpoint("/modules/AzureSQLEdge/inputs/opcDataInput")"
- In SQL Edge I try to create connection trougout internal Stream Analytics with this query:
Use OpcDB Go Declare @SQL_SA_Password varchar(200) = 'HERE MY PASSWORD' declare @query varchar(max) /* Create Objects Required for Streaming */ /* CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStr0ng3stP@ssw0rd'; */ If NOT Exists (select name from sys.external_file_formats where name = 'JSONFormat') Begin CREATE EXTERNAL FILE FORMAT [JSONFormat] WITH ( FORMAT_TYPE = JSON ) End If NOT Exists (select name from sys.external_data_sources where name = 'EdgeHub') Begin Create EXTERNAL DATA SOURCE [EdgeHub] With ( LOCATION = N'edgehub://' ) End If NOT Exists (select name from sys.external_streams where name = 'opcInput') Begin CREATE EXTERNAL STREAM opcInput WITH ( DATA_SOURCE = EdgeHub, FILE_FORMAT = JSONFormat, LOCATION = N'opcDataInput' ) End If NOT Exists (select name from sys.database_scoped_credentials where name = 'SQLCredential') Begin set @query = 'CREATE DATABASE SCOPED CREDENTIAL SQLCredential WITH IDENTITY = ''sa'', SECRET = ''' + @SQL_SA_Password + '''' Execute(@query) End If NOT Exists (select name from sys.external_data_sources where name = 'LocalSQLOutput') Begin CREATE EXTERNAL DATA SOURCE LocalSQLOutput WITH ( LOCATION = 'sqlserver://tcp:.,1433',CREDENTIAL = SQLCredential) End If NOT Exists (select name from sys.external_streams where name = 'opcOutput') Begin CREATE EXTERNAL STREAM opcOutput WITH ( DATA_SOURCE = LocalSQLOutput, LOCATION = N'OpcDB.dbo.opcMeasures' ) End EXEC sys.sp_create_streaming_job @name=N'opcProcessing', @statement= N'Select i.NodeId as tag,i.Value.Value as tagval, i.Value.SourceTimestamp as tagtime INTO opcOutput from opcInput i' exec sys.sp_start_streaming_job @name=N'opcProcessing'
But when I execute it and try to generate data on my OPC UA Server (mention that, data perfectly sending to IoT Hub if use $upstream in my route) I haven't any records in my OpcDB.dbo.opcMeasures (yes, it already exists in DB) table.
Where am I made a mistake?
JSON message from OPC Publisher is:
{
"body": [
{
"NodeId": "http://www.prosysopc.com/OPCUA/SimulationNodes/#i=1001",
"ApplicationUri": "urn:host.docker.internal:OPCUA:SimulationServer",
"Value": {
"Value": 8,
"SourceTimestamp": "2021-01-26T06:06:54Z"
}
}
],
"enqueuedTime": "2021-01-26T06:07:04.132Z",
"properties": {
"$$ContentType": "application/x-monitored-item-json-v1",
"iothub-message-schema": "application/ua+json",
"$$ContentEncoding": "utf-8"
}
}
Azure IoT Edge
Azure IoT Edge
Sign in to answer