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:
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)
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!