How to make MSCK REPAIR TABLE execute automatically in AWS Athena

Question

What steps do I need to take to ensure MSCK REPAIR TABLE runs automatically in AWS Athena?

Answer 1

• I will show you how to automate the MSCK REPAIR TABLE in AWS Athena using AWS Glue Crawler or AWS Lambda.

• Let me explain one by one.

• If you use an AWS Glue Crawler, it will automatically scan your S3 bucket and keep your Athena table's metadata up-to-date. No need to manually run the MSCK REPAIR TABLE—Glue handles all of it for you!

• This option is great for larger or more complex datasets.

• Suppose you are using AWS Lambda. You can create a lambda function to run the command on your Athena table.

• This is how you set up the Lambda function in Python.

              

• But remember to replace your_table_name with the name of your Athena table, your_database_name with the name of your database, and s3://your-query-results-bucket/ with the S3 location where you want Athena query results to be stored.

• Lastly, a CloudWatch Event should be set up to trigger this Lambda function regularly.

Learn more about AWS Athena with this tutorial: Athena + Glue + S3 Data. Check it out here!