Forum Discussion

Soobramoney's avatar
Soobramoney
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Lakehouse backup table

Hi,   I have a data pipeline that loads data into a staging table with the destination setting set to "Overwrite." This seems to create a _backup_XXXXXXXXXX table each time I load data. Prior to us...
Pipeline
  • Soobramoney's avatar
    Soobramoney
    1 year ago

    It seems you cannot prevent the backup tables when you are using the Overwrite setting. I had to create a clean job that runs once a day.

     

    import logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("BackupTableCleanup")

backup_tables = spark.sql("SHOW TABLES")


drop_backup_tables = backup_tables.filter(
(backup_tables["tableName"].like('%_backup_%')) &
)


drop_statements = drop_backup_tables.rdd.map(lambda row: f"DROP TABLE {row['tableName']};").collect()

for statement in drop_statements:
try:
logger.info(f"Executing: {statement}")
print(f"Executing: {statement}")
spark.sql(statement)

logger.info(f"Successfully executed: {statement}")
except Exception as e:
logger.error(f"Failed to execute: {statement}. Error: {e}")

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Soobramoney 

 

According to the official documentation, you can copy previous Version tables from Lakehouse by specifying version in the copy activity source. Then, I suggest you consider custom code in the pipeline to remove these tables.

 

 

https://learn.microsoft.com/en-us/fabric/data-factory/connector-lakehouse-copy-activity#destination

 

Specific steps you can consider:

 

First, get a list of all the tables that need to be deleted.

 

Write a script to delete these tables. You can write code in a language you are familiar with, here is an example using Python:

 

 

import pyodbc

# Connect to database
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_database;UID=your_username;PWD=your_password')
cursor = conn.cursor()

# Gets a list of tables to delete
tables_to_delete = ["backup_table1", "backup_table2", "backup_table3"]

# Delete list
for table in tables_to_delete:
    cursor.execute(f"DROP TABLE IF EXISTS {table}")
    print(f"Deleted table: {table}")

conn.commit()
cursor.close()
conn.close()

 

 

Integrate this script into your data pipeline. You can run this script after the data is loaded to ensure that the backup table is deleted.

 

I hope you found this idea helpful.

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.