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.
• The IAM role doesn't have the required S3 bucket permissions.
• The database user doesn't have the necessary 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.

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.

For more information, see Parameters.

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 issue, associate the IAM role with the Amazon Redshift cluster.

Complete the following steps:

1. Open the Amazon Redshift console or the AWS CLI.

2. Run one of the following commands:
   For Amazon Redshift Provisioned run the modify-cluster-iam-roles AWS CLI command:

   ## Redshift Provisioned
   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:

   ## Redshift Serverless
   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.

The IAM role doesn't have the required S3 permissions

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

"S3ServiceException:User: arn:aws:sts::<Account ID>:assumed-role/<IAM Role Name>/RedshiftIamRoleSession is not authorized to perform: s3:PutObject on resource: "<S3 Object Path>" because no identity-based policy".

Check that your IAM role has the required IAM permissions to successfully unload data to the target S3 bucket.

The database user doesn't have the necessary table or schema permissions

If the database user doesn't have the necessary permissions on the table or schema, then the user might get one of the following error messages:

• "ERROR: permission denied for schema <Schema Name>".
• "ERROR: permission denied for relation <Table Name>".

To grant the database user USAGE permission on the schema and SELECT permission on the table, run one of the following GRANT commands:

GRANT USAGE ON SCHEMA Schema Name TO DB User Name;

GRANT SELECT ON TABLE Table Name TO DB User Name;

Note: In the preceding commands, replace Schema Name with your schema's name, Table name with your table's name, and User Name with the user's username.

Additional troubleshooting

To identify the query ID and the error messages, see STL_QUERY or SYS_QUERY_HISTORY.

For more information, see STL_ERROR or SYS_UNLOAD_HISTORY.