Apache Drill Schema Support for SQL Server

Question

I want to know whether Apache Drill supports only dbo schema?? or will it support all type of schema?

I am running my system in window 8 and with latest version of Drill(1.5) with embedded mode.

I am trying to search with same storage plugin.

My Storage Plugin(for SQLServer):

{
  "type" : "jdbc",
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "url" : "jdbc:sqlserver://<servername>;databaseName=<databasename>",
  "username" : "<username>",
  "password" : "<****>",
  "enabled" : true
}

This Plugin has dbo & core schema (both have same type, no special permission).Its work for dbo schema where core schema is not working.

DBO Query:

select * from SqlServer.dbo.Attribute; //Its working.

Core Query:

select * from SqlServer.core.Users //Its not working

My question is whether Drill Supports only dbo schemna or all type of schema?

enable only sql server plugin & fire show databases; from Drill shell. What is the output? — Dev
ITs showing all the databases in that server. I Think dbo is by default schema of sql. show it showing all the table but while query through core (user created) schema, its showing table not found. — Sanjiv
Yes dbo is default schema in SQL server. Does SqlServer.core is shown with show databases; command? — Dev
Ya its showing... but apart from dbo schema i have other schema also.. those schemas are not showing (i.e core, D21) are not showing.. only dbo is showing — Sanjiv
I am confused SqlServer.core is shown or not? I know SqlServer.dbo is shown. — Dev

Sanjiv Sanjiv · Accepted Answer · 2016-03-21T12:50:55

select * from <StoragePluginName>.<databaseName>.<schemaName>.<tableName>;

Ex:

seleect * from SqlServer.Test.core.Category;

This query will work for all type of user created schemas. But for dbo(default) schema Databases name is not required. If you write database name while query through dbo schema, it will through error.

--> But This not the good solution. Because every time we have check for schema (default or user created schema). If it has dbo (default schema) then database name is not required in query. And If it has core (user created schema) schema then we have to give database name after Storage Plugin.

Apache Drill Schema Support for SQL Server

1 Answers