Oracle Bulkload

The Oracle Bulkload source reads all available data from multiple Oracle tables, then stops the flow. The source can use multiple threads to enable the parallel processing of data. For information about supported versions, see Supported systems and versions.

Use the Oracle Bulkload source to quickly read database tables, such as when you want to migrate tables to another database or system. You can use the source to read from static tables or non-static tables.

When you configure the Oracle Bulkload source, you specify connection information and the tables to read. You can also use a connection to configure the source.

You can configure advanced properties, such as the number of threads to use, the number of batches to include in each transaction request, and whether to consider the case of schemas and tables when executing queries.

By default, the source generates field attributes that provide additional information about each field.

The source can generate events for an event stream. For more information about dataflow triggers and the event framework, see Dataflow triggers overview.

Before you use the Oracle Bulkload source, you must complete a prerequisite task.
Note: The Oracle Bulkload source does not maintain an offset. Each time the flow runs, the source processes all available data. So, even if the flow stops before completing all processing, the source processes all available data again when you restart the flow.

Prerequisite

Before using the Oracle Bulkload source, install the Oracle JDBC driver for the database. The source cannot access the database until you install this driver.

To use the JDBC driver with multiple stage libraries, install the driver into each stage library associated with the stages.
  1. Download the Oracle JDBC driver from the Oracle website.
    Note: Writing XML data to Oracle requires installing the Oracle Data Integrator Driver for XML. For more information, see the Oracle documentation.
  2. Install the driver as an external library into the JDBC Oracle stage library, streamsets-datacollector-jdbc-oracle-lib, which includes the source.

For information about installing additional drivers, see Install external libraries.

Static and non-static tables

You can use the Oracle Bulkload source to read static tables, tables that do not change while the flow runs, or to read non-static tables, tables that change while the source runs.

When using the source to read from non-static tables that might change as the flow runs, configure the stage to use isolation levels. With isolation levels enabled, the source uses the serializable isolation level and only reads changes committed when the flow starts. The source does not capture changes made to the table while the flow runs. Oracle consistency checks that occur with this isolation level can significantly reduce throughput in an environment with many concurrent transactions.

After using the source to migrate data from static tables, you can use a separate flow that includes the Oracle CDC or Oracle CDC Client source to process CDC data from LogMiner redo logs or the JDBC Multitable Consumer source to read data continuously from tables.

After using the source to migrate data from non-static tables, processing CDC data does not capture changes made while migrating data, resulting in loss of data. Therefore, processing CDC data is not recommended after using the source to migrate data from non-static tables.

Batch processing

Unlike most Data Collector sources, the Oracle Bulkload source performs batch processing only. After processing all data, the source stops the flow, rather than waiting for additional data as with streaming flows.

The Oracle Bulkload source does not maintain an offset during processing. Each time that you run a flow that includes the source, the source processes the available data in the specified tables. Then the source stops the flow gracefully, allowing processes to complete.

Tip: If the flow stops before processing is complete, to avoid duplicate records, clear the target system of processed records before starting the flow again.

Schema and table names

When you configure the Oracle Bulkload source, you specify the tables that you want to read. To specify the tables, you define the schema and a table name pattern.

You can use SQL wildcards to define a set of tables within a schema or across multiple schemas.

For example, say you want to process all tables in the sales schema that start with SALES_. You can use the following configuration to specify the tables to process:
  • Schema: sales
  • Table Name Pattern: SALES_%

You can configure the source to consider the case of schema and table names when executing queries.

Multithreaded processing

The Oracle Bulkload source performs parallel processing and enables the creation of a multithreaded flow.

When you start the flow, the Oracle Bulkload source retrieves the list of tables defined in the table configuration. The source then uses multiple concurrent threads for processing based on the Maximum Pool Size property on the Advanced tab.

As the flow runs, Oracle creates blocks of data in memory. The Oracle Bulkload source creates a task from a block of data and passes it to an available flow runner. The flow runner creates batches from the task for processing based on the maximum batch size configured for the source.

A flow runner is a sourceless flow instance - an instance of the flow that includes all of the processors, executors, and targets in the flow and handles all flow processing after the source. Each flow runner processes one batch at a time, just like a flow that runs on a single thread.

When tasks created from Oracle blocks are smaller than desired, like when they are smaller than the maximum batch size, you can configure the source to merge small tasks. Use the Minimum Task Size property on the Advanced tab to specify the minimum number of records to include in a task. When set, smaller tasks are merged to enable more efficient processing.

Multithreaded flows preserve the order of records within each batch, just like a single-threaded flow. But since tasks are processed by different flow runners, the order that batches are written to targets is not ensured.

For more information about multithreaded flows, see Multithreaded flow overview.

Field attributes

The Oracle Bulkload source generates field attributes for columns converted to the Decimal or Datetime data types in Data Collector. The attributes provide additional information about each field.

The following data type conversions do not include all information in the corresponding Data Collector type:
  • The Oracle Number data type is converted to the Data Collector Decimal data type, which does not store scale and precision.
  • The Oracle Timestamp data type is converted to the Data Collector Datetime data type, which does not store nanoseconds.
To preserve this information during data type conversion, the source generates the following field attributes for these Data Collector data types:
Data Collector Data Type Generated Field Attribute Description
Decimal precision Provides the original precision for every number column.
Decimal scale Provides the original scale for every number column.
Datetime nanoSeconds Provides the original nanoseconds for every timestamp column.

You can use the record:fieldAttribute or record:fieldAttributeOrDefault functions to access the information in the attributes. For more information about working with field attributes, see Field attributes.

Event generation

The Oracle Bulkload source can generate events that you can use in an event stream.

Oracle Bulkload events can be used in any logical way. For example:

For more information about dataflow triggers and the event framework, see Dataflow triggers overview.

Event record

Event records generated by the Oracle Bulkload source have the following event-related record header attributes:
Record Header Attribute Description
sdc.event.type Event type. Uses the following type:
  • table-finished - Generated when the source completes processing all rows within a table.
sdc.event.version Integer that indicates the version of the event record type.
sdc.event.creation_timestamp Epoch timestamp when the stage created the event.

The Oracle Bulkload source can generate the following event record:

table-finished
The Oracle Bulkload source generates a table-finished event record when the source completes processing all data within a table.
The table-finished event record has the following additional fields:
Event Record Field Description
schema Schema associated with the table that has no remaining data to be processed.
table Table that has no remaining data to be processed.
record-count Number of records that were successfully processed.

Configuring an Oracle Bulkload source

Configure an Oracle Bulkload source to read data from one or more static database tables.

About this task

Before you use the source in a flow, complete the prerequisite task.

Procedure

  1. In the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Produce Events Generates event records when events occur. Use for event handling.
    On Record Error Error record handling for the stage:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the flow for error handling.
    • Stop Flow - Stops the flow.
  2. On the JDBC tab, configure the following JDBC properties:
    JDBC Property Description
    Connection

    SDC 7.2 and later

    		 Connection that defines the information that is required to connect to an external system.

    To connect to an external system, you can select a connection that contains the details, or you can specify the details in local properties. When you select a connection, the flow canvas hides properties that are defined in the connection.

    JDBC Connection String
    Connection string to use to connect to the database.
    Note: If you include the JDBC credentials in the connection string, use the user account created for the source.
    Use Credentials Enables entering credentials on the Credentials tab. Select when you do not include credentials in the JDBC connection string.
    Tables Tables to read. Configure properties for each table or set of tables that you want to read.

    Using simple or bulk edit mode, click the Add icon to define another table configuration.
    Schema Name Schema to use. You can enter a schema name or use SQL wildcards to define multiple schemas.
    Table Name A table name pattern that defines the tables to read. You can enter a table name or use SQL wildcards to define multiple tables.
    Additional JDBC Configuration Properties Additional JDBC configuration properties to use. To add properties, click Add and define the JDBC property name and value.

    Use the property names and values as expected by JDBC.

  3. To enter JDBC credentials separately from the JDBC connection string, on the Credentials tab, configure the following properties:
    Credentials Property Description
    Username

    Oracle user name.

    The user must have the following Oracle privileges:
    • SELECT on the tables being read.
    • SELECT on the SYS.DBA_EXTENTS system table.
    • READ on the SYS.USER_OBJECTS system table.
    Password Password for the account.
    Tip: To secure sensitive information such as user names and passwords, you can use runtime resources or credential stores.
  4. On the Advanced tab, optionally configure the following properties:
    Advanced Property Description
    Maximum Pool Size Number of threads to use for multithreaded processing.
    Batches per Request Number of batches to fetch from the database in each request.

    Default is 50.
    Max Batch Size (records) Maximum number of records processed at one time. Honors values up to the Data Collector maximum batch size.

    Default is 1000. The Data Collector default is 1000.
    Minimum Idle Connections Minimum number of connections to create and maintain. To define a fixed connection pool, set to the same value as Maximum Pool Size.

    Default is 1.

    Minimum Task Size Minimum number of records to allow in a task.

    This property determines whether smaller tasks should be merged with larger tasks for processing. Tasks are based on chunks of data provided by Oracle.

    Use -1 to opt out of using this property.
    Stop for SQL Exception Stops the flow upon encountering a SQL exception.
    Case Sensitive Considers the case of the schemas and tables when executing queries.
    Idle Timeout (seconds) Maximum time to allow a connection to idle. Use a time constant in an expression to define the time increment.

    Use 0 to avoid removing any idle connections.

    When the entered value is close to or more than the maximum lifetime for a connection, Data Collector ignores the idle timeout.

    Default is 10 minutes, defined as follows: 
    ${10 * MINUTES}
    Use Isolation Levels Isolates changes made to the table while the source reads data.

    Only select this option if reading from non-static tables that might change as the flow runs.

    Connection Timeout (seconds) Maximum time to wait for a connection. Use a time constant in an expression to define the time increment.
    Default is 30 seconds, defined as follows: 
    ${30 * SECONDS}