> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/maxscale/reference/maxscale-routers/maxscale-exasolrouter.md). # MaxScale Exasolrouter {% hint style="info" %} This functionality is available from MaxScale 25.10.1. {% endhint %} ## Overview *ExasolRouter* is a router that in itself is capable of using the [Exasol Analytics Engine](https://www.exasol.com). It is primarily intended to be used together with [SmartRouter](/docs/maxscale/reference/maxscale-routers/maxscale-smartrouter.md), with *writes* being directed to a regular MariaDB cluster and *reads* to either MariaDB or Exasol, depending on which one can provide the response faster. Unlike the other routers of MaxScale, the targets *ExasolRouter* routes to are not specified using `servers`, `targets`, or `cluster` settings in the configuration file. Instead, Exasol is specified using the [connection\_string](#connection_string) setting. However, if *ExasolRouter* is used standalone, a MariaDB server or a service should be specified using `targets`. *ExasolRouter* will not route to it, but it will use it for authenticating clients. Exasol will still be accessed on behalf of all clients using the credentials specified in the [connection\_string](#connection_string). *ExasolRouter* is intended to be used with the value of the Exasol setting `SQL_IDENTIFIER_COMPARISON` being `IGNORE CASE`. The value will be checked and a warning issued if the value is something else. ## Users A `user` and `password` must *always* be specified, but will only be used if a MariaDB server/service has been specified as a target, and only for authenticating a client. If that functionality is not needed, e.g. when *ExasolRouter* is used with *SmartRouter*, they can be left empty, but must still be present. ``` user= password= ``` The user and password to be used when accessing Exasol must be specified using `UID` and `PWD` in the [connection\_string](#connection_string). ## Preprocessing The SQL supported by Exasol is not identical with the SQL supported by MariaDB. To alleviate that, *ExasolRouter* can either preprocess the SQL internally or configure Exasol to do that. The approach is selected using the setting [preprocessor](#preprocessor). ## Settings ### `connection_string` * Type: string * Mandatory: Yes * Dynamic: No Specifies the Exasol connection string. The exact content depends on the contents of `odbc.ini` and `odbcinst.ini`. For example: ``` connection_string=DSN=ExasolDSN;UID=sys;PWD=exasol;FINGERPRINT=NOCERTCHECK ``` Here it is assumed there is an `odbc.ini` ODBC configuration file containing an `ExasolDSN` entry. ### `login_timeout` * Type: [duration](/docs/maxscale/maxscale-management/deployment/installation-and-configuration/maxscale-configuration-guide.md#durations) * Mandatory: No * Dynamic: No * Default: 30s Defines the login timeout in seconds for a connection. Corresponds to the Exasol connection string key `LOGINTIMEOUT`. If the value is set to 0, the timeout will not explicitly be set, which means that a `LOGINTIMEOUT` in the connection string will be honored. Otherwise this value will override. ### `query_timeout` * Type: [duration](/docs/maxscale/maxscale-management/deployment/installation-and-configuration/maxscale-configuration-guide.md#durations) * Mandatory: No * Dynamic: No * Default: 30s Defines the query timeout in seconds for a connection. Corresponds to the Exasol connection string key `QUERYTIMEOUT`. If the value is set to 0, the timeout will not explicitly be set, which means that a `QUERYTIMEOUT` in the connection string will be honored. Otherwise this value will override. ### `preprocessor` * Type: string * Mandatory: No * Dynamic: No * Values: `disabled`, `external[:preprocessor-script-name]`, `internal[:preprocessor-script-path]` * Default: `internal` The values mean: * `disabled`: The SQL is not transpiled internally and the external transpiler is not enabled. *ExasolRouter* itself performs some essential transpiling, to make it possible to use the `mariadb` command line tool against Exasol. * `external`: The preprocessor script is assumed to exist in Exasol and is taken into use in each session. By default, the preprocessor script is assumed to be `UTIL.maria_preprocessor`, but that can be changed by giving the name as an argument. If `external` is specified, the following statement will be executed for each session: `ALTER SESSION SET sql_preprocessor_script=`, with `` being by default `UTIL.maria_preprocessor` or what was explicitly specified. * `internal`: Transpiling is performed internally using a Python script that utilizes the library [SQLGlot](https://sqlglot.com/sqlglot.html). By default, the script is assumed to be `maria_preprocessor.py` located in the *share* directory of MaxScale. A different script can be used by providing it as an argument. Unless an absolute path is used, it is interpreted relative to the *share* directory. ### `python_libdir` * Type: path * Mandatory: No * Dynamic: No * Default: `/python3/site-packages` The library from which [SQLGlot](https://sqlglot.com/sqlglot.html) is loaded, if the value of `preprocessor` is `internal`. ### `appearance` * Type: [enum](/docs/maxscale/maxscale-management/deployment/installation-and-configuration/maxscale-configuration-guide.md#enumerations) * Mandatory: No * Dynamic: No * Values: `read_only`, `read_write` * Default: `read_only` Specifies how *ExasolRouter* appears to other components of MaxScale. This is of relevance only if another service uses an Exasol router service as target. **Note** Irrespective of the value, the router does not in any way restrict what kind of queries can be run through the router. ### `kill_connection_timeout` * Type: [duration](/docs/maxscale/maxscale-management/deployment/installation-and-configuration/maxscale-configuration-guide.md#durations) * Mandatory: No * Dynamic: No * Default: 10s The duration that an unused kill connection is kept alive after the last KILL command before it is automatically closed; a value of 0 closes it immediately after each use. ### `quote_identifiers` * Type: [boolean](/docs/maxscale/maxscale-management/deployment/installation-and-configuration/maxscale-configuration-guide.md#booleans) * Mandatory: No * Dynamic: No * Default: true Whether *ExasolRouter* should quote identifiers. This applies always when a `COM_INIT_DB` packet is converted to an `OPEN SCHEMA ...` statement and when the value of `preprocessor` is `disabled`. In the latter case, currently it only affects whether 'USE db' becomes 'OPEN SCHEMA db' or 'OPEN SCHEMA "db"'. ## Transformations ### `COM_INIT_DB` The MariaDB `COM\_INIT\_DB` protocol packet, using which the default database is changed, is transformed into the statement `OPEN SCHEMA `. ### SQL If the value of `preprocessor` is `disabled`, *ExasolRouter* itself translates some MariaDB constructs to equivalent Exasol constructs. Currently a transformation will be made *only* if there is an **exact** match (apart from case and differences in whitespace) with the MariaDb SQL. | MariaDb | Exasol | | ----------------------------------- | --------------------------------------------------------------------------------------------------------------------- | | `SELECT @@VERSION\_COMMENT LIMIT 1` | `SELECT 'Exasol' AS '@@version\_comment' LIMIT 1` | | `SELECT DATABASE()` | `SELECT TABLE\_NAME AS 'Database()' FROM EXA\_ALL\_TABLES WHERE TABLE\_SCHEMA = CURRENT\_SCHEMA` | | `SHOW DATABASES` | `SELECT SCHEMA\_NAME AS 'Database' FROM EXA\_SCHEMAS ORDER BY SCHEMA\_NAME` | | `SHOW TABLES` | `SELECT TABLE\_NAME AS 'Tables' FROM SYS.EXA\_ALL\_TABLES WHERE TABLE\_SCHEMA = CURRENT\_SCHEMA ORDER BY TABLE\_NAME` | ## ODBC *ExasolRouter* communicates with Exasol using ODBC. The necessary ODBC driver is included in the `maxscale-exasol` package and the files are copied to ``` /exasol/M.N.O/libexaodbc.so /exasol/M.N.O/libexacli.so ``` where `M.N.O` is the version of the ODBC library, e.g. `26.2.6`. `libexaodbc.so` is the actual ODBC driver and `libexacli.so` the lower level library using which the ODBC driver is implemented. In the `exasol` directory there is a symbolic link `current` pointing to the `M.N.O` directory. E.g. ``` /exasol/current -> 26.2.6 ``` The included driver version may change in each release of MaxScale. Hence, by referring to `current`, a MaxScale configuration will continue to work even if the included Exasol ODBC driver version would change. If it is important to become aware of a changed driver version, the driver can be referred to using the directory and not the symbolic link, as that will no longer work, if the included Exasol driver is updated in a MaxScale patch release. In [connection\_string](#connection_string), the driver can be referred to directly as in ``` connection_string=DRIVER=/path/to/exasol/current/libexaodbc.so;EXAHOST=127.0.0.1:8563;UID=sys;PWD=exasol;FINGERPRINT=NOCERTCHECK ``` Alternatively, the driver location can be specified in `/etc/odbcinst.ini` or `~/.odbcinst.ini`, in which case it need not be specified in `connection_string`. ``` [EXAODBC] Description = Exasol ODBC Driver Driver = /path/to/exasol/current/libexaodbc.so Threading = 2 FileUsage = 1 ``` With that file present, the connection string could be like: ``` connection_string=DRIVER=EXAODBC;EXAHOST=127.0.0.1:8563;UID=sys;PWD=exasol;FINGERPRINT=NOCERTCHECK ``` By creating an `/etc/odbc.ini` or `~/.odbc.ini`, the information that must be provided in the connection string can further be reduced. For instance, with the following, ``` [ExasolDSN] Driver = EXAODBC EXAHOST = 127.0.0.1:8563 UID = sys PWD = exasol FINGERPRINT=NOCERTCHECK ``` the connection string can be reduced to ``` connection_string=DSN=ExasolDSN ``` ## Examples ### SmartRouter The primary purpose of *ExasolRouter* is to be used together with [SmartRouter](/docs/maxscale/reference/maxscale-routers/maxscale-smartrouter.md). A minimal configuration looks as follows: ``` [Server1] type=server address=127.0.0.1 port=3306 protocol=mariadbbackend [ExasolService] type=service router=exasolrouter connection_string=DSN=ExasolDSN;UID=sys;PWD=exasol user= password= [SmartService] type=service router=smartrouter user=MyServiceUser password=MyServicePassword targets=Server1, ExasolService master=Server1 [SmartListener] type=listener service=SmartService port=4007 ``` It is assumed there is an `odbc.ini` ODBC configuration file containing an `ExasolDSN` entry. With this setup, all writes will always be sent to `Server1`. Reads will initially be sent to both `Server1` and `ExasolService` and once SmartRouter has learnt what kind of reads are best sent to which target, it will exclusively send reads to either `Server1` or `ExasolService`, depending on which one is likely to provide the response faster. Here, a single server was used as `master`. It could just as well be a [ReadWriteSplit](/docs/maxscale/reference/maxscale-routers/maxscale-readwritesplit.md) service in front of a MariaDB cluster, which would provide HA. ### Stand-Alone A minimal stand-alone configuration looks as follows. ``` [Server1] type=server address=127.0.0.1 port=3306 protocol=mariadbbackend [ExasolService] type=service router=exasolrouter connection_string=DSN=ExasolDSN;UID=sys;PWD=exasol;FINGERPRINT=NOCERTCHECK targets=Server1 user=MyServiceUser password=MyServicePassword [ExasolListener] type=listener service=ExasolService port=4008 ``` With this setup, it is possible to connect using the regular `mariadb` command line utility to the port 4008 and all queries will be sent to Exasol. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/maxscale/reference/maxscale-routers/maxscale-exasolrouter.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.