Dremio3.0.0 connect postgresql8.2 query error

1

Hi,dremio friend:

when I use the dremio3.0 to start querying the data via sql:

select t.ID,ABS(count(1)) dxj from gp.public.“csp_http” t group by t.ID

then i clicked the button “run”
after one minitues the server reponse error. and the executing node’s log beneach:

2018-11-06 07:46:38,979 [e0 - 241eb9c4-47ba-9c87-ea32-35435d15fc00:frag:0:0] INFO c.d.exec.store.jdbc.JdbcRecordReader - User Error Occurred [ErrorId: 1fe0b9ec-fc30-46c5-9067-97e17d95c869]
com.dremio.common.exceptions.UserException: The JDBC storage plugin failed while trying setup the SQL query.
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:746) ~[dremio-common-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:195) [dremio-extra-plugin-jdbc-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.exec.store.CoercionReader.setup(CoercionReader.java:109) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.op.scan.ScanOperator$1.run(ScanOperator.java:189) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.op.scan.ScanOperator$1.run(ScanOperator.java:185) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_152]
at javax.security.auth.Subject.doAs(Subject.java:422) [na:1.8.0_152]
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1836) [hadoop-common-2.8.3.jar:na]
at com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser(ScanOperator.java:185) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.op.scan.ScanOperator.setupReader(ScanOperator.java:177) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.op.scan.ScanOperator.setup(ScanOperator.java:163) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.SmartOp$SmartProducer.setup(SmartOp.java:560) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer(Pipe.java:79) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer(Pipe.java:63) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.SmartOp$SmartProducer.accept(SmartOp.java:530) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.driver.Pipeline.setup(Pipeline.java:58) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution(FragmentExecutor.java:347) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.exec.fragment.FragmentExecutor.run(FragmentExecutor.java:237) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.exec.fragment.FragmentExecutor.access$800(FragmentExecutor.java:88) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run(FragmentExecutor.java:594) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.task.AsyncTaskWrapper.run(AsyncTaskWrapper.java:103) [dremio-sabot-kernel-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
at com.dremio.sabot.task.slicing.SlicingThread.run(SlicingThread.java:110) [dremio-extra-sabot-scheduler-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:301) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:287) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:264) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:231) ~[postgresql-42.1.1.jre7.jar:42.1.1.jre7]
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at com.dremio.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:166) [dremio-extra-plugin-jdbc-3.0.0-201810262305460004-5c90d75.jar:3.0.0-201810262305460004-5c90d75]
… 26 common frames omitted

2

Hi @bjbhjsj,

It looks like the query is timing out. If you were to run this query directly in PostgreSQL how long does it take?

3

thanks jduong:

I query this sql by navicat tools
select t.id,count(1) cnt from csp_http t group by t.id limit 50
is ok

4

Hi @bjbhjsj,

What about without the limit clause? Also with the call to ABS() on COUNT(1).
Is the csp_http table very large?

5

hi, jduong,about 600 millions,