在 BigQuery 中查询

本指南介绍了如何在 BigQuery 中查询数据，以满足典型的制造数据引擎 (MDE) 使用情形。

记录与云元数据联接

如果停用了云元数据具体化，您可以通过将相关记录表与元数据 instance_id 上的 metadata-store 联接起来，使用以下 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

替换以下内容：

• RECORD_TABLE_NAME：记录表的名称。
• BUCKET_NAME：云元数据存储桶的名称。
• EVENT_TIMESTAMP：事件的时间戳。

为了提高查询性能，并由于 metadata-store 是按桶编号进行分区的，因此您可以选择在 ON 子句中指定桶编号，如以下 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：云元数据存储桶的名称。
• 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：事件的时间戳。