SQL Query

This function allows you to write custom SQL queries against a data source. The SQL Query node can only be connected to data source nodes that represent SQL databases (SQL Server, MySQL, PostgreSQL, Oracle, etc). You can then inject an SQL Select statement into the node in order to return the required data set from the source.

Configure the SQL Query Node

Connect the SQL Query node to the source node; enter your SQL script in the SQL Query window (green arrow below) in the Properties panel. By default, the resulting table is called 'Custom Query' but this can be changed (red highlight). You can preview the resulting table from the Preview panel (blue highlight).

You can also click the SQL icon (yellow arrow) to open the SQL editor where you can easily access the data source's tables to write your script (see below for more on this).

In this example, the following Select statement was used to return a the profileKey, Income, Age, and Purchased Bike columns from the customerPofile table, wherever the number of children was greater than 2:

SELECT "profileKey", "Income", "Age", "Purchased Bike" FROM "customerProfile" WHERE "Children" > 2

SQL Editor

Open the SQL Editor from the Properties panel; the editor exposes the data source's schemas; you can easily add tables and columns to the SQL statement by double clicking or dragging and dropping. The editor also exposes any variables that have been configured in the Data Flow, enabling you to inject variables into the SQL statement.

Edit an SQL Select Node

Edit the SQL Select node from its Properties panel. You can change the table name, SQL statement, column selection, set incremental loading, and add a description.