This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new cf3ce61497f [improvement](jdbc catalog) support sqlserver timestamp
type read (#31805) (#31922)
cf3ce61497f is described below
commit cf3ce61497f36d3ff7f56727227fa60b1e2cf709
Author: zy-kkk <[email protected]>
AuthorDate: Thu Mar 7 18:58:39 2024 +0800
[improvement](jdbc catalog) support sqlserver timestamp type read (#31805)
(#31922)
---
.../sqlserver/init/03-create-table.sql | 4 ++
.../docker-compose/sqlserver/init/04-insert.sql | 2 +
docs/en/docs/lakehouse/multi-catalog/jdbc.md | 35 +++++++--------
docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md | 1 +
.../java/org/apache/doris/jdbc/JdbcExecutor.java | 50 ++++++++++++++++++++++
.../jdbc/client/JdbcSQLServerClient.java | 1 +
.../jdbc/test_sqlserver_jdbc_catalog.out | 7 +++
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 3 ++
8 files changed, 86 insertions(+), 17 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 cddd38f7863..b09a2ca47a6 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -134,3 +134,7 @@ CREATE TABLE dbo.all_type (
bit_value bit NULL
);
+CREATE TABLE dbo.test_timestamp (
+id_col int PRIMARY KEY NOT NULL,
+timestamp_col timestamp NULL
+);
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
index c18f629707d..930ad497dba 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -97,3 +97,5 @@ Insert into dbo.all_type values
0
),
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
+
+insert into dbo.test_timestamp(id_col) values(1);
diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
index 274ce7eb495..e956d364ec2 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -360,23 +360,24 @@ As for data mapping from SQLServer to Doris, one Database
in Doris corresponds t
#### Type Mapping
-| SQLServer Type | Doris Type | Comment
|
-|----------------------------------------|---------------|--------------------------------------------------------------------------|
-| bit | BOOLEAN |
|
-| tinyint | SMALLINT | SQLServer's tinyint
is an unsigned number, so it maps to Doris' 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 |
|
-| time/datetimeoffset | STRING |
|
-| Other | UNSUPPORTED |
|
+| SQLServer Type | Doris Type | Comment
|
+|----------------------------------------|---------------|-----------------------------------------------------------------------------------|
+| bit | BOOLEAN |
|
+| tinyint | SMALLINT | SQLServer's tinyint
is an unsigned number, so it maps to Doris' 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 |
|
+| time/datetimeoffset | STRING |
|
+| timestamp | STRING | Read the
hexadecimal display of binary data, which has no practical significance. |
+| Other | UNSUPPORTED |
|
### Doris
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index ec569d0017d..ca0ca42c788 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -377,6 +377,7 @@ CREATE CATALOG jdbc_sqlserve PROPERTIES (
| datetime/datetime2/smalldatetime | DATETIMEV2 |
|
| char/varchar/text/nchar/nvarchar/ntext | STRING |
|
| time/datetimeoffset | STRING |
|
+| timestamp | STRING |
读取二进制数据的十六进制显示，无实际意义 |
| Other | UNSUPPORTED |
|
### Doris
diff --git
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java
index 3cccd59faff..3011c5d82f7 100644
---
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java
+++
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java
@@ -1921,6 +1921,53 @@ public class JdbcExecutor {
return hexString.toString();
}
+ private void byteaPutToSQLServerString(Object[] column, boolean
isNullable, int numRows, long nullMapAddr,
+ long offsetsAddr, long charsAddr) {
+ int[] offsets = new int[numRows];
+ byte[][] byteRes = new byte[numRows][];
+ int offset = 0;
+ if (isNullable) {
+ for (int i = 0; i < numRows; i++) {
+ if (column[i] == null) {
+ byteRes[i] = emptyBytes;
+ UdfUtils.UNSAFE.putByte(nullMapAddr + i, (byte) 1);
+ } else {
+ byteRes[i] = sqlserverByteArrayToHexString((byte[])
column[i]).getBytes(StandardCharsets.UTF_8);
+ }
+ offset += byteRes[i].length;
+ offsets[i] = offset;
+ }
+ } else {
+ for (int i = 0; i < numRows; i++) {
+ byteRes[i] = sqlserverByteArrayToHexString((byte[])
column[i]).getBytes(StandardCharsets.UTF_8);
+ offset += byteRes[i].length;
+ offsets[i] = offset;
+ }
+ }
+ byte[] bytes = new byte[offsets[numRows - 1]];
+ long bytesAddr = JNINativeMethod.resizeStringColumn(charsAddr,
offsets[numRows - 1]);
+ int dst = 0;
+ for (int i = 0; i < numRows; i++) {
+ for (int j = 0; j < byteRes[i].length; j++) {
+ bytes[dst++] = byteRes[i][j];
+ }
+ }
+ UdfUtils.copyMemory(offsets, UdfUtils.INT_ARRAY_OFFSET, null,
offsetsAddr, numRows * 4L);
+ UdfUtils.copyMemory(bytes, UdfUtils.BYTE_ARRAY_OFFSET, null,
bytesAddr, offsets[numRows - 1]);
+ }
+
+ private String sqlserverByteArrayToHexString(byte[] bytes) {
+ StringBuilder hexString = new StringBuilder("0x");
+ for (byte b : bytes) {
+ String hex = Integer.toHexString(0xFF & b);
+ if (hex.length() == 1) {
+ hexString.append('0');
+ }
+ hexString.append(hex);
+ }
+ return hexString.toString();
+ }
+
public void copyBatchStringResult(Object columnObj, boolean isNullable,
int numRows, long nullMapAddr,
long offsetsAddr, long charsAddr) {
Object[] column = (Object[]) columnObj;
@@ -1945,6 +1992,9 @@ public class JdbcExecutor {
|| tableType == TOdbcTableType.OCEANBASE)) {
// for mysql bytea type
byteaPutToMySQLString(column, isNullable, numRows, nullMapAddr,
offsetsAddr, charsAddr);
+ } else if (column[firstNotNullIndex] instanceof byte[] && tableType ==
TOdbcTableType.SQLSERVER) {
+ // for sqlserver bytea type
+ byteaPutToSQLServerString(column, isNullable, numRows,
nullMapAddr, offsetsAddr, charsAddr);
} else if (column[firstNotNullIndex] instanceof oracle.sql.CLOB &&
tableType == TOdbcTableType.ORACLE) {
// for oracle clob type
oracleClobToString(column, isNullable, numRows, nullMapAddr,
offsetsAddr, charsAddr);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
index 8657b381b09..6ac3ca40e61 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
@@ -82,6 +82,7 @@ public class JdbcSQLServerClient extends JdbcClient {
case "time":
case "datetimeoffset":
case "uniqueidentifier":
+ case "timestamp":
return ScalarType.createStringType();
case "image":
case "binary":
diff --git
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
index 7624abc4589..d7c15da2c7f 100644
---
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
+++
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
@@ -107,6 +107,13 @@ bit_value BOOLEAN Yes false \N NONE
1 doris 18 0 1 1 123.123 123.123 123.123
12345678901234567890123456789012345678 12345678901234567890123456789012345678
1234567890123456789012345678.0123456789 1234567890123456789012345678.0123456789
Make Doris Great! Make Doris Great! Make Doris Great! Make
Doris Great! Make Doris Great! Make Doris Great! 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 Make Doris Great!
Make Doris Great! 922337203685477.5807 214748. [...]
2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
+-- !desc_timestamp --
+id_col INT No true \N
+timestamp_col TEXT Yes true \N
+
+-- !query_timestamp --
+1
+
-- !sql --
INFORMATION_SCHEMA
db_accessadmin
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
index 068f781fad6..f297e0b51b3 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -84,6 +84,9 @@ suite("test_sqlserver_jdbc_catalog",
"p0,external,sqlserver,external_docker,exte
order_qt_ctas """ create table
internal.${internal_db_name}.ctas_all_type PROPERTIES("replication_num" = "1")
as select * from all_type; """
qt_desc_query_ctas """ desc
internal.${internal_db_name}.ctas_all_type; """
order_qt_query_ctas """ select * from
internal.${internal_db_name}.ctas_all_type order by id; """
+ order_qt_desc_timestamp """desc dbo.test_timestamp; """
+ order_qt_query_timestamp """select count(timestamp_col) from
dbo.test_timestamp; """
+
sql """ drop catalog if exists ${catalog_name} """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
