JdbcCatalog fails to initialize with MS SQL Server

akursar commented 3 months ago

Apache Iceberg version

1.5.0 (latest release)

Query engine

None

Please describe the bug 🐞

Attempting to use MS SQL Server as JdbcCatalog produces the following:

Exception in thread "main" org.apache.iceberg.jdbc.UncheckedSQLException: Cannot check and eventually update SQL schema
    at org.apache.iceberg.jdbc.JdbcCatalog.updateSchemaIfRequired(JdbcCatalog.java:238)
    at org.apache.iceberg.jdbc.JdbcCatalog.initialize(JdbcCatalog.java:144)
    at org.apache.iceberg.CatalogUtil.loadCatalog(CatalogUtil.java:255)
    at org.apache.iceberg.CatalogUtil.buildIcebergCatalog(CatalogUtil.java:309)
    at org.apache.iceberg.rest.RESTCatalogServer.backendCatalog(RESTCatalogServer.java:84)
    at org.apache.iceberg.rest.RESTCatalogServer.main(RESTCatalogServer.java:88)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'COLUMN'.

I believe this is due to changes in https://github.com/apache/iceberg/pull/9487 which introduced the failing ADD COLUMN statement.

Setting jdbc.schema-version=V0 to avoid the schema update prevents the fatal error.

nastra commented 3 months ago

@jbonofre FYI

jbonofre commented 3 months ago

@nastra thanks. I will take a look and move forward quickly on multi backends support.

pp-akursar commented 3 months ago

jdbc.schema-version=V0 workaround gets past initialization, but LIMIT clause elsewhere causes problems

UncheckedSQLException: Failed to execute exists query: SELECT table_namespace FROM iceberg_tables WHERE catalog_name = ? AND  
(table_namespace = ? OR table_namespace LIKE ? ESCAPE '\') LIMIT 1

jbonofre commented 3 months ago

@pp-akursar yes, that's expected for LIMIT as the syntax depends of the database backend (dialect).

Currently, JDBC Catalog is "guarantee" to work with SqlLite and PostgreSQL. But other backend (MySQL, MS SQL, ...), please be patient, I'm working on it.

OElabed commented 3 weeks ago

@jbonofre thank you for supporting that. I have just 2 questions:

i see that you target to support multiple backend (MySQL, MS SQL, ...). Do you target to support oracle database because we have the same error described above with v0 and v1?
if yes 🙂, can you share please the PR and in which version this change will be released?

Thank you again

jbonofre commented 3 weeks ago

@OElabed I'm working on 2 PRs:

short term, I'm implementing backend adapters as I did in ActiveMQ
mid term, I'm evaluating using jbid as abstract jdbc backend

I will keep you posted.

elabedo commented 3 weeks ago

Thank you @jbonofre , have you the PR links ? we can help you on the review or suggestions :)

Stefn93 commented 1 week ago

Hi, I had a similar issue with my oracle DB, just found out here that it's not gonna work for syntax problems. Should I open a new issue for the Oracle DB or is it fine to track this here?

jbonofre commented 1 week ago

@Stefn93 I propose to use this issue as it's basically the same thing with different backend.

apache / iceberg