Forum Discussion

dwhansen-cbg's avatar
dwhansen-cbg
Contributor
3 years ago
Solved

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!

redshift
  • dwhansen-cbg's avatar
    dwhansen-cbg
    3 years ago

    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.

9 Replies

  • ptaylor's avatar
    ptaylor
    Employee
    3 years ago

    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.

  • ptaylor's avatar
    ptaylor
    Employee
    3 years ago

    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?

  • dwhansen-cbg's avatar
    dwhansen-cbg
    Contributor
    3 years ago

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

    • ptaylor's avatar
      ptaylor
      Employee
      3 years ago

      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.

  • ptaylor's avatar
    ptaylor
    Employee
    3 years ago

    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?

    • dwhansen-cbg's avatar
      dwhansen-cbg
      Contributor
      3 years ago

      @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's avatar
        ptaylor
        Employee
        3 years ago

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