You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Michael Franzkowiak <mi...@contiamo.com> on 2015/09/22 12:53:31 UTC
JDBC Storage Plugin and Postgres
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 :) ).
Re: JDBC Storage Plugin and Postgres
Posted by Jacques Nadeau <ja...@dremio.com>.
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 <mi...@contiamo.com>
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 <mi...@contiamo.com>
> 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 <ad...@maprtech.com>
> 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 <
> michael@contiamo.com>
> >> 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 <ja...@dremio.com> 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" <mi...@contiamo.com>
> >>> 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
> >
> >
>
>
Re: JDBC Storage Plugin and Postgres
Posted by Michael Franzkowiak <mi...@contiamo.com>.
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 <mi...@contiamo.com> 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 <ad...@maprtech.com> 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 <mi...@contiamo.com>
>> 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 <ja...@dremio.com> 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" <mi...@contiamo.com>
>>> 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>
>
Re: JDBC Storage Plugin and Postgres
Posted by Michael Franzkowiak <mi...@contiamo.com>.
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 <ad...@maprtech.com> 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 <mi...@contiamo.com>
> 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 <ja...@dremio.com> 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" <mi...@contiamo.com>
>> 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>
Re: JDBC Storage Plugin and Postgres
Posted by Abdel Hakim Deneche <ad...@maprtech.com>.
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 <mi...@contiamo.com>
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 <ja...@dremio.com> 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" <mi...@contiamo.com>
> 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>
Re: JDBC Storage Plugin and Postgres
Posted by Michael Franzkowiak <mi...@contiamo.com>.
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 <ja...@dremio.com> 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" <mi...@contiamo.com> 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 :) ).
>>
Re: JDBC Storage Plugin and Postgres
Posted by Jacques Nadeau <ja...@dremio.com>.
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" <mi...@contiamo.com> 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 :) ).
>