I'm going to log this as a Jira bug. I figured out a work around, but I'm
pretty sure this is a bug and not intended behavior and performance is terrible
with the work around.
For Sybase (and probably MS SQL Server as well)..
A. The following plugin only allows access to tables created on the tempdb
database (in url). I'm pretty sure this is intended which is ok and the SQL
runs fine..
{
"type": "jdbc",
"driver": "com.sybase.jdbc4.jdbc.SybDriver",
"url": "jdbc:sybase:Tds:my_server_address:4100/tempdb",
"username": "my_login",
"password": "my_password",
"enabled": true
}
0: jdbc:drill:zk=local> select * from SYBASE.tempdb.guest.phone_book;
+-------------+------------+---------------+
| first_name | last_name | phone_number |
+-------------+------------+---------------+
| Bob | Marley | 555-555-5555 |
| Mary | Jane | 111-111-1111 |
| Bat | Man | 911-911-9999 |
+-------------+------------+---------------+
3 rows selected (5.367 seconds)
B. However, if you change the plug in by omitting the database then you lose
complete access to this tempdb database. In fact the only database which you
can pull tables is the user's default database which is the database a user
gets directed to if no database is specified in the url connection string.
{
"type": "jdbc",
"driver": "com.sybase.jdbc4.jdbc.SybDriver",
"url": "jdbc:sybase:Tds:my_server_address:4100",
"username": "my_login",
"password": "my_password",
"enabled": true
}
0: jdbc:drill:zk=local> select * from SYBASE.tempdb.guest.phone_book;
Sep 26, 2016 2:47:37 PM org.apache.calcite.sql.validate.SqlValidatorException
<init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
'SYBASE.temp
db.guest.phone_book' not found
Sep 26, 2016 2:47:37 PM org.apache.calcite.runtime.CalciteException <init>
0: jdbc:drill:zk=local> select * from SYBASE.dev.dbo.connection_stats limit 10;
+--------------------------+--------+------------+--------------+
| run_time | pmses | bondcalcs | connections |
+--------------------------+--------+------------+--------------+
| 2003-07-31 09:01:47.323 | 132 | 35 | 938 |
| 2003-07-31 09:11:48.02 | 133 | 125 | 1035 |
| 2003-07-31 09:21:48.62 | 127 | 111 | 1004 |
| 2003-07-31 09:31:49.253 | 123 | 78 | 970 |
| 2003-07-31 09:41:49.91 | 117 | 78 | 966 |
| 2003-07-31 09:51:50.56 | 113 | 78 | 976 |
| 2003-07-31 10:01:51.363 | 151 | 78 | 1070 |
| 2003-07-31 10:11:51.996 | 133 | 79 | 1055 |
| 2003-07-31 10:21:52.756 | 123 | 78 | 1035 |
| 2003-07-31 10:31:53.553 | 114 | 79 | 1029 |
+--------------------------+--------+------------+--------------+
10 rows selected (12.111 seconds)
C. Other commands on tempdb appear to work fine up to a certain point..
0: jdbc:drill:zk=local> use SYBASE;
+-------+------------------------------------+
| ok | summary |
+-------+------------------------------------+
| true | Default schema changed to [SYBASE] |
+-------+------------------------------------+
1 row selected (2.32 seconds)
0: jdbc:drill:zk=local> use tempdb;
+-------+-------------------------------------------+
| ok | summary |
+-------+-------------------------------------------+
| true | Default schema changed to [SYBASE.tempdb] |
+-------+-------------------------------------------+
1 row selected (25.538 seconds)
0: jdbc:drill:zk=local> use guest;
Error: VALIDATION ERROR: Schema [guest] is not valid with respect to either root
schema or current default schema.
Current default schema: SYBASE.tempdb
[Error Id: c3bb3a0e-44ec-4875-851d-2f2657c624a9 on xxx.xx.xxxx.com:31
010] (state=,code=0)
0: jdbc:drill:zk=local> show tables;
+---------------+------------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+---------------+------------------------------+
| SYBASE.tempdb | phone_book |
| SYBASE.tempdb | rep_all_config_param |
| SYBASE.tempdb | sysalternates |
| SYBASE.tempdb | sysattributes |
| SYBASE.tempdb | syscolumns |
| SYBASE.tempdb | syscomments |
| SYBASE.tempdb | sysconstraints |
| SYBASE.tempdb | sysdepends |
Etc.. etc..
D. The only work around is to create 80 different plugins on the same server
with 80 different default databases. This also makes SQL joins unmanageable
when the SQL engine is trying to join two tables without knowing they are on
the same server.
It should only take one second to join two tables on two different databases
within the same plugin, but it takes 5 minutes in some cases to pull data from
two different plugins (one for each database) and join them together.
David Lee
Vice President | BlackRock
Phone: +1.415.670.2744 | Mobile: +1.415.706.6874
-----Original Message-----
From: Lee, David
Sent: Thursday, September 22, 2016 1:59 PM
To: user@drill.apache.org
Subject: RE: Drill Support for Sybase Plugin
As I suspected.. It looks like there is some sort of conflict between SQL
String Validator from Calcite vs the SQL Executor over the missing
"owner.objectname" prefix for Sybase tables.
I turned on verbose error messaging..
"a drill is a terrible thing to waste"
0: jdbc:drill:zk=local> SET `exec.errors.verbose` = true . . . . . . . . . . .
> ;
+-------+-------------------------------+
| ok | summary |
+-------+-------------------------------+
| true | exec.errors.verbose updated. |
+-------+-------------------------------+
0: jdbc:drill:zk=local> select * from SYBASE.tempdb.phone_book;
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the
SQL query.
sql SELECT *
FROM "tempdb"."phone_book"
plugin SYBASE
Fragment 0:0
[Error Id: d8c05f9e-8a4e-48e5-925c-3ca6777da70c on xxxx.xx.xxxxx.com:31010]
(java.sql.SQLException) "tempdb"."phone_book" not found. Specify
owner.objectname or use sp_help to check whether the object exists (sp_help may
produce lotsof output).
I can reproduce this error messasge in SYBASE's native ISQL running:
select * from tempdb.phone_book
>[Error] Script lines: 29-29 ------------------------
tempdb.phone_book not found. Specify owner.objectname or use sp_help to check
whether the object exists (sp_help may produce lots of output).
Msg: 208, Level: 16, State: 1
Server: SYB1T, Line: 1
The valid syntaxes are:
select * from phone_book
select * from tempdb..phone_book
select * from tempdb.guest.phone_book
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.
© 2016 BlackRock, Inc. All rights reserved.
