DirectQuery: Add Support for Ad-hoc Querying with Azure Data Studio

Related products: API and SDKs

I just gained access to the DirectQuery early access so was trying to switch over from the PostgreSQL Gateway to the official DirectQuery solution.  When experimenting with the types of queries I can include in my SaaS application code, I use Azure Data Studio to query Postgres.  

Through ADS, I always get an error with DirectQuery:

cursor "b07f4a07-c9db-4d5d-9f7c-c9adb01abaf7" does not exist

 

Even for simple queries like this:

select  "timestamp"
from public.datapoint_aggregates
WHERE timeseries_external_id = '419b9b45-8e09-48a9-9912-08da820807fc'
AND "timestamp" > '2022-08-23T00:00:00'
AND "timestamp" < '2050-08-23T00:00:00'

This is likely caused by something the client is doing because using this same login, I am able to run the example PowerBI queries.

Hi Ben,

this is most likely happening because DirectQuery is lacking support for DB cursors.
Those are not required for PowerBi that’s why the feature wasn’t prioritized so far.

Any chance that you could turn of the cursors in the client?

Hi @ipolomanyi,

Azure Data Studio does not have an option in its Postgres plugin to disable cursors, but it is far from the most popular graphical interface for querying Postgres.  I wasn’t able to query with pgAdmin either, but I will try some other tools and see if there’s one that can do simple querying without cursors.  

Thanks!

@Ben Brandt hmm we have tested it with pgAdmin, what version doo you have?

 

pgAdmin 4 v6.12.

 

Here are the settings I use in the connection.

Host name/address: bi.az-eastus-1.cogniteapp.com

Port: 5432

Maintenance database: {my CDF project name.  Using “postgres” here gives an error.}

Username: {My email used for DirectQuery}

SSL Mode: Prefer

 

It logs in and shows me a dashboard with server metrics and activity.  But the “Databases” node in the browser will never show a DB and I am not allowed to open any type of SQL Query Tool.  

 

Could just be me never using pgAdmin before.

 

@Ben Brandt I tried that version and I experience the same issue, would it be possible for you to downgrade to v5.4?

This is the one I tested and it seemed to work:
https://pgadmin-archive.postgresql.org/pgadmin4/v5.7/index.html

If it’s something that you find useful we can the see what we should improve to support newer versions.

Thanks for the reply.  I am not needing pgAdmin to work right now.  I was mostly testing it out of curiosity to discover DirectQuery’s capabilities outside of PowerBI usage.

I am using the psql commandline now which does not require cursor functionality.  I am not getting any rows back from my query, but can’t figure out why.  Am I using `starts_with` correctly here?

 

pinnacle-dev=> select  "timestamp", "average"  from public.datapoint_aggregates WHERE starts_with(timeseries_external_id,'7f9109d2-cae7-4aba-3f7a-08da85138eb7') AND "timestamp" > '2020-08-23T00:00:00' AND "timestamp" < '2030-08-23T00:00:00';
 timestamp | average
-----------+---------
(0 rows)

I see the time series in CDF:

 

Figured it out.  The PowerQuery StartsWith gets converted to LIKE ‘{search string}%’ so this is what you’ve implemented to translate to the API call.  This is not supported for datapoint_aggregates, but is supported for timeseries as documented here:

Capabilities and limitations | Cognite Documentation

 

This query returns the result I expect:

select  * from public.timeseries WHERE external_id LIKE 'ecc86a5d7eee491942ee08da87bc39bbHsePoFMi%';

 

This is what a working datapoint_aggregates SQL looks like:

select  timeseries_external_id, "timestamp", "average", "count"  from public.datapoint_aggregates WHERE timeseries_external_id IN ('some external id', 'another external id') AND "timestamp" > '2020-08-23T00:00:00' AND "timestamp" < '2030-08-23T00:00:00';

 

@Ben Brandt exactly! We don’t support cross-table queries, apart from some exceptions.
Very good reverse engineering on your side :)

Parked→Gathering Interest
Terms & ConditionsCookie Settings