[jira] [Comment Edited] (DRILL-3180) Apache Drill JDBC storage plugin to query rdbms systems such as MySQL and Netezza from Apache Drill

["Hyzandey Castro Alves (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-3180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15070144#comment-15070144 ] 

Hyzandey Castro Alves edited comment on DRILL-3180 at 12/23/15 8:26 PM:
------------------------------------------------------------------------

Hi,  Jacques.

Considering this plugin can theoretically connect to any RDBMS through JDBC, I tried to connect it to some INFORMIX database with something like this (named it "informix"):

{
  "type": "jdbc",
  "driver": "com.informix.jdbc.IfxDriver",
  "url": "jdbc:informix-sqli://IP:PORT/sample_database:INFORMIXSERVER=sample_server;user=sample_user;password=sample_password",
  "username": null,
  "password": null,
  "enabled": true
}

It successfully connects and gets me to list "schemas" and tables, but I cannot get queries to work  because the SQL sent to the INFORMIX server looks like this:

SELECT *
FROM sample_database.sample_table

for a Drill query like this:

SELECT *
FROM informix.sample_database.sample_table

I think this error occurs because it is not necessary to qualify the table name with the database name. Worse than this, the appended database name  gets the query on INFORMIX to fail.

How could I fix this? I tried to query from "informix.sample_table" table, but it gets me a "VALIDATION ERROR: informix.sample_table not found". 


was (Author: hyzandey):
Hi,  Jacques.

Considering this plugin can theoretically connect to any RDBMS through JDBC, I tried to connect it to some INFORMIX database with something like this (named it "informix"):

{
  "type": "jdbc",
  "driver": "com.informix.jdbc.IfxDriver",
  "url": "jdbc:informix-sqli://IP:PORT/sample_database:INFORMIXSERVER=sample_server;user=sample_user;password=sample_password",
  "username": null,
  "password": null,
  "enabled": true
}

It successfully connects and get me to list "schemas" and tables, but I cannot get queries to work  because the SQL sent to the INFORMIX server looks like this:

SELECT *
FROM sample_database.sample_table

for a Drill query like this:

SELECT *
FROM informix.sample_database.sample_table

I think this error occurs because it is not necessary to qualify the table name with the database name. Worse than this, the appended database name  gets the query on INFORMIX to fail.

How could I fix this? I tried to query from "informix.sample_table" table, but it gets me a "VALIDATION ERROR: informix.sample_table not found". 

> Apache Drill JDBC storage plugin to query rdbms systems such as MySQL and Netezza from Apache Drill
> ---------------------------------------------------------------------------------------------------
>
>                 Key: DRILL-3180
>                 URL: https://issues.apache.org/jira/browse/DRILL-3180
>             Project: Apache Drill
>          Issue Type: New Feature
>          Components: Storage - Other
>    Affects Versions: 1.0.0
>            Reporter: Magnus Pierre
>            Assignee: Jacques Nadeau
>              Labels: Drill, JDBC, plugin
>             Fix For: 1.2.0
>
>         Attachments: patch.diff, pom.xml, storage-mpjdbc.zip
>
>   Original Estimate: 1m
>  Remaining Estimate: 1m
>
> I have developed the base code for a JDBC storage-plugin for Apache Drill. The code is primitive but consitutes a good starting point for further coding. Today it provides primitive support for SELECT against RDBMS with JDBC. 
> The goal is to provide complete SELECT support against RDBMS with push down capabilities.
> Currently the code is using standard JDBC classes.



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