Export and Import a Data Mapping for Modeling (Emulating) a DI (ETL/ELT/CDC) Tool

Any data mapping with replication mappings and/or query mappings can be exported (without loss) to the Data Mapping Scripts format. This format is based on the standard database SQL Data Manipulation Language (SQL DML) syntax and includes both:

• The data connection data models (e.g. database schema, tables, columns) of their source and target data stores faithfully representing any supported technology (RDBMS, NoSQL, File Systems)
• The data integration (DI/ETL/ELT/CDC) for the data flow lineage between these data stores.

The specifics of the syntax for the data mapping script format is explained in a sample file at:

$MM_HOME/conf/MIRModelBridgeTemplate/DataMappingScript/DataMappingScriptTutorial.sql

The data mapping scripts can be edited /modified, or may be generated entirely from scratch to model (simulate) a DI/ETL/ELT/CDC tool which may not be a part of the supported tools for a native model import.

The data mapping scripts can then be imported as independent DI models or may be imported back into a data mapping, where one may continue to benefit from the UI data mapper UI. The resulting data flow linage would be the same in both cases.

Note that the new data mapping script format provides a number of advantages over the older metadata Excel format (deprecated) including:

• The data mapping script format supports a full round trip (export/import) from/to a data mapping, while this is not possible with the older metadata Excel format where the export was more for reporting and the import for bootstrapping.
• The data mapping script format files are generally much more compact than the very verbose metadata Excel format files based on classifier/table maps and feature/column maps.
• The data mapping script format is easier to generate from SQL based DI/ETL/ELT/CDC tools.
• The data mapping script format supports both replication mappings and query mappings, while the metadata Excel format is quite limited in its handling of replication mappings.
• The data mapping script format supports more source and target data store technologies (RDBMS, NoSQL, File Systems) that is supported by the metadata Excel format.

Therefore, the export/import of data mapping script based files is the recommended method, while the legacy metadata Excel format is now deprecated and will soon be EOL for the data mapping export/import application.