BigQuery에서 쿼리

이 가이드에서는 일반적인 Manufacturing Data Engine (MDE) 사용 사례에 대해 BigQuery에서 데이터를 쿼리하는 방법을 설명합니다.

클라우드 메타데이터와 레코드 조인

클라우드 메타데이터 구체화가 사용 중지된 경우 다음 SQL 쿼리를 사용하여 메타데이터 instance_id의 metadata-store와 관련 레코드 테이블을 조인하여 클라우드 메타데이터 인스턴스에 액세스할 수 있습니다.

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이 버킷 번호로 파티셔닝되므로 다음 SQL 쿼리와 같이 ON 절에서 버킷 번호를 선택적으로 지정할 수 있습니다.

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 점 표기법을 사용하거나 JSON_VALUE와 같은 BigQuery JSON 함수 중 하나를 사용하여 문자열이나 기타 데이터 유형을 추출하여 메타데이터 인스턴스 속성에 액세스할 수 있습니다. 아래 예시를 참조하세요.

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: 이벤트의 타임스탬프입니다.