Hi David, Thanks much for the log files. I take it that one log is from Drill, the other from Dremio?
What I see is that Drill sends the query off to Dremio via JDBC. This tells us that most of your configuration is correct: Drill did establish a connection to Dremio. It is just the query that has an issue. The Dremio logs (those with "com.dremio") show that Dremio plans the query using its version of Calcite. Dremio then fails because it does not recognize the "namespace". The code in Drill which raises the exception includes the translated SQL query; the one that Drill tries to send to Dremio via JDBC. The translated query seems to be the one from your e-mail: SELECT * FROM "DREMIO"."Demo"."weather" Your earlier e-mail included your config parameters, including the connection string: "url": "jdbc:dremio:direct=localhost:41010;schema=Demo", Dremio's explanation of their connection string is at [1]. The syntax is: jdbc:dremio:direct=<DREMIO_COORDINATOR>:31010[;schema=<OPTIONAL_SCHMEMA>] The above says that Drill is connecting (via the network) to a Dremo server running on he same host. All good. The part that still has me concerned is the "schema=Demo" part. Unfortunately, the Dremio page does not explain the "schema" parameter, but we can guess that it gives the default namespace for resolving tables. That is, if the default Schema is "Demo", then I'd expect the valid SQL to be: SELECT * FROM weather Let's try an experiment. From Dremio's UI, try selecting a schema (perhaps with USE?) and run the full query. Then try running the simpler query. Which worked? If only the simpler one worked, try changing your config url to omit the schema. I don't yet understand where the "DREMIO" part of the table name is coming from. I rather suspect that is also not needed. To test, try all three various using the Dremio UI: DREMIO.Demo.weather, Demo.weather and just weather Hard to know if this is the problem just from reading logs, but it seems worth a shot. Once we see what happens, we can figure out what to try next. Thanks, - Paul [1] https://docs.dremio.com/drivers/dremio-jdbc-driver.html On Thursday, February 13, 2020, 6:31:08 AM PST, David Du <[email protected]> wrote: Thanks for your help, my command is: apache drill> *select* * *from* dremiodemo.demo.weather; this gave me error from console: Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM "DREMIO"."Demo"."weather" plugin dremiodemo Fragment 0:0 [Error Id: dd01f1ff-c7c8-4f36-996f-5f4ed3839c43 on 172.24.30.68:31010] (state=,code=0) But the log file is: tail -f sqlline.log 2020-02-13 09:23:48,780 [main] WARN o.a.drill.exec.metrics.DrillMetrics - Removing old metric since name matched newly registered metric. Metric name: drill.allocator.root.used 2020-02-13 09:23:48,780 [main] WARN o.a.drill.exec.metrics.DrillMetrics - Removing old metric since name matched newly registered metric. Metric name: drill.allocator.root.peak 2020-02-13 09:23:48,913 [main] INFO o.a.drill.exec.client.DrillClient - Foreman drillbit is 172.24.30.68 2020-02-13 09:23:48,913 [main] INFO o.a.drill.exec.client.DrillClient - Successfully connected to server 172.24.30.68:31010 2020-02-13 09:24:02,371 [21baa3fc-c178-4227-5d3d-00bf55c59097:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id 21baa3fc-c178-4227-5d3d-00bf55c59097 issued by anonymous: show databases 2020-02-13 09:24:04,145 [21baa3fc-c178-4227-5d3d-00bf55c59097:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 21baa3fc-c178-4227-5d3d-00bf55c59097:0:0: State change requested AWAITING_ALLOCATION --> RUNNING 2020-02-13 09:24:04,156 [21baa3fc-c178-4227-5d3d-00bf55c59097:frag:0:0] INFO o.a.d.e.w.f.FragmentStatusReporter - 21baa3fc-c178-4227-5d3d-00bf55c59097:0:0: State to report: RUNNING 2020-02-13 09:24:04,401 [21baa3fc-c178-4227-5d3d-00bf55c59097:frag:0:0] INFO o.a.d.e.c.ClassCompilerSelector - Java compiler policy: DEFAULT, Debug option: true 2020-02-13 09:24:04,571 [21baa3fc-c178-4227-5d3d-00bf55c59097:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 21baa3fc-c178-4227-5d3d-00bf55c59097:0:0: State change requested RUNNING --> FINISHED 2020-02-13 09:24:04,573 [21baa3fc-c178-4227-5d3d-00bf55c59097:frag:0:0] INFO o.a.d.e.w.f.FragmentStatusReporter - 21baa3fc-c178-4227-5d3d-00bf55c59097:0:0: State to report: FINISHED 2020-02-13 09:26:39,805 [21baa35f-ef83-15fe-f40b-8ff0415620f2:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id 21baa35f-ef83-15fe-f40b-8ff0415620f2 issued by anonymous: select * from dremiodemo.demo.weather 2020-02-13 09:26:40,308 [21baa35f-ef83-15fe-f40b-8ff0415620f2:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 21baa35f-ef83-15fe-f40b-8ff0415620f2:0:0: State change requested AWAITING_ALLOCATION --> RUNNING 2020-02-13 09:26:40,308 [21baa35f-ef83-15fe-f40b-8ff0415620f2:frag:0:0] INFO o.a.d.e.w.f.FragmentStatusReporter - 21baa35f-ef83-15fe-f40b-8ff0415620f2:0:0: State to report: RUNNING 2020-02-13 09:26:40,472 [21baa35f-ef83-15fe-f40b-8ff0415620f2:frag:0:0] INFO o.a.d.e.store.jdbc.JdbcRecordReader - User Error Occurred: The JDBC storage plugin failed while trying setup the SQL query. (VALIDATION ERROR: Table 'DREMIO.Demo.weather' not found SQL Query SELECT * FROM "DREMIO"."Demo"."weather" startLine 2 startColumn 6 endLine 2 endColumn 13 [Error Id: b7ae16cc-80dc-4950-b7e1-7ccbd9cad7e8 on 1672851h-t2349.noblis.org:41010] (org.apache.calcite.runtime.CalciteContextException) From line 2, column 6 to line 2, column 13: Table 'DREMIO.Demo.weather' not found sun.reflect.GeneratedConstructorAccessor149.newInstance():-1 sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 java.lang.reflect.Constructor.newInstance():423 org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463 org.apache.calcite.sql.SqlUtil.newContextException():803 org.apache.calcite.sql.SqlUtil.newContextException():788 org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4470 org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl():104 org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():120 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2971 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2956 org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3197 org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924 org.apache.calcite.sql.SqlSelect.validate():226 org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():899 org.apache.calcite.sql.validate.SqlValidatorImpl.validate():609 com.dremio.exec.planner.sql.SqlConverter.validate():229 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():184 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():173 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():169 com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43 com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():70 com.dremio.exec.work.foreman.AttemptManager.plan():415 com.dremio.exec.work.foreman.AttemptManager.lambda$run$0():324 com.dremio.service.commandpool.CommandWrapper.run():62 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Table 'DREMIO.Demo.weather' not found sun.reflect.GeneratedConstructorAccessor148.newInstance():-1 sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 java.lang.reflect.Constructor.newInstance():423 org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463 org.apache.calcite.runtime.Resources$ExInst.ex():572 org.apache.calcite.sql.SqlUtil.newContextException():803 org.apache.calcite.sql.SqlUtil.newContextException():788 org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4470 org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl():104 org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():120 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2971 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2956 org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3197 org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924 org.apache.calcite.sql.SqlSelect.validate():226 org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():899 org.apache.calcite.sql.validate.SqlValidatorImpl.validate():609 com.dremio.exec.planner.sql.SqlConverter.validate():229 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():184 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():173 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():169 com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43 com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():70 com.dremio.exec.work.foreman.AttemptManager.plan():415 com.dremio.exec.work.foreman.AttemptManager.lambda$run$0():324 com.dremio.service.commandpool.CommandWrapper.run():62 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 On Thu, Feb 13, 2020 at 12:16 AM Paul Rogers <[email protected]> wrote: > The next step would be to take a look at the log file. Anything > interesting there? Maybe an error or a stack trace? > > Thanks, > - Paul > > > > On Wednesday, February 12, 2020, 8:12:19 PM PST, Paul Rogers > <[email protected]> wrote: > > Going to take a wild guess. Since you configured the "Demo" schema in the > JDBC driver, you may not need it in the Drill query. Maybe try: > > SELECT * FROM dremiodemo.weather; > > Thanks, > - Paul > > > > On Wednesday, February 12, 2020, 10:34:35 AM PST, David Du < > [email protected]> wrote: > > My command is: > > *select* * *from* dremiodemo.demo.weather; > > > there is no quotes, but for some reason, the error message returned with > quotes > > > Thanks for your help! > > > -- ******************************************************** DISCLAIMER: This e-mail, including any attached files, is confidential, may be legally privileged, and is solely for the intended recipient(s). If you received this e-mail in error, please destroy it and notify us immediately by reply e-mail. Any unauthorized use, dissemination, disclosure, copying or printing is strictly prohibited. - BCMC, LLC
