Partilhar via

Parse dados JSON e Avro no Azure Stream Analytics

O serviço Azure Stream Analytics suporta o processamento de eventos em formatos de dados CSV, JSON e Avro. Os dados JSON e Avro podem ser estruturados e conter alguns tipos complexos, como objetos aninhados (registros) e matrizes.

Tipos de dados de registo

Os tipos de dados de registro são usados para representar matrizes JSON e Avro quando os formatos correspondentes são usados nos fluxos de dados de entrada. Estes exemplos demonstram um sensor de exemplo, que está lendo eventos de entrada no formato JSON. Eis um exemplo de um único evento:

{
    "DeviceId" : "12345",
    "Location" :
    {
        "Lat": 47,
        "Long": 122
    },
    "SensorReadings" :
    {
        "Temperature" : 80,
        "Humidity" : 70,
        "CustomSensor01" : 5,
        "CustomSensor02" : 99,
        "SensorMetadata" : 
        {
        "Manufacturer":"ABC",
        "Version":"1.2.45"
        }
    }
}

Acessar campos aninhados no esquema conhecido

Use notação de pontos (.) para aceder diretamente a campos aninhados a partir da sua consulta. Por exemplo, essa consulta seleciona as coordenadas Latitude e Longitude na propriedade Location nos dados JSON anteriores. Use a notação dos pontos para navegar por vários níveis, como mostrado no seguinte excerto:

SELECT
    DeviceID,
    Location.Lat,
    Location.Long,
    SensorReadings.Temperature,
    SensorReadings.SensorMetadata.Version
FROM input

O resultado é:

|DeviceID|Lat|Long|Temperature|Version|
|-|-|-|-|-|
|12345|47|122|80|1.2.45|

Selecionar todas as propriedades

Pode selecionar todas as propriedades de um registo aninhado usando o * caracter universal. Considere o seguinte exemplo:

SELECT
    DeviceID,
    Location.*
FROM input

O resultado é:

|DeviceID|Lat|Long|
|-|-|-|
|12345|47|122|

Aceder a campos aninhados quando o nome da propriedade é uma variável

Use a função GetRecordPropertyValue se o nome da propriedade for uma variável. Esta função ajuda-te a construir consultas dinâmicas sem codificar diretamente os nomes das propriedades.

Por exemplo, imagine que o fluxo de dados de amostra precisa de ser ligado a dados de referência contendo limiares para cada sensor de dispositivo. Um trecho desses dados de referência é mostrado no trecho a seguir.

{
    "DeviceId" : "12345",
    "SensorName" : "Temperature",
    "Value" : 85
},
{
    "DeviceId" : "12345",
    "SensorName" : "Humidity",
    "Value" : 65
}

O objetivo é juntar o conjunto de dados de amostra do topo do artigo a esses dados de referência, e gerar um evento para cada medida do sensor acima do seu limiar. Esta junção significa que o evento único pode gerar múltiplos eventos de saída se múltiplos sensores estiverem acima dos respetivos limiares. Para obter resultados semelhantes sem uma associação, consulte o seguinte exemplo:

SELECT
    input.DeviceID,
    thresholds.SensorName,
    "Alert: Sensor above threshold" AS AlertMessage
FROM input      -- stream input
JOIN thresholds -- reference data input
ON
    input.DeviceId = thresholds.DeviceId
WHERE
    GetRecordPropertyValue(input.SensorReadings, thresholds.SensorName) > thresholds.Value

GetRecordPropertyValue seleciona a propriedade em SensorReadings que corresponde ao nome da propriedade proveniente dos dados de referência. Depois extrai o valor associado do SensorReadings.

O resultado é:

|DeviceID|SensorName|AlertMessage|
| - | - | - |
| 12345 | Humidity | Alert: Sensor above threshold |

Converter campos de registro em eventos separados

Para converter campos de registo em eventos separados, use o operador APPLY juntamente com a função GetRecordProperties .

Ao usar os dados de amostra originais, pode usar a seguinte consulta para extrair propriedades em diferentes eventos:

SELECT
    event.DeviceID,
    sensorReading.PropertyName,
    sensorReading.PropertyValue
FROM input as event
CROSS APPLY GetRecordProperties(event.SensorReadings) AS sensorReading

O resultado é:

|DeviceID|SensorName|AlertMessage|
|-|-|-|
|12345|Temperature|80|
|12345|Humidity|70|
|12345|CustomSensor01|5|
|12345|CustomSensor02|99|
|12345|SensorMetadata|[object Object]|

Ao usar o WITH, pode encaminhar esses eventos para diferentes destinos:

WITH Stage0 AS
(
    SELECT
        event.DeviceID,
        sensorReading.PropertyName,
        sensorReading.PropertyValue
    FROM input as event
    CROSS APPLY GetRecordProperties(event.SensorReadings) AS sensorReading
)

SELECT DeviceID, PropertyValue AS Temperature INTO TemperatureOutput FROM Stage0 WHERE PropertyName = 'Temperature'
SELECT DeviceID, PropertyValue AS Humidity INTO HumidityOutput FROM Stage0 WHERE PropertyName = 'Humidity'

Analisar o registro JSON em dados de referência SQL

Quando usa o Azure SQL Database como dados de referência no seu trabalho, pode incluir uma coluna que contenha dados em formato JSON. O exemplo seguinte mostra este formato:

|DeviceID|Data|
|-|-|
|12345|{"key": "value1"}|
|54321|{"key": "value2"}|

Pode analisar o registo JSON na coluna Data escrevendo uma função simples em JavaScript definida pelo utilizador.

function parseJson(string) {
return JSON.parse(string);
}

Para aceder aos campos dos seus registos JSON, crie um passo na sua consulta de Stream Analytics, como mostrado no exemplo seguinte.

WITH parseJson as
(
SELECT DeviceID, udf.parseJson(sqlRefInput.Data) as metadata,
FROM sqlRefInput
)

SELECT metadata.key
INTO output
FROM streamInput
JOIN parseJson 
ON streamInput.DeviceID = parseJson.DeviceID

Tipos de dados de matriz

Os tipos de dados de matriz são uma coleção ordenada de valores. Esta secção detalha algumas operações típicas sobre valores de arrays. Estes exemplos utilizam as funções GetArrayElement, GetArrayElements, GetArrayLength e o operador APPLY .

Aqui está um exemplo de um evento. Tanto CustomSensor03 como SensorMetadata são do tipo array:

{
    "DeviceId" : "12345",
    "SensorReadings" :
    {
        "Temperature" : 80,
        "Humidity" : 70,
        "CustomSensor01" : 5,
        "CustomSensor02" : 99,
        "CustomSensor03": [12,-5,0]
     },
    "SensorMetadata":[
        {          
            "smKey":"Manufacturer",
            "smValue":"ABC"                
        },
        {
            "smKey":"Version",
            "smValue":"1.2.45"
        }
    ]
}

Trabalhar com um elemento específico do array

Selecione o elemento do array num índice especificado (selecione o primeiro elemento do array):

SELECT
    GetArrayElement(SensorReadings.CustomSensor03, 0) AS firstElement
FROM input

O resultado é:

|firstElement|
|-|
|12|

Selecionar comprimento da matriz

SELECT
    GetArrayLength(SensorReadings.CustomSensor03) AS arrayLength
FROM input

O resultado é:

|arrayLength|
|-|
|3|

Converter elementos de matriz em eventos separados

Selecione todos os elementos do array como eventos individuais. O operador APPLY , juntamente com a função incorporada GetArrayElements , extrai todos os elementos do array como eventos individuais:

SELECT
    DeviceId,
	CustomSensor03Record.ArrayIndex,
	CustomSensor03Record.ArrayValue
FROM input
CROSS APPLY GetArrayElements(SensorReadings.CustomSensor03) AS CustomSensor03Record

O resultado é:

|DeviceId|ArrayIndex|ArrayValue|
|-|-|-|
|12345|0|12|
|12345|1|-5|
|12345|2|0|

SELECT   
    i.DeviceId,	
    SensorMetadataRecords.ArrayValue.smKey as smKey,
    SensorMetadataRecords.ArrayValue.smValue as smValue
FROM input i
CROSS APPLY GetArrayElements(SensorMetadata) AS SensorMetadataRecords

O resultado é:

|DeviceId|smKey|smValue|
|-|-|-|
|12345|Manufacturer|ABC|
|12345|Version|1.2.45|

Para mostrar os campos extraídos em colunas, pivote o conjunto de dados usando a sintaxe WITH juntamente com a operação JOIN . Esta junção requer uma condição de contorno temporal que impeça duplicação:

WITH DynamicCTE AS (
	SELECT   
		i.DeviceId,
		SensorMetadataRecords.ArrayValue.smKey as smKey,
		SensorMetadataRecords.ArrayValue.smValue as smValue
	FROM input i
	CROSS APPLY GetArrayElements(SensorMetadata) AS SensorMetadataRecords 
)

SELECT
	i.DeviceId,
	i.Location.*,
	V.smValue AS 'smVersion',
	M.smValue AS 'smManufacturer'
FROM input i
LEFT JOIN DynamicCTE V ON V.smKey = 'Version' and V.DeviceId = i.DeviceId AND DATEDIFF(minute,i,V) BETWEEN 0 AND 0 
LEFT JOIN DynamicCTE M ON M.smKey = 'Manufacturer' and M.DeviceId = i.DeviceId AND DATEDIFF(minute,i,M) BETWEEN 0 AND 0

O resultado é:

|DeviceId|Lat|Long|smVersion|smManufacturer|
|-|-|-|-|-|
|12345|47|122|1.2.45|ABC|

Conteúdos relacionados

Tipos de Dados em Azure Stream Analytics

  • Last updated on