Skip to main content

Reading GZIP files from S3 into Vertica

  • February 24, 2017
  • 9 replies
  • 13 views
Forum|alt.badge.img+2

Vertica supports reading GZIP files directly from S3. Here are the commands to make that happen:

    $ cd /opt/vertica/sdk/examples/
    $ make
    $ vsql -f FilterFunctions.sql (Optional) /*example provided by Vertica */
    dbadmin=> CREATE LIBRARY GZipLib AS '/opt/vertica/sdk/examples/build/GZipLib.so';
    dbadmin=> CREATE FILTER GZip AS LANGUAGE 'C++' NAME 'GZipUnpackerFactory' LIBRARY GZipLib;
    dbadmin=> COPY DATA SOURCE S3(bucket='s3://external-data/201007*', delimiter='|') FILTER gzip() SKIP 1;

When you install Vertica, the sample file FilterFunctions.sql (available in GitHub)is stored in the /opt/vertica/sdk/examples/ directory. This file provides a step-by-step guide of how to use the User-Defined Filter Functions with sample data. Optionally, you can use this file to test if the UDFs are working with the filters installed as part of the C++ SDK.

    9 replies

    nitinkr55
    • nitinkr55
    • New Participant
    • April 12, 2017

    Could please elaborate about it

    Shayon_Sanyal
    Forum|alt.badge.img
    • Shayon_Sanyal
    • Participating Frequently
    • May 13, 2017

    Hi Nitin - The commands above demonstrate how to load gzipped files (gzipped through the S3 loader) directly into Vertica. In /opt/vertica/sdk/examples/FilterFunctions, we provide the BZIP and GZIP filters, which once installed gives you the capability to leverage this feature.

    Hope this helps!

    TrovadorSF
    • TrovadorSF
    • New Participant
    • January 12, 2018

    This is very useful. I was able to successfully load 1 gzipped csv file from a folder in a bucket on s3. However, when trying to load multiple files it seems to only pick-up one of them. Is there something needed to accept multiple gzipped files?

    TrovadorSF
    • TrovadorSF
    • New Participant
    • January 12, 2018

    Also sometimes I get this error:
    [VP001][5861] [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. java.lang.RuntimeException: com.vertica.support.exceptions.NonTransientException: [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.

    Can't seem to figure what causes this as it'll happen randomly for the same query.

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • January 17, 2018

    @TrovadorSF - Can you post the SQL you used to try to load more than one file?

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • April 26, 2019

    S3 UDSource was deprecated in Vertica 9.1 and removed in 9.1.1 Mainly 'cause it sucked.

    See:
    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/AWSLibrary/importAWSlibrary.htm

    sarojrout
    • sarojrout
    • New Participant
    • May 1, 2019

    Hey @Jim_Knicely , thats not working even. i tried

    DilipKumar
    Forum|alt.badge.img+1
    • DilipKumar
    • Participating Frequently
    • May 2, 2019

    @sarojrout ,

    You have 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’;

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

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

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • May 8, 2019

    @sarojrout - Were you able to follow @DilipKumar recommendations?

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement