Redshift Bulk Load on Cloudplex

[dwhansen-cbg]

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!

[dwhansen-cbg]

@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.

[ptaylor]

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

[dwhansen-cbg · Accepted solution]

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.

[ptaylor]

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.

[dwhansen-cbg]

@ptaylor I agree. That would definitely be helpful.