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

sarojroutsarojrout Registered User

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

  • DilipKumarDilipKumar Registered User

    @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