Hi Daniel, I can succesfully query data from tables in public schema.
i.e. when my database is 'gis' and my connection url in data store config (named 'pg') is "url": jdbc:postgresql://localhost:5432/gis" then: use pg.public; select * from roads; is executed succesfully. I see that you're trying to query 'postgres' schema (do you connect to 'postgres' db?). In this case you should ommit the schema name like below: use pg; select * from pg_extension; and you should see the results. But with schema name: use pg.postgres; select * from pg_extension; it throws the error. Regards, Karol Potocki On Fri, Jan 08, 2016 at 8:28 PM, daniel.a.co...@accenture.com <daniel.a.co...@accenture.com> wrote: > Hi, > > I am trying to query a Postgres database will the Apache distribution of > Drill, version 1.4.0. This issue is possibly related to the unresolved thread > here: > http://mail-archives.apache.org/mod_mbox/drill-user/201509.mbox/%3c96f2de9e-333b-47a8-8e36-8df72e88d...@contiamo.com%3E > > I've added the jets3t and JDBC jars to the 3rdparty folder. My storage > configuration in the Drill UI is as follows (with name "postgres"): > > { > "type": "jdbc", > "driver": "org.postgresql.Driver", > "url": "jdbc:postgresql://<my_postgres_ip>:5432/<database_name>", > "username": "<username>", > "password": "<password>", > "enabled": true > } > > When I run the query through the Drill shell in verbose mode I get: > > 0: jdbc:drill:zk=local> ALTER SESSION SET `exec.errors.verbose` = true; > 0: jdbc:drill:zk=local> use postgres.postgres; > 0: jdbc:drill:zk=local> select * from sensor_data; > > Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the > SQL query. > > sql SELECT * > FROM "postgres"."sensor_data" > plugin postgres > Fragment 0:0 > > [Error Id: 3f103697-f1cc-4aea-898b-7c47990a438b on ip-10-0-0-92:31010] > > (org.postgresql.util.PSQLException) ERROR: relation "postgres.sensor_data" > 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():645 > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags():481 > org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery():361 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.commons.dbcp.DelegatingStatement.executeQuery():208 > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177 > org.apache.drill.exec.physical.impl.ScanBatch.<init>():108 > org.apache.drill.exec.physical.impl.ScanBatch.<init>():136 > 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():147 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():127 > org.apache.drill.exec.physical.impl.ImplCreator.getChildren():170 > org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101 > org.apache.drill.exec.physical.impl.ImplCreator.getExec():79 > org.apache.drill.exec.work.fragment.FragmentExecutor.run():230 > org.apache.drill.common.SelfCleaningRunnable.run():38 > java.util.concurrent.ThreadPoolExecutor.runWorker():1145 > java.util.concurrent.ThreadPoolExecutor$Worker.run():615 > java.lang.Thread.run():745 (state=,code=0) > > However, when I run "show tables;" I am able to confirm that table > "sensor_data" exists along the several other tables in Postgres. Any idea > what is causing the error? > > Thanks, > Dan > > ________________________________ > > This message is for the designated recipient only and may contain privileged, > proprietary, or otherwise confidential information. If you have received it > in error, please notify the sender immediately and delete the original. Any > other use of the e-mail by you is prohibited. Where allowed by local law, > electronic communications with Accenture and its affiliates, including e-mail > and instant messaging (including content), may be scanned by our systems for > the purposes of information security and assessment of internal compliance > with Accenture policy. > ______________________________________________________________________________________ > > www.accenture.com[http://www.accenture.com] > >
