drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lee, David" <David....@blackrock.com>
Subject RE: Problems querying with SQL server due to database name containing a period (.)
Date Mon, 12 Jun 2017 18:12:59 GMT
I have the same problem with Sybase.. "dbo" is not recognized during SQL execution, but
somehow "dbo" is recognized when validating SQL.

https://issues.apache.org/jira/browse/DRILL-5427



-----Original Message-----
From: Erick Reyes [mailto:EReyes@trackman.com] 
Sent: Monday, June 12, 2017 9:45 AM
To: user@drill.apache.org
Subject: RE: Problems querying with SQL server due to database name containing a period (.)

Charles,

Thanks for the quick reply, the table does not contain a period, the database name does. This
is what my connection looks like 

{
  "type": "jdbc",
  "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "url": "jdbc:sqlserver://{conectionstring};datebaseName=TrackMan.Baseball",
  "username": "****",
  "password": "****",
  "enabled": true
}

This is what my query looks like 

select * from tsql.dbo.Game

Best,

Erick Reyes 

-----Original Message-----
From: Charles Givre [mailto:cgivre@gmail.com]
Sent: Monday, June 12, 2017 12:40 PM
To: user <user@drill.apache.org>
Subject: Re: Problems querying with SQL server due to database name containing a period (.)

Erick,
Are you putting backticks around the table?  IE:

SELECT <fields>
FROM sql.database.`TrackMan.Baseball`



On Mon, Jun 12, 2017 at 12:37 PM, Erick Reyes <EReyes@trackman.com> wrote:

> Hello,
>
> I am a data engineer for a small start-up in the sport technology 
> industry. We have SQL and NoSQL databases and want to leverage Apache 
> Drill as a way to query across our DBs. I was able to set up our 
> MongoDB and SQL server instances without major issues but querying one 
> of our SQL database with the name TrackMan.Baseball is giving us 
> issues. I believe this is due to the name containing a period.  Having 
> a period as part of the database name was a horrible design decision 
> made by us several years ago and we are now living with the 
> consequences, is there any way I can work around this issue? Below is 
> the error message I get when trying to query this database, databases 
> under the same connection string with a different name can be queried without any problems.
Is there a solution for me?
>
> Best,
>
> Erick S. Reyes
> Data Platforms TrackMan Baseball
> (312) 647-5373
>
> Query Failed: An Error Occurred
> org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR:
> From line 1, column 15 to line 1, column 18: Table 'tsql.dbo.Game' not 
> found SQL Query null [Error Id: 44aa1094-3bbc-4d9a-bb01-d021afa4fb6a
> on Stamford-SQL01.ISG.local:31010]
> (org.apache.calcite.runtime.CalciteContextException)
> From line 1, column 15 to line 1, column 18: Table 'tsql.dbo.Game' not 
> found sun.reflect.GeneratedConstructorAccessor97.newInstance():-1
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
> java.lang.reflect.Constructor.newInstance():423
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405
> org.apache.calcite.sql.SqlUtil.newContextException():765
> org.apache.calcite.sql.SqlUtil.newContextException():753
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():
> 3974
> org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106
> org.apache.calcite.sql.validate.AbstractNamespace.validate():86
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8
> 83
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2806
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2791
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3014
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
> org.apache.calcite.sql.validate.AbstractNamespace.validate():86
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8
> 83
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869
> org.apache.calcite.sql.SqlSelect.validate():210 org.apache.calcite.sql.
> validate.SqlValidatorImpl.validateScopedExpression():843
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate():557
> org.apache.drill.exec.planner.sql.SqlConverter.validate():169
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateN
> ode():606
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateA
> ndConvert():192
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan()
> :164
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():131
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():79
> org.apache.drill.exec.work.foreman.Foreman.runSQL():1050
> org.apache.drill.exec.work.foreman.Foreman.run():281 java.util.concurrent.
> ThreadPoolExecutor.runWorker():1142 java.util.concurrent.
> ThreadPoolExecutor$Worker.run():617 java.lang.Thread.run():748 Caused 
> By
> (org.apache.calcite.sql.validate.SqlValidatorException) Table 
> 'tsql.dbo.Game' not found
> sun.reflect.GeneratedConstructorAccessor96.newInstance():-1
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
> java.lang.reflect.Constructor.newInstance():423
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405
> org.apache.calcite.runtime.Resources$ExInst.ex():514
> org.apache.calcite.sql.SqlUtil.newContextException():765
> org.apache.calcite.sql.SqlUtil.newContextException():753
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():
> 3974
> org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106
> org.apache.calcite.sql.validate.AbstractNamespace.validate():86
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8
> 83
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2806
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2791
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3014
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
> org.apache.calcite.sql.validate.AbstractNamespace.validate():86
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():8
> 83
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():869
> org.apache.calcite.sql.SqlSelect.validate():210 org.apache.calcite.sql.
> validate.SqlValidatorImpl.validateScopedExpression():843
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate():557
> org.apache.drill.exec.planner.sql.SqlConverter.validate():169
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateN
> ode():606
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateA
> ndConvert():192
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan()
> :164
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan():131
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():79
> org.apache.drill.exec.work.foreman.Foreman.runSQL():1050
> org.apache.drill.exec.work.foreman.Foreman.run():281 java.util.concurrent.
> ThreadPoolExecutor.runWorker():1142 java.util.concurrent.
> ThreadPoolExecutor$Worker.run():617 java.lang.Thread.run():748
>
>


This message may contain information that is confidential or privileged. If you are not the
intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers
for further information.  Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy
for more information about BlackRock’s Privacy Policy.

For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

© 2017 BlackRock, Inc. All rights reserved.
Mime
View raw message