SQLite mapper
[ | version 3.0]
You are viewing outdated version, see the latest version
Connector
The connector is permitted for use of the platform background agent.
Connector processing type: Both (Row by row & Bulk), Default type: Bulk!
Debug script enabled.
The SQLite mapper connector introduces small, fast, self-contained, server-less, full-featured SQL database engine allowing you to write and run SQL statments and thus manipulate data using the SQLite. For SQLite syntax and full list of supported commands and functions of the database engine, see documentation. The SQLite mapper provides an alternative to another connector for manipulating data - JS mapper.
Accessing input data
If the input schema is selected, the input data will be predefined in the InputData table (see Predefined Variables). Input data are always in the form of an array, even if data contains a single record only.
Predefined variables
| Variable | Data type | Description |
|---|---|---|
TaskRunID |
any | ID of the currently executed TaskRun. |
InputData |
table | The table filled with input data. This table will be deleted when the connector finishes the run. |
DataCheckpoint |
any | The value of stored column on last row from last TaskRun. |
EndpointTokenID |
any | When a task is executed via linked endpoint, the variable contains ID of the token used for the authorization (see table below). |
EndpointTokenName |
any | When a task is executed via linked endpoint, the variable contains Name of the token used for the authorization (see table below). |
| Authorization token | EndpointTokenID |
EndpointTokenName |
|---|---|---|
| None | null |
null |
| Primary | 0 |
"Primary" |
| Secondary | ID of the used secondary token |
Name of the used seconday token |
Returning output data
The output of the connector expects an array of objects that must match the output schema in the structure. The defined Statement represents the body of the function whose output is parsed and returned as the output of the connector itself. The last line of the Statement (see Configuration) must therefore be SELECT clause
Example
If the input schema is defined:
| Column | Data type |
|---|---|
| UserID | integer |
| UserLogin | string |
| CreatedDate | DateTime |
| EndpointTokenName | string |
If the output schema is defined:
| Column | Data type |
|---|---|
| ID | integer |
| Login | string |
and you want to return some custom data to the output, use the folowing statement:
SELECT
15 as ID,
'user15' as Login
or you want to return records from input to the output, use the following statement:
SELECT
UserID as ID,
UserLogin as Login
FROM InputData
or you want to return records from the input to the output using some Predefined variables, use the following statement:
SELECT
UserID as ID,
UserLogin as Login
FROM InputData, variables
WHERE InputData.EndpointTokenName = variables.EndpointTokenName
or use the Data checkpoint column value in Predefined variables for filtering:
SELECT
UserID as ID,
UserLogin as Login
FROM InputData, variables
WHERE
strftime('%s', InputData.CreatedDate) > strftime('%s', variables.DataCheckpoint)
ORDER BY
InputData.CreatedDate
Configuration
SQLite Mapping Statement configuration
Statement
SQLite statement to be executed using SQLite engine loaded in-memory. For a full list of supported commands and functions of this engine, see documentation.
The output of the connector expects an array of objects that must match the output schema in structure. The defined Statement represents the body of the function whose output is parsed and returned as the output of the connector itself. Variables TaskRunID, EndpointID, EndpointName or DataStorageColumn (i.e. Date) can be accessed by variables.<var> clause. The last line of the Statement must therefore be some SELECT clause.
Example
SELECT * FROM InputData
SELECT * FROM InputData, variables WHERE InputData.EndpointID = variables.EndpointID
Data checkpoint column
Name of column from which value will be stored for another runs as text. Value is taken from column on last row and can be accessed from SQLite with prefix variables. Example: variables.DataCheckpoint.
SELECT
Number AS Number,
'asdasd' AS Base64,
Date AS Date,
Json AS Json,
Double AS Double,
variables.DataCheckpoint AS Bool
FROM InputData, variables
Input & Output Schema
Input
Input schema is optional
Output
Output schema is mandatory
Release notes
3.0.6
- Connection and commands timeout is respecting task step timeout.
3.0.2
- Implemantation of debug script generating.
3.0.1
- Map input data structure to output data structure using SQLite in-memory SQL engine.