Enrichment node

Find out more about the enrichment database node that is available in Event Processing.

Database

In situations where the data in the source table might not offer significant insights on its own, establishing connections with external databases and integrating their data can yield a more comprehensive result.

With the database node, you can retrieve data from external databases (PostgreSQL, MySQL, or Oracle) and integrate the data with the events within your workflow.

Note: Event Processing can be configured to connect to a secure PostgreSQL or MySQL database or Oracle. Contact your system administrator if you encounter issues while configuring the database node to communicate with a secure PostgreSQL, MySQL, or an Oracle database.

Adding a database node

To add a database node, complete the following steps.

  1. Ensure that a stream of events is available, from an Event Source node or from the output of any previously configured node.
  2. In the Palette, under Enrichment, drag the Database node into the canvas.

  3. Connect the node to an event source by dragging the Output Port from a source node into the Input Port of this node. A purple checkbox unconfigured_node icon is displayed on the database node indicating that the node is yet to be configured.

  4. Hover over the node and click Edit icon Edit to configure the node.

The Configure database enrichment window appears.

Configuring a database node

To configure a database node, complete the following steps.

  1. In the Details section, enter a name for your node. The output stream of events from this node will be referred with the name you entered.

  2. In the Connect to database, enter the URL of the secured database.

    PostgreSQL example:

    jdbc:postgresql://<host>:<port>/<database>?<configuration>

    MySQL example:

    jdbc:mysql://<host>:<port>/<database>?<configuration>

    Event Processing 1.1.1 icon Oracle example:

    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=<protocol>)(PORT=<port>)(HOST=<host>))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))

    Where:

    • <host> is the hostname of the database server.
    • <port> is the port number of the database server.
    • <database> is the name of the database that contains the table to be used for enrichment.
    • <configuration> is the list of configuration parameters for the connection.
    • <protocol> is used in order to activate SSL in the JDBC thin client.
    • <service_name> is used by the database to register itself with a listener.
  3. Click Next to open the Access Credentials pane. Enter your username and password (if prompted).
  4. Click Next. The Table selection pane is displayed.

  5. To choose a table from the provided database, you can either search for the table name in the search box, or select the radio button corresponding to the table name that you want to process events from.

    Important: Event Processing supports:

    • Lowercase names for tables and columns in PostgreSQL databases
    • Uppercase names for tables and columns in Oracle databases

  6. A preview of the schema of the selected table, along with its associated constraints, is displayed in the Data table preview. Click Next.

    Note: In the Data table preview, some data types under the Type column are listed as Not supported. These data types are not supported by Event Processing and the corresponding fields cannot be used to enrich events.

    The following data types in the remote database table are supported by Event Processing:

    Data type PostgreSQL MySQL Event Processing 1.1.1 icon Oracle
    BIGINT ✓ ✓  
    BIGSERIAL ✓    
    BINARY_FLOAT     ✓
    BINARY_DOUBLE     ✓
    BLOB     ✓
    BOOLEAN ✓ ✓  
    BYTES   ✓  
    CHAR     ✓
    CLOB     ✓
    CHARACTER ✓    
    CHARACTER VARYING ✓    
    DATE ✓ ✓ ✓
    DECIMAL ✓ ✓  
    DOUBLE PRECISION ✓   ✓
    FLOAT   ✓ ✓
    INTEGER ✓ ✓  
    NUMBER     ✓
    NUMERIC ✓    
    RAW     ✓
    REAL     ✓
    SMALLINT ✓   ✓
    SMALLSERIAL ✓    
    SERIAL ✓    
    STRING   ✓  
    TEXT ✓    
    TIME   ✓  
    TIMESTAMP ✓ ✓ ✓
    VARCHAR ✓   ✓

  7. In the Match Criteria pane, define your expression by using the property from the source, and the table field.

    For example, the incoming event has a field country_code with values such as 866, 453, 123 and you wish to lookup a human readable name from a database table.

    The match criteria is used as the join condition between the event and the database table.

    event.country_code = database_table.country_code

    Alternatively, you can use the assistant to create an expression. Select Assistant at the right end of the text-box to open the assistant. The assistant offers two separate drop-down lists of properties, for the source table attributes and the database table attributes with constraints, enabling you to construct the expression.

  8. After you defined an expression, click Next to open the Enrich Properties pane. Include or reject the database table fields for further processing.

  9. Click Next to open the Output properties pane, which contains both the input fields from the preceding node, and the previously chosen database table fields. Remove the fields that you do not want to be visible in the output.

    Note: To rename properties, hover over a property, and click the Edit icon Edit icon.

  10. Scroll down and click Configure to complete the configuration.

A green checkbox green checkbox appears on the database node if the database node is configured correctly. If there is any error in your configuration, a red checkbox red checkbox appears.

User actions are saved automatically. For save status updates, see the canvas header.