Uploaded image for project: 'Solr'
  1. Solr
  2. SOLR-8659

Improve Solr JDBC Driver to support more SQL Clients

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 6.0
    • Fix Version/s: None
    • Component/s: SolrJ
    • Labels:
      None

      Description

      SOLR-8502 was a great start to getting JDBC support to be more complete. This ticket is to track items that could further improve the JDBC support for more SQL clients and their features. A few SQL clients are:

      • DbVisualizer
      • SQuirrel SQL
      • Apache Zeppelin (incubating)
      • Spark
      • Python & Jython
      • IntelliJ IDEA Database Tool
      • ODBC clients like Excel/Tableau

        Issue Links

        1.
        SolrJ JDBC - ClassNotFoundException: com.google.common.base.MoreObjects Sub-task Closed Unassigned
         
        2.
        Implement DatabaseMetaDataImpl getTables() and fix getSchemas() Sub-task Resolved Kevin Risden
         
        3.
        Implement Connection.setReadOnly, Statement.set/getFetchSize, ResultSet.getType Sub-task Resolved Kevin Risden
         
        4.
        Implement Connection.prepareStatement Sub-task Closed Kevin Risden
         
        5.
        Implement StatementImpl/ResultSetImpl get/set fetch* methods and proper errors for traversal methods Sub-task Closed Kevin Risden
         
        6.
        Add setReadOnly(String ...) to ConnectionImpl Sub-task Resolved Kevin Risden
         
        7. Implement DatabaseMetaDataImpl.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) Sub-task Open Kevin Risden
         
        8.
        SolrJ JDBC - Implement "Select *" Sub-task Resolved Kevin Risden
         
        9. Implement PreparedStatementImpl parameterization Sub-task Reopened Kevin Risden
         
        10.
        Implement DatabaseMetaDataImpl.getPrimaryKeys(String catalog, String schema, String table) Sub-task Closed Kevin Risden
         
        11.
        SolrJ JDBC - DBVisualizer DB Capabilities Sub-task Closed Unassigned
         
        12.
        SolrJ JDBC - DbVisualizer Database Info Keywords and Functions Sub-task Closed Kevin Risden
         
        13.
        SolrJ JDBC - DbVisualizer info about data types Sub-task Closed Kevin Risden
         
        14.
        SolrJ JDBC - DbVisualizer DB Tables ResultSet is empty Sub-task Closed Unassigned
         
        15.
        SolrJ JDBC - DbVisualizer DB -> Table -> Tables tab ResultSet is empty Sub-task Closed Unassigned
         
        16.
        SolrJ JDBC - DbVisualizer DB -> DB -> References tab NPE Sub-task Closed Unassigned
         
        17.
        SolrJ JDBC - Ensure that SQuirrel SQL works with SolrJ JDBC Sub-task Resolved Kevin Risden
         
        18.
        SolrJ JDBC - Ensure that Python JayDeBeApi works with SolrJ JDBC Sub-task Closed Kevin Risden
         
        19.
        SolrJ JDBC - Ensure that R RJDBC works with SolrJ JDBC Sub-task Closed Kevin Risden
         
        20.
        SolrJ JDBC - Ensure that an ODBC-JDBC bridge works with SolrJ JDBC Sub-task Closed Kevin Risden
         
        21.
        Add documentation for how to use Solr JDBC driver with SQL client like DB Visualizer Sub-task Resolved Kevin Risden
         
        22.
        SolrJ JDBC - SQuirrel SQL documentation Sub-task Resolved Kevin Risden
         
        23.
        SolrJ JDBC - Apache Zeppelin JDBC documentation Sub-task Resolved Kevin Risden
         
        24.
        SolrJ JDBC - Python JayDeBeApi documentation Sub-task Resolved Kevin Risden
         
        25.
        SolrJ JDBC - Jython Documentation Sub-task Resolved Kevin Risden
         
        26.
        SolrJ JDBC - R RJDBC documentation Sub-task Resolved Kevin Risden
         
        27.
        SolrJ JDBC - ODBC-JDBC bridge documentation Sub-task Closed Kevin Risden
         
        28. SolrJ JDBC - Ensure that Spark works with SolrJ JDBC Sub-task Open Kevin Risden
         
        29. SolrJ JDBC - Spark documentation Sub-task Open Unassigned
         

          Activity

          Hide
          risdenk Kevin Risden added a comment - - edited

          Some rough notes that should be added as subtasks for DbVisualizer:

          • After connection, click on Database Info tab after double clicking on connection name ensure that DB capabilities are correct - SOLR-8815
          • After connection, click on Database Info tab after double clicking on connection name should add add info about supported Keywords and Functions - SOLR-8816
          • After connection, click on Data Types tab after double clicking on connection name should add info about data types - SOLR-8817
          • After connecting, ResultSet is empty if double click on "DB" under connection name then click on Tables tab. - SOLR-8818
          • After connecting, NPE if double click on "DB" under connection name then click on References tab. - SOLR-8819
          • After connecting, ResultSet is empty if double click on "Table" under "DB" then click on Tables tab. - SOLR-8820
          • After connecting, NPE if double click on "DB" under "DB" then click on References tab. - SOLR-8821
          • Implement DatabaseMetaDataImpl.getPrimaryKeys(String catalog, String schema, String table) - SOLR-8822
          • Implement DatabaseMetaDataImpl.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) - SOLR-8823
          Show
          risdenk Kevin Risden added a comment - - edited Some rough notes that should be added as subtasks for DbVisualizer: After connection, click on Database Info tab after double clicking on connection name ensure that DB capabilities are correct - SOLR-8815 After connection, click on Database Info tab after double clicking on connection name should add add info about supported Keywords and Functions - SOLR-8816 After connection, click on Data Types tab after double clicking on connection name should add info about data types - SOLR-8817 After connecting, ResultSet is empty if double click on "DB" under connection name then click on Tables tab. - SOLR-8818 After connecting, NPE if double click on "DB" under connection name then click on References tab. - SOLR-8819 After connecting, ResultSet is empty if double click on "Table" under "DB" then click on Tables tab. - SOLR-8820 After connecting, NPE if double click on "DB" under "DB" then click on References tab. - SOLR-8821 Implement DatabaseMetaDataImpl.getPrimaryKeys(String catalog, String schema, String table) - SOLR-8822 Implement DatabaseMetaDataImpl.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) - SOLR-8823
          Hide
          risdenk Kevin Risden added a comment - - edited

          As for SquirrelSQL the following will at minimum be needed:

          Show
          risdenk Kevin Risden added a comment - - edited As for SquirrelSQL the following will at minimum be needed: Documentation on how to use SquirrelSQL like SOLR-8521 - SOLR-8825 Test SquirrelSQL with Solr JDBC and resolve issues - SOLR-8827
          Hide
          risdenk Kevin Risden added a comment -

          Another thing to test is potentially a ODBC-JDBC bridge (http://docs.oracle.com/cd/E13167_01/aldsp/docs20/appdev/jdbcclt.html#1072126) to potentially use software like Excel/Tableau/other non Java tools.

          Show
          risdenk Kevin Risden added a comment - Another thing to test is potentially a ODBC-JDBC bridge ( http://docs.oracle.com/cd/E13167_01/aldsp/docs20/appdev/jdbcclt.html#1072126 ) to potentially use software like Excel/Tableau/other non Java tools.
          Hide
          joel.bernstein Joel Bernstein added a comment - - edited

          Yes, Tableau is going to be very important. We might want to consider changing this ticket to be "Improve Solr JDBC Driver to support Tableau"

          As the 6x branch progresses this will be one of my highest priorities along with SOLR-8593.

          Show
          joel.bernstein Joel Bernstein added a comment - - edited Yes, Tableau is going to be very important. We might want to consider changing this ticket to be "Improve Solr JDBC Driver to support Tableau" As the 6x branch progresses this will be one of my highest priorities along with SOLR-8593 .
          Hide
          risdenk Kevin Risden added a comment - - edited

          So for any ODBC program (Tableau, Excel, etc), this is going to require an ODBC-JDBC bridge. I have been trying to get OpenLink to work on my Mac, but they don't seem to support Mac OS X 10.11 :/ I have a support ticket open since Tuesday, but no word back yet. I may have to spin up a Windows VM or something else to try to test an ODBC-JDBC bridge.

          Show
          risdenk Kevin Risden added a comment - - edited So for any ODBC program (Tableau, Excel, etc), this is going to require an ODBC-JDBC bridge. I have been trying to get OpenLink to work on my Mac, but they don't seem to support Mac OS X 10.11 :/ I have a support ticket open since Tuesday, but no word back yet. I may have to spin up a Windows VM or something else to try to test an ODBC-JDBC bridge.
          Hide
          risdenk Kevin Risden added a comment -

          The OpenLink 6.x ODBC-JDBC bridge is linked against an older version of Java and won't work with JDK 7/8. I have an open support ticket with OpenLink (case 21637). Until I am able to get a new build won't be able to test Excel/Tableau/etc on my Mac. For reference the error message is as follows:

          /Library/Application\ Support/iODBC/bin/iodbctest "DSN=Solr;UID=hr;PWD=openlink"
          iODBC Demonstration program
          This program shows an interactive SQL processor
          Driver Manager: 03.52.1015.0210
          
          		Tue Feb 16 2016
          10:48:48 ERROR: Can't find DBMS Driver = [org/apache/solr/client/solrj/io/sql/DriverImpl]
          1: SQLDriverConnect = [OpenLink][ODBC][JDBC Server]java.lang.UnsupportedClassVersionError: org/apache/solr/client/solrj/io/sql/DriverImpl : Unsupported major.minor version 52.0 (0) SQLSTATE=HY000
          1: ODBC_Connect = [OpenLink][ODBC][JDBC Server]java.lang.UnsupportedClassVersionError: org/apache/solr/client/solrj/io/sql/DriverImpl : Unsupported major.minor version 52.0 (0) SQLSTATE=HY000
          
          Have a nice day.%
          
          Show
          risdenk Kevin Risden added a comment - The OpenLink 6.x ODBC-JDBC bridge is linked against an older version of Java and won't work with JDK 7/8. I have an open support ticket with OpenLink (case 21637). Until I am able to get a new build won't be able to test Excel/Tableau/etc on my Mac. For reference the error message is as follows: /Library/Application\ Support/iODBC/bin/iodbctest "DSN=Solr;UID=hr;PWD=openlink" iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.1015.0210 Tue Feb 16 2016 10:48:48 ERROR: Can't find DBMS Driver = [org/apache/solr/client/solrj/io/sql/DriverImpl] 1: SQLDriverConnect = [OpenLink][ODBC][JDBC Server]java.lang.UnsupportedClassVersionError: org/apache/solr/client/solrj/io/sql/DriverImpl : Unsupported major.minor version 52.0 (0) SQLSTATE=HY000 1: ODBC_Connect = [OpenLink][ODBC][JDBC Server]java.lang.UnsupportedClassVersionError: org/apache/solr/client/solrj/io/sql/DriverImpl : Unsupported major.minor version 52.0 (0) SQLSTATE=HY000 Have a nice day.%
          Hide
          joel.bernstein Joel Bernstein added a comment -

          I believe Spotfire has native JDBC support, maybe that's a good one to focus on. We can also just keep building out the driver for DbVisualizer and Squirrel and it should in theory work for a JDBC/ODBC bridge as well once we find a good one to test with. Alfresco (who I work for) is pretty interested in Tableau so it's likely to get some internal testing.

          Show
          joel.bernstein Joel Bernstein added a comment - I believe Spotfire has native JDBC support, maybe that's a good one to focus on. We can also just keep building out the driver for DbVisualizer and Squirrel and it should in theory work for a JDBC/ODBC bridge as well once we find a good one to test with. Alfresco (who I work for) is pretty interested in Tableau so it's likely to get some internal testing.
          Hide
          risdenk Kevin Risden added a comment -

          Good news: I was able to get a beta build of Openlink ODBC-JDBC 7.x that is linked against JDK 1.8 to test with. I was able to test the JDBC driver with Openlinks iODBC Demo app
          Less good news: There are some more methods that need to be implemented. I have a working patch and was able to get some results. The screenshot is attached. I am going to open a JIRA and attach the patch here this morning.

          Show
          risdenk Kevin Risden added a comment - Good news: I was able to get a beta build of Openlink ODBC-JDBC 7.x that is linked against JDK 1.8 to test with. I was able to test the JDBC driver with Openlinks iODBC Demo app Less good news: There are some more methods that need to be implemented. I have a working patch and was able to get some results. The screenshot is attached. I am going to open a JIRA and attach the patch here this morning.
          Hide
          joel.bernstein Joel Bernstein added a comment -

          This is great news, clears the path for excel and Tableau work.

          Show
          joel.bernstein Joel Bernstein added a comment - This is great news, clears the path for excel and Tableau work.
          Hide
          risdenk Kevin Risden added a comment -

          Side note about testing Tableau Desktop on a Mac. As of Tableau 9.2.5 there is no generic ODBC option available. This means testing it will be a pain. http://kb.tableau.com/articles/issue/generic-odbc-data-connection-unavailable-on-the-mac

          Show
          risdenk Kevin Risden added a comment - Side note about testing Tableau Desktop on a Mac. As of Tableau 9.2.5 there is no generic ODBC option available. This means testing it will be a pain. http://kb.tableau.com/articles/issue/generic-odbc-data-connection-unavailable-on-the-mac
          Hide
          joel.bernstein Joel Bernstein added a comment -

          Yeah Tableau is surprisingly windows centric. Wonder if they are phasing out the desktop product anyway in favor of something browser based.

          Show
          joel.bernstein Joel Bernstein added a comment - Yeah Tableau is surprisingly windows centric. Wonder if they are phasing out the desktop product anyway in favor of something browser based.
          Hide
          risdenk Kevin Risden added a comment - - edited

          IntelliJ IDEA has the ability to interact with databases that have JDBC drivers. This is another thing that can be tested for the Solr JDBC driver.

          Show
          risdenk Kevin Risden added a comment - - edited IntelliJ IDEA has the ability to interact with databases that have JDBC drivers. This is another thing that can be tested for the Solr JDBC driver.
          Hide
          risdenk Kevin Risden added a comment -

          Joel Bernstein - It would be great to get the following 3 JIRAs into Solr 6:

          Show
          risdenk Kevin Risden added a comment - Joel Bernstein - It would be great to get the following 3 JIRAs into Solr 6: SOLR-8819 SOLR-8809 SOLR-8810
          Show
          risdenk Kevin Risden added a comment - - edited A few more languages/clients that could be useful to test at some point: R - SOLR-9019 & SOLR-9021 RJDBC - https://cran.r-project.org/web/packages/RJDBC/index.html Python/Jython - SOLR-9011 & SOLR-9013 & SOLR-9018 https://wiki.python.org/jython/DatabaseExamples https://pypi.python.org/pypi/JayDeBeApi/ http://www.jython.org/jythonbook/en/1.0/DatabasesAndJython.html

            People

            • Assignee:
              Unassigned
              Reporter:
              risdenk Kevin Risden
            • Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:

                Development