You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by zy...@apache.org on 2023/06/26 15:10:53 UTC
[doris] branch master updated: [improvement](ms jdbc)Support for automatically obtaining the precision of the sqlserver datetime type (#21145)
This is an automated email from the ASF dual-hosted git repository.
zykkk 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 c9306e9c48 [improvement](ms jdbc)Support for automatically obtaining the precision of the sqlserver datetime type (#21145)
c9306e9c48 is described below
commit c9306e9c48bc66e58621aa4f8daf943f0a86d9a0
Author: zy-kkk <zh...@gmail.com>
AuthorDate: Mon Jun 26 23:10:46 2023 +0800
[improvement](ms jdbc)Support for automatically obtaining the precision of the sqlserver datetime type (#21145)
---
.../docker-compose/sqlserver/init/03-create-table.sql | 10 ++++++++++
.../docker-compose/sqlserver/init/04-insert.sql | 9 +++++++++
docs/en/docs/lakehouse/multi-catalog/jdbc.md | 17 ++++++++++++++---
docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 14 ++++++++++++--
.../doris/external/jdbc/JdbcSQLServerClient.java | 18 ++++++++++--------
.../jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out | 7 +++++--
.../jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy | 3 ++-
7 files changed, 62 insertions(+), 16 deletions(-)
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
index af5bcb483b..09b1cd06e7 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -86,4 +86,14 @@ CREATE TABLE dbo.test_binary (
varbinary_value varbinary(20) NULL
);
+CREATE TABLE dbo.DateAndTime
+(
+ DateColumn DATE,
+ TimeColumn TIME,
+ DateTimeColumn DATETIME,
+ SmallDateTimeColumn SMALLDATETIME,
+ DateTime2Column DATETIME2,
+ DateTimeOffsetColumn DATETIMEOFFSET
+);
+
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
index f1d8849897..4a9b4d2298 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -49,4 +49,13 @@ insert into dbo.test_binary values (2, 1, 0x4D616B6520446F72697320477265617421,
insert into dbo.test_binary values (3, -1, 0x4D616B6520446F72697320477265617421, 0x4D616B6520446F72697320477265617421);
+INSERT INTO dbo.DateAndTime
+VALUES (
+ '2023-06-25', -- DATE
+ '14:30:45', -- TIME
+ '2023-06-25T14:30:45', -- DATETIME
+ '2023-06-25T14:30:00', -- SMALLDATETIME
+ '2023-06-25T14:30:45.1234567', -- DATETIME2
+ '2023-06-25 14:30:45.1234567 -07:00' -- DATETIMEOFFSET
+);
diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
index 534b98bc3b..9e1d804928 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -595,7 +595,7 @@ When Doris connects to OceanBase, it will automatically recognize that OceanBase
Such errors occur because the `driver_class` has been wrongly put when creating the catalog. The problem with the above example is the letter case. It should be corrected as `"driver_class" = "com.mysql.jdbc.Driver"`.
-5. How to fix communication link failures?
+5. There is a communication link exception in reading MySQL
If you run into the following errors:
@@ -632,7 +632,7 @@ When Doris connects to OceanBase, it will automatically recognize that OceanBase
8. When using JDBC to query MySQL large data volume, if the query can occasionally succeed, occasionally report the following errors, and all the MySQL connections are completely disconnected when the error occurs:
- ```
+ ```
ERROR 1105 (HY000): errCode = 2, detailMessage = [INTERNAL_ERROR]UdfRuntimeException: JDBC executor sql has error:
CAUSED BY: CommunicationsException: Communications link failure
The last packet successfully received from the server was 4,446 milliseconds ago. The last packet sent successfully to the server was 4,446 milliseconds ago.
@@ -664,4 +664,15 @@ When Doris connects to OceanBase, it will automatically recognize that OceanBase
Or specify collation rules to be case sensitive when initializing the MYSQL database : character-set-server=UTF-8 and
- collation-server=utf8_bin.
\ No newline at end of file
+ collation-server=utf8_bin.
+
+10. There is a communication link exception in reading SQLServer
+
+ ```
+ ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.6)[CANCELLED][INTERNAL_ERROR]UdfRuntimeException: Initialize datasource failed:
+ CAUSED BY: SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption.
+ Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException:
+ unable to find valid certification path to requested target". ClientConnectionId:a92f3817-e8e6-4311-bc21-7c66
+ ```
+
+ In the create Catalog `jdbc_url` the JDBC connection string finally increase `encrypt=false`, such as `"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"`
\ No newline at end of file
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index 916eee62b6..b02180ce82 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -596,7 +596,7 @@ CREATE CATALOG jdbc_oceanbase PROPERTIES (
这是因为在创建 catalog 时，填写的driver_class不正确，需要正确填写，如上方例子为大小写问题，应填写为 `"driver_class" = "com.mysql.jdbc.Driver"`
-5. 读取 MySQL 问题出现通信链路异常
+5. 读取 MySQL 出现通信链路异常
如果出现如下报错：
@@ -660,4 +660,14 @@ CREATE CATALOG jdbc_oceanbase PROPERTIES (
在MYSQL中查询时添加“BINARY”关键字来强制区分大小写：select count(c_1) from table where BINARY c_1 = "aaa"; 或者在MYSQL中建表时候指定：
CREATE TABLE table ( c_1 VARCHAR(255) CHARACTER SET binary ); 或者在初始化MYSQL数据库时指定校对规则来区分大小写：
character-set-server=UTF-8 和 collation-server=utf8_bin。
-
\ No newline at end of file
+
+10. 读取 SQLServer 出现通信链路异常
+
+ ```
+ ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.6)[CANCELLED][INTERNAL_ERROR]UdfRuntimeException: Initialize datasource failed:
+ CAUSED BY: SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption.
+ Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException:
+ unable to find valid certification path to requested target". ClientConnectionId:a92f3817-e8e6-4311-bc21-7c66
+ ```
+
+ 可在创建 Catalog 的 `jdbc_url` 把JDBC连接串最后增加 `encrypt=false` ,如 `"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"`
\ No newline at end of file
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcSQLServerClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcSQLServerClient.java
index 6fe737634e..3d79cc6a9e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcSQLServerClient.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcSQLServerClient.java
@@ -56,21 +56,23 @@ public class JdbcSQLServerClient extends JdbcClient {
case "smallmoney":
return ScalarType.createDecimalV3Type(10, 4);
case "decimal":
- case "numeric":
+ case "numeric": {
int precision = fieldSchema.getColumnSize();
int scale = fieldSchema.getDecimalDigits();
return ScalarType.createDecimalV3Type(precision, scale);
+ }
case "date":
return ScalarType.createDateV2Type();
case "datetime":
- // datetime with millisecond precision
- return ScalarType.createDatetimeV2Type(3);
case "datetime2":
- // datetime2 with 100 nanoseconds precision, will lose precision
- return ScalarType.createDatetimeV2Type(6);
- case "smalldatetime":
- // smalldatetime with second precision
- return ScalarType.createDatetimeV2Type(0);
+ case "smalldatetime": {
+ // postgres can support microsecond
+ int scale = fieldSchema.getDecimalDigits();
+ if (scale > 6) {
+ scale = 6;
+ }
+ return ScalarType.createDatetimeV2Type(scale);
+ }
case "char":
case "varchar":
case "nchar":
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
index 4a9ca0ff32..7ad59d0b81 100644
--- a/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out
+++ b/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out
@@ -24,9 +24,9 @@
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
+1 2023-01-17 16:49:05.123 2023-01-17T16:49:05 2023-01-17T16:49:05.123456 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
+3 2023-01-17 16:49:05.123 2023-01-17T16:49:05 2023-01-17T16:49:05.123456 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 --
@@ -41,3 +41,6 @@
-- !test8 --
1 Make Doris Great! Make Doris Great!
+-- !dt --
+2023-06-25 14:30:45 2023-06-25T14:30:45 2023-06-25T14:30 2023-06-25T14:30:45.123456 2023-06-25 14:30:45.1234567 -07:00
+
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
index 5babedbd7c..932f40a8e0 100644
--- a/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy
+++ b/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy
@@ -31,7 +31,7 @@ suite("test_sqlserver_jdbc_catalog", "p0") {
"type"="jdbc",
"user"="SA",
"password"="Doris123456",
- "jdbc_url" = "jdbc:sqlserver://127.0.0.1:${sqlserver_port};DataBaseName=doris_test",
+ "jdbc_url" = "jdbc:sqlserver://127.0.0.1:${sqlserver_port};encrypt=false;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"
);"""
@@ -60,6 +60,7 @@ suite("test_sqlserver_jdbc_catalog", "p0") {
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; """
+ order_qt_dt """ select * from DateAndTime; """
sql """ drop catalog if exists ${catalog_name} """
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org