How to create events for different assets with transformations

  • 1 March 2023
  • 1 reply
  • 47 views

Userlevel 5

A common setup in CDF (Cognite Data Fusion) is to first build an asset hierarchy, and then to attach events, timeseries etc. to those assets. One way to do it is to use transformations.

 

In this short article, we’ll take events as an example. It works the same way for timeseries and sequences. Let’s assume we already have an asset hierarchy and we want to attach events to it. We’ll suppose that we have a RAW table, from which we want to create events, that looks like this: 

 

_AiI_0MJFcH7PmW2XIpd69ojldbhLNLKG-JcFLWJfwUleO9bGbrdVOMsBMQFVwWqhJrbiMlFh-s13MmRVUbxsHjmESstwe9bRjoIill_lCE9RDJ_VpdGhI-XSn28reT1LU_xUr6Pflrqvaa0acPPyck

 

In transformations, to attach an asset to a created event, you need to specify the corresponding asset ID (assetId in the target schema). Asset IDs being automatically generated, we usually prefer using external IDs because we know what they are made of: an ID from an ERP, a value following a naming convention, etc. 

 

Since the asset ID is the expected value for the assetId field in the target schema, we need to retrieve for each asset its ID, based on its external ID. As mentioned in the documentation, we can read from CDF resources in transformations, by using “_cdf” (https://docs.cognite.com/cdf/integration/guides/transformation/write_sql_queries#read-from-other-cdf-resource-types) . For example, we can read events with “_cdf.events”. So, all we have to do is to make a join between our source table for events and the existing assets, on the asset external IDs. That looks like this:

 

select

  cast(`e`.`start` as TIMESTAMP) as startTime,

  cast(`e`.`end` as TIMESTAMP) as endTime,

  cast(`e`.`description` as STRING) as description,

  cast(`e`.`type` as STRING) as type,

  cast(`e`.`externalId` as STRING) as externalId,

  array(cast(`a`.`id` as LONG)) as assetIds

from

  `event_db`.`events` e

  inner join `_cdf`.`assets` a on a.externalId = e.`assetExternalId`;

 

As you can see in the UI, for events, the assetIds field expects an array of Ids (events can be linked to multiple assets)

9ejW1NEFp2zT7_4FNNJs1kwXLPnz7ImcloV5gNbCOZXsV57_k342eJPozQOrAM1LRtKOcH5xHozf07m01jZ6O9_t5FQ18cog1GDVxJtRsvKitLon3v209nWHI-PPpInX8Q1GlIdxhQq84qSfEhu2MAQ

 

There is also a function allowing to retrieve asset IDs from their names, with a few different options. More about it here: https://docs.cognite.com/cdf/integration/guides/transformation/write_sql_queries/#asset_ids

 

Please let us know if you have any questions or remarks! 


1 reply

Userlevel 3

Great article Pierre. 

Pierre also opened a product idea around that topic, if you are interested, please upvote it

 

Reply