17.10 - Example: Copying Foreign Table without Data - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

This example uses CREATE TABLE AS … WITH NO DATA to create a copy of a foreign table without its data. The copy has the same options as the original and accesses the same external data.

CREATE FOREIGN TABLE Statement for Original Foreign Table

The following statement creates the foreign table riverflow_0627. Vantage detects the JSON data type and creates the JSON payload column.

CREATE FOREIGN TABLE riverflow_0627, 
  EXTERNAL SECURITY MyAuthObj
  USING (
    LOCATION ('/S3/YOUR-BUCKET.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
  );

CREATE TABLE AS … WITH NO DATA Statement for Copy

CREATE TABLE rivercopy_0627_t
AS riverflow_0627 WITH NO DATA;

Definition of Copy

Show the definition of the table:

SHOW TABLE myDB.rivercopy_0627_t;

Result:

CREATE MULTISET FOREIGN TABLE myDB.rivercopy_0627_t ,FALLBACK ,
     EXTERNAL SECURITY MyAuthObj ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE)
USING
(
      LOCATION  ('/S3/YOUR-BUCKET.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 ;