REST API Reference

Back Next

Example: Create an Oracle to Snowflake task

In this example, you create database ingestion and replication tasks that load Oracle data to Snowflake using initial, incremental, and combined initial and incremental loads.

The request bodies include only the properties that each task needs. For a complete request body, see Sample request body for creating a task.

For descriptions of all properties, see Source attributes, Target attributes, and Runtime attributes.

Request body

Send each request body in a POST request to the following resource:

POST <baseUrl>/dbmi/public/api/v2/task/create

Initial load

The following request body creates a combined load task named sf_to_snowflake_combined. The task uses the Salesforce Bulk API 2.0 to load the cli_sf__c object once, excluding the Period object, and then checks for changes every five minutes. The task renames the target table to cli_sf__c3:

{
    "type": "dbmi",
    "general": {
        "name": "oracle_to_snowflake_initial_load",
        "description": null,
        "location": "Default",
        "runtimeEnvironment": "invyaminirh01.informatica.com",
        "type": "initial"
    },
    "source": {
        "connection": "Oracle_19RDS_yk-CDC",
        "schema": "AUTO_INIT",
        "lobsEnabled": "true",
        "unloadFlashbackDisabled": "true",
        "jdbcPartitioningTechnique": "uniform",
        "sourceMaxParallelPartitions": "5",
        "unloadHelperThreadCount": "2",
        "selectionRules": [
            {
                "include": "SRC_ALLCHAR_8K",
                "fieldsSet": [
                    { "exclude": "VARCHARMAX" }
                ]
            }
        ]
    },
    "target": {
        "connection": "Snowflake_pat_sp",
        "schema": "ADOBE_PB",
        "stage": "gkn",
        "addLastReplicatedTime": "true",
        "cycleId": "false",
        "caseTransformation": "lowercase"
    },
    "runtimeOptions": {
        "executeInTaskflow": false,
        "enableAutoTuning": false
    },
    "replicationRules": {
        "source": "AUTO_INIT",
        "target": "ADOBE_PB",
        "overrides": {
            "tableRules": [],
            "fieldRules": []
        },
        "sourceSet": [
            {
                "fieldsSet": [
                    { "exclude": "VARCHARMAX" }
                ],
                "schema": "",
                "include": "SRC_ALLCHAR_8K"
            }
        ],
        "columnTransformations": []
    }
}

The following table describes the properties in the initial load request body:

Property	Description
type	Use dbmi to create a database ingestion and replication task.
general.name	Name of the task.
general.location	Project or folder that contains the task.
general.runtimeEnvironment	Runtime environment that runs the task.
general.type	Load type. The value `initial` loads a snapshot of the source data one time.
source.connection	Name of the Oracle source connection.
source.schema	Oracle schema that contains the source tables.
source.lobsEnabled	Indicates whether the task replicates large object (LOB) columns to the target.
source.unloadFlashbackDisabled	Disables the use of Oracle Flashback when the task fetches data from the source.
source.jdbcPartitioningTechnique	Technique that distributes source rows across partitions. The value uniform distributes primary keys evenly across partitions.
source.sourceMaxParallelPartitions	Maximum number of partitions that the task reads in parallel.
source.unloadHelperThreadCount	Number of helper threads that convert the unloaded rows into a format that the task can write to the target.
source.selectionRules	Rules that select the source tables. In this example, the task includes the SRC_ALLCHAR_8K table and excludes its VARCHARMAX column.
target.connection	Name of the Snowflake target connection.
target.schema	Snowflake schema where the task creates the target tables.
target.stage	Name of the internal staging area that holds data before the task writes it to the target tables.
target.addLastReplicatedTime	Indicates whether the task adds a column that records when each row was last replicated.
target.cycleId	Indicates whether the task adds a column that records the apply cycle ID.
target.caseTransformation	Case of the target table and column names. The value lowercase converts the names to lowercase.
runtimeOptions.executeInTaskflow	Indicates whether a taskflow runs the task.
runtimeOptions.enableAutoTuning	Indicates whether automatic performance tuning is enabled for the task.
replicationRules	Source and target selection rules for the task. The sourceSet list repeats the include rule and the excluded column.

A successful request returns HTTP status code 201. The response is similar to the following example:

{
    "location": "Default",
    "taskId": "757385",
    "frsId": "6kXq2PtYb9MwHn4RcLzV3d",
    "name": "oracle_to_snowflake_initial_load",
    "createdBy": "6r2IItttmU3ibDwPqxnRwb",
    "documentType": "DBMI_TASK",
    "parentInfo": [
        { "parentId": "7cCn5thwWFLhiZoSosphKL", "parentName": "REG", "parentType": "Space" },
        { "parentId": "3uDIOqah80qbMK3cXAceIp", "parentName": "Default", "parentType": "Project" }
    ]
}

Incremental load

The following request body creates an incremental load task named oracle_to_snowflake_cdc_incremental. The task uses the query-based change data capture (CDC) method to read changes from the SRC_ALLNUM_8K table in the QUERYBASEDCDC schema every five minutes, starting from the latest change. The task uses the QUERY_BASED_COL timestamp column to identify changed rows and loads the data to the NUM_SPIPE2 table in the SNOW_PB schema in Snowflake. The task replicates added and modified columns and ignores renamed and dropped columns:

{
    "type": "dbmi",
    "general": {
        "name": "oracle_to_snowflake_cdc_incremental",
        "description": null,
        "location": "Default",
        "runtimeEnvironment": "invyaminirh01.informatica.com",
        "type": "cdc"
    },
    "source": {
        "connection": "Oracle_19RDS_yk-CDC",
        "schema": "QUERYBASEDCDC",
        "restartPointForIncrementalLoad": "LATEST",
        "cdcTechnique": "QUERY_BASED_CDC",
        "cdcQueryBasedColumnType": "COLUMN_TYPE_TIMESTAMP",
        "cdcQueryBasedColumnName": "QUERY_BASED_COL",
        "cdcIntervalDays": "0",
        "cdcIntervalHours": "0",
        "cdcIntervalMins": "5",
        "readerInputIsPersisted": "false",
        "readEventBatchSize": "32",
        "readerHelperThreadCount": "3",
        "selectionRules": [
            { "include": "SRC_ALLNUM_8K" }
        ]
    },
    "target": {
        "connection": "Snowflake_pat_sp",
        "schema": "SNOW_PB",
        "renamingRules": [
            { "source": "SRC_ALLNUM_8K", "target": "NUM_SPIPE2" }
        ],
        "stage": "gkn",
        "addLastReplicatedTime": "false",
        "cycleId": "false",
        "caseTransformation": "none"
    },
    "runtimeOptions": {
        "executeInTaskflow": false,
        "enableAutoTuning": false,
        "schemaDriftOptions": {
            "addColumn": "replicate",
            "renameColumn": "ignore",
            "modifyColumn": "replicate",
            "dropColumn": "ignore"
        }
    },
    "replicationRules": {
        "source": "QUERYBASEDCDC",
        "target": "SNOW_PB",
        "overrides": {
            "tableRules": [
                { "source": "SRC_ALLNUM_8K", "target": "NUM_SPIPE2" }
            ],
            "fieldRules": []
        },
        "sourceSet": [
            { "schema": "", "include": "SRC_ALLNUM_8K" }
        ],
        "columnTransformations": []
    }
}

The following table describes the properties in the incremental load request body:

Property	Description
general.type	Load type. The value `cdc` replicates changes from the source continuously, starting from the restart point.
source.restartPointForIncrementalLoad	Point from which the task starts reading changes. The value LATEST starts from the most recent change.
source.cdcTechnique	CDC method that the task uses. The value QUERY_BASED_CDC queries the source tables for changes.
source.cdcQueryBasedColumnType	Data type of the column that the task uses to identify changed rows. The value COLUMN_TYPE_TIMESTAMP is a timestamp column.
source.cdcQueryBasedColumnName	Name of the column that the task uses to identify changed rows. The name is case sensitive.
source.cdcIntervalDays, cdcIntervalHours, cdcIntervalMins	Interval at which the task checks the source for changes. In this example, the task checks every 5 minutes.
source.readerInputIsPersisted	Indicates whether the task stores transaction data in a disk buffer. In this example, the task stores transaction data in a disk buffer.
source.readEventBatchSize	Number of change events that the task writes in a batch to the internal event queue.
source.readerHelperThreadCount	Number of helper threads that convert change data into a format that the task can write to the target.
target.renamingRules	Renames the SRC_ALLNUM_8K target table to NUM_SPIPE2.
runtimeOptions.schemaDriftOptions	Replicates added and modified columns, and ignores renamed and dropped columns.
replicationRules.overrides	Repeats the renaming rule for the replication.

A successful request returns HTTP status code 201. The response is similar to the following example:

{
    "location": "Default",
    "taskId": "757659",
    "frsId": "3tNf8WcQd5ZyJr2AxBvK7m",
    "name": "oracle_to_snowflake_cdc_incremental",
    "createdBy": "6r2IItttmU3ibDwPqxnRwb",
    "documentType": "DBMI_TASK",
    "parentInfo": [
        { "parentId": "7cCn5thwWFLhiZoSosphKL", "parentName": "REG", "parentType": "Space" },
        { "parentId": "3uDIOqah80qbMK3cXAceIp", "parentName": "Default", "parentType": "Project" }
    ]
}

Combined load

The following request body creates a combined load task named oracle_to_snowflake_combined. The task loads the COMPO8 table in the SP_UNLOAD schema once, and then uses the log-based CDC method to check for changes every five minutes, starting from the latest change. The task renames the target table to COMPO8_O9 and adds audit columns that describe each change, including the operation type, time, owner, transaction ID, and sequence:

{
    "type": "dbmi",
    "general": {
        "name": "oracle_to_snowflake_combined",
        "description": null,
        "location": "Default",
        "runtimeEnvironment": "invyaminirh01.informatica.com",
        "type": "combined"
    },
    "source": {
        "connection": "Oracle_19RDS_yk-CDC",
        "schema": "SP_UNLOAD",
        "restartPointForIncrementalLoad": "LATEST",
        "cdcTechnique": "LOG_BASED_CDC",
        "cdcIntervalDays": "0",
        "cdcIntervalHours": "0",
        "cdcIntervalMins": "5",
        "readerInputIsPersisted": "true",
        "lobsEnabled": "true",
        "selectionRules": [
            { "include": "COMPO8" }
        ]
    },
    "target": {
        "connection": "Snowflake_pat_sp",
        "schema": "SNOW_PB",
        "renamingRules": [
            { "source": "COMPO8", "target": "COMPO8_O9" }
        ],
        "stage": "gkn",
        "addBeforeImages": "true",
        "addLastReplicatedTime": "true",
        "cycleId": "true",
        "auditData": {
            "applyMode": "audit",
            "auditColumnPrefix": "INFA_",
            "addOperationType": "true",
            "addOperationTime": "true",
            "addOperationOwner": "true",
            "addOperationTransactionId": "true",
            "addOperationSequence": "true"
        },
        "caseTransformation": "none"
    },
    "runtimeOptions": {
        "executeInTaskflow": false,
        "enableAutoTuning": false,
        "schemaDriftOptions": {
            "addColumn": "replicate",
            "renameColumn": "ignore",
            "modifyColumn": "replicate",
            "dropColumn": "ignore"
        }
    },
    "replicationRules": {
        "source": "SP_UNLOAD",
        "target": "SNOW_PB",
        "overrides": {
            "tableRules": [
                { "source": "COMPO8", "target": "COMPO8_O9" }
            ],
            "fieldRules": []
        },
        "sourceSet": [
            { "schema": "", "include": "COMPO8" }
        ],
        "columnTransformations": []
    }
}

The following table describes the properties in the combined load request body:

Property	Description
general.type	Load type. The value `combined` loads a snapshot of the source data and then replicates changes.
source.cdcTechnique	CDC method that the task uses. The value LOG_BASED_CDC reads changes from the database logs.
source.cdcIntervalDays, cdcIntervalHours, cdcIntervalMins	Interval at which the task checks the source for changes. In this example, the task checks every 5 minutes.
source.readerInputIsPersisted	Indicates whether the task stores transaction data in a disk buffer. In this example, the task stores transaction data in a disk buffer.
target.addBeforeImages	Indicates whether the task adds the before images of changed rows to the target.
target.auditData	Settings for the audit columns. The value audit for applyMode applies an audit trail of every change to the target. The other properties add columns that record the operation type, time, owner, transaction ID, and sequence. The column names start with INFA_.

A successful request returns HTTP status code 201. The response is similar to the following example:

{
    "location": "Default",
    "taskId": "757421",
    "frsId": "8bHj4VsLm2PqXc6DwTnY1g",
    "name": "oracle_to_snowflake_combined",
    "createdBy": "6r2IItttmU3ibDwPqxnRwb",
    "documentType": "DBMI_TASK",
    "parentInfo": [
        { "parentId": "7cCn5thwWFLhiZoSosphKL", "parentName": "REG", "parentType": "Space" },
        { "parentId": "3uDIOqah80qbMK3cXAceIp", "parentName": "Default", "parentType": "Project" }
    ]
}

Rename Saved Search

Table of Contents

REST API Reference

REST API Reference

Example: Create an Oracle to Snowflake task

Example: Create an Oracle to Snowflake task

Request body

Initial load

Incremental load

Combined load