topic Re: Delete row from table is not working. in Data Engineering

Delete row from table is not working.

kjoth — Wed, 13 Oct 2021 06:54:01 GMT

I have created External table using spark via below command. (Using Data science & Engineering)

df.write.mode("overwrite").format("parquet").saveAsTable(name=f'{db_name}.{table_name}', path="dbfs:/reports/testing")

I have tried to delete a row based on filter condition using SQL endpoint (Using SQL )

DELETE FROM
 
  testing.mobile_number_table
 
WHERE
 
  x1 == 9940062964

Getting below error message.

Spark 3.0 Plans are not fully supported on table acl or credential passthrough clusters: DeleteFromTable (x1#6341L = 9940062964)

Re: Delete row from table is not working.

Hubert-Dudek — Wed, 13 Oct 2021 09:38:48 GMT

try using :

.format("delta")

if not help I would check dbfs mount

Re: Delete row from table is not working.

kjoth — Wed, 13 Oct 2021 12:55:34 GMT

Thank you.

Re: Delete row from table is not working.

Hubert-Dudek — Wed, 13 Oct 2021 17:41:35 GMT

if helped you can choose my answer as best one 🙂

Re: Delete row from table is not working.

jose_gonzalez — Wed, 13 Oct 2021 17:46:30 GMT

hi @karthick J ,

It seems like the error is coming from your table permissions. Are you using a high concurrency cluster? if you do, then check if have table ACLs enable. Also try to test it using a standard cluster.

Re: Delete row from table is not working.

kjoth — Mon, 18 Oct 2021 06:28:15 GMT

Hi @Jose Gonzalez , Yes, Table access control has been enabled. Is this what you are referring too.

Re: Delete row from table is not working.

kjoth — Mon, 18 Oct 2021 07:05:26 GMT

Hi @Hubert Dudek , Is this(delta) is the only way for updating & deleting records.

Re: Delete row from table is not working.

Hubert-Dudek — Mon, 18 Oct 2021 10:34:35 GMT

using delta file format is only ("real") way to delte smth from file as it is transaction file (so it make commit that record is deleted kind of sql/git)

In other data files it will require to overwrite everything every time.

Re: Delete row from table is not working.

jose_gonzalez — Mon, 18 Oct 2021 21:48:45 GMT

hi @karthick J ,

Can you try to delete the row and execute your command in a non high concurrency cluster? the reason why im asking this is because we first need to isolate the error message and undertand why is happening to be able to find the best solution. Is this issue still blocking you or you are able to mitigate/solve this error?

Re: Delete row from table is not working.

kjoth — Wed, 20 Oct 2021 07:01:26 GMT

HI @Jose Gonzalez ,

Have created a table via spark using non high concurrency cluster - not writing as delta format. And created the table via spark. Where I have tried to delete the row but getting the error 1. Via Spark Notebook SQL & 2. SQL query

df.write.mode("overwrite").format("parquet").saveAsTable(name=f'{db_name}.{table_name}', path="dbfs:/reports/testing")