Yes, I use the postgres jdbc jar. I can see the queries reaching the DB. Also, drill does validate that a table exists, it won’t let me query for non-existing tables (VALIDATION ERROR: From line 1, column 18 to line 1, column 26: Table ‘foobar' not found).
When querying, postgres complains about the “mydb” (database name) part in the FROM clause (I don’t think tables should be prefixed with the database name in postgres). I’m also wondering about why it’s trying to execute a “SELECT *” query when I’m specifying an individual column. Here is the output with verbose errors: 0: jdbc:drill:zk=local> !verbose verbose: on 0: jdbc:drill:zk=local> use postgres; +-------+---------------------------------------+ | ok | summary | +-------+---------------------------------------+ | true | Default schema changed to [postgres] | +-------+---------------------------------------+ 1 row selected (1.594 seconds) 0: jdbc:drill:zk=local> show tables; +--+ | | +--+ +--+ No rows selected (0.23 seconds) 0: jdbc:drill:zk=local> select some_column from mytable; Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM “mydb”."mytable" plugin postgres Fragment 0:0 [Error Id: 52096123-51b2-4d59-83f7-a8368f146d93 on 192.168.233.201:31010] (state=,code=0) java.sql.SQLException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM “mydb”."mytable" plugin postgres Fragment 0:0 [Error Id: 52096123-51b2-4d59-83f7-a8368f146d93 on 192.168.233.201:31010] at org.apache.drill.jdbc.impl.DrillCursor.nextRowInternally(DrillCursor.java:247) at org.apache.drill.jdbc.impl.DrillCursor.loadInitialSchema(DrillCursor.java:290) at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:1359) at org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:74) at net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:404) at net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(AvaticaStatement.java:351) at net.hydromatic.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:338) at net.hydromatic.avatica.AvaticaStatement.execute(AvaticaStatement.java:69) at org.apache.drill.jdbc.impl.DrillStatementImpl.execute(DrillStatementImpl.java:86) at sqlline.Commands.execute(Commands.java:841) at sqlline.Commands.sql(Commands.java:751) at sqlline.SqlLine.dispatch(SqlLine.java:737) at sqlline.SqlLine.begin(SqlLine.java:612) at sqlline.SqlLine.start(SqlLine.java:366) at sqlline.SqlLine.main(SqlLine.java:259) Caused by: org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM “mydb”."mytable" plugin postgres Fragment 0:0 [Error Id: 52096123-51b2-4d59-83f7-a8368f146d93 on 192.168.233.201:31010] at org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:118) at org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:110) at org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:47) at org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:32) at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:61) at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:233) at org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:205) at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:89) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324) at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:254) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324) at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324) at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:242) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324) at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324) at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:847) at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131) at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:511) at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:468) at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:382) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354) at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:111) at java.lang.Thread.run(Thread.java:745) > On 22 Sep 2015, at 15:24, Jacques Nadeau <[email protected]> wrote: > > Can you turn on verbose errors and rerun your query? Also, did you add the > Postgres jdbc driver jar to the classpath? Drill doesn't currently > prepackage particular driver jars. > On Sep 22, 2015 3:53 AM, "Michael Franzkowiak" <[email protected]> wrote: > >> I wanted to give the JDBC Storage Plugin a spin and tried to connect to a >> Postgres DB. >> >> I set up the storage plugin using the following config: >> >> { >> "type": "jdbc", >> "driver": "org.postgresql.Driver", >> "url": "jdbc:postgresql://127.0.0.1/mydb", >> "username": "dev", >> "password": null, >> "enabled": true >> } >> >> Unfortunately SHOW TABLES does not return anything. >> >> Also I wasn't able to execute a query on the Postgres DB. For any query I >> ran (e.g. SELECT some_column FROM mytable) it would just return: >> >> Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup >> the SQL query. >> sql SELECT * >> FROM "mydb"."mytable" >> plugin postgres >> Fragment 0:0 >> >> Anything I'm doing wrong? Not a drill expert ( yet :) ). >>
