Manage connection profiles

Overview

In this section, you learn how to use the Datastream API to:

• Create connection profiles for a source Oracle database, a source MySQL database, a source PostgreSQL database, a BigQuery destination, and a Cloud Storage destination
• Retrieve information about, update, and delete connection profiles
• Discover the structure of source or destination connection profiles

There are two ways that you can use the Datastream API. You can make REST API calls or you can use the Google Cloud CLI (CLI).

For high-level information about using gcloud to manage Datastream connection profiles, click Google Cloud SDK documentation.

Refer to the Using Datastream APIs tutorial to see an end-to-end walkthrough of how to use Datastream's APIs to create and manage connection profiles.

Create a connection profile for a source Oracle database

The following code shows a request to create a connection profile for an Oracle database, and specifies the connectivity method as using static IP addresses (IP allowlist).

REST

POST https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "[connection-profile-display-name]",
  "oracleProfile": {
    "hostname": "HOST_ID_ADDRESS",
    "port": PORT,
    "username": "USERNAME",
    "password": "PASSWORD",
    "databaseService": "DATABASE"
  },
  "staticServiceIpConnectivity": {}
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=myOracleCP
{
  "displayName": "my Oracle database",
  "oracleProfile": {
    "hostname": "1.2.3.4",
    "port": 1521,
    "username": "admin",
    "password": "12345"
    "databaseService": "ORCL",
  },
  "staticServiceIpConnectivity": {}
}

The following code shows a request to create a connection profile for an Oracle database, and specifies the connectivity method as private connectivity (VPC peering).

This method establishes secure connectivity between Datastream and the source database (internally within Google Cloud, or with external sources connected over VPN or Interconnect). This communication happens through a VPC peering connection.

POST https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "[connection-profile-display-name]",
  "oracleProfile": {
    "hostname": "HOST_ID_ADDRESS",
    "port": PORT,
    "username": "USERNAME",
    "password": "PASSWORD",
    "databaseService": "DATABASE"
  },
  "privateConnectivity": {
    "privateConnection": "https://datastream.googleapis.com/v1/projects/
    PROJECT_ID/locations/LOCATION/privateConnections/
    [private-connectivity-configuration-id]"
  }
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=myOracleVPCPeeringCP
{
  "displayName": "my Oracle database",
  "oracleProfile": {
    "hostname": "1.2.3.4",
    "port": 1521,
    "username": "admin",
    "password": "12345"
    "databaseService": "ORCL",
  },
    "privateConnectivity": {
      "privateConnection": "https://datastream.googleapis.com/v1/projects/
       myProjectId/locations/us-central1/privateConnections/myPrivateConnection"
  }
}

After the connection profile is created, you can view information about it by calling the connectionProfiles/get method. Your output appears, similar to the following:

{
  "name": "projects/projectId/location/us-central1/connectionProfiles/myOracleVPCPeeringCP",
  "createTime": "2019-12-22T16:17:37.159786963Z",
  "updateTime": "2019-12-22T16:17:37.159786963Z",
  "displayName": "my Oracle database",
  "oracleProfile": {
    "hostname": "1.2.3.4",
    "port": 1521,
    "databaseService": "ORCL",
    "username": "admin"
  },
  "privateConnectivity": {
    "privateConnection": "https://datastream.googleapis.com/v1/projects/
     myProjectId/locations/us-central1/privateConnections/myPrivateConnection"
  }
}

gcloud

For more information on using gcloud to create a connection profile for a source Oracle database, click here.

Create a connection profile for a source MySQL database

The following code shows a request to create a connection profile for a MySQL database, and specifies the connectivity method as using static IP addresses (IP allowlist).

REST

POST https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "[connection-profile-display-name]",
  "mysqlProfile": {
    "hostname": "HOST_ID_ADDRESS",
    "port": PORT,
    "username": "USERNAME",
    "password": "PASSWORD"
  },
  "staticServiceIpConnectivity": {}
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=mysql-cp
{
  "displayName": "my MySQL database",
  "mysqlProfile": {
    "hostname": "1.2.3.4",
    "port": 3306,
    "username": "root",
    "password": "12345"
  },
  "staticServiceIpConnectivity": {}
}

gcloud

For more information on using gcloud to create a connection profile for a source MySQL database, click here.

Create a connection profile for a source PostgreSQL database

The following code shows a request to create a connection profile for a PostgreSQL database.

REST

POST https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "[connection-profile-display-name]",
  "postgresqlProfile": {
    "hostname": "HOST_ID_ADDRESS",
    "port": PORT,
    "username": "USERNAME",
    "password": "PASSWORD",
    "database": "DATABASE"
  },
  "staticServiceIpConnectivity": {}
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=postgres-cp
{
  "displayName": "my PostgreSQL database",
  "postgresqlProfile": {
    "hostname": "1.2.3.4",
    "database": "postgres",
    "port": 5432,
    "username": "root",
    "password": "12345"
  },
  "staticServiceIpConnectivity": {}
}

gcloud

For more information about using gcloud to create connection profiles, click here.

Create a connection profile for a source SQL Server database

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

The following code shows a request to create a connection profile for a SQL Server database, and specifies the connectivity method as using static IP addresses (IP allowlist).

REST

POST https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "[connection-profile-display-name]",
  "sqlserverProfile": {
    "hostname": "HOST_ID_ADDRESS",
    "port": PORT,
    "username": "USERNAME",
    "password": "PASSWORD",
    "database": "DATABASE"
  },
  "staticServiceIpConnectivity": {}
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=sqlserver-cp
{
  "displayName": "my SQL Server database",
  "mysqlProfile": {
    "hostname": "1.2.3.4",
    "port": 1433,
    "username": "root",
    "password": "12345"
    "database": "database1"
  },
  "staticServiceIpConnectivity": {}
}

Google Cloud CLI

For more information about using gcloud to create connection profiles, see Google Cloud SDK documentation.

Create a connection profile for a BigQuery destination

The following code shows a request to create a connection profile for a BigQuery destination.

REST

POST https://datastream.googleapis.com/v1/projects/[project-id/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "connection-profile-display-name",
  "bigqueryProfile":  {}
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=myBigqueryCP
{
  "displayName": "my BigQuery destination",
  "bigqueryProfile": {}
}

gcloud

For more information on using gcloud to create a connection profiles, click here.

Create a connection profile for a Cloud Storage destination

The following code shows a request to create a connection profile for a Cloud Storage bucket. Because Cloud Storage is in the same network as Datastream, no special connectivity is required. Therefore, no connectivity method is specified.

REST

POST https://datastream.googleapis.com/v1/projects/[project-id/locations/
LOCATION/connectionProfiles?connectionProfileId=CONNECTION_PROFILE_ID
{
  "displayName": "connection-profile-display-name",
  "gcsProfile": {
    "bucketName": "bucket-name",
    "rootPath": "prefix-inside-bucket"
  }
}

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles?connectionProfileId=myGcsCP
{
  "displayName": "my Cloud Storage bucket",
  "gcsProfile": {
    "bucketName": "myBucket",
    "rootPath": "prefix-inside-bucket"
  }
}

gcloud

For more information on using gcloud to create a connection profile for Cloud Storage, see Cloud SDK documentation.

Get information about a connection profile

The following code shows a request to retrieve information about a connection profile for a source Oracle database, a source MySQL database, a BigQuery destination, or a Cloud Storage destination that has been created.

REST

GET https://datastream.googleapis.com/v1/projects/project-id/locations/
LOCATION/connectionProfiles/CONNECTION_PROFILE_ID

For example:

GET https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles/myOracleCP

gcloud

For more information on using gcloud to retrieve information about your connection profile, click here.

List connection profiles

The following code shows a request to retrieve information about all of your connection profiles.

REST

GET https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles

gcloud

For more information on using gcloud to retrieve information about all of your connection profiles, click here.

Update a connection profile

The following code shows a request to change the username and password of an existing connection profile for a source database.

By using the updateMask parameter in the request, only the fields that you specify have to be included in the body of the request (for this example, the username and password fields, represented by the oracle_profile.username and oracle_profile.password flags, respectively).

Updating a connection profile propagates all changes to any streams using it automatically.

REST

PATCH https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles/CONNECTION_PROFILE_ID?
updateMask=oracle_profile.username,oracle_profile.password
{
  "oracleProfile": {
    "username": "USERNAME",
    "password": "PASSWORD"
  }
}

For example:

PATCH https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles/myOracleCP?
updateMask=oracle_profile.username,oracle_profile.password
{
  "oracleProfile": {
    "username": "username",
    "password": "password"
  }
}

The following code shows a request to change the private connectivity configuration that's assigned to a connection profile. This connection profile uses the VPC peering network connectivity method.

For this example, you're assigning the new_private_connection configuration to the connection profile.

You'd assign a different private connectivity configuration to a connection profile in case you want to delete the existing configuration.

Also, before associating a private connectivity configuration with a connection profile, make sure that it was created successfully (that is, it has the CREATED state).

For more information about the states of a private connectivity configuration, see Create a private connectivity configuration.

PATCH -d {\"private_connectivity\":{\"private_connection_name\":\
"https://datastream.googleapis.com/v1/
projects/PROJECT_ID/locations/LOCATION/privateConnections/
new_private_connection\"}}" -H "Authorization: Bearer $TOKEN" -H 
"Content-Type: application/json" https://datastream.googleapis.com/v1/
projects/PROJECT_ID/locations/LOCATION/connectionProfiles/
CONNECTION_PROFILE_ID?update_mask=private_connectivity.private_connection_name

For example:

PATCH -d {\"private_connectivity\":{\"private_connection_name\":\
"https://datastream.googleapis.com/v1/
projects/myProjectId/locations/us-central1/privateConnections/
new_private_connection\"}}" -H "Authorization: Bearer $TOKEN" -H 
"Content-Type: application/json"
https://datastream.googleapis.com/v1/projects/
myProjectId/locations/us-central1/connectionProfiles/
myOracleVPCPeeringCP?update_mask=private_connectivity.private_connection_name

gcloud

For more information on using gcloud to update your connection profile, click here.

Delete a connection profile

The following code shows a request to delete a connection profile. After the connection profile is deleted, any streams using it will fail.

REST

DELETE https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles/CONNECTION_PROFILE_ID

For example:

DELETE https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles/myOracleCP

gcloud

For more information on using gcloud to delete your connection profile, click here.

Discover the structure of a source database

Use the discoverConnectionProfile API to retrieve the list of entities (for example, schemas and tables) from a source, and the metadata associated with the entities.

The API can receive as a parameter either an ID of an existing connection profile or a complete connection profile object definition. The function can return a single level (for example, all schemas in a database or all tables in a schema), or all entities recursively (for example, schemas, tables, and columns).

REST

POST https://datastream.googleapis.com/v1/projects/PROJECT_ID/locations/
LOCATION/connectionProfiles:discoverConnectionProfile?CONNECTION_PROFILE_ID

For example:

POST https://datastream.googleapis.com/v1/projects/myProjectId/locations/
us-central1/connectionProfiles:discoverConnectionProfile?myOracleCP

gcloud

For more information on using gcloud to discover the structure of your source database, click here.