I tried to unload Redshift permissions to S3 but the function HAS_TABLE_PRIVILEGE() uses leader-node which is incompatible with UNLOAD.
Is there an alternative ?
CodePudding user response:
As you are seeing leader-only data can't be treated like other data on Redshift. There is a workaround for this inside of Redshift but it is a bit clunky. You can push your select statement results into a cursor and read this information out of the cursor and into a data table which you can then manipulate like any other data. This is done in a stored procedure - example for moving a leader-only system table's data to a normal table How to join System tables or Information Schema tables with User defined tables in Redshift
Alternatively you can have the tool issuing the query just save the result to S3. This can be a Lambda function or just your bench with a AWS CLI command following. There are lots of different options when it comes to these "external" options.