Skip to main content
Solved

Is there documentation on SQL Queries?


Peter Quinn
Committed
Forum|alt.badge.img+2

Hi,

I’m just getting started with Cognite. I’m trying to create some assets. I’ve loaded them into RAW and I’m trying to write a SQL transformation to load them into assets. Is there documentation on what functions are available? I’ve gone through the online training and the simple example there, but it doesn’t cover what I’m trying to do.

My specific questions are that I’m looking for documentation to answer are (for now):

  1. What data type is the id, parentid, and other fields
  2. How do I cast the string data in the csv to right type. I assume there is a Cast function
    1. The UI tells me I have the wrong type. It doesn’t tell me which field and what type it expects. That would really be helpful. In this case, I know what field, but I don’t know the type it’s looking for.
  3. For a top level asset in the hierarchy, what do I use for parentID?
  4. If I have spaces in the field names in my CSV, how can I reference the field?

Best answer by Fredrik Holm

The SQL is based on Spark SQL 3.3.1, so I recommended looking at this:
https://spark.apache.org/docs/latest/sql-ref.html

View original
Did this topic help you find an answer to your question?

Peter Quinn
Committed
Forum|alt.badge.img+2

I don’t see how to edit my post, but it’s funny. I now see it telling me which field is wrong (externalid) and it needs to be string.

Also, I googled for SQL Cast and found that I need to use

cast (id as string)

and not cast(id, string)

Still, it would be nice to see this documented here.


Anita Hæhre
Seasoned Practitioner
Forum|alt.badge.img+1
  • Head of Academy and Community
  • December 12, 2022

Hi @Peter Quinn thanks for addressing this, we’re looking into it, and will update you and the community as soon as possible. 

On editing posts: you should have unlimited time to edit all your posts. Could you please share a screenshot of what you see when you try to edit your post?


Peter Quinn
Committed
Forum|alt.badge.img+2

Thanks for your quick response.

About editing posts. I didn’t see the UI for doing it. I will use this post to try again 😀

(short time later. I didn’t see the … in the lower righthand corner that lets me edit. Thanks again)


Anita Hæhre
Seasoned Practitioner
Forum|alt.badge.img+1
  • Head of Academy and Community
  • December 12, 2022

Super, happy it’s sorted:) 


Forum|alt.badge.img

The SQL is based on Spark SQL 3.3.1, so I recommended looking at this:
https://spark.apache.org/docs/latest/sql-ref.html


Reply


Cookie Policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie Settings