Skip to main content Skip to complementary content
Announcements

Using context variables to use different connection strings at execution time

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.

A pipeline shows a MySQL dataset with a context variable as the pipeline source, a Type converter and an Aggregate processor, and a Test dataset as the pipeline destination.

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.
    The preview panel shows a sample of the customer MySQL table used as the pipeline source.
  4. Click Plus 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 Plus 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.

    The preview panel of the Aggregate processor shows the output record, the number 52904 with 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.
      The Logs panel indicates that 52 records have been read.
    • no context variables were set in this pipeline.
      The Logs panel indicates that the original MySQL URL has been used, therefore no context variables were used at runtime.
  14. Go back to the Connection tab of the MySQL contact table source to add and assign a variable:
    1. Click the Context variable 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 Context variable icon will be displayed to indicate that a variable has been set in the pipeline.

      In the 'Assign a variable' window, the new variable is selected and the 'Assign' button is enabled.
  15. On the top toolbar of Talend Cloud Pipeline Designer, click the Run button to open the panel allowing you to select your run profile.
  16. Select your run profile in the list (for more information, see Run profiles), then click Run 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).
    The Logs panel indicates that 1153 records have been read.
  • You can also see the context variable value used to retrieve the data from the Production table at execution time.
    The Logs panel indicates that the MySQL URL set in the context variable value has been applied at runtime.

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 – let us know how we can improve!

Leave your feedback here