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"
)

remarks

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:

Type mapping

SQL Server to Doris type mapping

Query optimization

Statistics

Doris maintains table statistics in the Catalog so that it can better optimize query plans when executing queries.

See external-statistics to learn how to collect statistics.

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

1. Communication link abnormality occurs when reading SQL Server

   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

   You 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"