Oracle Cloud Infrastructure Documentation

Selecting the Target for a Data Loader Task

Select the data asset, connection, and schema to use as the target. Then configure a new target entity or select an existing data entity to load the data into.

Selecting a Target Data Asset, Connection, and Schema

To configure the target data entity for a data loader task, you begin by selecting a data asset, connection, and schema.

You can parameterize the resources after you make your selections.

  1. On the Create data loader task page, go to the Target step.
  2. To create and select the data asset for this target, select Create next to Data asset to create a data asset.
  3. To select a target data entity from an existing data asset, first use the resource menus to select the data asset, connection, and schema (or bucket) only in the order as displayed. Note the following:

    • The Data asset resources available for selection depend on the type of target you selected on the Basic information step. Click View all next to a resource if you do not find what you want from the resource menu.

    • If applicable for a data asset (for example, Object Storage), select a compartment before you select a bucket (schema).

    • For a database connection, you can click Add to create and add the connection.

  4. (Optional) You can assign a parameter to the data asset, connection, and schema after you have made selections for the resources. Do one of the following for a resource on the target:
    • Click Parameterize next to the resource to assign a parameter to that resource. Upon parameterizing, Data Integration adds a parameter of the appropriate type and sets the default parameter value to the value that's currently configured for that resource.
    • If available, click Reuse source <resource> parameter to use the same parameter that has been added to the resource of the same type on the source. For more information, see Reusing Parameters for Source or Target Resources.

    Note that when the data asset type is Object Storage, you can parameterize the bucket (schema) but not the compartment that contains the bucket.

  5. Depending on the data asset type of the target, you might have other options to select.
    Data asset type Option

    Object Storage, HDFS

    Under File settings, select the file type and compression type, and the encoding, if applicable. The compression types that are available for selection depends on the file type.

    For CSV, the other settings you can configure are:

    • If all of the files have the first row as a header row, select Data has header.
    • If the values in the data rows span multiple lines, select Multi-line.
    • Specify the Escape character that escapes other characters found in data values. For example: \
    • Select the Delimiter character that separates data fields. For example: COLON (:), COMMA (,), PIPE (|), SEMICOLON (;), or TAB (\t)
    • Specify the Quote character that treats other characters as literal characters. For example: "
    • If a column delimiter is included at the end of a data row in all files, select Trailing delimiter.

    Autonomous Data Warehouse, Autonomous Transaction Processing

    • If the data asset you select is configured with a default staging location, Data Integration automatically uses that Object Storage bucket for staging. Use the default staging location that is configured for the data asset or specify a different staging location.

      To use a different staging location, under Staging location, clear the checkbox Use default staging location settings. Then use the resource menus to select an Object Storage data asset, connection, compartment, and then the bucket (schema) to use for staging.

    • If the data asset you select doesn't have a default staging location configured, use the resource menus to select an Object Storage data asset, connection, compartment, and then the bucket (schema) to use for staging.
After selecting the data asset, connection, schema (or bucket), and applicable target options, configure the target load settings for a new data entity or existing data entity.
Using New Target Data Entities

Data Integration can create new target data entities when loading data from the source entities.

For the single data entity load type, you can use a parameter for the data entity only when you choose the option to enter a data entity name.

  1. Go to the Target step, Data entities tab.
  2. Under Target data entities load settings, select Create new data entity.

    For a new target, the integration strategy is always Insert.

  3. For Object Storage target only: Select the Create output as a single file checkbox if you want to use a single output file. Otherwise, multiple files are created.

    The single output file is overwritten every time the task is run. Creating a single output file might affect the performance of Data Integration. Do not use the single output file option for large datasets.

    For multiple data entities load type, each new entity in the target has a corresponding single file.

  4. Specify the Target data entity name option you want for the new target. You can choose from:

    • Use same entity names as source: Select this option to create target entities with the same names as the source entities.

    • Add prefix/suffix: Select this option to add a string at the start (prefix) or at the end (suffix) of the source entity names to create the target entity names.

    • (For single entity load type only) Specify entity name: Enter the new target entity name in the field.

      • For Object Storage: Enter the new data entity name, followed by a slash (/). For example, enter newfile/ or newdirectory/newfile/. However, if you select the Create output as a single file checkbox because you want to create a single file output, enter the new entity name without the slash (/) at the end.

      • For a database target: If the entity name that you provide exists, the outcome of the operation depends on the shape of the target from the data loader task and the shape of the existing entity.

  5. (Optional) (For single entity load type only) You can assign a parameter to the data entity only if you have selected to enter a data entity name in the previous step. Do one of the following:
    • Click Parameterize next to the resource to assign a parameter to that resource. Upon parameterizing, Data Integration adds a parameter of the appropriate type and sets the default parameter value to the value that's currently configured for that resource.
    • If available, click Reuse source data entity parameter to use the source entity parameter as the parameter for this target data entity. For more information, see Reusing Parameters for Source or Target Resources.
  6. If applicable, select the Settings tab and enter a value for Reject limit.

    For Autonomous Data Warehouse or Autonomous Transaction Processing:

    You can specify the maximum number of erroneous rows that can fail to load into the target before the task fails. For example, if the data source has 1,000 rows and you set the reject limit at 200, the task fails immediately after the 200th erroneous row is rejected.

    If you don't specify a value, the default is zero, which means the task fails upon the first erroneous row being rejected.

    If a task failure occurs, check the logs for the table names where the rows had been rejected. Then query the affected tables and rows in the autonomous database.

Using Existing Target Data Entities

When using existing target data entities, you select the integration strategy that determines how to load data into the existing target.

For single data entity load type, you can use a parameter for the data entity after you select a data entity.

  1. Go to the Target step, Data entities tab.
  2. Under Target data entities load settings, select Use existing data entity.
  3. To specify how to load data into the target, select one of the available integration strategy options.

    • Insert: Inserts new records, or appends the records when the data exists on the target.

    • Overwrite: Performs a truncate on the target before inserting new records.

      The integration strategy must be Overwrite if you want to create output as a single file for the Object Storage target.

    • Merge: Inserts new records, and merges existing ones. The merge strategy is available for database targets only.

      For Single data entity load type (see step 5), you have the option to select a unique key other than the primary key to merge your data.

      For Multiple data entities load type, the primary key is used by default. No further action or selection is needed.

  4. For Overwrite strategy and Object Storage target only: Select the Create output as a single file checkbox if you want to use a single output file. Otherwise, multiple files are created.

    The single output file is overwritten every time the task is run. Creating a single output file might affect the performance of Data Integration. Do not use the single output file option for large datasets.

    For multiple data entities load type, each selected entity in the target has a corresponding single file.

  5. For Single data entity load type:
    1. In the Available data entities table, select one data entity. You can:

      • Select the checkbox that is next to an entity name, and then select Set as target.

      • Filter the list by entering a name or a pattern in the field and pressing Enter.

        You can enter a partial name or a pattern using special characters such as *. For example, you can enter ADDRESS_* to find ADDRESS_EU1, ADDRESS_EU2, and ADDRESS_EU3.

        From the filtered list, select the checkbox that is next to an entity name, and then select Set as target.

      • For an Object Storage target with the Overwrite integration strategy, select an existing data entity that does not have the slash (/) at the end of the entity name.
      The name of the data entity you select is displayed next to the label Selected data entity. To select a different data entity to use as the target, click Remove and then select the entity from the Available data entities table.
    2. (Optional) You can assign a parameter to the data entity after you have selected a data entity in the previous step. Do one of the following:
      • Click Parameterize next to the resource to assign a parameter to that resource. Upon parameterizing, Data Integration adds a parameter of the appropriate type and sets the default parameter value to the value that's currently configured for that resource.
      • If available, click Reuse source data entity parameter to use the source entity parameter as the parameter for this target data entity. For more information, see Reusing Parameters for Source or Target Resources.
    3. (Optional) For a database target with the Merge integration strategy:

      The Merge key identifies the attribute or attributes to determine which existing rows or new rows to update or insert. You can select a primary/unique key or a non-primary key as the Merge key.

      Click Select or Edit. In the Merge key panel, do one of the following actions:

      • Select Primary/Unique key attributes and select a key.
      • Select Other attributes to use other (non-primary) attributes. Enter a name for the merge key and select the attributes from the list.
  6. For Multiple data entities load type: By default, source data entities are automatically mapped to available target data entities by name.
    1. In the Source data entities table, Mapping column, you can place the cursor over a mapping that has a green check mark to see the mapped target entity name.
    2. To create a manual mapping, drag the source data entity to the target data entity. A mapping rule is added when you perform a manual mapping.
    3. To remove an AUTO or a manual mapping, in the Target data entities table, Mapping column, select Clear next to a mapping. Then select Clear mapping to confirm that you want to remove the mapping. A mapping rule is added when you clear a mapping.
    4. In either the Source data entities or Target data entities table, you can:

      • Filter the list by entering a name or a pattern in the field and pressing Enter.

        You can enter a partial name or a pattern using special characters such as *. For example, you can enter ADDRESS_* to find ADDRESS_EU1, ADDRESS_EU2, and ADDRESS_EU3.

      • View all entities, mapped entities, or unmapped entities by selecting the appropriate option from the menu above the table.

    5. From the Actions menu, you can select:

      • Auto-map by name: Use this action to let Data Integration automatically create mappings between source entities and target entities by name.

      • Map by pattern: Use this action to define a source pattern and a target pattern for mapping source entities to target entities. Pattern matching is case-sensitive.

        See Map by Pattern.

    To manage mapping rules, see See Viewing and Removing Mapping Rules.
Viewing and Removing Mapping Rules (Multiple Target Data Entities)

Data Integration adds mapping rules when you manually map source data entities to existing target data entities for a data loader task.

Rules are also added when you remove mappings.

  1. Go to the Target step, Data entities tab.
  2. To display the rules created for a data loader task, click View mappings.
  3. In the Entity mapping rules panel, review the mappings that you have created.
  4. To remove a mapping rule, you can use one of two ways:

    • Select the checkbox for a rule, and then select Remove.

    • Select the Actions menu for a rule and then select Remove.