Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-27080

Support project pushdown in JDBC storage handler even when filters are not pushed

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 4.0.0-alpha-2
    • None
    • CBO
    • None

    Description

      CREATE EXTERNAL TABLE book
      (
          id int,
          title varchar(20),
          author int
      )
      STORED BY                                          
      'org.apache.hive.storage.jdbc.JdbcStorageHandler'
      TBLPROPERTIES (                                    
          "hive.sql.database.type" = "POSTGRES",
          "hive.sql.jdbc.driver" = "org.postgresql.Driver",
          "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
          "hive.sql.dbcp.username" = "qtestuser",
          "hive.sql.dbcp.password" = "qtestpassword",
          "hive.sql.table" = "book"
      );
      
      explain cbo select id from book where title = 'Les Miserables';
      
      CBO PLAN:
      HiveJdbcConverter(convention=[JDBC.POSTGRES])
        JdbcProject(id=[$0])
          JdbcFilter(condition=[=($1, _UTF-16LE'Les Miserables')])
            JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
      

      Good case: Only the id column is fetched from the underlying database (see JdbcProject) since it is necessary for the result.

      explain cbo select id from book where UPPER(title) = 'LES MISERABLES';
      
      CBO PLAN:
      HiveProject(id=[$0])
        HiveFilter(condition=[=(CAST(UPPER($1)):VARCHAR(2147483647) CHARACTER SET "UTF-16LE", _UTF-16LE'LES MISERABLES')])
          HiveProject(id=[$0], title=[$1], author=[$2])
            HiveJdbcConverter(convention=[JDBC.POSTGRES])
              JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
      

      Bad case: All table columns are fetched from the database although only id and title are necessary; id is the result so cannot be dropped and title is needed for HiveFilter since the UPPER operation was not pushed in the DBMS. The author column is not needed at all so the plan should have a JdbcProject with id, and title, on top of the JdbcHiveTableScan.

      Although it doesn't seem a big deal in some cases tables are pretty wide (more than 100 columns) while the queries rarely return all of them. Improving project pushdown to handle such cases can give a major performance boost.

      Pushing the filter with UPPER to JDBC storage handler is also a relevant improvement but this should be tracked under another ticket.

      The problem can be reproduced by running:

      mvn test -Dtest=TestMiniLlapLocalCliDriver -Dqfile=jdbc_project_pushdown.q -Dtest.output.overwrite
      

      Attachments

        1. jdbc_project_pushdown.q
          0.7 kB
          Stamatis Zampetakis

        Activity

          People

            Unassigned Unassigned
            zabetak Stamatis Zampetakis
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: