not able to copy the .gz files from s3 to vertca

sarojrout

Hi ,

I am getting the error inconsistently. Sometimes, am able to copy from s3 to vertica using the same comand and sometimes not able to .

Below is the error. If anybody has any suggestion, could you please share how to fix this?

ERROR 5861: Error calling plan() in User Function S3SourceFactory at [src/S3.cpp:383], error code: 0, message: Access Denied

DilipKumar

@sarojrout

You have to check few things when you use the new Copy from s3 syntax:

1. Set AWSAuth parameter as below:
   ALTER SESSION SET AWSAuth=‘AWS_ACCESS_KEY_ID:AWS_SECRET_ACCESS_KEY’;

2. Make sure you have the right region set as below: (change the region name accordingly)
   ALTER SESSION SET UDPARAMETER FOR awslib aws_region='us-west-2';

3. Make sure your bucket name is defined in the vertica storage location:
   select * from storage_locations where location_path ilike '%your_bucket_name%';

--If not defined then create and grant to the vertica user from where you run the copy
CREATE LOCATION 's3://xxxx...' SHARED USAGE 'USER' LABEL 's3user';

--Grant the location to the vertica user:
GRANT READ on LOCATION 's3://xxx...' to sys_vertica_xsbg;

4. Make sure you are able to list the files under the bucket from the OS command prompt as shown below:
   [vertica-2a-datanode1 ~]$ aws s3 ls s3://xxx...
   You should be able to see the files under your s3 bucket

5. Now you can try the copy statement.
   COPY SCHEMA_NAME.TABLE_NAME FROM 's3://xxx.../file_name.gz' FILTER GZip() DELIMITER E'\t' NULL AS '\N' REJECTMAX 100 skip 1 DIRECT COMMIT;

Hope this helps!!!

Thanks
Dilip Rachamalla