Using Datastream APIs

Setting environment variables

In this procedure, you'll set the following variables:

• $PROJECT: This variable is associated with your Google Cloud project. Any Google Cloud resources that you allocate and use must belong to a project.
• $TOKEN: This variable is associated with an access token. The access token provides a session that Cloud Shell uses to perform tasks in Datastream using REST APIs.

1. Launch your Cloud Shell application.

2. After authenticating into your application with your Google Account, enter the following command:

   gcloud auth login
   

3. At the Do you want to continue (Y/n)? prompt, enter Y.

4. Open a web browser and copy the URL into the browser.

5. Authenticate to the Google Cloud SDK with your Google Account. A code appears on the Sign in page. This code is your access token.

6. Copy the access token, paste it into the Enter verification code: parameter in your Cloud Shell application, and press Enter.

7. At the prompt, enter PROJECT=\"YOUR_PROJECT_NAME\" to set the $PROJECT environment variable to your Google Cloud project.

8. At the prompt, enter gcloud config set project YOUR_PROJECT_NAME to set the project that you'd like to work on to your Google Cloud project.

   Your command prompt updates to reflect your active project and respects this format: USERNAME@cloudshell:~ (YOUR_PROJECT_NAME)$

9. At the prompt, enter TOKEN=$(gcloud auth print-access-token) to retrieve the access token and store it as a variable.

10. At the prompt, enter the following commands to ensure that your $PROJECT and $TOKEN variables are set correctly:

    • echo $PROJECT
    • echo $TOKEN

Now that you set your variables, you can make requests to Datastream to create and manage both connection profiles and a stream.

Create and manage connection profiles

In this section, you create and manage connection profiles for a source Oracle database and a destination bucket in Cloud Storage.

When you create these connection profiles, you create records that contain information about the source database and destination Cloud Storage bucket. Datastream uses the information in the connection profiles to transfer data from the source database into a folder in the destination bucket.

Creating and managing connection profiles includes:

• Creating connection profiles for a source Oracle database and a destination bucket in Cloud Storage
• Retrieving information about a connection profile
• Modifying a connection profile
• Performing a discover API call on the source Oracle connection profile. This call lets you to look inside the database to see the objects associated with it. These objects include the schemas and tables that contain the data of the database. When you use Datastream to configure a stream, you may not want to pull all objects out of the database, but rather a subset of the objects (for example, only certain tables and schemas of the database). Use the discover API to help you find (or discover) the subset of database objects that you want to pull.

Create connection profiles

In this procedure, you create two connection profiles: one to a source Oracle database and another to a destination bucket in Cloud Storage.

1. Create a connection profile to a source Oracle database. At the prompt, enter the following command:

ORACLE="{\"displayName\":\"DISPLAY_NAME\",\"oracle_profile\":{\"hostname\":\"HOSTNAME\",\"username\":\"USERNAME\",\"password\":\"PASSWORD\",\"database_service\":\"DATABASE_SERVICE\",\"port\":"PORT_NUMBER\"},\"no_connectivity\":{}}"
  

Use the following table to help you understand the parameter values for the source Oracle database:

1. At the prompt, enter the echo $ORACLE | jq command to see the source connection profile you created in easy-to-read text.

   {
     "displayName": "DISPLAY_NAME",
     "oracle_profile": {
       "hostname": "HOSTNAME",
       "username": "USERNAME",
       "password": "PASSWORD",
       "database_service": "DATABASE_SERVICE",
       "port": PORT_NUMBER
      },
     "no_connectivity": {}
   }

2. Submit the Oracle connection profile so that it can be created. At the prompt, enter the following command:

   curl -X POST -d $ORACLE -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles?connection_profile_id=SOURCE_CONNECTION_PROFILE_ID

   Use the following table to help you understand the parameter values for this command:

   Parameter value	Replace with
   DATASTREAM_API_VERSION	The current version of the Datastream API (for example, v1).
   PROJECT_PATH	The full path of your Google Cloud project (for example, projects/$PROJECT/locations/YOUR_PROJECT_LOCATION).
   SOURCE_CONNECTION_PROFILE_ID	The unique identifier reserved for this connection profile (for example, cp-1).

3. Verify that you see the following lines of code:

   {
     "name": "PROJECT_PATH/operations/operation-SOURCE_CONNECTION_PROFILE_OPERATION_ID",
     "metadata": {
       "@type": "type.googleapis.com/google.cloud.datastream.DATASTREAM_API_VERSION.OperationMetadata",
       "createTime": "DATE_AND_TIME_STAMP",
       "target": "datastream.googleapis.com/DATASREAM_VERSION/PROJECT_PATH/connectionProfiles/SOURCE_CONNECTION_PROFILE_ID",
       "verb": "create",
       "requestedCancellation": false,
       "apiVersion": "DATASTREAM_API_VERSION"
     },
     "done": false
   }

4. Create a connection profile to a destination bucket in Cloud Storage. At the prompt, enter the following command:

   GOOGLECLOUDSTORAGE="{\"displayName\":\"DISPLAY_NAME\",\"gcs_profile\":{\"bucket\":\"BUCKET_NAME\",\"root_path\":\"/FOLDER_PATH\"},\"no_connectivity\":{}}"

   Use the following table to help you understand the parameter values for the destination bucket:

   Parameter value	Replace with
   DISPLAY_NAME	The display name of the connection profile to the destination bucket.
   BUCKET_NAME	The name of the destination bucket.
   FOLDER_PATH	The folder in the destination bucket into which Datastream will transfer data from the source database (for example, /root/path).

5. At the prompt, enter the echo $GOOGLECLOUDSTORAGE | jq command to see the destination connection profile you created in easy-to-read text.

   {
     "displayName": "DISPLAY_NAME",
     "gcs_profile": {
       "bucket": "BUCKET_NAME",
       "root_path": "/FOLDER_PATH"
     },
     "no_connectivity": {}
   }

6. Submit the Cloud Storage connection profile so that it can be created. At the prompt, enter the following command:

   curl -X POST -d $GOOGLECLOUDSTORAGE -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles?connection_profile_id=DESTINATION_CONNECTION_PROFILE_ID

7. Verify that you see the following lines of code:

   {
     "name": "PROJECT_PATH/operations/operation-DESTINATION_CONNECTION_PROFILE_OPERATION_ID",
     "metadata": {
       "@type": "@type": "type.googleapis.com/google.cloud.datastream.DATASTREAM_API_VERSION.OperationMetadata",
       "createTime": "DATE_AND_TIME_STAMP",
       "target": "datastream.googleapis.com/DATASTREAM_VERSION/PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID",
       "verb": "create",
       "requestedCancellation": false,
       "apiVersion": "DATASTREAM_API_VERSION"
     },
     "done": false
   }

8. Confirm that both connection profiles are created. At the prompt, enter the following command:

   curl -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles

9. Verify that you receive two returned results for both the source and destination connection profiles.

   {
     "connectionProfiles": [
       {
         "name": "PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID",
         "createTime": "DATE_AND_TIME_STAMP",
         "updateTime": "DATE_AND_TIME_STAMP",
         "displayName": "DISPLAY_NAME",
         "gcsProfile": {
           "bucket": "BUCKET_NAME",
           "rootPath": "FOLDER_PATH"
         },
         "noConnectivity": {}
       },
      {
       "name": "PROJECT_PATH/connectionProfiles/SOURCE_CONNECTION_PROFILE_ID",
       "createTime": "DATE_AND_TIME_STAMP",
       "updateTime": "DATE_AND_TIME_STAMP",
       "displayName": "DISPLAY_NAME",
       "oracleProfile": {
         "hostname": "HOSTNAME",
         "port": PORT_NUMBER,
         "username": "USERNAME",
         "databaseService": "DATABASE_SERVICE"
       },
       "noConnectivity": {}
       }
     ]
   }

Manage connection profiles

In this procedure, you manage the connection profiles that you created for a source Oracle database and a destination bucket in Cloud Storage. This includes:

• Retrieving information about the destination Cloud Storage connection profile
• Modifying this connection profile. For this tutorial, you'll change the folder of the destination Cloud Storage bucket to /root/tutorial. Datastream transfers data from the source database into this folder.
• Performing a discover API call on the source Oracle connection profile

1. Retrieve information about the destination Cloud Storage connection profile. At the prompt, enter the following command:

   curl -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID

2. Verify that you see information about this connection profile.

   {
     "name": "PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID",
     "createTime": "DATE_AND_TIME_STAMP",
     "updateTime": "DATE_AND_TIME_STAMP",
     "displayName": "DISPLAY_NAME",
     "gcsProfile": {
       "bucket": "BUCKET_NAME",
       "rootPath": "FOLDER_PATH"
     },
     "noConnectivity": {}
   }

3. Modify this connection profile. To do this, first, set an UPDATE variable. This variable contains the values of the connection profile that you want to change. For this tutorial, you'll change the folder of the destination bucket to /root/tutorial.

   To set the variable, at the prompt, enter the following command:

   UPDATE="{\"gcsProfile\":{\"rootPath\":\"/root/tutorial\"}}"

4. At the prompt, enter the following command:

   curl -X PATCH -d $UPDATE -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID?update_mask=gcsProfile.rootPath

5. Verify that you see the following lines of code:

   {
     "name": "PROJECT_PATH/operations/operation-DESTINATION_CONNECTION_PROFILE_OPERATION_ID",
     "metadata": {
       "@type": "type.googleapis.com/google.cloud.datastream.DATASTREAM_API_VERSION.OperationMetadata",
       "createTime": "DATE_AND_TIME_STAMP",
       "target": "PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID",
       "verb": "update",
       "requestedCancellation": false,
       "apiVersion": "DATASTREAM_API_VERSION"
     },
     "done": false
   }

6. Confirm that the connection profile is modified. At the prompt, enter the following command:

   curl -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID

7. Verify that the folder of the destination bucket of the Cloud Storage connection profile is now /root/tutorial.

   {
     "name": "PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID",
     "createTime": "DATE_AND_TIME_STAMP",
     "updateTime": "DATE_AND_TIME_STAMP",
     "displayName": "DISPLAY_NAME",
     "gcsProfile": {
       "bucket": "BUCKET_NAME",
       "rootPath": "/root/tutorial"
     },
     "noConnectivity": {}
   }

8. Use the Datastream discover API to discover the schemas and tables of the source Oracle database. Datastream provides access to this database through the source connection profile.

   1. Discover the schemas of the Oracle database. At the prompt, enter the following command:

      curl -X POST -d "{\"connection_profile_name\":\"projects/YOUR_PROJECT_NUMBER/locations/YOUR_PROJECT_LOCATION/connectionProfiles/SOURCE_CONNECTION_PROFILE_ID\", \"oracle_rdbms\":{\"oracleSchemas\":[{\"schema\":\"ROOT\"}]}}" -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles:discover

   2. Verify that Datastream retrieves all schemas of your database.

   3. Retrieve the tables of a schema in your database. For this tutorial, you'll use the discover API to retrieve the tables of the ROOT schema. However, you can discover the tables of any schema in your database.

      At the prompt, enter the following command:

   curl -X POST -d "{\"connection_profile_name\":\"projects/YOUR_PROJECT_NUMBER/locations/YOUR_PROJECT_LOCATION/connectionProfiles/SOURCE_CONNECTION_PROFILE_ID\", \"oracle_rdbms\":{\"oracleSchemas\":[{\"schema\":\"ROOT\"}]}}" -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" https://datastream.googleapis.com/DATASTREAM_API_VERSION/PROJECT_PATH/connectionProfiles:discover

   1. Verify that Datastream retrieves all tables of the schema that you specified (for this tutorial, the ROOT schema).

Now that you created and managed connection profiles for a source Oracle database and a destination bucket in Cloud Storage, you're ready to create and manage a stream in Datastream.

Creating and managing a stream

In this section, you create and manage a stream. Datastream uses this stream to transfer data, schemas, and tables from the source database into a folder in the destination Cloud Storage bucket.

Creating and managing a stream includes:

• Validating a stream to ensure that the stream will run successfully, and that all validation checks pass. These checks include:
  • Whether the source is configured properly to allow Datastream to stream data from it.
  • Whether the stream can connect to both the source and the destination.
  • The end-to-end configuration of the stream.
• Creating the stream with the following lists:
  • An allow list. This list specifies the tables and schemas in the source database that Datastream can transfer into a folder in the destination bucket in Cloud Storage. For this tutorial, this is the /root/tutorial folder.
  • A reject list. This list specifies the tables and schemas in the source database that Datastream is restricted from transferring into the folder in the Cloud Storage destination bucket.
• Retrieving information about the stream
• Modifying the stream
• Starting the stream so that Datastream can transfer data, schemas, and tables from the source database into a folder in the destination Cloud Storage bucket.
• Using the Fetch Errors API to detect any errors associated with the stream
• Pausing the stream. When a stream is paused, Datastream won't pull any new data from the source database into the destination bucket.
• Resuming the paused stream so that Datastream can continue to transfer data into the destination bucket.

Create a stream

In this procedure, you create a stream from the source Oracle database into a folder in the destination Cloud Storage bucket. The stream you create will include both an allow list and a reject list.

1. Set a SCHEMAS variable. This variable defines the schemas that contain the data and tables that you want Datastream to retrieve from the source database and transfer into the /root/tutorial folder of the Cloud Storage destination bucket. For this tutorial, you'll set the SCHEMAS variable to be associated with the ROOT schema.

   At the prompt, enter the following command:

   SCHEMAS="{\"oracleSchemas\":[{\"schema\":\"ROOT\"}]}"

2. At the prompt, enter the echo $SCHEMAS | jq command to see the ROOT schema that you defined for this variable in easy-to-read text.

3. Create a stream. At the prompt, enter the following command:

   STREAM="{\"display_name\":\"DISPLAY_NAME\",\"source_config\":{\"source_connection_profile_name\":\"PROJECT_PATH/connectionProfiles/SOURCE_CONNECTION_PROFILE_ID",\"oracle_source_config\":{\"allowlist\":$SCHEMAS,\"rejectlist\":{}}},\"destination_config\":{\"destination_connection_profile_name\":\"PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID\",\"gcs_destination_config\":{\"file_rotation_mb\":5,\"file_rotation_interval\":{\"seconds\":15},\"avro_file_format\":{}},\"backfill_all\":{}}}"

4. At the prompt, enter the echo $STREAM | jq command to see the stream you created in easy-to-read text.

   {
     "display_name": "DISPLAY_NAME",
     "source_config": {
       "source_connection_profile_name": "PROJECT_PATH/connectionProfiles/SOURCE_CONNECTION_PROFILE_ID",
       "oracle_source_config": {
         "allowlist": {
           "oracleSchemas": [
             {
               "schema": "ROOT"
             }
           ]
         },
         "rejectlist": {}
       }
     },
     "destination_config": {
       "destination_connection_profile_name": "PROJECT_PATH/connectionProfiles/DESTINATION_CONNECTION_PROFILE_ID",
       "gcs_destination_config": {
         "file_rotation_mb": 5,
         "file_rotation_interval": {
           "seconds": 15
         },
         "avro_file_format": {}
       }
     },
     "backfill_all": {}
   }

   Use this table to help you understand the following parameters of the stream:

   Parameter	Description
   allowlist	The schemas, containing tables and data, that will be transferred from the source database into a folder of the Cloud Storage destination bucket. For this tutorial, all tables and data from the ROOT schema (and only this schema) will be transferred into the /root/tutorial folder of the destination bucket.