Consultar no BigQuery

Este guia explica como consultar dados no BigQuery para casos de uso típicos do Manufacturing Data Engine (MDE).

Registros combinados com metadados da nuvem

Se a materialização de metadados da nuvem estiver desativada, você poderá acessar instâncias de metadados da nuvem unindo a tabela de registros relevante com o metadata-store no instance_id de metadados com a seguinte consulta SQL:

SELECT
  dnr.*,
  ms.instance
FROM
  mde_data.`RECORD_TABLE_NAME` AS dnr
LEFT JOIN
  mde_dimension.`metadata-store` AS ms
ON
  ms.instance_id = JSON_VALUE(cloud_metadata_ref, "$.BUCKET_NAME.instance_id")
WHERE
  DATE(event_timestamp) = 'EVENT_TIMESTAMP'
LIMIT 100

Substitua:

• RECORD_TABLE_NAME: nome da tabela de registros.
• BUCKET_NAME: nome do bucket de metadados da nuvem.
• EVENT_TIMESTAMP: carimbo de data/hora do evento.

Para melhorar o desempenho da consulta e como o metadata-store é particionado no número do bucket, é possível especificar o número do bucket na cláusula ON, como na seguinte consulta SQL:

SELECT
  dnr.*,
  ms.instance
FROM
  mde_data.`<RECORD_TABLE_NAME>` AS dnr
LEFT JOIN
  mde_dimension.`metadata-store` AS ms
ON
  ms.instance_id = JSON_VALUE(cloud_metadata_ref, "$.BUCKET_NAME.instance_id")
  AND ms.bucket_number = <BUCKET_NUMBER>
WHERE
  DATE(event_timestamp) = 'EVENT_TIMESTAMP'
LIMIT 100

Substitua:

• BUCKET_NAME: nome do bucket de metadados da nuvem.
• EVENT_TIMESTAMP: carimbo de data/hora do evento.

Acesso aos atributos da instância de metadados do Cloud

É possível acessar atributos de instância de metadados usando a notação de ponto JSON, que sempre retorna um objeto JSON, ou usando uma das funções JSON do BigQuery, como JSON_VALUE para extrair strings ou outros tipos de dados. Confira o exemplo a seguir:

SELECT
  dnr.*,
  ms.instance.deviceName                    -- this returns a double quoted JSON string
  JSON_VALUE(ms.instance, '$.deviceName')   -- this returns a string
FROM
  mde_data.`example-record-tbl` AS dnr
LEFT JOIN
  mde_dimension.`metadata-store` AS ms
ON
  ms.instance_id = JSON_VALUE(cloud_metadata_ref, "$.bucket.instance_id")
WHERE
  DATE(event_timestamp) = '2023-01-01'
LIMIT 100

Da mesma forma, se a materialização de metadados da nuvem estiver ativada, você poderá acessar os atributos da instância de metadados diretamente do registro. Veja o exemplo a seguir:

SELECT
  *   (EXCEPT materialized_cloud_metadata),
  materialized_cloud_metadata.device.deviceName                     -- this returns a double quoted JSON string
  JSON_VALUE(materialized_cloud_metadata., '$.device.deviceName')   -- this returns a string
FROM
  mde_data.`example-record-tbl`
WHERE
  DATE(event_timestamp) = '2023-01-01'
LIMIT 100

Como obter uma lista de todos os IDs de instância contidos em "cloud_metadata_ref"

Para receber uma matriz de todos os IDs de instâncias de metadados contidos no campo cloud_metadata_ref de um registro, siga estas etapas:

1. Crie a função definida pelo usuário (UDF) com a seguinte consulta SQL:

   CREATE OR REPLACE FUNCTION `mde_data.get_instance_ids`(input JSON) RETURNS ARRAY<STRING> LANGUAGE js AS R"""
   return input ? Object.keys(input).map(bucketName => input[bucketName].instance_id).filter(instance_id => instance_id != null) : [];
   """;
   

2. Execute a função em uma consulta:

   SELECT
    mde_data.get_instance_ids(cloud_metadata_ref) as metadata_instance_ids,
    *,
   FROM
    mde_data.`RECORD_TABLE_NAME`
   WHERE
   DATE(event_timestamp) = 'EVENT_TIMESTAMP'
   LIMIT 100
   

   Substitua:

• RECORD_TABLE_NAME: nome da tabela de registros.
• EVENT_TIMESTAMP: carimbo de data/hora do evento.