Use ALTER FOREIGN TABLE to remove columns from DDL that are no longer present in Parquet files.
Schema evolution allows the removal of columns from many position in Parquet files.
If schema evolution is disabled, the ALTER FOREIGN TABLE query returns an error.
Original Schema
EmpName City DeptNo EmpID ------ ------ ------ ------ John Austin 1759 65784 Robert Atlanta 6294 69824 Frank Columbus 2197 48392 Colin Phoenix 4372 72985 Brandon Hartford 7863 34862
Column 3 Removed
EmpName DeptNo EmpID ------- ------ ------ John 1759 65784 Robert 6294 69824 Frank 2197 48392 Colin 4372 72985 Brandon 7863 34862
Show Columns
SHOW TABLE table nos_table;
Result:
CREATE MULTISET FOREIGN TABLE NOS_SCHEMA.nos_table ,FALLBACK ,
EXTERNAL SECURITY NOS_SCHEMA.TDAWSEDBS_AUTH ,
MAP = TD_MAP1
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
EmpID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
EmpName VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
City VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
DeptNo VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
USING
(
LOCATION ('/s3/s3.amazonaws.com/bucket/PARQUETDATA/')
MANIFEST ('FALSE')
PATHPATTERN ('$var1/$var2/$var3/$var4')
STOREDAS ('PARQUET')
SCHEMA_EVOLUTION ('TRUE')
)
NO PRIMARY INDEX
PARTITION BY COLUMN ADD 65527;
ALTER FOREIGN TABLE nos_table DROP col3;
Result:
*** Table has been modified. *** Warning: 9557 Collect or refresh single and multicolumn PARTITION Statistics. *** Total elapsed time was 1 second.