[Yandex Cloud documentation](../../../index.md) > [Yandex MPP Analytics for PostgreSQL](../../index.md) > [Step-by-step guides](../index.md) > Working with PXF > Creating external data sources > JDBC

# Creating an external JDBC data source

In Yandex MPP Analytics for PostgreSQL, you can use the following as an [external data source](../../concepts/external-tables.md#pxf-data-sources) with the JDBC connection type:

* ClickHouse®
* MySQL®
* Oracle
* PostgreSQL
* SQL Server
* Trino

This list contains managed Yandex Cloud DBs and third-party DBs.

## Create an external data source {#create-external-source}

{% list tabs group=instructions %}

- Management console {#console}

    1. Open the [folder dashboard](https://console.yandex.cloud).
    1. Navigate to **Yandex MPP Analytics for PostgreSQL**.
    1. Open the page of the Yandex MPP Analytics for PostgreSQL cluster in question.
    1. In the left-hand panel, select ![image](../../../_assets/console-icons/arrow-right-arrow-left.svg) **PXF**.
    1. Click **Create data source**.
    1. Select the `JDBC` connection type.
    1. Enter a source name.
    1. Configure at least one [optional setting](../../concepts/settings-list.md#jdbc-settings).
    1. Click **Create**.

    After you create an external data source, [create an external table](create-table.md).

- CLI {#cli}

    If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../../cli/quickstart.md#install).

    The folder used by default is the one specified when [creating](../../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

    To create an external JDBC data source:

    1. View the description of the CLI command for creating a data source:

        ```bash
        yc managed-greenplum pxf-datasource create jdbc --help
        ```

    1. Configure the data source:

        ```bash
        yc managed-greenplum pxf-datasource create jdbc <external_data_source_name> \
           --cluster-id=<cluster_ID> \
           --driver=<driver_address> \
           --url=<database_URL> \
           --user=<username> \
           --password=<password>
        ```

        Where:

        * `cluster-id`: Cluster ID. You can get it with the [list of clusters in the folder](../cluster-list.md#list-cluster).
        * `driver`: JDBC driver class in Java. The possible values are:

            * `com.clickhouse.jdbc.ClickHouseDriver`
            * `com.ibm.as400.access.AS400JDBCDriver`
            * `com.microsoft.sqlserver.jdbc.SQLServerDriver`
            * `com.mysql.cj.jdbc.Driver`
            * `org.postgresql.Driver`
            * `oracle.jdbc.driver.OracleDriver`
            * `net.snowflake.client.jdbc.SnowflakeDriver`
            * `io.trino.jdbc.TrinoDriver`

        * `url`: Database URL. Examples:

            * `jdbc:mysql://mysqlhost:3306/testdb`: For a local MySQL® database.
            * `jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/db1`: For a Yandex Managed Service for PostgreSQL cluster. The address contains a [special FQDN](../../../managed-postgresql/operations/connect/fqdn.md#special-fqdns) of the master host in the cluster.
            * `jdbc:oracle:thin:@host.example:1521:orcl`: For an Oracle database.

        * `user`: DB owner username.
        * `password`: DB user password.

        You can also configure [advanced settings](../../concepts/settings-list.md#jdbc-settings).

- REST API {#api}

    1. [Get an IAM token for API authentication](../../api-ref/authentication.md) and put it into an environment variable:

        ```bash
        export IAM_TOKEN="<IAM_token>"
        ```

    1. Call the [PXFDatasource.Create](../../api-ref/PXFDatasource/create.md) method, e.g., via the following [cURL](https://curl.se/) request:

        ```bash
        curl \
            --request POST \
            --header "Authorization: Bearer $IAM_TOKEN" \
            --header "Content-Type: application/json" \
            --url 'https://mdb.api.cloud.yandex.net/managed-greenplum/v1/clusters/<cluster_ID>/pxf_datasources' \
            --data '{
                      "datasource": {
                        "name": "<external_data_source_name>",
                        "jdbc": {
                          "driver": "<driver_address>",
                          "url": "<database_URL>",
                          "user": "<user_login>",
                          "password": "<user_password>",
                          ...
                        }
                      }
                    }'
        ```

        Where:

        * `name`: External data source name.
        * `jdbc`: External data source settings. Configure at least one [optional setting](../../concepts/settings-list.md#jdbc-settings).

        You can get the cluster ID with the [list of clusters in the folder](../cluster-list.md#list-clusters).

    1. View the [server response](../../api-ref/PXFDatasource/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

    1. [Get an IAM token for API authentication](../../api-ref/authentication.md) and put it into an environment variable:

        ```bash
        export IAM_TOKEN="<IAM_token>"
        ```

    1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
       
       ```bash
       cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
       ```
       
       Below, we assume that the repository contents reside in the `~/cloudapi/` directory.

    1. Call the [PXFDatasourceService.Create](../../api-ref/grpc/PXFDatasource/create.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

        ```bash
        grpcurl \
            -format json \
            -import-path ~/cloudapi/ \
            -import-path ~/cloudapi/third_party/googleapis/ \
            -proto ~/cloudapi/yandex/cloud/mdb/greenplum/v1/pxf_service.proto \
            -rpc-header "Authorization: Bearer $IAM_TOKEN" \
            -d '{
                  "cluster_id": "<cluster_ID>"
                  "datasource": {
                    "name": "<external_data_source_name>",
                    "jdbc": {
                      "driver": "<driver_address>",
                      "url": "<database_URL>",
                      "user": "<user_login>",
                      "password": "<user_password>",
                      ...
                    }
                  }
                }' \
            mdb.api.cloud.yandex.net:443 \
            yandex.cloud.mdb.greenplum.v1.PXFDatasourceService.Create
        ```

        Where:

        * `name`: External data source name.
        * `jdbc`: External data source settings. Configure at least one [optional setting](../../concepts/settings-list.md#jdbc-settings).

        You can get the cluster ID with the [list of clusters in the folder](../cluster-list.md#list-clusters).

    1. Check the [server response](../../api-ref/grpc/PXFDatasource/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- SQL {#sql}

  This method works well for Yandex MPP Analytics for PostgreSQL that uses [Apache Cloudberry™](https://cloudberry.apache.org).
  
  To create an external data source, do the following:

  1. Create an external data source: 

      ```sql
      CREATE SERVER "<local_source_name>"
        FOREIGN DATA WRAPPER jdbc_pxf_fdw
        OPTIONS (
          jdbc_driver '<JDBC_driver_class_name>',
          db_url 'jdbc:<DBMS_type>://<cluster_FQDN>:<port>/<database_name>',
          user '<username>',
          pass '<password>'
        );
      ```
  
  1. Create a mapping between a local user and a user in the external data source:
  
      ```sql
      CREATE USER MAPPING FOR CURRENT_USER
        SERVER "<local_source_name>";
      ```

{% endlist %}

{% note tip %}

After you create an external data source, [create an external table](create-table.md).

{% endnote %}

_Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries._

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._