Performing Multiple Operations on the Same TableWe need a SELECT query with suitable clauses/operators for the intended operation. We can use separate Pipelines to build each query. Alternatively, we can also use the ELT Copy Snap to create multiple copies of the initial SELECT query and then use suitable Snaps for the intended operations. In this example, we want to sort the records in the table and filter the records in the table. To sort the records, we need a query with the ORDER BY clause. To filter records, we need a query with the WHERE clause. This Pipeline shows how we can use the ELT Copy, ELT Sort, and ELT Filter Snaps to accomplish these tasks.
First, we build a SELECT query to read the target table. To do so, we can use an ELT Select Snap, in this example: Read Part A. This Snap is configured to output a SELECT * query to read the target table in the database. Additionally, this Snap is also configured to show a preview of the SELECT query's execution:
A preview of the output from the ELT Select Snap is shown below:
Then, we add an ELT Copy Snap to the ELT Select Snap, so that the output of the ELT Select Snap is replicated. Each of the output views in the ELT Copy Snap contains the following output:
We can connect the appropriate Snaps to each of the ELT Copy Snap output views. In this example, we want to sort the records and filter them. So we add the ELT Sort Snap to one of the output views and the ELT Filter Snap to the other output view. We use the following configuration for the ELT Sort Snap, since we want to sort the table records based on the values in the CUST_ID column in the ascending order. This Snap builds the following query based on this configuration:
Since we want to filter the records based on the value in the GRADE column in the table, we configure the ELT Filter Snap to do so:
This Snap builds the following query based on this configuration:
We can also add ELT Insert-Select Snaps downstream of these Snaps and write the result of the queries into other tables. |