topic Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables in Get Started Discussions

column "storage_sub_directory" is now always NULL in system.information_schema.tables

GeKo — Wed, 24 Apr 2024 11:53:42 GMT

Hello,

I am running a job that depends on the information provided in column storage_sub_directory in system.information_schema.tables .... and it worked until 1-2 weeks ago.

Now I discovered in the doc that this column is deprecated and always null , was there an announcement about that change (and if yes, where) ?

Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables

JessicaS — Mon, 29 Apr 2024 14:31:39 GMT

Same issue here - we were using this to join to our S3 inventory files to determine the top tables to optimize and vacuum. Started failing about 2 weeks ago

Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables

NandiniN — Wed, 01 May 2024 06:58:24 GMT

Hello,

Linking the documentation - https://docs.databricks.com/en/sql/language-manual/information-schema/tables.html#definition

STORAGE_SUB_DIRECTORY

STRING

Yes

Deprecated. Always NULL.

Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables

GeKo — Wed, 01 May 2024 11:24:31 GMT

Hello @NandiniN ,

many thanks for your reply.

The official doc I already know and I am aware that *suddenly* the storage_sub_directory is always NULL.

But now I need a solution from where I can get this information ? is the storage_sub_directory in another system.information_schema table/view ... or from where to extract this information now ??

Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables

GeKo — Fri, 10 May 2024 09:37:10 GMT

any news on that @NandiniN ?

where has this info gone...?!?!

Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables

NandiniN — Tue, 14 May 2024 03:51:12 GMT

Hi @GeKo ,

You can get it from the UC's List tables (GET /api/2.1/unity-catalog/tables) API for now. That API response's payload has the following field:

"storage_location": "string"

In future, this should be supported by Databricks as storage path column (currently there was some issue because of which it was deprecated), I do not have an ETA on that, but in future you can issue a support ticket to get the status of SC-107840 or monitor the release documentation.

Re: column "storage_sub_directory" is now always NULL in system.information_schema.tables

GeKo — Tue, 14 May 2024 09:36:37 GMT

Many thanks for the update @NandiniN