MSCK REPAIR TABLE returns FAILED org.apache.hadoop.hive.ql.exec.DDLTask. nu

Curious if you ever were able to get the root cause for this. We had the same problem (very intermittent).

Thanks
-Bill

SET mapred.input.dir.recursive=true;
SET hive.mapred.supports.subdirectories=true;
ALTER TABLE table_name RECOVER PARTITIONS;

I had same error, but resolved it to attach "s3:ListBucket" permission for underlying bucket on execution role. If you run the query from Lambda function or other AWS services, please try to add following policy on execution role. Athena needs to traverse folders to load partitions.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": "arn:aws:s3:::{YOUR_BUCKET_NAME}",
            "Effect": "Allow"
        }
    ]
}

I had the same issue until I added permissions for action glue:BatchCreatePartition. Found that here https://aws.amazon.com/premiumsupport/knowledge-center/athena-aws-glue-msck-repair-table/

Hi BillMan2,

Did you ever get to the bottom of your issues? I am also getting this error intermittently.

Cheers
Ben

That error usually means there’s a problem with the table structure or partitions. I faced something similar once while troubleshooting a home repairs database, and fixing the partition paths cleared it up!

For me, this turned out to be permissions (in the context of running the query from a lambda, and with setup in CDK).

It's worth remembering that when you are working in the Athena Query Editor, you are likely using Athena Full Access role, which grants all actions for Athena, Glue and S3 across all resources. If you are following the Least Permissive Principle you will likely want to contrain the actions and resources to only those required.

Unfortunately, Athena's error reports hide the underlying reason for failure when its Glue or S3 related, which makes analysing the problem harder than necessary.

I created the following functions to apply the necessary permissions to my Lambda construct.

  grantRepair(grantee: IGrantable) {
    grantee.grantPrincipal.addToPrincipalPolicy(new PolicyStatement({
      sid: 'Glue',
      actions: [
        "glue:GetDatabase",
        "glue:GetDatabases",
        "glue:GetTable",
        "glue:GetTables",
        "glue:UpdateTable",
        "glue:CreateTable",
        "glue:BatchCreatePartition",
        "glue:BatchDeletePartition",
        'glue:GetPartition',
        'glue:GetPartitions',
        "glue:UpdatePartition",
        "glue:BatchGetPartition"
      ],
      resources: [
        Arn.format({ service: 'glue', resource: 'catalog'}, Stack.of(this)),
        this.database.databaseArn,
        this.tableArn,
      ],
    }))

  grantQueryExecution(grantee: IGrantable) {
    grantee.grantPrincipal.addToPrincipalPolicy(new PolicyStatement({
      sid: 'Athena',
      actions: [
        'athena:GetDataCatalog',
        'athena:GetWorkGroup',
        'athena:StartQueryExecution',
        'athena:StopQueryExecution',
        'athena:GetQueryExecution',
        'athena:GetQueryResults',
      ],
      resources: [
        Arn.format({ service: 'athena', resource: 'workgroup', resourceName: this.workgroup.name }, Stack.of(this)),
        Arn.format({ service: 'athena', resource: 'datacatalog', resourceName: 'AwsDataCatalog' }, Stack.of(this)),
      ],
    }))
    grantee.grantPrincipal.addToPrincipalPolicy(new PolicyStatement({
      sid: 'ListAthenaWorkGroups',
      actions: [
        'athena:ListWorkGroups',
        'athena:ListDataCatalogs',
      ],
      resources: ['*'],
    }))