Share via

Have a problem when connect external datasource to Azure SQL Edge

Егор Карташов 6 Reputation points
2021-01-26T08:29:01.77+00:00

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:

  1. 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")"
  2. 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

An Azure service that is used to deploy cloud workloads to run on internet of things (IoT) edge devices via standard containers.


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.