Configure PolyBase to access external data in Teradata

APPLIES TO: yesSQL Server (Windows only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This article explains how to use PolyBase on a SQL Server instance to query external data in Teradata.

Prerequisites

If you haven't installed PolyBase, see PolyBase installation. The installation article explains the prerequisites.

To use PolyBase on Teradata, VC++ redistributable is needed.

Configure an external table

To query the data from a Teradata data source, you must create external tables to reference the external data. This section provides sample code to create these external tables.

These objects are created in this section:

  • CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
  • CREATE EXTERNAL DATA SOURCE (Transact-SQL)
  • CREATE EXTERNAL TABLE (Transact-SQL)
  • CREATE STATISTICS (Transact-SQL)
  1. Create a master key on the database, if one doesn't already exist. A master key is required to encrypt the credential secret.

     CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
    

    Arguments

    PASSWORD ='password'

    Is the password that's used to encrypt the master key in the database? The password must meet the Windows password policy requirements of the computer that hosts the instance of SQL Server.

  2. Create a database scoped credential.

    /*  specify credentials to external data source
     *  IDENTITY: user name for external source.  
    *  SECRET: password for external source.
    */
    CREATE DATABASE SCOPED CREDENTIAL credential_name
    WITH IDENTITY = 'username', Secret = 'password'
    
  3. Create an external data source with CREATE EXTERNAL DATA SOURCE.

    /*  LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    *  PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    *  CREDENTIAL: the database scoped credential, created above.
    */  
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH ( 
    LOCATION = teradata://<server address>[:<port>],
    -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );
    
  4. Create external tables that represent data stored in an external Teradata system with CREATE EXTERNAL TABLE.

    /*  LOCATION: Teradata table/view in '<database_name>.<object_name>' format
     *  DATA_SOURCE: the external data source, created above.
     */
    CREATE EXTERNAL TABLE customer(
     L_ORDERKEY INT NOT NULL,
     L_PARTKEY INT NOT NULL,
    L_SUPPKEY INT NOT NULL,
    L_LINENUMBER INT NOT NULL,
    L_QUANTITY DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
    L_DISCOUNT DECIMAL(15,2) NOT NULL,
    L_TAX DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG CHAR NOT NULL,
    L_LINESTATUS CHAR NOT NULL,
    L_SHIPDATE DATE NOT NULL,
    L_COMMITDATE DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE CHAR(10) NOT NULL,
    L_COMMENT VARCHAR(44) NOT NULL
    )
    WITH (
    LOCATION='customer',
    DATA_SOURCE= external_data_source_name
    );
    
  5. Optional: Create statistics on an external table.

    For optimal query performance, create statistics on external table columns, especially the ones used for joins, filters, and aggregates.

     CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 
    

Next steps

To learn more about PolyBase, see Overview of SQL Server PolyBase.