Connecting to a database using custom JDBC string
You can connect to your database using a custom JDBC string, allowing you to extend the settings offered by our extractor builder. This is useful for when you want to configure SSL, add certificates, or use .jks files for your database connection.
The steps detailed here follow the same initial steps as the standard database connection, with an additional step added to define and input your custom string.
Step 1: Define the connection type
Before creating a connection between your database and the Celonis Platform you must decide which connection type you use.
You have two options here:
Direct connections to the database
Use this when you want to allow the Celonis Platform to directly access your database.
Uplinked connections to the database
Use this when you don't want to or can't allow the Celonis Platform to directly access your database. The connection is then established using an on-premise extractor.
For more information about the on-premise extractor (including system requirements), see: On-premise extractors.
The next step is to modify your network settings to allow the database extractor to communicate with your database and the Celonis Platform.
The settings here are based on the connection type you defined in step 1:
Network settings for direct connections
The following network settings apply for direct connections:
Source system | Target system | Port | Protocol | Description |
|---|---|---|---|---|
Celonis Platform | Source system | Depending 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 you normally use 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. |
Network settings for uplinked connections
The following network settings apply for uplinked connections (via the on-premise extractor):
Source system | Target system | Port | Protocol | Description |
|---|---|---|---|---|
On-premise Extractor server | Source system | Depending 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 you normally use 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. |
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 to connect the on-premise extractor server and the cloud endpoint.
For a complete list of inbound and outbound Celonis Platform IP addresses to be allowlisted if needed, see: Allowlisting domain names and IP addresses
Note
This step is only needed if your database is not reachable from the Celonis Platform.
For this step, you need to set up the database extractor in your network if your database is not reachable from the Celonis Platform.
For more information, see: Setting up
If you would like to use a proxy (optional), see: Proxy settings for on-prem clients.
In order to extract from a database, you need to create or use an existing user who has read (SELECT) access to all tables that should be extracted.
The user should also access the schema information_schema to read metadata for the tables to be extracted.
For more information about this, consult your database's official documentation.
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:
For MySQL connections, use the following formatting:
jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...
For example:
jdbc:mysql://localhost:3306/test?user=root&password=secret
For SQL connections, use the following formatting:
jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
For example:
jdbc:sqlserver://localhost\\sqlexpress;user=sa;password=secret
For Oracle connections, use the following formatting:
jdbc:oracle:<drivertype>:@<database>
For example:
jdbc:oracle:thin:@localhost:1521:testdb
For SAP S/4HANA connections, use the following formatting:
jdbc:sap://<server>:<port>/?databaseName=<database>
For example:
jdbc:sap://localhost:30013/?databaseName=testdb
For SAP S/4HANA connections, use the following formatting:
jdbc:postgresql://host:port/database
For example:
jdbc:postgresql://localhost:5432/testdb
You can now create the connection between your database and the Celonis Platform from your data pool diagram:
Click Data Connections.
Click Add Data Connection and select Connect to Data Source.
For direct connections, select Cloud - Database.
For uplinked connections, select: On-Premise - Database and then select your uplink (configured as part of step 1).
Configure the following connection details, with the options here depending on your choice of uplinked or direct connection:
Name: An internal reference for the data connection.
Uplink Connections: The name of the Uplink Extractor Server installed on your end.
Database Type: The type (generic name) of the database to which you want to connect.
Configuration type: Select Custom JDBC Connection String
JDBC Connection String: Enter the string you want to use for this connection.
Schema name: The schema to use (optional).
Additional Properties: Additional properties like validateCertificate=false for a HANA database or integratedSecurity=true for domain users in MSSQL.
Username and password: The database credentials used by the user in configured in step 4.
Maximum number of parallel extractions: This is usually governed by the database type you are using. The default value chosen is 4.
Timeout for database connections: Timeout for all database connections created in this connection (specific to this connection only). This value will overwrite the local timeout (in application.yaml) in case of uplinked connection.
Click Test Connection and correct any highlighted issues.
Click Save.
The connection between your Cloudera Impala account and the Celonis Platform is establised. You can manage this connection at any time by clicking Options:
The following connection parameters are not supported:
- AUTO_DESERIALIZE("autoDeserialize"),
- ALLOW_URL_IN_LOCAL_IN_FILE("allowUrlInLocalInfile"),
- ALLOW_LOAD_LOCAL_IN_FILE("allowLoadLocalInfile"),
- ALLOW_PUBLIC_KEY_RETRIEVAL("allowPublicKeyRetrieval"),
- EXPOSE_METADATA("exposeMetadata"),
- STATEMENT_INTERCEPTORS("statementInterceptors"),
- QUERY_INTERCEPTORS("queryInterceptors"),
- DETECT_CUSTOM_COLLATIONS("detectCustomCollations"),
//IBM DB2 driver properties according to <add this link - https://www.ibm.com/support/pages/node/7010029>
- TRACE_FILE("traceFile"),
- CLIENT_REROUTE_SERVER_LIST_JNDI_NAME("clientRerouteServerListJNDIName"),
- PLUGIN_CLASS_NAME("pluginClassName");