在 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 陣列,請按照下列步驟操作:
使用下列 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) : []; """;在查詢中執行函式:
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:事件的時間戳記。