Using context variables to use different connection strings at execution time - Cloud

Talend Cloud Pipeline Designer User Guide
EnrichVersion
Cloud
EnrichProdName
Talend Cloud
EnrichPlatform
Talend Pipeline Designer
task
Administration and Monitoring > Monitoring executions
Administration and Monitoring > Monitoring logs
Data Governance > Filtering data
Data Quality and Preparation > Filtering data
Data Quality and Preparation > Managing datasets
Deployment > Deploying > Executing Pipelines
Design and Development > Designing Pipelines
Subscription

In this scenario context variables are added to override the connection credentials and thus to switch between a pre-Production database and a Production database at execution time.

Before you begin

  • You have previously created a connection to the system storing your source data, here a MySQL connection.

  • You have previously added the dataset holding your source data.

    Here, a table containing contact data including customer identifiers, names, addresses, countries, credit limits, etc.

  • You also have created the destination connection, here a Test dataset where you will store output logs.

Procedure

  1. Click ADD PIPELINE on the Pipelines page. Your new pipeline opens.
  2. Give the pipeline a meaningful name.

    Example

    Switch from a MySQL Pre-Prod to PROD table with context variables
  3. Click ADD SOURCE to open the panel allowing you to select your source data, here MySQL contact table. A sample of your data is displayed in the preview panel.
  4. Click and add a Type converter processor to the pipeline. The Configuration panel opens.
  5. Give a meaningful name to the processor; convert credit type for example.
  6. In the Converters area:
    1. Select .creditlimit in the Field path area, as you want to convert the records corresponding to the user financial credit limits.
    2. Select Double in the Output type list as you want to convert the credit limit values from a String to a Double type, which will then allow you to perform calculations on these values.
  7. Click SAVE to save your configuration.

    You can see that the records about credit limits are converted to a Double type.

  8. Click and add an Aggregate processor to the pipeline. The Configuration panel opens.
  9. Give a meaningful name to the processor; calculate average credit limit for example.
  10. In the Operation area:
    1. Select .creditlimit in the Field path area, as you want to perform a calculation operation on the records corresponding to the user financial credit limits.
    2. Select Average in the Operation list as you want to calculate the average credit limit for this list of customers.
  11. Click SAVE to save your configuration.

    You can see that the records about credit limits are converted to a Double type.

  12. Click the ADD DESTINATION item on the pipeline to open the panel allowing to select the Test dataset that will hold your output logs.
  13. (Optional) If you execute your pipeline at this stage, you will see in the logs that:
    • the pipeline was successfully executed and 52 records have been read.
    • no context variables were set in this pipeline.
  14. Go back to the Connection tab of the MySQL contact table source to add and assign a variable:
    1. Click the icon next to the JDBC URL parameter to open the [Add a variable] window.
    2. Give a name to your variable, PROD connection string data for example.
    3. Enter the variable value that will overwrite the default resource to be retrieved, jdbc:mysql://myprodconnectionstring.us-east-1.rds.amazonaws.com:3306/mytable for example.
    4. Enter a description if needed and click ADD.
    5. Now that your variable is created, you are redirected to the [Assign a variable] window that lists all context variables. Select yours and click ASSIGN.
      Your variable and its value are assigned to the JDBC URL parameter of the MySQL dataset, which means the JDBC URL that points to the production MySQL table will overwrite the JDBC URL you have defined previously. Instead of retrieving data from the pre-Production table, the Production table data will be retrieved.
    6. Click SAVE to save your configuration.

      Once the variable is assigned, the icon will be displayed to indicate that a variable has been set in the pipeline.

  15. On the top toolbar of Talend Cloud Pipeline Designer, select your run profile in the list (for more information, see Run profiles).
  16. Click the run icon to run your pipeline.

Results

Your pipeline is being executed, the data is aggregated and corresponds to the context variable you have assigned to the source and destination connection strings:
  • In the pipeline execution logs, you can see that a higher number of records have been read (1153).
  • You can also see the context variable value used to retrieve the data from the Production table at execution time.