Forum Discussion
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 using "Overwrite," I had it set to "Overwrite Schema," which did not create backup tables. However, this setting introduced datatype issues on the table, so I switched to "Overwrite."
Do you have any advice on how to prevent these backup tables from being created, or should I create a process to delete them?
Best regards,
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}")
3 Replies
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.
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}")I know this post is over a year old, but is this really an acceptable solution? Seems more like "Defect support system" (meaning we have to add extra coding and stuff to 'fix' stuff that Microsoft won't address.
