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: イベントのタイムスタンプ。

Cloud メタデータ インスタンス属性へのアクセス

メタデータ インスタンス属性には、常に 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: イベントのタイムスタンプ。