Search code examples
mysqlmongodbviewpresto

presto create view on CLI gives connector does not support create view error

  1. presto server started with a mongodb and myql catalogs.
  2. from presto cli, I can query the tables of both sources.
  3. I can even issue a query that combines data from both sources.
  4. In Mongo it is mongodb.test.actors which has name,city,dob fields.
  5. In MySql it is mysql.imdb_full.actors which has name, sex fields.

The problem is when I try to issue a create view directly from presto cli without running an 'use .schema', as follows , I get an exception Schema must be specified when session schema is not set as follows

presto> create view xyz_view as select name,city,dob from mongodb.test.actors;
Query 20210303_063035_00046_ecshq failed: line 1:1: Schema must be specified when session schema is not set
com.facebook.presto.sql.analyzer.SemanticException: line 1:1: Schema must be specified when session schema is not set
        at com.facebook.presto.metadata.MetadataUtil.lambda$createQualifiedObjectName$2(MetadataUtil.java:148)
        at java.util.Optional.orElseThrow(Optional.java:290)
        at com.facebook.presto.metadata.MetadataUtil.createQualifiedObjectName(MetadataUtil.java:147)
        at com.facebook.presto.execution.CreateViewTask.execute(CreateViewTask.java:82)
        at com.facebook.presto.execution.CreateViewTask.execute(CreateViewTask.java:49)
        at com.facebook.presto.execution.DataDefinitionExecution.start(DataDefinitionExecution.java:187)
        at com.facebook.presto.$gen.Presto_0_247_a896816____20210303_053748_1.run(Unknown Source)
        at com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:254)
        at com.facebook.presto.dispatcher.LocalDispatchQuery.lambda$startExecution$5(LocalDispatchQuery.java:114)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

When I switch to 'use mongodb.test', and issue create view as below, I get this connector does not support creating views as follows.

presto:test> create view xyz_view as select name,city,dob from mongodb.test.actors;
Query 20210303_062603_00045_ecshq failed: This connector does not support creating views
com.facebook.presto.spi.PrestoException: This connector does not support creating views
        at com.facebook.presto.spi.connector.ConnectorMetadata.createView(ConnectorMetadata.java:528)
        at com.facebook.presto.metadata.MetadataManager.createView(MetadataManager.java:1030)
        at com.facebook.presto.execution.CreateViewTask.execute(CreateViewTask.java:108)
        at com.facebook.presto.execution.CreateViewTask.execute(CreateViewTask.java:49)
        at com.facebook.presto.execution.DataDefinitionExecution.start(DataDefinitionExecution.java:187)
        at com.facebook.presto.$gen.Presto_0_247_a896816____20210303_053748_1.run(Unknown Source)
        at com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:254)
        at com.facebook.presto.dispatcher.LocalDispatchQuery.lambda$startExecution$5(LocalDispatchQuery.java:114)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

I have checked that ConnectorMetadata.java createView() does not have any code except to throw this exception.

The same error comes when I switch schema to mysql.imdb_full saying connector does not support create view.

But mysql connector documentation does not show create view as a limitation.

Kindly help.

Solution

  • View creation is supported in Hive connector only.

    In particular, it's not supported in JDBC connectors like MySQL.

    But mysql connector documentation does not show create view as a limitation.

    I filed https://github.com/trinodb/trino/issues/7150 for this.