Generate SQL SELECT from Pipeline Actions

This example shows how to push the work of a DataPipeline Foundations pipeline into the database. Pipeline actions such as AggregateGroupFieldsAction, RenameFieldsAction and SortFieldsAction normally configure readers that aggregate, rename and sort records in memory. Each of them can also render the same work as a SQL SELECT with generateSqlSelect(), and because the result is a Select that doubles as a query source, the actions chain into nested sub-selects.

Java Code Listing

package com.northconcepts.datapipeline.foundations.examples.pipeline;

import com.northconcepts.datapipeline.foundations.pipeline.action.aggregate.AggregateGroupFieldsAction;
import com.northconcepts.datapipeline.foundations.pipeline.action.aggregate.AggregateGroupFieldsAction.GroupByField;
import com.northconcepts.datapipeline.foundations.pipeline.action.aggregate.AggregateGroupFieldsAction.OperatorType;
import com.northconcepts.datapipeline.foundations.pipeline.action.transform.RenameFieldsAction;
import com.northconcepts.datapipeline.foundations.pipeline.action.transform.SortFieldsAction;
import com.northconcepts.datapipeline.jdbc.sql.select.Select;
import com.northconcepts.datapipeline.jdbc.sql.select.TableQuerySource;

public class GenerateSqlSelectFromPipelineActions {

    public static void main(String[] args) {
        AggregateGroupFieldsAction aggregate = new AggregateGroupFieldsAction()
                .add(new GroupByField("Rating", "Rating", OperatorType.GROUPBY, true))
                .add(new GroupByField("Account", "Accounts", OperatorType.COUNT, true))
                .add(new GroupByField("Balance", "TotalBalance", OperatorType.SUM, true));

        RenameFieldsAction rename = new RenameFieldsAction()
                .add("Rating", "CreditRating")
                .add("Accounts", "AccountCount")
                .add("TotalBalance", "TotalBalance");

        SortFieldsAction sort = new SortFieldsAction()
                .add("TotalBalance", false);

        Select select = aggregate.generateSqlSelect(new TableQuerySource("credit_balance"));
        select = rename.generateSqlSelect(select.setNestedAlias("totals"));
        select = sort.generateSqlSelect(select.setNestedAlias("renamed"));

        System.out.println(select.setPretty(true).getSqlFragment());
    }

}

Code Walkthrough

1. An AggregateGroupFieldsAction is configured with three GroupByField entries, each naming a source field, a target field, an OperatorType and whether nulls are excluded: group by Rating, count Account as Accounts and sum Balance as TotalBalance.
2. A RenameFieldsAction renames Rating to CreditRating and Accounts to AccountCount, and maps TotalBalance to itself. A rename action projects only the fields in its mapping, so that self-mapping is what carries TotalBalance through to the outer query.
3. A SortFieldsAction sorts by TotalBalance; the false argument means descending.
4. aggregate.generateSqlSelect(new TableQuerySource("credit_balance")) produces a Select over the credit_balance table with the grouped column, the two aggregate expressions and a GROUP BY clause.
5. setNestedAlias("totals") names that query for use as a derived table (MySQL requires an alias on every derived table), and rename.generateSqlSelect(...) wraps it in a sub-select projecting the renamed columns. setNestedAlias("renamed") and sort.generateSqlSelect(...) wrap it once more to add the ORDER BY clause.
6. select.setPretty(true).getSqlFragment() renders the statement with indentation and prints it.

Console Output

SELECT *
FROM (
  SELECT Rating as CreditRating, Accounts as AccountCount, TotalBalance
  FROM (
    SELECT Rating, count(Account) as Accounts, sum(Balance) as TotalBalance
    FROM credit_balance
    GROUP BY Rating
  ) totals
) renamed
ORDER BY TotalBalance DESC