How it works Navigate the tree Create statement models

Metadata model in DMS Schema Conversion

When you work with DMS Schema Conversion, the service represents your database schemas as a hierarchical tree. This tree is called the metadata tree, and it applies to both source and target schemas. Each element in the tree, including the root element ("Servers"), is a metadata model.

The metadata tree contains two kinds of elements:

Objects — Represent actual database objects such as tables, functions, views, sequences, and indexes. You can retrieve the SQL definition of an object using the DescribeMetadataModel request.
Categories — Virtual grouping containers such as "Schemas", "Tables", "Functions", and "Indexes". Categories organize objects in the tree for navigation but do not have SQL definitions themselves.

The metadata tree loads data only when you request it. This approach is called on-demand loading (also known as lazy loading), which means DMS Schema Conversion retrieves data from your database only as needed. You use the import operation to load metadata — either by refreshing a specific metadata model or importing an entire subtree at once. Operations such as assessment and conversion also load the metadata tree automatically.

How the metadata model works

The metadata model in DMS Schema Conversion follows a lifecycle of operations. Operations that modify the metadata tree (such as import and convert) are asynchronous. You start a request for these operations, and it runs in the background. Call the corresponding describe API to check the request status. Operations that read the tree (such as describing children or definitions) are synchronous.

Note

In DMS Schema Conversion, the terms operation and request are used interchangeably.

The typical workflow includes the following steps:

Import — Load metadata from your source or target database into the metadata tree. You can either load the initial metadata model or reload an existing model from the database. For more information, see Navigate the tree. For the API reference, see StartMetadataModelImport.
Assess — Analyze the selected source metadata models to identify conversion complexity and potential issues. For more information, see Creating database migration assessment reports with DMS Schema Conversion.
Convert — Convert the selected source metadata models to a target-compatible format. The converted definitions are stored as part of the target metadata tree. For more information, see Converting database schemas in DMS Schema Conversion: step-by-step guide.
Export — Save metadata definitions. You can export both source and target metadata as SQL scripts to your Amazon S3 bucket. For non-virtual targets, you can also apply converted objects directly to your target database. For more information about virtual targets, see Virtual data provider. For more information about applying converted objects, see Applying your converted code.

After you assess or convert source metadata models, you can generate an assessment report to review the results. For more information, see Creating database migration assessment reports with DMS Schema Conversion.

For supported migration pairs, you can also create custom statement metadata models from SQL definitions using StartMetadataModelCreation. For more information, see Create statement models.

Navigate the metadata model tree

You can navigate the metadata tree using the following API requests:

DescribeMetadataModelChildren — Returns the children of a given metadata model. Each child includes selection rules (filters that identify specific metadata models) that you can pass to the next call to drill deeper.
DescribeMetadataModel — Returns the name, type, and SQL definition of a specific metadata model.

Both requests require the Origin parameter (SOURCE or TARGET) and use selection rules to identify the metadata model. The navigation pattern is the same for both source and target trees. For more information about the selection rules format, see Selection rules in DMS Schema Conversion.

Choose your preferred interface to view navigation instructions.

AWS Management Console

After you open your schema conversion project, you can browse both source and target metadata trees. For more information about opening a project, see Converting database schemas in DMS Schema Conversion: step-by-step guide.

The console displays two tree panels: the source tree on the left and the target tree on the right. Each panel consists of a header that shows the database engine, an Actions menu, and the metadata tree itself.

The Actions menu is context-dependent. Not every metadata model has all actions available. Source and target trees have different action lists.

To view the SQL definition and properties of a metadata model, choose it in the tree. The center panel displays the Source SQL and Target SQL tabs with the definition, and a Properties tab with metadata attributes. Additional tabs might appear depending on the metadata model type — for example, Columns for tables and views, or Parameters for routines.

To expand the children of a metadata model, choose the expand icon (black triangle) next to it. Choose either the expand icon or the metadata model name to load its children automatically.

When you choose a source metadata model that has been converted, the corresponding target metadata model is selected automatically in the target tree panel.

Browse the metadata tree

The following walkthrough shows how to browse the metadata tree and view a metadata model definition.

In the tree panel, choose the expand icon next to your server to display the top-level categories, such as Databases or Schemas depending on the database engine.
Continue expanding categories to navigate to the metadata model you want to explore. For example, expand Databases, then a specific database, then Schemas, then a schema name, then a category such as Tables, Views, or Procedures. Some database engines don't have the Databases level — in that case, expand Schemas directly.
Choose a metadata model to view its SQL definition in the center panel. The Source SQL tab shows the source definition, and the Target SQL tab shows the converted definition if the metadata model has been converted.

AWS CLI

Browse the metadata tree

To browse the metadata tree, you follow a repeating pattern: import metadata at a level, then describe the children at that level. Repeat this process to drill deeper into the tree.

Start by calling start-metadata-model-import with a selection rule that identifies the top-level metadata model. Include the server-name and a top-level category-name (either Databases or Schemas, depending on the database engine) in the object-locator. For more information about the selection rules structure, see Selection rules in DMS Schema Conversion.


aws dms start-metadata-model-import \
    --migration-project-identifier "my-migration-project" \
    --origin SOURCE \
    --refresh \
    --selection-rules '{"rules":[{"rule-type":"selection","rule-id":"1",
      "rule-name":"1","object-locator":{"server-name":"my-server","category-name":"top-level-category"},"rule-action":"explicit"}]}'

Import requests are asynchronous. Use describe-metadata-model-imports to check the status before browsing. Use the request-id filter with the RequestIdentifier value returned by start-metadata-model-import to find a specific import:


aws dms describe-metadata-model-imports \
    --migration-project-identifier "my-migration-project" \
    --filters Name=request-id,Values=request-identifier

After the import completes, call describe-metadata-model-children to retrieve the children at that level:


aws dms describe-metadata-model-children \
    --migration-project-identifier "my-migration-project" \
    --origin SOURCE \
    --selection-rules '{"rules":[{"rule-type":"selection","rule-id":"1",
      "rule-name":"1","object-locator":{"server-name":"my-server"},
      "rule-action":"explicit"}]}'

Each child in the response includes a SelectionRules field. Pass these selection rules to the next import and describe calls to drill deeper:


{
    "MetadataModelChildren": [
        {
            "MetadataModelName": "Schemas",
            "SelectionRules": "{\"rules\":[{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"1\",\"object-locator\":{\"server-name\":\"src-database-server\",\"category-name\":\"Schemas\"},\"rule-action\":\"explicit\"}]}"
        }
    ]
}

To drill into the next level, import at that level using the selection rules returned from the previous describe-metadata-model-children call, and then describe its children:


aws dms start-metadata-model-import \
    --migration-project-identifier "my-migration-project" \
    --origin SOURCE \
    --refresh \
    --selection-rules 'selection-rules-from-previous-response'

Before describing children, ensure the import has completed by checking its status with describe-metadata-model-imports. Then call describe-metadata-model-children:


aws dms describe-metadata-model-children \
    --migration-project-identifier "my-migration-project" \
    --origin SOURCE \
    --selection-rules 'selection-rules-from-previous-response'

The response returns the children at that level, each with their own selection rules. Pass these selection rules to the next import and describe calls to continue drilling deeper into the tree.


{
    "MetadataModelChildren": [
        {
            "MetadataModelName": "child-name",
            "SelectionRules": "selection-rules-JSON-string"
        }
    ]
}

Using --refresh compared to full import

Use --refresh to reload a specific metadata model from the database. This also loads the names of its direct children. You can list these children using describe-metadata-model-children, but to describe or browse them further, you must run another import at the child level.

To load an entire subtree (for example, all objects in a schema, a database, or a specific category such as Tables or Procedures), omit the --refresh flag and change rule-action to include in the selection rules:


aws dms start-metadata-model-import \
    --migration-project-identifier "my-migration-project" \
    --origin SOURCE \
    --selection-rules '{"rules":[{"rule-type":"selection","rule-id":"1",
      "rule-name":"1","object-locator":{...},
      "rule-action":"include"}]}'

Loading a full subtree can take minutes or hours for large schemas and places load on your database. For interactive exploration, use --refresh and drill into specific branches as needed.

Get the definition of a metadata model

To retrieve the SQL definition of a metadata model, use describe-metadata-model. Pass the selection rules that identify the specific metadata model from a previous describe-metadata-model-children response:


aws dms describe-metadata-model \
    --migration-project-identifier "my-migration-project" \
    --origin SOURCE \
    --selection-rules 'selection-rules-for-target-object'

The response includes the metadata model name, type, and SQL definition:


{
    "MetadataModelName": "employees",
    "MetadataModelType": "table",
    "Definition": "CREATE TABLE hr.employees(\n    id integer NOT NULL,\n    name varchar(100),\n    department_id integer\n);"
}

Note

The Definition field might not be populated for some metadata models, such as schemas and categories.

View converted definitions

After you convert source metadata models, the converted definitions are available on the target tree. There are three ways to retrieve them.

Using target selection rules from the source response

When you describe a source metadata model after conversion, the response includes a TargetMetadataModels field with the target selection rules:


{
    "MetadataModelName": "employees",
    "MetadataModelType": "table",
    "TargetMetadataModels": [
        {
            "MetadataModelName": "employees",
            "SelectionRules": "{\"rules\":[{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"1\",\"object-locator\":{\"server-name\":\"tgt-database-server\",\"schema-name\":\"hr\",\"table-name\":\"employees\",\"table-type\":\"table\"},\"rule-action\":\"explicit\"}]}"
        }
    ],
    "Definition": "CREATE TABLE hr.employees(\n    id integer NOT NULL,\n    name varchar(100),\n    department_id integer\n);"
}

Pass the selection rules from TargetMetadataModels to describe-metadata-model with --origin TARGET:


aws dms describe-metadata-model \
    --migration-project-identifier "my-migration-project" \
    --origin TARGET \
    --selection-rules 'selection-rules-from-TargetMetadataModels'

The response contains the converted SQL definition in the target engine syntax:


{
    "MetadataModelName": "employees",
    "MetadataModelType": "table",
    "Definition": "CREATE TABLE hr.employees (\nid INT NOT NULL,\nname VARCHAR(100) DEFAULT NULL,\ndepartment_id INT DEFAULT NULL,\nPRIMARY KEY (id)\n);"
}

Using get-target-selection-rules

You can use get-target-selection-rules to convert source selection rules into their target counterparts directly, without first calling describe-metadata-model on the source:


aws dms get-target-selection-rules \
    --migration-project-identifier "my-migration-project" \
    --selection-rules 'source-selection-rules'

Navigating the target tree directly

You can also browse the target tree the same way as the source tree, using --origin TARGET. This is useful when you want to explore all converted objects without starting from the source.

Create statement metadata models

Note

Statement creation currently supports only the following directions: from SQL Server to Aurora PostgreSQL, or from SQL Server to Amazon RDS for PostgreSQL.

You can create statement metadata models from SQL definitions using StartMetadataModelCreation. This is useful when you want to convert SQL statements that do not exist as objects in your source database — for example, application queries or ad-hoc SQL code.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Concepts

Selection rules