The INPUT SQL operator allows you to retrieve data from external sources—primarily SQL databases (e.g., PostgreSQL, MS SQL Server, Oracle, MySQL, SQLite, as well as databases using ODBC or OleDb drivers) and Excel files—and transform them into a data stream that is placed on the operator’s output data bus.
Data is retrieved using the SQL query defined in the operator’s configuration. This query can use:
- project parameters,
- global variables,
- the GRAVITY metalanguage (e.g., for dynamically building queries or inserting values).
The output of the operator is a data bus containing records that correspond to the query result. The columns of this data bus are automatically matched to the structure of the data returned by the source.
The INPUT SQL operator is most commonly used as the first step in integration processes, where source data is retrieved from external databases or spreadsheets and then further processed within the GRAVITY application.
Example of a GRAVITY project using the INPUT SQL operator:
SELECTING AND CREATING A DATABASE CONNECTION
Go to the Physical link tab in the operator’s properties.
If you already have a connection defined, select it from the Connection dropdown. The connection fields will be populated according to the selected connection.
You can modify the connection details by clicking the Edit button.
If you do not yet have an appropriate database connection defined, you can create one using the Add button.
After entering the new connection details, you can test it using the Test connection button and save it in the system using the Save button.
Once saved, the connection will be available in the system under the name provided in the Name field.
CREATING A DATABASE QUERY
Go to the Statement tab to create the database query. The database query is written in SQL.
In the SQL query, you can use parameters defined in the Parameters tab. You can select a parameter using the parameter dictionary.
You can also use the metalanguage in SQL queries to append parts of the query conditionally, depending on whether a condition is met.
- Conditional expression syntax:
{[expression] ? “expression true” : “expression false”} - Expression that adds text wrapped in quotes:
{TO_SQLTEXT([text to insert])} - Expression that inserts text without any modification:
{TO_PURE_VALUE([text to insert])}
Example usage in SQL code:
select id, name from g.customer where {@id != null && @id != 0 ? " where and id = @id" : ""}
Example usage in a query:
select id, name, {TO_SQLTEXT(@email)} as email from g.customer
Example usage in a query:
select id, name from {TO_PURE_VALUE(@schema)}.customer
CONFIGURING OPERATOR PARAMETERS
You can declare any number of parameters that can be used in the SQL query. The created parameters become project parameters, which means they can also be used by any operator within the project.
INCREMENTAL DATA DOWNLOAD
Data can be retrieved incrementally: to enable this, go to the Incremental download tab and set the Incremental downloading parameter to With incremental action.
If you choose the incremental method, you must specify the key position column (incremental column). Additionally, you can specify conditions that must be met for the system to treat the column’s value as the last retrieved value once the processing is complete. The last retrieved value can be cleared using the CLEAR VALUE option.
