drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Lee (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-4908) Unable to setup Sybase JDBC Plugin with access to multiple databases
Date Tue, 27 Sep 2016 19:48:20 GMT
David Lee created DRILL-4908:
--------------------------------

             Summary: Unable to setup Sybase JDBC Plugin with access to multiple databases
                 Key: DRILL-4908
                 URL: https://issues.apache.org/jira/browse/DRILL-4908
             Project: Apache Drill
          Issue Type: Improvement
          Components: SQL Parser
    Affects Versions: 1.8.0
         Environment: linux, sybase ase, sybase iq, windows
            Reporter: David Lee
             Fix For: Future


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





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message