SQL Server
Doris JDBC Catalog supports connecting to SQL Server databases through the standard JDBC interface. This document describes how to configure a SQL Server database connection.
Terms and Conditions
To connect to a SQL Server database you need
SQL Server 2012 or later, or Azure SQL Database.
JDBC driver for SQL Server database, you can download the latest or specified version of SQL Server JDBC driver from Maven repository. It is recommended to use SQL Server JDBC Driver 11.2.x and above.
Doris Network connection between each FE and BE node and the SQL Server server, default port is 1433.
Connect to SQL Server
CREATE CATALOG sqlserver PROPERTIES (
"type"="jdbc",
"user"="root",
"password"="secret",
"jdbc_url" = "jdbc:sqlserver://<host>:<port>;databaseName=<databaseName>;encrypt=false",
"driver_url" = "mssql-jdbc-11.2.3.jre8.jar",
"driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
)
jdbc_url
defines the connection information and parameters to be passed to the SQL Server JDBC driver. URL support is provided in SQL Server JDBC Driver Documentation parameters.
Connection security
The JDBC driver and connector automatically use Transport Layer Security (TLS) encryption and certificate verification. This requires configuring the appropriate TLS certificate on the SQL Server database host.
If you have not set up the necessary configuration, you can disable encryption in the connection string using the encrypt attribute:
"jdbc_url"="jdbc:sqlserver://<host>:<port>;databaseName=<databaseName>;encrypt=false"
The TLS section of the SQL Server JDBC driver documentation describes trustServerCertificate in detail , hostNameInCertificate, trustStore and trustStorePassword and other parameters.
Hierarchical mapping
When mapping SQLServer, a Database in Doris corresponds to a Schema under the specified Database (<databaseName>
in the jdbc_url
parameter) in SQL Server. The Table under Doris' Database corresponds to the Tables under Schema in SQL Server. That is, the mapping relationship is as follows:
Doris | SQLServer |
---|---|
Catalog | Database |
Database | Schema |
Table | Table |
Type mapping
SQL Server to Doris type mapping
SQL Server Type | Doris Type | Comment |
---|---|---|
bit | BOOLEAN | |
tinyint | SMALLINT | SQLServer's tinyint is an unsigned number, so it is mapped to Doris's 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, no practical significance |
Other | UNSUPPORTED |
Query optimization
Predicate pushdown
When executing a query like where dt = '2022-01-01'
, Doris can push these filtering conditions down to the external data source, thereby directly excluding data that does not meet the conditions at the data source level, reducing inaccuracies. Necessary data acquisition and transmission. This greatly improves query performance while also reducing the load on external data sources.
Row limit
If you have the limit keyword in the query, Doris will escape the limit into SQL Server's TOP
syntax to reduce the amount of data transfer.
Escape characters
Doris will automatically add the escape character ([]) to the field names and table names in the query statements sent to SQL Server to avoid conflicts between field names, table names and SQL Server internal keywords.
FAQ
Certificate authentication exception occurs when connecting to SQL Server
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-7c66You can add
encrypt=false
to the end of the JDBC connection string when creating the Catalog, such as"jdbc_url" = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=doris_test;encrypt=false"
TLS exception occurs when connecting to SQL Server
The server selected protocol version TLS10 is not accepted by client preferences [TLS13, TLS12]
This is due to a TLS protocol version mismatch between SQL Server and the JDBC client. The connected SQL Server only supports TLS 1.0, and the JAVA environment where the JDBC client is located has TLS 1.0 disabled by default.
The solution is as follows:
Enable TLS 1.2 on SQL Server. Reference: SQL Server TLS 1.2 support
Enable TLS 1.0 for the JDK.
vim ${JAVA_HOME}/lib/security/java.security
#find this paragraph
jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \
DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \
include jdk.disabled.namedCurves
#Delete TLSv1, TLSv1.1 and change it to the following
jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, \
DH keySize < 1024, EC keySize < 224, anon, NULL, \
include jdk.disabled.namedCurves