Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

Maps

When replicating data between different storages types, Replicate makes a best effort to maintain the data’s structure when transferring it from the source. In practice, there are situations where more control over source data mapping is required.

It’s possible to precisely define how the data retrieved from the source is applied to the destination by using a mapping file. The mapping file can be provided as follows:

./bin/replicate snapshot conf/conn/oracle.yaml conf/conn/memsql.yaml --map oracle_to_memsql_map.yaml

Replicate attempts to maintain the source data structure by using the same names for all replicated objects (catalogs, schemas, tables, columns). However, in cases where the source uses schemas but the destination uses catalogs, a different mapping may be required to reconcile different multi-tenancy approaches.

A mapper file contains a map of rules where each rule applies to a single destination catalog or schema (namespace). For databases that support both catalog and schema, each rule applies to a single schema, and the schema must be prefixed with the catalog (fully qualified). For each destination namespace, it’s possible to define a list of source namespaces, the contents of which will then be mapped into it.

When more control is needed, it’s possible to define additional rules for tables and columns. Table rules are defined in the similar fashion as namespace rules, using a “tables” map where multiple source tables can be specified for each destination table. Note that, while source table names must be fully qualified with catalog and schema, destination tables are defined using only their names.

For each table mapping, it’s also possible to map columns based on their names. Note that this is an experimental feature that may not function reliably.

File Format

rules:
  [<dst_namespace>]:
    source:
    - [<src_namespace>]:

    tables:
      <dst_table_name>:
        source:
        - [<src_namespace>, <src_table_name>]:
            <dst_col_name>: <src_col_name>

Example

Oracle to MemSQL

rules:
  [memsql_tpch]:
    source:
    - tpch

    tables:
      dst_part:
        source:
          [tpch, part]:
      dst_orders:
    	  source:
          [tpch, orders]:
            dst_orderkey: orderkey
        	dst_comments: comments

All tables from Oracle’s tpch schema are replicated to MemSQL’s memsql_tpch database. Tables tpch.part and tpch.orders from Oracle are replicated as dst_part and dst_orders in MemSQL, respectively.