Example: Copying a Foreign Table, Not Including Data - Advanced SQL Engine - Teradata Database

You can use CREATE TABLE ... AS and specify the WITH NO DATA clause to create a copy of a foreign table with same options as source foreign table. When you specify the WITH NO DATA clause, the definition of the source foreign table is copied to the target foreign table and the target foreign table accesses the same external data as the source foreign table.

Below is the source foreign table definition of riverflow_0627 for this example. The JSON payload column is created by the system automatically as it detects the JSON data type.

The DefAuth_S3 authorization object was created in Example: Specifying a Definer Authorization for a Foreign Table.

CREATE FOREIGN TABLE riverflow_0627, 
EXTERNAL SECURITY DEFINER TRUSTED DefAuth_S3
USING
 (
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
) ;

This statement creates the target foreign table rivercopy_0627_t with the same definition as the source foreign table riverflow_0627 as.

CREATE TABLE rivercopy_0627_t
AS riverflow_0627 WITH NO DATA;

This statement shows the definition of the foreign table copy, rivercopy_0627_t.

CREATE MULTISET FOREIGN TABLE myDB.rivercopy_0627_t ,FALLBACK ,
     EXTERNAL SECURITY DEFINER TRUSTED DEFAUTH_S3 ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE)
USING
(
      LOCATION  ('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
      PATHPATTERN  ('$Var1/$Var2/$Var3/$Var4/$Var5/$Var6/$Var7/$Var8/$Var9/$Var10/$Var11/$Var12/$Var13/$Var14/$Var15/$Var16/$Var17/$Var18/$Var19/$Var20')
      ROWFORMAT  ('{"record_delimiter":"\n","character_set":"UTF8"}')
      STOREDAS  ('TEXTFILE')
)
NO PRIMARY INDEX ;