Lancer une requête dans BigQuery

Ce guide explique comment interroger des données dans BigQuery pour les cas d'utilisation typiques de Manufacturing Data Engine (MDE).

Joindre des enregistrements avec des métadonnées cloud

Si la matérialisation des métadonnées cloud est désactivée, vous pouvez accéder aux instances de métadonnées cloud en joignant la table d'enregistrement pertinente à metadata-store sur les métadonnées instance_id avec la requête SQL suivante :

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

Remplacez les éléments suivants :

• RECORD_TABLE_NAME : nom de la table d'enregistrements.
• BUCKET_NAME : nom du bucket de métadonnées cloud.
• EVENT_TIMESTAMP : code temporel de l'événement.

Pour améliorer les performances des requêtes et puisque metadata-store est partitionné sur le numéro de bucket, vous pouvez éventuellement spécifier le numéro de bucket dans la clause ON, comme dans la requête SQL suivante :

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

Remplacez les éléments suivants :

• BUCKET_NAME : nom du bucket de métadonnées cloud.
• EVENT_TIMESTAMP : code temporel de l'événement.

Accès aux attributs d'instance des métadonnées cloud

Vous pouvez accéder aux attributs d'instance de métadonnées à l'aide de la notation par points JSON, qui renvoie toujours un objet JSON, ou à l'aide de l'une des fonctions JSON BigQuery, telles que JSON_VALUE, pour extraire des chaînes ou d'autres types de données. Consultez l'exemple suivant :

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

De même, si la matérialisation des métadonnées cloud est activée, vous pouvez accéder aux attributs d'instance de métadonnées directement à partir de l'enregistrement. Consultez l'exemple ci-dessous :

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

Obtenir la liste de tous les ID d'instance contenus dans cloud_metadata_ref

Pour obtenir un tableau de tous les ID d'instance de métadonnées contenus dans le champ cloud_metadata_ref d'un enregistrement, procédez comme suit :

1. Créez la fonction définie par l'utilisateur (UDF) avec la requête SQL suivante :

   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. Exécutez la fonction dans une requête :

   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
   

   Remplacez les éléments suivants :

• RECORD_TABLE_NAME : nom de la table d'enregistrements.
• EVENT_TIMESTAMP : code temporel de l'événement.