Support CREATE VIEW in JDBC-based connectors

[harlequin]

I wanted to create a VIEW on a connected MSSql Server, which is handled by the JDBC Base Connector.

Which is causing a not supported error.

I have read through the source code and found that the JDBC Base doesn't include "createView" or "listView" statements.

Is there a known limitation why the JDBC is not supporting views?

Because if there will be a check included that the catalog and, maybe, also schema shall be the same on the CREATE VIEW statement,
this should work ...

[ebyhr]

I guess you've mixed up SQL Server view and Trino view. Those "createView" & "listView" should be used for Trino views.

• SQL Server view: Created by SQL Server and the view definition uses their syntax
• Trino view: Created by Trino and the view definition uses our syntax

[harlequin]

Ok, have understand the topic.

But how, or what, has to be changed to make the "CREATE VIEW" enabled on JDBC/MSSQL connections?

I have seen that there is a pull request for "direct sql command execution" on the underlaying connector.
But this seems for me a little bit hacky

[fong-kinduct]

The ability to quickly create views from your warehouse down stream into JDBC based connectors would bring a lot of value to the project.

since we can't wrap drop table ...; create table as select ...; in a transaction as a work around, the ability to create or replace view ... in JDBC would enable to share views downstream to applications much more efficiently and seamlessly.

[Bart-De-Pauw]

Currently I'm using Oracle connector and PostgreSQL connectors.
When executing "create view", the following error is raised "This connector does not support creating views"
It's also as such documented, but when would this enhancement be picked-up and also maid availble for Oracle, PostgreSQL or other connectors?
This would be a major improvement in my opinion, as we plan on using DBT on top of Trino to get a holistic view over our data.

[JustGitting]

I've moved this to a new issue #21118 as seems to be a different to OP's problem.

Sorry if this is the wrong place to post or I should create a new issue. However, I've just found the same problem.

I've been using DBT to transform tables on an SQL Server, but now want to connect to several DBs and Trino is the way to go. I've setup Trino and can connect successfully to the different SQL Servers. However, I'm unable to create views, which is a key task of DBT.

To test "Create View" I did the following:

$ cat /etc/trino/catalog/sql_server_1.properties
connector.name=sqlserver
connection-url=jdbc:sqlserver://SQLSERVER1:1433;databaseName=Test_DB;encrypt=false;trustcertificate=true
connection-user=${ENV:DB_USER_SQLSERVER}
connection-password=${ENV:DB_PASSWORD_SQLSERVER}

$ sudo docker exec -it <CONTAINTERID> trino --catalog sql_server_1

Try to create view directly in trino:

trino> CREATE VIEW sql_server_1.my_schema.test_view AS SELECT 1 AS val;
Query ... failed: This connector does not support creating views

What is the work-around for MS SQL Server using the JDBC connector or something we can change in DBT?

The documentation for the SQL Server connector (https://trino.io/docs/current/connector/sqlserver.html#sql-support) indicates it supports all basic SQL syntax, including "CREATE VIEW" (https://trino.io/docs/current/sql/create-view.html). Hence my confusion.

Also of note, when I checked the catalog in DBeaver, it shows all the views that I've created previously in DBT as tables. Very odd.

[JustGitting]

Hi @ebyhr, why isn't "creating views" via JDBC supported? It's a fundamental SQL feature and is needed for DBT.

I found the PR to support generic JDBC drivers for Trino to connect to other DBs (#3105). However, development stopped in 2022.

In this case, can the Trino SQL Server docs (https://trino.io/docs/current/connector/sqlserver.html) be updated to let people know of this limitation. It only mentions "Column names with leading and trailing spaces are not supported" as a limitation.

Scratch that ... I just found the issue "Document CREATE VIEW is not supported in connectors #7150"
which states "Rather than documenting limitations (of which there are too many) we document explicitly what is supported."

This is a very big limitation that should be flagged/noted. It would have saved me setting up Trino.

[maxfomenko]

The issue should be addressed by this PR:
#22556