Working with Microsoft SQL Server databases
This section provides basic information about working with an external Microsoft SQL Server databases.
To work with an external Microsoft SQL Server database, you need to follow these steps:
-
Create a secret containing the password for connecting to the database.
CREATE OBJECT ms_sql_server_datasource_user_password (TYPE SECRET) WITH (value = "<password>");
-
Create an external data source that describes a specific Microsoft SQL Server database. The
LOCATION
parameter contains the network address of the Microsoft SQL Server instance to connect to. TheDATABASE_NAME
specifies the database name (for example,master
). TheLOGIN
andPASSWORD_SECRET_NAME
parameters are used for authentication to the external database. You can enable encryption for connections to the external database using theUSE_TLS="TRUE"
parameter.CREATE EXTERNAL DATA SOURCE ms_sql_server_datasource WITH ( SOURCE_TYPE="Microsoft SQL Server", LOCATION="<host>:<port>", DATABASE_NAME="<database>", AUTH_METHOD="BASIC", LOGIN="user", PASSWORD_SECRET_NAME="ms_sql_server_datasource_user_password", USE_TLS="TRUE" );
-
Deploy the connector and configure the YDB dynamic nodes to interact with it. Additionally, ensure network access from the YDB dynamic nodes to the external data source (at the address specified in the
LOCATION
parameter of theCREATE EXTERNAL DATA SOURCE
request). If network connection encryption to the external source was enabled in the previous step, the connector will use the system's root certificates (more details on TLS configuration can be found in the guide on deploying the connector). -
Execute a query to the database.
Query syntax
The following SQL query format is used to work with Microsoft SQL Server:
SELECT * FROM ms_sql_server_datasource.<table_name>
where:
ms_sql_server_datasource
- the external data source identifier;<table_name>
- the table name within the external data source.
Limitations
When working with Microsoft SQL Server clusters, there are a number of limitations:
-
External sources are available only for reading data through
SELECT
queries. The federated query processing engine currently does not support queries that modify tables in external sources. -
If the date value stored in the external data source is outside the allowed range for YDB (all dates used must be later than 1970-01-01 but earlier than 2105-12-31), such a value in YDB will be converted to
NULL
. -
The YDB federated query processing system is capable of delegating the execution of certain parts of a query to the system acting as the data source. Query fragments are passed through YDB directly to the external system and processed within it. This optimization, known as "predicate pushdown", significantly reduces the volume of data transferred from the source to the federated query processing engine. This reduces network load and saves computational resources for YDB.
A specific case of predicate pushdown, where filtering expressions specified after the
WHERE
keyword are passed down, is called "filter pushdown". Filter pushdown is possible when using:Description Example Filters like IS NULL
/IS NOT NULL
WHERE column1 IS NULL
orWHERE column1 IS NOT NULL
Logical conditions OR
,NOT
,AND
WHERE column IS NULL OR column2 is NOT NULL
Comparison conditions =
,<>
,<
,<=
,>
,>=
with other columns or constantsWHERE column3 > column4 OR column5 <= 10
Supported data types for filter pushdown:
YDB Data Type Bool
Int8
Int16
Int32
Int64
Float
Double
Supported data types
In the Microsoft SQL Server database, the optionality of column values (whether the column can contain NULL
values or not) is not a part of the data type system. The NOT NULL
constraint for any column of any table is stored within the IS_NULLABLE
column the INFORMATION_SCHEMA.COLUMNS system table, i.e., at the table metadata level. Therefore, all basic Microsoft SQL Server types can contain NULL
values by default, and in the YDB type system, they should be mapped to optional.
Below is a correspondence table between Microsoft SQL Server types and YDB types. All other data types, except those listed, are not supported.
Microsoft SQL Server Data Type | YDB Data Type | Notes |
---|---|---|
bit |
Optional<Bool> |
|
tinyint |
Optional<Int8> |
|
smallint |
Optional<Int16> |
|
int |
Optional<Int32> |
|
bigint |
Optional<Int64> |
|
real |
Optional<Float> |
|
float |
Optional<Double> |
|
date |
Optional<Date> |
Valid date range from 1970-01-01 to 2105-12-31. Values outside this range return NULL . |
smalldatetime |
Optional<Datetime> |
Valid time range from 1970-01-01 00:00:00 to 2105-12-31 23:59:59. Values outside this range return NULL . |
datetime |
Optional<Timestamp> |
Valid time range from 1970-01-01 00:00:00 to 2105-12-31 23:59:59. Values outside this range return NULL . |
datetime2 |
Optional<Timestamp> |
Valid time range from 1970-01-01 00:00:00 to 2105-12-31 23:59:59. Values outside this range return NULL . |
binary |
Optional<String> |
|
varbinary |
Optional<String> |
|
image |
Optional<String> |
|
char |
Optional<Utf8> |
|
varchar |
Optional<Utf8> |
|
text |
Optional<Utf8> |
|
nchar |
Optional<Utf8> |
|
nvarchar |
Optional<Utf8> |
|
ntext |
Optional<Utf8> |