You can add ‌SQL transform steps to your pipelines. SQL transform steps enable you to modify the incoming pipeline data by running SQL queries. 

The SQL transform step enables you to use SQL queries to modify data as it moves through a pipeline. At each step of the pipeline, you can use SQL transforms to: 

  • Select, summarize, and filter data.
  • Calculate new columns.
  • Combine data from multiple sources.

SQL dialect used in SQL transform steps

SQL transform steps use the Apache Calcite SQL dialect and are limited to Data Query Language (DQL), for example SELECT statements. For information about the SQL syntax, see this Apache Calcite SQL language reference page. 

Add SQL transform steps

To add a SQL transform steps to a pipeline:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Pipelines from the left-side panel.
  4. Create a new pipeline or edit an existing pipeline.
  5. Select the add icon  that appears between the Source and Sink nodes in the designer view.
  6. Select SQL transform from the Add new step right-side panel. 
  7. Select the SQL transform node.
  8. Enter the SQL transform name in the Label field. 
  9. Select Configure query and complete these steps on the Configure query modal:
    1. Enter the SQL query in the middle section of the modal. 
    2. Review your available columns in the left-side panel. 
    3. Review your output columns in the right-side panel. 
    4. Select Validate query to check the correctness of the syntax.
      If the validation fails, you need to correct your query to continue. 
    5. Select Save to save the query or Cancel to discard your changes. 
  10. Optionally, select the Add source node to add additional sources to the SQL transform step.
  11. Select Publish, and then select Run to execute the pipeline. 

Considerations on SQL query

Within the SQL query, the incoming data to the step is referred to by its label. If the SQL query needs a column name with a reference to the source, include the source label followed by a dot and then the column name.

If you have two source labels called Employees and EmployeeFilter, you can combine both sources in the SQL query. It can be written as follows:

select
   employees.*
from
   employees, employeefilter
where
   employees.COUNTRY_CODE = employeefilter.Country_Code

The editor provides SQL syntax checking and automatic code completion.

If column names or dataset names include spaces, enclose the names in backticks:

select
   *
from
   empdetails
where
   empdetails.`leave date` is not NULL

Note: The SQL query is case-sensitive.