Hello Team, We are trying to create normal view or Materialized view using oracle or elasticsearch connector in trino, It says This connector does not support creating views
Let us know way to create view or materialized view using oracle or elasticsearch connector
When you create a materialized view for a federated query such as one that leverages the oracle and elasticsearch connectors, you need to create the materialized view with a catalog and a connector that supports (and stores) the MV.
For Starburst Enterprise, that usually means using the Hive Connector. Check out Starburst Hive connector — Starburst Enterprise that tells you that you need the caching service configured and when you create a MV (that sources data from ANY connector). This connector (i.e. the Hive one) will store the “materialized” content wherever this connector is configured to persist data.
For Starburst Galaxy, the Great Lakes connectivity described at Starburst | Great Lakes connectivity allows for MVs to be created as well.
Neither connector can easily support views.
The Oracle connector would have to support Oracle views. And in fact it does support those. Any view you define in Oracle shows up as a table in Trino. The reason is that the view definition is written in Oracle SQL … and therefore has to be evaluated and processed by Oracle. If the Trino connector could create a view and have it stored in the Oracle information schema it would be defined using Trino SQL and therefore be impossible to evaluate by Oracle. Only Trino could use them.
The same situation exists in Elasticsearch … with the difference that there is no native SQL dialect or view.
In both cases … what you have to do is use a catalog in Trino that uses an object storage connector with a metastore. These are Hive, Hudi, Delta Lake or Iceberg. With these you can define a view that is stored in the metastore of those catalogs … but the query actually accesses a different catalog. You just have to educate your users that views are found in a different catalog.