例: 外部テーブルのSHOW TABLE - Advanced SQL Engine - Teradata Database

これは、外部テーブルのSHOW TABLE出力の例です。

以下は、テーブル定義の例です。

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; 

この文は、外部テーブルTable1の定義を表示します。

 SHOW TABLE Ftable1; 

この文は、外部テーブルについての次の定義を返します(FOREIGNキーワードと、USING句のオプション(LOCATION、MANIFEST、PATHPATTERN、ROWFORMAT、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; 

この文は、XML形式のテーブル定義を返します。

SHOW IN XML Table Ftable1; 

以下は、XML形式の外部テーブル定義です。

<?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>