Abfrage in BigQuery

In diesem Leitfaden wird beschrieben, wie Sie Daten in BigQuery für typische Anwendungsfälle der Manufacturing Data Engine (MDE) abfragen.

Datensätze werden mit Cloud-Metadaten zusammengeführt

Wenn die Materialisierung von Cloud-Metadaten deaktiviert ist, können Sie auf Cloud-Metadateninstanzen zugreifen, indem Sie die entsprechende Datensatz-Tabelle mit der Metadatentabelle metadata-store über die Metadaten-ID instance_id verknüpfen. Verwenden Sie dazu die folgende SQL-Abfrage:

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

Ersetzen Sie Folgendes:

• RECORD_TABLE_NAME: Name der Datensatz-Tabelle.
• BUCKET_NAME: Name des Cloud-Metadaten-Buckets.
• EVENT_TIMESTAMP: Zeitstempel des Ereignisses.

Um die Abfrageleistung zu verbessern, können Sie optional die Bucket-Nummer in der ON-Klausel angeben, da metadata-store nach Bucket-Nummer partitioniert ist. Beispiel:

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

Ersetzen Sie Folgendes:

• BUCKET_NAME: Name des Cloud-Metadaten-Buckets.
• EVENT_TIMESTAMP: Zeitstempel des Ereignisses.

Zugriff auf Instanzattribute für Cloud-Metadaten

Sie können auf Metadaten-Instanzattribute mit der JSON-Punktnotation zugreifen, die immer ein JSON-Objekt zurückgibt, oder mit einer der BigQuery-JSON-Funktionen, z. B. JSON_VALUE, um Strings oder andere Datentypen zu extrahieren. Sehen Sie sich folgendes Beispiel an:

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

Wenn die Materialisierung von Cloud-Metadaten aktiviert ist, können Sie auch direkt über den Datensatz auf die Attribute der Metadateninstanz zugreifen. Sehen Sie sich folgendes Beispiel an:

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

Liste aller Instanz-IDs in cloud_metadata_ref abrufen

So rufen Sie ein Array aller Metadaten-Instanz-IDs ab, die im Feld cloud_metadata_ref eines Datensatzes enthalten sind:

1. Erstellen Sie die benutzerdefinierte Funktion (UDF) mit der folgenden SQL-Abfrage:

   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. Führen Sie die Funktion in einer Abfrage aus:

   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
   

   Ersetzen Sie Folgendes:

• RECORD_TABLE_NAME: Name der Datensatz-Tabelle.
• EVENT_TIMESTAMP: Zeitstempel des Ereignisses.