Load Oracle data into BigQuery

To schedule recurring data transfers from your Oracle database to BigQuery, you create a transfer configuration to specify what data objects to transfer, and how often to schedule the data transfer. After you set up the transfer configuration, the BigQuery Data Transfer Service transfers the latest data into a BigQuery table on the specified schedule.

To learn about how an Oracle transfer works, see Introduction to Oracle data transfers.

Limitations

Oracle transfers are subject to the following limitations:

• The maximum number of simultaneous connections to an Oracle database is limited, and as a result, the number of simultaneous transfer runs to a single Oracle database is limited to that maximum amount.
• You must set up a network attachment in cases where a public IP is not available for an Oracle database connection, with the following requirements:
  • The data source must be accessible from the subnet where the network attachment resides.
  • The network attachment must not be in the subnet within the range 240.0.0.0/24.
  • Network attachments cannot be deleted if there are active connections to the attachment. To delete a network attachment, contact Cloud Customer Care.
  • For the us multi-region, the network attachment must be in the us-central1 region. For the eu multi-region, the network attachment must be in the europe-west4 region.
• The minimum interval time between recurring Oracle transfers is 15 minutes. The default interval for a recurring transfer is 24 hours.
• A single transfer configuration can only support one data transfer run at a given time. In the case where a second data transfer is scheduled to run before the first transfer is completed, then only the first data transfer completes while any other data transfers that overlap with the first transfer is skipped.
  • To avoid skipped transfers within a single transfer configuration, we recommend that you increase the duration of time between large data transfers by configuring the Repeat frequency.
• During a data transfer, the Oracle connector identifies indexed and partitioned key columns to transfer your data in parallel batches. For this reason, we recommend that you specify primary key columns or use indexed columns in your table to improve the performance and reduce the error rate in your data transfers.
  • If you have indexed or primary key constraints, only the following column types are supported for creating parallel batches:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • Oracle data transfers that don't use primary key or indexed columns can't support more than 2,000,000 records per table.
• If your configured network attachment and virtual machine (VM) instance are located in different regions, there might be cross-region data movement when you transfer data from Oracle.

Incremental transfer limitations

• You can only choose TIMESTAMP columns as watermark columns.
• Incremental ingestion is only supported for assets with valid watermark columns.
• Values in a watermark column must be monotonically increasing.
• Incremental transfers cannot sync delete operations in the source table.
• A single transfer configuration can only support either incremental or full ingestion.
• You cannot update objects in the asset list after the first incremental ingestion run.
• You cannot change the write mode in a transfer configuration after the first incremental ingestion run.
• You cannot change the watermark column or the primary key after the first incremental ingestion run.
• The destination BigQuery table is clustered using the provided primary key and is subject to clustered table limitations.
• When you update an existing transfer configuration to the incremental ingestion mode for the first time, the first data transfer after that update transfers all available data from your data source. Any subsequent incremental data transfers will transfer only the new and updated rows from your data source.

To learn about how incremental transfers work, see Full or incremental transfers.

Before you begin

The following sections describe the steps that you need to take before you create an Oracle transfer.

Oracle prerequisites

• Create a User credential in the Oracle database.
• Grant Create Session system privileges to the user to allow session creation.
• Assign a tablespace to the user account.

You must also have the following Oracle database information when creating an Oracle transfer.

BigQuery prerequisites

• Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
• Create a BigQuery dataset to store your data.
• If you intend to set up transfer run notifications for Pub/Sub, verify that you have the pubsub.topics.setIamPolicy Identity and Access Management (IAM) permission. Pub/Sub permissions are not required if you only set up email notifications. For more information, see BigQuery Data Transfer Service run notifications.

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you the BigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to create a BigQuery Data Transfer Service data transfer:

• BigQuery Data Transfer Service permissions:
  • bigquery.transfers.update
  • bigquery.transfers.get
• BigQuery permissions:
  • bigquery.datasets.get
  • bigquery.datasets.getIamPolicy
  • bigquery.datasets.update
  • bigquery.datasets.setIamPolicy
  • bigquery.jobs.create

You might also be able to get these permissions with custom roles or other predefined roles.

For more information, see Grant bigquery.admin access.

Load Oracle data into BigQuery

Add Oracle data into BigQuery by setting up a transfer configuration using one of the following options:

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

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],
  

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.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate",
        "connector.networkAttachment":
        "projects/dev-project1/regions/us-central1/networkattachments/na1"
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']]}'

To manually run a data transfer outside of your regular schedule, you can start a backfill run.

Troubleshoot transfer setup

If you are having issues setting up your data transfer, see Oracle transfer issues.

What's next

• To learn more about BigQuery Data Transfer Service, see What is BigQuery Data Transfer Service.
• To learn about using transfers, including getting information about a transfer configuration, listing transfer configurations, and viewing a transfer's run history, see Manage transfers.
• To learn how to load data with BigQuery Omni operations, see Load data with BigQuery Omni operations.