Can you file a JIRA. We've been working on adding integration test per system. We got the MySQL one in and found a few hiccups in 1.2. I see a couple issues have come up for Oracle and I'm guessing we'll hit a few for Postgres as well.
-- Jacques Nadeau CTO and Co-Founder, Dremio On Wed, Oct 21, 2015 at 1:43 PM, Michael Franzkowiak <[email protected]> wrote: > Hi, > > now that 1.2 is out I wanted to give the JDBC storage plugin another try > with postgres (9.4). > > I still have the same issue: All queries to the postgres DB are prefixed > with the DB name (“mydb” below) and thus fail with a “relation does not > exist”. > > I tried not specifying a database in the connection url in the storage > plugin configuration but Drill doesn’t let me even save that configuration. > > My config looks like this: > > { > "type": "jdbc", > "driver": "org.postgresql.Driver", > "url": "jdbc:postgresql://127.0.0.1/mydb", > "username": "dev", > "password": null, > "enabled": true > } > > The postgres jdbc jar is in the 3rd party folder and I can see the queries > arriving (and failing) at postgres. > > Cheers > Michael > > > On 22 Sep 2015, at 18:47, Michael Franzkowiak <[email protected]> > wrote: > > > > Thanks, Abdel. > > > > This is the output: > > > > 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: 48eb1871-33bd-40b7-b2a0-a34bc98a1511 on 192.168.233.201:31010 > ] > > > > (org.postgresql.util.PSQLException) ERROR: relation "mydb.mytable" does > not exist > > Position: 15 > > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2182 > > org.postgresql.core.v3.QueryExecutorImpl.processResults():1911 > > org.postgresql.core.v3.QueryExecutorImpl.execute():173 > > org.postgresql.jdbc2.AbstractJdbc2Statement.execute():618 > > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags():454 > > org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery():334 > > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():153 > > org.apache.drill.exec.physical.impl.ScanBatch.<init>():101 > > org.apache.drill.exec.physical.impl.ScanBatch.<init>():128 > > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40 > > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33 > > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():150 > > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():173 > > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():130 > > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():173 > > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():104 > > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > > org.apache.drill.exec.work.fragment.FragmentExecutor.run():232 > > org.apache.drill.common.SelfCleaningRunnable.run():38 > > java.util.concurrent.ThreadPoolExecutor.runWorker():1142 > > java.util.concurrent.ThreadPoolExecutor$Worker.run():617 > > java.lang.Thread.run():745 (state=,code=0) > > > >> On 22 Sep 2015, at 18:38, Abdel Hakim Deneche <[email protected]> > wrote: > >> > >> Michael, > >> > >> To turn Drill's verbose errors use the following command: > >> > >> ALTER SESSION SET `exec.errors.verbose` = true > >> > >> Thanks > >> > >> On Tue, Sep 22, 2015 at 8:30 AM, Michael Franzkowiak < > [email protected]> > >> wrote: > >> > >>> 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 :) ). > >>>>> > >>> > >>> > >> > >> > >> -- > >> > >> Abdelhakim Deneche > >> > >> Software Engineer > >> > >> <http://www.mapr.com/> > >> > >> > >> Now Available - Free Hadoop On-Demand Training > >> < > http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available > > > > > >
