在 BigQuery 中查詢

本指南說明如何針對典型的 Manufacturing Data Engine (MDE) 用途,在 BigQuery 中查詢資料。

記錄會與雲端中繼資料合併

如果停用雲端中繼資料具體化,您可以透過下列 SQL 查詢,將相關記錄資料表與中繼資料 metadata-store 中繼資料 instance_id 聯結,存取雲端中繼資料執行個體:

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

更改下列內容:

  • RECORD_TABLE_NAME:記錄資料表的名稱。
  • BUCKET_NAME:雲端中繼資料 bucket 的名稱。
  • EVENT_TIMESTAMP:事件的時間戳記。

為提升查詢效能,並由於 metadata-store 是依據 bucket 編號進行分割,因此您可以在 ON 子句中指定 bucket 編號 (選用),如下列 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

更改下列內容:

  • BUCKET_NAME:雲端中繼資料 bucket 的名稱。
  • EVENT_TIMESTAMP:事件的時間戳記。

存取雲端中繼資料執行個體屬性

您可以使用 JSON 點標記法存取中繼資料執行個體屬性,這會一律傳回 JSON 物件,也可以使用 BigQuery JSON 函式 (例如 JSON_VALUE) 擷取字串或其他資料類型。請參閱以下示例:

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

同樣地,如果啟用雲端中繼資料具體化,您可以直接從記錄存取中繼資料執行個體屬性。請參閱以下範例:

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

取得 cloud_metadata_ref 中包含的所有執行個體 ID 清單

如要取得記錄 cloud_metadata_ref 欄位中包含的所有中繼資料例項 ID 陣列,請按照下列步驟操作:

  1. 使用下列 SQL 查詢建立使用者定義函式 (UDF):

    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. 在查詢中執行函式:

    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
    

    更改下列內容:

  • RECORD_TABLE_NAME:記錄資料表的名稱。
  • EVENT_TIMESTAMP:事件的時間戳記。