Consulta en BigQuery

En esta guía, se explica cómo consultar datos en BigQuery para los casos de uso típicos de Manufacturing Data Engine (MDE).

Los registros se unen con los metadatos de la nube

Si la materialización de metadatos de la nube está inhabilitada, puedes acceder a las instancias de metadatos de la nube uniendo la tabla de registros pertinente con el metadata-store en los metadatos instance_id con la siguiente consulta en 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

Reemplaza lo siguiente:

  • RECORD_TABLE_NAME: Es el nombre de la tabla de registros.
  • BUCKET_NAME: Es el nombre del bucket de metadatos de la nube.
  • EVENT_TIMESTAMP: Es la marca de tiempo del evento.

Para mejorar el rendimiento de las consultas y, dado que la tabla metadata-store está particionada según el número de bucket, puedes especificar de forma opcional el número de bucket en la cláusula ON, como en la siguiente consulta en 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

Reemplaza lo siguiente:

  • BUCKET_NAME: Es el nombre del bucket de metadatos de la nube.
  • EVENT_TIMESTAMP: Es la marca de tiempo del evento.

Acceso a los atributos de instancia de metadatos de Cloud

Puedes acceder a los atributos de la instancia de metadatos con la notación de puntos JSON, que siempre devuelve un objeto JSON, o con una de las funciones JSON de BigQuery, como JSON_VALUE para extraer cadenas o cualquier otro tipo de datos. Consulta el siguiente ejemplo:

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

Del mismo modo, si la materialización de metadatos de Cloud está habilitada, puedes acceder a los atributos de la instancia de metadatos directamente desde el registro. Consulta el siguiente ejemplo:

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

Cómo obtener una lista de todos los IDs de instancias incluidos en cloud_metadata_ref

Para obtener un array de todos los IDs de instancias de metadatos incluidos en el campo cloud_metadata_ref de un registro, sigue estos pasos:

  1. Crea la función definida por el usuario (UDF) con la siguiente consulta en 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. Ejecuta la función en una 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
    

    Reemplaza lo siguiente:

  • RECORD_TABLE_NAME: Es el nombre de la tabla de registros.
  • EVENT_TIMESTAMP: Es la marca de tiempo del evento.