Query in BigQuery

This guide explains how to query data in BigQuery for typical Manufacturing Data Engine (MDE) use-cases.

Records join with cloud metadata

If cloud metadata materialization is disabled, you can access cloud metadata instances by joining relevant record table with the metadata-store on the metadata instance_id with the following SQL query:

  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 
 

Replace the following:

• RECORD_TABLE_NAME : Name of the record table.
• BUCKET_NAME : Name of the cloud metadata bucket.
• EVENT_TIMESTAMP : Timestamp of the event.

To improve query performance and since the metadata-store is partitioned on bucket number, you can optionally specify the bucket number in the ON clause, as the following SQL query:

  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 
 

Replace the following:

• BUCKET_NAME : Name of the cloud metadata bucket.
• EVENT_TIMESTAMP : Timestamp of the event.

Cloud metadata instance attributes access

You can access metadata instance attributes using the JSON dot notation which always returns a JSON object, or using one of the BigQuery JSON functions , such as JSON_VALUE to extract strings or other data types. See the following example:

  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 
 

Similarly, if cloud metadata materialization is enabled, you can access metadata instance attributes directly from the record. See the following example:

  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 
 

Obtaining a list of all instance IDs contained in cloud_metadata_ref

To obtain an array of all metadata instance IDs contained in the cloud_metadata_ref field of a record follow these steps:

1. Create the user defined function (UDF) with the following SQL query:

     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. Execute the function in a query:

     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 
    
   

   Replace the following:

• RECORD_TABLE_NAME : Name of the record table.
• EVENT_TIMESTAMP : Timestamp of the event.