How do I troubleshoot issues with UNLOAD in Amazon Redshift?

Last updated: 2020-09-25

I'm trying to unload data from my Amazon Redshift cluster to Amazon Simple Storage Service (Amazon S3). However, I'm getting an error. How I troubleshoot this?

Short description

When unloading data from your Amazon Redshift cluster to your Amazon S3 bucket, you might encounter the following errors:

DB user is not authorized to assume the AWS Identity and Access Management (IAM) Role error

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> 4

403 Access Denied error

[Amazon](500310) Invalid operation: S3ServiceException:Access Denied,Status 403,Error AccessDenied,

Note: Use the UNLOAD command with the SELECT statement when unloading data to your S3 bucket. Unload the text data in either a delimited or fixed-width format (regardless of the data format used while being loaded). You can also specify whether a compressed Gzip file should be filed.

Resolution

DB user is not authorized to assume the AWS IAM Role error

If the database user isn't authorized to assume the IAM role, check the following:

403 Access Denied error

If you receive a 403 Access Denied error from your S3 bucket, confirm that the proper permissions are granted for your S3 API operations:

  "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 are using server-side encryption with S3-managed encryption keys, then your S3 bucket encrypts each of its objects with a unique key. As an additional safeguard, the key itself is also encrypted with a master key that is regularly rotated. 

To encrypt the unloaded data at rest, verify that the s3:x-amz-server-side-encryption parameter in your S3 bucket policy is set to "true":

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

Confirm that KMS_KEY_ID is encrypted 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;

If your S3 bucket is encrypted with a master key from AWS Key Management Service (AWS KMS), then add these permissions to your IAM policy:

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

Authorizing Amazon Redshift to access other 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

Did this article help?

Submit feedback

Do you need billing or technical support?

Contact AWS Support