Skip to main content

Hello everyone,

I'm encountering an issue while working with Data model and SQL Transformation. I have a data model where I use a composite type to represent measurements. Here's a detailed description of my situation and the problem I'm facing.

Context

I want to model a type Metrics that includes two attributes, temperature and humidity. Each of these attributes is a composite type Measure, containing two sub-attributes: value and unit. Here is what my schema looks like in GraphQL:

enum Unit {
  Celsius
  Percent
}

type Measure {
  value: Float!
  unit: Unit!
}

type Metrics {
  temperature: Measure!
  humidity: Measure!
}

Problem

Although this model is syntactically correct in GraphQL, I am having difficulties loading this model using Cognite Data Fusion's SQL transformation. I haven't found a way to cast the unit and value fields into a Struct of type Measure.

Attempted SQL Transformation

I've tried several approaches to write the SQL transformation, but I can't seem to get the correct format. Here is an example of a transformation I attempted:

SELECT
  id,
  name,
  STRUCT(
    temperature_value AS value,
    temperature_unit AS unit
  ) AS temperature,
  STRUCT(
    humidity_value AS value,
    humidity_unit AS unit
  ) AS humidity
FROM
  my_data_table

Result

Despite my attempts, I receive type errors when loading the data, indicating that the temperature and humidity fields cannot be correctly cast into a Struct of type Measure.

Request for Help

I believe what I want to achieve is possible with GraphQL, but I haven't found how to perform this mapping in SQL Transformation. Has anyone encountered this problem before or have any suggestions on how to solve this SQL transformation issue?

Thank you in advance for your help!

Hi @Oussama ALLALI,

We have forwarded your request to our engineering team, and they will respond to you in this thread. Please be aware that there might be a delay in their response due to the summer vacation period. We appreciate your patience during this time. Thank you, and feel free to reach out if you have any other inquiries. 


Thank you @Dilini Fernando  for your comment, What is the usual response time ? We have a modeling choice to make, and we’d like to get on the right track.


The “shared summer vacation” period in Norway generally speaking ends at the end of July, but the specifics vary from year to year.

For 2024, the first day back at work after this period is Monday July 29th (so one week from when I wrote this comment). 


Reply