Cognite Data Fusion: Allow PostgreSQL Gateway Datapoints Query By Timeseries ID

Hi Ben, 

Yes, “id” of a timeseries is needed. I have created a dev ticket to investigate the error and provide a fix. :)

I would encourage you to consider using explore data or transformations previews for querying as Postgres Gateway is intended only for ingestion. Though querying is possible we don’t officially support it. 

https://docs.cognite.com/cdf/integration/guides/interfaces/postgres_gateway#when-should-you-use-the-postgresql-gateway
 

Best regards,

Sunil

Thank you for the input @Sunil Krishnamoorthy.  Is there a server-side .NET SDK available to run ad-hoc SQL for the “explore data” and “transformations preview” that you have suggested?  

@Ben Brandt you can run ad hoc SQL using Cognite’s .NET SDK and the Python SDK 

https://cognite-sdk-python.readthedocs-hosted.com/en/latest/cognite.html#preview-transformations

https://github.com/cognitedata/cognite-sdk-dotnet/blob/e4e991a123c1732fd4220ba3294793e6bf69c611/CogniteSdk/src/Resources/Transformations.cs#L357

Python SDK is our gold standard and very well supported. .NET is stable as well but the documentation needs to be improved. :) 

Nice!  I had no idea transformation preview was in the SDK’s. Thanks for the help.

Any way to parameterize these transformation SQL?  As a 3rd party app dev, we sometimes have a need to segment data and handle is separately, but would like to re-use the same transformation SQL.  In specific case of this would be data for different customers we store in the same CDF project but segment by Data Set Id.  We would like to define a transformation and pass in a DataSetId parameter to run the transformation for a specific dataset and then at a different time run the same transformation with a different DataSetId.  

The workaround right now is we can internally manage a set of transformation template SQL and create copies with the parameters substituted in for each context, DataSetId in the example, that we’d like to handle.  But this adds a bit of management overhead as new DataSets are added or removed.

If CDF were my product, I’d categorize this as an unimplemented feature, but since I’m just a user I’ll call this a Bug  and report it.
 

Hi Ben,

There is a bug that Set command is not allowed when validating the query. Meanwhile before we fix it, can you try something like below as a alternative solution?

WITH AllMyVars as ( select 123 as myVar )
select myVar as key from AllMyVars