S3 access issue: "Check your key and signing method."

nchammas-mm

I'm having trouble loading some data into Vertica from S3. The error I'm getting is:

SQL Error [5861] [VP001]: [Vertica][VJDBC](5861) 
    ERROR: Error calling plan() in User Function S3SourceFactory at [src/S3.cpp:265], error code: 0, message:
    The request signature we calculated does not match the signature you provided. Check your key and signing method.

I'm using AWS_SET_CONFIG() to set AWS credentials in my session, and I am able to successfully load some data from S3 into Vertica. However, a specific set of data on S3 doesn't work, yielding the above error.

The statement I'm using to copy the data looks like this:

COPY schema_name.table_name
SOURCE s3(bucket='s3://bucket-name/path/part1.csv')
RECORD TERMINATOR '\r\r\n'
DELIMITER ','
ABORT ON ERROR;

I'm running Vertica v8.1.1-0.

Any clues on why I might be getting the above error?

Jim_Knicely

Hi,

You might be hitting a bug in version 8.1.1-0?

Fixed in 8.1.1-1: When loading files from S3, reusing the authentication manager object from a previous request sometimes caused an invalid request signature, and the load failed with an error.

nchammas-mm

Oh, fascinating. I should have checked the release notes.

I'll look into upgrading and report back. Thanks for the quick response.

nchammas-mm

We upgraded the database to 8.1.1-9, but I am still seeing the same error when trying to COPY this particular dataset in from S3.

I looked at the release notes for Vertica 9 to see if there's anything else we may be missing. Do VER-55364 (from Vertica 9) and VER-55502 (from Vertica 8.1.1) refer to the same issue? What about VER-55726, could that be related?

Is there anything I can do to further narrow down the source of the issue, short of upgrading to Vertica 9?

nchammas-mm

Hmm, just a shot in the dark since I wasn't the person who did the upgrade:

Is it possible that the database version got upgraded but the AWS Library somehow didn't? How would I check that?

Jim_Knicely

Check the /opt/vertica/packages/AWS/lib/ directory. Make sure the aws.so file is your version and also check the file timestamps.

[dbadmin@vertica01 lib]$ ls -rlt /opt/vertica/packages/AWS/lib/
total 11084
-rw-r--r-- 1 root root 11347936 Oct 16 11:25 aws.so.9.0.0
lrwxrwxrwx 1 root root       12 Nov  3 09:54 aws.so -> aws.so.9.0.0

If the above is ok, then ... What do you mean by "...a specific set of data on S3 doesn't work..."?

Is it a different bucket? Or the same bucket, different path, or the same bucket, same path, different file?

nchammas-mm

I'll check the version of aws.so and report back.

What do you mean by "...a specific set of data on S3 doesn't work..."?

I am able to load one dataset from S3 fine but not another. Both datasets are in the same bucket but under different paths.

nchammas-mm

So this is what that directory looks like:

$ ll /opt/vertica/packages/AWS/lib/
total 6140
lrwxrwxrwx. 1 root root      12 Dec  4 15:53 aws.so -> aws.so.8.1.1
-rw-r--r--. 1 root root 6284208 Nov  9 18:27 aws.so.8.1.1

I reinstalled the AWS package following the instructions here:

$ admintools -t install_package -d advana -P AWS --force-reinstall
Installing package AWS...
...Success!

However, the directory looks the same. And the filenames don't show the patch version (-0, -1, etc.), so I don't know if it did anything.

But if I retry my COPY I now get a different error (after a bit of a wait):

SQL Error [5861] [VP001]: [Vertica][VJDBC](5861) ERROR: Error calling process() in User Function UDSource at [src/S3.cpp:137], error code: 0, message: Client Error: Cannot allocate more than memory limit: (134224764 / 134217728)!

I remember seeing this error once before, but I forget under what conditions exactly. 134217728 bytes is 128 MiB. The file I'm trying to load from S3 is 5.7 GB.

I guess this means I've moved past the S3 bug and now I need to fiddle with some settings to load my file from S3. Is that correct? Why would Vertica have such a low limit for COPYing data in? (Perhaps it's something we set incorrectly, as opposed to a default.)

Jim_Knicely

Is it possible to stick a small file in that S3 bucket / path to test to see if you can load it? That way you'll know that at least the one issue is fixed.

Jim_Knicely

What client are you using? Looks like you a re getting JDBC error.

Since the file is so large, make sure you are using the DIRECT load method:

COPY schema_name.table_name
SOURCE s3(bucket='s3://bucket-name/path/part1.csv')
RECORD TERMINATOR '\r\r\n'
DELIMITER ','
ABORT ON ERROR
DIRECT;

nchammas-mm

Good tip. Looks like we did indeed fix the S3 issue.

The memory issue was happening because I was using an incorrect record terminator in my COPY, so I guess Vertica kept searching for the end of the record until it hit its 128 MiB limit, which is totally reasonable!

Now that I've fixed the record terminator, my COPY succeeds on the original file. :thumbsup:

Thanks again for your help, Jim.

Jim_Knicely

Awesome!

nchammas-mm

By the way, the error with the record terminator was that I wasn't using extended string literals, which are required to get things like \r to work as expected.

So instead of RECORD TERMINATOR '\r\r\n' I needed RECORD TERMINATOR E'\r\r\n'.