[
https://issues.apache.org/jira/browse/DRILL-4326?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15127045#comment-15127045
]
Akon Dey commented on DRILL-4326:
---------------------------------
On further investigation, I found that this actually works if you use the table
name {{public.ips}} instead of {{ips}}. This should be documented somewhere. It
will help newbies start using Drill more easily.
There is still a usability issue which should be fixed or appropriately
documented. The {{show tables;}} command lists the table with name {{its}},
however, to access it one must qualify the table name with its schema name, in
this case {{public}} using {{public.ps}}.
Please see the log below for details.
{noformat}
0: jdbc:drill:zk=local> use pgdb;
use pgdb;
1 row selected (0.461 seconds)
+-------+-----------------------------------+
| ok | summary |
+-------+-----------------------------------+
| true | Default schema changed to [pgdb] |
+-------+-----------------------------------+
0: jdbc:drill:zk=local> show tables;
show tables;
59 rows selected (0.687 seconds)
+---------------+--------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+---------------+--------------------------+
| pgdb.test | ips |
| pgdb.test | pg_aggregate |
| pgdb.test | pg_am |
| pgdb.test | pg_amop |
| pgdb.test | pg_amproc |
| pgdb.test | pg_attrdef |
| pgdb.test | pg_attribute |
| pgdb.test | pg_auth_members |
| pgdb.test | pg_authid |
| pgdb.test | pg_cast |
| pgdb.test | pg_class |
| pgdb.test | pg_collation |
| pgdb.test | pg_constraint |
| pgdb.test | pg_conversion |
| pgdb.test | pg_database |
| pgdb.test | pg_db_role_setting |
| pgdb.test | pg_default_acl |
| pgdb.test | pg_depend |
| pgdb.test | pg_description |
| pgdb.test | pg_enum |
| pgdb.test | pg_event_trigger |
| pgdb.test | pg_extension |
| pgdb.test | pg_foreign_data_wrapper |
| pgdb.test | pg_foreign_server |
| pgdb.test | pg_foreign_table |
| pgdb.test | pg_index |
| pgdb.test | pg_inherits |
| pgdb.test | pg_language |
| pgdb.test | pg_largeobject |
| pgdb.test | pg_largeobject_metadata |
| pgdb.test | pg_namespace |
| pgdb.test | pg_opclass |
| pgdb.test | pg_operator |
| pgdb.test | pg_opfamily |
| pgdb.test | pg_pltemplate |
| pgdb.test | pg_proc |
| pgdb.test | pg_range |
| pgdb.test | pg_rewrite |
| pgdb.test | pg_seclabel |
| pgdb.test | pg_shdepend |
| pgdb.test | pg_shdescription |
| pgdb.test | pg_shseclabel |
| pgdb.test | pg_statistic |
| pgdb.test | pg_tablespace |
| pgdb.test | pg_trigger |
| pgdb.test | pg_ts_config |
| pgdb.test | pg_ts_config_map |
| pgdb.test | pg_ts_dict |
| pgdb.test | pg_ts_parser |
| pgdb.test | pg_ts_template |
| pgdb.test | pg_type |
| pgdb.test | pg_user_mapping |
| pgdb.test | sql_features |
| pgdb.test | sql_implementation_info |
| pgdb.test | sql_languages |
| pgdb.test | sql_packages |
| pgdb.test | sql_parts |
| pgdb.test | sql_sizing |
| pgdb.test | sql_sizing_profiles |
+---------------+--------------------------+
0: jdbc:drill:zk=local> select * from pgdb.test.ips;
select * from pgdb.test.ips;
0: jdbc:drill:zk=local> Error: DATA_READ ERROR: The JDBC storage plugin failed
while trying setup the SQL query.
sql SELECT *
FROM "test"."ips"
plugin pgdb
Fragment 0:0
[Error Id: 5455fd05-6b25-4489-bc5c-5615db197b9f on 10.200.104.128:31010]
(state=,code=0)
0: jdbc:drill:zk=local> select * from public.ips;
select * from public.ips;
2 rows selected (0.121 seconds)
+-------+----------+
| ipid | ipv4dot |
+-------+----------+
| 1 | 1.2.3.4 |
| 2 | 1.2.3.5 |
+-------+----------+
0: jdbc:drill:zk=local>
{noformat}
> JDBC Storage Plugin for PostgreSQL does not work
> ------------------------------------------------
>
> Key: DRILL-4326
> URL: https://issues.apache.org/jira/browse/DRILL-4326
> Project: Apache Drill
> Issue Type: Bug
> Components: Storage - Other
> Affects Versions: 1.3.0, 1.4.0, 1.5.0
> Environment: Mac OS X JDK 1.8 PostgreSQL 9.4.4 PostgreSQL JDBC jars
> (postgresql-9.2-1004-jdbc4.jar, postgresql-9.1-901-1.jdbc4.jar, )
> Reporter: Akon Dey
>
> Queries with the JDBC Storage Plugin for PostgreSQL fail with DATA_READ ERROR.
> The JDBC Storage Plugin settings in use are:
> {code}
> {
> "type": "jdbc",
> "driver": "org.postgresql.Driver",
> "url": "jdbc:postgresql://127.0.0.1/test",
> "username": "akon",
> "password": null,
> "enabled": false
> }
> {code}
> Please refer to the following stack for further details:
> {noformat}
> Akons-MacBook-Pro:drill akon$
> ./distribution/target/apache-drill-1.5.0-SNAPSHOT/apache-drill-1.5.0-SNAPSHOT/bin/drill-embedded
> Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M;
> support was removed in 8.0
> Jan 29, 2016 9:17:18 AM org.glassfish.jersey.server.ApplicationHandler
> initialize
> INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29
> 01:25:26...
> apache drill 1.5.0-SNAPSHOT
> "a little sql for your nosql"
> 0: jdbc:drill:zk=local> !verbose
> verbose: on
> 0: jdbc:drill:zk=local> use pgdb;
> +-------+-----------------------------------+
> | ok | summary |
> +-------+-----------------------------------+
> | true | Default schema changed to [pgdb] |
> +-------+-----------------------------------+
> 1 row selected (0.753 seconds)
> 0: jdbc:drill:zk=local> select * from ips;
> Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the
> SQL query.
> sql SELECT *
> FROM "test"."ips"
> plugin pgdb
> Fragment 0:0
> [Error Id: 26ada06d-e08d-456a-9289-0dec2089b018 on 10.200.104.128:31010]
> (state=,code=0)
> java.sql.SQLException: DATA_READ ERROR: The JDBC storage plugin failed while
> trying setup the SQL query.
> sql SELECT *
> FROM "test"."ips"
> plugin pgdb
> Fragment 0:0
> [Error Id: 26ada06d-e08d-456a-9289-0dec2089b018 on 10.200.104.128: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:1923)
> at
> org.apache.drill.jdbc.impl.DrillResultSetImpl.execute(DrillResultSetImpl.java:73)
> 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:101)
> at sqlline.Commands.execute(Commands.java:841)
> at sqlline.Commands.sql(Commands.java:751)
> at sqlline.SqlLine.dispatch(SqlLine.java:746)
> at sqlline.SqlLine.begin(SqlLine.java:621)
> at sqlline.SqlLine.start(SqlLine.java:375)
> at sqlline.SqlLine.main(SqlLine.java:268)
> 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 "test"."ips"
> plugin pgdb
> Fragment 0:0
> [Error Id: 26ada06d-e08d-456a-9289-0dec2089b018 on 10.200.104.128:31010]
> at
> org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:119)
> at
> org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:113)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:46)
> at
> org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:31)
> at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:67)
> at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:374)
> at
> org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:89)
> at
> org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:252)
> at
> org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:123)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:285)
> at
> org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:257)
> 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)
> 0: jdbc:drill:zk=local>
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
