Example: HELP TABLE with Foreign Table - Teradata VantageCloud Lake

The CREATE FOREIGN TABLE Statement creates a foreign table with a PARTITION BY clause even if you do not specify one. The PARTITION BY clause determines the virtual columns. HELP TABLE output identifies virtual columns.

CREATE FOREIGN TABLE Statement

CREATE MULTISET FOREIGN TABLE CRIMEDATA,FALLBACK ,
     EXTERNAL SECURITY DEFINER TRUSTED S3 ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      ID_Number BIGINT,
      Case_Number VARCHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Year_x BIGINT,
      Month_x VARCHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Day_x BIGINT,
      Arrest BYTEINT,
      District FLOAT)
USING
(
      LOCATION  ('/s3/s3.amazonaws.com/crimedata-vcol-pq/')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$city/$year_x/$month_x/$var3')
      STOREDAS  ('PARQUET')
)
NO PRIMARY INDEX
PARTITION BY (COLUMN, CITY VARCHAR(2), YEAR_X BIGINT, MONTH_X VARCHAR(3) );

HELP TABLE Statement

HELP TABLE CRIMEDATA;

 *** Help information returned. 4 rows.
 *** Total elapsed time was 1 second.
 
Column Name                    Type Comment
------------------------------ ---- ----------------------------------------------------------------
Location                       CV   ?
ID_Number                      I8   ?
Case_Number                    CV   ?
Year_x                         I8   ?
Month_x                        CV   ?
Day_x                          I8   ?
Arrest                         I1   ?
District                       F    ?
CITY                           CV   Virtual Column