#####    ######    #####   ######     ###      ###    ####      #####   
 ## ##     ##     ##   ##    ##      ## ##    ## ##    ##      ##   ##  
 ##  ##    ##     ##         ##     ##   ##  ##   ##   ##      ##       
 ##  ##    ##      #####     ##     ##   ##  ##   ##   ##       #####   
 ##  ##    ##          ##    ##     ##   ##  ##   ##   ##           ##  
 ## ##     ##     ##   ##    ##      ## ##    ## ##    ##      ##   ##  
#####      ##      #####     ##       ###      ###    #######   #####   

Table of Contents

• Introduction
• How to use dtstools
• How to use tableSize
• How to use tableMaintenance
• How to use LastMaintenance
• Future implementations
• Notes
• References

Introduction

This package aims to provide functionality to work with Delta Lake.

Facilitating the visualization of the actual size (Storage) of your Delta tables and their maintenance (Vacuum and Optimize)

Below are the steps performed in order of execution:

1. Executed a describe detail to get the current location and size
2. A scan (dbutils.fs.ls) is performed on the Storage folders recursively to calculate the space used in the Storage, excluding the _delta_logs, with this we can calculate an average of how much can be released with Vacuum
3. Returns a Dataframet

How to use dtstools

First install the package via PyPi

pip install --upgrade dtstools

Import the dtsTable module into your context

from dtstools import dtsTable

Use the function dtsTable.Help() to see examples of function usage and a summary of each function.

dtsTable.Help()

How to use tableSize

Find out the true size of your table

Call the tableSize function passing the database and table name, use display() to see the results.

This function returns a Dataframe.

dtsTable.tableSize(databaseName,tableName).display()

Save the result in a Delta Table for monitoring and baseline

dtsTable.tableSize(databaseName,tableName) \
 .write.format('delta') \
 .mode('append') \
 .saveAsTable("db_demo.tableSize",path='abfss://container@storage.dfs.core.windows.net/bronze/tableSize')

Get the size of all tables in your database

for tb in spark.sql(f"show tables from db_demo").collect():
    try:
        print(">> Collecting data... Table:",tb.tableName)
        dtsTable.tableSize(tb.database,tb.tableName) \
        .write.format('delta') \
        .mode('append') \
        .saveAsTable("db_demo.tableSize",path='abfss://container@storage.dfs.core.windows.net/bronze/tableSize')
    except Exception as e:
        print (f"###### Error to load tableName {tb.tableName} - {e}######")

How to use tableMaintenance

Apply maintenance to a table, Optimize and Vacuum.

Apply in a single table.

dtsTable.tableMaintenance(schemaName="Database", tableName="tableName", zorderColumns='none', vacuumRetention=168, vacuum=True, optimize=True, debug=False)

Apply maintenance to all tables for YOUR database

for tb in spark.sql(f"show tables from db_demo").collect():
  dtsTable.tableMaintenance(schemaName=tb.database, tableName=tb.tableName, zorderColumns='none', vacuumRetention=168, vacuum=True, optimize=True, debug=False)

Last Maintenance

Use the function dtsTable.LastMaintenance() to see summary vacuum result and optimize operations.

dtsTable.lastMaintenance('database','tableName')

Future implementations

1. Use Unity Catalog
   
2. Run for all databases
   
3. Minimize costs with dbutils.fs.ls by looking directly into the transaction log
   

Notes

• For partitioned tables with many partitions, the execution time can take longer, so monitor the first executions well, the use is at your responsibility, despite not having any risk mapped so far, it can only generate more transactions for your storage
• Cost of Azure Storage Transactions: Read Operations (per 10,000) - $0.0258 (two cents per 10,000 operations) (Estimated price as of 4/21/2023)

References

https://github.com/reginaldosilva27/dtstools

Author: Reginaldo Silva

• Blog Data In Action
• Github
• Dataside