[
https://issues.apache.org/jira/browse/DRILL-4908?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Lee closed DRILL-4908.
----------------------------
Resolution: Duplicate
Closing this issue and reopening it as part of Drill 1.10 with new examples..
The behavior in Drill 1.10 is different now and this use case cannot be
reproduced with the example included.
> 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.15#6346)
