locked
Azure Streaming Analytics query reference - entire message in a string RRS feed

  • Question

  • How can I select the entire message as a blob or string in a streaming analytics query? I can either select * or select individual fields and use the different metadata and helper functions.

    But unable to find a way to simply select the entire message content as a string.

    Thanks.

    Friday, February 7, 2020 7:05 PM

Answers

  • I see. Thank you for this additional detail, Sanimesa.

    By default, the SQL Database connector is going to write the JSON object where property values and the column schema are aligned. You are wanting the entire JSON object to be written to a single table column. 

    You likely need to import the JSON to Azure SQL Database using the following T-SQL examples as detailed: Import a JSON document into a single column and there is an Azure Blob Storage example.

    Create External Data Source:

    CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
     WITH ( TYPE = BLOB_STORAGE,
            LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
            CREDENTIAL= MyAzureBlobStorageCredential);

    Followed by running the import:

    BULK INSERT Product
    FROM 'data/product.dat'
    WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

    So, you currently can not accomplish this directly when outputting JSON from ASA to Azure SQL Database but can do so indirectly by outputting JSON from ASA to Blob Storage followed by running an import as an External Data Source to Azure SQL Database. 

    Please let me know if this answers your question.

    Regards,

    Mike


    Saturday, February 8, 2020 3:03 AM

All replies

  • Hi Sanimesa,

    Please see the following document with regard to query patterns: Common query patterns in Azure Stream Analytics

    As an example:

    A SELECT * query projects all the fields of an incoming event and sends them to the output.

    SELECT
    	*
    INTO Output
    FROM Input

    Please let me know if you require additional information.

    Regards,

    Mike


    Friday, February 7, 2020 9:56 PM
  • Hello Mike,

    Thanks for your response, I am aware of that option, but it breaks the output into individual fields. What I want is the entire message in a blob. This is useful in scenarios where the devices send a variety of messages.

    Let me ask this another way: I can route telemetry messages into a custom endpoint into an Azure Blob. The Blob shows each message as a complete JSON. How can I do the same when my target is Azure SQL database? Put the entire message into a single SQL database field?

    Thanks.

    Saturday, February 8, 2020 12:10 AM
  • I see. Thank you for this additional detail, Sanimesa.

    By default, the SQL Database connector is going to write the JSON object where property values and the column schema are aligned. You are wanting the entire JSON object to be written to a single table column. 

    You likely need to import the JSON to Azure SQL Database using the following T-SQL examples as detailed: Import a JSON document into a single column and there is an Azure Blob Storage example.

    Create External Data Source:

    CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
     WITH ( TYPE = BLOB_STORAGE,
            LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
            CREDENTIAL= MyAzureBlobStorageCredential);

    Followed by running the import:

    BULK INSERT Product
    FROM 'data/product.dat'
    WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

    So, you currently can not accomplish this directly when outputting JSON from ASA to Azure SQL Database but can do so indirectly by outputting JSON from ASA to Blob Storage followed by running an import as an External Data Source to Azure SQL Database. 

    Please let me know if this answers your question.

    Regards,

    Mike


    Saturday, February 8, 2020 3:03 AM