We have created a ADO output adapter for PSQL database in openPDC to archive PMU readings. And we are able send the data to PSQL table. But we are able to send only value to PSQL table using the below output adapter.
But our requirement is to send multiple values(like freq, phase, amplitude, etc) to a single table in PSQL database. How can we do that?
From the below topic we found that we cann’t send multiple values to single table using one AD adapter. So they suggested to use JOIN feature in SQL.
And in that they have provided one simple example about using JOIN like below.
WITH tsm AS
(
SELECT
SignalType.Acronym AS SignalType,
TimeSeriesMeasurement.Timestamp,
TimeSeriesMeasurement.Value
FROM
TimeSeriesMeasurement JOIN
Measurement ON TimeSeriesMeasurement.SignalID = Measurement.SignalID JOIN
SignalType ON Measurement.SignalTypeID = SignalType.ID
)
SELECT
FREQ.Timestamp,
VPHA.Value AS VPhase,
IPHA.Value AS APhase,
FREQ.Value AS Frequencie
FROM
tsm FREQ JOIN
tsm VPHA ON FREQ.Timestamp = VPHA.Timestamp JOIN
tsm IPHA ON FREQ.Timestamp = IPHA.Timestamp
WHERE
FREQ.SignalType = ‘FREQ’ AND
VPHA.SignalType = ‘VPHA’ AND
IPHA.SignalType = ‘IPHA’
ORDER BY FREQ.Timestamp
So can someone explain us how to use the above example? Like how many tables we need to create and what will be the columns of those tables.
Or please suggest us if any other method is present.
You can send multiple measurement streams to a single table. What you can’t do is define separate columns for each measurement stream in the TimeSeriesMeasurement table. I haven’t tried using PostgreSQL, but you should only need one table that looks something like this.
CREATE TABLE TimeSeriesMeasurement
(
SignalID NCHAR(36) NOT NULL,
Timestamp VARCHAR(24) NOT NULL,
Value DOUBLE PRECISION NOT NULL
)
The SignalID column is used to join the data with the Measurement table so you can determine which values belong to which measurement stream. The example query does that using a Common Table Expression so that it can be easily referenced multiple times in the query.
To pivot the data so that each measurement stream gets its own column in the result set, the query joins the CTE with itself multiple times. I chose to use joins here because it should generally work regardless of the database engine you’re using. You may instead want to look into the crosstab() function in PostgreSQL for pivoting data.
Actually we want each PMU measurement in seperate column because we wants access this table data from python code and need to process it. So we need all data in single table like.
CREATE TABLE FinalPMUTable
(
SignalID NCHAR(36) NOT NULL, (optinal column)
Timestamp VARCHAR(24) NOT NULL,
Volt_Phase_A DOUBLE NOT NULL,
Volt_Phase_B DOUBLE NOT NULL,
Volt_Phase_C DOUBLE NOT NULL,
Volt_Pos_Seq DOUBLE NOT NULL,
Angle_Phase_A DOUBLE NOT NULL,
Angle_Phase_B DOUBLE NOT NULL,
Angle_Phase_C DOUBLE NOT NULL,
Angle_Pos_Seq DOUBLE NOT NULL,
Frequencie DOUBLE NOT NULL,
ROCOF DOUBLE NOT NULL,
)
This how my final table looks like.
So can we achive this using crosstab() function?
I have tried using JOIN feature. But there is a drawback in this feature. We can join all the values from different table and we can view the joined data in query tool. But we cann’t get the joined into to seperate table. We want Joined data in seperate table, so that we can access single table in python code to get all the measurements of PMU.
Is this possible in crosstab() function? I want all the measuremnt in seperate table and each type measurement should be in seperate coloum as mentioned above.
The system cannot automatically put data into your FinalPMUTable. If you wanted that functionality from openPDC, you would need to implement a custom adapter that is capable of doing that.
You can write a query that copies data from TimeSeriesMeasurement to FinalPMUTable.
WITH tsm AS (...)
INSERT INTO FinalPMUTable(...)
SELECT ... FROM ...
However, assuming you can use Python to load data from a view instead of a table, you’d probably be better off doing that. Then you don’t have to worry about things like running the INSERT query periodically, dumping and refilling the table, or constraining the query to fill in only missing data.
CREATE VIEW FinalPMUTable
WITH tsm AS (...)
AS SELECT ... FROM ...
I don’t actually know how the crosstab() function works. I only did a quick Google search to discover how to pivot data in PostgreSQL, and that function came up. Presumably, it’s both easier to use and performs better than doing multiple joins the way I’ve done it in my example. If you are able to create a view using crosstab() that you can load into your Python application, then that’s the way I would recommend doing it.
As I am new to PSQL database concepts, I am not able understand the below mentioned query clearly.
CREATE VIEW FinalPMUTable
WITH tsm AS (…)
AS SELECT … FROM …
What is tsm in the above query?
Can you please write full Query, consider we have 3 tables as mentioned below. I want Final VIEW named as FinalPMUTable which contains value columns of all 3 tables.
CREATE TABLE PMUFrequency
(
SignalID NCHAR(36) NOT NULL,
Timestamp VARCHAR(24) NOT NULL,
Freq_value DOUBLE PRECISION NOT NULL
)
CREATE TABLE PMUVoltage
(
SignalID NCHAR(36) NOT NULL,
Timestamp VARCHAR(24) NOT NULL,
Volt_value DOUBLE PRECISION NOT NULL
)
CREATE TABLE PMUAngle
(
SignalID NCHAR(36) NOT NULL,
Timestamp VARCHAR(24) NOT NULL,
Angle_value DOUBLE PRECISION NOT NULL
)
tsm is part of a Common Table Expression. It’s like a temporary view that exists only for one query.
TBH, it’s not really my job to teach people how to use SQL, or to write SQL queries for them. I don’t mind helping you understand how to use our products, but I believe I’ve already given you enough information that you should be able to write the query yourself. So unfortunately, I’m going to have to refuse your request for a full SQL query. Instead, you can refer to the full query you referenced in your original post to assist you in writing your query.