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

Indexing not activated with left outer join and where clause

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: tez-branch, 0.13.1
    • Fix Version/s: None
    • Component/s: Hive, Indexing, Tez
    • Labels:
    • Environment:

      HDInsight

      Description

      I am attempting to optimize a query using indexing. However, indexing is not being activated, and my query takes an impractical amount of time to complete. Note that John Pullokkaran at Hortonworks has had a look at the "explain extended" log and suggested that I file a bug report.

      Here is how I set up indexing:
      Set hive.optimize.index.filter=true;
      DROP INDEX IF EXISTS ipv4indexes ON ipv4geotable;
      CREATE INDEX ipv4indexes
      ON TABLE ipv4geotable (StartIp, EndIp)
      AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
      WITH DEFERRED REBUILD
      IDXPROPERTIES ('hive.index.compact.binary.search'='true');
      ALTER INDEX ipv4indexes ON ipv4geotable REBUILD;

      And here is my query:

      DROP TABLE IF EXISTS ipv4table;
      CREATE TABLE ipv4table AS
      SELECT logon.IP, ipv4.Country
      FROM
      (SELECT * FROM logontable WHERE isIpv4(IP)) logon
      LEFT OUTER JOIN
      (SELECT StartIp, EndIp, Country FROM ipv4geotable) ipv4 ON isIpv4(logon.IP)
      WHERE ipv4.StartIp <= logon.IP AND logon.IP <= ipv4.EndIp;

      What the query is doing is extracting an IP from logontable and finding in which range it lies within a geolocation table (which is sorted). When a range is found, a country matching the IP range is returned.

      I need to set up indexing in order to conduct a binary search rather than going through millions of rows one at a time. Note that I use the Tez engine, but the problem also occurs with map-reduce.

        Attachments

          Activity

            People

            • Assignee:
              Teabennie Bennie Can
              Reporter:
              Teabennie Bennie Can
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Due:
                Created:
                Updated: