Schema and data type mapping in copy activity

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article describes how the Azure Data Factory copy activity performs schema mapping and data type mapping from source data to sink data.

Schema mapping

Default mapping

By default, copy activity maps source data to sink by column names in a case-sensitive manner. If the sink doesn't exist, such as when writing to files, the source field names become the sink names. If the sink already exists, it must contain all columns being copied from the source. This default mapping supports flexible schemas and schema drift from source to sink from execution to execution - all the data returned by source data store can be copied to sink.

If your source is a text file without a header line, you need to use explicit mapping because the source doesn't contain column names.

Explicit mapping

Specify explicit mapping to customize the column and field mapping from source to sink. By using explicit mapping, you can copy only part of the source data to the sink, map source data to sink with different names, or reshape tabular or hierarchical data. The copy activity:

1. Reads the data from the source and determines the source schema.
2. Applies your defined mapping.
3. Writes the data to the sink.

Learn more about:

• Tabular source to tabular sink
• Hierarchical source to tabular sink
• Tabular/Hierarchical source to hierarchical sink

Configure the mapping in the Authoring UI by going to the copy activity and selecting the mapping tab. Or, programmatically specify the mapping in the copy activity by using the translator property. The following properties are supported in translator -> mappings array -> objects -> source and sink, which point to the specific column or field to map data.

The following properties are supported under translator in addition to mappings:

Tabular source to tabular sink

For example, to copy data from Salesforce to Azure SQL Database and explicitly map three columns:

1. On the copy activity, select the mapping tab, and then select Import schemas to import both source and sink schemas.

2. Map the needed fields and exclude or delete the rest.

Configure the same mapping in the copy activity payload (see translator).

{
    "name": "CopyActivityTabularToTabular",
    "type": "Copy",
    "typeProperties": {
        "source": { "type": "SalesforceSource" },
        "sink": { "type": "SqlSink" },
        "translator": {
            "type": "TabularTranslator",
            "mappings": [
                {
                    "source": { "name": "Id" },
                    "sink": { "name": "CustomerID" }
                },
                {
                    "source": { "name": "Name" },
                    "sink": { "name": "LastName" }
                },
                {
                    "source": { "name": "LastModifiedDate" },
                    "sink": { "name": "ModifiedDate" }
                }
            ]
        }
    },
    ...
}

To copy data from delimited text files without a header line, represent the columns by ordinal instead of names.

{
    "name": "CopyActivityTabularToTabular",
    "type": "Copy",
    "typeProperties": {
        "source": { "type": "DelimitedTextSource" },
        "sink": { "type": "SqlSink" },
        "translator": {
            "type": "TabularTranslator",
            "mappings": [
                {
                    "source": { "ordinal": "1" },
                    "sink": { "name": "CustomerID" }
                }, 
                {
                    "source": { "ordinal": "2" },
                    "sink": { "name": "LastName" }
                }, 
                {
                    "source": { "ordinal": "3" },
                    "sink": { "name": "ModifiedDate" }
                }
            ]
        }
    },
    ...
}

Hierarchical source to tabular sink

When you copy data from a hierarchical source to a tabular sink, the copy activity supports the following capabilities:

• Extract data from objects and arrays.
• Cross apply multiple objects with the same pattern from an array, in which case to convert one JSON object into multiple records in tabular result.

For more advanced hierarchical-to-tabular transformation, use Data Flow.

For example, if you have source Azure DocumentDB or MongoDB document with the following content:

{
    "id": {
        "$oid": "592e07800000000000000000"
    },
    "number": "01",
    "date": "20170122",
    "orders": [
        {
            "prod": "p1",
            "price": 23
        },
        {
            "prod": "p2",
            "price": 13
        },
        {
            "prod": "p3",
            "price": 231
        }
    ],
    "city": [ { "name": "Seattle" } ]
}

To copy the data into a text file, use the following format with a header line. Flatten the data inside the arrays (order_pd and order_price) and use a cross join with the common root information (number, date, and city):

Define this mapping in the Data Factory authoring UI:

1. On copy activity, go to the Mapping tab and select Import schemas to import both source and sink schemas. As the service samples the top few objects when importing schema, if any field doesn't appear, add it to the correct layer in the hierarchy - hover on an existing field name and choose to add a node, an object, or an array.

2. Select the array from which you want to iterate and extract data. The UI autopopulates Collection reference. Note that this operation supports only a single array.

3. Map the needed fields to sink. The service automatically determines the corresponding JSON paths for the hierarchical side.

You can also switch to Advanced editor. You can directly see and edit the fields' JSON paths. If you choose to add new mapping in this view, specify the JSON path.

You can configure the same mapping in copy activity payload (see translator):

{
    "name": "CopyActivityHierarchicalToTabular",
    "type": "Copy",
    "typeProperties": {
        "source": { "type": "MongoDbV2Source" },
        "sink": { "type": "DelimitedTextSink" },
        "translator": {
            "type": "TabularTranslator",
            "mappings": [
                {
                    "source": { "path": "$['number']" },
                    "sink": { "name": "orderNumber" }
                },
                {
                    "source": { "path": "$['date']" },
                    "sink": { "name": "orderDate" }
                },
                {
                    "source": { "path": "['prod']" },
                    "sink": { "name": "order_pd" }
                },
                {
                    "source": { "path": "['price']" },
                    "sink": { "name": "order_price" }
                },
                {
                    "source": { "path": "$['city'][0]['name']" },
                    "sink": { "name": "city" }
                }
            ],
            "collectionReference": "$['orders']"
        }
    },
    ...
}

Tabular/Hierarchical source to hierarchical sink

The user experience flow is similar to Hierarchical source to tabular sink.

When copying data from a tabular source to a hierarchical sink, the service doesn't support writing to an array inside an object.

When copying data from a hierarchical source to a hierarchical sink, you can preserve an entire layer's hierarchy by selecting the object or array and mapping to sink without touching the inner fields.

For more advanced data reshape transformation, use Data Flow.

Parameterize mapping

To create a templatized pipeline that dynamically copies a large number of objects, first determine whether you can use the default mapping or if you need to define explicit mapping for each object.

If you need explicit mapping, follow these steps:

1. Define a parameter with an object type at the pipeline level, such as mapping.

2. Parameterize the mapping: On the copy activity, go to the mapping tab, choose to add dynamic content, and select the parameter you created. The activity payload is the following:

   {
       "name": "CopyActivityHierarchicalToTabular",
       "type": "Copy",
       "typeProperties": {
           "source": {...},
           "sink": {...},
           "translator": {
               "value": "@pipeline().parameters.mapping",
               "type": "Expression"
           },
           ...
       }
   }
   

3. Construct the value to pass into the mapping parameter. It should be the entire object of the translator definition. For samples, see the explicit mapping section. For example, for tabular source to tabular sink copy, the value should be {"type":"TabularTranslator","mappings":[{"source":{"name":"Id"},"sink":{"name":"CustomerID"}},{"source":{"name":"Name"},"sink":{"name":"LastName"}},{"source":{"name":"LastModifiedDate"},"sink":{"name":"ModifiedDate"}}]}.

Data type mapping

Copy activity maps source types to sink types by using the following flow:

1. Convert from source native data types to interim data types used by Azure Data Factory and Synapse pipelines.
2. Automatically convert the interim data type as needed to match the corresponding sink types. This step applies to both default mapping and explicit mapping.
3. Convert from interim data types to sink native data types.

Copy activity currently supports the following interim data types: Boolean, Byte, Byte array, Datetime, DatetimeOffset, Decimal, Double, GUID, Int16, Int32, Int64, SByte, Single, String, Timespan, UInt16, UInt32, and UInt64.

The following data type conversions are supported between the interim types from source to sink.

(1) Date/Time includes DateTime, DateTimeOffset, Date, and Time.

(2) Float-point includes Single and Double.

(3) Integer includes SByte, Byte, Int16, UInt16, Int32, UInt32, Int64, and UInt64.

Copy activity supports the following properties for data type conversion (under the translator section for programmatic authoring):

Example:

{
    "name": "CopyActivity",
    "type": "Copy",
    "typeProperties": {
        "source": {
        	"type": "ParquetSource"
        },
        "sink": {
            "type": "SqlSink"
        },
        "translator": {
            "type": "TabularTranslator",
            "typeConversion": true,
            "typeConversionSettings": {
                "allowDataTruncation": true,
                "treatBooleanAsNumber": true,
                "dateTimeFormat": "yyyy-MM-dd HH:mm:ss.fff",
                "dateTimeOffsetFormat": "yyyy-MM-dd HH:mm:ss.fff zzz",
                "timeSpanFormat": "dd\.hh\:mm",
                "culture": "en-gb"
            }
        }
	},
    ...
}

Legacy models

Alternative column-mapping (legacy model)

To map between tabular-shaped data, specify copy activity -> translator -> columnMappings. In this case, both input and output datasets require the structure section. Column mapping supports mapping all or a subset of columns in the source dataset structure to all columns in the sink dataset structure. The following error conditions result in an exception:

• Source data store query result doesn't have a column name that you specified in the input dataset structure section.
• Sink data store (if with predefined schema) doesn't have a column name that you specified in the output dataset structure section.
• Either fewer columns or more columns in the structure of sink dataset than specified in the mapping.
• Duplicate mapping.

In the following example, the input dataset has a structure, and it points to a table in an on-premises Oracle database.

{
    "name": "OracleDataset",
    "properties": {
        "structure":
         [
            { "name": "UserId"},
            { "name": "Name"},
            { "name": "Group"}
         ],
        "type": "OracleTable",
        "linkedServiceName": {
            "referenceName": "OracleLinkedService",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {
            "tableName": "SourceTable"
        }
    }
}

In this sample, the output dataset has a structure and it points to a table in Salesforce.

{
    "name": "SalesforceDataset",
    "properties": {
        "structure":
        [
            { "name": "MyUserId"},
            { "name": "MyName" },
            { "name": "MyGroup"}
        ],
        "type": "SalesforceObject",
        "linkedServiceName": {
            "referenceName": "SalesforceLinkedService",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {
            "tableName": "SinkTable"
        }
    }
}

The following JSON defines a copy activity in a pipeline. The columns from source map to columns in sink by using the translator -> columnMappings property.

{
    "name": "CopyActivity",
    "type": "Copy",
    "inputs": [
        {
            "referenceName": "OracleDataset",
            "type": "DatasetReference"
        }
    ],
    "outputs": [
        {
            "referenceName": "SalesforceDataset",
            "type": "DatasetReference"
        }
    ],
    "typeProperties":    {
        "source": { "type": "OracleSource" },
        "sink": { "type": "SalesforceSink" },
        "translator":
        {
            "type": "TabularTranslator",
            "columnMappings":
            {
                "UserId": "MyUserId",
                "Group": "MyGroup",
                "Name": "MyName"
            }
        }
    }
}

If you use the syntax "columnMappings": "UserId: MyUserId, Group: MyGroup, Name: MyName" to specify column mapping, it's still supported as-is.

Alternative schema mapping (legacy model)

You can specify copy activity -> translator -> schemaMapping to map between hierarchical-shaped data and tabular-shaped data. For example, you can copy from MongoDB or REST to a text file, and copy from Oracle to Azure Cosmos DB for MongoDB or Azure DocumentDB (with MongoDB compatibility). The copy activity translator section supports the following properties:

Example: copy from MongoDB to Oracle:

For example, if you have a MongoDB document with the following content:

{
    "id": {
        "$oid": "592e07800000000000000000"
    },
    "number": "01",
    "date": "20170122",
    "orders": [
        {
            "prod": "p1",
            "price": 23
        },
        {
            "prod": "p2",
            "price": 13
        },
        {
            "prod": "p3",
            "price": 231
        }
    ],
    "city": [ { "name": "Seattle" } ]
}

and you want to copy it into an Azure SQL table in the following format by flattening the data inside the array (order_pd and order_price) and cross join with the common root info (number, date, and city):

Configure the schema mapping rule as the following copy activity JSON sample:

{
    "name": "CopyFromMongoDBToOracle",
    "type": "Copy",
    "typeProperties": {
        "source": {
            "type": "MongoDbV2Source"
        },
        "sink": {
            "type": "OracleSink"
        },
        "translator": {
            "type": "TabularTranslator",
            "schemaMapping": {
                "$.number": "orderNumber",
                "$.date": "orderDate",
                "prod": "order_pd",
                "price": "order_price",
                "$.city[0].name": "city"
            },
            "collectionReference":  "$.orders"
        }
    }
}

Related content

See the other Copy Activity articles:

• Copy activity overview