You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by da...@accenture.com on 2016/01/08 20:28:06 UTC
Querying Postgres will Drill
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-8DF72E88D8AD@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
Re: Querying Postgres will Drill
Posted by Karol Potocki <k2...@gmx.com>.
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.corin@accenture.com <da...@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-8DF72E88D8AD@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]
>
>