Facing access denied on querying struct columns

Question

I am able to query my table using Redshift spectrum. However, when I try to access a column, defined as a struct, I am getting the following error:

ERROR: Spectrum Scan Error: S3ServiceException:Access Denied,Status 403,Error AccessDenied

Any idea why is this happening?

Answer 1

I think I'm suffering this pain too. What happens if you go

set json_serialization_enable to true;

And then select the struct field?

And try like

select json_extract_path_text(structfield, 'key') from external_schema.table;

I feel that the S3 access error is bogus and instead there is an issue with the Glue table definition that's not quite right.

Answer 2

Like you mentioned you are using the amazon redshift spectrum. when you are getting access denies it is probably an issue of permissions, please check your role and its permissions which it attached to the redshift cluster.

you mentioned your column which is defined as struct did you create your external table like this https://stackoverflow.com/a/66705424/13126651 here my external table was a single object with single key entries and value was array of objects.

example

CREATE EXTERNAL TABLE jatinspectrum.extab (
enteries array<struct<title:varchar(4000),link:varchar(4000),author:varchar(4000),published_date:timestamp,category:array<varchar(4000)>>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile
LOCATION 's3://xxxxxxxxxxxxxx/xxxxxxxxxxxxxx/xxxxxxxxxxx/';

Refers docs for roles and external table