Trino demotion issue when moving between catalogs

[MikeWallis42]

When promoting a model that has changed catalogs in Trino (in this example Hive -> Iceberg) we end up with an unsupported format error.

I think what's happening is that the new view is created in the new catalog using CREATE OR REPLACE VIEW during the promotion step.
When it comes to demoting the old view in the old catalog this is then attempted using DROP VIEW IF EXISTS using the old catalog.

By this point the view has already been modified to an Iceberg view and so Trino can't drop it when using the Hive catalog as they're different lower level APIs.

I think the root cause of the issue is that by changing the catalog which forms part of the name of the model, this then means that sqlmesh wants to create a new model and remove an old model.
If only the catalog has changed, in the case of Trino, we should only create the new model (promote) and not remove the old model (demote).

I suppose this only matters when both are using the same catalog (in this case Glue/HMS), if they were physically different databases then this wouldn't be an issue.
For the moment we're manually dropping views to get around the issue but when we move to production that becomes a bigger issue due to access constraints.

[izeigerman]

Thanks for reporting @MikeWallis42!

By this point the view has already been modified to an Iceberg view and so Trino can't drop it when using the Hive catalog as they're different lower level APIs.

How was the view modified at this point? By who?

[erindru]

I was unable to reproduce this.

Assuming:

• a catalog called hive that uses the Trino Hive connector
• a catalog called iceberg that uses the Trino Iceberg connector
• SQLMesh's Trino connection configured with catalog: iceberg (making iceberg the default catalog)

I created the following model:

MODEL (
  name hive.lake.test_model,
  kind FULL
);

SELECT 1;

Ran sqlmesh plan and it created it.

I then renamed it to lake.test_model and ran sqlmesh plan:

Models:
├── Added:
│   └── lake.test_model
└── Removed:
    └── hive.lake.test_model
Models needing backfill [missing dates]:
└── lake.test_model: [full refresh]

It was able to drop the table from hive catalog and add it to the iceberg catalog (which is configured as default) and update the corresponding views.

I tried this with kind VIEW as well with similar results.

Now, I suspect your model was a bit more complicated than this so are you able to share the MODEL (..) block definition of the model you encountered this with and steps to reproduce the issue?

Also, echoing what @izeigerman mentioned - how was the view modified? Was it modified by SQLMesh or something else?

[MikeWallis42]

Thank you for getting back to me!
The view modification that I mentioned was done by sqlmesh as part of the promotion step.

I'll spend a bit of time this morning trying to come up with a MRE.

[erindru]

@MikeWallis42 did you manage to figure out a MRE for this issue?