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 <[email protected]> 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 <[email protected] <mailto:[email protected]> >> <mailto:[email protected]>> 于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
