About Copying Foreign Server Objects - Teradata Data Mover

Teradata Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.00
Published
December 2016
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
rdo1467305237457.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem

The foreign server object, introduced in Teradata Database 15.0, enables you to create definitions for querying data from a non-Teradata system, such as Hadoop, Aster, or Oracle. Data Mover supports copying those foreign server objects definitions from one Teradata system to another Teradata system.

All the foreign server objects are kept in the database TD_SERVER_DB. This database should exist in any Teradata Database 15.0 and later.

A foreign server object DDL for a Hadoop system is shown below. A database user with proper permission can create foreign server objects for non-Teradata systems for which system information is known. For information about how to create, update, drop, or use foreign server objects, refer to the Teradata Database documentation.

Example

Create a foreign server object by specifying CREATE FOREIGN SERVER, the name of the object, and server information.
CREATE FOREIGN SERVER MyServer USING 
server('sdll9119.labs.teradata.com')
port('9083')
hosttype('hadoop')
hiveport ('10000')
username('hive')
DO IMPORT WITH syslib.load_from_hcatalog_hdp1_3_2 
USING transformformatting('true'),
DO EXPORT WITH syslib.load_to_hcatalog_hdp1_3_2;
The example above creates an object MyServer, which defines the remote hadoop server sdll7680.labs.teradata.com, with the access port 9083.
  • You can retrieve data from MyServer on a Teradata Database system using function load_from_hcatalog_hdp1_3_2.
  • You can export data to MyServer using function load_to_hcatalog_hdp1_3_2.
  • The object MyServer is created in a database TD_SERVER_DB; you do not need to use a fully-qualified name.

Requirements and Restrictions

  • Creating and copying foreign server objects requires that the respective foreign server packages are installed and configured properly on the Teradata Database system. If you need assistance, contact the Global Technical Support Center at https://access.teradata.com.
  • The Data Mover job user must be granted permission for foreign server objects, or the job will fail.
  • Data Mover retrieves the foreign objects DDL by executing the SHOW FOREIGN <foreign_server_name> SQL statement.
  • The Teradata Database system user must be granted permission for foreign server objects, or the job will fail.
  • Both source and target systems must be Teradata systems at version 15.0 or later.
  • Data Mover does not parse a foreign server object DDL; the exact same DDL is executed on the target system.
  • Copy foreign server object definitions only.
  • Relocation is not supported; all foreign server objects must be in database TD_SERVER_DB.
  • Partial copy is not supported.
  • Row count validation is not supported.
  • Compare DDL is not supported.
  • Using staging tables and staging databases is not supported when moving foreign server object definitions.
  • Foreign server object definitions can be copied using ARC, TPT, or JDBC.
  • If a foreign server with the same name exists on the target system and the overwrite_existing_objects tag is set to true, the foreign server is deleted and recreated on target system. When a foreign object already exists and overwrite_existing_objects is false, an exception occurs.