cancel
Showing results for 
Search instead for 
Did you mean: 
Machine Learning
Dive into the world of machine learning on the Databricks platform. Explore discussions on algorithms, model training, deployment, and more. Connect with ML enthusiasts and experts.
cancel
Showing results for 
Search instead for 
Did you mean: 

INFORMATION_SCHEMA IS NOT POPULATED WITH TABLE INFORMATION

Databricks3
Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

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​ 

View solution in original post

11 REPLIES 11

-werners-
Esteemed Contributor III

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>)

I am using Unity Catalog pointing to s3

-werners-
Esteemed Contributor III

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';

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

image.png

Databricks3
Contributor

Getting the below result.@Werner Stinckens​ image

-werners-
Esteemed Contributor III

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

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​ 

-werners-
Esteemed Contributor III

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

Anonymous
Not applicable

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! 

EwenGillies
New Contributor II

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.

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!