MSCK Repair table command works only after the table is recreated

0
votes

MSCK REPAIR TABLE command adds partitions only after recreating the table.

MSCK Repair table does not add the partitions to the table but it lists the partitions not in the metastore. However, when I recreate the table and run the MSCK Repair table command, it works. But the next day I run the MSCK Repair table command to add the new partitions to the metastore it does not add the partitions. But if I recreate the table again and run the MSCK repair table command, then the partitions are added.

This issue suddenly started and I'm not sure what to make of it. Anyone with any idea on what might be going on is greatly appreciated.

Below is the message I receive when the repair table fails.

Partitions not in metastore:
prod_partner_visits:year=2021/month=06/day=09/hour=00

Partitions missing from filesystem:
prod_partner_visits:year=2021/month=06/day=09/hour=00

But I have that partition in the s3 bucket and it will add the partition when I recreate the athena table.

Below is the DDL of the table

CREATE EXTERNAL TABLE `prod_partner_visits`(   
  `visitortoken` string COMMENT 'from deserializer',    
  `timestamp` string COMMENT 'from deserializer',    
  `timeuuid` string COMMENT 'from deserializer',   
  `event` string COMMENT 'from deserializer',    
  `message` string COMMENT 'from deserializer'
)   
PARTITIONED BY (      
  `year` int,   
  `month` int,    
  `day` int,    
  `hour` int
)  
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'  
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'  
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'   
LOCATION 's3://partner-traffic-channel-prod/topics/prod-cp-channel-visit'  
TBLPROPERTIES (   
  'has_encrypted_data'='false',   
  'transient_lastDdlTime'='1623329273'
)
2
amazon-web-servicesamazon-athena
Can you show us what it says when you try to repair the table but it fails? - John Rotenstein
Below is message I receive when the repair table fails. Partitions not in metastore: prod_partner_visits:year=2021/month=06/day=09/hour=00 Partitions missing from filesystem: prod_partner_visits:year=2021/month=06/day=09/hour=00 - roni4743
It seems very strange that it is wanting to add the same directory that it then reports as "missing" from storage. Do you have any files stored in that directory? - John Rotenstein
Yes I have files in the directory. - roni4743
Could you post the table definition (for example via generate create table ddl)? - Nicolas Busca

2 Answers

1
votes

The problem here is that you have partitions of type int, while MSCK REPAIR TABLE will only correctly add partitions when the column types are string.

Here are some alternatives:

  1. ALTER TABLE ADD PARTITION... https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.html

  2. Partition projection (recommended): https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html

1
votes

One reason that could explain this is that your partition keys are not actually integers. Consider this query:

SELECT *
FROM prod_partner_visits
WHERE year = 2021
AND month = 6
AND day = 9
AND hour = 3

Athena would look for a partition with the keys (2021, 6, 9, 3), corresponding tot he location s3://partner-traffic-channel-prod/topics/prod-cp-channel-visit/year=2021/month=6/day=9/hour=3. However, the correct location is …/year=2021/month=06/day=09/hour=03 – notice the leading zeroes.

This is not the reason why MSCK REPAIR TABLE fails, but I think it illustrates the problem. I suspect something similar goes on inside of that command when it reads for example "09" and interprets it as the integer 9.

MSCK REPAIR TABLE is not great to use other than when loading partitions for a new table. You shouldn't use it continously, because it's very inefficient (see Why is MSCK REPAIR TABLE so slow? for an explanation why).

There is a solution where Athena will automatically pick up the partitions for you: Partition Projection. You should be able to use the example for Kinesis Firehose to get started.