Not able use with SQL Client like SQLWorkbench - No support for catalog / schema / set Max Rows

[ssenathi]

Hi,

I was excited to see a new JDBC Driver for Athena and thought of using it for our interactive use.

the key issues are

1. Doesn't support Catalog / Schema
2. Doesn't support VIRTUAL/ EXTERNAL TABLE ( All tables in Athena are defined as External Tables, It only looks for TABLE and VIEW)
3. Doesn't support VIRTUAL VIEW ( Any views defined in Athena is VIRTUAL VIEW)
4. Most Importantly - i Couldn't any query from the SQLWOrkbench as it always returns the following exception in the log (getWarnings not supported)
5. No Proxy Support ( we sit behind proxy server and there is no way to pass in PROXY config to client) - ( after patching getWarning to return empty string).
   2019-09-02 14:12:41 INFO Loaded global SSH host configurations from C:\Users\STHSH4.sqlworkbench\wbssh.settings
   2019-09-02 14:13:33 INFO Global SSH host configuration file C:\Users\STHSH4.sqlworkbench\wbssh.settings removed.
   2019-09-02 14:13:36 WARN The JDBC driver does not support the setMaxRows() function! (Not implemented)
   2019-09-02 14:13:37 WARN Error retrieving warnings Not implemented
   java.lang.UnsupportedOperationException: Not implemented
   at io.burt.athena.AthenaStatement.getWarnings(AthenaStatement.java:281)
   at workbench.util.SqlUtil.getWarnings(SqlUtil.java:1651)
   at workbench.sql.SqlCommand.appendWarnings(SqlCommand.java:400)
   at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:172)
   at workbench.sql.StatementRunner.runStatement(StatementRunner.java:552)
   at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3468)
   at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2199)
   at workbench.gui.sql.SqlPanel$16.run(SqlPanel.java:2137)

SELECT count(*) from cmdm_sf_rec_results.account
java.util.concurrent.ExecutionException: software.amazon.awssdk.core.exception.SdkClientException
2019-09-02 14:12:41 INFO Loaded global SSH host configurations from C:\Users\STHSH4.sqlworkbench\wbssh.settings
2019-09-02 14:13:33 INFO Global SSH host configuration file C:\Users\STHSH4.sqlworkbench\wbssh.settings removed.
2019-09-02 14:13:36 WARN The JDBC driver does not support the setMaxRows() function! (Not implemented)
2019-09-02 14:13:37 WARN Error retrieving warnings Not implemented
java.lang.UnsupportedOperationException: Not implemented
at io.burt.athena.AthenaStatement.getWarnings(AthenaStatement.java:281)
at workbench.util.SqlUtil.getWarnings(SqlUtil.java:1651)
at workbench.sql.SqlCommand.appendWarnings(SqlCommand.java:400)
at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:172)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:552)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3468)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2199)
at workbench.gui.sql.SqlPanel$16.run(SqlPanel.java:2137)

2019-09-02 14:13:37 ERROR Error executing:
SELECT count(*) from
java.util.concurrent.ExecutionException: software.amazon.awssdk.core.exception.SdkClientException
2019-09-02 14:13:59 WARN The JDBC driver does not support the setMaxRows() function! (Not implemented)
2019-09-02 14:13:59 WARN Error retrieving warnings Not implemented
java.lang.UnsupportedOperationException: Not implemented
at io.burt.athena.AthenaStatement.getWarnings(AthenaStatement.java:281)
at workbench.util.SqlUtil.getWarnings(SqlUtil.java:1651)
at workbench.sql.SqlCommand.appendWarnings(SqlCommand.java:400)
at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:172)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:552)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3468)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2199)
at workbench.gui.sql.SqlPanel$16.run(SqlPanel.java:2137)

If the intention is purely for using in java code ( not interactive use) , i understand. I assumed any JDBC (standard) drivers could be used with Interactive tool like SQLWorkbench. Today we use SQLWorkbench for Athena for Adhoc / interactive/ discovery type which is well suited for athena

[iconara]

Hi and thank you for trying it out. The primary target for us has been non-interactive use, but there's no reason it couldn't work for interactive use eventually. JDBC is huge, and contains a lot of details that are not important for an application, and covering every single one of these has not been a priority. SQLWorkbench will use a couple of features, other tools other features and unless you cover them all there's always something that will break unfortunately.

In this case you seem to have run into that setMaxRows is not implemented. Please read the Known issues & limitations section of the readme for the difference between UnsupportedOperationException/"not implemented" and SQLFeatureNotSupportedException.

Could you clarify what you mean by the list of features? I don't understand what for example "Doesn't support Catalog / Schema" means.

[DALDEI]

I belive what ssenthi is referring to is full support for the various metadata APIs in Connection and ResultSet (and more?)
DB GUIs typically make heavy use of these in order to present a tree of database objects - schema/database/tables and usually try to look for things like stored procedures, views, triggers etc. Possibly this can be hacked by creative use of the result values instead of throwing exceptions ? I doubt it --
This is the kind of thing that even if your 100% JDBC Compliant theres enough optional and vendor differences that its unlikely to work without huge effort. (or a kind of hybrid with the official JDBC under the hood providing the metadata - not fun )

[iconara]

I think this is an area where we will have to rely on contributions. We will probably not close the holes that appear when using the driver with different tools, and we need help to get them fixed. Some of these will be very easy to fix, but some will be much more complicated.

To take one example, DatabaseMetadata#getProcedures and a similar methods return a ResultSet. The ResultSet interface is enormous, and the implementation we use for actual queries can't be reused in this context. Many of these methods only need to return a ResultSet that is empty, but even an implementation of an empty result is a couple of hundred of lines of code, unfortunately.

[grddev]

For what it is worth, I set up an interactive session in DBeaver, and I was able to query just fine using this driver. I deselected all "optional" features in the driver-manager configuration, and if I open e.g. the tables view, it complains about it not being supported, but opening a SQL editor and running statements work just fine.

To take one example, DatabaseMetadata#getProcedures and a similar methods return a ResultSet. The ResultSet interface is enormous, and the implementation we use for actual queries can't be reused in this context. Many of these methods only need to return a ResultSet that is empty, but even an implementation of an empty result is a couple of hundred of lines of code, unfortunately. I wasn't running queries through a proxy, though.

While I don't know for sure, I would venture a guess that the reason the methods return a ResultSet is that the expectation is that you would effectively use the SQL standard INFORMATION_SCHEMA or an equivalent to issue real database queries to implement the methods underlying the DatabaseMetadata class. That being said, the information-schema implementation in Athena seems a bit lacking (e.g. it doesn't seem to be possible to distinguish a table and a view), and as far as I know, the DDL queries (like SHOW TABLES) does not seem to be composable, so some of the information would require custom ResultSet implementations.

However, contrary to your claim, I think the Result abstraction readily available internally in the driver would make it relatively easy implement the custom ResultSets. An empty ResultSet would likely not be more than 50 lines of boilerplate, which is still a lot, but far from hundreds of lines.