Creating an external JDBC data source

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To create an external JDBC data source:

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

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

2. 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 the 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.

1. Get an IAM token for API authentication and put it in an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Use the PXFDatasource.Create method and send the following request, e.g., via cURL:

   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.

   You can get the cluster ID with the list of clusters in the folder.

3. View the server response to make sure your request was successful.

1. Get an IAM token for API authentication and put it in an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume that the repository contents reside in the ~/cloudapi/ directory.

3. Use the PXFDatasourceService.Create call and send the following request, e.g., via gRPCurl:

   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.

   You can get the cluster ID with the list of clusters in the folder.

4. View the server response to make sure your request was successful.