Action is an operation performed in order to obtain or store data in a data source. Each data source has a list of available actions, which can be used in different Skyvia components.
Currently actions are used in the following Data Flow components:
- Source — this component uses actions to obtain data from a data source.
- Lookup — this component uses actions to obtain records that match the lookup input records.
- Target — this component uses actions to store records in a data source.
The same actions for the same connector are configured in the same way regardless of where they are used. This section describes common actions for most data sources and how to configure them. Note that available actions are connector-specific, and even settings for the same action may differ for different connectors. You can find the list of supported actions and any specific if an action is different for a connector in the Connectors documentation.
Different actions are configured differently. For example, the Execute Command action requires you to enter SQL command to execute, and the Insert action requires adjusting certain settings visually, like selecting a table to insert data to and specifying the list of autogenerated columns you want to get back. However, most actions share one feature in common — parameters.
For example, in the Insert action, parameters correspond to target table columns, so you determine how they are mapped to input columns. In the Execute Command action, parameters are just SQL parameters that you use in your command. You enter the command text with parameters, and Skyvia can automatically create the list of parameters from it.
You can find the list of action parameters under other action settings. The list of parameters updates automatically while you edit other action settings. However, if you modify action settings so that existing parameters should be deleted, you will be prompted for the confirmation of deleting them.
You can refresh the list of parameters manually whenever you need by clicking the button near Parameters.
Action parameters must be mapped in order to determine what data an action must query or store. What you can map to action parameters depends on where the action is used.
- For actions in Source component of a data flow, you can map parameters to the data flow variables, including variables created for data flow parameters.
- For actions in Lookup or Target component of a data flow, you may use data flow variables and input columns for mapping.
You can edit parameter mapping by clicking the button near Parameters. It opens Mapping Editor, where you can add or delete parameters, rename them, and map them using Skyvia expressions.
Parameter mapping is configured in a convenient Mapping Editor. Mapping editor has a list of parameters on the left. Here you can quickly map parameters to the input columns with the same name by clicking the button. You can also add parameters, using the button.
If your parameter list is too long, you can also use the and enter a part of the parameter name to quickly find the necessary parameter.
To map a parameter, select this parameter in the list and specify the expression, calculating the necessary parameter value. In a simple case this can be just a name of an input column or a data flow variable. Variable names must be preffixed with the ‘@’ character. You can quickly select the name of a input column in the Properties list and the name of a variable in the Variables list.
For more complex cases you can use all the power of Skyvia expression syntax, editing expressions in the editor with code competion, hints, expression validation and result preview, the same as in the Expression editor.
You can also rename the selected parameter by editing its name in the Name box.
This action executes an SQL command against the data source. In the Source and Lookup components, the command should query data from a data source. In the Target component, this command should store data in the data source.
To configure such an action, simply enter the SQL command. You also can click the Open in Editor link, to open the command in a Query Editor window. This window has the Connection Object List on the left, where you can browse connection objects and view connection metadata in order to enter table and column names without errors in your commands.
Query Editor also allows you to preview data, returned by the command. For this, command must return data and not use SQL parameters. You can click the Preview button and view the first 20 returned rows. You can load more rows, if you want, by pressing the F8 key or clicking Load More. To return to the editor, click Hide Preview.
You also need to configure mapping for the action parameters, which correspond to the SQL parameters, used in the SQL command. Use ‘:’ (colon) as the prefix of your SQL parameter names.
When you use the Execute Command action in a Lookup component, the command must query rows, matching to an input row. You can achieve it by comparing columns with parameters in the WHERE clause and map these parameters to the necessary input columns.
This action queries data from a data source. It is available in the Source components. This action allows you to configure the query visually, with Visual Query Builder.
To configure this action, click the Open Query Builder link. It opens the Query Editor window with visual query builder, where you can add columns, configure filters, add aggregations and functions, build joins, set up data ordering, etc. See Configuring Queries with Query Builder for more details.
Query Editor also allows you to preview data, returned by the query. You can click the Preview button and view the first 20 returned rows. You can load more rows, if you want, by pressing the F8 key or clicking Load More. To return to the editor, click Hide Preview.
This action queries records, matching to an input record, from the data source. It is available in the Lookup components.
To configure this action, you need to perform the following steps:
- Select the Table to look for the records from.
- Enter or select the lookup Keys - the lookup table columns, by which input records and lookup table records are matched. Note that you enter or select multiple columns one by one.
- Enter or select the Result Columns - the columns that will be added to the output records. Note that you enter or select multiple columns one by one.
- If you want case insensitive comparison, select the Case Insensitive checkbox.
- Configure mapping for the action parameters. Lookup action parameters correspond to the selected Keys.
This action writes new records to the data source. It is available in the Target components.
To configure the Insert action, you need to select the table to insert data to. Then you need to map action parameters. For the Insert action, parameters correspond to the target table columns that allow inserting data. You need to map at least the parameters corresponding to the required target table fields. Parameters for columns that you don’t want to insert data to can be simply deleted from the mapping.
For most connectors (but not all), the Insert action allows obtaining values of autogenerated columns from the target. This can be convenient to obtain the result IDs (or any other fields) of the inserted records. To obtain the field values, enter or select the corresponding fields in the Returning box.
This action updates existing records in the data source. It is available in the Target components.
To configure the Update action, first you need to select the table to update data in. Then you need to enter or select the Keys - target table columns, by which Skyvia will search the record to update. This does not need to be a record Id or primary key, but they must uniquely identify a record. If multiple records are found by the specified key values, they all are updated.
Finally, you need to map the action parameters. For the Update action, parameters correspond to the target table columns that allow updating data and to the selected Keys. You need to map at least the parameters corresponding to the selected Keys in order to be able to find records to update. You also need to map parameters for the columns that you want to update.
This action deletes existing records from the data source. It is available in the Target components.
To configure the Delete action, you need to select the table to delete data from. Then you need to map action parameters. For the Delete action, parameters correspond to the target table primary key columns.
Note that unlike the Update action, the Delete action does not allow identifying a record to delete by an arbitrary set of columns. You need to know the target record primary key values to delete them. In Data Flow, you can obtain primary key values of the records to delete prior to the Target component with the Delete action - either via a Source or via Lookup component. For example, you can use Lookup to get primary key values, matching records by other columns.