Configuring normal lookup operations

You configure the Hive connector to perform a normal lookup on an Hive database.

  • To specify the format of the data records that the Hive connector reads from an Hive database, set up column definitions on a link.
  • Configure the Hive connector as a source for the reference data.

In a normal lookup, the connector runs the specified SELECT statement only one time; therefore, the SELECT statement cannot include any input parameters. The Lookup stage searches the result set data that is provided by the connector and looks for matches for the parameter sets that arrive in the form of records on the input link to the Lookup stage. A normal lookup is also known as an in-memory lookup because the lookup is performed on the cached data in memory.Typically you use a normal lookup when the target table is small enough that all of the rows in the table can fit in memory.

Typically you use a normal lookup when the target table is small enough that all of the rows in the table can fit in memory.

  1. Add a Lookup stage to the job design canvas, and then create a reference link from the Hive Connector stage to the Lookup stage.
  2. Double-click the Oracle Connector stage.
  3. From the Lookup Type list, select Normal.
  4. To save the changes, click OK.
  5. Double-click the Lookup stage.
  6. To specify the key columns, drag the required columns from the input link to the reference link.
    The columns from the input link contain values that are used as input values for the lookup operation.
  7. Map the input link and reference link columns to the output link columns and specify conditions for a lookup failure:
    1. Drag or copy the columns from the input link and reference link to your output link.
    2. To define conditions for a lookup failure, click the Constraints icon in the menu.
    3. In the Lookup Failure column, select a value, and then click OK. If you select Reject, you must have a reject link from the Lookup stage and a target stage in your job configuration to capture the rejected records.
    4. Click OK.
  8. Save, compile, and run the job.