Create an S3 event integration to automatically copy files from Amazon S3 buckets

Your Amazon S3 bucket must have a bucket policy that allows several Amazon S3 permissions. For example, the following example policy allows permissions for the resource bucket amzn-s3-demo-bucket that is hosted in us-east-1. Both the Amazon S3 bucket and the integration are in the same Amazon Web Services Region.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Auto-Copy-Policy-01",
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
                },
            "Action": [
                "s3:GetBucketNotification",
                "s3:PutBucketNotification",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::amzn-s3-demo-bucket",
            "Condition": {
                "StringLike": {
                    "aws:SourceArn": "arn:aws:redshift:us-east-1:123456789012:integration:*",
                    "aws:SourceAccount": "123456789012"
                }
            }
        }
    ]
}

Your target Amazon Redshift provisioned cluster or Redshift Serverless namespace must have permission to the bucket. Confirm an IAM role that is associated with your cluster or serverless namesspace has a IAM policy that allows the proper permissions. The policy must allow both s3:GetObject for a bucket resource such as amzn-s3-demo-bucket and s3:ListBucket for a bucket resource and its contents such as amzn-s3-demo-bucket/*.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AutoCopyReadId",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::amzn-s3-demo-bucket",  
                "arn:aws:s3:::amzn-s3-demo-bucket/*" 
            ]
        }
    ]
}

Add your policy to an IAM role that has a trust relationship for the role is as follows.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "redshift.amazonaws.com"
                ]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

If your target data warehouse is a provisioned cluster, you can associate an IAM role to your provisioned cluster using the Amazon Redshift console, Cluster permissions tab in your cluster details. For information about how to associate a role to your provisioned cluster, see Associating IAM roles with clusters in the Amazon Redshift Management Guide.

If your target data warehouse is Redshift Serverless, you can associate an IAM role to your serverless namespace using the Redshift Serverless console, Security and encryption tab in your namespace details. For information about how to associate a role to your serverless namespace, see Granting permissions to Amazon Redshift Serverless in the Amazon Redshift Management Guide.

Your Amazon Redshift data warehouse must also have a resource policy that allows the Amazon S3 bucket. If you use the Amazon Redshift console, when you create the s3 event integration, Amazon Redshift provides the option Fix it for me to add this policy to your Amazon Redshift data warehouse. To update a resource policy yourself, you can use the put-resource-policy Amazon CLI command. For example, to attach a resource policy to your Amazon Redshift provisioned cluster for an S3 event integration with an Amazon S3 bucket, run a Amazon CLI command similar to the following. The following example shows a policy for a provisioned cluster namespace in the us-east-1 Amazon Web Services Region for user account 123456789012. The bucket is named amzn-s3-demo-bucket.

aws redshift put-resource-policy \
--policy file://rs-rp.json \
--resource-arn "arn:aws:redshift: us-east-1:123456789012:namespace/cc4ffe56-ad2c-4fd1-a5a2-f29124a56433"

Where rs-rp.json contains:

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": {
				"Service": "redshift.amazonaws.com"
			},
			"Action": "redshift:AuthorizeInboundIntegration",
			"Resource": "arn:aws:redshift:us-east-1:123456789012:namespace/cc4ffe56-ad2c-4fd1-a5a2-f29124a56433",
			"Condition": {
				"StringEquals": {
					"aws:SourceArn": "arn:aws:s3:::amzn-s3-demo-bucket"
				}
			}
		},
		{
			"Effect": "Allow",
			"Principal": {
				"AWS": "arn:aws:iam::123456789012:role/myRedshiftRole"
			},
			"Action": "redshift:CreateInboundIntegration",
			"Resource": "arn:aws:redshift:us-east-1:123456789012:namespace/cc4ffe56-ad2c-4fd1-a5a2-f29124a56433",
			"Condition": {
				"StringEquals": {
					"aws:SourceArn": "arn:aws:s3:::amzn-s3-demo-bucket"
				}
			}
		}
	]
}

To attach a resource policy to your Redshift Serverless namespace for an S3 event integration with an Amazon S3 bucket, run a Amazon CLI command similar to the following. The following example shows a policy for a serverless namespace in the us-east-1 Amazon Web Services Region for user account 123456789012. The bucket is named amzn-s3-demo-bucket.

aws redshift put-resource-policy \
--policy file://rs-rp.json \
--resource-arn "arn:aws:redshift-serverless:us-east-1:123456789012:namespace/namespace-1"

Where rs-rp.json contains:

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": {
				"Service": "redshift.amazonaws.com"
			},
			"Action": "redshift:AuthorizeInboundIntegration",
			"Resource": "arn:aws:redshift-serverless:us-east-1:123456789012:namespace/namespace-1",
			"Condition": {
				"StringEquals": {
					"aws:SourceArn": "arn:aws:s3:::amzn-s3-demo-bucket"
				}
			}
		},
		{
			"Effect": "Allow",
			"Principal": {
				"AWS": "arn:aws:iam::123456789012:user/myUser"
			},
			"Action": "redshift:CreateInboundIntegration",
			"Resource": "arn:aws:redshift-serverless:us-east-1:123456789012:namespace/namespace-1",
			"Condition": {
				"StringEquals": {
					"aws:SourceArn": "arn:aws:s3:::amzn-s3-demo-bucket"
				}
			}
		}
	]
}

SYS_COPY_JOB – contains a row for each currently defined COPY JOB.

SYS_COPY_JOB_DETAIL – contains details on pending, error, and ingested files for each COPY JOB.

SYS_COPY_JOB_INFO – contains messages logged about a COPY JOB.

SYS_LOAD_HISTORY – contains details of COPY commands.

SYS_LOAD_ERROR_DETAIL – contains details of COPY command errors.

SVV_COPY_JOB_INTEGRATIONS – contains details of S3 event integrations.

STL_LOAD_ERRORS – contains errors from COPY commands.

STL_LOAD_COMMITS – contains information used to troubleshoot a COPY command data load.

You can create a maximum of 200 COPY JOBS for each cluster or workgroup in an Amazon Web Services account.

You can create a maximum of 35 copy jobs for each Amazon S3 bucket.

You can create a maximum of 50 S3 event integrations for each Amazon Redshift target.

You can't create an S3 event integration with a source Amazon S3 bucket which has a period (.) in the bucket name.