Example: SHOW TABLE for a Foreign Table - Advanced SQL Engine - Teradata Database

This is an example of the SHOW TABLE output for a foreign table.

Below is the table definition for the example.

CREATE FOREIGN TABLE Ftable1,  
 EXTERNAL SECURITY DEFINER TRUSTED DefAuth_S3
 ( 
  Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
  Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE 
 )
USING (LOCATION ('/s3/td-usgs.s3.amazonaws.com/DATA/') 
      )
NO PRIMARY INDEX; 

This statement displays the definition for the foreign table Table1.

 SHOW TABLE Ftable1; 

The statement returns the following definition for the foreign table, including the FOREIGN keyword and the USING clause options LOCATION, MANIFEST, PATHPATTERN, ROWFORMAT, and STOREDAS.

CREATE MULTISET FOREIGN TABLE MyDB.Ftable1, 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/DATA/')
      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; 

This statement returns the table definition in XML format.

SHOW IN XML Table Ftable1; 

Below is the foreign table definition in XML format.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?><TeradataDBObjectSet version="1.0" xmlns="http://schemas.teradata.com/dbobject" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.teradata.com/dbobject http://schemas.teradata.com/dbobject/DBObject.xsd"><Table authName="DefAuth_S3" authType="DEFINER TRUSTED" baseClass="Table" dbName="MyDB" fallback="true" foreigntable="true" kind="Multiset" map="TD_MAP1" map_kind="contiguous" name="Ftable1" objId="0:3457" objVer="1"><ColumnList><Column name="Location" nullable="true" order="1"><DataType><Char casespecific="true" charset="UNICODE" length="2048" uppercase="false" varying="true"/></DataType></Column><Column name="Payload" nullable="true" order="2"><DataType><JSON charset="UNICODE" inlinelength="32000" size="8388096"/></DataType></Column></ColumnList><UsingClauseList><Clause name="LOCATION" value="/s3/td-usgs.s3.amazonaws.com/DATA/"/><Clause name="MANIFEST" value="FALSE"/><Clause name="PATHPATTERN" value="$Var1/$Var2/$Var3/$Var4/$Var5/$Var6/$Var7/$Var8/$Var9/$Var10/$Var11/$Var12/$Var13/$Var14/$Var15/$Var16/$Var17/$Var18/$Var19/$Var20"/><Clause name="ROWFORMAT" value="{&quot;record_delimiter&quot;:&quot;\n&quot;, &quot;character_set&quot;:&quot;UTF8&quot;}"/><Clause name="STOREDAS" value="TEXTFILE"/></UsingClauseList><Indexes><NoPrimaryIndex/></Indexes><SQLText><![CDATA[CREATE MULTISET FOREIGN TABLE MyDB.Ftable1, 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/DATA/')
  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 ]]></SQLText></Table><Environment><Server dbRelease="17H.00.00.119" dbVersion="17H.00.00.119" hostName="localhost"/><User userId="00001A04" userName="Cindy"/><Session charset="UTF8" dateTime="2019-10-03T13:24:32"/></Environment></TeradataDBObjectSet>