Skip to main content

Dear Community,

How do we transform or create transformation of SQL Table data with some time series values in to Timeseries Tags In CDF?

Sample Data:

index   datetime                   CO2      H2O 
                                                       
 0   2019-05-07 01:00:00+00:00     50.5     25.0
 1   2019-05-07 02:00:00+00:00     55.0     27.7 

 

All we need is 2 tags CO2 & H2O with 2 data points (TS) in a dataset. I can run each tag under separate transformation.

Please help with SQL query to do in Transformation .

@Satish Basa 
To create the “tags” in CDF, as an example, you could try something like:

select
concat('ts-', name) as externalId,
name as name
from
values
('H20'),
('CO2') as tab(name)

 

 Then you’ll run 2 transformations (one for CO2, one for H20):
 

select
'ts-CO2' as externalId, -- CHANGE this for H20
-- you'll need to enter the correct format
to_timestamp(datetime, 'FORMAT PATTERN') as timestamp,
CO2 as value -- CHANGE this for H20
from
`_cdf`.your_cdf_raw_data_table

Hope this helps,

-Jason


@Jason Dressel  Awesome and Swift reply, Thank you a lot. Got It.


@Mayur Agare  Please Check This Out.


Hi Satish,

I’m the product manager for our Time Series API.  Can I ask from which source is this time series data coming from please? 

Where possible we try to minimise the use of RAW for onboarding of data points (particularly high volumes of data points).  Understanding the source of this data can help us streamline the onboarding process and enable you to directly write this data to the API, where feasible.


@Satish Basa thanks for sharing it !!!

 


Dear @Glen Sykes ,

Thank you for your kind  help.

We have 2 types of sources here. (working with brewery customer in SEA)

  1. SQL Data From Customer Multi-Site SCADA Systems, Consists of Transactional & TS Data.
  2. Some Simulated data using Python + Cognite SDK. (Want to run in Cognite Functions at time-based schedules)

for item  1 @Jason Dressel  helped to clarify.

for item 2 i am facing some issues like below:

2024-02-28 03:19 Function started

2024-02-28 03:20 : Traceback (most recent call last): 2024-02-28 03:20 : File "/home/site/wwwroot/function/_cognite_function_entry_point.py", line 459, in run_handle 2024-02-28 03:20 : result = handle(*function_argument_values) 2024-02-28 03:20 : File "/home/site/wwwroot/function/handler.py", line 74, in handle 2024-02-28 03:20 : insertDataFrame(Site1_Milling_Data)# Insert Site1 Data 2024-02-28 03:20 : File "/home/site/wwwroot/function/handler.py", line 59, in insertDataFrame 2024-02-28 03:20 : import pandas as pd

2024-02-28 03:20 : ModuleNotFoundError: No module named 'pandas'

2024-02-28 03:20 Function ended

 

Appreciate your help.


Reply