Copy data from Teradata using Azure Data Factory

This article outlines how to use the Copy Activity in Azure Data Factory to copy data from a Teradata database. It builds on the copy activity overview article that presents a general overview of copy activity.

Supported capabilities

You can copy data from Teradata database to any supported sink data store. For a list of data stores that are supported as sources/sinks by the copy activity, see the Supported data stores table.

Specifically, this Teradata connector supports:

  • Teradata version 12 and above.
  • Copying data using Basic or Windows authentication.

Prerequisites

To use this Teradata connector, you need to:

Getting started

You can use one of the following tools or SDKs to use Copy Activity with a pipeline. Select a link for step-by-step instructions:

The following sections provide details about properties that are used to define Data Factory entities specific to Teradata connector.

Linked service properties

The following properties are supported for Teradata linked service:

Property Description Required
type The type property must be set to: Teradata Yes
server Name of the Teradata server. Yes
authenticationType Type of authentication used to connect to the Teradata database.
Allowed values are: Basic, and Windows.
Yes
username Specify user name to connect to the Teradata database. Yes
password Specify password for the user account you specified for the username. Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. Yes
connectVia The Integration Runtime to be used to connect to the data store. A Self-hosted Integration Runtime is required as mentioned in Prerequisites. Yes

Example:

{
    "name": "TeradataLinkedService",
    "properties": {
        "type": "Teradata",
        "typeProperties": {
            "server": "<server>",
            "authenticationType": "Basic",
            "username": "<username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see the datasets article. This section provides a list of properties supported by Teradata dataset.

To copy data from Teradata, set the type property of the dataset to RelationalTable. The following properties are supported:

Property Description Required
type The type property of the dataset must be set to: RelationalTable Yes
tableName Name of the table in the Teradata database. No (if "query" in activity source is specified)

Example:

{
    "name": "TeradataDataset",
    "properties": {
        "type": "RelationalTable",
        "linkedServiceName": {
            "referenceName": "<Teradata linked service name>",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {}
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by Teradata source.

Teradata as source

To copy data from Teradata, set the source type in the copy activity to RelationalSource. The following properties are supported in the copy activity source section:

Property Description Required
type The type property of the copy activity source must be set to: RelationalSource Yes
query Use the custom SQL query to read data. For example: "SELECT * FROM MyTable". No (if "tableName" in dataset is specified)

Example:

"activities":[
    {
        "name": "CopyFromTeradata",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Teradata input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "RelationalSource",
                "query": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Data type mapping for Teradata

When copying data from Teradata, the following mappings are used from Teradata data types to Azure Data Factory interim data types. See Schema and data type mappings to learn about how copy activity maps the source schema and data type to the sink.

Teradata data type Data factory interim data type
BigInt Int64
Blob Byte[]
Byte Byte[]
ByteInt Int16
Char String
Clob String
Date DateTime
Decimal Decimal
Double Double
Graphic String
Integer Int32
Interval Day TimeSpan
Interval Day To Hour TimeSpan
Interval Day To Minute TimeSpan
Interval Day To Second TimeSpan
Interval Hour TimeSpan
Interval Hour To Minute TimeSpan
Interval Hour To Second TimeSpan
Interval Minute TimeSpan
Interval Minute To Second TimeSpan
Interval Month String
Interval Second TimeSpan
Interval Year String
Interval Year To Month String
Number Double
Period(Date) String
Period(Time) String
Period(Time With Time Zone) String
Period(Timestamp) String
Period(Timestamp With Time Zone) String
SmallInt Int16
Time TimeSpan
Time With Time Zone String
Timestamp DateTime
Timestamp With Time Zone DateTimeOffset
VarByte Byte[]
VarChar String
VarGraphic String
Xml String

Next steps

For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see supported data stores.