​

Intro

This article describes how to configure the available Utility tiles in Magic ETL. Learn how to use tiles in a Magic ETL DataFlow.

---

• Add Constants tile
• Add Formula tile
• Alter Columns tile
• Duplicate Column tile
• Get Schema tile
• Limit tile (beta)
• Meta Select tile
• Order tile (beta)
• Select Columns tile
  • Rename fields with the Select Columns tile (video)
• Series tile (beta)
• SQL tile
  • Known limitations
  • Configure the SQL tile
  • Leverage analytic and window functions and aggregations
• Value Mapper tile

---

​

Add Constants Tile

The Add Constants tile lets you add a column with constant values. After dragging an Add Constants tile to the canvas, follow these steps to configure it:

1. Select the tile to open the tile editor below the canvas. Complete the remaining steps inside the tile editor.
2. (Optional) Rename the tile by clicking Edit (pencil icon) and entering the name you want.
3. Enter the name of the new column.
4. Select the column type.

   Text	Contains text and numbers (which are treated as text).
   Floating Decimal	Contains numbers in decimal notation.
   Fixed Decimal	Contains decimal numbers with a fixed number of digits after the decimal point.
   Integer	Contains numbers without a decimal part.
   Date	Contains date values.
   Timestamp	Contains date and time values.

5. Enter the constant value, date, date and time, or leave it blank.
   
6. Select Done.
   
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.

​

Add Formula Tile

The Add Formula tile lets you add a column with constant values. After dragging an Add Formula tile to the canvas, follow these steps to configure it:

1. Select the tile to open the tile editor below the canvas. Complete the remaining steps inside the tile editor.
2. (Optional) Rename the tile by clicking Edit (pencil icon) and entering the name you want.
3. Enter the name of the new column.
4. Enter the formula you would like to perform. Learn how to write functions for the Add Formula tile, including with the help of the AI SQL Assistant.
5. Select Done.
   
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.

​

Alter Columns

The Alter Columns tile lets you change a column’s data type (for example, from number to text). For more information about data types, see Understanding Chart Data. Example For example, the Alter Columns tile transforms the data in the first image to the data in the second image. After dragging an Alter Columns tile to the canvas, follow these steps to configure it:

1. Select the tile to open the tile editor below the canvas. Complete the remaining steps inside the tile editor.
2. (Optional) Rename the tile by clicking Edit (pencil icon) and entering the name you want.
3. For each column where you want to change the data type, do the following:
   1. Select the column you want to set.
   2. (Optional) Rename the column if needed.
   3. Select the new data type for the column. The available data types are listed above.
      
   4. (Optional) Repeat as many times as needed.
4. Select Done.
   
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.

​

Duplicate Column Tile

The Duplicate Column tile lets you duplicate a column to replace a column of the same data type. For example, you can duplicate a column with string values to replace another column with string values. Both columns must already exist in the DataSet. After dragging a Duplicate Column tile to the canvas, select it to open the tile editor and follow these steps to configure it:

1. Select the tile to open the tile editor below the canvas. Complete the remaining steps inside the tile editor.
2. (Optional) Rename the tile by selecting Edit (pencil icon) and entering the name you want.
3. Choose the column in the DataSet to replace.
4. Choose the column in the DataSet to duplicate. You can only choose from columns that have the same data type as the column you are replacing.
5. Select Done.
   
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.

​

Get Schema Tile

The Get Schema tile generates a table with schema information for the input table that can be modified and consumed by the Meta Select tile. Learn how to use the Meta Select and Get Schema tiles together.

​

Limit Tile (Beta)

The Limit tile allows you to limit the number of rows of data included in your output. You can apply an offset to the limit, which specifies the zero-based row index for the limit to start on. You may use the Limit tile together with the Order tile to choose which rows are included in the output. There are two steps to configuring the Limit tile:

1. Enter the number of rows to include—the limit.
2. Enter the zero-based row index for the limit to start on—the offset. If you want the included rows to begin at row 1, leave this blank.

​

Meta Select Tile

The Meta Select tile uses one table to define how to select columns from another table. Learn how to use the Meta Select tile.

​

Order Tile (Beta)

The Order tile allows you to view the values in a specific column in Ascending or Descending order. There are also advanced options for String Collation and Locale. You can include multiple columns and their position in the list controls the position in which they are sorted. This means that the first column is sorted first, then the rows will be sorted within that sort by the second column chosen and so on.

​

Select Columns Tile

The Select Columns tile lets you select columns to add to the output, reorder columns, and rename column headings. After dragging a Select Columns tile to the canvas, select it to open the tile editor and follow these steps to configure it:

1. Select the tile to open the tile editor below the canvas. Complete the remaining steps inside the tile editor.
2. (Optional) Rename the tile by selecting Edit (pencil icon) and entering the name you want.
3. (Optional) Select the columns you want to include (or exclude) by doing any of the following:
   1. Select Add Column and choose a column you want to include. Repeat to add other columns.
   2. Select Add All Columns, then exclude columns by selecting Remove (X icon) for the column.
      
4. (Optional) Reorder columns by using the handle beside a column name to drag it to the correct position for your configuration.
   
5. (Optional) Enter a new name for each column you want to rename in the appropriate field.
   
   Tip: You can preview the data transformed by a tile by running a preview, selecting the tile on the canvas, then viewing the Preview tab in the tile editor.

​

Rename Fields with the Select Columns Tile (video)

​

Series Tile (Beta)

The Series tile creates a column with a series of numbers up to the configured row count. This can be helpful for assigning ordinal values to rows to enable sorting or to assign unique identifiers (IDs). There are two steps to configuring the Series tile:

1. Enter the number of rows to generate in the new column.
2. Enter a name for the new column.

In this example, the new column will have 25 rows, and the name of the column will be Row count.

​

SQL Tile

The SQL tile allows you to write your own freeform SQL. It uses a MySQL-flavored syntax, and most MySQL functions are available. Also supported are the basics of Redshift syntax, Common Table Expressions (CTEs), analytic and window functions and aggregations, as well as most other standard SQL functions.

​

Known Limitations

• Stored procedures and variables are not supported.
• Non-SELECT statements are not supported.

​

Configure the SQL Tile

1. Drag the SQL tile onto the Magic ETL canvas from the Utility section in the left panel and connect it to a valid input tile. In the Code tab of the tile editor, the SQL pane is populated with a SELECT * from the input tile.
   
   Any tables you connect display on the left side of the tile editor.
   • • Select Down to expand a table to see a list of included columns.
     • Use the search bar to locate a specific column.
       
2. Write your own SQL in the code pane or use the AI SQL Assistant to create your SQL.

3. As needed, connect other tiles to the output of the SQL tile. You can use a mix of SQL tiles and standard Magic ETL tiles.

​

Leverage Analytic and Window Functions and Aggregations

Analytic and window functions and aggregations allow you to explore data iteratively through running totals, rankings, moving averages, and cumulative counts directly in your Magic ETL DataFlow. Use these analytic and window functions within your SQL tile:

• CUME_DIST
• DENSE_RANK
• LAG
• LEAD
• NTH_VALUE
• PERCENT_RANK
• RANK

Use these analytic and window aggregations within your SQL tile:

• APPROXIMATE_COUNT_DISTINCT
• AVG
• CORR
• COUNT, including COUNT(*) and COUNT(DISTINCT…)
• COVAR_POP
• COVAR_SAMP
• FIRST_VALUE, including FIRST_VALUE(…IGNORE NULLS)
• LAST_VALUE, including LAST_VALUE(… IGNORE NULLS)
• MAX
• MEDIAN
• MIN
• STDDEV_POP
• STDDEV_SAMP
• SUM
• VARIANCE_POP
• VARIANCE_SAMP

​

Convert SQL DataFlow to Magic ETL

The Edit as Magic ETL option is available for MySQL and Redshift DataFlows. Follow these steps to use it:

1. Open your MySQL or Redshift DataFlow in the Data Center.
   
2. Select Options (three vertical dots icon) > Edit as Magic ETL.
   
   Your DataFlow opens in a Magic ETL canvas.
   
   Important notes:

   • Needed tables are brought into the canvas as inputs.
   • Each existing transform is presented as its own SQL tile.
   • Sometimes, you may need to update the SQL to modify any unsupported operations or syntax. See our SQL expressions reference.
   • In this new DataFlow, using the Save As option saves a copy of the new Magic ETL DataFlow rather than modifying the original DataFlow.

​

Value Mapper Tile

The Value Mapper tile lets you search and replace string values in a specific column according to the pairs of string values you enter. (You might use the Value Mapper action to replace abbreviations or convert language codes.) You can either have replacement values overwrite an existing column or be in a new column. For example, the Value Mapper tile transforms this data from the first image below to the second. Follow these steps to configure the Value Mapper tile:

1. Select the tile to open the tile editor below the canvas. Complete the remaining steps inside the tile editor.
2. (Optional) Rename the tile by selecting Edit (pencil icon) and entering the name you want.
3. Select the column you want to search.
4. Select whether the values overwrite the values in the specified column or appear in a new column.
   1. (Conditional) If writing values to a new column, enter the name of the column.
5. Select whether to write the original value or a default value when a match is not found in a row.
   1. (Conditional) If writing a default value, enter the value.
6. For each value mapping you want, enter the value to search for and the value to replace with.
7. (Conditional) If you want to add a mapping, then click Add Mapping.
   Tip: You can preview the data transformed by a tile by running a preview, clicking the tile in the canvas, then clicking the Preview tab.