This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new ba71516eba [feature](jdbc catalog) support SQLServer jdbc catalog
(#16093)
ba71516eba is described below
commit ba71516ebad2c10c28acd76b9154eb46b4b40974
Author: Tiewei Fang <[email protected]>
AuthorDate: Fri Jan 20 12:37:38 2023 +0800
[feature](jdbc catalog) support SQLServer jdbc catalog (#16093)
---
.../sqlserver/init/01-drop-database.sql | 18 +++++
.../sqlserver/init/02-create-database.sql | 18 +++++
.../sqlserver/init/03-create-table.sql | 89 ++++++++++++++++++++++
.../docker-compose/sqlserver/init/04-insert.sql | 52 +++++++++++++
.../docker-compose/sqlserver/sqlserver.env | 19 +++++
.../docker-compose/sqlserver/sqlserver.yaml | 47 ++++++++++++
docker/thirdparties/start-thirdparties-docker.sh | 7 ++
docker/thirdparties/stop-thirdparties-docker.sh | 3 +
.../Create/CREATE-CATALOG.md | 24 ++++++
docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 46 ++++++++++-
.../docs/lakehouse/multi-catalog/multi-catalog.md | 2 +-
.../Create/CREATE-CATALOG.md | 24 ++++++
.../org/apache/doris/catalog/JdbcResource.java | 2 +-
.../java/org/apache/doris/catalog/OdbcTable.java | 12 ++-
.../org/apache/doris/external/jdbc/JdbcClient.java | 78 ++++++++++++++-----
regression-test/conf/regression-conf.groovy | 1 +
.../test_sqlserver_jdbc_catalog.out | 43 +++++++++++
.../jdbc_catalog_p0/test_pg_jdbc_catalog.groovy | 10 +--
.../test_sqlserver_jdbc_catalog.groovy | 72 +++++++++++++++++
19 files changed, 538 insertions(+), 29 deletions(-)
diff --git
a/docker/thirdparties/docker-compose/sqlserver/init/01-drop-database.sql
b/docker/thirdparties/docker-compose/sqlserver/init/01-drop-database.sql
new file mode 100644
index 0000000000..60e0db7773
--- /dev/null
+++ b/docker/thirdparties/docker-compose/sqlserver/init/01-drop-database.sql
@@ -0,0 +1,18 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+drop database if exists doris_test;
\ No newline at end of file
diff --git
a/docker/thirdparties/docker-compose/sqlserver/init/02-create-database.sql
b/docker/thirdparties/docker-compose/sqlserver/init/02-create-database.sql
new file mode 100644
index 0000000000..c2e3414541
--- /dev/null
+++ b/docker/thirdparties/docker-compose/sqlserver/init/02-create-database.sql
@@ -0,0 +1,18 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+create database doris_test;
\ No newline at end of file
diff --git
a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
new file mode 100644
index 0000000000..af5bcb483b
--- /dev/null
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -0,0 +1,89 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+use doris_test;
+
+CREATE TABLE dbo.student (
+ id int PRIMARY KEY NOT NULL,
+ name varchar(10) NOT NULL,
+ age int NULL
+);
+
+CREATE TABLE dbo.test_int (
+ id int PRIMARY KEY NOT NULL,
+ tinyint_value tinyint NOT NULL,
+ smallint_value smallint NULL,
+ bigint_value bigint NULL
+);
+
+CREATE TABLE dbo.test_float (
+ id int PRIMARY KEY NOT NULL,
+ real_value real NOT NULL,
+ float_value float NULL,
+ floatn_value float(5) NULL,
+ decimal_value decimal(10,5) NULL,
+ numeric_value numeric(10,5) NULL
+);
+
+CREATE TABLE dbo.test_decimal (
+ id int PRIMARY KEY NOT NULL,
+ decimal_value decimal(38,0) NULL,
+ numeric_value numeric(38,0) NULL,
+ decimal_value2 decimal(38,10) NULL,
+ numeric_value2 numeric(38,10) NULL
+);
+
+CREATE TABLE dbo.test_char (
+ id int PRIMARY KEY NOT NULL,
+ char_value char(20) NOT NULL,
+ varchar_value varchar(20) NULL,
+ varcharmax_value varchar(max) NULL,
+ nchar_value nchar(20) NULL,
+ nvarchar_value nvarchar(20) NULL,
+ nvarcharmax_value nvarchar(max) NULL
+);
+
+CREATE TABLE dbo.test_time (
+ id int PRIMARY KEY NOT NULL,
+ date_value date NOT NULL,
+ time_value time NULL,
+ datetime_value datetime NULL,
+ datetime2_value datetime2 NULL,
+ smalldatetime_value smalldatetime NULL,
+ datetimeoffset_value datetimeoffset NULL
+);
+
+CREATE TABLE dbo.test_text (
+ id int PRIMARY KEY NOT NULL,
+ text_value text NOT NULL,
+ ntext_value ntext NULL
+);
+
+CREATE TABLE dbo.test_money (
+ id int PRIMARY KEY NOT NULL,
+ money_value money NOT NULL,
+ smallmoney_value smallmoney NULL
+);
+
+CREATE TABLE dbo.test_binary (
+ id int PRIMARY KEY NOT NULL,
+ bit_value bit NOT NULL,
+ binary_value binary(20) NULL,
+ varbinary_value varbinary(20) NULL
+);
+
+
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
new file mode 100644
index 0000000000..f1d8849897
--- /dev/null
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -0,0 +1,52 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+Insert into dbo.student values (1, 'doris', 18), (2, 'alice', 19), (3, 'bob',
20);
+
+Insert into dbo.test_int values
+(1, 0, 1, 1), (2, 1, -1, -1),
+(3, 255, 32767, 9223372036854775807), (4, 128, -32768, -9223372036854775808);
+
+Insert into dbo.test_float values
+(1, 123.123, 123.123, 123.123, 123.123, 123.123),
+(2, 12345.12345, 12345.12345, 12345.12345, 12345.12345,12345.12345),
+(3, -123.123, -123.123, -123.123, -123.123, -123.123);
+
+Insert into dbo.test_decimal values
+(1, 12345678901234567890123456789012345678,
12345678901234567890123456789012345678,
1234567890123456789012345678.0123456789,
1234567890123456789012345678.0123456789),
+(2, -12345678901234567890123456789012345678,
-12345678901234567890123456789012345678,
-1234567890123456789012345678.0123456789,
-1234567890123456789012345678.0123456789);
+
+Insert into dbo.test_char values
+(1, 'Make Doris Great!', 'Make Doris Great!', 'Make Doris Great!', 'Make Doris
Great!', 'Make Doris Great!', 'Make Doris Great!');
+
+Insert into dbo.test_time values (1, '2023-01-17', '16:49:05.1234567',
'2023-01-17 16:49:05', '2023-01-17 16:49:05.1234567', '2023-01-17 16:49:05',
'2023-01-17 16:49:05+08:00'),
+(2, '2023-01-17', '16:49:05', '2023-01-17 16:49:05', '2023-01-17 16:49:05',
'2023-01-17 16:49:05', '2023-01-17 16:49:05+08:00');
+Insert into dbo.test_time values (3, '2023-01-17', '16:49:05.1234567',
'2023-01-17 16:49:05', '2023-01-17 16:49:05.1234567', '2023-01-17 16:49:05',
'2023-01-17 16:49:05.1234567+08:00');
+Insert into dbo.test_time values (4, '2023-01-17', '16:49:05', '2023-01-17
16:49:05', '2023-01-17 16:49:05', '2023-01-17 16:49:05', '2023-01-17
16:49:05+08:00');
+
+Insert into dbo.test_text values (1, 'Make Doris Great!', 'Make Doris Great!');
+
+Insert into dbo.test_money values (1, 922337203685477.5807, 214748.3647);
+Insert into dbo.test_money values (2, -922337203685477.5808, -214748.3648);
+Insert into dbo.test_money values (3, 123.123, 123.123);
+
+insert into dbo.test_binary values (1, 0,
0x4D616B6520446F72697320477265617421, 0x4D616B6520446F72697320477265617421);
+insert into dbo.test_binary values (2, 1,
0x4D616B6520446F72697320477265617421, 0x4D616B6520446F72697320477265617421);
+insert into dbo.test_binary values (3, -1,
0x4D616B6520446F72697320477265617421, 0x4D616B6520446F72697320477265617421);
+
+
+
diff --git a/docker/thirdparties/docker-compose/sqlserver/sqlserver.env
b/docker/thirdparties/docker-compose/sqlserver/sqlserver.env
new file mode 100644
index 0000000000..0aa395abdf
--- /dev/null
+++ b/docker/thirdparties/docker-compose/sqlserver/sqlserver.env
@@ -0,0 +1,19 @@
+#!/usr/bin/env bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+DOCKER_SQLSERVER_EXTERNAL_PORT=1433
\ No newline at end of file
diff --git a/docker/thirdparties/docker-compose/sqlserver/sqlserver.yaml
b/docker/thirdparties/docker-compose/sqlserver/sqlserver.yaml
new file mode 100644
index 0000000000..02f909f5d5
--- /dev/null
+++ b/docker/thirdparties/docker-compose/sqlserver/sqlserver.yaml
@@ -0,0 +1,47 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+version: '3'
+services:
+ doris--sqlserver_2022:
+ image: "mcr.microsoft.com/mssql/server:2022-latest"
+ container_name: "doris--sqlserver_2022"
+ ports:
+ - ${DOCKER_SQLSERVER_EXTERNAL_PORT}:1433
+ healthcheck:
+ test: ["CMD", "/opt/mssql-tools/bin/sqlcmd", "-Usa", "-PDoris123456",
"-Q", "select 1"]
+ interval: 5s
+ timeout: 30s
+ retries: 120
+ volumes:
+ - ./init:/docker-entrypoint-initdb.d
+ restart: always
+ environment:
+ # Accept the end user license Agreement
+ - ACCEPT_EULA=Y
+ # password of SA
+ - SA_PASSWORD=Doris123456
+ networks:
+ - doris--sqlserver_2022
+ hello-world:
+ image: hello-world
+ depends_on:
+ doris--sqlserver_2022:
+ condition: service_healthy
+
+networks:
+ doris--sqlserver_2022:
\ No newline at end of file
diff --git a/docker/thirdparties/start-thirdparties-docker.sh
b/docker/thirdparties/start-thirdparties-docker.sh
index 61e0df9713..e25be54711 100755
--- a/docker/thirdparties/start-thirdparties-docker.sh
+++ b/docker/thirdparties/start-thirdparties-docker.sh
@@ -63,6 +63,13 @@ sudo mkdir -p "${ROOT}"/docker-compose/oracle/data/
sudo rm "${ROOT}"/docker-compose/oracle/data/* -rf
sudo docker compose -f "${ROOT}"/docker-compose/oracle/oracle-11.yaml
--env-file "${ROOT}"/docker-compose/oracle/oracle-11.env up -d
+# sqlserver
+sed -i "s/doris--/${CONTAINER_UID}/g"
"${ROOT}"/docker-compose/sqlserver/sqlserver.yaml
+sudo docker compose -f "${ROOT}"/docker-compose/sqlserver/sqlserver.yaml
--env-file "${ROOT}"/docker-compose/sqlserver/sqlserver.env down
+sudo mkdir -p "${ROOT}"/docker-compose/sqlserver/data/
+sudo rm "${ROOT}"/docker-compose/sqlserver/data/* -rf
+sudo docker compose -f "${ROOT}"/docker-compose/sqlserver/sqlserver.yaml
--env-file "${ROOT}"/docker-compose/sqlserver/sqlserver.env up -d
+
# hive
# before start it, you need to download parquet file package, see "README" in
"docker-compose/hive/scripts/"
sed -i "s/doris--/${CONTAINER_UID}/g"
"${ROOT}"/docker-compose/hive/hive-2x.yaml
diff --git a/docker/thirdparties/stop-thirdparties-docker.sh
b/docker/thirdparties/stop-thirdparties-docker.sh
index dc2c5773d8..963bd1e85d 100755
--- a/docker/thirdparties/stop-thirdparties-docker.sh
+++ b/docker/thirdparties/stop-thirdparties-docker.sh
@@ -36,5 +36,8 @@ sudo docker compose -f
"${ROOT}"/docker-compose/postgresql/postgresql-14.yaml --
# oracle 11
sudo docker compose -f "${ROOT}"/docker-compose/oracle/oracle-11.yaml
--env-file "${ROOT}"/docker-compose/oracle/oracle-11.env down
+# sqlserver
+sudo docker compose -f "${ROOT}"/docker-compose/sqlserver/sqlserver.yaml
--env-file "${ROOT}"/docker-compose/sqlserver/sqlserver.env down
+
# hive
sudo docker compose -f "${ROOT}"/docker-compose/hive/hive-2x.yaml --env-file
"${ROOT}"/docker-compose/hive/hadoop-hive.env down
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 b92f712dca..2cc2e03eaa 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
@@ -216,6 +216,30 @@ CREATE CATALOG catalog_name PROPERTIES (
);
```
+ **SQLServer**
+ ```sql
+ -- The first way
+ CREATE RESOURCE sqlserver_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="SA",
+ "password"="Doris123456",
+ "jdbc_url" =
"jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
+ "driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
+ "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
+ );
+ CREATE CATALOG sqlserver_catlog WITH RESOURCE sqlserver_resource;
+
+ -- The second way, note: keys have 'jdbc' prefix in front.
+ CREATE CATALOG sqlserver_catlog PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="SA",
+ "jdbc.password"="Doris123456",
+ "jdbc.jdbc_url" =
"jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
+ "jdbc.driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
+ "jdbc.driver_class" =
"com.microsoft.sqlserver.jdbc.SQLServerDriver"
+ );
+ ```
+
### Keywords
CREATE, CATALOG
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index 7abaf0a369..f6654eef36 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -33,7 +33,7 @@ JDBC Catalog 通过标准 JDBC 协议，连接其他数据源。
## 使用限制
-1. 支持 MySQL、PostgreSQL、Oracle、Clickhouse
+1. 支持 MySQL、PostgreSQL、Oracle、SQLServer、Clickhouse
## 创建 Catalog
@@ -71,7 +71,6 @@ JDBC Catalog 通过标准 JDBC 协议，连接其他数据源。
| Database | Schema |
| Table | Table |
-
3. Oracle
```sql
@@ -107,6 +106,27 @@ JDBC Catalog 通过标准 JDBC 协议，连接其他数据源。
);
```
+5. SQLServer
+
+ ```sql
+ 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"
+ );
+ ```
+
+ 映射 SQLServer 时，Doris 的一个 Database 对应于 SQLServer 中指定 Database（如示例中
`jdbc_url` 参数中的 "doris_test"）下的一个 Schema。而 Doris 的 Database 下的 Table 则对应于
SQLServer 中，Schema 下的 Tables。即映射关系如下：
+
+ |Doris | SQLServer |
+ |---|---|
+ | Catalog | Database |
+ | Database | Schema |
+ | Table | Table |
+
### 参数说明
参数 | 是否必须 | 默认值 | 说明
@@ -182,7 +202,7 @@ JDBC Catalog 通过标准 JDBC 协议，连接其他数据源。
### Oracle
- ORACLE Type | Doris Type | Comment |
+| ORACLE Type | Doris Type | Comment |
|---|---|---|
| number(p) / number(p,0) | | Doris会根据p的大小来选择对应的类型：`p < 3` -> `TINYINT`; `p <
5` -> `SMALLINT`; `p < 10` -> `INT`; `p < 19` -> `BIGINT`; `p > 19` ->
`LARGEINT` |
| number(p,s) | DECIMAL | |
@@ -194,6 +214,26 @@ JDBC Catalog 通过标准 JDBC 协议，连接其他数据源。
| LONG/ RAW/ LONG RAW/ INTERVAL | STRING | |
|Other| UNSUPPORTED |
+### SQLServer
+
+| SQLServer Type | Doris Type | Comment |
+|---|---|---|
+| bit | BOOLEAN | |
+| tinyint | SMALLINT | SQLServer的tinyint是无符号数，所以映射为Doris的SMALLINT |
+| smallint | SMALLINT | |
+| int | INT | |
+| bigint | BIGINT | |
+| real | FLOAT | |
+| float/money/smallmoney | DOUBLE | |
+| 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
|
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/multi-catalog.md
b/docs/zh-CN/docs/lakehouse/multi-catalog/multi-catalog.md
index 797f8f42b3..ffd96fd19c 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/multi-catalog.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/multi-catalog.md
@@ -301,7 +301,7 @@ select k1, k4 from table; // Query OK.
使用 Doris 对 External Catalog 中库表进行访问，并不受外部数据目录自身的权限控制，而是依赖 Doris 自身的权限访问管理功能。
-Doris 的权限管理功能提供了对 Cataloig 层级的扩展，具体可参阅
[权限管理](../../admin-manual/privilege-ldap/user-privilege.md) 文档。
+Doris 的权限管理功能提供了对 Catalog 层级的扩展，具体可参阅
[权限管理](../../admin-manual/privilege-ldap/user-privilege.md) 文档。
## 元数据更新
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 9c71ee73a8..65b3417302 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
@@ -222,6 +222,30 @@ CREATE CATALOG catalog_name PROPERTIES (
);
```
+ **SQLServer**
+ ```sql
+ -- 方式一
+ CREATE RESOURCE sqlserver_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="SA",
+ "password"="Doris123456",
+ "jdbc_url" =
"jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
+ "driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
+ "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
+ );
+ CREATE CATALOG sqlserver_catlog WITH RESOURCE sqlserver_resource;
+
+ -- 方式二，注意有jdbc前缀
+ CREATE CATALOG sqlserver_catlog PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="SA",
+ "jdbc.password"="Doris123456",
+ "jdbc.jdbc_url" =
"jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
+ "jdbc.driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
+ "jdbc.driver_class" =
"com.microsoft.sqlserver.jdbc.SQLServerDriver"
+ );
+ ```
+
### Keywords
CREATE, CATALOG
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 afdd099702..c02666d928 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
@@ -70,7 +70,7 @@ public class JdbcResource extends Resource {
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 SQLSERVER = "SQLSERVER";
public static final String CLICKHOUSE = "CLICKHOUSE";
public static final String JDBC_PROPERTIES_PREFIX = "jdbc.";
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
index 562f4d7807..5372c56312 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OdbcTable.java
@@ -88,7 +88,17 @@ public class OdbcTable extends Table {
}
private static String mssqlProperName(String name) {
- return "[" + name + "]";
+ // In JdbcExternalTable, the name contains databaseName, like: db.table
+ // So, we should split db and table, then switch to [db].[table].
+ String[] fields = name.split("\\.");
+ String result = "";
+ for (int i = 0; i < fields.length; ++i) {
+ if (i != 0) {
+ result += ".";
+ }
+ result += ("[" + fields[i] + "]");
+ }
+ return result;
}
private static String psqlProperName(String name) {
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 6f420246c5..8f1a5d3a70 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
@@ -23,6 +23,7 @@ import org.apache.doris.catalog.PrimitiveType;
import org.apache.doris.catalog.ScalarType;
import org.apache.doris.catalog.Type;
import org.apache.doris.common.Config;
+import org.apache.doris.common.DdlException;
import com.google.common.collect.Lists;
import com.zaxxer.hikari.HikariConfig;
@@ -57,8 +58,11 @@ public class JdbcClient {
public JdbcClient(String user, String password, String jdbcUrl, String
driverUrl, String driverClass) {
this.jdbcUser = user;
- this.dbType = parseDbType(jdbcUrl);
-
+ try {
+ this.dbType = JdbcResource.parseDbType(jdbcUrl);
+ } catch (DdlException e) {
+ throw new JdbcClientException("Failed to parse db type from
jdbcUrl: " + jdbcUrl, e);
+ }
ClassLoader oldClassLoader =
Thread.currentThread().getContextClassLoader();
try {
// TODO(ftw): The problem here is that the jar package is handled
by FE
@@ -158,6 +162,9 @@ public class JdbcClient {
case JdbcResource.ORACLE:
rs = stmt.executeQuery("SELECT DISTINCT OWNER FROM
all_tables");
break;
+ case JdbcResource.SQLSERVER:
+ rs = stmt.executeQuery("SELECT name FROM sys.schemas");
+ break;
default:
throw new JdbcClientException("Not supported jdbc type");
}
@@ -190,6 +197,7 @@ public class JdbcClient {
case JdbcResource.POSTGRESQL:
case JdbcResource.ORACLE:
case JdbcResource.CLICKHOUSE:
+ case JdbcResource.SQLSERVER:
rs = databaseMetaData.getTables(null, dbName, null, types);
break;
default:
@@ -219,6 +227,7 @@ public class JdbcClient {
case JdbcResource.POSTGRESQL:
case JdbcResource.ORACLE:
case JdbcResource.CLICKHOUSE:
+ case JdbcResource.SQLSERVER:
rs = databaseMetaData.getTables(null, dbName, null, types);
break;
default:
@@ -289,6 +298,7 @@ public class JdbcClient {
case JdbcResource.POSTGRESQL:
case JdbcResource.ORACLE:
case JdbcResource.CLICKHOUSE:
+ case JdbcResource.SQLSERVER:
rs = databaseMetaData.getColumns(null, dbName, tableName,
null);
break;
default:
@@ -325,6 +335,8 @@ public class JdbcClient {
return clickhouseTypeToDoris(fieldSchema);
case JdbcResource.ORACLE:
return oracleTypeToDoris(fieldSchema);
+ case JdbcResource.SQLSERVER:
+ return sqlserverTypeToDoris(fieldSchema);
default:
throw new JdbcClientException("Unknown database type");
}
@@ -609,6 +621,52 @@ public class JdbcClient {
}
}
+ public Type sqlserverTypeToDoris(JdbcFieldSchema fieldSchema) {
+ String sqlserverType = fieldSchema.getDataTypeName();
+ switch (sqlserverType) {
+ case "bit":
+ return Type.BOOLEAN;
+ case "tinyint":
+ case "smallint":
+ return Type.SMALLINT;
+ case "int":
+ return Type.INT;
+ case "bigint":
+ return Type.BIGINT;
+ case "real":
+ return Type.FLOAT;
+ case "float":
+ case "money":
+ case "smallmoney":
+ return Type.DOUBLE;
+ case "decimal":
+ case "numeric":
+ int precision = fieldSchema.getColumnSize();
+ int scale = fieldSchema.getDecimalDigits();
+ return ScalarType.createDecimalV3Type(precision, scale);
+ case "date":
+ return ScalarType.getDefaultDateType(Type.DATE);
+ case "datetime":
+ case "datetime2":
+ case "smalldatetime":
+ return ScalarType.createDatetimeV2Type(6);
+ case "char":
+ case "varchar":
+ case "nchar":
+ case "nvarchar":
+ case "text":
+ case "ntext":
+ case "time":
+ case "datetimeoffset":
+ return ScalarType.createStringType();
+ case "image":
+ case "binary":
+ case "varbinary":
+ default:
+ return Type.UNSUPPORTED;
+ }
+ }
+
public List<Column> getColumnsFromJdbc(String dbName, String tableName) {
List<JdbcFieldSchema> jdbcTableSchema = getJdbcColumnsInfo(dbName,
tableName);
List<Column> dorisTableSchema =
Lists.newArrayListWithCapacity(jdbcTableSchema.size());
@@ -620,20 +678,4 @@ public class JdbcClient {
}
return dorisTableSchema;
}
-
- private String parseDbType(String url) {
- if (url.startsWith(JdbcResource.JDBC_MYSQL) ||
url.startsWith(JdbcResource.JDBC_MARIADB)) {
- return JdbcResource.MYSQL;
- } else if (url.startsWith(JdbcResource.JDBC_POSTGRESQL)) {
- 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;
- // }
- throw new JdbcClientException("Unsupported jdbc database type, please
check jdbcUrl: " + url);
- }
}
diff --git a/regression-test/conf/regression-conf.groovy
b/regression-test/conf/regression-conf.groovy
index d05791ff7d..6babba3cc4 100644
--- a/regression-test/conf/regression-conf.groovy
+++ b/regression-test/conf/regression-conf.groovy
@@ -74,6 +74,7 @@ enableJdbcTest=false
mysql_57_port=3316
pg_14_port=5442
oracle_11_port=1521
+sqlserver_2022_port=1433
// hive catalog test config
// To enable jdbc test, you need first start hive container.
diff --git
a/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out
b/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out
new file mode 100644
index 0000000000..3768e8b9a6
--- /dev/null
+++ b/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out
@@ -0,0 +1,43 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test0 --
+1 doris 18
+2 alice 19
+3 bob 20
+
+-- !in_tb --
+1 doris 18
+2 alice 19
+3 bob 20
+
+-- !test1 --
+1 0 1 1
+2 1 -1 -1
+3 255 32767 9223372036854775807
+4 128 -32768 -9223372036854775808
+
+-- !test2 --
+1 123.123 123.123 123.123 123.12300 123.12300
+2 12345.123 12345.12345 12345.123 12345.12345
12345.12345
+3 -123.123 -123.123 -123.123 -123.12300
-123.12300
+
+-- !test3 --
+1 Make Doris Great! Make Doris Great! Make Doris Great!
Make Doris Great! Make Doris Great! Make Doris Great!
+
+-- !test5 --
+1 2023-01-17 16:49:05 2023-01-17T16:49:05
2023-01-17T16:49:05 2023-01-17T16:49 2023-01-17 16:49:05 +08:00
+2 2023-01-17 16:49:05 2023-01-17T16:49:05
2023-01-17T16:49:05 2023-01-17T16:49 2023-01-17 16:49:05 +08:00
+3 2023-01-17 16:49:05 2023-01-17T16:49:05
2023-01-17T16:49:05 2023-01-17T16:49 2023-01-17 16:49:05.1234567
+08:00
+4 2023-01-17 16:49:05 2023-01-17T16:49:05
2023-01-17T16:49:05 2023-01-17T16:49 2023-01-17 16:49:05 +08:00
+
+-- !test6 --
+1 9.223372036854776E14 214748.3647
+2 -9.223372036854776E14 -214748.3648
+3 123.123 123.123
+
+-- !test7 --
+1 12345678901234567890123456789012345678
12345678901234567890123456789012345678 1234567890123456789012345678.0123456789
1234567890123456789012345678.0123456789
+2 -12345678901234567890123456789012345678
-12345678901234567890123456789012345678
-1234567890123456789012345678.0123456789
-1234567890123456789012345678.0123456789
+
+-- !test8 --
+1 Make Doris Great! Make Doris Great!
+
diff --git a/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy
b/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy
index 629761bfb3..71462cc307 100644
--- a/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy
+++ b/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy
@@ -38,7 +38,7 @@ suite("test_pg_jdbc_catalog", "p0") {
"driver_class" = "org.postgresql.Driver"
);"""
- sql """CREATE CATALOG ${catalog_name} WITH RESOURCE
jdbc_resource_catalog_pg"""
+ sql """CREATE CATALOG ${catalog_name} WITH RESOURCE ${resource_name}"""
sql """ drop table if exists ${inDorisTable} """
sql """
@@ -74,7 +74,7 @@ suite("test_pg_jdbc_catalog", "p0") {
order_qt_test14 """ select * from test12 order by id; """
sql """drop catalog if exists ${catalog_name} """
- sql """drop resource if exists jdbc_resource_catalog_pg"""
+ sql """drop resource if exists ${resource_name}"""
// test old create-catalog syntax for compatibility
sql """ CREATE CATALOG ${catalog_name} PROPERTIES (
@@ -86,9 +86,9 @@ suite("test_pg_jdbc_catalog", "p0") {
"jdbc.driver_class" = "org.postgresql.Driver");
"""
- sql """switch ${catalog_name}"""
- sql """use ${ex_schema_name}"""
+ sql """ switch ${catalog_name} """
+ sql """ use ${ex_schema_name} """
order_qt_test_old """ select * from test3 order by id; """
- sql """drop resource if exists jdbc_resource_catalog_pg"""
+ sql """ drop catalog if exists ${catalog_name} """
}
}
diff --git
a/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy
new file mode 100644
index 0000000000..4301a80b29
--- /dev/null
+++ b/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy
@@ -0,0 +1,72 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_sqlserver_jdbc_catalog", "p0") {
+ String enabled = context.config.otherConfigs.get("enableJdbcTest");
+ if (enabled != null && enabled.equalsIgnoreCase("true")) {
+ String resource_name = "sqlserver_catalog_resource";
+ String catalog_name = "sqlserver_catalog";
+ String internal_db_name = "regression_test_jdbc_catalog_p0";
+ String ex_db_name = "dbo";
+ String sqlserver_port =
context.config.otherConfigs.get("sqlserver_2022_port");
+
+ String inDorisTable = "doris_in_tb";
+
+ sql """ drop catalog if exists ${catalog_name} """
+ sql """ drop resource if exists ${resource_name} """
+
+ sql """ create resource if not exists ${resource_name} properties(
+ "type"="jdbc",
+ "user"="SA",
+ "password"="Doris123456",
+ "jdbc_url" =
"jdbc:sqlserver://127.0.0.1:${sqlserver_port};DataBaseName=doris_test",
+ "driver_url" =
"https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mssql-jdbc-11.2.3.jre8.jar";,
+ "driver_class" =
"com.microsoft.sqlserver.jdbc.SQLServerDriver"
+ );"""
+
+ sql """ CREATE CATALOG ${catalog_name} WITH RESOURCE ${resource_name}
"""
+
+ sql """ drop table if exists ${inDorisTable} """
+ sql """
+ CREATE TABLE ${inDorisTable} (
+ `id` INT NULL COMMENT "主键id",
+ `name` string NULL COMMENT "名字",
+ `age` INT NULL COMMENT "年龄"
+ ) DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES("replication_num" = "1");
+ """
+
+ sql """ switch ${catalog_name} """
+ sql """ use ${ex_db_name} """
+
+ order_qt_test0 """ select * from student order by id; """
+ sql """ insert into internal.${internal_db_name}.${inDorisTable}
select * from student; """
+ order_qt_in_tb """ select id, name, age from
internal.${internal_db_name}.${inDorisTable} order by id; """
+
+ order_qt_test1 """ select * from test_int order by id; """
+ order_qt_test2 """ select * from test_float order by id; """
+ order_qt_test3 """ select * from test_char order by id; """
+ order_qt_test5 """ select * from test_time order by id; """
+ order_qt_test6 """ select * from test_money order by id; """
+ order_qt_test7 """ select * from test_decimal order by id; """
+ order_qt_test8 """ select * from test_text order by id; """
+
+
+ sql """ drop catalog if exists ${catalog_name} """
+ sql """ drop resource if exists ${resource_name} """
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
