Hi thomas_berry,
How are you doing today?, As per my understanding, You're spot on with your understanding, and you're not alone in running into this limitation. Unity Catalog doesnโt currently support creating tables using a JDBC query like in your example, especially with complex joins, because UC doesn't allow JDBC-based file schemes for table creationโit expects you to use foreign catalogs through CREATE CONNECTION and CREATE FOREIGN CATALOG. And you're rightโthose only expose existing tables or views in the source, and joins in custom queries arenโt supported unless pre-defined as views on the source side. Unfortunately, if youโre not allowed to create views in the source system, that limits your options. For now, the best workaround is to pull the tables individually through the foreign catalog into Databricks and then write the join logic within Databricks (even though it wonโt be pushed down). Itโs not ideal for performance, but it's the safest option when view creation is off the table. Hopefully in the future, Unity Catalog might support more flexible query definitions for federated sourcesโbut for now, we're a bit limited there. Let me know if you want help building a workaround using Databricks-side logic!
Regards,
Brahma