To direct queries to a specific cluster, set the cluster at the role level using the following SQL statement:
ALTER ROLE <your_user> SET CLUSTER = <custom_cluster>;
Replace <your_user> with the name of your Materialize role and <custom_cluster> with the name of the cluster you want to use.
Once set, all new sessions for that user will automatically run in the specified cluster, eliminating the need to manually specify it in each query or connection.
## Execute and visualize a query 1. Create a new SQL cell. 2. Inside the cell, select the new **Materialize** connection and paste the following query: ```mzsql SELECT number, row_num FROM ( SELECT power(series_number, 2) AS number, row_number() OVER (ORDER BY series_number ASC, series_number DESC) AS row_num FROM ( SELECT generate_series(0, 1000) AS series_number ) AS subquery ); ``` This query generates a series of 1000 numbers squared and assigns row numbers to each. 3. Click the **Run** button. 4. Inside the cell, click the **Chart** button and configure as follows: 1. In the **X Axis** options, select the **row_num** column. 2. In the **Y Axis** options, select the **number** column.To direct queries to a specific cluster, set the cluster at the role level using the following SQL statement:
ALTER ROLE <your_user> SET CLUSTER = <custom_cluster>;
Replace <your_user> with the name of your Materialize role and <custom_cluster> with the name of the cluster you want to use.
Once set, all new sessions for that user will automatically run in the specified cluster, eliminating the need to manually specify it in each query or connection.
## Known limitations When using Looker with Materialize, be aware of the following limitations: 1. **Connection Test Error**: You might encounter this error when testing the connection to Materialize from Looker: ``` Test kill: Cannot cancel queries: Query could not be found in database. ``` This error occurs because Looker attempts to run a test query cancellation, which checks for `pg_stat_activity` (not currently supported in Materialize). While this error can be safely ignored and doesn't impact most Looker functionality, there are workarounds for query cancellation if you need to stop a running query: a. Use `pg_cancel_backend` in Materialize: ```sql SELECT pg_cancel_backend(connection_id) FROM mz_sessions WHERE id = 'your_session_id'; ``` b. Via the Materialize Console: - Go to [Materialize Console](/console/) - Navigate to Query History - Filter by 'Running' queries - Click on the query you want to cancel - Select "Request Cancellation" 2. **Symmetric Aggregates**: Looker uses symmetric aggregates, which rely on types and operations not fully supported in Materialize: - Materialize doesn't support the `BIT` type used by Looker for symmetric aggregates. - Looker may use various aggregations (e.g., `SUM DISTINCT`, `AVG DISTINCT`) for symmetric aggregates. - You can disable symmetric aggregates in Looker if needed. For instructions, refer to the [Looker documentation on disabling symmetric aggregates](https://cloud.google.com/looker/docs/reference/param-explore-symmetric-aggregates#not_all_database_dialects_support_median_and_percentile_measure_types_with_symmetric_aggregates). - Looker is fully functional for non-symmetric aggregations when visualizing Materialize data. 3. **Handling Symmetric Aggregates**: a. Test query performance with and without symmetric aggregates to determine the optimal configuration. b. If you encounter performance issues, disable symmetric aggregates in your Looker setup using the link provided above. c. For use cases requiring symmetric aggregates, contact Materialize support for optimization guidance. --- ## Metabase You can use [Metabase](https://www.metabase.com/) to create real-time dashboards based on the data maintained in Materialize. ## Database connection details To set up a connection from Metabase to Materialize, use the native [PostgreSQL database driver](https://www.metabase.com/docs/latest/administration-guide/databases/postgresql.html) with the following parameters: Field | Value ----------------- | ---------------- Database type | **PostgreSQL** Host | Materialize host name. Port | **6875** Database name | **materialize** Database username | Materialize user. Database password | App-specific password. SSL mode | Require For more details and troubleshooting, check the [Metabase documentation](https://www.metabase.com/docs/latest/administration-guide/databases/postgresql.html). ## Configure a custom clusterTo direct queries to a specific cluster, set the cluster at the role level using the following SQL statement:
ALTER ROLE <your_user> SET CLUSTER = <custom_cluster>;
Replace <your_user> with the name of your Materialize role and <custom_cluster> with the name of the cluster you want to use.
Once set, all new sessions for that user will automatically run in the specified cluster, eliminating the need to manually specify it in each query or connection.
## Refresh rate By default, the lowest [refresh rate](https://www.metabase.com/docs/latest/users-guide/07-dashboards.html#auto-refresh) for Metabase dashboards is 1 minute. You can manually set this to a lower interval by adding `#refresh=1` (as an example, for a `1` second interval) to the end of the URL, and opening the modified URL in a new tab. Because Metabase queries are simply reading data out of self-updating views in Materialize, setting your dashboards to auto-refresh at lower rates should not have a significant impact on database performance. To minimize this impact, we recommend carefully choosing an [indexing strategy](/sql/create-index/) for any objects serving results to Metabase. [//]: # "TODO(morsapaes) Once we revamp quickstarts, add Related pages section pointing to a quickstart that uses Metabase" --- ## Power BI You can use [Power BI](https://powerbi.microsoft.com/) to create dashboards based on the data maintained in Materialize. ## Database connection details To set up a connection from Power BI to Materialize, use the native [PostgreSQL database driver](https://learn.microsoft.com/en-us/power-query/connectors/postgresql#connect-to-a-postgresql-database-from-power-query-desktop) with the following parameters: Field | Value ---------------------- | ---------------- Database type | **PostgreSQL database** Server | Your Materialize host name followed by `:6875`For example: `id.us-east-1.aws.materialize.cloud:6875` Database | **materialize** Data Connectivity mode | **DirectQuery** Database username | Materialize user. Database password | App-specific password.  ## Configure a custom cluster
To direct queries to a specific cluster, set the cluster at the role level using the following SQL statement:
ALTER ROLE <your_user> SET CLUSTER = <custom_cluster>;
Replace <your_user> with the name of your Materialize role and <custom_cluster> with the name of the cluster you want to use.
Once set, all new sessions for that user will automatically run in the specified cluster, eliminating the need to manually specify it in each query or connection.
## Troubleshooting Errors like the following indicate that there is a problem with the connection settings: 1. `A non-recoverable error happened during a database lookup.` If you see this error, check that the server name is correct and that you are using port `6875`. Note that the server name should not include the protocol (`http://` or `https://`), and should not include a trailing slash (`/`) or the database name. 2. `PostgreSQL: No password has been provided but the backend requires one (in cleartext)` If you see this error, check that you have entered the correct password. If you are using an app-specific password, make sure that you have not included any spaces or other characters that are not part of the password. If the issue persists, try the following: - Go to **File** - Options and settings - Data source settings - Delete any references for the Materialize host from the data source list - Try to connect again For more details and troubleshooting, check the [Power BI documentation](https://learn.microsoft.com/en-us/power-query/connectors/postgresql#troubleshooting). ## Known limitations When you connect to Materialize from Power BI, you will get a list of your tables and views. However, [Power BI does not display materialized views](https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=92420736-afdc-45b9-8962-743a53acfa66) in that list. To work around this Power BI limitation, you can use one of the following options: 1. Create a view that selects from the materialized view, and then use the view in Power BI. For example, if you have a materialized view called `my_view`, you can create a view called `my_view_bi` with the following SQL: ```mzsql CREATE VIEW my_view_bi AS SELECT * FROM my_view; ``` Then, in Power BI, you can use the `my_view_bi` view. 2. If applicable, instead of using a materialized view, create a view with an [index](/sql/create-index) instead. 3. Use the [Power BI Native query folding](https://learn.microsoft.com/en-us/power-query/connectors/postgresql#native-query-folding) to write your own query rather than using the Power BI UI. For example: ``` = Value.NativeQuery(Source, "select * from my_view;") ``` --- ## Tableau You can use [Tableau Cloud](https://www.tableau.com/products/cloud-bi) and [Tableau Desktop](https://www.tableau.com/products/desktop) to create real-time dashboards based on the data maintained in Materialize. ## Tableau Cloud ### Database connection details To set up a connection from Tableau Cloud to Materialize, use the native [PostgreSQL database connector](https://help.tableau.com/current/pro/desktop/en-us/examples_postgresql.htm) with the following parameters: Field | Value ----------------- | ---------------- Server | Materialize host name. Port | **6875** Database | **materialize** Username | Materialize user. Password | App-specific password. Require SSL | ✓ For more details and troubleshooting, check the [Tableau documentation](https://help.tableau.com/current/pro/desktop/en-us/examples_postgresql.htm). [//]: # "TODO(morsapaes) Clarify minimum refresh rate and details about live connections" ## Tableau Desktop ### Setup **macOS:** To set up a connection from Tableau Desktop to Materialize, you must: 1. Download the [Java 8 JDBC driver for PostgreSQL](https://jdbc.postgresql.org/download/) 1. Copy the `.jar` file to the following directory (which may have to be created manually): `~/Library/Tableau/Drivers` **Linux:** To set up a connection from Tableau Desktop to Materialize, you must: 1. Download the [Java 8 JDBC driver for PostgreSQL](https://jdbc.postgresql.org/download/) 1. Copy the `.jar` file to the following directory (which may have to be created manually): `/opt/tableau/tableau_driver/jdbc` **Windows:** To set up a connection from Tableau Desktop to Materialize, you must: 1. Download the [Java 8 JDBC driver for PostgreSQL](https://jdbc.postgresql.org/download/) 1. Copy the `.jar` file to the following directory (which may have to be created manually): `C:\Program Files\Tableau\Drivers` ### Database connection details Once you've set up the required driver, start Tableau and run through the following steps: 1. On the left side, find the **Connect to a Server** section 1. Select **More** and then **PostgreSQL** 1. Use the following details to configure the connection: Field | Value -------------- | ---------------------- Server | Materialize host name. Port | **6875** Database | **materialize** Authentication | Username and Password Username | Materialize user. Password | App-specific password. Require SSL | ✓ 4. Click **Sign In** to connect to Materialize For more details and troubleshooting, check the [Tableau documentation](https://help.tableau.com/current/pro/desktop/en-us/examples_postgresql.htm). [//]: # "TODO(morsapaes) Clarify minimum refresh rate and details about live connections" ## Configure a custom clusterTo direct queries to a specific cluster, set the cluster at the role level using the following SQL statement:
ALTER ROLE <your_user> SET CLUSTER = <custom_cluster>;
Replace <your_user> with the name of your Materialize role and <custom_cluster> with the name of the cluster you want to use.
Once set, all new sessions for that user will automatically run in the specified cluster, eliminating the need to manually specify it in each query or connection.
### Troubleshooting Errors like the following indicate that the JDBC driver was not successfully installed. ``` ERROR: Expected FOR, found WITH; Error while executing the query ``` ``` ERROR: WITH HOLD is unsupported for cursors; Error while executing the query ``` The errors occur because Tableau falls back to a legacy PostgreSQL ODBC driver that does not support connecting to Materialize. Follow the [Setup](#setup) instructions again and ensure you've downloaded the driver to the correct folder for your platform.