Examples: Using SNAPSHOT_LOCATION with FOREIGN TABLE - Teradata Database

Create foreign table

CREATE FOREIGN TABLE test_sales
 , EXTERNAL SECURITY INVOKER TRUSTED AWS_AUTH_1
 USING (
 LOCATION ('s3://pg255044/sales/')
 SNAPSHOT_LOCATION('s3://pg255044/MetaCacheManifestFile/')
   );

Output (manifest file created at Object Store)

sdt60785:~ # s3cmd ls s3://pg255044/MetaCacheManifestFile/ -r
2025-06-05 10:00         1064  s3://pg255044/MetaCacheManifestFile/f3cf79af-66d9-4752-bf72-15690cbf47e8/U2/TEST_SALES/manifest_0000.json

Show table

SHOW TABLE test_sales;

CREATE MULTISET FOREIGN TABLE U2.test_sales ,FALLBACK ,
     EXTERNAL SECURITY INVOKER TRUSTED AWS_AUTH_1 ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      State VARCHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Zipcode INTEGER,
      "TransactionTime" VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
      Retailer VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC,
      TransactionId VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC,
      SoldCnt SMALLINT,
      StockCnt SMALLINT)
USING
(
      LOCATION  ('s3://pg255044/sales/')
      SNAPSHOT_LOCATION  ('s3://pg255044/MetaCacheManifestFile/')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$var1/$var2/$State/$var4/$var5/$var6')
      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 
PARTITION BY ( var2 VARCHAR(28),State VARCHAR(3),var4 INTEGER,
var5 VARCHAR(3) );

SELECT on foreign table

SELECT * from test_sales;

Location
---------------------------------------------------------------------------
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAB.csv
S3://pg255044/sales/US_west_region/CA/191231/AA/recCAAA.csv
S3://pg255044/sales/US_south_region/FL/200131/AA/recFLAA.csv
S3://pg255044/sales/US_west_region/CA/200131/BB/recCABB.csv
S3://pg255044/sales/US_west_region/AZ/200131/BB/recAZBB.csv
S3://pg255044/sales/US_west_region/AZ/191231/AA/recAZAA.csv
S3://pg255044/sales/US_east_region/NY/191231/AA/recNYAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAB.csv
S3://pg255044/sales/US_west_region/CA/191231/AA/recCAAA.csv
S3://pg255044/sales/US_south_region/FL/200131/AA/recFLAA.csv
S3://pg255044/sales/US_west_region/CA/200131/BB/recCABB.csv
S3://pg255044/sales/US_west_region/AZ/200131/BB/recAZBB.csv
S3://pg255044/sales/US_west_region/AZ/191231/AA/recAZAA.csv
S3://pg255044/sales/US_east_region/NY/191231/AA/recNYAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAB.csv

DROP of a foreign table

DROP TABLE test_sales;

Files will be deleted during the cleanup process after the DROP.

s3cmd ls s3://pg255044/MetaCacheManifestFile/ -r | wc -l
0

s3cmd ls s3://pg255044/MetaCacheManifestFile/ -r

Transform normal FOREIGN TABLE to Snapshot Directory Listing FOREIGN TABLE

Use ALTER to add the SNAPSHOT_LOCATION to the normal FOREIGN TABLE. This will transform the table to cache metadata table and write a manifest file in the cache location provided.

CREATE FOREIGN TABLE test_sales_TB
 , EXTERNAL SECURITY INVOKER TRUSTED AWS_AUTH_1
 USING (
 LOCATION ('s3://pg255044/sales/')
   );

SHOW TABLE test_sales_TB;        
SHOW TABLE test_sales_TB;

CREATE MULTISET FOREIGN TABLE U2.test_sales_TB ,FALLBACK ,
     EXTERNAL SECURITY INVOKER TRUSTED AWS_AUTH_1 ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      State VARCHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Zipcode INTEGER,
      "TransactionTime" VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
      Retailer VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC,
      TransactionId VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC,
      SoldCnt SMALLINT,
      StockCnt SMALLINT)
USING
(
      LOCATION  ('s3://pg255044/sales/')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$var1/$var2/$State/$var4/$var5/$var6')
      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 
PARTITION BY ( var2 VARCHAR(29),State VARCHAR(3),var4 INTEGER,
var5 VARCHAR(3) );

Perform ALTER to update the SNAPSHOT_LOCATION.

ALTER FOREIGN TABLE U2.test_sales_TB
UPDATE 
SNAPSHOT_LOCATION('s3://pg255044/MetaCacheManifestTBFile/');

Manifest file generated at provided SNAPSHOT_LOCATION path.

s3cmd ls -r s3://pg255044/MetaCacheManifestTBFile/
2025-06-05 11:16          933  s3://pg255044/MetaCacheManifestTBFile/54f34291-dcd8-4747-bb0a-03cd9c67f783/U2/TEST_SALES_TB/manifest_0000.json

SELECT * FROM U2.test_sales_TB;

Location
---------------------------------------------------------------------------
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAB.csv
S3://pg255044/sales/US_west_region/CA/191231/AA/recCAAA.csv
S3://pg255044/sales/US_south_region/FL/200131/AA/recFLAA.csv
S3://pg255044/sales/US_west_region/AZ/200131/BB/recAZBB.csv
S3://pg255044/sales/US_west_region/AZ/191231/AA/recAZAA.csv
S3://pg255044/sales/US_east_region/NY/191231/AA/recNYAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAB.csv
S3://pg255044/sales/US_west_region/CA/191231/AA/recCAAA.csv
S3://pg255044/sales/US_south_region/FL/200131/AA/recFLAA.csv
S3://pg255044/sales/US_west_region/AZ/200131/BB/recAZBB.csv
S3://pg255044/sales/US_west_region/AZ/191231/AA/recAZAA.csv
S3://pg255044/sales/US_east_region/NY/191231/AA/recNYAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAA.csv
S3://pg255044/sales/US_north_region/MN/191231/AA/recMNAB.csv