Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Duplicate
-
1.8.0
-
None
-
linux, sybase ase, sybase iq, windows
Description
This may also be a problem with Microsoft SQL Server which uses the same SQL Syntax.
I am unable to setup a single JDBC plugin which allows me to query tables on different databases on the server.
I can setup multiple JDBC plugins for each database on the server and join data across multiple JDBC connections, but this is extremely inefficient and SQL queries
just hang.
Test Case: Create two tables on two different databases and write a single SQL statement to join them together. Try to replicate the results in Apache Drill.
A. Temp tables in Sybase:
use tempdb
go
create table phone_book
(
first_name varchar(10),
last_name varchar(20),
phone_number varchar(12)
)
go
insert phone_book values ('Bob','Marley','555-555-5555')
insert phone_book values ('Mary','Jane','111-111-1111')
insert phone_book values ('Bat','Man','911-911-9999')
go
use tempdb_adhoc
go
create table cities
(
first_name varchar(10),
last_name varchar(20),
city varchar(20)
)
go
insert cities values ('Bob','Marley','San Francisco')
insert cities values ('Mary','Jane','New York')
insert cities values ('Bat','Man','Gotham')
go
select a.first_name, a.last_name, a.phone_number, b.city
from tempdb.guest.phone_book a
join tempdb_adhoc.guest.cities b
on b.first_name = a.first_name
and b.last_name = a.last_name
go
Returns Back in SYBASE ISQL:
first_name last_name phone_number city
---------- -------------------- ------------ --------------------
Bob Marley 555-555-5555 San Francisco
Mary Jane 111-111-1111 New York
Bat Man 911-911-9999 Gotham
B. Drill JDBC Plugin Setups:
DEV:
{
"type": "jdbc",
"driver": "com.sybase.jdbc4.jdbc.SybDriver",
"url": "jdbc:sybase:Tds:my_server:4100",
"username": "my_login",
"password": "my_password",
"enabled": true
}
DEV_TEMPDB:
{
"type": "jdbc",
"driver": "com.sybase.jdbc4.jdbc.SybDriver",
"url": "jdbc:sybase:Tds:my_server:4100/tempdb",
"username": "my_login",
"password": "my_password",
"enabled": true
}
DEV_TEMPDB_ADHOC:
{
"type": "jdbc",
"driver": "com.sybase.jdbc4.jdbc.SybDriver",
"url": "jdbc:sybase:Tds:my_server:4100/tempdb_adhoc",
"username": "my_login",
"password": "my_password",
"enabled": true
}
C. Examples of Drill Statements which work and don't work.
1. Returns back redundant schemas for each JDBC plugin:
0: jdbc:drill:zk=local> show schemas;
--------------------------------------
| SCHEMA_NAME |
--------------------------------------
| DEV.tempdb |
| DEV.tempdb_adhoc |
| DEV_TEMPDB.tempdb |
| DEV_TEMPDB.tempdb_adhoc |
| DEV_TEMPDB_ADHOC.tempdb |
| DEV_TEMPDB_ADHOC.tempdb_adhoc |
--------------------------------------
2. SQL selects work within schemas and joins across schemas:
0: jdbc:drill:zk=local> select * from DEV_TEMPDB.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 (1.585 seconds)
0: jdbc:drill:zk=local> select * from DEV_TEMPDB_ADHOC.tempdb_adhoc.guest.cities;
;
-------------------------------------
| first_name | last_name | city |
-------------------------------------
| Bob | Marley | San Francisco |
| Mary | Jane | New York |
| Bat | Man | Gotham |
-------------------------------------
3 rows selected (1.173 seconds)
0: jdbc:drill:zk=local> select a.first_name, a.last_name, a.phone_number, b.city
. . . . . . . . . . . > from DEV_TEMPDB.tempdb.guest.phone_book a
. . . . . . . . . . . > join DEV_TEMPDB_ADHOC.tempdb_adhoc.guest.cities b
. . . . . . . . . . . > on b.first_name = a.first_name
. . . . . . . . . . . > and b.last_name = a.last_name;
--------------------------------------------------+
| first_name | last_name | phone_number | city |
--------------------------------------------------+
| Bob | Marley | 555-555-5555 | San Francisco |
| Mary | Jane | 111-111-1111 | New York |
| Bat | Man | 911-911-9999 | Gotham |
--------------------------------------------------+
3 rows selected (3.937 seconds)
0: jdbc:drill:zk=local>
3. However even though both DEV_TEMPDB.tempdb and DEV_TEMPDB.tempdb_adhoc schemas are shown in show schemas, you cannot query anything on DEV_TEMP.tempdb_adhoc.
Thse SQL select fails:
0: jdbc:drill:zk=local> select * from DEV_TEMPDB.tempdb_adhoc.guest.cities;
Sep 27, 2016 11:54:01 AM org.apache.calcite.sql.validate.SqlValidatorException <init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table 'DEV_TEMPDB.tempdb_adhoc.guest.cities' not found
Sep 27, 2016 11:54:01 AM org.apache.calcite.runtime.CalciteException <init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, column15 to line 1, column 24: Table 'DEV_TEMPDB.tempdb_adhoc.guest.cities' not found
Error: VALIDATION ERROR: From line 1, column 15 to line 1, column 24: Table 'DEV_TEMPDB.tempdb_adhoc.guest.cities' not found
4. Simplified SQL selects work if you set your default schema
0: jdbc:drill:zk=local> use DEV_TEMPDB.tempdb_adhoc;
-----------------------------------------------------+
| true | Default schema changed to [DEV_TEMPDB.tempdb] |
-----------------------------------------------------+
1 row selected (1.369 seconds)
0: jdbc:drill:zk=local> select * from 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 (1.445 seconds)
5. However if you omit guest from the above statement you pass validation, but get a JDBC error. "guest" is optional in Sybase SQL statements. Omitting the owner of
the database table in Sybase in a query turns on Sybase's owner resolution logic on which would choose "login".phone_book > dbo.phone_book > guest.phone_book.
0: jdbc:drill:zk=local> select * from phone_book;
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.
sql SELECT *
FROM "tempdb"."phone_book"
plugin DEV_TEMPDB
Fragment 0:0
This statement doesn't look formatted properly either. It should be tempdb..phone_book if owner is omitted.
D. The end result here is that you can see all the different schemas and even run show tables on all the schemas, but you cannot query any of the tables in all the
schemas.
You should be able to just setup a single Sybase plugin like DEV and run the following query:
0: jdbc:drill:zk=local> select a.first_name, a.last_name, a.phone_number, b.city
. . . . . . . . . . . > from DEV.tempdb.guest.phone_book a
. . . . . . . . . . . > join DEV.tempdb_adhoc.guest.cities b
. . . . . . . . . . . > on b.first_name = a.first_name
. . . . . . . . . . . > and b.last_name = a.last_name;
Sep 27, 2016 12:06:32 PM org.apache.calcite.sql.validate.SqlValidatorException <init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table 'DEV.tempdb.guest.phone_book' not found
Sep 27, 2016 12:06:32 PM org.apache.calcite.runtime.CalciteException <init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 2, column6 to line 2, column 8: Table 'DEV.tempdb.guest.phone_book' not found
Error: VALIDATION ERROR: From line 2, column 6 to line 2, column 8: Table 'DEV.tempdb.guest.phone_book' not found
Attachments
Issue Links
- is cloned by
-
DRILL-5427 SQL Execution Syntax incorrect for Sybase RDBMS
-
- Open
-