Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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

Comments

  • @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;

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

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.