clickhouse how to do a complex query

[hpratap]

Hello,

I'm facing an issue with Power BI where I need to utilize a complex ClickHouse query. While I can successfully connect to individual tables using the ClickHouse connector (which allows direct connections), my requirement is to execute my own custom query that involves joining multiple tables with specific conditions and subqueries. However, it seems this feature isn't available in the current setup of Power BI's ClickHouse connector.

Could you please advise on how I can achieve this?

Thank you for your support!

[v-sgandrathi]

Hi @hpratap,

Thank you for bringing up this question.

@rohit1991  provided the correct answer, thank you for your response to the query.

At present, the native Power BI ClickHouse connector does not allow you to enter custom SQL queries directly, unlike some other database connectors. You are restricted to choosing from predefined tables or views.

The workarounds that have been suggested are currently the most effective solutions available.

For more advanced needs, you could consider developing a custom Power Query connector using the Power Query SDK, which can provide additional flexibility if you have the technical resources.

You can find more information in this Microsoft documentation:

Power Query ClickHouse connector - Power Query | Microsoft Learn

I hope this explanation helps clarify your concern. If you have any more questions, please feel free to ask.

[rohit1991 · Accepted solution]

Hi @hpratap ,

The current Power BI ClickHouse connector doesn’t support entering custom SQL queries directly like some other database connectors do. It only lets you import tables or views.

Workarounds:

• Create a View in ClickHouse: Write your complex SQL as a view on the ClickHouse server, then connect to that view from Power BI. This is the most reliable way.
  
  

• Use ODBC Connector: If your setup allows, try connecting via the ClickHouse ODBC driver, Power BI’s ODBC connector lets you paste in custom SQL queries.
  
  

• Dataflow or ETL: As an alternative, you could pre-process your data (with your query logic) upstream and then connect Power BI to the output.
  
  

Unfortunately, until Power BI adds native support for custom queries in the ClickHouse connector, these are your best options.