topic Get number of rows in delta lake table from metadata without count(*) in Administration & Architecture

Get number of rows in delta lake table from metadata without count(*)

JDL — Mon, 27 Nov 2023 20:42:47 GMT

Hello folks,

Is there a way with sql query to get count from delta table metadata without doing count(*) on each of table? Wondering, if this information is stored in any of INFORMATION_SCHEMA tables.

I have a use-case to get counts from 1000's of delta tables and do some further processing based on count.

It doesn't need to exact count but an estimate would be fine too.

Re: Get number of rows in delta lake table from metadata without count(*)

JDL — Tue, 28 Nov 2023 22:15:32 GMT

Thanks @Retired_mod for your response. I tried the sql query but gives error about the path.

I tried different version of path.
If I specify full path of table, which I retrieve from catalog explorer starting with abfss:// getting below error:

[RequestId=28a3048d-1371-4908-96cd-090961fe9356 ErrorClass=INVALID_PARAMETER_VALUE.LOCATION_OVERLAP] Input path url 'abfss://<>.dfs.core.windows.net/__unitystorage/catalogs/<>/tables/<>' overlaps with managed storage within 'GenerateTemporaryPathCredential' call

If I specify path without abfss:// getting error, Path must be absolute

Thoughts?

Re: Get number of rows in delta lake table from metadata without count(*)

SSundaram — Fri, 01 Dec 2023 00:06:09 GMT

Here is a related one.

https://community.databricks.com/t5/data-engineering/how-to-get-the-total-number-of-records-in-a-delta-table-from-the/td-p/20441

Re: Get number of rows in delta lake table from metadata without count(*)

JDL — Fri, 01 Dec 2023 13:51:46 GMT

Thanks @Retired_mod for detailed information but I am still not clear on resolution.

Table type is shown as MANAGED. Does this mean I can't get count of this table without count(*)?

2. I am using path starting with abfss://. Copying the exact path of this table from catalog explorer.

3. I am not familiar if dbutils.fs.ls can be used in SQL Query. I need this information using SQL only due to some limitation.

All IAM permissions are in place.

Re: Get number of rows in delta lake table from metadata without count(*)

JDL — Fri, 01 Dec 2023 13:53:16 GMT

Thanks @SSundaram for the link. I need this information via sql query only.