Any Working Solutions for Direct Query(or Real Time) from Power BI to Apache Doris? (有什麼辦法可以讓Power BI 即時讀取 Doris Table資料嗎?)

[artjio]

中文:

我目前在Doris 串接 Power BI上遇到問題,因為原本使用Oracle 資料庫，
有些資料表連線是Import(排程刷新) ,有些資料表則是用direct query(即時連線),
而目前要從Oracle 切換到 Doris時,移轉過程發現Power BI 對於My SQL的driver 的支援只有import,
資訊如連結: https://learn.microsoft.com/en-us/power-query/connectors/mysql-database,
而網路上有查到兩個可能方案
1.用mariaDB 的ODBC
參考Youtube上看到有人用mariaDB 的ODBC Driver 去連Mysql(ref:https://www.youtube.com/watch?v=EL3DdMVAUnQ)

結果:

(1)3.1.25的ODBC驅動，可以查詢到資料庫與資料表，但在撈資料時，會報exception
(2)3.2.25的ODBC驅動，則是連連入都沒辦法，相關資訊如附圖

2.使用spark(搭配spark-doris-connector)
因為看到Doris可以支援Spark connector，所以思考是否可以 Power BI -(Direct query)> Spark -> Doris 但實際建置後發現，發現效益與成本太差，因為需要建置 Spark + Apache Kyuubi (or spark thrift server)+ Mysql or Postgresql
因為需要讓Spark 跟Kyuubi 共用同個hive metastore，所以需要用mysql或postgreSQL，不能使用Derby(會被鎖住)，
透過crontab 排程，定期執行script 執行.scala，讓doris table 結果儲存成Spark的Table。

目前嘗試這兩種方案皆不可行，不知道是否有其他解決方案?
或是有其他mariaDB ODBC Driver 有人嘗試過是可以連的?

English:

I’m currently encountering issues while trying to connect Apache Doris to Power BI. Previously, our data source was Oracle, and in Power BI, some tables used the Import mode (refreshed on a schedule), while others used Direct Query for real-time access.

Now, during the transition from Oracle to Doris, I discovered that Power BI only supports Import mode when using the MySQL connector, as noted in the official documentation:
https://learn.microsoft.com/en-us/power-query/connectors/mysql-database

After researching online, I found two potential solutions:

1. Using MariaDB ODBC Driver
   I came across a YouTube video where someone used the MariaDB ODBC driver to connect to MySQL:
   Video Reference

Test Results:

ODBC Driver 3.1.25: Able to connect and list databases and tables, but querying data results in exceptions.

ODBC Driver 3.2.25: Fails to establish any connection at all. (See attached image for error details.)

2. Using Apache Spark (with spark-doris-connector)
   Since Doris supports a Spark connector, I explored a potential architecture:
   Power BI (Direct Query) → Spark → Doris

However, after setting it up, I found the cost and complexity too high:

Requires deploying Apache Spark and Apache Kyuubi (or Spark Thrift Server), plus MySQL or PostgreSQL as a shared Hive Metastore backend.

Derby cannot be used due to locking issues.

Crontab is used to schedule scripts to run .scala files, transforming Doris table results into Spark tables for Power BI to access.

Conclusion:

Both approaches have proven unfeasible so far.
Is there any alternative solution available?
Or has anyone successfully connected Power BI to Doris using a different version of the MariaDB ODBC Driver?