Integrate with JDBC compatible data sources - ApsaraDB for SelectDB

A JDBC Catalog connects SelectDB to external databases through the standard JDBC protocol. Once connected, SelectDB automatically synchronizes metadata from the external data source, letting you run federated queries across MySQL, PostgreSQL, Oracle, SQLServer, ClickHouse, Doris, SAP HANA, Trino/Presto, and OceanBase—without moving data.

Prerequisites

Before you begin, ensure that:

All nodes in the data source cluster and your SelectDB instance can communicate over the network. They must be in the same Virtual Private Cloud (VPC). If they are not, resolve the connectivity issue first. For more information, see How do I resolve network connectivity issues between a SelectDB instance and a data source?
The IP addresses of all nodes in the data source cluster are added to the SelectDB instance whitelist. For more information, see Configure a whitelist.
If the source cluster has its own whitelist, add the SelectDB instance's CIDR block to it.
- To get the SelectDB instance's VPC IP address, see How do I view the IP addresses in the VPC to which my ApsaraDB SelectDB instance belongs?
- To get the public IP address, run the ping command against the SelectDB instance's public endpoint.
You have a basic understanding of catalogs. For more information, see Data lakehouse.

Create a JDBC catalog

Syntax

CREATE CATALOG <catalog_name>
PROPERTIES ("key"="value", ...)

Parameters

Parameter	Required	Default	Description
`user`	Yes	—	Username for the database account.
`password`	Yes	—	Password for the database account.
`jdbc_url`	Yes	—	JDBC connection string. Format varies by database: MySQL — `jdbc:mysql://host:port/db`; PostgreSQL — `jdbc:postgresql://host:port/db`; Oracle — `jdbc:oracle:thin:@host:port:sid`; SQLServer — `jdbc:sqlserver://host:port;DataBaseName=db`. See the per-database examples below for the full list.
`driver_url`	Yes	—	Path to the JDBC driver JAR file. Specify a file name (for example, `mysql-connector-java-8.0.25.jar`) to load from the local `jdbc_drivers/` directory, or an HTTP URL to download the file.
`driver_class`	Yes	—	Class name of the JDBC driver. Common values: MySQL — `com.mysql.cj.jdbc.Driver`; PostgreSQL — `org.postgresql.Driver`; Oracle — `oracle.jdbc.driver.OracleDriver`; SQLServer — `com.microsoft.sqlserver.jdbc.SQLServerDriver`. See the per-database examples for other databases.
`lower_case_table_names`	No	`"false"`	When `true`, synchronizes database and table names in lowercase. See Lowercase name synchronization.
`only_specified_database`	No	`"false"`	When `true`, synchronizes only the databases specified in `include_database_list`.
`include_database_list`	No	`""`	Takes effect only when `only_specified_database=true`. Comma-separated list of databases to synchronize. Case-sensitive.
`exclude_database_list`	No	`""`	Takes effect only when `only_specified_database=true`. Comma-separated list of databases to exclude. Case-sensitive. If a database appears in both lists, `exclude_database_list` takes precedence.

Driver package path

The driver_url parameter accepts two formats:

File name — for example, mysql-connector-java-8.0.25.jar. SelectDB searches the local jdbc_drivers/ directory. The following four driver packages are pre-installed:
- mysql-connector-java-8.0.25.jar
- postgresql-42.5.1.jar
- mssql-jdbc-11.2.3.jre8.jar
- ojdbc8.jar
HTTP URL — for example, https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar. SelectDB downloads the file from this URL. Only unauthenticated HTTP services are supported.

Lowercase name synchronization

When lower_case_table_names=true, SelectDB maintains a lowercase-to-actual-name mapping so you can query databases and tables using lowercase names regardless of their original casing.

Behavior by version:

SelectDB 2.X — Effective only for Oracle. All database and table names are converted to uppercase before the query is sent to Oracle. For example, if you set lower_case_table_names=true and Oracle has a table named TEST in the TEST schema, you can query it with SELECT * FROM oracle_catalog.test.test. SelectDB automatically converts test.test to TEST.TEST.
SelectDB 3.X and later — Effective for all databases. Names are converted to their actual casing before query execution. If you upgraded from an earlier version, run REFRESH <catalog_name> for this change to take effect.

Constraints:

If two database or table names differ only in case (for example, SelectDB and selectdb), SelectDB cannot query them due to ambiguity.
If the frontend (FE) lower_case_table_names parameter is set to 1 or 2, set the JDBC catalog's lower_case_table_names to true. If the FE parameter is 0, you can set it to either true or false (default: false).

Synchronize specific databases

Use only_specified_database, include_database_list, and exclude_database_list together to limit which databases SelectDB synchronizes.

When connecting through JDBC, you can also pre-select a database in the jdbc_url itself—for example, specify the database name in the MySQL URL, or use currentSchema in the PostgreSQL URL.

When using include_database_list or exclude_database_list with Oracle, use ojdbc8.jar or a later version.

Examples by database

MySQL

CREATE CATALOG jdbc_mysql PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
    "driver_url" = "mysql-connector-java-8.0.25.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
)

Level mapping

SelectDB	MySQL
Catalog	MySQL Server
Database	Database
Table	Table

Type mapping

MySQL type	SelectDB type	Notes
BOOLEAN	TINYINT
TINYINT	TINYINT
SMALLINT	SMALLINT
MEDIUMINT	INT
INT	INT
BIGINT	BIGINT
UNSIGNED TINYINT	SMALLINT	SelectDB has no UNSIGNED type; range is expanded to the next level.
UNSIGNED MEDIUMINT	INT	SelectDB has no UNSIGNED type; range is expanded to the next level.
UNSIGNED INT	BIGINT	SelectDB has no UNSIGNED type; range is expanded to the next level.
UNSIGNED BIGINT	LARGEINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL	DECIMAL
UNSIGNED DECIMAL(p,s)	DECIMAL(p+1,s) / STRING	If `p+1 > 38`, STRING is used.
DATE	DATE
TIMESTAMP	DATETIME
DATETIME	DATETIME
YEAR	SMALLINT
TIME	STRING
CHAR	CHAR
VARCHAR	VARCHAR
JSON	JSON
SET	STRING
BIT	BOOLEAN / STRING	BIT(1) maps to BOOLEAN; all other BIT types map to STRING.
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	STRING
BLOB, MEDIUMBLOB, LONGBLOB, TINYBLOB	STRING
TINYSTRING, STRING, MEDIUMSTRING, LONGSTRING	STRING
BINARY, VARBINARY	STRING
Other	UNSUPPORTED

PostgreSQL

CREATE CATALOG jdbc_postgresql PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo",
    "driver_url" = "postgresql-42.5.1.jar",
    "driver_class" = "org.postgresql.Driver"
);

Level mapping

A SelectDB database maps to a schema in the PostgreSQL database specified in the jdbc_url (for example, the schemas in demo).

SelectDB	PostgreSQL
Catalog	Database
Database	Schema
Table	Table

SelectDB retrieves accessible schemas by running: SELECT nspname FROM pg_namespace WHERE has_schema_privilege('<UserName>', nspname, 'USAGE');

Type mapping

PostgreSQL type	SelectDB type	Notes
boolean	BOOLEAN
smallint / int2	SMALLINT
integer / int4	INT
bigint / int8	BIGINT
decimal / numeric	DECIMAL
real / float4	FLOAT
double precision	DOUBLE
smallserial	SMALLINT
serial	INT
bigserial	BIGINT
char	CHAR
varchar / text	STRING
timestamp	DATETIME
date	DATE
json / jsonb	JSON
time	STRING
interval	STRING
point / line / lseg / box / path / polygon / circle	STRING
cidr / inet / macaddr	STRING
bit	BOOLEAN / STRING	bit(1) maps to BOOLEAN; all other bit types map to STRING.
uuid	STRING
Other	UNSUPPORTED

Oracle

CREATE CATALOG jdbc_oracle PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
    "driver_url" = "ojdbc8.jar",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);

Level mapping

A SelectDB database maps to a user in Oracle. A table maps to a table that the user has permissions to access.

SelectDB	Oracle
Catalog	Database
Database	User
Table	Table

Synchronization of Oracle SYNONYM TABLEs is not supported.

Type mapping

Oracle type	SelectDB type	Notes
number(p) / number(p,0)	TINYINT / SMALLINT / INT / BIGINT / LARGEINT	Mapped based on p: p < 3 → TINYINT; p < 5 → SMALLINT; p < 10 → INT; p < 19 → BIGINT; p > 19 → LARGEINT
number(p,s) [if s > 0 and p > s]	DECIMAL(p,s)
number(p,s) [if s > 0 and p < s]	DECIMAL(s,s)
number(p,s) [if s < 0]	TINYINT / SMALLINT / INT / BIGINT / LARGEINT	SelectDB sets p to `p+	s	`and applies the same mapping as` number(p)/number(p,0)`.
number (no p or s specified)	Not supported	SelectDB does not currently support Oracle `NUMBER` without explicit precision and scale.
decimal	DECIMAL
float / real	DOUBLE
DATE	DATETIME
TIMESTAMP	DATETIME
CHAR / NCHAR	STRING
VARCHAR2 / NVARCHAR2	STRING
LONG / RAW / LONG RAW / INTERVAL	STRING
Other	UNSUPPORTED

SQLServer

Important

For SelectDB 3.0.8 and later, include encrypt=false in the jdbc_url.

CREATE CATALOG jdbc_sqlserver PROPERTIES (
    "type"="jdbc",
    "user"="SA",
    "password"="SelectDB123456",
    "jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=SelectDB_test;encrypt=false",
    "driver_url" = "mssql-jdbc-11.2.3.jre8.jar",
    "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
);

Level mapping

A SelectDB database maps to a schema in the SQLServer database specified in the jdbc_url (for example, schemas in SelectDB_test).

SelectDB	SQLServer
Catalog	Database
Database	Schema
Table	Table

Type mapping

SQLServer type	SelectDB type
bit	BOOLEAN
tinyint	SMALLINT
smallint	SMALLINT
int	INT
bigint	BIGINT
real	FLOAT
float	DOUBLE
money	DECIMAL(19,4)
smallmoney	DECIMAL(10,4)
decimal / numeric	DECIMAL
date	DATE
datetime / datetime2 / smalldatetime	DATETIMEV2
char / varchar / text / nchar / nvarchar / ntext	STRING
binary / varbinary	STRING
time / datetimeoffset	STRING
Other	UNSUPPORTED

Doris

SelectDB connects to Doris using the MySQL JDBC driver.

CREATE CATALOG jdbc_doris PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",
    "driver_url" = "mysql-connector-java-8.0.25.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
)

Type mapping

Doris type	SelectDB type	Notes
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INT	INT
BIGINT	BIGINT
LARGEINT	LARGEINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMALV3	DECIMALV3 / STRING	Selected based on the precision and scale of the DECIMAL field.
DATE	DATE
DATETIME	DATETIME
CHAR	CHAR
VARCHAR	VARCHAR
STRING	STRING
TEXT	STRING
HLL	HLL	Set `return_object_data_as_binary=true` to query HLL columns.
Array	Array	Internal type mapping follows the rules above. Nested complex types are not supported.
BITMAP	BITMAP	Set `return_object_data_as_binary=true` to query BITMAP columns.
Other	UNSUPPORTED

ClickHouse

CREATE CATALOG jdbc_clickhouse PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
    "driver_url" = "clickhouse-jdbc-0.4.2-all.jar",
    "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
);

Level mapping

SelectDB	ClickHouse
Catalog	ClickHouse Server
Database	Database
Table	Table

Type mapping

ClickHouse type	SelectDB type
Bool	BOOLEAN
String	STRING
Date / Date32	DATE
DateTime / DateTime64	DATETIME
Float32	FLOAT
Float64	DOUBLE
Int8	TINYINT
Int16 / UInt8	SMALLINT
Int32 / UInt16	INT
Int64 / UInt32	BIGINT
Int128 / UInt64	LARGEINT
Int256 / UInt128 / UInt256	STRING
DECIMAL	DECIMALV3 / STRING
Enum / IPv4 / IPv6 / UUID	STRING
Array	ARRAY
Other	UNSUPPORTED

SAP HANA

CREATE CATALOG jdbc_hana PROPERTIES (
    "type"="jdbc",
    "user"="SYSTEM",
    "password"="SAPHANA",
    "jdbc_url" = "jdbc:sap://localhost:31515/TEST",
    "driver_url" = "ngdbc.jar",
    "driver_class" = "com.sap.db.jdbc.Driver"
)

Level mapping

SelectDB	SAP HANA
Catalog	Database
Database	Schema
Table	Table

Type mapping

SAP HANA type	SelectDB type
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INT
BIGINT	BIGINT
SMALLDECIMAL	DECIMALV3
DECIMAL	DECIMALV3 / STRING
REAL	FLOAT
DOUBLE	DOUBLE
DATE	DATE
TIME	STRING
TIMESTAMP	DATETIME
SECONDDATE	DATETIME
VARCHAR	STRING
NVARCHAR	STRING
ALPHANUM	STRING
SHORTTEXT	STRING
CHAR	CHAR
NCHAR	CHAR

OceanBase

CREATE CATALOG jdbc_oceanbase PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:oceanbase://127.0.0.1:2881/demo",
    "driver_url" = "oceanbase-client-2.4.2.jar",
    "driver_class" = "com.oceanbase.jdbc.Driver"
)

SelectDB auto-detects whether OceanBase is running in MySQL or Oracle mode. Level mapping and type mapping follow the MySQL or Oracle rules accordingly. See the MySQL and Oracle sections.

Query data

Use the three-part name <catalog>.<database>.<table> to query data in a JDBC catalog:

SELECT * FROM mysql_catalog.mysql_database.mysql_table WHERE k1 > 1000 AND k3 = 'term';

Escape characters: SelectDB automatically escapes field and table names based on each database's standards—backticks for MySQL, double quotation marks for PostgreSQL and Oracle, and square brackets for SQLServer. This may make field names case-sensitive. Run EXPLAIN on a query to see the escaped SQL sent to the remote database.

Predicate pushdown

SelectDB pushes WHERE clause conditions down to the external data source to filter data at the source, reducing unnecessary data transfer and improving query performance.

When enable_func_pushdown=true (session variable), SelectDB also pushes functions in the WHERE clause to the external data source. This is supported only for MySQL. The following functions are excluded from pushdown: DATE_TRUNC and MONEY_FORMAT. To disable function pushdown, set enable_func_pushdown=false. Run EXPLAIN to inspect which conditions are pushed down.

Row count limit

If a query includes the LIMIT keyword, SelectDB translates it into the appropriate syntax for the target database.

Write data

After creating a JDBC catalog, write data using INSERT INTO or INSERT INTO...SELECT:

-- Write a single row
INSERT INTO mysql_catalog.mysql_database.mysql_table VALUES(1, "doris");

-- Write query results
INSERT INTO mysql_catalog.mysql_database.mysql_table SELECT * FROM table;

For large data volumes, use INSERT INTO...SELECT instead of INSERT INTO. The INSERT INTO statement is inefficient for bulk writes.

Transactions

Data from SelectDB is written to a JDBC catalog in batches. If an import is interrupted, previously written data may need to be rolled back. To handle this, the JDBC Catalog supports transactions for data writes. To enable transaction support, set the enable_odbc_transcation session variable:

SET enable_odbc_transcation = TRUE;

Transactions ensure atomicity for writes to JDBC foreign tables but reduce write performance. Enable transactions only when data consistency is critical.