How do I troubleshoot issues with the UNLOAD command in Amazon Redshift?

Short description

When you run the UNLOAD command, you might receive an error message that's similar to one of the following:

• "error: User arn:aws:redshift:us-west-2:account-id:dbuser:cluster-identifier/dbuser username is not authorized to assume IAM Role arn:aws:iam::account-id:role/Role name"
• "[Amazon](500310) Invalid operation: S3ServiceException:Access Denied,Status 403,Error AccessDenied,"
• "ERROR: Specified unload destination on S3 is not empty. Consider using a different bucket / prefix, manually removing the target files in S3, or using the ALLOWOVERWRITE option."

To resolve the issue, take one of the following actions based on the error message that you receive:

• Authorize the database to assume the AWS Identity and Access Management (IAM) role.
• Grant your Amazon S3 operations the appropriate permissions.
• Remove or overwrite the objects in destination bucket. Change the S3 key prefix. Or, use a different bucket.

Resolution

Authorize the database to assume the IAM role

To resolve this issue, associate the IAM role with your Amazon Redshift cluster. When you include the IAM role in the UNLOAD command, make sure that there are no trailing spaces. Also, make sure that you use the correct trust relationship for the IAM role that you assign to the Amazon Redshift cluster.

Grant your Amazon S3 operations the appropriate permissions 

If you receive a 403 Access Denied error from your S3 bucket, then grant your S3 API operations the appropriate permissions.

Example policy:

{
"Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:Get*",       
        "s3:List*",
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::your_bucket",
        "arn:aws:s3:::your_bucket/*"
      ]
    }
  ]
}

If you're using server-side encryption with Amazon S3 managed encryption keys (SSE-S3), then your S3 bucket encrypts each object with a unique key. 

To encrypt the unloaded data at rest, run the following command to set the s3:x-amz-server-side-encryption parameter in your S3 bucket policy to true:

"Condition": {   "Null": {
           "s3:x-amz-server-side-encryption": "true"
           }

Run the following command to confirm that you encrypted KMS_KEY_ID so that you can unload the encrypted data to your S3 bucket:

unload ('select * from a') to 's3://mybucket/test/varchar/' iam_role 'arn:aws:iam::xxxxxx:role/RedshiftS3Access' ALLOWOVERWRITE CSV
KMS_KEY_ID '11111111111111111111111111'
encrypted;

For S3 buckets that you encrypted with an AWS Key Management Service (AWS KMS) root key, add the following IAM policy permissions:

"Action": [
"kms:Encrypt",
"kms:Decrypt",
"kms:ReEncrypt*",
"kms:GenerateDataKey*",
"kms:DescribeKey"
]

Resolve bucket issues

When you're trying to unload files in a folder where the same file name exists, you receive an error message.

To resolve this issue, take one of the following actions:

• Create a new S3 bucket, or use a different S3 bucket.
• Change the S3 key prefix.
• Remove the files that are in the destination S3 bucket.
• Include the ALLOWOVERWRITE option in the UNLOAD command.

Create a new S3 bucket, or use a different S3 bucket

1. Create a new S3 bucket, or choose a different S3 bucket.
2. Update the bucket policy of the new or different S3 bucket to include the following permissions:

{
    "Version": "2012-10-17",
    "Id": "ExamplePolicy01",
    "Statement": [
        {
            "Sid": "ExampleStatement01",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::0123456789:user/<UserName>"
            },
            "Action": [
                "s3:GetObject",
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::testbucket/*",
                "arn:aws:s3:::testbucket",
                "arn:aws:s3:::testbucket2/*",  --> Additional permission for new bucket
                "arn:aws:s3:::testbucket2"       
            ]
        }
    ]
}

3. Use the new bucket path in the UNLOAD command.
Example command:

unload ('select * from test_unload')   
to 's3://testbucket2/unload/test_unload_file1'
iam_role 'arn:aws:iam::0123456789:role/redshift_role';

Change the S3 key prefix

When you run the UNLOAD command, change the S3 bucket key prefix in the S3 path.

Example command that includes unload2 as the changed prefix:

unload ('select * from test_unload')   
to 's3://testbucket/unload2/test_unload_file1'
iam_role 'arn:aws:iam::0123456789:role/redshift_role';

For more information, see Organizing objects using prefixes.

Remove the files that are in the destination S3 bucket

Important: When you use the CLEANPATH option to remove files, you're permanently deleting the files and can't recover them. Also, if you specify the ALLOWOVERWRITE option, then you can't specify the CLEANPATH option.

To use the CLEANPATH option, you must have the s3:DeleteObject permission on the S3 bucket.

Example permissions policy:

{
    "Version": "2012-10-17",
    "Id": "ExamplePolicy01",
    "Statement": [
        {
            "Sid": "ExampleStatement01",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::0123456789:user/<UserName>"
            },
            "Action": [
                "s3:GetObject",
                "s3:GetBucketLocation",
                "s3:ListBucket",
                "s3:DeleteObject"  --> Additional Action added for delete object
            ],
            "Resource": [
                "arn:aws:s3:::testbucket/*",
                "arn:aws:s3:::testbucket"
            ]
        }
    ]
}

To remove existing files from only the partition folders, include PARTITION BY. 

Example UNLOAD command that includes CLEANPATH and PARTITION BY:

unload ('select * from test_unload')  
to 's3://testbucket/unload/test_unload_file1
iam_role 'arn:aws:iam::0123456789:role/redshift_role'
partition by (col1,col2) include
CLEANPATH;

Note: If you don't want to remove the partition columns form the unloaded files, then specify PARTITION BY with the INCLUDE option.

Use the ALLOWOVERWRITE option

If the UNLOAD command finds files that it might overwrite, then the unload operation fails. When you include ALLOWOVERWRITE in the command, UNLOAD overwrites existing files, including the manifest file.

To use the ALLOWOVERWRITE option, you must have the s3:PutObject permission on the S3 bucket.

Example permissions policy:

{
    "Version": "2012-10-17",
    "Id": "ExamplePolicy01",
    "Statement": [
        {
            "Sid": "ExampleStatement01",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::0123456789:user/<UserName>"
            },
            "Action": [
                "s3:GetObject",
                "s3:GetBucketLocation",
                "s3:ListBucket",
                "s3:PutObject"  --> Additional Action added for overwriting objects
            ],
            "Resource": [
                "arn:aws:s3:::testbucket/*",
                "arn:aws:s3:::testbucket"
            ]
        }
    ]
}

Example UNLOAD command that includes the ALLOWOVERWRITE option:

unload ('select * from test_unload')   
to 's3://testbucket/unload/test_unload_file1
iam_role 'arn:aws:iam::0123456789:role/redshift_role' allowoverwrite;

Related information

Authorizing Amazon Redshift to access AWS services on your behalf

How to prevent uploads of unencrypted objects to Amazon S3

Queries appear to hang and sometimes fail to reach the cluster

Unloading encrypted data files

Policies and permissions in Amazon S3