Welcome back! We hope that you’re enjoying these introductory blogs about Oracle Cloud Infrastructure (OCI) Data Integration.
Today, we’re learning about the SQL override feature in OCI Data Integration. In this blog post, we walk you through an example of how you can provide custom SQL queries in OCI Data Integration at source operator level. Now, you can directly provide complex SQL queries that act as a source in the data flow and data loader task. You can take the application of databases and ensure that the complex queries can run at the database level and process only the wanted output in Data Integration.
In this use case, with the help of SQL queries, we identify the employees, reporting manager, and their position in the organization and use this SQL query in the source operator of the data flow for performing further transformations.
Prerequisites
- Workspace, projects, and applications have been created. To know more, refer to our blogs on Data Integration and Oracle documentation.
- Define data assets for source and target in the workspace.
Let’s explore how to use Custom SQL queries in OCI Data Integration
In this example, we use the following source and target table:
- Employees
- Positions
- Employees_details
The employees table contains employee_key and manager_id. Using self-Join, we can find employees and their reporting managers. Then using the position key from the position table, we can find an employee’s position in the organization.
Implementation
The following canvas screenshot shows the data flow implemented in OCI Data Integration. Here, with the help of expression and left outer join, we find the employee, their experience (in years), and reporting manager. Then we join the position tables to find the positions of each employee in the organization.
Figure 1: Data Flow implementation (without using custom SQL query)
Before loading into target tables, the data looks like the following example in the JOIN_BY_POSITIONS operator:
Figure 2: Data Xplorer tab of JOIN_BY_POSITIONS operator
Figure 3: Preview of final data in JOIN_BY_POSITION operator
Now, you can achieve this scenario using Custom SQL query. You can directly provide the SQL query at the source operator level and perform the transformations in the SQL query.
To specify the custom SQL query in the source operator, perform the following steps:
- Add the source operator in the canvas.
- Select the data asset, connection, and schema.
- Click Select for your data entity.
- Select Enter Custom SQL.
- When clicking Enter Custom SQL, we can provide a single SQL statement in a SQL editor and configure the data entities for your source operator.
- Click the Select button. SQL Query is selected as the entity with the name, identifier number].
Figure 4: Data Entity panel showing the Enter Custom SQL option
Figure 5: Data Entity panel showing the SQL Editor, Validate button, and validation status
To preview the data of a specified SQL query, you can click the data tab of the source operator.
Figure 6: Data Xplorer tab of source operator
Figure 7: Data preview of the custom SQL query in source operator
Now, join this source operator with the position entity using inner join to find the positions of employees in the organization. The result looks like the following data flow.
Figure 8: Data Flow implementation with SQL entity
The data tab of JOIN_BY_POSITION operator shows how the final data looks before loading into the target table.
Figure 9: Data Xplorer tab of the JOIN_BY_POSITIONS operator
Figure 10: Preview of the final data in JOIN_BY_POSITION node
You can also assign the parameter to this SQL override entity, which allows you to reuse the same data flow logic with other similar schema, such as a production schema, or to provide the custom SQL query during runtime.
To assign the parameter to SQL entity, use the following steps:
- Click Assign Parameter, next to the SQL entity.
- Click Add New Parameter.
- In the Add New Parameter panel, provide the parameter name.
- Keep the existing or add new SQL query and click Add.
Figure 11: Assign Parameters panel
You can change these assigned parameters in the integration task or during runtime by clicking Edit in Configure Parameters. To learn more about parameters, check out Using Parameters in Oracle Cloud Infrastructure (OCI) Data Integration and creating and running a data flow using integration tasks.
Conclusion
In this blog, we explained how to provide custom SQL queries in OCI Data Integration at the data entity level. We provided a complex SQL query as part of source in Data Integration and were able to perform transformations, reducing four operators (two sources, expression, and joiner operators) into single source operator. Compare Figure 1 and Figure 8. The SQL override feature also have the following advantages:
- We can capture complex transformation and joins through SQL query instead of designing in the data flow through transformations.
- We can use some of the transformations available in source system but not yet supported in Data Integration Service operators.
- We can provide optimized queries and get better performance.
We hope that this blog helps as you learn more about Oracle Cloud Infrastructure Data Integration. For more information, check out the tutorials and documentation. Remember to check out all the blogs on OCI Data Integration!