Hi David, A few questions: How are you querying Drill? The queries you are sending in the emails do not look syntactically correct to begin with. I'm referring to the use of quotation marks and backticks. These make a HUGE difference with Drill so it is really important to use the correct ones. It is therefore difficult to offer suggestions if we are seeing queries like that.
Can you query other schemata in Dremio? Do they work? Are you first executing a USE query? If so have you tried removing the schema and table names? IE: USE dremiodemo Then SELECT * FROM `demo` Is your Dremio installation running on the same box as Drill? Out of mere curiosity, what is the use case for querying Dremio via Drill? Best, -- C > On Feb 14, 2020, at 11:19 AM, David Du <[email protected]> wrote: > > Anybody has any ideas about this issue? > > Thanks > > On Thu, Feb 13, 2020 at 10:05 AM David Du <[email protected]> wrote: > >> I used this command: >> >> *select* * *from* dremiodemo.demo.weather; >> >> >> since dremiodemo.demo is the schema name listed from show >> databases command. >> >> >> But why the drill generated a different query as?: >> >> sql SELECT * >> >> FROM "DREMIO"."Demo"."weather" >> >> plugin dremiodemo >> >> >> Why "DREMIO" is added instead of "dremiodemo"? >> >> >> Thanks >> >> >> >> >> >> >> On Thu, Feb 13, 2020 at 9:30 AM 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
