Uploaded image for project: 'Apache Knox'
  1. Apache Knox
  2. KNOX-2128

Custom DataSource and SQL Commands for KnoxShell and KnoxShellTable

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.4.0
    • Component/s: KnoxShell
    • Labels:

      Description

      As described in KIP-14 , GroovyShell allows for the extension of the shell itself with custom commands. By providing commands for the management of DataSource configuration and use in SQL queries, we can simplify the interaction with SQL engines and databases for JDBC-based database access in KnoxShell to make for a powerful scripting and shell environment.

      DataSource and SQL commands really go hand in hand here and will be done at the same time.

      • Datasources (:datasource|:ds) CRUD and select operations for a set of JDBC datasources that are persisted to disk in the user home directory
      • SQL (:SQL|:sql) SQL query execution with persisted SQL history per datasource

      Examples:

      
      :ds add test_ds connectStr driverClass authn_type
      
      

      The above will add a new datasource to the KnoxShell environment with the name test_ds the required connectStr, the classname of the driver to use and either "basic" or "none" for authentication requirements.

      Invoking the above will actually result in a table rendering of all the currently configured datasources.

      When there are more than one, the following command must be used to select the datasource to use for SQL commands:

      
      :ds select test_ds
      
      

      This command selects the desired datasource by name and sets it as the select datasource context within the environment.

      If there is only one datasource in the environment, its selection is implied and the explicit selection isn't required with the above command.

      Once a datasource is selected the SQL command may be used to interact with the datasource.

      
      :sql assign books
      
      

      The above command will present the user with a Java Swing dialog to prompt for a SQL statement, challenge the user for authentication if required and leverage the KnoxShellTable JDBC builder API to submit the SQL query to the selected datasource and return the resultset in a KnoxShellTable variable in KnoxShell environment with the name books. It will also result in the rendering of the tablular result.

      The KnoxShellTable variable called "books" may then be used directly within the KnoxShell environment using the KnoxShellTable fluent API to select, filter, sort and join with other tables - as desired.

      
      :ds remove test_ds
      
      

      The above command invocation will remove the configured datasource and deselect it - if selected and persist the changes.

       

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                lmccay Larry McCay
                Reporter:
                lmccay Larry McCay
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 3h
                  3h