Drill is always a level when using Drill as the driver. I found it does not
have a problem when querying the local file system like the below example.
But probably an issue for JDBC connection.
https://drill.apache.org/docs/using-jdbc-with-squirrel-on-windows/
Another issue I found is when querying through Squirrel,
drill automatically changes the query to
SELECT *
FROM "SCHEMA"."OAUSER"."AG_MANIFEST"
FETCH NEXT 100 ROWS ONLY;
However, 'FETCH NEXT 100 ROWS ONLY' is not a query that is accepted by all
databases. (details in logs below) And I can't find a way to remove it.
BTW, I don't think the SQL is changed by Squirrel SQL, because I tried with
a different JDBC driver that can query with no problem.
Thank you for your attention and help!
[image: image.png]
Logs:
Sql: SELECT *
FROM "SCHEMA"."OAUSER"."AG_MANIFEST"
FETCH NEXT 100 ROWS ONLY
[Error Id: 65f9140d-fd03-4b9f-aa84-8595983d08fa ]
at
org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:688)
at
org.apache.drill.exec.store.jdbc.JdbcBatchReader.open(JdbcBatchReader.java:155)
at
org.apache.drill.exec.physical.impl.scan.framework.ManagedScanFramework.open(ManagedScanFramework.java:211)
at
org.apache.drill.exec.physical.impl.scan.framework.ShimBatchReader.open(ShimBatchReader.java:76)
at
org.apache.drill.exec.physical.impl.scan.ReaderState.open(ReaderState.java:224)
at
org.apache.drill.exec.physical.impl.scan.ScanOperatorExec.nextAction(ScanOperatorExec.java:286)
at
org.apache.drill.exec.physical.impl.scan.ScanOperatorExec.next(ScanOperatorExec.java:242)
at
org.apache.drill.exec.physical.impl.protocol.OperatorDriver.doNext(OperatorDriver.java:201)
at
org.apache.drill.exec.physical.impl.protocol.OperatorDriver.start(OperatorDriver.java:179)
at
org.apache.drill.exec.physical.impl.protocol.OperatorDriver.next(OperatorDriver.java:129)
at
org.apache.drill.exec.physical.impl.protocol.OperatorRecordBatch.next(OperatorRecordBatch.java:149)
at
org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:109)
at
org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:101)
at
org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:59)
at
org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:93)
at
org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:160)
at
org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:103)
at
org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:81)
at
org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:93)
at
org.apache.drill.exec.work.fragment.FragmentExecutor.lambda$run$0(FragmentExecutor.java:321)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1762)
at
org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:310)
at
org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Caused by: java.sql.SQLSyntaxErrorException: [BIOVIA][BIOVIA Discoverant
JDBC Driver][Data Access SQL Engine]*Syntax error in SQL statement. syntax
error line 3 *at or after token <NEXT>.[10179]
at com.biovia.jdbc.oabase.oaa0.b(Unknown Source)
at com.biovia.jdbc.oabase.oaa0.a(Unknown Source)
at com.biovia.jdbc.oabase.oaa_.b(Unknown Source)
at com.biovia.jdbc.oabase.oaa_.a(Unknown Source)
at com.biovia.openaccess.ssp.oaar.a(Unknown Source)
at com.biovia.openaccess.ssp.oaf.a(Unknown Source)
at com.biovia.openaccess.ssp.oaf.a(Unknown Source)
at com.biovia.openaccess.ssp.oaf.f(Unknown Source)
at com.biovia.openaccess.ssp.oaf.e(Unknown Source)
at com.biovia.openaccess.ssp.oaf.b(Unknown Source)
at com.biovia.openaccess.ctxt.stmt.oac.a(Unknown Source)
at com.biovia.jdbc.openaccess.oad.d(Unknown Source)
at com.biovia.jdbc.oabase.oabq.a(Unknown Source)
at com.biovia.jdbc.oabase.oab4.a(Unknown Source)
at com.biovia.jdbc.oabase.oac5.b(Unknown Source)
at com.biovia.jdbc.oabase.oab4.a(Unknown Source)
at com.biovia.jdbc.oabase.oar.a(Unknown Source)
at com.biovia.jdbc.oabase.BaseConnection.prepareStatement(Unknown Source)
at com.biovia.jdbc.oabase.BaseConnection.prepareStatement(Unknown Source)
at com.biovia.jdbc.oabase.BaseConnection.prepareStatement(Unknown Source)
at
com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at
com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at
org.apache.drill.exec.store.jdbc.JdbcBatchReader.open(JdbcBatchReader.java:135)
... 26 common frames omitted
Best,
Yu
James Turton <ja...@somecomputer.xyz> 于2023年12月6日周三 23:55写道：
> There no doubt is but I think that already requires one to write some
> code, at which point I'd personally rather try to work closer to what I
> see as the source of the trouble.
>
> On 2023/12/07 08:04, Charles Givre wrote:
> > James,
> > I wonder if there would be a way to simply ignore or remove the leading
> DRILL? Of course, if someone names a plugin "drill" that would then cause
> problems.
> > --C
> >
> >> On Dec 7, 2023, at 00:41, James Turton <dz...@apache.org> wrote:
> >>
> >> This is what I consider to be a misfeature in Drill. At some point it
> was decided that rooting the INFORMATION_SCHEMA hierarchy in a fictitious
> catalog named DRILL would increase Drill's compatibility with BI tools.
> What breaks as a result is the correspondence between INFORMATION_SCHEMA
> and schema paths in queries. Clients like Squirrel generate queries like
> >>
> >> SELECT * FROM DRILL.dfs.tmp.a_table
> >>
> >> which go on to fail because of the leading 'DRILL.'.
> >>
> >> For now you can write SELECTs by hand to view table content. I have got
> a Jira open to adda toggle tro Drill that switches off the DRILL catalog in
> INFORMATION_SCHEMA but I haven't started development yet.
> >>
> >> On 2023/12/05 23:58, yu sun wrote:
> >>> Hi James,
> >>> Thank you so much for your help!
> >>> It works! I query successfully in drill bash. But in Squirrel SQL. I
> cannot get the table content show up due to the following error:
> >>> 2023-12-05 10:20:24,573 [1a90a426-e547-8df5-db2a-be595f708cb9:foreman]
> INFO o.a.d.e.p.s.conversion.SqlConverter - User Error Occurred: From line
> 1, column 19 to line 1, column 60: Object 'DRILL' not found (From line 1,
> column 19 to line 1, column 60: *Object 'DRILL' not found*)
> >>> org.apache.drill.common.exceptions.UserException: VALIDATION ERROR:
> From line 1, column 19 to line 1, column 60: Object 'DRILL' not found
> >>> [Error Id: d10ed29c-6559-4805-8405-2cdfb0db3350 ]
> >>> at
> org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:688)
> >>> at
> org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:220)
> >>> at
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:662)
> >>> at
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:198)
> >>> at
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:172)
> >>> at
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:298)
> >>> at
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:179)
> >>> at
> org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:129)
> >>> at
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:94)
> >>> at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:594)
> >>> at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:274)
> >>> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> >>> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> >>> at java.lang.Thread.run(Thread.java:750)
> >>> Caused by: org.apache.calcite.runtime.CalciteContextException: From
> line 1, column 19 to line 1, column 60: Object 'DRILL' not found
> >>> at sun.reflect.GeneratedConstructorAccessor110.newInstance(Unknown
> Source)
> >>> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> >>> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> >>> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
> >>> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)
> >>> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5464)
> >>> at
> org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:183)
> >>> at
> org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:188)
> >>> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3429)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3408)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3766)
> >>> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> >>> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> >>> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:282)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1081)
> >>> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787)
> >>> at
> org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:212)
> >>> ... 12 common frames omitted
> >>> Caused by: org.apache.calcite.sql.validate.SqlValidatorException:
> Object 'DRILL' not found
> >>> at sun.reflect.GeneratedConstructorAccessor109.newInstance(Unknown
> Source)
> >>> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> >>> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> >>> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
> >>> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)
> >>> ... 31 common frames omitted
> >>> The database structure looks like that. And when I click on one table,
> the query log show:
> >>>
> {"schema":"jdbc.schema.oauser","start":1701796824382,"finish":1701796824574,"outcome":"FAILED","remoteAddress":"
> 127.0.0.1:59371 <http://127.0.0.1:59371
> <http://127.0.0.1:59371/>>","id":"1a90a426-e547-8df5-db2a-be595f708cb9","query":"select
> tbl.* from `DRILL`.`jdbc.schema.oauser`.`AG_MANIFEST` tbl","user":"t1"}
> >>> image.png
> >>> Do you know how to configure 'DRILL' so it can be recognize in
> Squirrel SQL?
> >>> Thank you!
> >>> Yu
> >>> James Turton <dz...@apache.org <mailto:dz...@apache.org> <mailto:
> dz...@apache.org>> 于2023年12月1 日周五 23:16写道：
> >>> __
> >>> jdbc:drill:drillbit=<hostname>:31010
> >>> The above should work for connecting to embedded Drill running on
> a remote host. If it doesn't then I'd test whether Drill has bound to port
> 31010 on a reachable IP address and whether any firewall is interfering.
> >>> Related:
> >>>
> https://drill.apache.org/docs/ports-and-bind-addresses-used-by-drill/ <
> https://drill.apache.org/docs/ports-and-bind-addresses-used-by-drill/>
> >>> On 2023/12/01 20:38, yu sun wrote:
> >>>> Hi there,
> >>>>
> >>>> I'm trying to replace the previous JDBC driver with Drill.
> However, I had a
> >>>> hard time configuring Squirrel SQL after going through all the
> tutorials.
> >>>>
> >>>> My previous alias URL has the format below:
> >>>> jdbc:dataaccess://<database host>:<database
> port>;ServerDataSource=<data
> >>>> source name>
> >>>>
> >>>> I want to use the embedded mode with Drill. The below URL works
> in Squirrel
> >>>> SQL.
> >>>> jdbc:drill:drillbit=localhost:31010
> >>>> But how can I visit the database in the specific host and port,
> rather than
> >>>> my localhost?
> >>>> Where shall I put the information of <database host>:<database
> >>>> port>;ServerDataSource=<data source name>?
> >>>>
> >>>> I tried to use jdbc:drill:drillbit=<database host>:<database
> >>>> port>;ServerDataSource=<data source name> but it won't work.
> >>>>
> >>>> Thank you so much for your help!
> >>>> Yu Sun
> >
>
>
