How to Connect ClickHouse to Power BI in PostgreSQL

How do I connect ClickHouse to Power BI?

Connect ClickHouse to Power BI through the ClickHouse ODBC/Native connectors so you can visualize database tables and queries instantly.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why link ClickHouse and Power BI?

Joining ClickHouse’s ultra-fast analytics engine with Power BI’s rich visuals lets you build real-time dashboards without exporting data. You query ClickHouse directly and slice results inside Power BI.

What prerequisites must be in place?

• ClickHouse server reachable from your workstation
• Power BI Desktop (May 2022+)
• ClickHouse ODBC driver or the Native connector (preview)
• A read-only user in ClickHouse

How do I install the ClickHouse ODBC driver?

Download the appropriate 64-bit driver from ClickHouse docs, run the installer, and confirm the new DSN appears in Windows ODBC Administrator.

Which connection string options matter?

Set DSN, Host, Port, User, Password, and optional Database. For large result sets add MaxRowsToFetch=1000000.

How do I add ClickHouse as a data source in Power BI?

1. Open Power BI Desktop → Get Data.
2. Choose ODBC (or “ClickHouse (Beta)” if enabled).
3. Select your DSN or paste the Native connector URL.
4. Enter credentials, select DirectQuery to avoid importing data.
5. Navigate to the schema, tick tables or paste SQL.

How do I query "Customers" and related tables?

Use the Power Query editor or switch to the native query interface and paste SQL such as the example below. Click OK, then apply changes to load the model.

How can I optimize performance?

• Prefer DirectQuery so Power BI pushes filters to ClickHouse.
• Index high-cardinality columns with primary key and ORDER BY clauses.
• Limit columns and rows in your SQL—Power BI retrieves exactly what you request.

What security settings should I check?

Create a dedicated Power BI user in ClickHouse with SHOW and SELECT privileges only. Encrypt traffic with HTTPS or ClickHouse's built-in TLS by setting sslmode=require in the DSN.

Best practices recap

• Keep queries atomic and aggregate in ClickHouse.
• Name measures clearly to match business terms.
• Refresh visuals during off-peak hours when using Import mode.
• Monitor query logs to spot slow visuals.

Why How to Connect ClickHouse to Power BI in PostgreSQL is important

How to Connect ClickHouse to Power BI in PostgreSQL Example Usage


Power BI ➜ Home ➜ Get Data ➜ ODBC ➜ DSN = "ClickHouse_ecommerce"
Native SQL:
SELECT customer_id,
       COUNT(*)              AS orders,
       SUM(total_amount)     AS revenue
FROM Orders
GROUP BY customer_id;

How to Connect ClickHouse to Power BI in PostgreSQL Syntax


-- ODBC connection string example
Driver={ClickHouse ODBC Driver};
Host=clickhouse.myshop.local;Port=9440;Database=ecommerce;
User=power_bi_reader;Password=••••;
MaxRowsToFetch=1000000;sslmode=require;

-- Sample query Power BI can push to ClickHouse
SELECT c.id, c.name, c.email, o.id AS order_id,
       o.order_date, o.total_amount, p.name AS product_name,
       oi.quantity
FROM Customers AS c
JOIN Orders      AS o  ON o.customer_id = c.id
JOIN OrderItems  AS oi ON oi.order_id   = o.id
JOIN Products    AS p  ON p.id          = oi.product_id
WHERE o.order_date >= today() - 30
ORDER BY o.order_date DESC;

Common Mistakes

Using the Import mode by default. This copies large ClickHouse tables into Power BI, negating ClickHouse’s speed advantage. Fix: choose DirectQuery when adding the source.
Leaving default MaxRowsToFetch at 65 535. Power BI then truncates large result sets. Fix: raise MaxRowsToFetch (e.g., 1 000 000) in the DSN or connection string.

Frequently Asked Questions (FAQs)

Can I use the new Native connector instead of ODBC?

Yes. Enable the ClickHouse (Beta) connector under File → Options → Preview Features, then restart Power BI. Enter the full URL (e.g., https://clickhouse.myshop.local:8443) and credentials.