> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Endpoint

> How to enable, configure, and secure the SQL endpoint that lets Tableau and Power BI query Atoti cubes via the SQL Bridge, covering the `bi-adapter` license, the `atoti.server.sql-bridge.enabled` property, the port (`atoti.server.sql-bridge.port`), and encryption (`atoti.server.sql-bridge.ssl.*`).

The SQL endpoint lets a BI tool connect to an Atoti cube as if it were a PostgreSQL database. Each
cube is exposed as a table. It runs a listener that speaks the PostgreSQL wire protocol. It is
built on a component called the SQL Bridge, shipped as the Maven artifact
`activepivot-server-sql-bridge`. Clients authenticate with an Atoti username and password.

The [Atoti Tableau Adapter](./tableau/tableau_adapter) connects Tableau to Atoti through the SQL
endpoint. See its [how-to guide](./tableau/tableau_adapter_how_to) for the Tableau-specific
connection steps. Power BI also connects to Atoti through the SQL endpoint.

## Which SQL is supported?

The SQL endpoint speaks the PostgreSQL wire protocol, so any PostgreSQL client can connect to it.
However, only the SQL that Tableau and Power BI generate is supported. Queries written by hand or
sent by other tools might not be supported.

## How to enable the SQL endpoint

> [!NOTE]
>
> This feature requires a license that enables the `bi-adapter` component.

Include the following dependency in the `pom.xml` file of an Atoti project:

```xml theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
<dependency>
    <groupId>com.activeviam.activepivot</groupId>
    <artifactId>activepivot-server-sql-bridge</artifactId>
    <version>${atoti-server.version}</version>
</dependency>
```

Then set `atoti.server.sql-bridge.enabled` to `true` in the Atoti application's configuration.
This property defaults to `false`.
The SQL endpoint opens a PostgreSQL-protocol listener and two
REST endpoints that generate the `.tds` and `.pbit` files. It stays off by default because those need
the application's security configuration to be reviewed for them.

## How to choose the listening port

By default, the server listens for SQL queries on port **5432**. This port number can be changed by
setting the `atoti.server.sql-bridge.port` property of the Atoti application.

Only one process on a machine can hold a given port. Port 5432 is unavailable if the machine also
runs a real PostgreSQL, or a second Atoti server. Set the property to another fixed port in that case,
for example `5433`.

A deployment should use a fixed port. The generated `.tds` and `.pbit` files embed that port, and so
does a client configured by hand. None of them need to change across restarts.

Set the property to `0` instead for a test. This also helps on a machine where no single fixed port
can be guaranteed free. The operating system then picks a free port at startup, but a different one
at every restart. The `.tds` and `.pbit` files must be regenerated, and any hand-configured client
reconfigured, after each restart.

> [!WARNING]
>
> Binding the configured port happens in the background. If another process already holds the port,
> the application still starts. The [`sqlBridge` health component](#health) then reports `DOWN` with
> the cause, and an error is logged. The `.tds` and `.pbit` endpoints answer `503 Service
>   Unavailable`.

## How to secure the file generation endpoints

The SQL endpoint exposes two REST endpoints that generate connection files.
`POST /activeviam/bi-adapter/rest/v1/tableau/tds/{cubeName}` generates the Tableau `.tds` file.
`POST /activeviam/bi-adapter/rest/v1/powerbi/pbit/{cubeName}` generates the Power BI `.pbit` file.
Each response describes the cube's measures and levels, together with the SQL endpoint's host and
port.

The SQL endpoint is enabled when `atoti.server.sql-bridge.enabled` is `true` and a license enables
the `bi-adapter` component. See [How to enable the SQL endpoint](#how-to-enable-the-sql-endpoint).
Whenever that happens, the Spring Boot starter secures both endpoints. A caller must be
authenticated and hold one of the roles configured by `atoti.server.security.roles-user` or
`atoti.server.security.roles-admin`. Atoti Server authenticates the request the same way as its
other machine-to-machine endpoints, through the [Machine to Machine DSL](../../../starters/atoti_starter#machine-to-machine-dsl).

Setting `atoti.server.endpoint.bi-adapter.security.enabled` to `false` removes the starter's filter
chain. Use this for an Atoti application that already secures these two endpoints with a filter
chain of its own.

## How to encrypt the SQL endpoint

This optional step encrypts the SQL endpoint independently of the web server.

The PostgreSQL wire protocol negotiates SSL inside the connection. A client opens a plain TCP
connection, sends an SSLRequest, then starts the TLS handshake on that same connection. A load
balancer or reverse proxy cannot terminate TLS on the SQL port the way it terminates HTTPS. The
port must pass through unchanged, and Atoti Server must encrypt the endpoint itself. HTTPS for
Atoti UI and the REST endpoints can still be terminated at the load balancer, with the web server
left in clear in that case.

The SQL endpoint's encryption is configured through the `atoti.server.sql-bridge.ssl.*` property
block:

* When no property of the block is set, the SQL endpoint follows the web server. It is encrypted
  whenever `server.ssl.*` enables HTTPS, using the same material.
* When any property of the block is set, it replaces the web server's configuration for the
  endpoint as a whole. There is no per-key merging. For example, `atoti.server.sql-bridge.ssl.enabled=false`
  keeps the endpoint in clear next to an HTTPS web server, and a keystore or bundle of its own
  encrypts the endpoint next to a web server that stays in clear.

The block accepts the same keys as Spring Boot's `server.ssl.*`: `enabled`, `bundle`, `key-store`,
`key-store-password`, `key-store-type`, `key-alias`, `key-password`, `trust-store`,
`trust-store-password`, `trust-store-type`, `certificate`, `certificate-private-key`,
`trust-certificate`, `protocol`, `enabled-protocols`, `ciphers`, and `client-auth` (`none`, `want`,
`need`). The material is loaded through an SSL bundle, the same way Spring Boot loads it for the
web server. Anything that works for `server.ssl.*` works here too. A trust store is required only
to verify client certificates.

The following properties encrypt the SQL endpoint with its own certificate, while the web server
stays in clear:

```properties theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
atoti.server.sql-bridge.ssl.key-store=file:/etc/atoti/sql-bridge.p12
atoti.server.sql-bridge.ssl.key-store-password=changeit
```

Unusable SSL material, such as a missing keystore, a wrong password, or an unknown bundle, stops
the application from starting with an error naming the property block to fix.

Certificate rotation without a restart applies when
`atoti.server.sql-bridge.ssl.bundle` names a bundle declared with `reload-on-update=true`.
Spring Boot watches the bundle's files, and a renewed
certificate is taken into use by the SQL endpoint for every connection opened from then on;
connections already open keep the certificate they negotiated. The following properties declare
such a bundle and reference it from the SQL endpoint's SSL block:

```properties theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}}
spring.ssl.bundle.jks.sql-bridge.keystore.location=file:/etc/atoti/sql-bridge.p12
spring.ssl.bundle.jks.sql-bridge.keystore.password=changeit
spring.ssl.bundle.jks.sql-bridge.reload-on-update=true
atoti.server.sql-bridge.ssl.bundle=sql-bridge
```

If the renewed material cannot be loaded, for example a corrupt or half-written keystore, the SQL
endpoint keeps serving the previous certificate and logs an error, so a failed rotation never
takes the endpoint down. A `key-store`, `certificate`, or other store named directly in the block
is still read once at startup, as it is for the web server, and a restart is needed to pick up its
renewal.

> [!NOTE]
>
> Encrypting the endpoint lets a client encrypt its connection. A client that does not ask for SSL is
> still served in clear. Configure the client to require encryption: `sslmode=require` (or stricter)
> for JDBC and psql, or the "Require SSL" option in Tableau.

## Configuration reference

The SQL endpoint is configured through the following properties of the Atoti application:

| Property | Default | Description |
| - | - | - |
| `atoti.server.sql-bridge.enabled` | `false` | Enables the SQL endpoint. A license that enables the `bi-adapter` component is also required. See [How to enable the SQL endpoint](#how-to-enable-the-sql-endpoint). |
| `atoti.server.sql-bridge.port` | `5432` | Port the SQL Bridge listens on, or `0` to let the operating system pick a free one. Whichever port it ends up listening on is the one embedded in the generated file. |
| `atoti.server.endpoint.bi-adapter.security.enabled` | `true` | Secures the two REST endpoints that generate the `.tds` and `.pbit` files, requiring an authenticated user holding one of the roles configured by `atoti.server.security.roles-user` or `atoti.server.security.roles-admin`. Set to `false` for an application that secures these endpoints with a filter chain of its own. See [How to secure the file generation endpoints](#how-to-secure-the-file-generation-endpoints). |
| `atoti.server.sql-bridge.levelNameMode` | `SHORTEST` | How the exposed columns name the cube levels. `SHORTEST` uses the level name alone when no other level shares that name, and falls back to the complete `level@hierarchy@dimension` name otherwise. `COMPLETE` always uses the complete name. |
| `atoti.server.sql-bridge.ssl.*` | follows `server.ssl.*` | Encrypts the SQL endpoint independently of the web server. See [How to encrypt the SQL endpoint](#how-to-encrypt-the-sql-endpoint). |

## Monitoring

The Atoti BI adapter relies on BI tools sending SQL queries to Atoti Server: a component called the
SQL Bridge translates each query and forwards it to the cube.
That SQL layer is an implementation detail, but it shows up in logs and traces.

### Health

The SQL Bridge contributes a `sqlBridge` component to Spring Boot Actuator's health endpoint. It appears under `components.sqlBridge` in the `/actuator/health` response when details are shown. The application must include Spring Boot Actuator (`spring-boot-starter-actuator`) and expose the health endpoint. Without Actuator, nothing changes for the application.

The component reports one of three statuses:

* `UP`: the SQL endpoint's listener is bound. Details: `port`, the port clients must target. This matters when `atoti.server.sql-bridge.port` is `0` and the operating system picked the port. Also `ssl`, `true` when the endpoint is encrypted.
* `DOWN`: the listener could not bind its port, for example because another process already holds it. Details: `error`, the failure. Also `configuredPort` and `ssl`.
* `UNKNOWN`: the listener has not yet bound its port. This lasts a few milliseconds after application startup. Details: `configuredPort` and `reason`. This status does not degrade the application's aggregated health.

Details such as `port` and `error` are shown only when `management.endpoint.health.show-details` is `always` or `when-authorized`. The component can be turned off with `management.health.sqlbridge.enabled=false`.

### Logging

Two loggers are available:

* `atoti.server.query.sqlbridge`: logs the query processing. This includes the logical and physical query plans built from the incoming SQL. It also includes the cube, levels, and measures targeted by each underlying query. This logger also reports, at `ERROR`, a port the listener could not bind.
* `atoti.server.query.sqlbridge.protocol`: logs the protocol exchange, including received and sent messages, session lifecycle, and authentication. It also logs any error encountered while handling a query.

### Tracing

Tracing is used to provide insight into the SQL queries received from BI tools.
Atoti Server tracing must be enabled and configured before SQL query traces become available. See [Tracing](../../../monitoring/tracing) for setup instructions.

Every query received is wrapped in a `Sql Bridge query` span, tagged with:

* `sql-query`: the raw SQL query text sent by the BI tool.
* `sql-query-params`: the parameters bound to the query, when it is a parameterized (prepared) query.