2

For reasons beyond my control, I have the following:

  • A table CustomerPhoneNumber in DynamoDB under one AWS account.
  • A Redshift cluster under a different AWS account (same geographic region; EU)

Is there any way to run the COPY command to move data from Dynamo into Redshift across accounts?

Typically if they were under the same account, it would be done via IAM role pretty easily: 

copy public.my_table (col1, col2, col3) from 'dynamodb://CustomerPhoneNumber' iam_role 'arn:aws:iam::XXXXXXXXXXX:role/RandomRoleName' readratio 40;

But obviously this doesn't work in my case.

Any ideas?

Ray
  • 3,137
  • 8
  • 32
  • 59

3 Answers3

2

Answer above by John is not applicable any more; This is how you can do it-

  1. AWS account with required resource like dynamodb in this case(trustING account) need to have account requiring access(trusTED AWS account)... as trusted in their Dynamo db read only role: arn:aws:iam:::role/
  2. Create a policy which does sts:AssumeRole (above trustING account's role arn as the resource),
  3. attach that policy to redshift-access-role(which has all privileges required to run the copy command).

  4. Run the command as:

    iam_role 'arn:aws:iam::<trusTEDawsAccountId>:role/redshift_access_role,arn:aws:iam::<trusTINGawsAccountId>:role/<dynamodbreadrole>' 
readratio 50

Details in: https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html

Max Vollmer
  • 8,412
  • 9
  • 28
  • 43
Rohit
  • 21
  • 2
  • This worked, and could be accepted as the right answer. Followed [this](https://aws.amazon.com/premiumsupport/knowledge-center/redshift-s3-cross-account/) guide for setting up cross-account access. – Rushi Agrawal May 13 '20 at 11:13
1

You can use CREDENTIALS and specify the access key and secret key for the other account. Add the following to your COPY statement:

credentials 'aws_access_key_id=AKIAXXXXX;aws_secret_access_key=yyyyyy'

You cannot use cross account roles with Redshift. To quote Amazon documentation:

An IAM role can be associated with an Amazon Redshift cluster only if both the IAM role and the cluster are owned by the same AWS account.

Authorizing COPY and UNLOAD Operations Using IAM Roles

John Hanley
  • 74,467
  • 6
  • 95
  • 159
-1

apparently stackoverflow need formatting, the code is:

copy redshift_tbl from 'dynamodb://dynamotbl'
iam_role 'arn:aws:iam::<TRUSTEDacAWSid>:role/redshift_access_role,arn:aws:iam::<trusTINGacAWSid>:role/<dynamodb-role-in-trustingac>' 
readratio 50

*Note: no space between commas in roles

Rohit
  • 21
  • 2
  • Please edit your original answer, instead of posting another one with a correction. I have edited your original answer for you. I recommend you delete this one. – Max Vollmer Dec 10 '19 at 20:54