hive like和regexp效率 hive中rlike用法

目录

一、函数使用

1.like的使用详解

2.rlike使用详解

3.regexp的用法和rlike一样

二、使用案例

1.like的使用案例

2.rlike使用案例

3.regexp的用法和rlike一样

三、源码分析

1.UDFLike.class

2.UDFRegexp.class

四、总结

---

大家好，我是老六。在工作过程中，我发现有很多同学傻傻分不清rlike、like、regexp的区别以及用法，今天和大家一起来看下它们的用法以及区别。

一、函数使用

hive官方解释如图

我给大家翻一下啊。

1.like的使用详解

格式是A like B,其中A是字符串，B是表达式，表示能否用B去完全匹配A的内容，换句话说能否用B这个表达式去表示A的全部内容，注意这个和rlike是有区别的。返回的结果是True/False.B只能使用简单匹配符号 _和%，”_”表示任意单个字符，字符”%”表示任意数量的字符like的匹配是按字符逐一匹配的，使用B从A的第一个字符开始匹配，所以即使有一个字符不同都不行。

2.rlike使用详解

A rlike B ，表示B是否在A里面即可。而A like B,则表示B是否是A.
B中的表达式可以使用Java中全部正则表达式，具体正则规则参考Java，或者其他标准正则语法。

3.regexp的用法和rlike一样

二、使用案例

1.like的使用案例

2.rlike使用案例

3.regexp的用法和rlike一样

三、源码分析

like、rlike、regexp使用的UDF如图

由图可见rlike和regexp使用相同的UDF类都为UDFRegexp.class，不同于like使用UDFLike.class；

接来下我们再分别看一下UDFRegexp.class和UDFLike.class的代码。

1.UDFLike.class

/**
 * UDFLike.
 *
 */
@Description(name = "like",
    value = "_FUNC_(str, pattern) - Checks if str matches pattern",
    extended = "Example:\n"
    + "  > SELECT a.* FROM srcpart a WHERE a.hr _FUNC_ '%2' LIMIT 1;\n"
    + "  27      val_27  2008-04-08      12")
@VectorizedExpressions({FilterStringColLikeStringScalar.class})
public class UDFLike extends UDF {
  private final Text lastLikePattern = new Text();
  private Pattern p = null;

  // Doing characters comparison directly instead of regular expression
  // matching for simple patterns like "%abc%".
  private enum PatternType {
    NONE, // "abc"
    BEGIN, // "abc%"
    END, // "%abc"
    MIDDLE, // "%abc%"
    COMPLEX, // all other cases, such as "ab%c_de"
  }

  private PatternType type = PatternType.NONE;
  private final Text simplePattern = new Text();

  private final BooleanWritable result = new BooleanWritable();

  public UDFLike() {
  }

  public static String likePatternToRegExp(String likePattern) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < likePattern.length(); i++) {
      // Make a special case for "\\_" and "\\%"
      char n = likePattern.charAt(i);
      if (n == '\\'
          && i + 1 < likePattern.length()
          && (likePattern.charAt(i + 1) == '_' || likePattern.charAt(i + 1) == '%')) {
        sb.append(likePattern.charAt(i + 1));
        i++;
        continue;
      }

      if (n == '_') {
        sb.append(".");
      } else if (n == '%') {
        sb.append(".*");
      } else {
        sb.append(Pattern.quote(Character.toString(n)));
      }
    }
    return sb.toString();
  }

  /**
   * Parses the likePattern. Based on it is a simple pattern or not, the
   * function might change two member variables. {@link #type} will be changed
   * to the corresponding pattern type; {@link #simplePattern} will record the
   * string in it for later pattern matching if it is a simple pattern.
   * <p>
   * Examples: <blockquote>
   *
   * <pre>
   * parseSimplePattern("%abc%") changes {@link #type} to PatternType.MIDDLE
   * and changes {@link #simplePattern} to "abc"
   * parseSimplePattern("%ab_c%") changes {@link #type} to PatternType.COMPLEX
   * and does not change {@link #simplePattern}
   * </pre>
   *
   * </blockquote>
   *
   * @param likePattern
   *          the input LIKE query pattern
   */
  private void parseSimplePattern(String likePattern) {
    int length = likePattern.length();
    int beginIndex = 0;
    int endIndex = length;
    char lastChar = 'a';
    String strPattern = new String();
    type = PatternType.NONE;

    for (int i = 0; i < length; i++) {
      char n = likePattern.charAt(i);
      if (n == '_') { // such as "a_b"
        if (lastChar != '\\') { // such as "a%bc"
          type = PatternType.COMPLEX;
          return;
        } else { // such as "abc\%de%"
          strPattern += likePattern.substring(beginIndex, i - 1);
          beginIndex = i;
        }
      } else if (n == '%') {
        if (i == 0) { // such as "%abc"
          type = PatternType.END;
          beginIndex = 1;
        } else if (i < length - 1) {
          if (lastChar != '\\') { // such as "a%bc"
            type = PatternType.COMPLEX;
            return;
          } else { // such as "abc\%de%"
            strPattern += likePattern.substring(beginIndex, i - 1);
            beginIndex = i;
          }
        } else {
          if (lastChar != '\\') {
            endIndex = length - 1;
            if (type == PatternType.END) { // such as "%abc%"
              type = PatternType.MIDDLE;
            } else {
              type = PatternType.BEGIN; // such as "abc%"
            }
          } else { // such as "abc\%"
            strPattern += likePattern.substring(beginIndex, i - 1);
            beginIndex = i;
            endIndex = length;
          }
        }
      }
      lastChar = n;
    }

    strPattern += likePattern.substring(beginIndex, endIndex);
    simplePattern.set(strPattern);
  }

  private static boolean find(Text s, Text sub, int startS, int endS) {
    byte[] byteS = s.getBytes();
    byte[] byteSub = sub.getBytes();
    int lenSub = sub.getLength();
    boolean match = false;
    for (int i = startS; (i < endS - lenSub + 1) && (!match); i++) {
      match = true;
      for (int j = 0; j < lenSub; j++) {
        if (byteS[j + i] != byteSub[j]) {
          match = false;
          break;
        }
      }
    }
    return match;
  }

  public BooleanWritable evaluate(Text s, Text likePattern) {
    if (s == null || likePattern == null) {
      return null;
    }
    if (!likePattern.equals(lastLikePattern)) {
      lastLikePattern.set(likePattern);
      String strLikePattern = likePattern.toString();

      parseSimplePattern(strLikePattern);
      if (type == PatternType.COMPLEX) {
        p = Pattern.compile(likePatternToRegExp(strLikePattern));
      }
    }

    if (type == PatternType.COMPLEX) {
      Matcher m = p.matcher(s.toString());
      result.set(m.matches());
    } else {
      int startS = 0;
      int endS = s.getLength();
      // if s is shorter than the required pattern
      if (endS < simplePattern.getLength()) {
        result.set(false);
        return result;
      }
      switch (type) {
      case BEGIN:
        endS = simplePattern.getLength();
        break;
      case END:
        startS = endS - simplePattern.getLength();
        break;
      case NONE:
        if (simplePattern.getLength() != s.getLength()) {
          result.set(false);
          return result;
        }
        break;
      }
      result.set(find(s, simplePattern, startS, endS));
    }
    return result;
  }

}

2.UDFRegexp.class

/**
 * UDFRegExp.
 *
 */
@Description(name = "rlike,regexp",
    value = "str _FUNC_ regexp - Returns true if str matches regexp and "
    + "false otherwise", extended = "Example:\n"
    + "  > SELECT 'fb' _FUNC_ '.*' FROM src LIMIT 1;\n" + "  true")
@VectorizedExpressions({FilterStringColRegExpStringScalar.class})
public class UDFRegExp extends UDF {
  static final Log LOG = LogFactory.getLog(UDFRegExp.class.getName());

  private final Text lastRegex = new Text();
  private Pattern p = null;
  private boolean warned = false;

  private final BooleanWritable result = new BooleanWritable();

  public UDFRegExp() {
  }

  public BooleanWritable evaluate(Text s, Text regex) {
    if (s == null || regex == null) {
      return null;
    }
    if (regex.getLength() == 0) {
      if (!warned) {
        warned = true;
        LOG.warn(getClass().getSimpleName() + " regex is empty. Additional "
            + "warnings for an empty regex will be suppressed.");
      }
      result.set(false);
      return result;
    }
    if (!regex.equals(lastRegex) || p == null) {
      lastRegex.set(regex);
      p = Pattern.compile(regex.toString());
    }
    Matcher m = p.matcher(s.toString());
    result.set(m.find(0));
    return result;
  }

}

四、总结

like与rlike的使用对比总结：rlike功能和like功能大致一样，like是后面只支持简单表达式匹配（_%）,而rlike则支持标准正则表达式语法。所以如果正则表达式使用熟练的话，建议使用rlike，功能更加强大。所有的like匹配都可以被替换成rlike。反之，则不行。但是注意：like是从头逐一字符匹配的，是全部匹配，但是rlike则不是,可以从任意部位匹配，而且不是全部匹配。