Hello team, I have recently started writing an implementation of Avatica server. I am trying to use a commercial ODBC connector for Windows to integrate tools like Excel and Tableau via avatica server.
Here is the driver: https://www.cloudera.com/downloads/connectors/phoenix/odbc/1-0-8-1011.html The driver seems to be dated from June 23, 2016 (before the Hortonworks acquisition): https://hortonworks.com/wp-content/uploads/2016/08/phoenix-ODBC-guide.pdf I have installed the driver for Windows and launched the Windows ODBC configuration tool: %windir%\system32\odbcad32.exe And set a configuration as follows: ---------- - Configuration ---------- host: localhost port: 8080 path: test Mechanism: no authentication ---------- On the avatica side, I have implemented some sample code to place avatica in front of H2 with northwind database: Older style implementation (18 Mar 2016, Release 1.7.1) ```gradle dependencies { implementation 'org.apache.calcite.avatica:avatica-core:1.7.1' implementation 'org.apache.calcite.avatica:avatica-server:1.7.1' } ``` ```java Server h2server = Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092").start(); Server h2serverWeb = Server.createWebServer("-webAllowOthers", "-webPort", "8082").start(); Connection h2connection = DriverManager.getConnection("jdbc:h2:./test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=RUNSCRIPT FROM 'src/test/resources/northwind.sql'", "sa", ""); ConfigurableJdbcMeta meta = new ConfigurableJdbcMeta("jdbc:h2:tcp://localhost:9092/./test", "sa", ""); LocalService service = new LocalService(meta); HughProtobufHandler pHandler = new HughProtobufHandler(service); HughJsonHandler jHandler = new HughJsonHandler(service); server.start(); server.join(); ``` And newer style implementation (05 Apr 2024, Release 1.25.0) ```gradle dependencies { implementation 'org.apache.calcite.avatica:avatica-core:1.25.0' implementation 'org.apache.calcite.avatica:avatica-core:1.25.0' } ``` ```java Server h2server = Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092").start(); Server h2serverWeb = Server.createWebServer("-webAllowOthers", "-webPort", "8082").start(); Connection h2connection = DriverManager.getConnection("jdbc:h2:./test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=RUNSCRIPT FROM 'src/test/resources/northwind.sql'", "sa", ""); ConfigurableJdbcMeta meta = new ConfigurableJdbcMeta("jdbc:h2:tcp://localhost:9092/./test", "sa", ""); LocalService service = new LocalService(meta); HughJsonHandler handler = new HughJsonHandler(service); HttpServer.Builder<Server> builder = new HttpServer.Builder<Server>() .withHandler(handler) .withPort(8080); HttpServer avaticaserver = builder.build(); avaticaserver.start(); avaticaserver.join(); ``` Both of these return "500 internal server error". Here is the trace in Wireshark of Phoenix ODBC Connector 1.0.8.1011 for CDP to Avatica (18 Mar 2016, Release 1.7.1). ========== = START Wireshark trace ========== POST /test/ HTTP/1.1 Host: localhost:8080 Content-Type: application/octet-stream Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2 User-Agent: Phoenix ODBC Connection: keep-alive Content-Length: 105 ?org.apache.calcite.avatica.proto.Requests$OpenConnectionRequest& $ae2df14d-913f-1870-03ef-a2d458f6f7cd ` HTTP/1.1 500 Server Error Date: Mon, 19 Aug 2024 13:43:56 GMT Content-Type: application/json;charset=utf-8 Content-Length: 2773 Server: Jetty(9.2.15.v20160210) {"response":"error","exceptions":["com.fasterxml.jackson.core.JsonParseException: Unexpected character ('?' (code 63)): expected a valid value (number, String, array, object, 'true', 'false' or 'null')\n at [Source: \n?org.apache.calcite.avatica.proto.Requests$OpenConnectionRequest\u0012&\n$ae2df14d-913f-1870-03ef-a2d458f6f7cd; line: 2, column: 2]\r\n\tat com.fasterxml.jackson.core.JsonParser._constructError(JsonParser.java:1581)\r\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportError(ParserMinimalBase.java:533)\r\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportUnexpectedChar(ParserMinimalBase.java:462)\r\n\tat com.fasterxml.jackson.core.json.ReaderBasedJsonParser._handleOddValue(ReaderBasedJsonParser.java:1624)\r\n\tat com.fasterxml.jackson.core.json.ReaderBasedJsonParser.nextToken(ReaderBasedJsonParser.java:689)\r\n\tat com.fasterxml.jackson.databind.ObjectMapper._initForReading(ObjectMapper.java:3776)\r\n\tat com.fasterxml.jackson.databind.ObjectMapper._readMapAndClose(ObjectMapper.java:3721)\r\n\tat com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:2726)\r\n\tat org.apache.calcite.avatica.remote.JsonHandler.decode(JsonHandler.java:57)\r\n\tat org.apache.calcite.avatica.remote.JsonHandler.decode(JsonHandler.java:37)\r\n\tat org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:93)\r\n\tat org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:52)\r\n\tat org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:105)\r\n\tat hugh.HughJsonHandler.handle(HughJsonHandler.java:31)\r\n\tat org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)\r\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)\r\n\tat org.eclipse.jetty.server.Server.handle(Server.java:499)\r\n\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)\r\n\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)\r\n\tat org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)\r\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)\r\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)\r\n\tat java.base/java.lang.Thread.run(Thread.java:833)\r\n"],"errorMessage":"JsonParseException: Unexpected character ('?' (code 63)): expected a valid value (number, String, array, object, 'true', 'false' or 'null')\n at [Source: \n?org.apache.calcite.avatica.proto.Requests$OpenConnectionRequest\u0012&\n$ae2df14d-913f-1870-03ef-a2d458f6f7cd; line: 2, column: 2]","errorCode":-1,"sqlState":"00000","severity":"UNKNOWN","rpcMetadata":{"response":"rpcMetadata","serverAddress":"AP-LMcALY5bFLMz:8080"}} POST /test/ HTTP/1.1 Host: localhost:8080 Content-Type: application/octet-stream Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2 User-Agent: Phoenix ODBC Connection: keep-alive Content-Length: 92 {"request":"openConnection","connectionId":"0b4c6fba-0b81-49f5-bdd9-7092754aee69","info":{}} HTTP/1.1 200 OK Date: Mon, 19 Aug 2024 13:43:56 GMT Content-Type: application/json;charset=utf-8 Content-Length: 111 Server: Jetty(9.2.15.v20160210) {"response":"openConnection","rpcMetadata":{"response":"rpcMetadata","serverAddress":"AP-LMcALY5bFLMz:8080"}} POST /test/ HTTP/1.1 Host: localhost:8080 Content-Type: application/octet-stream Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2 User-Agent: Phoenix ODBC Connection: keep-alive Content-Length: 229 {"request":"connectionSync","connectionId":"0b4c6fba-0b81-49f5-bdd9-7092754aee69","connProps":{"connProps":"connPropsImpl","autoCommit":true,"readOnly":false,"transactionIsolation":null,"catalog":null,"schema":null,"dirty":true}} HTTP/1.1 200 OK Date: Mon, 19 Aug 2024 13:43:56 GMT Content-Type: application/json;charset=utf-8 Content-Length: 262 Server: Jetty(9.2.15.v20160210) {"response":"connectionSync","connProps":{"connProps":"connPropsImpl","autoCommit":true,"readOnly":false,"transactionIsolation":2,"catalog":"TEST","schema":"PUBLIC","dirty":false},"rpcMetadata":{"response":"rpcMetadata","serverAddress":"AP-LMcALY5bFLMz:8080"}} POST /test/ HTTP/1.1 Host: localhost:8080 Content-Type: application/octet-stream Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2 User-Agent: Phoenix ODBC Connection: keep-alive Content-Length: 81 {"request":"getTableTypes","connectionId":"0b4c6fba-0b81-49f5-bdd9-7092754aee69"} HTTP/1.1 500 Server Error Date: Mon, 19 Aug 2024 13:43:56 GMT Content-Type: application/json;charset=utf-8 Content-Length: 1980 Server: Jetty(9.2.15.v20160210) {"response":"error","exceptions":["java.lang.NullPointerException\r\n\tat java.base/java.util.Objects.requireNonNull(Objects.java:208)\r\n\tat org.apache.calcite.avatica.jdbc.StatementInfo.<init>(StatementInfo.java:40)\r\n\tat org.apache.calcite.avatica.jdbc.JdbcMeta.registerMetaStatement(JdbcMeta.java:318)\r\n\tat org.apache.calcite.avatica.jdbc.JdbcMeta.getTableTypes(JdbcMeta.java:361)\r\n\tat hugh.ConfigurableJdbcMeta.getTableTypes(ConfigurableJdbcMeta.java:46)\r\n\tat org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:173)\r\n\tat org.apache.calcite.avatica.remote.Service$TableTypesRequest.accept(Service.java:541)\r\n\tat org.apache.calcite.avatica.remote.Service$TableTypesRequest.accept(Service.java:526)\r\n\tat org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)\r\n\tat org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:52)\r\n\tat org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:105)\r\n\tat hugh.HughJsonHandler.handle(HughJsonHandler.java:31)\r\n\tat org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)\r\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)\r\n\tat org.eclipse.jetty.server.Server.handle(Server.java:499)\r\n\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)\r\n\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)\r\n\tat org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)\r\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)\r\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)\r\n\tat java.base/java.lang.Thread.run(Thread.java:833)\r\n"],"errorMessage":"NullPointerException: (null exception message)","errorCode":-1,"sqlState":"00000","severity":"UNKNOWN","rpcMetadata":{"response":"rpcMetadata","serverAddress":"AP-LMcALY5bFLMz:8080"}} POST /test/ HTTP/1.1 Host: localhost:8080 Content-Type: application/octet-stream Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2 User-Agent: Phoenix ODBC Connection: keep-alive request: {"request":"connectionSync","connectionId":"ec77d999-6e4a-1468-06c4-11aec6dac3c4","connProps":{"connProps":"connPropsImpl","autoCommit":true,"readOnly":false,"transactionIsolation":null,"catalog":null,"schema":null,"dirty":true}} Content-Length: 0 HTTP/1.1 500 Server Error Date: Mon, 19 Aug 2024 13:43:57 GMT Content-Type: application/json;charset=utf-8 Content-Length: 1806 Server: Jetty(9.2.15.v20160210) {"response":"error","exceptions":["org.apache.calcite.avatica.NoSuchConnectionException\r\n\tat org.apache.calcite.avatica.jdbc.JdbcMeta.getConnection(JdbcMeta.java:542)\r\n\tat org.apache.calcite.avatica.jdbc.JdbcMeta.connectionSync(JdbcMeta.java:642)\r\n\tat hugh.ConfigurableJdbcMeta.connectionSync(ConfigurableJdbcMeta.java:40)\r\n\tat org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:307)\r\n\tat org.apache.calcite.avatica.remote.Service$ConnectionSyncRequest.accept(Service.java:2048)\r\n\tat org.apache.calcite.avatica.remote.Service$ConnectionSyncRequest.accept(Service.java:2024)\r\n\tat org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)\r\n\tat org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:52)\r\n\tat org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:105)\r\n\tat hugh.HughJsonHandler.handle(HughJsonHandler.java:31)\r\n\tat org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)\r\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)\r\n\tat org.eclipse.jetty.server.Server.handle(Server.java:499)\r\n\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)\r\n\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)\r\n\tat org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)\r\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)\r\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)\r\n\tat java.base/java.lang.Thread.run(Thread.java:833)\r\n"],"errorMessage":null,"errorCode":1,"sqlState":"00000","severity":"ERROR","rpcMetadata":{"response":"rpcMetadata","serverAddress":"AP-LMcALY5bFLMz:8080"}} ========== = END Wireshark trace ========== Questions: 1. Does anyone know if this commercial driver was working with Avatica at any point? 2. Could it still be working, and I am just missing some custom configuration in Avatica? Reference: 1. phoenix-queryserver code - https://github.com/apache/phoenix-queryserver/blob/3360154858e27cabe258dfb33b37ec31ed3bd210/phoenix-queryserver/src/main/java/org/apache/phoenix/queryserver/server/QueryServer.java#L379 From, Hugh Pearse
