(question) Power BI connector

[harlequin]

(Question)
We want to use Trino directly with Power BI.

I have seen that there are existing commercial products.

Is there any other way to connect to Power BI and be able to perform direct query.

Or is there a Plan to create an open source ODBC Driver?

[tooptoop4]

have u tried https://github.com/openlookeng/hetu-odbc-driver ? "this driver can be connected to other versions (such as prestosql and prestodb) based on presto evolution through configuration, so you can alse use it as a presto odbc driver."

[harlequin]

thanks for your suggestions

my further investigations brought that the standard ODBC Connector from Power BI doesn't support "Direct Query" in the cloud.

Out of this I'm thinking to have a "thrift" server which implements the "hive2" interface and forward the requests directly to Trino.

So like a gatway on pure Java level.

Another option would be that Trino it self provides a thrift hive2 compatible endpoint ...

[buremba]

@harlequin after some research, it looks like ODBC seems to support Direct Query. but it needs to be enabled explicitly switching the direct mode in the connection screen. Hive also makes use of ODBC connector. Did you start working on The proxy Hive metastore for Trino or would you like to contribute?

[harlequin]

Hey,

I have started to get the source from Apache Spark and take only the thrift server component.

I have started to replace the "thrift" functions with JDBC commands to trino.

Authentication and query execution is working, BUT I have problem to forward the Dataset to PowerBI.

Another big problem, from my point, ..... really "hackish" ;)

If I have a more clean code I will distribute for sure

[buremba]

Hi @harlequin, I agree with you as it's being too hackish. :)

I spent some time on it and I believe that the most straightforward way would be through the ODBC driver as PowerBI has native integration with ODBC.

1. Hetu seems to a fork of Presto and they have an ODBC driver.
2. Elasticsearch (has a similar approach to Trino) also has an ODBC driver here: https://github.com/opendistro-for-elasticsearch/sql/tree/main/sql-odbc
3. The commercial solutions such as Starburst, Qubole, and Teradata seem to work with a third part company called Magnitude to be able to provide an ODBC driver for their customers but it's not available for the community version of Trino.

Given that the commercial solutions of Trino also offer an ODBC driver rather than making Trino talk like Hive, I believe that's the correct approach. It looks like already solved the issue so if we can somehow fork their ODBC driver or fork the Mariadb's ODBC driver and make it talk to Trino, that would be the ideal way.

What do you think? I would love to collaborate if you're interested. @findepi not sure you're the right person but since you're an active contributor from Starburst, I would love to hear your opinion.

[harlequin]

When you are going with the native ODBC implementation you don't have the Direct Query Modus directly available.

At this point you have also to develop a Power BI Connector which handles the ODBC as direct query source.

I have seen that on Microsoft there is a data source called "HD Insight" which is the same like Apache Spark Thrift just rebranded.

This data source supports direct query and also "cloud usage".

The main problem is that our IT is very restrictive in direction PowerBI and Data Gateways (ODBC driver installation, custom connector etc.).

[buremba]

Ah, I see. The custom connector option seems to be straightforward but if you have such limitation, that makes sense. Do you have any plans to open-source the bridge?

[FleischerT]

Hi,
is there any news in this regard?

[jbguerraz]

Came here for that as well 👍
Looks like most of databases OSS projects provide their own implementations of ODBC driver.
AWS produced a full featured (incl. power bi connector) project here https://github.com/opensearch-project/sql-odbc/tree/main which could be a starting point (sadly, looks like, they didn't open-source their Athena ODBC driver...).
Would love to help with it!