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>
