Schedule an Oracle transfer

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. Click Data transfers > Create a transfer.

3. In the Source type section, for Source, select Oracle.

4. In the Transfer config name section, for Display name, enter a name for the transfer.

5. In the Schedule options section:

   • In the Repeat frequency list, select an option to specify how often this transfer runs. To specify a custom repeat frequency, select Custom. If you select On-demand, then this transfer runs when you manually trigger the transfer.

   • If applicable, select either Start now or Start at set time and provide a start date and run time.

6. In the Destination settings section, for Dataset, select the dataset you created to store your data.

7. In the Data source details section, do the following:

   • For Network attachment, select an existing network attachment or click Create Network Attachment.
   • For Host, enter the hostname or IP of the database.
   • For Port, enter the port number that the Oracle database is using for incoming connections, such as 1520.
   • For Database name, enter the name of the Oracle database.
   • For Connection type, enter the connection URL type, either SERVICE, SID, or TNS.
   • For Username, enter the username of the user initiating the Oracle database connection.
   • For Password, enter the password of the user initiating the Oracle database connection.

   • For Oracle objects to transfer, click BROWSE to select any tables to be transferred to the BigQuery destination dataset.

     • You can also manually enter any objects to include in the transfer in this field.

     

8. In the Service Account menu, select a service account associated with your Google Cloud project. The selected service account must have the required roles to run this transfer.

   If you signed in with a federated identity, then a service account is required to create a transfer. If you signed in with a Google Account, then a service account for the transfer is optional.

   For more information about using service accounts with data transfers, see Use service accounts.

9. Optional: In the Notification options section, do the following:

   • To enable email notifications, click the Email notification toggle. When you enable this option, the transfer administrator receives an email notification when a transfer run fails.
   • To enable Pub/Sub transfer run notifications for this transfer, click the Pub/Sub notifications toggle. You can select your topic name, or you can click Create a topic to create one.

10. Click Save.

bq

Enter the bq mk command and supply the transfer creation flag --transfer_config:

bq mk \
    --transfer_config \
    --project_id=PROJECT_ID \
    --data_source=DATA_SOURCE \
    --display_name=DISPLAY_NAME \
    --target_dataset=DATASET \
    --params='PARAMETERS'

Where:

• PROJECT_ID (optional): your Google Cloud project ID. If --project_id isn't supplied to specify a particular project, the default project is used.
• DATA_SOURCE: the data source — oracle.
• DISPLAY_NAME: the display name for the transfer configuration. The transfer name can be any value that lets you identify the transfer if you need to modify it later.
• DATASET: the target dataset for the transfer configuration.

• PARAMETERS: the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. The following are the parameters for an Oracle transfer:

  • connector.networkAttachment (optional): name of the network attachment to connect to the Oracle database.
  • connector.authentication.Username: username of the Oracle account.
  • connector.authentication.Password: password of the Oracle account.
  • connector.database: name of the Oracle database.
  • connector.endpoint.host: the hostname or IP of the database.
  • connector.endpoint.port: the port number that the Oracle database is using for incoming connections, such as 1520.
  • connector.connectionType: the connection URL type, either SERVICE, SID, or TNS.
  • assets: the path to the Oracle objects to be transferred to BigQuery, using the format: DATABASE_NAME/SCHEMA_NAME/TABLE_NAME

For example, the following command creates an Oracle transfer in the default project with all the required parameters:

bq mk \
    --transfer_config \
    --target_dataset=mydataset \
    --data_source=oracle \
    --display_name='My Transfer' \
    --params='{"assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"], \
        "connector.authentication.username": "User1", \
        "connector.authentication.password":"ABC12345", \
        "connector.database":"DB1", \
        "Connector.endpoint.host":"192.168.0.1", \
        "Connector.endpoint.port":"1520", \
        "connector.connectionType":"SERVICE", \
        "connector.networkAttachment": \
        "projects/dev-project1/regions/us-central1/networkattachments/na1"}'

API

Use the projects.locations.transferConfigs.create method and supply an instance of the TransferConfig resource.