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: Resolved
    • Major
    • Resolution: Fixed
    • 4.0.0-alpha-2
    • 4.1.0
    • CBO

    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

        Issue Links

          Activity

            People

              soumyakanti.das Soumyakanti Das
              zabetak Stamatis Zampetakis
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: