ptolemy 1.0.0

Background

Amazon Web Services provides a tool for migrating data to, from or between SQL databases. This tool is named Database Migration Service (DMS). When running DMS, users can supply a table mapping, which specifies allows the user to control what data is sent from the source database to the target database. A full list of table mapping options can be found here.

Table mappings are written as JSON documents, which can grow to be long and complex. ptolemy allows the user to write terse YAML source files, which can be compiled to valid JSON table mappings using the ptolemy cli tool.

Usage

$ # Display an example source file:
$ cat migrate_all_tables_in_a_schema.yaml
selection:
  include:
    -
      object-locators:
        schema-names:
          - Test
        table-names:
          - "%"
$ # Compile it to a DMS table mapping:
$ ptolemy migrate_all_tables_in_a_schema.yaml
{
    "rules": [
        {
            "object-locator": {
                "schema-name": "Test",
                "table-name": "%"
            },
            "rule-action": "include",
            "rule-id": "1",
            "rule-name": "1",
            "rule-type": "selection"
        }
    ]
}

API

$ ptolemy -h
ptolemy [-h] [-d] [-v] source

positional arguments:
  source         path to the source file

optional arguments:
  -h, --help     show this help message and exit
  -d, --debug    enable debug logs
  -v, --version  show program's version number and exit

Install

Install via pip (recommended):

$ pip install ptolemy

Install from source:

$ git clone git@github.com:cloudreach/ptolemy.git
$ cd ptolemy
$ make install

License

ptolemy is licensed under the Apache Software License 2.0.

Source Syntax

The following sections describe the source syntax. It is intended to show users who have working knowledge of DMS JSON mapping files how to write their YAML equivalents. For an overview of the JSON mapping files, see the documentation. Most items are the same as those in JSON mapping files, with the exception of object-locators, which are explained in the section Object Locators

The descriptions are written in pseudo-yaml, where the syntax ( option_a|option_b ) indicates that an item could take the value option_a or option_b.

For working examples, see the examples directory.

selection:
  ( include|exclude ):
    -
      object-locators:
        schema-names:
          - <schema-name-1>
        table-names:
          - <table-name-1>
        filters:
          -
            filter-type: source
            column-name: <column-name-1>
            filter-conditions:
              -
                filter-operator: ( ste|gte|eq|between )
                value: <value>

transformation:
  ( rename|remove-column|convert-lowercase|convert-uppercase|add-prefix|remove-prefix|replace-prefix|add-suffix|remove-suffix|replace-suffix ):
    -
      object-locators:
        schema-names:
          - <schema-name-1>
        table-names:
          - <table-name-1>
        column-names:
          - <column-name-1>
      rule-target: ( schema|table|column )
      value: <value>
      old-value: <old-value>

Multiple Source File Compilation

Multiple source files can be compiled at once with the following Bash snippet. The snippet recursively finds all YAML files under the directory src/, compiles the source to a DMS mapping file, and saves it to a file with the same name and path under a directory named mappings/, with the extension .json.

source_files="$(find src -type f -name '*.yaml')"
for source_file in $source_files; do
  source_file_without_extension=${source_file%.*}
  source_file_with_json_extension=${source_file_without_extension}.json
  destination_file=mappings${source_file_with_json_extension#src}
  mkdir -p "$(dirname $destination_file)"
  ptolemy $source_file > $destination_file
done

Running the code from the following directory:

.
└── src
  ├── db-a
  │   ├── schema-1.yaml
  │   └── schema-2.yaml
  └── db-b
      ├── schema-1.yaml
      └── schema-2.yaml

would result in:

.
├── mappings
│   ├── db-a
│   │   ├── schema-1.json
│   │   └── schema-2.json
│   └── db-b
│       ├── schema-1.json
│       └── schema-2.json
└── src
    ├── db-a
    │   ├── schema-1.yaml
    │   └── schema-2.yaml
    └── db-b
        ├── schema-1.yaml
        └── schema-2.yaml