ℹ️ We are using JDBC bridges to connect to other databases from ClickHouse. The config file of JDBC bridge are in the Vault.
Requirements : to connect a new database, Arkhn recommends to use a replica rather than production database. The data flow between ClickHouse and the external database must be enabled by opening the necessary ports to allow communication through the JDBC bridge.
- Connect to the vault
PLATFORM-URL/ui/vault/ - Go to
secrets/ch-datasources/datasources.json
- Click on "Create new version" and enter your database credentials according to the configuration outlined above.
For the naming convention, database name must be in lower case and schema name must be in upper case. Database and schema name must be separate by_or__if there is a_in the schema name.
⚠️ Be careful with spacing: there must be exactly one space after:
¶ Base Oracle
{
"database_SCHEMA": {
"converter": {
"mappings": [{ "nativeType": "TIMESTAMP", "to": "DateTime64" }]
},
"driverClassName": "oracle.jdbc.driver.OracleDriver",
"jdbcUrl": "jdbc:oracle:thin:@//serveur:port/database",
"username": "username",
"password": "password",
"connectionTestQuery": "SELECT 1 FROM DUAL"
}
¶ Base SQLServer
{
"database_SCHEMA": {
"converter": {
"mappings": [
{ "nativeType": "date", "to": "DateTime64" },
{ "nativeType": "datetime", "to": "DateTime64" },
{ "nativeType": "datetime2", "to": "DateTime64" }
]
},
"driverClassName": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"jdbcUrl": "jdbc:sqlserver://serveur:port;databaseName=name;encrypt=false;",
"username": "username",
"password": "password",
"maximumPoolSize": 10,
"minimumIdle": 0,
"parameters": {
"fetch_size": 100000
},
}
- You can test your connection with :
select *
from jdbc('database_SCHEMA', 'table_name')
⚠️ Be aware that JDBC is case sensitive. If the source table name is uppercase, the table name parameter must be 'TABLE_NAME'