Skip to main content Skip to complementary content

Grouping data, writing aggregated data and dropping the source table

Procedure

  1. Double-click tSQLTemplateAggregate to open its Basic settings view.
    Basic settings view.
  2. On the Database Type list, select the relevant database type, and from the Component List, select the relevant database connection component if more than one connection is used.
  3. Enter the names for the database, source table, and target table in the corresponding fields and define the data structure in the source and target tables.
    The source table schema consists of three columns: First_Name, Last_Name and Country. The target table schema consists of two columns: country and total. In this example, we want to group citizens by their nationalities and count citizen number in each country. To do that, we define the Operations and Group by parameters accordingly.
  4. In the Operations table, click the [+] button to add one or more lines, and then click the Output column cell and select the output column that will hold the counted data from the drop-down list.
  5. Click the Function cell and select the operation to be carried on from the drop-down list.
  6. In the Group by table, click the [+] button to add one or more lines, and then click the Output column cell and select the output column that will hold the aggregated data from the drop-down list.
  7. Click the SQL Template tab to open the corresponding view.
    SQL template view.
  8. Click the [+] button twice under the SQL Template List table to add two SQL templates.
  9. Click on the first SQL template row and select the MySQLAggregate template from the drop-down list. This template generates the code to aggregate data according to the configuration in the Basic settings view.
  10. Do the same to select the MySQLDropSourceTable template for the second SQL template row. This template generates the code to delete the source table where the data to be aggregated comes from.
    Information noteNote:

    To add new SQL templates to an ELT component for execution, you can simply drop the templates of your choice either onto the component in the design workspace, or onto the component's SQL Template List table.

    Information noteNote:

    The templates set up in the SQL Template List table have priority over the parameters set in the Basic settings view and are executed in a top-down order. So in this use case, if you select MySQLDropSourceTable for the first template row and MySQLAggregate for the second template row, the source table will be deleted prior to aggregation, meaning that nothing will be aggregated.

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 – please let us know!

Leave your feedback here