Mapping is a process which covers one of the following use cases:
•An output table for a single input table is created. The new table has the exact number of rows as the old one, but the columns may be different.
•One output table is created from several input tables. The new table has the exact number of rows as all of the selected tables together and it also contains all of the unique columns from the selected tables.
The mapping steps requires that the user defines the list of columns to be written in the new table. There are three ways to do this:
•By specifying all required columns.
•By skipping the specification of the required columns but setting the mapRemaining attribute to true.
•By using both together - specifying only the columns which will be transformed and using mapRemaining to infer the remaining, undefined columns and include them as well.
A column may be taken-over or transformed. The following mappings are available:
•Simple mapping: A column may be simply taken over without any additional processing (simple mapping). The name may be taken as is or changed to an arbitrary game.
•Fixed values: A new column may be created containing fixed values.
•Auto values: A new column may be created containing values inserted dynamically (random numbers, date and time, GUIDs, placeholders).
•Transformed values: A new column may be created by transforming the existing column using a set of transform options (uppercasing, lowercasing, switch-case statements, etc.).
•Aggregated values: A new column my be created by aggregating two or more other columns (max/min value, average, concatenated string, first not-null value, etc.).
•Custom values: A custom value may be calculated using SQL syntax.
Mapping uses the following syntax:
{ "id": 1, // unique ID "type": "map", "name": "Description of the step", "source": "Name of the source table", "columns": { // a dictionary of columns "TargetName1": {}, // definition of source1 "TargetName2": {} // definition of source2 [...] }, "target": "The name of the output table" } |
The following minimum properties are required:
•ID (must be unique)
•Type (must be set to "map")
•Source (must be one of the following)
oA string representing the table name.
oA string representing a wildcard to look for table(s).
oAn integer representing the source as another step.
oAn object with the property table set to the name of the source table.
oAn object with the property step set to the ID of the source step.
oAn array of tables or steps to perform union select.
•Either a non-empty list of columns or the attribute mapRemaining set to true.
Configure Mapping in RayVentory Data Hub
•SOURCES: Define one or more sources that will be used for the transformation. It supports auto-completion and will offer all available tables matching the current input string for selection.
•COLUMNS: Enter a column from which the value will be taken by clicking on the Add button and choosing a column and a type as described below for the different types.
•MAP REMAINING COLUMNS: When the field is checked, the remaining columns will be taken over. Disable the option by unchecking the checkbox.
Aggregated Columns
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Aggregated columns to define an aggregation type that will be used with the selected column. If Aggregated columns is selected as type, the following fields will be available.
•SOURCE COLUMN: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•AGGREGATION TYPE: Define the method that will be used to produce a single value out of the input values by selecting the aggregation type from the dropdown menu. The following options are available.
oAverage: Selects the average value of one or more values.
oCoalesce: Selects the first not-empty value from the list of one or more values.
oConcat: Join all given non-empty values using a specific separator (from left to right).
oFirst non null: Selects the first not-empty value from the list of one or more values.
oMaximum: Selects the maximum of one or more values.
oMinimum: Selects the minimum of one or more values.
oSum: Selects the sum of one or more values.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Automatic Value
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Automatic value to define an automatic value that will be used. If Automatic value is selected as type, the following fields will be available.
•VALUE: Use the dropdown menu to select the automatic value that should be used. The following values are available:
oCurrent datetime
oGUID
oAuto incremented number
oTable name
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Constant Value
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Constant value to define a value that will be used. If Constant value is selected as type, the following fields will be available.
•VALUE: Enter the value that should be used. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Regular Expression Match
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Regular expression match to define a regular expression that will be used to transform the column. If Regular expression match is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•REGULAR EXPRESSION: Enter the regular expression that will be used for the transform.
Detailed information on how to use regular expressions in ETL can be found in the Programmability chapter in the ETL Implementation Guide.
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
Column
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Column to define a column that will be used. If Column is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
SQL
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select SQL to define a macro that will be used to transform the column. If SQL is selected as type, the following fields will be available.
•SQL: Enter the SQL macro that will be used for the transform.
Detailed information on how to use scripts in ETL can be found in the Programmability chapter in the ETL Implementation Guide.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Switch
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Switch to define a switch that will be used to transform the column. If Switch is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•CASES: If the CASES tab is selected, instead of the DEFAULT tab, it is possible to add one or more cases. If a case is added the following fields will be available for configuration:
oCASE: Enter a value that should be used for the case. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
oTHEN: Enter a value that should be used as then. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
•DEFAULT: If the DEFAULT tab is selected, instead of the CASES tab, it is possible to add one default value by choosing default. Then a new field will be available where the value can be defined.
oDEFAULT VALUE: Enter a value that should be used default. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Transformed Column
•TARGET COLUMN NAME: Enter the name of the target column. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TYPE: Select Transformed column to define a transformation type that will be used to transform the column. If Transformed column is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TRANSFORMATION TYPE: Select the transformation type that will be used to transform the column.
oIs not null or empty
oIs null or empty
oLength
oLowercase
oUppercase
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.