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

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?

Terry_Caudill

I am using Vertica 9.1.1-2

Jim_Knicely

Check out:

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

kboyd

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)
//[email protected]
www.vertica.com

Terry_Caudill

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

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)
//[email protected]
www.vertica.com

Terry_Caudill

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