How to give user access to external table of parquet data on S3
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?
0
Comments
I am using Vertica 9.1.1-2
Check out:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/GRANT/GRANTStorageLocation.htm
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
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';
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
That did it! Wasn't looking in the docs for EON mode - I'll remember include that in my future documentation searches.