How to achive clickhouse connection with direct query?

[tomtan]

Hi Experts,

My requirement is that I have a clickhouse with lots of data, and I want to leverage PBI to connect to it to do direct query.

I did check lots of the web information on the solution. Finnally what I learned is that we could use clickhouse odbc for the connection. Howerver, due to the quantity of data, it is impossible to full load the table and maybe I need to enable direct query for it.

The challenge is that the default odbc connector doesn't provide direct query option. I also checked the web and it seems that the solution is to develop our own connector leverage Forks · microsoft/DataConnectors · GitHub. 

Question:

1. What is the suggest way for PBI to connect to clickhouse and enable direct query?

2. Is there any shared clickhouse connector for PBI to support direct query?

Thanks.

[belyaevvo]

You can use power bi custom connector like this https://github.com/belyaevvo/ClickHouseConnector

[amitchandak]

@tomtan , How much is data volume, Are you on the premium version? Make sure you load data in service using the deployment pipeline, you should use a large dataset format in the premium  workspace

Power BI Incremental Refresh Pro; Premium Deployment pipeline - https://youtu.be/nIxTRdeCYSE

[tomtan]

Thanks. We have data with each day 30M+ rows and we need to cover 1 year's data. So total rows would be 10B. We don't yet use premium version but if it works then we could swich to it. But as I know the load mode may not be able to support such volume data, and thus maybe we need direct query instead.