Trouble Partitioning my Amazon Spectrum Table

Question

Getting this error in particular:

ERROR: Error when calling external catalog API: The number of partition keys do not match the number of partition values

Answer 1

Just posting this because googling that error turned nothing up. This can happen when you ALTER TABLE ADD PARTITION on an external table created without the PARTITIONED BY clause.

Read this for details: CREATE EXTERNAL TABLE docs. In retrospect, you, like me, should have read this before trying this command.

Answer 2

In my case I only specified one partition field in the ALTER TABLE ADD PARTITION statement while in the CREATE EXTERNAL TABLE I declared:

PARTITIONED BY (year varchar(4), month varchar(2), day varchar(2))

So the user is supposed to ADD PARTITION declaring the whole path, e.g.:

alter TABLE my_external_schema.my_external_table
add partition (year='2022', month='01', day='01') 
location 's3://my_bucket/year=2022/month=01/day=01/';