Forum Discussion

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

How do you delete specific rows from a Lakehouse table?

Greetings, all. I have some files loaded into a Lakehouse that I've also loaded into a Lakehouse table. I want to delete some of the rows in the Lakehouse table, but I can't seem to find a way to do ...
Need Help
pipelines
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi arpost ,
    Thanks for using Fabric Community.

    It is possible to delete rows in a Lakehouse table using Spark-SQL in notebooks. I have created a repro of the scenario. I have attached the screenshots for your reference.
    Trying to delete the row which has value of 46134



    Query succeeded


    After deletion of the row:


    You can refer this link for more information: Link1 
    Hope this helps. Do let us know if you have any further issues. Glad to help.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi arpost ,
Thanks for using Fabric Community.

It is possible to delete rows in a Lakehouse table using Spark-SQL in notebooks. I have created a repro of the scenario. I have attached the screenshots for your reference.
Trying to delete the row which has value of 46134



Query succeeded


After deletion of the row:


You can refer this link for more information: Link1 
Hope this helps. Do let us know if you have any further issues. Glad to help.

BaBallero's avatar
BaBallero
Icon for Helper I rankHelper I
1 year ago

I went exactly the same way but it doesn´t work for me. Are there any bugs at the moment?
Everything is fine without the where clause (deleting all the data) but not with a where clause, regardless if it´s a clause to text or date column.

Any suggestions?

  • TimoRiikonen's avatar
    TimoRiikonen
    Icon for Advocate V rankAdvocate V
    1 year ago

    These discussions should be still valid for Lakehouses (and probably Warehouses) without schema.

    I heard that Lakehouse with schema has more deletion issues.

    Is this the case?

    • BaBallero's avatar
      BaBallero
      Icon for Helper I rankHelper I
      1 year ago

      No, I´m using a Lakehouse without schema.

      • YOUNG99's avatar
        YOUNG99
        Frequent Visitor
        1 year ago

        How are trying to delete have you tried the pyspark method above?

  • BaBallero's avatar
    BaBallero
    Icon for Helper I rankHelper I
    1 year ago

    It seems to be something with timeout - when I delete just 150 rows, everything works fine!

    DELETE FROM `Table`
    WHERE attribute = 'ABC';

    But when I change the attribute die 'BCA', which is many thousands of rows in the table, it crashes with the following message:
    INCONSISTENT_BEHAVIOR_CROSS_VERSION.READ_ANCIENT_DATETIME
    I don´t see any connection to datetime in this? 


    Happy for any idea...