Re: How to config Drill in Squirrel SQL with the host and port where the database is

Wed, 06 Dec 2023 21:41:26 -0800

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>","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]>> 于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

Reply via email to