Skip to main content

How to give user access to external table of parquet data on S3

  • October 20, 2018
  • 6 replies
  • 25 views
Terry_Caudill

As dbadmin I have performed EXPORT TO PARQUET to place parquet data in S3. I then successfully created an external table so that I can query using:

CREATE EXTERNAL TABLE tracking(...) AS COPY FROM 's3://my_bucket/tracking//.parquet' PARQUET(hive_partition_cols='startDate');

dbadmin can query the external table, but any other vertica user gets the following error:
ERROR 4368: Permission denied for storage location [s3://my_bucket/tracking//.parquet]

How do I allow other vertica users query access to the parquet data stored in S3?

    6 replies

    Terry_Caudill
    • Terry_Caudill
    • Author
    • New Participant
    • October 20, 2018

    I am using Vertica 9.1.1-2

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • October 20, 2018

    Check out:

    https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/GRANT/GRANTStorageLocation.htm

    kboyd
    • kboyd
    • New Participant
    • October 23, 2018
    Hi Terry,

    Have you had a chance to check the GRANT function on the storage location, suggested on the forum, and if so, did it work?

    If not, could you please send me the exact command you are passing? Also, can you please submit a case for the issue and forward me the support case#?

    Thanks very much,
    Kristen

    Kristen Boyd
    Vertica Solutions Architect
    V   +1 972 369 6495 (office/mobile)
    //kristen.boyd@microfocus.com
    www.vertica.com
    Terry_Caudill
    • Terry_Caudill
    • Author
    • New Participant
    • October 23, 2018

    It appears that GrantStorageLocation requires a location. How do you create a location that is in S3?

    I attempted
    CREATE LOCATION 's3://my_bucket/tracking' ALL NODES USAGE 'USER';

    and received:

    ERROR: [Vertica]VJDBC ERROR: Must specify shared
    storage for built-in shared file system Error Code: 7868

    Query = CREATE LOCATION 's3://my_bucket/tracking' ALL NODES USAGE 'USER';

    kboyd
    • kboyd
    • New Participant
    • October 23, 2018
    Hi Terry,

    Looks like you're missing the SHARED syntax, like so:

    CREATE LOCATION 's3://datalake' SHARED USAGE 'USER' LABEL 's3user';

    Then grant READ access to the user or role on the location. Here are instructions from our documentation:

    https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/Eon/CreatingExternalTablesFromS3.htm?Highlight=query parquet data from s3

    Kristen Boyd
    Vertica Solutions Architect
    V   +1 972 369 6495 (office/mobile)
    //kristen.boyd@microfocus.com
    www.vertica.com
    Terry_Caudill
    • Terry_Caudill
    • Author
    • New Participant
    • October 24, 2018

    That did it! Wasn't looking in the docs for EON mode - I'll remember include that in my future documentation searches.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement