Creating an external JDBC data source

Written by

Yandex Cloud

Updated at November 18, 2024

Create an external data source
Example of a REST API request

In Managed Service for Greenplum®, as an external data source with the JDBC connection type, you can use the following:

ClickHouse®.
HBase
MySQL®.
Oracle
PostgreSQL.
SQL Server.

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

Create an external data source

Management console

CLI

API

Go to the folder page and select Managed Service for Greenplum.
Open the page of the Managed Service for Greenplum® cluster you need.
In the left-hand panel, select PXF.
Click Create data source.
Select the JDBC connection type.
Enter a source name.
Configure at least one optional setting.
Click Create.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create an external JDBC data source:

View the description of the CLI command to create a data source:
```
yc managed-greenplum pxf-datasource create jdbc --help
```
Configure the data source:
```
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 a list of clusters in the folder.
- driver: JDBC driver class in Java. The possible values are:
  - com.simba.athena.jdbc.Driver
  - 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® DB.
  - 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 of the master host in the cluster.
  - jdbc:oracle:thin:@host.example:1521:orcl: For an Oracle DB.
- user: DB owner username.
- password: DB user password.
You can also configure advanced settings.

To add a JDBC data source to a Managed Service for Greenplum® cluster, use the create REST API method for the PXFDatasource resource or the PXFDatasourceService/Create gRPC API call and provide the following in the request:

Cluster ID in the clusterId parameter. To find out the cluster ID, get a list of clusters in the folder.
Data source name in the name parameter.
External source settings in the jdbc parameter.

Example of a REST API request

The example below shows how to create an external data source for a Managed Service for PostgreSQL cluster using the Managed Service for Greenplum® REST API. To create a source:

Get an IAM token. It is used for authentication in the API.
Add the IAM token to the following environment variable:
```
export IAM_TOKEN=<token>
```

Send a request using cURL:

curl --location "https://mdb.api.cloud.yandex.net/managed-greenplum/v1/clusters/<cluster_ID>/pxf_datasources" \
     --header "Content-Type: text/plain" \
     --header "Authorization: Bearer ${IAM_TOKEN}" \
     --data "{
         \"datasource\": {
             \"name\": \"jdbc\",
             \"jdbc\": {
                 \"driver\": \"org.postgresql.Driver\",
                 \"url\": \"jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>\",
                 \"user\": \"<user_login>\",
                 \"password\": \"<user_password>\"
             }
         }
     }"

In the request body, specify the following parameters:

name: Source name, e.g., jdbc.
driver: DB driver address.
url: Database URL. It contains a special FQDN of the current master host.

You can get the cluster ID with a list of clusters in the folder.
user: DB owner username.
password: User password.

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Creating an external JDBC data source

Create an external data sourceCreate an external data source

Example of a REST API requestExample of a REST API request

Was the article helpful?

Create an external data source

Example of a REST API request