Committed ⭐️⭐️

Solved

How To Transform SQL RAW Data Into Time Series Tags

Forum|Forum|2 years ago
February 28, 2024
6 replies
121 views

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

J

Jason DresselAnswer

Expert ⭐️⭐️⭐️⭐️

@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 BasaAuthor

Committed ⭐️⭐️

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

Satish Basa

Satish BasaAuthor

Committed ⭐️⭐️

@Mayur Agare Please Check This Out.

Satish Basa

Glen Sykes

Expert ⭐️⭐️⭐️⭐️

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

Mayur Agare

Active ⭐️⭐️⭐️

@Satish Basa thanks for sharing it !!!

Satish BasaAuthor

Committed ⭐️⭐️

Dear @Glen Sykes ,

Thank you for your kind help.

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

SQL Data From Customer Multi-Site SCADA Systems, Consists of Transactional & TS Data.
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

Sign up

Welcome to Cognite Hub