topic Permission Error When Running DELETE FROM in Warehousing & Analytics

Permission Error When Running DELETE FROM

Carsten03 — Thu, 22 Feb 2024 09:53:33 GMT

Hi,

I want to remove duplicate rows from my managed delta table in my unity catalog. I use a query on a SQL warehouse similar to this:

WITH cte AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY id,##,##,## ORDER BY ts) AS row_num FROM catalog.schema.table ) DELETE FROM cte WHERE row_num > 1;

Using this I get following error:

Could not verify permissions for DeleteFromTable (row_num#734 > 1)

I checked the grants where I have ALL PRIVILEGES and the IAM Role (we are on AWS) for the external location and I have permissions to delete from S3. Is there anything else I have to set to be able to delete rows or is there anything wrong with my query? I couldn't find anything online.

Re: Permission Error When Running DELETE FROM

Carsten03 — Thu, 22 Feb 2024 11:15:35 GMT

I have first tried to use _metadata.row_index to delete the correct rows but also this resulted in an error. My solution was now to use spark and overwrite the table.

table_name = "catalog.schema.table" df = spark.read.table(table_name) count_df = df.count() dedup = df.drop_duplicates() count_dedup = dedup.count() count_df - count_dedup dedup.write.mode("overwrite").saveAsTable(table_name) assert spark.read.table(table_name).count() == count_dedup

I would still be interested if anyone has a good approach using SQL for this.

Re: Permission Error When Running DELETE FROM

ac0 — Fri, 29 Mar 2024 18:24:30 GMT

Pitching in here since i had the same problem: the issue you can't delete from a CTE; you need to delete from the actual table which can be joined to the CTE.