FileReadException error when creating materialized view reading two schemas

[kalebkemp]

Hi all. I'm getting an error `com.databricks.sql.io.FileReadException` when attempting to create a materialized view which reads tables from two different schemas in the same catalog. Is this just a limitation in databricks or do I potentially have some configuration issue? If I take out any reference to the other schema I'm able to create the view just fine. I can provide a stack trace or more information if necessary.

e.g.

this works:
create materialized view my_catalog.my_schema.kk_test as select * from my_catalog.my_schema.some_table 

this does not work:
create materialized view my_catalog.my_schema.kk_test1 as select * from my_catalog.my_other_schema.some_table

(this is a very simplified version, I do actually need data from both schemas) 

[VZLA]

@kalebkemp 

Can you please check if this is not an access issue?:

SHOW GRANTS ON SCHEMA my_catalog.my_other_schema;

Also test if you can successfully run a query that access data from both schemas:

SELECT *
FROM my_catalog.my_schema.some_table
JOIN my_catalog.my_other_schema.some_table
ON <join_condition>;

If this query fails, it confirms that the issue is with access or configuration rather than the materialized view creation itself.

Can you also please share the complete com.databricks.sql.io.FileReadException stacktrace?

[agallard]

Hi @kalebkemp ,

The error you're encountering (com.databricks.sql.io.FileReadException) when creating a materialized view that reads from two different schemas in the same catalog might not necessarily be a Databricks limitation. It is more likely related to permissions, configuration issues, or unsupported behavior in the way the materialized view accesses the data. Let’s walk through potential reasons and solutions.

• Check permissions: Ensure SELECT and USAGE privileges are granted for all referenced schemas and tables.
• Verify storage locations: Ensure both tables are in compatible storage systems and paths.
• Create intermediate views or tables: Consolidate data from both schemas into a single source before creating the materialized view.
• Check for broken file references: Verify and repair any issues with the underlying Delta tables.
• Consider using standard views: If materialized views are too restrictive, use standard views for flexibility.

By following these steps, you should be able to resolve the FileReadException and create the materialized view successfully. Let me know if you need further clarification or assistance!

Regards!