Creating an external JDBC data source
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
- 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. - Source name in the
name
parameter. - External source settings in the
jdbc
parameter.
Sample 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\": \"<username>\", \"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