Query in BigQuery

Questa guida spiega come eseguire query sui dati in BigQuery per i casi d'uso tipici di Manufacturing Data Engine (MDE).

I record vengono uniti ai metadati cloud

Se la materializzazione dei metadati cloud è disattivata, puoi accedere alle istanze dei metadati cloud unendo la tabella dei record pertinente con metadata-store nei metadati instance_id con la seguente query 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

Sostituisci quanto segue:

• RECORD_TABLE_NAME: il nome della tabella dei record.
• BUCKET_NAME: Nome del bucket dei metadati cloud.
• EVENT_TIMESTAMP: timestamp dell'evento.

Per migliorare le prestazioni delle query e poiché metadata-store è partizionato in base al numero di bucket, puoi specificare facoltativamente il numero di bucket nella clausola ON, come nella seguente query 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

Sostituisci quanto segue:

• BUCKET_NAME: Nome del bucket dei metadati cloud.
• EVENT_TIMESTAMP: timestamp dell'evento.

Accesso agli attributi dell'istanza dei metadati cloud

Puoi accedere agli attributi dell'istanza dei metadati utilizzando la notazione con punti JSON, che restituisce sempre un oggetto JSON, oppure utilizzando una delle funzioni JSON di BigQuery, ad esempio JSON_VALUE, per estrarre stringhe o altri tipi di dati. Vedi il seguente esempio:

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

Allo stesso modo, se la materializzazione dei metadati cloud è attivata, puoi accedere agli attributi dell'istanza dei metadati direttamente dal record. Vedi il seguente esempio:

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

Ottenere un elenco di tutti gli ID istanza contenuti in cloud_metadata_ref

Per ottenere un array di tutti gli ID istanza dei metadati contenuti nel campo cloud_metadata_ref di un record:

1. Crea la funzione definita dall'utente con la seguente query 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. Esegui la funzione in una query:

   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
   

   Sostituisci quanto segue:

• RECORD_TABLE_NAME: il nome della tabella dei record.
• EVENT_TIMESTAMP: timestamp dell'evento.