Skip to main content
Question

Redshift to Vertica migration issue

  • November 7, 2018
  • 5 replies
  • 18 views
Vertica_Curtis
Forum|alt.badge.img+1

So, this is less of a question, and more of a log of some research and (hopefully) resolution to an interesting problem we seem to be encountering in a POC at Finicity.

So, Finicity is doing a POC whereby they are extracting data from Redshift, unloading it into s3 (using Redshift's UNLOAD command) and then (attempting) to load the data from s3 into Vertica using COPY.

The initial problem was around Vertica's COPY statement. When running the COPY command in Vertica it generated an error that said "Access Denied". In this particular case, it was referencing a file (0020) out of 80 separate files in the s3 bucket. By default, Redshift extracts files in parallel, and creates many files per table.

This led us to wonder if there was some specific issue with file 0020. To test this, we used the AWS CLI to run a few simple tests.

I could ls the file correctly. But I could not copy it. The syntax is something like:
aws s3 cp s3:\bucket\file .

(Also, for the record, the vertica.log showed that every file in the set were generating the same error. Management console apparently just reports on the last file in the set, so it's a little confusing.)

That produced this error:
fatal error: An error occurred (403) when calling the HeadObject operation: Forbidden

Which led us to this blog:
http://www.stojanveselinovski.com/blog/2016/05/20/aws-s3-object-acl-and-403-error/

That suggested that the issue had to do with which AWS_PROFILE we were using. AWS_PROFILE is a convenient way of defining the AWS ID and KEY.

We then created a file manually (in Linux) and uploaded it to the s3 bucket, using the CLI. Then we copied that file into a small, simple two-column table. That worked perfectly fine.

At first we decided this must be a Redshift issue. The initial thinking was that Redshift was encrypting files during the extract, but that doesn't appear to be the case. While it does appear to be true that Redshift CAN encrypt data during the unload step, it does not appear to be the default behavior.

We also ruled out the bucket itself. The same files placed into different buckets also don't work. So, our current conclusion is that it's the ROLE that Redshift is using to extract the files, that's causing the issue.

We're still investigating. I'll post an update once we figure out a resolution.

    5 replies

    LenoyJ
    Forum|alt.badge.img+1
    • LenoyJ
    • Participating Frequently
    • November 8, 2018

    Is this an EON POC? Though if it were an EON POC you would've probably ran into access issues when setting up the communal storage in admintools - so I'm guessing it's not. Nonetheless, try checking the IAM roles for the Vertica instances if they're on AWS. Make sure it has sufficient access to the S3 bucket since even 'aws s3 cp' failed.

    Vertica_Curtis
    Forum|alt.badge.img+1
    • Vertica_Curtis
    • Author
    • Participating Frequently
    • November 8, 2018

    It is an Eon POC.

    An update. It seems the issue has to do with encryption on the unload from Redshift. The documentation for the Unload command has options to do encryption during the unload, but these are optional commands. It seems, however, that even without these options (which the client is not using), there is encryption by default.

    So, what appears to be happening is that the KMS key for encryption is not available to the bucket that's reading the data. So, when it lands there, the HeadObject (essentially the metadata of the file) is unreadable, and the encryption values are listed as "access denied". So, even through the s3 console itself, the files can't be read (obvious, but worth noting: this isn't a Vertica issue).

    The client filed a ticket with AWS to get a resolution on this, and the answer from AWS is that the accounts have to be "chained" (I'll try to get a link on how to set this up). This apparently enables the roles to share the encryption keys.

    Also, it should be noted that the files already unloaded to the s3 bucket that Vertica is set to read from are encrypted, and even though the encryption key was put into that bucket, it is not retroactive, so the files we've unloaded will need to be unloaded again.

    As an alternative, it should be possible (and maybe easier) to just build the Eon Cluster in the same VPC that Redshift resides in. This should cause it to use the same role and the same keys, and these other issues should sort of wash themselves away automatically in doing this.

    So, current status: We're going to see if we can set up the chaining, and if that proves to be too difficult, we'll rebuild the cluster in the other VPC.

    Vertica_Curtis
    Forum|alt.badge.img+1
    • Vertica_Curtis
    • Author
    • Participating Frequently
    • November 8, 2018

    Another update, for those following along at home... Apparently the AWS documentation for setting up account role chaining is wrong. Their infrastructure guy followed what the doc said, and he got a syntax error. So, they've given up, and they're just going to build a cluster in the same VPC where Redshift is. That's being set up now.

    Car1os
    Forum|alt.badge.img
    • Car1os
    • Participating Frequently
    • November 8, 2018
    Not sure if it's the VPC or the role that doesn't have the correct credentials, I do think there is something you can do at the VPC level, but I'm not 100% sure.

    For testing they should just create an instance with our AMI, and run aws from the cli, and see if that works. Assign the IAM role that you will be using.



    On 11/8/18, 3:45 PM, "Vertica_Curtis" wrote:

    Vertica Forum https://forum.vertica.com/
    New Comment on Redshift to Vertica migration issue in AskVertica

    Another update, for those following along at home... Apparently the AWS documentation for setting up account role chaining is wrong. Their infrastructure guy followed what the doc said, and he got a syntax error. So, they've given up, and they're just going to build a cluster in the same VPC where Redshift is. That's being set up now.

    Reply to this email directly or follow the link below to check it out: https://forum.vertica.com/discussion/comment/241653#Comment_241653
    Vertica_Curtis
    Forum|alt.badge.img+1
    • Vertica_Curtis
    • Author
    • Participating Frequently
    • November 9, 2018

    Ok, so after 2 days of jacking with this problem, we finally got it to work. It seems that Redshift unloads stuff encrypted. And, to complicate things, I think this client also encrypts their buckets, per policy. What makes this confusing is that, when you try to cp to or from an encrypted bucket, instead of getting a meaningful error message, you just get a flat "access denied". So, unlike other systems, I could encrypt a file, and you could copy said file - and in those cases, you just get a garbage file. In s3, it doesn't even allow you to get it at all, nor can you upload into an encrypted bucket without the appropriate authorization.

    This ends up having the effect of having permissions set to * and yet, getting things like "getObject denied" - access denied" errors, which of course cause a lot of head-scratching, because we were like "we have global permissions!"

    To solve this, you have to add the KMS policy to the bucket. Also, on the unload from Redshift, the key is defined as part of the unload syntax. But it seems that the bucket won't even allow you to interact with encrypted files if the policy isn't defined as part of the bucket's definition.

    Later I'll post the unload syntax from Redshift that we used, to have a record of that.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement