Skip to main content
Solved

How To Transform SQL RAW Data Into Time Series Tags

Satish Basa
Active

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 .

Best answer by Jason Dressel

@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

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

J
Seasoned Practitioner
Forum|alt.badge.img

@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
1 person likes this
  • Satish Basa
    Satish Basa
Satish Basa
Active

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

Satish Basa
Satish Basa
Active

@Mayur Agare  Please Check This Out.

Satish Basa
M
1 person likes this
  • M
    Mayur Agare
Glen Sykes
Seasoned Practitioner
  • Glen Sykes
  • Seasoned Practitioner
  • February 28, 2024

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
Satish Basa
1 person likes this
  • Satish Basa
    Satish Basa
M
Active
Forum|alt.badge.img

@Satish Basa thanks for sharing it !!!

 

Satish Basa
Active

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

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