Redshift Bulk Load on Cloudplex

We have a Redshift cluster that we would like to load data into. Using the Redshift - Insert snap is simple, but prohibitively slow and isn’t the recommended way to load data into a Redshift. We would like to use the Redshift - Bulk Load snap, but are running into a few issues when setting up the Redshift Account in SnapLogic.

  1. Our understanding is that using IAM Roles for authentication is NOT possible on a Cloudplex. Is this true? If so, this is a huge issue.
  2. If we can’t use an IAM Role for authentication, the only other option is an AWS Access Key with its secret and token. The main issue with this approach is that the tokens are temporary and only last a few hours at a maximum. How can we use an AWS Access Key with its secret and token without having to refresh the token every 15 minutes? This doesn’t seem useful.

Any help would be great. Thanks!

That’s a surprising statement. I’m not aware of any such time-based limitations on AWS access tokens, and this doesn’t match my experience with them, where I’ve used the same token+secret pair for months/years. Can you say more about this?

@ptaylor It’s possible I’m misunderstanding what the token is and how to get it, but my current understanding is that we need to get an AWS STS Token using either the AWS CLI or SDK. I’m basing my statement on what is in this documentation: get-session-token — AWS CLI 1.27.51 Command Reference

This duration can range from 900 seconds (15 minutes) up to a maximum of 129,600 seconds (36 hours), with a default of 43,200 seconds (12 hours).

Can you point me in the right direction on how to get a token (how you’re doing it so there isn’t a time-based limitation) so we can use the Bulk Load snap?

My apologies. I misunderstood. Some AWS resources like S3 buckets can be accessed with just an Access Key and Secret, which are long term credentials, depending on the security policies configured for that resource. But others may require an STS Token as well, which is temporary. It looks like our Redshift Bulk Load does require an STS Token when not using an IAM Role.

Let me see if I can bring attention to your question from someone who knows more about this topic than I do.

1 Like

It looks like our Redshift Bulk Load does require an STS Token when not using an IAM Role.

Actually, it doesn’t. If the bucket policy doesn’t require the use of a token, neither does the Bulk Load snap. Do you have access to any buckets that only require an Access Key and Secret Key, but not an STS Token?

@ptaylor None of our S3 buckets require an STS Token. We have other third party tools that use the “bulk load”/copy action in Redshift with only an Access Key and Secret Key. I’m not sure why it’s not working in SnapLogic.

Well in that case, in what way isn’t it working? Are you getting an error? If so, please share the details.

We think we figured out what was happening. Every 180 days we are required by our company policies to swap out our Access Keys and Secrets for security purposes. When we swapped them out, there was somehow a value in the Token field on the Redshift Account. We tried again by updating the Access Key and Secret, then clearing out the Token field and that seemed to do the trick. We’re not sure if this is a long-term fix or if it will fail again after a certain amount of time, but we are good to go for now.

1 Like

That’s great to hear! I’m glad it’s working for you now.

It would help if the UI made it possible to tell when an encrypted field has a value or not. Currently, it looks the same either way: “Value is encrypted” even if it has no value.

image

2 Likes

@ptaylor I agree. That would definitely be helpful.