topic Re: how to find the size of a table in python or sql? in Data Engineering

how to find the size of a table in python or sql?

ramankr48 — Wed, 19 Oct 2022 11:01:39 GMT

let's suppose there is a database db, inside that so many tables are there and , i want to get the size of tables . how to get in either sql, python, pyspark.

even if i have to get one by one it's fine.

Re: how to find the size of a table in python or sql?

elgeo — Wed, 19 Oct 2022 13:01:07 GMT

DESCRIBE DETAIL table_name returns the sizeInBytes

Re: how to find the size of a table in python or sql?

youssefmrini — Wed, 19 Oct 2022 14:58:03 GMT

Describe detail give you only the size of the latest snapshot. It's worth running a dbutils.fs.ls

Re: how to find the size of a table in python or sql?

shan_chandra — Wed, 19 Oct 2022 16:04:05 GMT

@Raman Gupta - Please refer to the below

Calculate the size of the Delta table:

%scala 
import com.databricks.sql.transaction.tahoe._
val deltaLog = DeltaLog.forTable(spark, "dbfs:/delta-table-path")
val snapshot = deltaLog.snapshot                     // the current delta table snapshot
println(s"Total file size (bytes): ${deltaLog.snapshot.sizeInBytes}"

calculate the size of the non delta table:

%scala
spark.read.table("non-delta-table-name").queryExecution.analyzed.stats

Re: how to find the size of a table in python or sql?

ramankr48 — Wed, 19 Oct 2022 17:36:14 GMT

Already know in scala.

I wanted this in either python or sql

scala link: https://kb.databricks.com/sql/find-size-of-table.html#:~:text=To%20find%20the%20size%20of%20a%20delta%20table%2C,println%20%28s%20%22Total%20file%20size%20%28bytes%29%3A%20%24%20%7BdeltaLog.snapshot.sizeInBytes%7D%22%29

Re: how to find the size of a table in python or sql?

shan_chandra — Wed, 19 Oct 2022 17:54:01 GMT

@Raman Gupta - could you please try the below

%python
spark.sql("describe detail delta-table-name").select("sizeInBytes").collect()

Re: how to find the size of a table in python or sql?

ramankr48 — Thu, 20 Oct 2022 05:26:56 GMT

Thanks @Shanmugavel Chandrakasu