Apache Drill 1.2 and SQL Server JDBC

0
votes

Apache Drill 1.2 adds the exciting feature of including JDBC relational sources in your query. I would like to include Microsoft SQL Server.

So, following the docs I copied the SQL Server jar sqldjbc42.jar (the most recent MS JDBC driver) into the proper 3rd party directory.

I successfully added the storage.

The configuration is:

{
  "type": "jdbc",
  "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "url": "jdbc:sqlserver://myservername",
  "username": "myusername",
  "password": "mypassword",
  "enabled": true
}

as "mysqlserverstorage"

However, running queries fails. I've tried:

select * from mysqlserverstorage.databasename.schemaname.tablename

(of course I've use real existing tables instead of the placeholders here)

Error:

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 2, column 6 to line 2, column 17: Table 'mysqlserverstorage.databasename.schemaname.tablename' not found [Error Id: f5b68a73-973f-4292-bdbf-54c2b6d5d21e on PC1234:31010]

and

select * from mysqlserverstorage.`databasename.schemaname.tablename`

Error:

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: Exception while reading tables [Error Id: 213772b8-0bc7-4426-93d5-d9fcdd60ace8 on PC1234:31010]

Has anyone had success in configuring and using this new feature?

2
sql-serverjdbcapache-drill
can you try select * from mysqlserverstorage.databasename.tablename - Dev
Does not work either, different error: org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM "Databasename"."Tablename" plugin sqldwh Fragment 0:0 [Error Id: 88aaa505-48ae-4ccc-9ff3-ceda878c2734 on PC1234:31010] - Alexander
don't know much about Microsoft SQL server. I tried for MySQL. Check it may help : stackoverflow.com/questions/33232644/… - Dev
I haven't tried it, but if you want to experiment this Drill 1.3 preview, it fixes several JDBC storage plugin issues: people.apache.org/~jacques/apache-drill-1.3.0.rc0. Tip: re: mssql 3-part naming, use one storage plugin config connection for just one mssql database. - catpaws
The success really wasn't what I thought--he didn't use the plugin. - catpaws

2 Answers

1
votes

Success has been reported using a storage plugin configuration, such as 

{
  type: "jdbc",
  enabled: true,
  driver: "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  url:"jdbc:sqlserver://172.31.36.88:1433;databaseName=msdb",
  username:"root",
  password:"<password>"
}

on pre-release Drill 1.3 and using sqljdbc41.4.2.6420.100.jar.

1
votes

Construct you query as,

select * from storagename.schemaname.tablename

This will work with sqljdbc4.X as it works for me.