General Principles Add Missing Dates Add text column Add Total Rows Aggregate Append Compare Text Columns Argmax Argmin Concatenate columns Convert Convert date to text Convert text to date Cumulated sum Custom step Delete column(s) Geographically dissolve data Duplicate Duration Evolution Extract date information Extract substring Fill null Filter Formula Geographical hierarchy Get unique groups/values Hierarchical rollup ifthenelse Join Keep column(s) Moving Average Percentage Pivot Rank Rename Replace Geographical simplification Sort Split column Column's Statistics To lowercase To uppercase Top N rows Unpivot Waterfall
This document describes the steps that can be used in our SQL Translator pipeline. A step is the unit of transformation generated by a user interaction with the SQL visual query builder.
The steps were implemented using the following concepts:
- A step is named following this convention: **
- A pipeline starts with a select step using the query provided by the user like below
WITH SELECT_STEP_0 AS (<USER'S QUERY STRING>)
- Each step concatenates the resulting query string it generates to the query string received in input
For example, a SelectStep followed by a FilterStep results in
WITH SELECT_STEP_0 AS (<USER'S QUERY STRING>), FILTER_STEP_1 AS (SELECT * FROM SELECT_STEP_0 WHERE PRICE > 10)
- Each step updates the final selection query with its name
For example, the query string sent to the data provider for a SelectStep followed by a FilterStep would be
WITH SELECT_STEP_0 AS (<USER'S QUERY STRING>), FILTER_STEP_1 AS (SELECT * FROM SELECT_STEP_0 WHERE PRICE > 10) SELECT * FROM FILTER_STEP_1
The pipeline alone is not sufficient to be able to produce the query. SQL required the exhaustive list of columns to select from the moment we want to exclude or rename one.
For this reason, while crafting the query, the translator needs to request the columns names, once per source table.
It keeps them in a
MetadataManager that get transmitted and updated across steps.