Hop Expression Plugin

Overview

This plugin provides 4 transformations and 2 actions that use the power of an expression language.

Expression transformation calculate fields with expression.

Route transformation switches rows according to an expression condition.

Where transformation filter rows with expression condition.

Aggregate transformation uses aggregation functions to calculate values on groups of rows.

Set variable action provide the possibility to set variables or modify existing variables using expressions.

Loop (experimental) action provide the possibility to execute a workflow in a loop while a condition expression is true.

Expression language

An expression is a combination of one or more literal values, resolvable identifiers, operators and functions that evaluate to a value.

/**
 * Multi line comment
 */

case
  when REGION_ID<0 then Upper(Left(LABEL,5))
  when REGION_ID between 0 and 100 then InitCap(Substr("LABEL FR",8,50))
  else To_Char([YEAR]+1,'FM9999') || ' XXX '
end

• Supports 8 data types: Boolean Integer Number String Date Binary Json Inet

• Constant value: TRUE, FALSE, NULL, 'String', 1234, -45e-2, 0xF0A1, 0b10110111, DATE '2022-04-25', TIMESTAMP '2022-04-25 05:28:59', BINARY 'FA6CB', JSON '{"name":"John"}', INTERVAL 3 YEAR

• Array [1,4,8] and element at <array>[1]

• Logical operators NOT AND OR XOR

• Arithmetic operators + - * / %

• Comparison operators > >= < ⇐ = != <> BETWEEN IN IS TRUE IS FALSE IS NULL IS DISTINCT FROM

• Concatenate operator ||

• Conditional operators CASE WHEN COALESCE IF IFNULL NULLIF ZEROIFNULL NULLIFZERO GREATEST LEAST

• Pattern matching operators LIKE ILIKE SIMILAR TO

• Cast operator :: or CAST(<value> AS <type> FORMAT <format>)

• Over 200 scalar and aggregate functions (conditional, mathematical, trigonometry, conversion, bitwise…​).

• User Defined Function (UDF) support has metadata

• Use of comments to facilitate reading

• Are not case-sensitive, excepted for identifier

• Can be parenthesized to control the precedence order

• Optimized immediately after parsing, so the 5+XYZ+4*2+5 is optimized to XYZ+18 and has no impact on performance

Documentation are work in progress.

Extends with plugins

Use @FunctionPlugin annotation to create custom function in your plugin.

/**
 * The function compute Levenshtein distance.
 */
@FunctionPlugin
public class Levenshtein extends Function {

  public Levenshtein() {
    super("LEVENSHTEIN", true, ReturnTypes.INTEGER_NULLABLE, OperandTypes.STRING_STRING, OperatorCategory.STRING, "/docs/levenshtein.html");
  }

  @Override
  public Object eval(final IExpression[] operands) throws Exception {
    String str1 = operands[0].getValue(String.class);
    if (str1 == null)
      return null;
    String str2 = operands[1].getValue(String.class);
    if (str2 == null)
      return null;

    return Long.valueOf(StringUtils.getLevenshteinDistance(str1, str2));
  }
}

How to install

System Requirements

Apache Hop 2.7 or above. Web Hop is not supported because expression editor use JFace.

Manual Install

1. Unzip the plugin archive into the hop\plugins\misc directory

2. Restart Hop

Support

This plugin is provided as is, without any warranties, expressed or implied. This software is not covered by any Support Agreement.

License

Licensed under the Apache License, Version 2.0.