Generate Mapping
Use the Generate Mapping screen to create a new mapping from source and target systems.
Last updated
Use the Generate Mapping screen to create a new mapping from source and target systems.
Last updated
Use the Generate Mapping screen in the left navigation menu to create a new mapping from source and target systems. This feature is also accessible via the Design tab in the top navigation.
You can access via the Home Screen in the left navigation menu or in drop down menu via the Design tab in the main header.
When on the Generate Mapping page, you can collapse the Choose Connections and Choose Mapping Type menus by clicking on the header of each section.
You can resize the left hand column of the page by hovering the cursor over the line that divides it from the center column.
To generate a new mapping choose a source and target data store from the two select lists.
To be available as a source or target, an Administrator must add them in the Manage Connections screen.
Once a source and target is selected, you then choose one of the available Mapping Types below.
All Schemas and Tables allows you generate a mapping of every table in every schema of your source database.
Schema(s) allows you to select one or schemas in the source database, generating mappings for each table in the schema(s) selected.
Tables(s) allows you to select a single schema and then select one or more tables in the schema.
Views(s) allows you to select a single schema and then select one or more views in the schema.
Custom Query provides the ability to craft custom SQL queries as mappings. Custom SQL gives you the ability to build data pipelines with the most advanced series of conditions.
If a selected map(s) already exists, you will receive a warning they will be overwritten if you should choose to continue.
All Schemas and Tables **allows you generate a mapping of every table in every schema of your source database.
Once you click the Generate Mapping, the Generation Results will appear in the center of the page. The results will return the following fields:
Map Type
Source Connection
Source Container
Source Table
Target Connection
Map Name
Status
Run Mapping
Custom Query provides the ability to craft custom SQL queries as mappings. Custom SQL gives you the ability to build data pipelines with the most advanced series of conditions.
Enter Target Schema Name
Enter Target Table Name
Enter SQL Statement
In order for Kodda to generate a sequence (sometimes referred to as an identity column) use the Kodda function $sequence()$ in your query. At run time Kodda will generate this column as an identity/sequence column on the target table.
Be sure to review the Options and Workflow Options before you click Generate Mapping
Once you generate your mapping(s), the Generation Results in the center pane of the screen show your mappings with links that you can use to jump directly to the Edit Mapping screen, with the noted mapping open for you to review and/or modify as necessary.
If you auto-generated a workflow as described in Workflow Options Design Workflow screen.
Workflow Options
Description
Generate Workflow
Workflow Name
When you opt to automatically generate a workflow with the Generate Workflow option described above, enter a name here for the resulting workflow. If a workflow with this name already exists, you are informed of it and forced to provide a unique workflow name.
Max Parallel
Set the maximum number of parallel mappings allowed in the auto-generated workflow or some basic ability to tune your resulting workflow. For example, if generating mappings for 100 tables, and you select to automatically generate a workflow as noted above, setting this parameter to 20 will create a workflow with 20 parallel streams, each executing 5 mappings in sequence. These parallel streams are randomly generated by Kodda's internal algorithms. You may review and edit the auto-generated workflow if you believe your data pipeline is large and/or complex enough to warrant the time spent optimizing the data ingest process, such as a 2000 table process in which you want some tables to specifically load before others, or other complex use cases.
*While there is no max, a number between 2-8 is recommended.
Options
Description
Disable Audit Columns
By default, Kodda automatically includes three standard audit columns of si_job_id, si_create_date and si_update_date on each table mapping it creates, and auto-populates them in the target records when the mapping is run. These columns are very useful in tracing the lineage of your target data. If the user does not want these fields generated in the target data, users can disable their creation by turning it on.
Disable Target DDL
When Kodda runs a mapping, if the mapped object does not exist in the target database, by default Kodda creates and runs DDL (Data Definition Language) to create the object, then loads the data. Kodda will also add any additional columns that do not yet exist on the target table. Disable this default behavior by turning this option on if you do not want your target table structures to be modified by the mappings generated. Disabling target DDL is useful if you have two databases that may not be fully in-sync and yet you still want to migrate data between them without touching their structure. For instance, if you are replicating data from a production to a test or development database, and you want to move the data only, but not change the structure in your test or development environment. The Detect Target Changes option below is often also useful when disabling this option.
Detect Source Changes
Kodda can duplicate tables from source to target automatically. Alternatively, turning this option on allows you to enforce that all the mapping(s) generated pick up any source data structure changes when moving data into the target. In effect, this allows you to add columns in your source system that will automatically appear in your mapping at run time. In addition if you drop columns in table(s) in your source system they would no longer be included data extract process.
Detect Target Changes
Set this will option on to ensure that data will only be delivered to columns that are available in the target. When generating a mapping to move data from source to target, if a column exists on the source, Kodda's default presumption is that this column also exists on the target. This option instructs Kodda at run time to ignore columns from the source that do not yet exist in the target table. For example, if a column exists on the source, and the Disable Target DDL option has been enabled, Kodda will not attempt to insert data from this column on the target.
Match Source Name
By default Kodda will auto rename schemas, tables and columns with reserved words on the target table postfixed with an "_". Additionally, Kodda will auto rename columns that exceed the 30 characters in length. Kodda also generates a name for the target schema/database used for the target table based on the source connection and source schema name. Enable this to override this behavior and ensure that the generated mappings are set to deliver data to target objects with the same schema, table and column names found in the source.
Use this multi-select list to select schema(s) when you opt to create a mapping for a Schema(s) or Table(s) as noted above. The button will remain disabled until all required selections have been made.
Tables(s) allows you to select a single schema and then select one or more tables in the schema. The button will remain disabled until all required selections have been made.
After you choose Connections, Mapping Type (Views) and Schema/Table, the Generation Results will be displayed in the center of the page. The button will remain disabled until all required selections have been made.
After you enter the Target Schema Name, Target Table Name and the custom SQL Statement, the Test SQLbutton will appear below the Generate Mappingbutton in the Activities section.
When you click on the Test SQL button, the Query Result will display. If your custom SQL is not accurate, you will be presented with an error message.
After the SQL is tested, click on the button to complete the mapping of the custom query.
Generate Mapping creates an auto generated data pipeline from source to target, storing those parameters in the repository. Click the green Generate Mapping button and Kodda generates one or more mappings based upon how you set the Mapping Types noted above.
Additionally, simply click the respective icon to run individual mappings from the list one at a time or run the entire auto-generated workflow.
Before you hit the button, if you set this Generate Workflow option on, then Kodda automatically creates all the mappings you need and a workflow to run them all. When generation completes, you see this new workflow under all the mappings in the Generation Results on the center console of the screen. This simple option allows you to generate the mapping and workflow all in one step, and then with one more click of the button, you can run the entire set of new mappings. If your workflow is large and/or complex enough to warrant additional review, you can always edit the workflow to optimize its auto-generated logic. To edit it, just click the provided link which will migrate you directly to this workflow in the Design Workflow screen.
