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

JDL
New Contributor III

‎11-27-2023 12:42 PM

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.

0 Kudos

JDL
New Contributor III

‎11-28-2023 02:15 PM

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?

0 Kudos

JDL
New Contributor III
In response to Retired_mod

‎12-01-2023 05:51 AM

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

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

JDL_0-1701438470267.png

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.

JDL
New Contributor III
In response to SSundaram

‎12-01-2023 05:53 AM

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