Skip to main content Skip to complementary content

Defining a query template for a specific database

A query template defines the query logic required to analyze columns against regular expressions.

The steps to define a query template in Talend Studio include the following:

  • Create a query template for a specific database,
  • Set the database-specific regular expression if this expression is not simple enough to be used with all databases.

The below example shows how to define a query template specific for the Microsoft SQL Server database.

Before you begin

You have selected the Profiling perspective.

Procedure

  1. In the DQ Repository tree view, expand Libraries > Indicators.
  2. Expand System Indicators > Pattern Matching.
    Pattern Matching node expanded in the DQ Repository tree view.
  3. Double-click Regular Expression Matching, or right-click it and select Open from the contextual menu.

    The corresponding sections are displayed to show the indicator metadata and its definition.

    Overview of the Indicator Metadata and the Indicator Definition sections.

    You need now to add to the list of databases the database for which you want to define a query template. This query template will compute the regular expression matching.

  4. Click the [+] button at the bottom of the Indicator Definition section to add a field for the new template.
    Overview of the Indicator Definition section.
  5. In the new field, click the arrow and select the database for which you want to define the template. In this example, select Ingres.
  6. Copy the indicator definition of any of the other databases.
  7. Click the Edit... button next to the new field.

    The Edit expression dialog box is displayed.

    Overview of the Edit expression dialog box.
  8. Paste the indicator definition (template) in the Expression box and then modify the text after WHEN in order to adapt the template to the selected database. In this example, replace the text after WHEN with WHEN REGEX.
  9. Click OK. The new template is displayed in the field.
  10. Click the save icon on top of the editor to save your changes.

Results

You have finalized creating the query template specific for the Ingres database. You can now start analyzing the columns in this database against regular expressions.

If the regular expression you want to use to analyze data on this server is simple enough to be used with all databases, you can start your column analyses immediately. If not, you must edit the definition of the regular expression to work with this specific database, Ingres in this example.

If an analysis with a user-defined indicator runs successfully at least one time and later the indicator definition template for the database is deleted, the analysis does not fail. It keeps running successfully because it uses the previously generated SQL query.

For more information on how to set the database-specific regular expression definition, see Editing a regular expression or a SQL pattern.

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