Working with Managed Service for YDB databases
This section provides the basic information about working with Managed Service for YDB.
To work with a Managed Service for YDB database from Yandex Query, follow these steps:
- Create a connection containing your database connection credentials.
- Run a query to the database.
Example of a query for reading data from Managed Service for YDB:
SELECT * FROM ydb_connection.my_table
Where:
ydb_connection
: Name of the DB connection you created.my_table
: Name of the table in the database.
Setting up a connection
To create a connection to Managed Service for YDB:
-
In the management console
, select the folder where you want to create a connection. -
In the list of services, select Yandex Query.
-
In the left-hand panel, go to the Connections tab.
-
Click
Create new. -
Specify the connection parameters:
-
Under General parameters:
- Name: Name of the connection to Managed Service for YDB.
- Type:
Managed Service for YDB
.
-
Under Connection type parameters:
-
Cluster: Select an existing Managed Service for YDB database or create a new one.
-
Service account: Select the Managed Service for YDB service account to use for connecting to
Managed Service for YDB
clusters and database authentication. If you do not have a service account, create one and assign theydb.viewer
role to it.To use a service account, the
iam.serviceAccounts.user
role is required.
-
-
-
Click Create.
Query syntax
Here is the SQL query format used to access Managed Service for YDB:
SELECT * FROM <connection>.<table_name>
Where:
<connection>
: Name of the DB connection you created.<table_name>
: Name of the table in the database.
Limitations
External sources are only available for data reading through SELECT
requests. Yandex Query does not currently support requests that modify tables in external sources.
Filter pushdown
Yandex Query can transfer processing of request parts to the data source system. This means that filter expressions are provided through Yandex Query directly to the database for processing; these are typically the query conditions specified in WHERE
. This kind of processing is called filter pushdown
.
Filter pushdown is possible when using:
Description | Example |
---|---|
Filters like IS NULL /IS NOT NULL |
WHERE column1 IS NULL or WHERE column1 IS NOT NULL |
Logical conditions OR , NOT , or AND |
WHERE column IS NULL OR column2 is NOT NULL |
Conditions = , <> , < , <= , > , >= for comparison with other columns or constants |
WHERE column3 > column4 OR column5 <= 10 |
Supported data types for filter pushdown:
Data type Yandex Query |
---|
Bool |
Int8 |
Uint8 |
Int16 |
Uint16 |
Int32 |
Uint32 |
Int64 |
Uint64 |
Float |
Double |
String |
Utf8 |
Supported data types
The tables below show how Managed Service for YDB and Yandex Query data types map. Data types not listed in the tables are not supported.
Primitive data types
Data type Managed Service for YDB | Data type Yandex Query |
---|---|
Bool |
Bool |
Int8 |
Int8 |
Uint8 |
Uint8 |
Int16 |
Int16 |
Uint16 |
Uint16 |
Int32 |
Int32 |
Uint32 |
Uint32 |
Int64 |
Int64 |
Uint64 |
Uint64 |
Float |
Float |
Double |
Double |
Date |
Date |
Datetime |
Datetime |
Timestamp |
Timestamp |
String |
String |
Utf8 |
Utf8 |
Json |
Json |
Optional data types
Data type Managed Service for YDB | Data type Yandex Query |
---|---|
Optional<Bool> |
Optional<Bool> |
Optional<Int8> |
Optional<Int8> |
Optional<Uint8> |
Optional<Uint8> |
Optional<Int16> |
Optional<Int16> |
Optional<Uint16> |
Optional<Uint16> |
Optional<Int32> |
Optional<Int32> |
Optional<Uint32> |
Optional<Uint32> |
Optional<Int64> |
Optional<Int64> |
Optional<Uint64> |
Optional<Uint64> |
Optional<Float> |
Optional<Float> |
Optional<Double> |
Optional<Double> |
Optional<Date> |
Optional<Date> |
Optional<Datetime> |
Optional<Datetime> |
Optional<Timestamp> |
Optional<Timestamp> |
Optional<String> |
Optional<String> |
Optional<Utf8> |
Optional<Utf8> |
Optional<Json> |
Optional<Json> |