在SelectDB中使用JDBC数据源 - 云数据库 SelectDB 版

本文介绍云数据库 SelectDB 版与JDBC数据源进行对接使用的流程，帮助您对兼容JDBC协议的数据源进行联邦分析。

概述

JDBC Catalog支持通过标准JDBC协议连接其他数据源。连接后，SelectDB会自动同步数据源下的Database和Table的元数据，以便快速访问这些外部数据。

SelectDB支持选择MySQL、PostgreSQL、Oracle、SQLServer、Clickhouse、Doris、SPA HANA、Trino/Presto、OceanBase作为JDBC数据源。

创建语法

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

参数说明

参数	是否必选	默认值	说明
user	是	无	对应数据库的账号。
password	是	无	对应数据库的密码。
jdbc_url	是	无	JDBC连接串。
driver_url	是	无	JDBC Driver Jar包名称。
driver_class	是	无	JDBC Driver Class名称。
lower_case_table_names	否	"false"	是否以小写的形式同步JDBC外部数据源的库名和表名。
only_specified_database	否	"false"	指定是否只同步指定的Database。
include_database_list	否	""	当`only_specified_database=true`时，指定同步多个Database，以英文逗号（,）分隔。DB名称大小写敏感。
exclude_database_list	否	""	当`only_specified_database=true`时，指定不需要同步的多个Database，以英文逗号（,）分隔。DB名称大小写敏感。

驱动包路径

创建JDBC Catalog需要指定对应数据库的驱动包路径。对应参数driver_url可以通过以下方式指定：

文件名。例如mysql-connector-java-8.0.25.jar，SelectDB会自动在本地jdbc_drivers/目录下查找相应jar包，其中默认包含了mysql-connector-java-8.0.25.jar、postgresql-42.5.1.jar、mssql-jdbc-11.2.3.jre8.jar、ojdbc8.jar这4个常用的Driver包，您可按需使用。
HTTP地址。例如https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar，系统会从这个HTTP地址下载Driver文件，仅支持无认证的HTTP服务。

小写表名同步

当lower_case_table_names设置为true时，SelectDB通过维护小写名称到远程系统中实际名称的映射，能够查询非小写的数据库和表。

SelectDB 2.X版本仅对Oracle数据库有效。在查询时，会将所有的库名和表名转换为大写，再查询 Oracle。例如
Oracle在TEST空间下有TEST表，SelectDB在创建Catalog时设置lower_case_table_names=true，则SelectDB可以通过SELECT * FROM oracle_catalog.test.test查询到TEST表，SelectDB会自动将test.test格式化成TEST.TEST下发到Oracle。需要注意这是个默认行为，这意味着此时不能查询Oracle中小写的表名。
对于其他数据库，仍需要在查询时指定实际的库名和表名。
SelectDB 3.X及之后的版本，对所有的数据库都有效，在查询时，会将所有的库名和表名转换为真实的名称再去查询。如果是从老版本升级到3.X，需要Refresh <catalog_name>才能生效。
但是，如果数据库或者表名只有大小写不同，例如SelectDB和selectdb，则SelectDB由于歧义而无法查询它们。
当FE参数的lower_case_table_names设置为1或2时，JDBC Catalog的lower_case_table_names参数必须设置为true。如果FE参数的lower_case_table_names设置为0，则 JDBC Catalog的参数可以为true或false，默认为false。这确保了SelectDB在处理内部和外部表配置时的一致性和可预测性。

指定同步数据库

以下参数和指定同步数据库有关：

only_specified_database：在通过JDBC连接时可以指定连接到哪个Database或Schema。如：MySQL的jdbc_url中可以指定Database，PostgreSQL的jdbc_url中可以指定currentSchema。
include_database_list：仅在only_specified_database=true时生效，指定需要同步的Database，以英文逗号（,）分隔，Database名称是大小写敏感的。
exclude_database_list：仅在only_specified_database=true时生效，指定不需要同步的多个Database，以英文逗号（,）分隔，Database名称是大小写敏感的。

说明

当include_database_list和exclude_database_list有重合的Database配置时，会优先考虑exclude_database_list。
如果使用该参数连接Oracle数据库，则必须使用ojdbc8.jar以上版本的jar包。

创建示例

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",
    "checksum" = "fdf55dcef04b09f2eaf42b75e61ccc9a"
)

说明

若出现compute driver checksum相关错误，可临时增加如下property屏蔽checksum检测机制：

"checksum" = "fdf55dcef04b09f2eaf42b75e61ccc9a"。

层级映射

SelectDB	MySQL
Catalog	MySQL Server
Database	Database
Table	Table

类型映射

MYSQL Type	SelectDB Type	备注
BOOLEAN	TINYINT	无
TINYINT	TINYINT	无
SMALLINT	SMALLINT	无
MEDIUMINT	INT	无
INT	INT	无
BIGINT	BIGINT	无
UNSIGNED TINYINT	SMALLINT	SelectDB没有UNSIGNED数据类型，所以扩大一个数量级。
UNSIGNED MEDIUMINT	INT	SelectDB没有UNSIGNED数据类型，所以扩大一个数量级。
UNSIGNED INT	BIGINT	SelectDB没有UNSIGNED数据类型，所以扩大一个数量级。
UNSIGNED BIGINT	LARGEINT	无
FLOAT	FLOAT	无
DOUBLE	DOUBLE	无
DECIMAL	DECIMAL	无
UNSIGNED DECIMAL(p,s)	DECIMAL(p+1,s) / STRING	如果`p+1>38`, 将使用SelectDB STRING类型。
DATE	DATE	无
TIMESTAMP	DATETIME	无
DATETIME	DATETIME	无
YEAR	SMALLINT	无
TIME	STRING	无
CHAR	CHAR	无
VARCHAR	VARCHAR	无
JSON	JSON	无
SET	STRING	无
BIT	BOOLEAN/STRING	BIT(1)会映射为BOOLEAN，其他BIT映射为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",
    "checksum" = "20c8228267b6c9ce620fddb39467d3eb"
);

层级映射

映射PostgreSQL时，SelectDB的一个Database对应PostgreSQL中指定Catalog下的一个Schema，例如上方示例中jdbc_url参数中demo中的schemas。而SelectDB的Database下的Table则对应于PostgreSQL中Schema下的Tables。即映射关系如下：

SelectDB	PostgreSQL
Catalog	Database
Database	Schema
Table	Table

说明

SelectDB通过SQL语句SELECT nspname FROM pg_namespace WHERE has_schema_privilege('<UserName>', nspname, 'USAGE');来获得PG user能够访问的所有Schema并将其映射为SelectDB的Database。

类型映射

POSTGRESQL Type	SelectDB Type	备注
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)会映射为BOOLEAN，其他bit映射为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",
    "checksum" = "621a393d7be9ff0f2fec6fbba2c8f9b6"
);

层级映射

映射Oracle时，SelectDB的一个Database对应Oracle中的一个User。而SelectDB的Database中的Table则对应于Oracle中该User有权限访问的Table。即映射关系如下：

SelectDB	Oracle
Catalog	Database
Database	User
Table	Table

说明

当前不支持同步Oracle的SYNONYM TABLE。

类型映射

ORACLE Type	SelectDB Type	备注
number(p) / number(p,0)	TINYINT/SMALLINT/INT/BIGINT/LARGEINT	SelectDB会根据p的大小来选择对应的类型： p < 3，TINYINT p < 5，SMALLINT p < 10，INT p < 19，BIGINT p > 19，LARGEINT
number(p,s),[ if(s>0 && p>s) ]	DECIMAL(p,s)	无
number(p,s),[ if(s>0 && p < s) ]	DECIMAL(s,s)	无
number(p,s),[ if(s<0) ]	TINYINT/SMALLINT/INT/BIGINT/LARGEINT	`s<0`的情况下, SelectDB会将p设置为`p+\|s\|`，并进行和`number(p)/number(p,0)`一样的映射。
number	无	SelectDB目前不支持未指定p和s的Oracle类型。
decimal	DECIMAL	无
float/real	DOUBLE	无
DATE	DATETIME	无
TIMESTAMP	DATETIME	无
CHAR/NCHAR	STRING	无
VARCHAR2/NVARCHAR2	STRING	无
LONG/ RAW/ LONG RAW/ INTERVAL	STRING	无
Other	UNSUPPORTED	无

SQLServer

创建示例

重要

若您使用的SelectDB版本高于或等于3.0.8，为了确保您能正常访问SQLServer的数据，务必在jdbc_url连接串中包含encrypt=false参数。

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",
    "checksum" = "b204274eb02a848ac405961e6f43e7bd"
);

层级映射

映射SQLServer时，SelectDB的Database对应SQLServer中指定Database，例如上方示例中jdbc_url参数中的SelectDB_test下的一个Schema。而SelectDB中Database下的Table则对应SQLServer中Schema下的Tables。即映射关系如下：

SelectDB	SQLServer
Catalog	Database
Database	Schema
Table	Table

类型映射

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 JDBC Catalog也支持连接Doris数据库。

创建示例

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",
    "checksum" = "fdf55dcef04b09f2eaf42b75e61ccc9a"
)

类型映射

Doris Type	JDBC Catalog SelectDB Type	备注
BOOLEAN	BOOLEAN	无
TINYINT	TINYINT	无
SMALLINT	SMALLINT	无
INT	INT	无
BIGINT	BIGINT	无
LARGEINT	LARGEINT	无
FLOAT	FLOAT	无
DOUBLE	DOUBLE	无
DECIMALV3	DECIMALV3/STRING	将根据DECIMAL字段的（precision, scale）选择用何种类型。
DATE	DATE	无
DATETIME	DATETIME	无
CHAR	CHAR	无
VARCHAR	VARCHAR	无
STRING	STRING	无
TEXT	STRING	无
HLL	HLL	查询HLL需要设置`return_object_data_as_binary=true`。
Array	Array	Array内部类型适配逻辑参考上述类型，不支持嵌套复杂类型。
BITMAP	BITMAP	查询BITMAP需要设置`return_object_data_as_binary=true`。
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",
    "checksum" = "9be22a93267dc4b066e0a3aefc2dd024"
);

层级映射

SelectDB	ClickHouse
Catalog	ClickHouse Server
Database	Database
Table	Table

类型映射

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

SPA 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"
)

层级映射

SelectDB	SAP HANA
Catalog	Database
Database	Schema
Table	Table

类型映射

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

Trino或Presto

创建示例

Trino

CREATE CATALOG jdbc_trino PROPERTIES (
    "type"="jdbc",
    "user"="hadoop",
    "password"="",
    "jdbc_url" = "jdbc:trino://localhost:9000/hive",
    "driver_url" = "trino-jdbc-389.jar",
    "driver_class" = "io.trino.jdbc.TrinoDriver"
);

Presto

CREATE CATALOG jdbc_presto PROPERTIES (
    "type"="jdbc",
    "user"="hadoop",
    "password"="",
    "jdbc_url" = "jdbc:presto://localhost:9000/hive",
    "driver_url" = "presto-jdbc-0.280.jar",
    "driver_class" = "com.facebook.presto.jdbc.PrestoDriver"
);

层级映射

映射Trino或Presto时，SelectDB的Database对应Trino或Presto中指定Catalog下的一个Schema。而SelectDB中Database下的Table则对应Trino或Presto中Schema下的Tables。即映射关系如下：

SelectDB	Trino/Presto
Catalog	Catalog
Database	Schema
Table	Table

类型映射

Trino/Presto Type	SelectDB Type
boolean	BOOLEAN
tinyint	TINYINT
smallint	SMALLINT
integer	INT
bigint	BIGINT
decimal	DECIMAL/DECIMALV3/STRING
real	FLOAT
double	DOUBLE
date	DATE
timestamp	DATETIME
varchar	TEXT
char	CHAR
array	ARRAY
others	UNSUPPORTED

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在连接OceanBase时，会自动识别OceanBase处于MySQL或者Oracle模式，层级对应和类型映射参见上方的MySQL与Oracle章节。

数据查询

示例

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

说明

由于可能存在使用数据库内部的关键字作为字段名，为使得这种状况下SelectDB仍能正确查询，在SQL语句中，SelectDB会根据各个数据库的标准自动在字段名与表名上加上转义符。例如 MYSQL(``)、PostgreSQL("")、SQLServer([])、ORACLE("")等，此时可能会造成字段名的大小写敏感，您可以通过EXPLAIN SQL命令，查看转义后下发到各个数据库的查询语句。

谓词下推

当执行类似于WHERE dt = '2022-01-01'的查询时，SelectDB能够将这些过滤条件下推到外部数据源，从而直接在数据源层面过滤不符合条件的数据，减少了不必要的数据获取和传输。这将大大提高了查询性能，同时也降低了对外部数据源的负载。

当会话变量enable_func_pushdown设置为true时，SelectDB会将WHERE之后的函数条件也下推到外部数据源。该功能目前仅支持MySQL，如遇到MySQL不支持的函数，可以将此参数设置为false，目前SelectDB会自动识别部分MySQL不支持的函数进行下推条件过滤，您可以通过EXPLAIN SQL命令查看具体的查询语句。

目前不支持下推的函数为：DATE_TRUNC和MONEY_FORMAT。

行数限制

如果在查询中带有LIMIT关键字，SelectDB会将其转译成适合不同数据源的语义。

数据写入

在SelectDB中建立JDBC Catalog后，可以通过INSERT INTO语句直接写入数据，也可以将SelectDB执行完查询之后的结果写入JDBC Catalog，或者是从一个JDBC Catalog将数据导入另一个JDBC Catalog中。

示例

INSERT INTO mysql_catalog.mysql_database.mysql_table VALUES(1, "doris");
INSERT INTO mysql_catalog.mysql_database.mysql_table SELECT * FROM table;

事务

SelectDB的数据是由一组Batch的方式写入JDBC Catalog。如果中途导入中断，之前写入的数据可能需要回滚。所以JDBC Catalog支持数据写入时的事务，事务的支持需要通过设置会话变量enable_odbc_transcation。

SET enable_odbc_transcation = TRUE;

事务保证了JDBC外表数据写入的原子性，但一定程度上会降低数据写入的性能，可按需开启该功能。