在SelectDB中使用JDBC資料來源 - ApsaraDB for SelectDB

本文介紹ApsaraDB for 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外表資料寫入的原子性，但一定程度上會降低資料寫入的效能，可按需開啟該功能。