Skip to main content

Celonis Product Documentation

Setting up a database connection using a custom JDBC string
Step 1: Define the Connection Type (responsible role: database server and infrastructure admin)

There are two scenarios:

  1. Uplinked: You do not want to or cannot allow the Celonis Platform to access your database directly but you want to use an on-premise Extractor instead.

    Only if your database is not reachable from the Celonis Platform: Set up an Extractor server on-premise in your network. A detailed diagram showing the extraction flow can be found here.

    Please refer to the documentation on system requirements of the on-premise server.

  2. Direct: You want to allow the Celonis Platform to access your database directly.

Step 2: Modify your network settings to allow the Extractor to communicate to the database and the Celonis Platform (responsible role: infrastructure admin)

There are two scenarios that depend on the connection type selected in Step 1:

Network settings for Scenario A (access through an on-premise Extractor)

Source System

Target System

Port

Protocol

Description

On premise Extractor server

Source system

(on-premise)

depends on the database, typical ports are 5432 for Postgres and 30015 for HANA for example

TCP

JDBC connection from on-premise Extractor server to the database. The port is the one that you use normally to connect to the database.

On premise Extractor server

Celonis Platform

443

TCP

HTTPS connection from on-premise Extractor server to Celonis cloud endpoint. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL) and they can be found in the section below.

Network settings for Scenario B (direct access)

Source System

Target System

Port

Protocol

Description

Celonis Platform

Source system

(on-premise)

depends on the database, typical ports are 5432 for Postgres and 30015 for HANA for example

TCP

JDBC connection from the Celonis Platform to the database. The port is the one that you use normally to connect to the database. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL) and they can be found in the section below.

Celonis Platform IP addresses depending on the cluster

The respective clusters use multiple IPs each so you need to enable all three of them in your firewall configuration for the connection between the on premise Extractor server and the cloud endpoint.

Step 3: Only if your database is not reachable from the Celonis Platform: Set up the database Extractor on premise in your network (responsible role: infrastructure admin)

Please refer to the documentation on how to set up an on premise Extractor .

For downloads, contach Celonis Support.

If you would like to use a proxy (optional), please refer to the proxy configuration page.

Step 4: Configure a user with sufficient permissions in your database (responsible role: database admin)

In order to extract from a database, you need to create or use an existing user which has read access to all tables which should be extracted. The user should also have access to the schema information_schema to read metadata for the tables to be extracted.

Step 5: Get the JDBC custom connection URL (responsible role: data engineer)

The custom JDBC connection URL differs depending on the database type you want to connect to. Here is a summary of the URL's for the most common databases with examples:

  1. MySQL

    - Format of database URL

    jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

    Example:

    jdbc:mysql://localhost:3306/test?user=root&password=secret
  2. SQL Server

    - Format of database URL

    jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

    Example:

    jdbc:sqlserver://localhost\\sqlexpress;user=sa;password=secret
  3. Oracle

    - Format of database URL

    jdbc:oracle:<drivertype>:@<database>

    Example:

    jdbc:oracle:thin:@localhost:1521:testdb
  4. SAP HANA

    - Format of database URL

    jdbc:sap://<server>:<port>/?databaseName=<database>

    Example:

    jdbc:sap://localhost:30013/?databaseName=testdb
  5. PostgreSQL

    - Format of database URL

    jdbc:postgresql://host:port/database

    Example:

    jdbc:postgresql://localhost:5432/testdb
Step 6: Create the Data Connection in a Data Pool (responsible role: data engineer)
35553340.png

Fill in your connection details of the database that you would like to connect to:

  1. Name: The name that you would like the data connection to have

  2. Connection Type: Choose whether you want to directly connect to the database or use an uplinked one

  3. Database Type: Choose the database type you want to connect to

  4. Server name: The database server name or IP of the database server

  5. Configuration Type: Select Custom JDBC String

  6. Custom String: Insert your JDBC custom connection URL string

  7. Maximum Parallel Table Extrations: Choose how many tables should be extracted in parallel

  8. Schema Name: Optionally set a Schema name

  9. Additional Properties: Additional properties like validateCertificate=false for a HANA database or integratedSecurity=true for domain users in MSSQL

  10. User name: The username to connect to the database server

  11. Password: The password of the user