Skip to content
background-image background-image

This version of the connector is outdated. Show recent version

sqlite_mapper

SQLite Mapper

  • Version

    3.0

  • Agent restriction

    None

  • Processing type

    Row by row & Bulk

  • Multi-input step

    Not Supported

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.