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.
This action executes an SQL command against the data source. Apply Execute Command to Source and Lookup components to query data. Apply Execute Command to Target components to modify data.
Execute command is supported for most of the databases and cloud apps. When you query a database, use a database-specific SQL syntax. To learn more about SQL syntax for Cloud Sources visit Supported SQL for Cloud Sources page.
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.