Mengkueri di BigQuery

Panduan ini menjelaskan cara membuat kueri data di BigQuery untuk kasus penggunaan Manufacturing Data Engine (MDE) yang umum.

Penggabungan data dengan metadata cloud

Jika materialisasi metadata cloud dinonaktifkan, Anda dapat mengakses instance metadata cloud dengan menggabungkan tabel rekaman yang relevan dengan metadata-store di metadata instance_id menggunakan kueri SQL berikut:

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

Ganti kode berikut:

• RECORD_TABLE_NAME: Nama tabel rekaman.
• BUCKET_NAME: Nama bucket metadata cloud.
• EVENT_TIMESTAMP: Stempel waktu peristiwa.

Untuk meningkatkan performa kueri dan karena metadata-store dipartisi berdasarkan nomor bucket, Anda dapat secara opsional menentukan nomor bucket dalam klausa ON, seperti kueri SQL berikut:

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

Ganti kode berikut:

• BUCKET_NAME: Nama bucket metadata cloud.
• EVENT_TIMESTAMP: Stempel waktu peristiwa.

Akses atribut instance metadata cloud

Anda dapat mengakses atribut instance metadata menggunakan notasi titik JSON yang selalu menampilkan objek JSON, atau menggunakan salah satu fungsi JSON BigQuery, seperti JSON_VALUE untuk mengekstrak string atau jenis data lainnya. Lihat contoh berikut:

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

Demikian pula, jika materialisasi metadata cloud diaktifkan, Anda dapat mengakses atribut instance metadata langsung dari rekaman. Lihat contoh berikut:

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

Mendapatkan daftar semua ID instance yang ada dalam cloud_metadata_ref

Untuk mendapatkan array semua ID instance metadata yang ada dalam kolom cloud_metadata_ref dari sebuah rekaman, ikuti langkah-langkah berikut:

1. Buat fungsi yang ditentukan pengguna (UDF) dengan kueri SQL berikut:

   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. Jalankan fungsi dalam kueri:

   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
   

   Ganti kode berikut:

• RECORD_TABLE_NAME: Nama tabel rekaman.
• EVENT_TIMESTAMP: Stempel waktu peristiwa.