---
metadata:
  - name: generator
    content: Diplodoc Platform v5.50.6
alternate:
  - https://ydb.tech/docs/en/concepts/query_execution/federated_query/ydb.md?version=main
  - https://ydb.tech/docs/ru/concepts/query_execution/federated_query/ydb.md?version=main
sourcePath: en/core/concepts/query_execution/federated_query/ydb.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Working with YDB databases

<!-- source: en/concepts/query_execution/federated_query/_includes/experimental_connectors_warning.md -->
{% note warning %}

External connectors are an experimental feature of YDB. To work with external DBMS through connectors, you need to deploy [fq-connector-go](https://ydb.tech/docs/en/devops/deployment-options/manual/federated-queries/connector-deployment.md?version=main#fq-connector-go) and explicitly enable the corresponding sources in the cluster configuration YDB. The functionality may change, so use in production is not recommended without thorough testing.

{% endnote %}
<!-- endsource: en/concepts/query_execution/federated_query/_includes/experimental_connectors_warning.md -->

YDB can act as an external data source for another YDB database. This section describes how to set up collaboration between two independent YDB databases in federated query processing mode.

To connect to an external YDB database from another YDB database that acts as a federated query processing engine, you need to perform the following steps on the latter:

1. Prepare authentication credentials to access the remote YDB database. Currently, in federated queries to YDB, the [login and password](https://ydb.tech/docs/en/security/authentication.md?version=main#static-credentials) authentication method is available (other methods are not supported). The password to the external database is stored as a [secret](https://ydb.tech/docs/en/concepts/datamodel/secrets.md?version=main):


   ```yql
    CREATE SECRET ydb_datasource_user_password WITH (value = "<password>");
   ```

2. Create an [external data source](https://ydb.tech/docs/en/concepts/datamodel/external_data_source.md?version=main) describing an external YDB database. The `LOCATION` parameter contains the network address of the YDB instance to which a network connection is made. In `DATABASE_NAME`, specify the database name (for example, `local`). For authentication to the external database, use the values of the `LOGIN` and `PASSWORD_SECRET_PATH` parameters. You can enable encryption of connections to the external database using the `USE_TLS="TRUE"` parameter. If encryption is enabled, then in the `<port>` field of the `LOCATION` parameter, you must specify the gRPCs port of the external YDB; otherwise, specify the gRPC port.


   ```yql
   CREATE EXTERNAL DATA SOURCE ydb_datasource WITH (
       SOURCE_TYPE="Ydb",
       LOCATION="<host>:<port>",
       DATABASE_NAME="<database>",
       AUTH_METHOD="BASIC",
       LOGIN="user",
       PASSWORD_SECRET_PATH="ydb_datasource_user_password",
       USE_TLS="TRUE"
   );
   ```

3. <!-- source: en/concepts/query_execution/federated_query/_includes/connector_deployment.md -->
   Deploy the [connector](https://ydb.tech/docs/en/concepts/query_execution/federated_query/architecture.md?version=main#connectors)  and [configure](https://ydb.tech/docs/en/devops/deployment-options/manual/federated-queries/index.md?version=main)  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 the `CREATE 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](https://ydb.tech/docs/en/devops/deployment-options/manual/federated-queries/connector-deployment.md?version=main) on deploying the connector.
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/connector_deployment.md -->
4. [Execute a query](#query) to an external data source.

## Query syntax {#query}

To retrieve data from tables of an external YDB database, use the following SQL query form:


```yql
SELECT * FROM ydb_datasource.`<table_name>`
```


where:

- `ydb_datasource`: external data source identifier
- `<table_name>` is the full table name within the [directory hierarchy](https://ydb.tech/docs/en/concepts/architecture/index.md?version=main#ydb-hierarchy) in the YDB database, for example, `table`, `dir1/table1`, or `dir1/dir2/table3`.

If the table is at the top level of the hierarchy (does not belong to any directory), you may omit the backticks around the table name "`":


```yql
SELECT * FROM ydb_datasource.<table_name>
```


## Limitations {#limitations}

When working with external YDB data sources, there are a number of limitations:

1. <!-- source: en/concepts/query_execution/federated_query/_includes/supported_requests.md -->
   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.
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/supported_requests.md -->
2. <!-- source: en/concepts/query_execution/federated_query/_includes/predicate_pushdown_preamble.md -->
   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 by them. This optimization, known as "predicate pushdown", significantly reduces the amount of data transferred from the source to the federated query processing engine. This reduces network load and saves computational resources for the federated YDB.

   A specific case of predicate pushdown, when the filtering expressions are specified after the `WHERE` keyword, are passed to the data source, is called "filter pushdown". Filter pushdown is possible when using:
   <!-- endsource: en/concepts/query_execution/federated_query/_includes/predicate_pushdown_preamble.md -->

   | Description | Example | Limitation |
   | --- | --- | --- |
   | Filters of the form `IS NULL`/`IS NOT NULL` | `WHERE column1 IS NULL` or `WHERE column1 IS NOT NULL` |  |
   | Logical conditions `OR`, `NOT`, `AND` and parentheses to control calculation priority. | `WHERE column1 IS NULL OR (column2 IS NOT NULL AND column3 > 10)`. |  |
   | [Comparison operators](../../../yql/reference/syntax/expressions.md#comparison-operators) with other columns or constants. | `WHERE column1 > column2 OR column3 <= 10`. |  |
   | Pattern matching operator `LIKE`. | `WHERE column1 LIKE '_abc%'` | Currently, only simple patterns based on prefixes (`'abc_'`, `'abc%'`), suffixes (`'_abc'`, `'%abc'`), or substring search (`'_abc_'`, `'%abc%'`, `'_abc%'`, `'%abc_'`) are supported for pushdown. If you need to push down more complex patterns, it is recommended to use `REGEXP`. |
   | String pattern matching operator `REGEXP`. | `WHERE column1 REGEXP '.*abc.*'` |  |

   When using other types of filters, pushdown to the source is not performed: filtering of the external table rows will be performed on the federated YDB side, which means that YDB will perform a full scan of the external table at the time of query processing.

   Supported data types for filter pushdown:

   | Data type YDB |
   | --- |
   | `Bool` |
   | `Int8` |
   | `Uint8` |
   | `Int16` |
   | `Uint16` |
   | `Int32` |
   | `Uint32` |
   | `Int64` |
   | `Uint64` |
   | `Float` |
   | `Double` |
   | `String` |
   | `Utf8` |

## Supported data types

When working with tables located in an external database YDB, users have access to a limited set of data types. All other types, except those listed below, are not supported. In some cases, type conversion is performed, meaning that columns of a table from the external database YDB change their type after the table is read by the database YDB processing the federated query.

| Data type of the external source YDB | Data type in the federated YDB |
| --- | --- |
| `Bool` | `Bool` |
| `Int8` | `Int8` |
| `Int16` | `Int16` |
| `Int32` | `Int32` |
| `Int64` | `Int64` |
| `Uint8` | `Uint8` |
| `Uint16` | `Uint16` |
| `Uint32` | `Uint32` |
| `Uint64` | `Uint64` |
| `Float` | `Float` |
| `Double` | `Double` |
| `String` | `String` |
| `Utf8` | `Utf8` |
| `Date` | `Date` |
| `Datetime` | `Datetime` |
| `Timestamp` | `Timestamp` |
| `Json` | `Json` |
| `JsonDocument` | `Json` |