Copy Foreign Server Objects | Teradata Data Mover - About Copying Foreign Server Objects - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.20
Published
November 2021
Language
English (United States)
Last Update
2021-11-04
dita:mapPath
wph1512683331242.ditamap
dita:ditavalPath
4101_UG_dm_1620.ditaval
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 exists in Teradata Database 15.0 or 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.

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://support.teradata.com.
  • The Data Mover job user must be granted permission for foreign server objects; otherwise, the job fails.
  • 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; otherwise, the job fails.
  • 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 run 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, DSA, TPT, or JDBC.
  • If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.

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.