How do I resolve UNLOAD command and S3 access issues in Amazon Redshift?

Short description

When you run UNLOAD commands in Amazon Redshift, errors can occur for the following reasons:

• The target Amazon Simple Storage Service (Amazon S3) bucket or prefix contains existing objects.
• You didn't attach the AWS Identity and Access Management (IAM) role to your Amazon Redshift cluster.
• You didn't grant the IAM role the required S3 bucket permissions.
• You didn't grant the database user the required table or schema permissions.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Identify the query ID and error messages

Run the STL_QUERY or SYS_QUERY_HISTORY to view details of user queries. For more information, see STL_ERROR or SYS_UNLOAD_HISTORY.

Objects exist in the S3 bucket or prefix

If objects already exist in the target S3 bucket or prefix, then you might get the following error message:

"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 error, run the following UNLOAD command with the ALLOWOVERWRITE parameter:

UNLOAD ('<SELECT query>') 
TO 's3://S3 bucket or prefix' 
IAM_ROLE 'IAM Role' 
ALLOWOVERWRITE; 

Note: Replace S3 bucket or prefix with your S3 bucket or prefix and IAM Role with your IAM role.

You didn't associate the IAM role with the cluster

If you didn't associate the IAM role that's specified in the UNLOAD command with the Amazon Redshift cluster, then you get the following error message:

"UnauthorizedException, error type : 138, message: The requested role is not associated to cluster"

To resolve the error, associate the IAM role with the Amazon Redshift cluster. First, open the Amazon Redshift console or the AWS CLI. Then run one of the following commands:

For Amazon Redshift Provisioned run the modify-cluster-iam-roles AWS CLI command:

aws redshift modify-cluster-iam-roles \ 
    --cluster-identifier "Cluster Name" \ 
    --add-iam-roles "IAM Role ARN" 

Note: Replace Cluster Name with your cluster's name and IAM Role ARN with your IAM role's Amazon Resource Name (ARN).
For Amazon Redshift Serverless, run the update-namespace AWS CLI command:

aws redshift-serverless update-namespace \ 
    --namespace-name Namespace Name \ 
    --iam-roles "IAM Role ARN"

Note: Replace Namespace Name with your namespace's name and IAM Role ARN with your IAM role's ARN.

For more information, see Managing IAM role association with a cluster.

You didn't grant the IAM role the required S3 permissions

If the IAM role doesn't have the required S3 permissions, then the UNLOAD command fails and you get the following error message:

"S3ServiceException:User: arn:aws:sts:::assumed-role//RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "" because no identity-based policy".

To resolve this error, grant your IAM role the required IAM permissions to unload data to the target S3 bucket.

You didn't grant the database user the required table or schema permissions

If you didn't grant the database user the required permissions on the table or schema, then the user might get one of the following error messages:

"ERROR: permission denied for schema"

To resolve the error, grant the database user USAGE permission on the schema and SELECT permission on the table, run the following command:

GRANT USAGE ON SCHEMA Schema Name TO DB User Name

Note: Replace Schema Name with your schema's name and User Name with your user's name.

-or-

"ERROR: permission denied for relation"

To resolve the error, grant the database user USAGE permission on the schema and SELECT permission on the table, run the following command:

GRANT SELECT ON TABLE Table Name TO DB User Name;

Note: Replace Table Name with your table's name and User Name with your user's name.