You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/01/16 06:09:16 UTC
[doris] 02/06: [feature](multi-catalog) support clickhouse jdbc catalog (#15780)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
commit f2ec5dde8cbcac3ebf8041b40574fbe6e3be1f07
Author: yongkang.zhong <zh...@qq.com>
AuthorDate: Fri Jan 13 10:07:22 2023 +0800
[feature](multi-catalog) support clickhouse jdbc catalog (#15780)
---
be/src/vec/exec/vjdbc_connector.cpp | 7 +-
.../docs/ecosystem/external-table/multi-catalog.md | 51 ++++++++++++--
.../Create/CREATE-CATALOG.md | 24 ++++++-
.../docs/ecosystem/external-table/multi-catalog.md | 51 ++++++++++++--
.../Create/CREATE-CATALOG.md | 24 ++++++-
.../org/apache/doris/catalog/JdbcResource.java | 5 +-
.../org/apache/doris/external/jdbc/JdbcClient.java | 79 ++++++++++++++++++++++
7 files changed, 225 insertions(+), 16 deletions(-)
diff --git a/be/src/vec/exec/vjdbc_connector.cpp b/be/src/vec/exec/vjdbc_connector.cpp
index b8898c0b2b..090cb6e0b6 100644
--- a/be/src/vec/exec/vjdbc_connector.cpp
+++ b/be/src/vec/exec/vjdbc_connector.cpp
@@ -235,7 +235,8 @@ Status JdbcConnector::_check_type(SlotDescriptor* slot_desc, const std::string&
type_str, slot_desc->type().debug_string(), slot_desc->col_name());
switch (slot_desc->type().type) {
case TYPE_BOOLEAN: {
- if (type_str != "java.lang.Boolean" && type_str != "java.math.BigDecimal") {
+ if (type_str != "java.lang.Boolean" && type_str != "java.math.BigDecimal" &&
+ type_str != "java.lang.Byte") {
return Status::InternalError(error_msg);
}
break;
@@ -244,7 +245,7 @@ Status JdbcConnector::_check_type(SlotDescriptor* slot_desc, const std::string&
case TYPE_SMALLINT:
case TYPE_INT: {
if (type_str != "java.lang.Short" && type_str != "java.lang.Integer" &&
- type_str != "java.math.BigDecimal") {
+ type_str != "java.math.BigDecimal" && type_str != "java.lang.Byte") {
return Status::InternalError(error_msg);
}
break;
@@ -281,7 +282,7 @@ Status JdbcConnector::_check_type(SlotDescriptor* slot_desc, const std::string&
case TYPE_DATETIME:
case TYPE_DATETIMEV2: {
if (type_str != "java.sql.Timestamp" && type_str != "java.time.LocalDateTime" &&
- type_str != "java.sql.Date") {
+ type_str != "java.sql.Date" && type_str != "java.time.LocalDate") {
return Status::InternalError(error_msg);
}
break;
diff --git a/docs/en/docs/ecosystem/external-table/multi-catalog.md b/docs/en/docs/ecosystem/external-table/multi-catalog.md
index 41f94ad4b0..0d0439ec2b 100644
--- a/docs/en/docs/ecosystem/external-table/multi-catalog.md
+++ b/docs/en/docs/ecosystem/external-table/multi-catalog.md
@@ -381,7 +381,7 @@ Parameter | Description
### Connect JDBC
-The following example creates a Catalog connection named jdbc. This jdbc Catalog will connect to the specified database according to the 'jdbc.jdbc_url' parameter(`jdbc::mysql` in the example, so connect to the mysql database). Currently, only the MYSQL database type is supported.
+The following example creates a Catalog connection named jdbc. This jdbc Catalog will connect to the specified database according to the 'jdbc.jdbc_url' parameter(`jdbc::mysql` in the example, so connect to the mysql database). Currently, supports MYSQL, POSTGRESQL, CLICKHOUSE database types.
**mysql catalog example**
@@ -391,8 +391,8 @@ CREATE RESOURCE mysql_resource PROPERTIES (
"type"="jdbc",
"user"="root",
"password"="123456",
- "jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
- "driver_url" = "file:/path/to/mysql-connector-java-5.1.47.jar",
+ "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
+ "driver_url" = "file:///path/to/mysql-connector-java-5.1.47.jar",
"driver_class" = "com.mysql.jdbc.Driver"
)
CREATE CATALOG jdbc WITH RESOURCE mysql_resource;
@@ -400,7 +400,7 @@ CREATE CATALOG jdbc WITH RESOURCE mysql_resource;
-- 1.2.0 Version
CREATE CATALOG jdbc PROPERTIES (
"type"="jdbc",
- "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
...
)
```
@@ -414,7 +414,7 @@ CREATE RESOURCE pg_resource PROPERTIES (
"user"="postgres",
"password"="123456",
"jdbc_url" = "jdbc:postgresql://127.0.0.1:5449/demo",
- "driver_url" = "file:/path/to/postgresql-42.5.1.jar",
+ "driver_url" = "file:///path/to/postgresql-42.5.1.jar",
"driver_class" = "org.postgresql.Driver"
);
CREATE CATALOG jdbc WITH RESOURCE pg_resource;
@@ -427,6 +427,28 @@ CREATE CATALOG jdbc PROPERTIES (
)
```
+**CLICKHOUSE catalog example**
+
+```sql
+-- 1.2.0+ Version
+CREATE RESOURCE clickhouse_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="default",
+ "password"="123456",
+ "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar",
+ "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
+)
+CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
+
+-- 1.2.0 Version
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ ...
+)
+```
+
Where `jdbc.driver_url` can be a remote jar package
```sql
@@ -723,6 +745,25 @@ For Hive/Iceberge/Hudi
| bit/bit(n)/bit varying(n) | STRING | `bit` type corresponds to the `STRING` type of DORIS. The data read is `true/false`, not `1/0` |
| uuid/josnb | STRING | |
+#### CLICKHOUSE
+
+| ClickHouse Type | Doris Type | Comment |
+|------------------------|------------|--------------------------------------------------------------------------------------------------------------------------------------|
+| Bool | BOOLEAN | |
+| String | STRING | |
+| Date/Date32 | DATE | |
+| DateTime/DateTime64 | DATETIME | Data that exceeds Doris's maximum DateTime accuracy is truncated |
+| Float32 | FLOAT | |
+| Float64 | DOUBLE | |
+| Int8 | TINYINT | |
+| Int16/UInt8 | SMALLINT | DORIS does not have the UNSIGNED data type, so expand the type |
+| Int32/UInt16 | INT | DORIS does not have the UNSIGNED data type, so expand the type |
+| Int64/Uint32 | BIGINT | DORIS does not have the UNSIGNED data type, so expand the type |
+| Int128/UInt64 | LARGEINT | DORIS does not have the UNSIGNED data type, so expand the type |
+| Int256/UInt128/UInt256 | STRING | Doris does not have a data type of this magnitude and is processed with STRING |
+| DECIMAL | DECIMAL | Data that exceeds Doris's maximum Decimal precision is mapped to a STRING |
+| Enum/IPv4/IPv6/UUID | STRING | In the display of IPv4 and IPv6, an extra `/` is displayed before the data, which needs to be processed by the `split_part` function |
+
## Privilege Management
Using Doris to access the databases and tables in the External Catalog is not controlled by the permissions of the external data source itself, but relies on Doris's own permission access management.
diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
index b6d76b94b1..33e9e4af9a 100644
--- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
+++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
@@ -168,7 +168,29 @@ CREATE CATALOG catalog_name PROPERTIES (
"jdbc.driver_url" = "file:/path/to/postgresql-42.5.1.jar",
"jdbc.driver_class" = "org.postgresql.Driver"
);
- ```
+ ```
+
+ **clickhouse**
+
+ ```sql
+ -- 1.2.0+ Version
+ CREATE RESOURCE clickhouse_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="default",
+ "password"="123456",
+ "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar",
+ "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
+ )
+ CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
+
+ -- 1.2.0 Version
+ CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ ...
+ )
+ ```
### Keywords
diff --git a/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md b/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
index 58f7bfe578..8192eea436 100644
--- a/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
+++ b/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
@@ -454,7 +454,7 @@ mysql> select * from test;
### 连接JDBC
以下示例，用于创建一个名为 jdbc 的 Catalog, 通过jdbc 连接指定的Mysql。
-jdbc Catalog会根据`jdbc.jdbc_url` 来连接指定的数据库（示例中是`jdbc::mysql`, 所以连接MYSQL数据库），当前支持MYSQL、POSTGRESQL数据库类型。
+jdbc Catalog会根据`jdbc.jdbc_url` 来连接指定的数据库（示例中是`jdbc::mysql`, 所以连接MYSQL数据库），当前支持MYSQL、POSTGRESQL、CLICKHOUSE数据库类型。
**MYSQL catalog示例**
@@ -464,8 +464,8 @@ CREATE RESOURCE mysql_resource PROPERTIES (
"type"="jdbc",
"user"="root",
"password"="123456",
- "jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
- "driver_url" = "file:/path/to/mysql-connector-java-5.1.47.jar",
+ "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
+ "driver_url" = "file:///path/to/mysql-connector-java-5.1.47.jar",
"driver_class" = "com.mysql.jdbc.Driver"
)
CREATE CATALOG jdbc WITH RESOURCE mysql_resource;
@@ -473,7 +473,7 @@ CREATE CATALOG jdbc WITH RESOURCE mysql_resource;
-- 1.2.0 版本
CREATE CATALOG jdbc PROPERTIES (
"type"="jdbc",
- "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
...
)
```
@@ -487,7 +487,7 @@ CREATE RESOURCE pg_resource PROPERTIES (
"user"="postgres",
"password"="123456",
"jdbc_url" = "jdbc:postgresql://127.0.0.1:5449/demo",
- "driver_url" = "file:/path/to/postgresql-42.5.1.jar",
+ "driver_url" = "file:///path/to/postgresql-42.5.1.jar",
"driver_class" = "org.postgresql.Driver"
);
CREATE CATALOG jdbc WITH RESOURCE pg_resource;
@@ -500,6 +500,28 @@ CREATE CATALOG jdbc PROPERTIES (
)
```
+**CLICKHOUSE catalog示例**
+
+```sql
+-- 1.2.0+ 版本
+CREATE RESOURCE clickhouse_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="default",
+ "password"="123456",
+ "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar",
+ "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
+)
+CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
+
+-- 1.2.0 版本
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ ...
+)
+```
+
其中`jdbc.driver_url`可以是远程jar包：
```sql
@@ -722,6 +744,25 @@ select k1, k4 from table; // Query OK.
| bit/bit(n)/bit varying(n) | STRING | `bit`类型映射为doris的`STRING`类型，读出的数据是`true/false`, 而不是`1/0` |
| uuid/josnb | STRING | |
+#### CLICKHOUSE
+
+| ClickHouse Type | Doris Type | Comment |
+|------------------------|------------|-----------------------------------------------------|
+| Bool | BOOLEAN | |
+| String | STRING | |
+| Date/Date32 | DATE | |
+| DateTime/DateTime64 | DATETIME | 对于超过了Doris最大的DateTime精度的数据，将截断处理 |
+| Float32 | FLOAT | |
+| Float64 | DOUBLE | |
+| Int8 | TINYINT | |
+| Int16/UInt8 | SMALLINT | Doris没有UNSIGNED数据类型，所以扩大一个数量级 |
+| Int32/UInt16 | INT | Doris没有UNSIGNED数据类型，所以扩大一个数量级 |
+| Int64/Uint32 | BIGINT | Doris没有UNSIGNED数据类型，所以扩大一个数量级 |
+| Int128/UInt64 | LARGEINT | Doris没有UNSIGNED数据类型，所以扩大一个数量级 |
+| Int256/UInt128/UInt256 | STRING | Doris没有这个数量级的数据类型，采用STRING处理 |
+| DECIMAL | DECIMAL | 对于超过了Doris最大的Decimal精度的数据，将映射为STRING |
+| Enum/IPv4/IPv6/UUID | STRING | 在显示上IPv4,IPv6会额外在数据最前面显示一个`/`,需要自己用`split_part`函数处理 |
+
## 权限管理
使用 Doris 对 External Catalog 中库表进行访问，并不受外部数据目录自身的权限控制，而是依赖 Doris 自身的权限访问管理功能。
diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
index bbdf6491e1..81ee7b30cb 100644
--- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
@@ -174,7 +174,29 @@ CREATE CATALOG catalog_name PROPERTIES (
"jdbc.driver_url" = "file:/path/to/postgresql-42.5.1.jar",
"jdbc.driver_class" = "org.postgresql.Driver"
);
- ```
+ ```
+
+ **clickhouse**
+
+ ```sql
+ -- 1.2.0+ Version
+ CREATE RESOURCE clickhouse_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="default",
+ "password"="123456",
+ "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar",
+ "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
+ )
+ CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
+
+ -- 1.2.0 Version
+ CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
+ ...
+ )
+ ```
### Keywords
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
index 8a4e45968b..3862a38e3b 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
@@ -65,11 +65,12 @@ public class JdbcResource extends Resource {
public static final String JDBC_POSTGRESQL = "jdbc:postgresql";
public static final String JDBC_ORACLE = "jdbc:oracle";
public static final String JDBC_SQLSERVER = "jdbc:sqlserver";
-
+ public static final String JDBC_CLICKHOUSE = "jdbc:clickhouse";
public static final String MYSQL = "MYSQL";
public static final String POSTGRESQL = "POSTGRESQL";
public static final String ORACLE = "ORACLE";
private static final String SQLSERVER = "SQLSERVER";
+ public static final String CLICKHOUSE = "CLICKHOUSE";
public static final String JDBC_PROPERTIES_PREFIX = "jdbc.";
public static final String JDBC_URL = "jdbc_url";
@@ -234,6 +235,8 @@ public class JdbcResource extends Resource {
return ORACLE;
} else if (url.startsWith(JDBC_SQLSERVER)) {
return SQLSERVER;
+ } else if (url.startsWith(JDBC_CLICKHOUSE)) {
+ return CLICKHOUSE;
}
throw new DdlException("Unsupported jdbc database type, please check jdbcUrl: " + url);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
index 11ece6f620..fa10819a7b 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
@@ -163,6 +163,9 @@ public class JdbcClient {
rs = stmt.executeQuery("SELECT schema_name FROM information_schema.schemata "
+ "where schema_owner='" + jdbcUser + "';");
break;
+ case JdbcResource.CLICKHOUSE:
+ rs = stmt.executeQuery("SHOW DATABASES");
+ break;
default:
throw new JdbcClientException("Not supported jdbc type");
}
@@ -195,6 +198,9 @@ public class JdbcClient {
case JdbcResource.POSTGRESQL:
rs = databaseMetaData.getTables(null, dbName, null, types);
break;
+ case JdbcResource.CLICKHOUSE:
+ rs = databaseMetaData.getTables(null, dbName, null, types);
+ break;
default:
throw new JdbcClientException("Unknown database type");
}
@@ -222,6 +228,9 @@ public class JdbcClient {
case JdbcResource.POSTGRESQL:
rs = databaseMetaData.getTables(null, dbName, null, types);
break;
+ case JdbcResource.CLICKHOUSE:
+ rs = databaseMetaData.getTables(null, dbName, null, types);
+ break;
default:
throw new JdbcClientException("Unknown database type: " + dbType);
}
@@ -290,6 +299,9 @@ public class JdbcClient {
case JdbcResource.POSTGRESQL:
rs = databaseMetaData.getColumns(null, dbName, tableName, null);
break;
+ case JdbcResource.CLICKHOUSE:
+ rs = databaseMetaData.getColumns(null, dbName, tableName, null);
+ break;
default:
throw new JdbcClientException("Unknown database type");
}
@@ -320,6 +332,8 @@ public class JdbcClient {
return mysqlTypeToDoris(fieldSchema);
case JdbcResource.POSTGRESQL:
return postgresqlTypeToDoris(fieldSchema);
+ case JdbcResource.CLICKHOUSE:
+ return clickhouseTypeToDoris(fieldSchema);
default:
throw new JdbcClientException("Unknown database type");
}
@@ -486,6 +500,69 @@ public class JdbcClient {
}
}
+ public Type clickhouseTypeToDoris(JdbcFieldSchema fieldSchema) {
+ String ckType = fieldSchema.getDataTypeName();
+ if (ckType.startsWith("LowCardinality")) {
+ ckType = ckType.substring(15, ckType.length() - 1);
+ if (ckType.startsWith("Nullable")) {
+ ckType = ckType.substring(9, ckType.length() - 1);
+ }
+ } else if (ckType.startsWith("Nullable")) {
+ ckType = ckType.substring(9, ckType.length() - 1);
+ }
+ if (ckType.startsWith("Decimal")) {
+ String[] accuracy = ckType.substring(8, ckType.length() - 1).split(", ");
+ int precision = Integer.parseInt(accuracy[0]);
+ int scale = Integer.parseInt(accuracy[1]);
+ if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) {
+ if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) {
+ return ScalarType.createStringType();
+ }
+ return ScalarType.createDecimalType(precision, scale);
+ } else {
+ return ScalarType.createStringType();
+ }
+ } else if ("String".contains(ckType) || ckType.startsWith("Enum")
+ || ckType.startsWith("IPv") || "UUID".contains(ckType)
+ || ckType.startsWith("FixedString")) {
+ return ScalarType.createStringType();
+ } else if (ckType.startsWith("DateTime")) {
+ return ScalarType.getDefaultDateType(Type.DATETIME);
+ }
+ switch (ckType) {
+ case "Bool":
+ return Type.BOOLEAN;
+ case "Int8":
+ return Type.TINYINT;
+ case "Int16":
+ case "UInt8":
+ return Type.SMALLINT;
+ case "Int32":
+ case "UInt16":
+ return Type.INT;
+ case "Int64":
+ case "UInt32":
+ return Type.BIGINT;
+ case "Int128":
+ case "UInt64":
+ return Type.LARGEINT;
+ case "Int256":
+ case "UInt128":
+ case "UInt256":
+ return ScalarType.createStringType();
+ case "Float32":
+ return Type.FLOAT;
+ case "Float64":
+ return Type.DOUBLE;
+ case "Date":
+ case "Date32":
+ return ScalarType.getDefaultDateType(Type.DATE);
+ default:
+ return Type.UNSUPPORTED;
+ }
+ // Todo(zyk): Wait the JDBC external table support the array type then supported clickhouse array type
+ }
+
public List<Column> getColumnsFromJdbc(String dbName, String tableName) {
List<JdbcFieldSchema> jdbcTableSchema = getJdbcColumnsInfo(dbName, tableName);
List<Column> dorisTableSchema = Lists.newArrayListWithCapacity(jdbcTableSchema.size());
@@ -505,6 +582,8 @@ public class JdbcClient {
return JdbcResource.POSTGRESQL;
} else if (url.startsWith(JdbcResource.JDBC_ORACLE)) {
return JdbcResource.ORACLE;
+ } else if (url.startsWith(JdbcResource.JDBC_CLICKHOUSE)) {
+ return JdbcResource.CLICKHOUSE;
}
// else if (url.startsWith("jdbc:sqlserver")) {
// return SQLSERVER;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org