JDBC Catalog

Download

Focus Mode

Font Size

Last updated: 2024-07-04 10:20:11

Note:
This feature is applicable to Tencent Cloud TCHouse-D  1.2 and later versions.
JDBC Catalog connects to other data sources through the standard JDBC protocol. After the connection is made, Doris will automatically synchronize the Metadata of the Database and Table under the data source for quick access to these external data.
Creating Catalog
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-5.1.47.jar",    
"driver_class" = "com.mysql.jdbc.Driver");
PostgreSQL
CREATE CATALOG jdbc_postgresql PROPERTIES (    
"type"="jdbc",    
"user"="root",    
"password"="123456",    
"jdbc_url" = "jdbc:postgresql://127.0.0.1:5449/demo",    
"driver_url" = "postgresql-42.5.1.jar",    
"driver_class" = "org.postgresql.Driver");
When mapping PostgreSQL, one Database in Doris corresponds to one Schema under a specified Catalog in PostgreSQL (such as the schemas under "demo" in the jdbc_url parameter in the example). And a Table in a Doris Database corresponds to Tables under a Schema in PostgreSQL. The mapping relationship is as follows:
Doris
PostgreSQL
Catalog
Database
Database
Schema
Table
Table
Doris uses SQL statements to get all the schemas that the PG user can access and maps them as Doris databases.
select nspname from pg_namespace where has_schema_privilege('<UserName>', nspname, 'USAGE'); 
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" = "ojdbc6.jar",    
"driver_class" = "oracle.jdbc.driver.OracleDriver");
When mapping Oracle, one Database in Doris corresponds to one User in Oracle. A Table in the Doris Database corresponds to a Table to which this User has permission to access in Oracle. The mapping relationship is as follows:
Doris
Oracle
Catalog
Database
Database
User
Table
Table
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.3.2-patch11-all.jar",    
"driver_class" = "com.clickhouse.jdbc.ClickHouseDriver");
SQLServer
CREATE CATALOG sqlserver_catalog PROPERTIES (    
"type"="jdbc",    
"user"="SA",    
"password"="Doris123456",    
"jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",    "driver_url" = "mssql-jdbc-11.2.3.jre8.jar",    
"driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver");
When mapping SQLServer, one Database in Doris corresponds to one Schema under a specified Database (such as "doris_test" in the jdbc_url parameter in the example) in SQLServer. A Table in the Doris Database corresponds to Tables under a Schema in SQLServer. The mapping relationship is as follows:
Doris
SQLServer
Catalog
Database
Database
Schema
Table
Table
Doris
JDBC Catalog also supports connecting to another Doris database:
CREATE CATALOG doris_catalog PROPERTIES (    
"type"="jdbc",   
"user"="root",    
"password"="123456",    
"jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",    
"driver_url" = "mysql-connector-java-5.1.47.jar",    
"driver_class" = "com.mysql.jdbc.Driver");
Currently, JDBC Catalog only supports connection to a Doris database with the JDBC jar package of version 5.x. If the 8.x JDBC jar package is used, column type mismatch issues may occur.
Parameter Description
Parameter
Required
Default Value
Description
User
Yes
-
Username of the corresponding database
Password
Yes
-
Password for the corresponding database
jdbc_url
Yes
-
JDBC connection string
driver_url
Yes
-
JDBC Driver Jar package name*
driver_class
Yes
-
JDBC Driver Class name
only_specified_database
No
"false"
Specify whether to only synchronize the specified database
lower_case_table_names
No
"false"
Whether to synchronize the table name of the JDBC external data source in lowercase
include_database_list
No
""
When only_specified_database=true, specify to synchronize multiple databases, separated by ','. The db name is case-sensitive.
exclude_database_list
No
""
When only_specified_database=true, specify multiple databases that don't need to be synchronized, separated by ','. The db name is case-sensitive.
Driver Package Path
Driver_urlcan be specified in the following three ways:
1. File name. For example, mysql-connector-java-5.1.47.jar. The Jar package must be stored in the jdbc_drivers/ directory of the FE and BE deployment directory in advance. The system will automatically find the package in this directory. The location of this directory can also be modified by thejdbc_drivers_dir configuration in fe.conf and be.conf.
2. Local absolute path. Like file:///path/to/mysql-connector-java-5.1.47.jar. The Jar package must be stored in the path specified by all the FE/BE nodes in advance.
3. Http address. For example:https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-5.1.47.jar. The system will download the driver file from this http address. Only unauthenticated http services are supported.
Specifying the Synchronized Database
only_specified_database: When connecting to jdbc, you can specify which database/schema to link to, such as: database can be specified in jdbc_url of MySQL, and currentSchema can be specified in jdbc_url of pg.
include_database_list: when only_specified_database=true, specify the databases to be synchronized, separated by ','. The default is '', that is, to synchronize all the databases without filtering. The db name is case-sensitive.
exclude_database_list: When only_specified_database=true, specify multiple databases that don't need to be synchronized, separated by ','. The default is '', that is, to sync all the databases without filtering. The db name is case-sensitive.
When the databases configurations ofinclude_database_list and exclude_database_list overlap,exclude_database_listwill take priority to take effect first.
If you connect to the Oracle database using this parameter, you must use the jar package later than the ojdbc8.jar versions.
Data Query
select * from mysql_catalog.mysql_database.mysql_table where t1 > 1000 and t2 ='term';
Since there may be situations where the internal keywords of the database are used as field names, in order to query correctly under this situation, escape characters will be automatically added to field names and table names in SQL statements according to the standards of each database. For example MYSQL(``),PostgreSQL(""), SQLServer([]), and ORACLE(""), therefore, this may cause case sensitivity of field names. You can view the query statements issued to each databases after escaping through explain sql.
Column Type Mapping
MySQL
MYSQL Type
Doris Type
Comment
BOOLEAN
BOOLEAN
-
TINYINT
TINYINT
-
SMALLINT
SMALLINT
-
MEDIUMINT
INT
-
INT
INT
-
BIGINT
BIGINT
-
UNSIGNED TINYINT
SMALLINT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
UNSIGNED MEDIUMINT
INT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
UNSIGNED INT
BIGINT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
UNSIGNED BIGINT
LARGEINT
-
FLOAT
FLOAT
-
DOUBLE
DOUBLE
-
DECIMAL
DECIMAL
-
DATE
DATE
-
TIMESTAMP
DATETIME
-
DATETIME
DATETIME
-
YEAR
SMALLINT
-
TIME
STRING
-
CHAR
CHAR
-
VARCHAR
VARCHAR
-
TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,TINY blob , blob ,MEDIUM blob ,LONG blob ,TINYSTRING,STRING,MEDIUMSTRING,LONGSTRING,BINARY,VARBINARY,JSON,SET,BIT
STRING
-
Other
UNSUPPORTED
-
PostgreSQL
POSTGRESQL Type
Doris Type
Comment
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
-
time
STRING
-
interval
STRING
-
point/line/lseg/box/path/polygon/circle
STRING
-
cidr/inet/macaddr
STRING
-
bit/bit(n)/bit varying(n)
STRING
The BIT type is mapped to the STRING type of Doris, and the read data is true/false instead of 1/0.
uuid/JSONB
STRING
-
Other
UNSUPPORTED
-
Oracle
ORACLE Type
Doris Type
Comment
number(p) / number(p,0)
TINYINT/SMALLINT/INT/BIGINT/LARGEINT
Doris selects the corresponding type based on the size of 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
When s<0, Doris sets p as p+|s| and maps the same as number(p) / number(p,0).
number
-
Doris currently does not support Oracle type with unspecified p and s.
decimal
DECIMAL
-
float/real
DOUBLE
-
DATE
DATETIME
-
TIMESTAMP
DATETIME
-
CHAR/NCHAR
STRING
-
VARCHAR2/NVARCHAR2
STRING
-
LONG/ RAW/ LONG RAW/ INTERVAL
STRING
-
Other
UNSUPPORTED
-
SQLServer
SQLServer Type
Doris Type
Comment
bit
BOOLEAN
-
tinyint
SMALLINT
The tinyint of SQLServer is unsigned, so it is mapped to SMALLINT of Doris.
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
-
Clickhouse
ClickHouse Type
Doris Type
Comment
Bool
BOOLEAN
-
String
STRING
-
Date/Date32
DATEV2
The Jdbc Catalog uses the DATEV2 type to connect to ClickHouse by default.
DateTime/DateTime64
DATETIMEV2
The Jdbc Catalog uses the DATETIMEV2 type to connect to ClickHouse by default.
Float32
FLOAT
-
Float64
DOUBLE
-
Int8
TINYINT
-
Int16/UInt8
SMALLINT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int32/UInt16
INT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int64/Uint32
BIGINT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int128/UInt64
LARGEINT
Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int256/UInt128/UInt256
STRING
Doris does not have data type of this magnitude, it is processed with STRING.
DECIMAL
DECIMAL/DECIMALV3/STRING
The type is decided based on the (precision, scale) of Doris DECIMAL field and the enable_decimal_conversion switch.
Enum/IPv4/IPv6/UUID
STRING
When displaying, IPv4 and IPv6 will display an extra "/" at the very beginning of the data. You need to handle it using the split_part function.
Array
ARRAY
The adaptation logic of Array internal types refers to the above types. Nested types are not supported.
Other
UNSUPPORTED
-
﻿

Help and Support

Was this page helpful?

You can also Contact sales or Submit a Ticket for help.

Help us improve! Rate your documentation experience in 5 mins.

Feedback

Parameter	Required	Default Value	Description
User	Yes	-	Username of the corresponding database
Password	Yes	-	Password for the corresponding database
jdbc_url	Yes	-	JDBC connection string
driver_url	Yes	-	JDBC Driver Jar package name*
driver_class	Yes	-	JDBC Driver Class name
only_specified_database	No	"false"	Specify whether to only synchronize the specified database
lower_case_table_names	No	"false"	Whether to synchronize the table name of the JDBC external data source in lowercase
include_database_list	No	""	When only_specified_database=true, specify to synchronize multiple databases, separated by ','. The db name is case-sensitive.
exclude_database_list	No	""	When only_specified_database=true, specify multiple databases that don't need to be synchronized, separated by ','. The db name is case-sensitive.

MYSQL Type	Doris Type	Comment
BOOLEAN	BOOLEAN	-
TINYINT	TINYINT	-
SMALLINT	SMALLINT	-
MEDIUMINT	INT	-
INT	INT	-
BIGINT	BIGINT	-
UNSIGNED TINYINT	SMALLINT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
UNSIGNED MEDIUMINT	INT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
UNSIGNED INT	BIGINT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
UNSIGNED BIGINT	LARGEINT	-
FLOAT	FLOAT	-
DOUBLE	DOUBLE	-
DECIMAL	DECIMAL	-
DATE	DATE	-
TIMESTAMP	DATETIME	-
DATETIME	DATETIME	-
YEAR	SMALLINT	-
TIME	STRING	-
CHAR	CHAR	-
VARCHAR	VARCHAR	-
TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,TINY blob , blob ,MEDIUM blob ,LONG blob ,TINYSTRING,STRING,MEDIUMSTRING,LONGSTRING,BINARY,VARBINARY,JSON,SET,BIT	STRING	-
Other	UNSUPPORTED	-

POSTGRESQL Type	Doris Type	Comment
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	-
time	STRING	-
interval	STRING	-
point/line/lseg/box/path/polygon/circle	STRING	-
cidr/inet/macaddr	STRING	-
bit/bit(n)/bit varying(n)	STRING	The BIT type is mapped to the STRING type of Doris, and the read data is true/false instead of 1/0.
uuid/JSONB	STRING	-
Other	UNSUPPORTED	-

ORACLE Type	Doris Type	Comment
number(p) / number(p,0)	TINYINT/SMALLINT/INT/BIGINT/LARGEINT	Doris selects the corresponding type based on the size of 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	When s<0, Doris sets p as p+\|s\| and maps the same as number(p) / number(p,0).
number	-	Doris currently does not support Oracle type with unspecified p and s.
decimal	DECIMAL	-
float/real	DOUBLE	-
DATE	DATETIME	-
TIMESTAMP	DATETIME	-
CHAR/NCHAR	STRING	-
VARCHAR2/NVARCHAR2	STRING	-
LONG/ RAW/ LONG RAW/ INTERVAL	STRING	-
Other	UNSUPPORTED	-

SQLServer Type	Doris Type	Comment
bit	BOOLEAN	-
tinyint	SMALLINT	The tinyint of SQLServer is unsigned, so it is mapped to SMALLINT of Doris.
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	-

ClickHouse Type	Doris Type	Comment
Bool	BOOLEAN	-
String	STRING	-
Date/Date32	DATEV2	The Jdbc Catalog uses the DATEV2 type to connect to ClickHouse by default.
DateTime/DateTime64	DATETIMEV2	The Jdbc Catalog uses the DATETIMEV2 type to connect to ClickHouse by default.
Float32	FLOAT	-
Float64	DOUBLE	-
Int8	TINYINT	-
Int16/UInt8	SMALLINT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int32/UInt16	INT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int64/Uint32	BIGINT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int128/UInt64	LARGEINT	Doris does not have an UNSIGNED data type, so it is enlarged by an order of magnitude.
Int256/UInt128/UInt256	STRING	Doris does not have data type of this magnitude, it is processed with STRING.
DECIMAL	DECIMAL/DECIMALV3/STRING	The type is decided based on the (precision, scale) of Doris DECIMAL field and the enable_decimal_conversion switch.
Enum/IPv4/IPv6/UUID	STRING	When displaying, IPv4 and IPv6 will display an extra "/" at the very beginning of the data. You need to handle it using the split_part function.
Array	ARRAY	The adaptation logic of Array internal types refers to the above types. Nested types are not supported.
Other	UNSUPPORTED	-

tencent cloud

Tencent Cloud TCHouse-D

JDBC Catalog

Creating Catalog

MySQL

PostgreSQL

Oracle

Clickhouse

SQLServer

Doris

Parameter Description

Driver Package Path

Specifying the Synchronized Database

Data Query

Column Type Mapping

MySQL

PostgreSQL

Oracle

SQLServer

Clickhouse

Help and Support