Database Integrations

This page is the single reference for supported databases, JDBC connection strings, required and permissions.

Supported Databases

Supported Data Sources

Synthesized easily connects to a variety of data sources.

POSTGRES ORACLE MYSQL MARIADB SQLITE MSSQL Snowflake DB2 LUW CSV XML SAP HANA

PostgreSQL

For a PostgreSQL, the basic format of the connect string is:

jdbc:postgresql://<host>:<port>/<database>

Example of JDBC URL:

jdbc:postgresql://localhost:5432/postgres

More information about connecting to the PostgreSQL database can be found in the documentation.

SQL Server

The general form of the connection URL for SQL Server is:

jdbc:sqlserver://<host>:<port>[;<property>=<value>]

Example of JDBC URL which is used to connect to master database without encryption:

jdbc:sqlserver://localhost:1433;encrypt=false;databaseName=master

More information about connecting to the SQL Server database can be found in the documentation.

MySQL

The general form of the connection URL is:

jdbc:mysql://<host>:<port>[/<database>][?properties]

Example:

jdbc:mysql://localhost:3306/sakila
LOAD DATA LOCAL INFILE requires the MySQL server (or managed instance) to allow local infile operations (local_infile=1). The platform automatically enables the JDBC-side property when USE_FILE_BATCH_INSERT is set, but you must ensure the server configuration permits it.

More information about connecting to the MySQL database can be found in the documentation.

Oracle

When connecting to an Oracle database using JDBC, the URL format depends on the type of connection method. Some of the connection methods are described below.

Service Name Connection

The format of the JDBC URL to connect Oracle databases via service name:

jdbc:oracle:thin:@//<host>:<port>/<serviceName>

Example:

jdbc:oracle:thin:@//localhost:1521/DB

TNS URL Format

tnsnames.ora entries can be included in the JDBC URL to connect to Oracle databases:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service>)))

Example:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB)))

More information about connecting to the Oracle database can be found in the documentation.

Db2

Basic URL template for Db2 (type 4 connectivity) is:

jdbc:db2://<host>:<port>/<database>

Example:

jdbc:db2://localhost:50000/testdb

More information about connecting to the Db2 database can be found in the documentation.

MariaDB

Basic URL template for MariaDB is:

jdbc:mariadb://<host>:<port>/<database>[?key1=value1&key2=value2...]

Example:

jdbc:mariadb://localhost:3306/mydatabase

More information about connecting to the MariaDB database can be found in the documentation.

SQLite

The general form of the connection URL for SQLite is:

jdbc:sqlite:<path_to_sqlite_file>

Example of JDBC URL with the absolute path to the SQLite database file (test.db):

jdbc:sqlite:/var/data/test.db

SAP HANA

For a SAP HANA database, the basic format of the connect string is:

jdbc:sap://<host>:<port>/<database>

Example of JDBC URL:

jdbc:sap://ec2-35-177-245-71.eu-west-2.compute.amazonaws.com:39015

CSV File

For a CSV file, the basic format of the connect string is:

s3://<file storage>:<port>/<bucket>/<directory>

Example of JDBC URL:

s3://minio:9000/csvinput/data/

XML/XSD File

For a XML file, the basic format of the connect string is:

xml:s3://<file storage>:<port>/<bucket>/<directory>/<file.xsd>

Example of JDBC URL:

xml:s3://minio:9000/my-root/data/CCR001.xsd

Required Permissions

PostgreSQL

-- Read-only (recommended for source)
GRANT CONNECT ON DATABASE mydb TO tdk_user;
GRANT USAGE ON SCHEMA public TO tdk_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tdk_user;

-- Read-write (for target database)
GRANT ALL PRIVILEGES ON DATABASE mydb TO tdk_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tdk_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO tdk_user;

MySQL / MariaDB

-- Read-only (recommended for source)
GRANT SELECT ON mydb.* TO 'tdk_user'@'%';

-- Read-write (for target database)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON mydb.* TO 'tdk_user'@'%';

Oracle

-- Read-only (recommended for source)
GRANT CONNECT TO tdk_user;
GRANT SELECT ANY TABLE TO tdk_user;

-- Read-write (for target database)
GRANT CONNECT, RESOURCE TO tdk_user;
GRANT CREATE TABLE TO tdk_user;
GRANT UNLIMITED TABLESPACE TO tdk_user;

SQL Server / Azure SQL

-- Read-only (recommended for source)
CREATE USER tdk_user FOR LOGIN tdk_login;
GRANT SELECT ON SCHEMA::dbo TO tdk_user;

-- Read-write (for target database)
CREATE USER tdk_user FOR LOGIN tdk_login;
ALTER ROLE db_datareader ADD MEMBER tdk_user;
ALTER ROLE db_datawriter ADD MEMBER tdk_user;
ALTER ROLE db_ddladmin ADD MEMBER tdk_user;

Troubleshooting