topic Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION in Machine Learning

INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Databricks3 — Wed, 07 Jun 2023 05:31:10 GMT

I have created a metastore and within that metastore i have created multiple schemas and tables underlying it but none of table details is visible from information schema. All the tables are empty.

Could you please let me know if I am missing here. Below query is returning no data.

%sql
select * from <catalog_name>.information_schema.tables

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

-werners- — Wed, 07 Jun 2023 07:01:11 GMT

I think that it only applies to Unity.

If you want to list the tables of a schema; you can use SHOW TABLES (after USE <schema>)

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Databricks3 — Wed, 07 Jun 2023 07:04:20 GMT

I am using Unity Catalog pointing to s3

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

-werners- — Wed, 07 Jun 2023 07:12:31 GMT

Could be a permission issue or a wrong query,

can you try this query:

> SELECT table_owner

FROM information_schema.tables

WHERE table_schema = 'information_schema'

AND table_name = 'columns';

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Databricks3 — Wed, 07 Jun 2023 07:17:30 GMT

Getting the below error message. Even if I use <catalog_name>.information_schema.tables it is not returning anything.

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Databricks3 — Wed, 07 Jun 2023 07:41:39 GMT

Getting the below result.@Werner Stinckens

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

-werners- — Wed, 07 Jun 2023 07:45:34 GMT

ok so it is not a permission issue.

Meaning your query needs change.

What exactly do you want to see?

Here you can find the ERD

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Databricks3 — Wed, 07 Jun 2023 07:53:39 GMT

I can access all the tables if I use system.information_schema instead of catalog_name.information_schema. But my point is why it is working in this way. When I am creating a catalog, a information_schema is created inside it by default.

And it is having no information of the same catalog.

Anyway now I can access it using system. Thanks a lot 🙂 @Werner Stinckens

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

-werners- — Wed, 07 Jun 2023 07:58:25 GMT

no idea, I guess information_schema, being generated, follows some special logic.

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Anonymous — Thu, 08 Jun 2023 08:26:48 GMT

Hi @SK ASIF ALI

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

EwenGillies — Thu, 24 Aug 2023 11:54:43 GMT

I noticed this issue is currently caused when you rename a catalog. The contents of <catalog>.information_schema are all views like this:

SELECT
  *
FROM
  system.information_schema.columns
WHERE
  table_catalog = '<catalog>'

If you rename the catalog, the view definitions don't get updated. For example, if you made a catalog named `my_awesome_catalog`, then the view in `my_awesome_catalog.information_schema` will be:

SELECT
  *
FROM
  system.information_schema.columns
WHERE
  table_catalog = 'my_awesome_catalog'

If you then renamed `my_awesome_catalog` to `my_new_named_catalog`, the view inside `my_new_named_catalog.information_schema` will remain unchanged, and will still be:

SELECT
  *
FROM
  system.information_schema.columns
WHERE
  table_catalog = 'my_awesome_catalog'

Assuming you no longer have a catalog named `my_awesome_catalog`, this will return zero rows. Even worse, if you make a new catalog named `my_awesome_catalog`, the `my_new_named_catalog.information_schema` views will return the wrong information completely.

Not great overall, definitively an easily identifiable bug in Databricks.

Re: INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

John_Gooding — Fri, 20 Oct 2023 12:32:16 GMT

I can confirm this as a bug, either remove the ability to rename a catalog from the UI, or fix it or provide a method to rebuild the information_Schema views. Took me a while to track this down thinking it was a permissions issue, but turned out the creator of the catalog renamed it right after they realized their mistake instead of re-creating the catalog.

Now we have 2 days of work to do to rebuild the catalog into a new catalog since a user can't modify the infomration_schema views to resolve.

Thanks