Search code examples
azure-databricksdatabricks-sql

Able to View Table in Databricks with whe DESCRIBE function/method but unable view the Data with REFRESH

When I execute the Databricks method 'DESCRIBE' on table I'm unable to view the table. However, executing the REFRESH method produces the folloiwng error:

REFRESH TABLE retail.managers

Error in SQL statement: SecurityException: User cannot SELECT on table `retail`.`managers` because:
--The owner of table `retail`.`codeproduct` is different from the owner of table `retail`.`managers`

DESCRIBE retail.managers

enter image description here

Any thoughts on why I'm getting the error? I should mention the tables aren't listed in our Catalog Explorer. Which is why I'm surprised the DESCRIBE retail.managers produces the table in the first place.

Solution

  • The error message indicates a security exception, That the user cannot SELECT on the retail.managers table because the owner of the retail.codeproduct table is different from the owner of the retail.managers table.

    • This error occours when the user lacks ownership and proper permissions for the involved tables.

    As you mentioned you cannot see the tables listed in catalog explorer. If you are using the databricks manged tables By default, managed tables are stored in /user/hive/warehouse in Hive warehouse. You can grant permission like below

    Manged Tables: A managed table in Spark SQL is a table where Spark takes care of both the data and metadata. Now, a global managed table is a special type of table that is accessible across all clusters. When you delete or drop this table, both the actual data and the metadata are removed entirely.

    For example I have used Hive_metastore catalog in databricks. I have enabled the spark.databricks.acl.sqlOnly true In the cluster configuration like below: enter image description here Using the above command it allows to grant permission on the table enter image description here

    You can select the user and grant the permissions for default hive_metastore manged tables

    enter image description here

    As per databricks if you want your table to appear in you default catalog,you can create a Default catalog for the workspace:

    enter image description here

    Note: This setting only applies to Unity Catalog compatible compute i.e. when the workspace has an assigned Unity Catalog metastore, and the cluster is in access mode 'Shared' or 'Single User', or in SQL warehouses.