Example: Altering LOCATION Alters PARTITION BY - Teradata Vantage - Analytics Database

This example does not use the Tables for Examples.

Show Table before Alteration

SHOW TABLE t2;

CREATE MULTISET FOREIGN TABLE PQ.t2 ,FALLBACK ,
     EXTERNAL SECURITY DEFINER TRUSTED S3_AUTH ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      i1 BYTEINT,
      i2 BYTEINT,
      c1 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      c2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
USING
(
      LOCATION  ('/s3/s3.amazonaws.com/nos-opt-csv/PartitionBy/')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$var1/$year_a/$month_a/$var4')
      ROWFORMAT  ('{"field_delimiter":",","record_delimiter":"\r\n","character_set":"LATIN"}')
      STOREDAS  ('TEXTFILE')
      HEADER  ('TRUE')
      STRIP_EXTERIOR_SPACES  ('FALSE')
      STRIP_ENCLOSING_CHAR  ('NONE')
)
NO PRIMARY INDEX
PARTITION BY ( year_a SMALLINT,month_a BYTEINT );

Alter LOCATION

ALTER FOREIGN TABLE t2 UPDATE LOCATION ('/S3/s3.amazonaws.com/nos-opt-csv/PartitionBy/year_a=2021/');

Show Table after Alteration

SHOW TABLE t2;

CREATE MULTISET FOREIGN TABLE PQ.t2 ,FALLBACK ,
     EXTERNAL SECURITY DEFINER TRUSTED S3_AUTH ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      i1 BYTEINT,
      i2 BYTEINT,
      c1 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      c2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
USING
(
      MANIFEST  ('FALSE')
      ROWFORMAT  ('{"field_delimiter":",","record_delimiter":"\r\n","character_set":"LATIN"}')
      STOREDAS  ('TEXTFILE')
      HEADER  ('TRUE')
      STRIP_EXTERIOR_SPACES  ('FALSE')
      STRIP_ENCLOSING_CHAR  ('NONE')
      location  ('/S3/s3.amazonaws.com/nos-opt-csv/PartitionBy/year_a=2021/')
      PATHPATTERN  ('$var1/$year_a/$month_a/$var4')
)
NO PRIMARY INDEX
PARTITION BY ( month_a BYTEINT );