NOTE

If DB2 is selected as the source database, map data from Table to File (T2F) and then File to Table (F2T).

Processing on Datatypes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE is not supported, even though source model generation is supported for those datatypes.

NOTE

Data source based on File present in HDFS system cannot be loaded into RDBMS target Infodom.

NOTE

You can create a new table by clicking  if the target information domain is based on HDFS database. The newly created table will be part of the OFSAAI data model and it is made visible and available to all other modules. For more information, see Dynamic Creation of Table.

NOTE

For a single DI Mapping, you can use different target tables. That is, after mapping a source column to a column in a Target Entity, you can select another Target Entity and start mapping source columns to that target table columns. Also the same source column can be mapped to different target columns of different target entities.

NOTE

Columns mapped to #MISDATE will use the NLS format of DB for loading. For loading successfully, the DB_DATE_FORMAT given in AAI_DB_PROPERTY table should be the NLS date format of the corresponding atomic schema. To know the NLS date format of the DB , you can fire the following query:
select * from V$nls_Parameters

NOTE

Row Level Transformation is supported only for F2T.

In case of date based columns in F2T, when you map the same source date column to multiple target columns, an expression value is added for all mapped target columns, except the first column mapped. The expression will be in this format: TO_DATE(<<first record>>,'mm-dd-yyyy').

NOTE

If the defined expression uses function that has a placeholder or calls a stored procedure that has a placeholder for String data type, enclose the placeholder in single quotes. Using double-quotes would generate error during extract definition or batch execution. Also expressions with Date/Timestamp data type placeholders are not supported.

NOTE

The View SQL and Validate button will be enabled only if your user group is mapped to the User Role DMADV.

NOTE

1.      If a partitioned column is not mapped and the static value is not set for the partitioned column, an alert is displayed. The saving of mapping definition does not fail. You can set a static value at any time before execution.

2.     For H2H definition, if the source and target are pointing to two different Hive Schemas, it is mandatory to prefix the schema name to the source tables. Otherwise, the execution will fail.

3.     When you click Save, if there are Primary Key Columns in the Target Entities which are not mapped, then the following alert appears:
[8368] Mandatory Columns are not Mapped [9024] Do you want to continue?
You can click OK if no change is required and proceed, or click Cancel to stay on the current window.

Property Name

Property Value

Constraints

Delete Duplicate

Select Yes if you want to delete the duplicate records after insertion if Primary Keys are disabled.

Disable Primary Key

Select Yes to disable Primary Key while loading the data.

In Batch and Bulk modes if any of the foreign keys are in Disabled state before loading the data using T2T or the property Disable Primary Key is set to Yes, then all the Primary Keys and corresponding Foreign Keys are disabled before loading and are enabled back after loading. Hence the initial status of foreign and primary keys can be changed from Disabled to Enabled.

In Direct mode, if the Disable Primary Key property is not set (selected as No), then the Delete Duplicate property is set to Yes automatically, which in turn reports all the duplicate records in the error log table.

File

Frequency

Select the frequency of loading the data file into Data Warehouse. This property can be used to schedule Batch operations.

The options are Daily, Weekly, Monthly, Quarterly, Yearly, and One Time Load.

Load Empty

If this is set to Yes, the task will be successful, even if there are no records to load or if all the records are discarded or rejected.

MIS Date Field

Specify the MIS Date field in the source data file. If MIS Date is not part of the download, then you can use the MISDate() function in the Data Mapping window to add MIS Date to the table automatically.

Loading

Load Previous

Set to Yes if you want to load the data of the previous period if current period data is not available.

Loading Type

Select the loading type from the drop-down list. The options are:

Insert- The records will be overwritten.

Append- The records will be appended to the target table.

Read Priority

Specify the priority of reading data from. The options are Persistent Store and Memory Store.

Write Priority

Specify the priority of writing data to. The options are Persistent Store and Memory Store.

Loading Mode

Record Load Limit

If the number of records in the source table exceeds Record Load Limit value, the data loading will not happen. If the value is set as 0 or not specified, record count check is skipped.

Direct or Batch or Bulk

Specify the Loading Mode as Direct, Batch, or Bulk.

In Bulk Mode of loading, note that:

·       Loading is possible only when the target database and the data source created for the definition are in the same database.

·       If the schema used for source and target is different but the database is same, then the target schema should be granted “Select” access for the source table.

·       You cannot specify the Batch Size and commit happens at the end of batch load.

Batch loading is faster for lesser records as compared to larger number of records, which sometimes lead to loss of data while loading.

Batch Size

Specify the Batch Size if you want to load the records in batches. The ideal values for batch sizes are 1024, 2048, 10000, or 20000. Huge batch sizes may result in failure if the required system resources are not available.

If it is not specified, commit is done on the entire set.

Rejection

Rejection Threshold

Enter the maximum errors in absolute value that a Data File can have and the Data Load will be marked successful.

Once the erroneous record count exceeds the Rejection Threshold value, the data loading task will fail and the inserted values will be rolled back for that table. Inserts for the previous tables won't be reverted. Rejection Threshold will be applied to each of the target table individually in a batch.

By default, the value is set as UNLIMITED.

Note the behavior of Rejection Threshold and Rejection Threshold %:

Rejection Threshold is checked before Rejection Threshold %. If you set a value for Rejection Threshold, it will be considered as the rejection limit and any value given to Rejection Threshold % is not considered.

If you set Rejection Threshold as UNLIMITED or blank, it checks for Rejection Threshold % and the value set for Rejection Threshold % will be taken as rejection limit.

If you set both Rejection Threshold and Rejection Threshold % as UNLIMITED or blank, the whole Data file will be loaded irrespective of the number of errors.

Rejection Threshold %

Set Rejection Threshold as a percentage of the number of rows in the Data file.

Enter the maximum errors as a percentage of the number of rows in the data file, which a Data File can have and the Data Load will be marked as successful.

By default, the value is set as UNLIMITED.

Rejection Threshold % is considered only if Rejection Threshold is set to UNLIMITED or blank.

Property Name

Property Value

Loading

Loading Type

Select the loading type from the drop-down list. The options are:

Insert- The records will be overwritten.

Append- The records will be appended to the target table.

Read Priority

This field determines the priority of reading the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Write Priority

This field determines the priority of writing the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list. 

Loading Mode

Record Load Limit

If the number of records in the source table exceeds Record Load Limit value, the data loading will not happen. If the value is set as 0 or not specified, record count check is skipped.

Sqoop

Split By Column

This is applicable only if you are using Sqoop for loading to Hive tables.

Specify the split by column in the format “TableName.ColumnName”. It should not be an expression. Additionally, the column should not be of data type “Date” and it should not have Null data.

This is a mandatory field for T2H executions using Sqoop.

If you have not provided any value for this field, the T2H Sqoop engine defaults the value to the last mapped source column.

Ideally, you should set Split-by column to a PK numeric column. If the split by column is String based, Generic Options property needs to be set to -Dorg.apache.sqoop.splitter.allow_text_splitter=true

Generic Options

This field is applicable only in Sqoop 1.44SSH mode.

Specify the generic arguments which will be appended before all the tool specific arguments. For example, -Doraoop.nologging=true

Specific Options

This field is applicable only in Sqoop SSH mode.

Specify any tool specific arguments, which will be appended at the end of the Sqoop command. For example, --connection-param-file ora.properties --update-mode allowinsert --update-key <COLUMN_NAME>

Property Name

Property Value

Loading

Loading Type

Select the loading type from the drop-down list. The options are:

Insert- The records will be overwritten.

Append- The records will be appended to the target table.

Read Priority

This field determines the priority of reading the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Write Priority

This field determines the priority of writing the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Loading Mode

Record Load Limit

If the number of records in the source table exceeds Record Load Limit value, the data loading will not happen. If the value is set as 0 or not specified, record count check is skipped.

Property Name

Property Value

File

Data File

Enter the name of the Data File which need to be extracted. You can specify multiple files separated by ‘/’.

This property is useful to create metadata definitions for multiple Flat-Files of the same structure by copying the Definition File.

Hive and Impala

Is File Local To Hive Server

Select Yes if the file is on the server where HiveServer is running, else select No from the drop-down list. This is applicable only for remote file source.

Loading

Loading Type

Select the loading type from the drop-down list. The options are:

Insert- The records will be overwritten.

Append- The records will be appended to the target table.

Read Priority

This field determines the priority of reading the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Write Priority

This field determines the priority of writing the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Property Name

Property Value

Loading

Loading Type

Select the loading type from the drop-down list. The options are:

Insert- The records will be overwritten.

NOTE:

Limitation: In the Insert Mode for H2T SQOOP Execution, the Target Tables are truncated.

If a Task fails, the changes cannot be rolled back.

Append- The records will be appended to the target table.

Read Priority

This field determines the priority of reading the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Write Priority

This field determines the priority of writing the data from Memory Store or Persistent Store.

Select Memory Store or Persistent Store from the drop-down list.

Loading Mode

Record Load Limit

If the number of records in the source table exceeds Record Load Limit value, the data loading will not happen. If the value is set as 0 or not specified, record count check is skipped.

Batch Size

Specify the Batch Size if you want to load the records in batches. The ideal values for batch sizes are 1024, 2048, 10000, or 20000. Huge batch sizes may result in failure if the required system resources are not available.

If it is not specified, commit is done on the entire set.

Rejection

Rejection Threshold

Enter the maximum errors in absolute value that a Data File can have and the Data Load will be marked successful.

Once the erroneous record count exceeds the Rejection Threshold value, the data loading task will fail and the inserted values will be rolled back for that table. Inserts for the previous tables won't be reverted. Rejection Threshold will be applied to each of the target table individually in a batch.

By default, the value is set as UNLIMITED.

Sqoop

Generic Options

This field is applicable only in Sqoop 1.44SSH mode.

Specify the generic arguments which will be appended before all the tool specific arguments. For example, -Doraoop.nologging=true

Specific Options

This field is applicable only in Sqoop SSH mode.

Specify any tool specific arguments, which will be appended at the end of the Sqoop command. For example, --connection-param-file ora.properties --update-mode allowinsert --update-key <COLUMN_NAME>

Use Staging

Select Yes to use staging table during Sqoop export.

Property Name

Property Value

File

Frequency

Select the frequency of loading the data file into Data Warehouse. This property can be used to schedule Batch operations.

The options are Daily, Weekly, Monthly, Quarterly, Yearly, and One Time Load.

MIS Date Field

Specify the MIS Date field in the source data file. If MIS Date is not part of the download, then you can use the MISDate() function in the Data Mapping window to add MIS Date to the table automatically.

Data File

Enter the data file name if it is different from the Definition name. This property is useful to create metadata definitions for multiple Flat-Files of the same structure by copying the Definition File.

Note: For F2T CPP execution, you should not enter “/ “ in the Data File name.

Load Emplty

If this is set to Yes, the task will be successful, even if there are no records to load or if all the records are discarded or rejected.

Prefix

Enter the string that is prefixed with the data file name separated by an underscore (_).

Suffix

Select No if the data file name is not suffixed.

Select Information Date if the data file name is suffixed with Information Date or MIS Date in YYYYMMDD format separated by an underscore (_).

Constraints

Disable Primary Key

Select Yes to disable Primary Key while loading the data.

In Batch and Bulk modes if any of the foreign keys are in Disabled state before loading the data using T2T or the property Disable Primary Key is set to Yes, then all the Primary Keys and corresponding Foreign Keys are disabled before loading and are enabled back after loading. Hence the initial status of foreign and primary keys can be changed from Disabled to Enabled.

In Direct mode, if the Disable Primary Key property is not set (selected as No), then the Delete Duplicate property is set to Yes automatically, which in turn reports all the duplicate records in the error log table.

Disable Check Constraints

Select Yes if the Check Constraints on columns of the table needs to be disabled or select No to load with the constraints enabled.

Loading Mode

Record Load Limit

If the number of records in the source file exceeds Record Load Limit value, the data loading will not happen. If the value is set as 0 or not specified, record count check is skipped.

Loading

Load Previous

Set to Yes if you want to load the data of the previous period if current period data is not available.

Loading Type

Select the loading type from the drop-down list. The options are:

Insert- The records will be overwritten.

Append- The records will be appended to the target table.

Duplicate Row

Duplicate Row Checks

Select Yes to check for Duplicate Rows and to be removed from the Data File.

Duplicate Row

This field determines which of the Duplicate Record(s) to be removed if found. The options are Keep Last Occurrence and Keep First Occurrence.

Misc

Abort-Failure Condition

Select Stop to stop the loading on reaching the Rejection Threshold. Select Continue to ensure the reading of the entire Data File.

Query

Enter the Query that needs to be executed before file loading.

Discard Max

Enter the maximum errors allowed for SQL*Loader Discards while loading.

Edit and Reload

Select Yes to have an option of editing the error file and re-loading it. 

Oracle

Continue If

Enter a condition which when satisfied will continue the file load.

Direct Load

Select Yes to do Fast Load into the Oracle Database only if you have not defined any target expressions.

Select Force to do Fast Load into the Oracle Database if target expressions have only constant values.

Select No if you do not want to enable Fast Load.

Load When

Enter a condition which when satisfied will start the file load.

Parallel Load

Select Yes to load data in parallel into the Database table for faster Load, else select No.

Preserve Blanks

Select Yes to retain blank values in the Data without trimming.

BINDSIZE

For conventional path loads, BINDSIZE specifies the maximum size (bytes) of the bind array. The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined.

Number of ROWS

For conventional path loads, ROWS specifies the number of rows in the bind array.

For direct path loads, ROWS identifies the number of rows you want to read from the data file before a data save. The default is to read all rows and save data once at the end of the load.

Trailing Null Columns

Select Yes to retain Trailing Null Columns in the Data File.

Growth

Incremental Growth

Enter the Incremental Growth of Data in absolute values over the previous period.

Incremental Growth %

Enter the Incremental Growth of Data in percentage over the previous period.

Rejection

Rejection Threshold

Enter the maximum errors in absolute value that a Data File can have and the Data Load will be marked successful.

Once the erroneous record count exceeds the Rejection Threshold value, the data loading task will fail and the inserted values will be rolled back for that table. Inserts for the previous tables won't be reverted. Rejection Threshold will be applied to each of the target table individually in a batch.

By default, the value is set as UNLIMITED.

Rejection Threshold is considered only if Rejection Threshold % is set to UNLIMITED or blank.

If you set both Rejection Threshold % and Rejection Threshold as UNLIMITED or blank, the whole Data file will be loaded irrespective of the number of errors.

Rejection Threshold %

Set Rejection Threshold as a percentage of the number of rows in the Data file.

Enter the maximum errors as a percentage of the number of rows in the data file, which a Data File can have and the Data Load will be marked as successful.

By default, the value is set as UNLIMITED.

Note the behavior of Rejection Threshold % and Rejection Threshold:

Rejection Threshold % is checked before Rejection Threshold. If you set a value for Rejection Threshold %, it will be considered as the rejection limit and it will not check Rejection Threshold.

If you set Rejection Threshold % as UNLIMITED or blank, it checks for Rejection Threshold and the value set for Rejection Threshold will be taken as rejection limit.

If you set both Rejection Threshold and Rejection Threshold % as UNLIMITED or blank, the whole Data file will be loaded irrespective of the number of errors.

NOTE

Whenever you make any changes in the Select Entity grid, click the Select button to refresh the Source Entity Details grid to reflect the changes done.

NOTE

If the defined expression uses function that has a placeholder or calls a stored procedure that has a placeholder for String data type, enclose the placeholder in single quotes. Using double-quotes would generate error during extract definition or batch execution. Also expressions with Date/Timestamp data type placeholders are not supported.

NOTE

Hints are not supported for T2F definitions.

NOTE

The View SQL and Validate button will be enabled only if your user group is mapped to the User Role DMADV.

NOTE

No validation is provided for missing Field Orders. Hence, during execution those columns after the missing field order will be omitted. Click the Reorder button to sort and reorder the Field Order numbers to fill any missing numbers.

NOTE

Date Format should be mentioned for target columns with Logical Data Type as Date Time. Else, the execution will fail.

Property Name

Property Value

File

Data File

Enter the data file name.

Data File Name can be different from the Definition File Name. This property is useful to create metadata definitions for multiple Flat-Files of the same structure by copying the Definition File.

Suffix

Select No if you do not want to suffix the data file name.

Select Information Date if you want to suffix the data file name with Information Date or MIS Date in YYYYMMDD format separated by an underscore (_).

Prefix

Enter the string that you want to prefix with the data file name separated by an underscore (_).

Misc

Field Delimiter

Enter the field separator used in the Data File. By default, comma (,) is selected.

Rules

Check Rules

Select Header, Trailer, Header and Trailer or No from the drop-down list depending on where the Validity rules are specified in the Data File.

Header Identifier

This field is enabled only if you select Header or Header and Trailer options for Check Rules.

Specify the first Character or String that identifies the Header Record.

Header Field Order

This field is enabled only if you select Header or Header and Trailer options for Check Rules.

Specify the header field order as comma separated values-: 1-Header Identifier,2-Data File Name, 3-Information Date, 4-Number of records, 5-Value of Checksum, 6-Basis of Checksum.

For example, if you specify 1,3,2,4,5,6; the header fields will be Header Identifier, Information Date, Data File Name, Number of records, Value of Checksum, Basis of Checksum.

Trailer Identifier

This field is enabled only if you select Trailer or Header and Trailer options for Check Rules.

Specify the first Character or String that identifies the Trailer Record.

Trailer Field Order

This field is enabled only if you select Trailer or Header and Trailer options for Check Rules.

Specify the Trailer field order as comma separated values-: 1- Trailer Identifier,2-Data File Name, 3-Information Date, 4-Number of Records, 5-Value of Checksum, 6-Basis of Checksum.

Data File Name

Select Yes if the name of the data file should be provided as part of the Header/Trailer.

Information Date

Select Yes if the Information (MIS) Date in the Data File should be provided as part of the Header/Trailer.

Number of Records

Select Yes if the number of records in the Data File should be provided as part of the Header/Trailer.

Checksum

Select Yes if a Check Sum Value should be provided as part of the Header/Trailer.

Basis of Checksum

Specify the Source Column Name on which the Check Sum is computed. It has to be a Numeric column.

NOTE

When a DQ rule is associated with a T2T mapping and the Allow Correction on DI Source checkbox is not selected in the System Configuration> Configuration > Others tab, DQ rule checking is done on source, but data correction is done while loading to the target table.

NOTE

Ensure the structure of the source/target in the mapping definition is same as that of the replacing source/target.

Note that you can use both EXEC_ENV_SOURCE and EXEC_ENV_TARGET together as well. Only limitation is if the resultant definition is T2T, it cannot be executed using CPP engine.

NOTE

1.      Ensure that the task with “CLOSE_SPARK_SESSION”,”YES” has less precedence set from all the rest of the H2H-spark tasks.

2.     By default, the created spark session will be closed when any of the H2H-spark tasks fail.

3.     Execution of H2H with large number of mappings may fail because Spark restricts the length of the SQL code in the spark.sql file to maximum 65535 (2^16 - 1).

4.    When you run an H2H Load with Hive and Apache Spark, it fails with the following error:

Error executing statement : java.lang.RuntimeException: Cannot create staging directory

'hdfs://<HOST_NAME>/user/hive/warehouse/hivedatadom.db/dim_account/.hive-staging_hive_2020-07-06_22-44-57_448_3115454008595470139-1': Permission denied:

user=<USER_NAME>, access=WRITE,

inode="/user/hive/warehouse/hivedatadom.db/dim_account":hive:hive:drwxrwxr-x

Provide the required permissions to the logged-in user in the Hive Database Storage, which enables the user to access and perform tasks in the storage.

NOTE

For H2T, the Prescript is fired on the source.

NOTE

If you need to enable dynamic partition in non-strict mode, set the below property as a Prescript in the Data Mapping window:

set hive.exec.dynamic.partition.mode=nonstrict

NOTE

If the defined expression uses function that has a placeholder or calls a stored procedure that has a placeholder for String data type, enclose the placeholder in single quotes. Using double-quotes would generate error during extract definition or batch execution. Also expressions with Date/Timestamp data type placeholders are not supported.