Example: Creating Foreign Table Using Path Style - Teradata Vantage

CREATE FOREIGN TABLE Statement

CREATE  FOREIGN TABLE test_customer ,
     EXTERNAL SECURITY INVOKER TRUSTED TDAWSEDBS_AUTH
    USING
(
      LOCATION  ('/s3/s3.amazonaws.com/bucket/URI3/')
                )
NO PRIMARY INDEX ;

SHOW TABLE Statement

SHOW TABLE test_customer;

Result:

CREATE MULTISET FOREIGN TABLE NOS_USR.test_customer ,FALLBACK ,
     EXTERNAL SECURITY INVOKER TRUSTED TDAWSEDBS_AUTH ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      id BYTEINT,
      name VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
      gender CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
USING
(
      LOCATION  ('/s3/s3.amazonaws.com/bucket/URI3/')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$var1/$var2')
      ROWFORMAT  ('{"field_delimiter":",","record_delimiter":"\n","charact
er_set":"LATIN"}')
      STOREDAS  ('TEXTFILE')
      HEADER  ('TRUE')
      STRIP_EXTERIOR_SPACES  ('FALSE')
      STRIP_ENCLOSING_CHAR  ('NONE')
)
NO PRIMARY INDEX ;

SELECT Statement

SELECT * FROM test_customer;

Result:

Location
---------------------------------------------------------------------------
/S3/s3.amazonaws.com/bucket/URI3/sample.csv
/S3/s3.amazonaws.com/bucket/URI3/test.csv
/S3/s3.amazonaws.com/bucket/URI3/sample.csv
/S3/s3.amazonaws.com/bucket/URI3/test.csv