# Enable Canal Engine

> Enable Canal - Fast Connector and Caching Engine - for your Data Connection

:::tip Knowledge Checkpoint
To learn about **Holistics Canal**, please head to this [doc](/docs/performance/canal-engine).
:::

## Supported databases

  
    
      Database
      Status
      Supported version
      Connection pooling
      Parallel download
    
  
  
    
      PostgreSQL
      ✅ Stable
      PostgreSQL >= 12 (not end-of-life)
      ✅ Yes
      ❌ No
    
    
      Redshift
      ✅ Stable
      
      ✅ Yes
      ❌ No
    
    
      BigQuery
      ✅ Stable
      
      ✅ Yes
      ❌ No
    
    
      Snowflake
      ✅ Stable
      
      ✅ Yes
      ✅ Yes
    
    
      MySQL
      ✅ Stable
      MySQL 8.0, 8.4, 9.1 (not end-of-life)
      ✅ Yes
      ❌ No
    
    
      Amazon Athena
      ✅ Stable
      
      ✅ Yes
      ❌ No
    
    
      Microsoft SQL Server
      ✅ Stable
      SQL Server 2016 or later (not end-of-life)
      ✅ Yes
      ❌ No
    
    
      ClickHouse
      ✅ Stable
      See Supported Versions
      ✅ Yes
      ❌ No
    
    
      Databricks
      ✅ Stable
      
      ✅ Yes
      ✅ Yes
    
    
      MotherDuck
      🆕 Beta
      
      ✅ Yes
      ❌ No
    
    
      Presto
      🆕 Beta
      
      ✅ Yes
      ❌ No
    
    
      Trino
      🆕 Beta
      
      ✅ Yes
      ❌ No
    
    
      Oracle
      🆕 Beta
      Oracle >= 19c (not end-of-life)
      ✅ Yes
      ❌ No
    
  

**Connection pooling** means Holistics will try to re-use the same connection for multiple queries. [Learn more](/docs/performance/canal-engine#connection-pooling)

**Parallel download** allows some databases to download query results in multiple parts simultaneously. This typically only makes a difference when your result set has more than 5,000 rows.

## How to enable Holistics Canal

:::tip Enabled by default
Holistics Canal is enabled by default on **stable** databases.  
Check out [Supported Databases](#supported-databases) to learn more about database support status.
:::

In your Data Source settings, open **Advanced Holistics Settings**, check **Enable Holistics Canal** (and then **Save** it):

If you find any issues (typically with "Canal" in the error message), please reach out to Holistics Support.  
In case it's urgent, please
1. Make sure to screenshot the error first
2. Disable Canal on your Data Source
3. Report the error (with screenshot) to Holistics Support

## Performance expectations

Canal typically speeds up **Steps 4 and 5** of Holistics [Report/Query Execution](/docs/performance/report-running-mechanism).
* It reduces the time between when **the query is finished** and the time **the result is delivered to the end-users**. In other words, it reduces Holistics **latency** in query executions.
* It does **not** make the Data Warehouse execute a query faster.
  * To optimize queries, please refer to this [doc](/docs/performance/troubleshooting#my-query-takes-too-long-to-run-on-the-data-warehouse).

Below is an example comparison taken from our [Community Post](https://community.holistics.io/t/upcoming-holistics-canal-fast-connector-and-caching-system/2132#benchmarks-15).
Note that the query itself takes < 300ms to run in the database.

## Database-specific notes

**PostgreSQL and Redshift**
* Holistics Canal might still work with PostgreSQL < 12, but it is not guaranteed because PostgreSQL versions below 12 are end-of-life ([ref](https://www.postgresql.org/support/versioning/)).
* See [Number precision](#number-precision) below.

**BigQuery**
* When using Holistics Canal, your BigQuery queries on Holistics will always use [**GoogleSQL**](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax) (formerly known as **StandardSQL**) instead of BigQuery's [legacy syntax](https://cloud.google.com/bigquery/docs/reference/legacy-sql).
* See [Number precision](#number-precision) below.

**MySQL**
* Holistics Canal might still work with older versions like MySQL 5.7, but it is not guaranteed because those versions are end-of-life ([ref](https://endoflife.date/mysql)).
    * For example, MySQL 5.7 may typically have TLS/SSL issues.
* See [Number precision](#number-precision) below.

**SQL Server**
* Holistics Canal might still work with older versions like SQL Server 2014, but it is not guaranteed because those versions are end-of-life ([ref](https://learn.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16#lifecycle-dates))

**ClickHouse**
* Holistics Canal connects to ClickHouse using its **native protocol**, which is accessible at **port 9000** (without SSL/TLS) or **port 9440** (with SSL/TLS) [by default](https://clickhouse.com/docs/en/guides/sre/network-ports).
* Holistics Canal does **not** support using [`FORMAT` clause](https://clickhouse.com/docs/en/sql-reference/statements/select/format). Using `FORMAT` clause in your queries would result in errors.

**MotherDuck**
* MotherDuck Data Sources **always** use Canal.
* Unsupported data types: UNION, BIT, VARINT, UHUGEINT
* See [Number precision](#number-precision) below.

**Databricks**
* Holistics uses [Databricks Cloud Fetch](https://www.databricks.com/blog/2021/08/11/how-we-achieved-high-bandwidth-connectivity-with-bi-tools.html) to enable high-bandwidth data transfer (aka. the _Parallel Download_ feature).

**Other databases**

If you are using another database that is [supported by Holistics](/docs/connect/databases-supported) but not yet supported by Holistics Canal, please let us know via this [form](https://form.jotform.com/230511857392457/prefill/66fcc4dc303765e102d717984267).

## Data type handling

Canal aims to preserve data precision while keeping it readable and efficient. Here are some special considerations:

### Number precision
* **Floats**: Canal supports **upto 64-bit** floating-point numbers.
* **Decimals**: For the "decimal" or "numeric" data types that have a fixed/arbitrary precision (non-floating precision), Canal supports **upto decimal(38, 9)**.
  * For **PostgreSQL** and **Redshift**:
    * This means that a decimal number can only have upto 38 digits, including 29 whole digits and 9 fractional digits.
      * Numbers that have more than 29 whole digits _will raise an error_ in Canal.
      * Numbers that have more than 9 fractional digits _will be rounded_ to 9 fractional digits.
  * For **BigQuery**:
    * `BIGDECIMAL` and `BIGNUMERIC` data are _cached_ and _post-processed_ as `double` (aka. `float64`), meaning it can still be computed on your Data Warehouse and displayed to end-users, but with floating-point precision instead of fixed precision.
  * For **MySQL** and **ClickHouse**:
    * **Decimal/Numeric** data with precision **> 38** is _cached_ and _post-processed_ as `double` (aka. `float64`), meaning it can still be computed on your Data Warehouse and displayed to end-users, but with floating-point precision instead of fixed precision.
  * For **Oracle**:
    * **NUMBER without a specified precision** is _cached_ and _post-processed_ as `NUMBER(38, 9)`
  * Workarounds for decimals that are larger than `decimal(38, 9)`:
    * Truncate/round to make them smaller: For example, instead of processing in meters, convert your data into kilometers. Canal only operates on the _result_ of your data, so you can still perform big computations first, then convert the result to smaller numbers later before returning the query result.
    * Convert into `text` -> cannot apply numeric calculations but can still display them.
    * Convert into `double` (i.e. 64-bit float) -> lose fixed precision but can still display and apply numeric calculations with floating-point precision.
* For **MotherDuck**:
  * **HUGEINT** data is _cached_ and _post-processed_ as `text`, meaning it can still be computed on your Data Warehouse and displayed to end-users, but Holistics won't be able to apply visual-level calculations to it.

### Time precision

Holistics only processes Time and Timestamp/Datetime values up to **microsecond** precision. Values that have higher precisions will be rounded to microsecond precision.  

We believe that microsecond precision is adequately _precise_ and _efficient_ for most analytical use cases. But please let us know if it does not suit your needs.