Solved

How To Transform SQL RAW Data Into Time Series Tags

  • 28 February 2024
  • 6 replies
  • 47 views
Satish Basa
Rank

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 .

icon

Best answer by Jason Dressel 28 February 2024, 16:13

View original
Satish Basa

6 replies

J
Rank
Userlevel 4
Badge

@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

Satish Basa
Rank

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

Satish Basa
Satish Basa
Rank

@Mayur Agare  Please Check This Out.

Satish Basa
Glen Sykes
Rank
Userlevel 3

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.

Glen | Cognite Product Management
M
Badge

@Satish Basa thanks for sharing it !!!

 

Satish Basa
Rank

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.

Satish Basa

Reply


Terms & ConditionsCookie Settings