Hive
  1. Hive
  2. HIVE-987

Hive CLI Omnibus Improvement ticket

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: CLI
    • Labels:
      None
    • Tags:
      CLI history tab completion readline jline

      Description

      Add the following features to the Hive CLI:

      • Command History
      • ReadLine support
        • HIVE-120: Add readline support/support for alt-based commands in the CLI
        • Java-ReadLine is LGPL, but it depends on GPL readline library. We probably need to use JLine instead.
      • Tab completion
        • HIVE-97: tab completion for hive cli
      • Embedded/Standalone CLI modes, and ability to connect to different Hive Server instances.
        • HIVE-818: Create a Hive CLI that connects to hive ThriftServer
      • .hiverc configuration file
      • Improved support for comments.
        • HIVE-430: Ability to comment desired for hive query files
      • Different output formats
        • HIVE-49: display column header on CLI
        • XML output format

      For additional inspiration we may want to look at the Postgres psql shell: http://www.postgresql.org/docs/8.1/static/app-psql.html

      Finally, it would be really cool if we implemented this in a generic fashion and spun it off as an apache-commons
      shell framework. It seems like most of the Apache Hadoop projects have their own shells, and I'm sure the same is true
      for non-Hadoop Apache projects as well.

      1. HIVE-987.1.patch
        1 kB
        John Sichi
      2. HIVE-987.2.patch
        0.6 kB
        John Sichi
      3. sqlline-1.0.8_eb.jar
        76 kB
        John Sichi

        Issue Links

          Activity

          Hide
          John Sichi added a comment -

          In case it's useful (at least as a place from which to borrow code), here's a jline-based pure Java CLI generic to any JDBC back end, with a BSD license:

          http://sqlline.sourceforge.net/

          It supports most of the features above, plus some other nice stuff like Control-C for cancel. Development by the original author has tapered off, but it used as the primary CLI for LucidDB, where new enhancements are made on it occasionally.

          Show
          John Sichi added a comment - In case it's useful (at least as a place from which to borrow code), here's a jline-based pure Java CLI generic to any JDBC back end, with a BSD license: http://sqlline.sourceforge.net/ It supports most of the features above, plus some other nice stuff like Control-C for cancel. Development by the original author has tapered off, but it used as the primary CLI for LucidDB, where new enhancements are made on it occasionally.
          Hide
          John Sichi added a comment -

          To give sqlline a try, do the following:

          1) Apply patch HIVE-987.1.patch
          2) Download the attached sqlline-1.0.8_eb.jar and put it in your cli/lib directory
          3) ant package
          4) in one shell, run build/dist/bin/hive --service hiveserver (to start the Hive Thrift service)
          5) build/dist/bin/hive --service beeline (to start a sqlline client talking to the localhost Thrift service)
          6) Ignore the "Error: Method not supported (state=,code=0)" messages when they appear

          Command completion requires HIVE-1126 to be applied too.

          Show
          John Sichi added a comment - To give sqlline a try, do the following: 1) Apply patch HIVE-987 .1.patch 2) Download the attached sqlline-1.0.8_eb.jar and put it in your cli/lib directory 3) ant package 4) in one shell, run build/dist/bin/hive --service hiveserver (to start the Hive Thrift service) 5) build/dist/bin/hive --service beeline (to start a sqlline client talking to the localhost Thrift service) 6) Ignore the "Error: Method not supported (state=,code=0)" messages when they appear Command completion requires HIVE-1126 to be applied too.
          Hide
          John Sichi added a comment -

          Some examples:

          sqlline version 1.0.8-eb by Marc Prud'hommeaux
          0: jdbc:hive://localhost:10000/default> CREATE TABLE pokes (foo INT, bar STRING);  
          +--+
          |  |
          +--+
          +--+
          No rows selected (0.624 seconds)
          Error: Method not supported (state=,code=0)
          0: jdbc:hive://localhost:10000/default> LOAD DATA LOCAL INPATH '/Users/jsichi/open/hive-trunk/data/files/kv1.txt' OVERWRITE INTO TABLE pokes;
          +--+
          |  |
          +--+
          +--+
          No rows selected (0.265 seconds)
          Error: Method not supported (state=,code=0)
          0: jdbc:hive://localhost:10000/default> select * from pokes where foo=100;
          +------+----------+
          | foo  |   bar    |
          +------+----------+
          | 100  | val_100  |
          | 100  | val_100  |
          +------+----------+
          2 rows selected (4.716 seconds)
          Error: Method not supported (state=,code=0)
          0: jdbc:hive://localhost:10000/default> !outputformat xmlattr             
          0: jdbc:hive://localhost:10000/default> select * from pokes where foo=100;
          <resultset>
            <result foo="100" bar="val_100"/>
            <result foo="100" bar="val_100"/>
          </resultset>
          2 rows selected (3.693 seconds)
          Error: Method not supported (state=,code=0)
          0: jdbc:hive://localhost:10000/default> !outputformat xmlelements         
          0: jdbc:hive://localhost:10000/default> select * from pokes where foo=100;
          <resultset>
            <result>
              <foo>100</foo>
              <bar>val_100</bar>
            </result>
            <result>
              <foo>100</foo>
              <bar>val_100</bar>
            </result>
          </resultset>
          2 rows selected (3.288 seconds)
          Error: Method not supported (state=,code=0)
          0: jdbc:hive://localhost:10000/default> !quit
          Closing: org.apache.hadoop.hive.jdbc.HiveConnection
          
          Show
          John Sichi added a comment - Some examples: sqlline version 1.0.8-eb by Marc Prud'hommeaux 0: jdbc:hive://localhost:10000/default> CREATE TABLE pokes (foo INT, bar STRING); +--+ | | +--+ +--+ No rows selected (0.624 seconds) Error: Method not supported (state=,code=0) 0: jdbc:hive://localhost:10000/default> LOAD DATA LOCAL INPATH '/Users/jsichi/open/hive-trunk/data/files/kv1.txt' OVERWRITE INTO TABLE pokes; +--+ | | +--+ +--+ No rows selected (0.265 seconds) Error: Method not supported (state=,code=0) 0: jdbc:hive://localhost:10000/default> select * from pokes where foo=100; +------+----------+ | foo | bar | +------+----------+ | 100 | val_100 | | 100 | val_100 | +------+----------+ 2 rows selected (4.716 seconds) Error: Method not supported (state=,code=0) 0: jdbc:hive://localhost:10000/default> !outputformat xmlattr 0: jdbc:hive://localhost:10000/default> select * from pokes where foo=100; <resultset> <result foo="100" bar="val_100"/> <result foo="100" bar="val_100"/> </resultset> 2 rows selected (3.693 seconds) Error: Method not supported (state=,code=0) 0: jdbc:hive://localhost:10000/default> !outputformat xmlelements 0: jdbc:hive://localhost:10000/default> select * from pokes where foo=100; <resultset> <result> <foo>100</foo> <bar>val_100</bar> </result> <result> <foo>100</foo> <bar>val_100</bar> </result> </resultset> 2 rows selected (3.288 seconds) Error: Method not supported (state=,code=0) 0: jdbc:hive://localhost:10000/default> !quit Closing: org.apache.hadoop.hive.jdbc.HiveConnection
          Hide
          John Sichi added a comment -

          If people are +1 on sqlline, we could create a new beeline wrapper script (instead of invoking it via hive --service). We could leave the old hive CLI command available for backwards compatibility as well as for those who prefer its hardcore feel. We would also need to make Hive JDBC driver embedded mode work again (at least it doesn't seem to be working for me) to avoid having to run a server.

          We'd also need to fix/suppress whatever JDBC calls are throwing "Method not supported", and some other problems (e.g. currently running Hive's "set" command to show settings does not work through sqlline). In general, using sqlline should help drive improvements to Hive's JDBC driver.

          Note that sqlline is BSD-licensed, so I think it should be OK as a dependency. (It also depends on jline, which is already part of Hive CLI.) The provenance for the version I attached is described here:

          http://pub.eigenbase.org/wiki/SqllineDocs

          Show
          John Sichi added a comment - If people are +1 on sqlline, we could create a new beeline wrapper script (instead of invoking it via hive --service). We could leave the old hive CLI command available for backwards compatibility as well as for those who prefer its hardcore feel. We would also need to make Hive JDBC driver embedded mode work again (at least it doesn't seem to be working for me) to avoid having to run a server. We'd also need to fix/suppress whatever JDBC calls are throwing "Method not supported", and some other problems (e.g. currently running Hive's "set" command to show settings does not work through sqlline). In general, using sqlline should help drive improvements to Hive's JDBC driver. Note that sqlline is BSD-licensed, so I think it should be OK as a dependency. (It also depends on jline, which is already part of Hive CLI.) The provenance for the version I attached is described here: http://pub.eigenbase.org/wiki/SqllineDocs
          Hide
          Raghotham Murthy added a comment -

          jdbc/src/test/org/apache/hadoop/hive/jdbc/TestJdbcDriver.java shows using hive's jdbc driver in embedded mode. Doesnt it run as part of ant test?

          Show
          Raghotham Murthy added a comment - jdbc/src/test/org/apache/hadoop/hive/jdbc/TestJdbcDriver.java shows using hive's jdbc driver in embedded mode. Doesnt it run as part of ant test?
          Hide
          John Sichi added a comment -

          @Raghu: you are right. I screwed up when I was testing the embedded mode; it actually works fine already.

          Show
          John Sichi added a comment - @Raghu: you are right. I screwed up when I was testing the embedded mode; it actually works fine already.
          Hide
          Ashish Thusoo added a comment -

          I am +1 on this. I think this can open up good possibilities. I have not looked at sqlline code but how much does it depend on actually SQL dialect. Plus, how easy is it to extend to hdfs related command e.g. the CLI today has commands that can do set of conf variables. It also supports the hadoop dfs commands as well which talk directly to hdfs. I am not sure if too many people use them, but I do. Would be great to get them integrated with sqlline if that is possible.

          Show
          Ashish Thusoo added a comment - I am +1 on this. I think this can open up good possibilities. I have not looked at sqlline code but how much does it depend on actually SQL dialect. Plus, how easy is it to extend to hdfs related command e.g. the CLI today has commands that can do set of conf variables. It also supports the hadoop dfs commands as well which talk directly to hdfs. I am not sure if too many people use them, but I do. Would be great to get them integrated with sqlline if that is possible.
          Hide
          John Sichi added a comment -

          sqlline is agnostic as to SQL dialect, so commands such as show/describe/dfs just work.

          (The one exception I have found so far is the set command, which is throwing an NPE; probably something about the result set we return to list all the settings. Shouldn't be hard to fix.)

          sqlline has some of its own commands such as !help and !quit; these are always prefixed with bang. Anything else it just sends through, with the exception of comments, which it strips off before sending.

          Show
          John Sichi added a comment - sqlline is agnostic as to SQL dialect, so commands such as show/describe/dfs just work. (The one exception I have found so far is the set command, which is throwing an NPE; probably something about the result set we return to list all the settings. Shouldn't be hard to fix.) sqlline has some of its own commands such as !help and !quit; these are always prefixed with bang. Anything else it just sends through, with the exception of comments, which it strips off before sending.
          Hide
          John Sichi added a comment -

          Change SQLLINE_OPTS to this to use embedded mode:

          SQLLINE_OPTS='-u jdbc:hive:// -d org.apache.hadoop.hive.jdbc.HiveDriver -n sa'

          Options such as this can also be overridden on the command line when invoking, e.g. to connect to a particular server:

          hive --service beeline -u jdbc:hive://theirserver:10001/default

          Show
          John Sichi added a comment - Change SQLLINE_OPTS to this to use embedded mode: SQLLINE_OPTS='-u jdbc:hive:// -d org.apache.hadoop.hive.jdbc.HiveDriver -n sa' Options such as this can also be overridden on the command line when invoking, e.g. to connect to a particular server: hive --service beeline -u jdbc:hive://theirserver:10001/default
          Hide
          John Sichi added a comment -

          Updated beeline patch.

          Show
          John Sichi added a comment - Updated beeline patch.
          Hide
          John Sichi added a comment -

          Note that for the real thing, we would not want to use the Hadoop wrapper scripts; there's no need to drag in all of that. The sqlline client can be very thin (just the sqlline jar plus jline and the Hive JDBC client jar).

          Show
          John Sichi added a comment - Note that for the real thing, we would not want to use the Hadoop wrapper scripts; there's no need to drag in all of that. The sqlline client can be very thin (just the sqlline jar plus jline and the Hive JDBC client jar).

            People

            • Assignee:
              Unassigned
              Reporter:
              Carl Steinbach
            • Votes:
              0 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

              • Created:
                Updated:

                Development