Skip to main content Skip to complementary content
Qlik Resources
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal

Using a reference validation rule

After setting up a validation rule, you can apply it on your Job designs. For example, let's apply a referential integrity check validation rule when updating a database with new data. However, before uploading those new data you want to make sure they match the ones already existing in the database.

This scenario is based on the reference check validation rule created in the Referential rule section of the Talend Studio User Guide.

Setting up the Job environment for a reference validation rule

Procedure

  1. From the Palette, place these components onto the design workspace: a database input component and a database output component, here tMysqlInput and tMysqlOutput, to upload the data, a tLogRow component to display the rejected data in the console and a tJava component to display the number of lines processed in the console.
  2. Connect the input and output database components using a Row > Main connection, and connect the tMysqlInput to the tJava components using a Trigger > OnSubjobOk connection.
    Information noteNote:

    You will be able to create the reject link between the tMysqlOutput and tLogRow components only when you will have applied the validation rule to the tMysqlOutput component.

    Screenshot of the Job in the Designer.

Configuring the components for using a reference validation rule

Procedure

  1. Double-click the tMysqlInput component to display its Basic settings.
    Screenshot of the component basic settings.
  2. Select Repository as Property type and click the three-dot button next to the field to retrieve the connection properties that corresponds to the metadata you want to check.
  3. Select Repository from the Schema drop-down list and click the three-dot button next to the field to retrieve the schema that corresponds to your database table.
  4. Click the three-dot button next to the Table field to select the table to check.
  5. Click Guess Query to automatically retrieve the query corresponding to the table schema.
  6. Double-click the tMysqlOutput component to display its Basic settings.
    Screenshot of the component basic settings.
  7. Select Repository as Property type and click the three-dot button next to the field to retrieve the connection properties that corresponds to the database table in which you want to load the new data.
  8. Click the three-dot button next to the Table field to select the table in which you will load the data.
  9. In the Action on table list, select Default and in the Action on data list, select the action corresponding to the ones defined in the validation rule you apply on the Job.
    Here, as you selected On insert and On update in the referential check validation rule you use, so select Update or insert to trigger the rule.
  10. If the schema of the input and output components did not synchronize automatically, click Sync columns and the schema of the input flow will automatically be retrieved.

Applying the validation rule and viewing the Job execution result

Procedure

  1. Click the Validation Rules tab in the Component view of tMysqlOutput.
    Screenshot of the component validation rules.
  2. Select the Use an existing validation rule check box to apply the validation rule to the component.
  3. In the Validation Rule Type list, select Repository and click the three-dot button to select the validation rule from the Repository Content window.
  4. Right-click tMysqlOutput, select Row > Rejects in the menu and drag to tLogRow to create a reject link between the two components.

    If you have enabled the Reject link option for this validation rule you can retrieve the rejected data to the reject flow.

  5. Double-click the tJava component to display its Basic settings.
    Screenshot of the component basic settings.
  6. In the Code field, type in the code that will display the number of updated, inserted, and rejected lines processed: 
    System.out.println("Updates: "+((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED"))+"\nInserts:
"+((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_INSERTED"))+"\nRejects:
"+((Integer)globalMap.get("tLogRow_1_NB_LINE")));
  7. Save your Job and press F6 to run it.
    Screenshot of the console after successfully running the Job.

Results

Valid data is inserted or updated in the database table and the console displays the rows rejected by the validation rule, along with the number of updates, inserts, and rejects processed in the Job.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here