Manage database and S3 data
The Work.AI platform lets you create the following types of data management configurations:
Database data management contains user-defined data cleanup logic based on a stored procedure or custom SQL. Custom SQLs clean up Data Stores (ds schema), whereas stored procedures purge all other data schemas.
S3 data management cleans up the files from a specified S3 bucket based on the maximum file age and other parameters.
Database data archive management allows you to archive obsolete Data Store and database data to avoid its accidental loss as a result of purging.
Database data restoration management restores the archived data and merges it back to the Data Stores or database.
Together with data from completed Business Processes (BP), the default out-of-the-box data management procedure automatically removes all BPs that have not been run for over 90 days or beyond your custom purging period. So, to avoid losing critical BP data, back up it up in advance using the Database data archive management configuration. Later, you can recover the backup data using the Database data restoration management configuration.
You can run data management procedures in one of the following ways:
In this document, execution refers to a single run of a data management configuration.
Configure data management permissions
To access the Data Management feature, one of the following permissions is required:
- Configure Database and S3 Data Management: allows viewing, executing, creating, updating, and deleting data management configurations to purge S3 and database data.
- Configure Data Archival and Restoration: allows viewing, executing, creating, updating, and deleting data management configurations for archiving process data and restoring it.
To grant the permissions to a user, follow the instructions in the Manage Control Tower roles and permissions guide.
Make sure to log out and log back into Control Tower to apply the permission changes.
View data management configurations
To view the list of available data management configurations in Control Tower, go to Advanced > Data Management. On the right, you can see a table containing data on existing data management configurations and action buttons.
The Data Management table comprises the following information:
- The Name and description column contains the names and descriptions of data management configurations.
- Based on indicates whether a data management configuration is based on a stored procedure or custom SQL or cleans up the S3 storage. Custom SQLs clean up Data Stores (ds schema), whereas stored procedures purge all other data schemas.
- The Last execution column comprises the timestamps for the next and the last executions.
- Execution status indicates the state of the latest execution related to a given data management configuration. Available statuses are:
- Queued: the data management procedure execution is waiting in the queue at the execution engine.
- In progress: the data management procedure execution is in progress.
- Failed: the data management procedure execution failed at the database side.
- Done: the data management procedure execution was completed successfully.
- Timeout: the execution timeout for the data management procedure is exceeded. Applicable to S3 data management only.
- Partial success: the data management procedure removed only some of the files matching the specified criteria. Applicable to S3 data management only.
- The Execute button lets you submit a data management procedure execution. For details, refer to Execute data management configuration from list.
- The Enable/Disable buttons activate or deactivate the selected data management configuration. The configuration must be enabled to be run as scheduled by cron or executed manually.
- The Delete button removes the selected configuration.
Create database data management configuration
To create a database data management configuration, follow the steps below:
Go to Advanced > Data Management.
Click the Configure button and choose Database data management.
In the Database data management configuration popup, in the required Name field, specify an arbitrary name for the new configuration. This is the name you see in the Name and description column on the Data Management list page.
In the optional Description field, give more details about the created configuration. After saving the configuration, you can see the details in the Name and description column on the Data Management list page.
In the Set up a schedule section, click Set up to schedule recurrent executions. For instructions, refer to the Schedule data management configuration with cron section.
When no schedule is set, you can only run the configuration manually from the Data Management list page. For details, refer to the Execute data management configuration from list section.
In Configuration based on, select one of the available options:
Stored procedure, if you apply a stored procedure to perform data cleanup. When the procedure you chose in the Procedure drop-down box has parameters, a list of fields appears below it.
In the figure above, the dp#purge_outdated_data_sp stored procedure has five parameters: i_purge_set, i_retention_days, i_batch_size, i_batch_delay_ms, and i_session_duration_min. For details about the parameters, read the Develop data management procedures topic.
A number of stored procedures are available out of the box. For details, refer to Explore OOTB stored procedures. For instructions on implementing custom ones, refer to Develop stored procedures.
Custom SQL, if you need to create a configuration for Data Store cleanup. In the SQL box appearing below, specify custom SQL code.
The custom SQL cannot have any parameters and only works with the Data Store (ds) schema. For details on developing custom SQLs, refer to Develop data management procedures.
Click Save. The new configuration is added to the list on the Data Management page.
When purging the ds schema with custom SQL, make sure not to delete the ds table itself. For instance, using drop table ds_delete_me is wrong as it would lead to the delete_me Data Store becoming inoperational.
Create S3 data management configuration
To create a data management configuration for purging S3, follow the steps below:
Go to Advanced > Data Management.
Click the Configure button and choose S3 data management.
On the S3 data management configuration page, configure the fields as described below. Mind that all fields marked with an asterisk (*) are required.
Name: specify an arbitrary name for the new S3 data management configuration. This is the name you see in the Name and description column on the Data Management list page.
Description: give details about the created configuration. After saving the configuration, you can see the details in the Name and description column on the Data Management list page.
Set up a schedule: click Set up to schedule recurrent executions. For instructions, refer to the Schedule data management configuration with cron section. When no schedule is set, you can only run the configuration manually from the Data Management list page. For details, refer to the Execute data management configuration from list section.
Access Key Alias: specify the alias in Secrets Vault that references the S3 access key.
Secret Key Alias: add the alias in Secrets Vault that references the S3 secret key.
Bucket: specify an S3 bucket to clean up.
Path: set the path (prefix) to the files to be deleted. The path with / (slash) signifies all files in the bucket.
File Name Pattern: a Unix-like pattern to identify the files to be deleted.
Delete Files Older Than: set the storage period duration after which files are to be deleted.
Recursive: select the checkbox if you want the data management configuration to delete all files on the path, including from associated sub-folders. Otherwise, the configuration cleans the files only from the top-level folder on the path.
Session Duration: specify the cleanup session duration. If a data management procedure execution exceeds the duration, it is stopped with the TIMEOUT status.
Click Save. The new configuration is added to the list on the Data Management page.
Back up process data before purging
Work.AI features a dedicated data backup mechanism for preventing accidental loss of essential data as a result of applying the data management procedure removing the BPs that have not been active for over 90 days. The diagram below illustrates the mechanism:
As you can see from the diagram above, all data from CT schema tables is duplicated to CT archive ones and from the DS tables to DS archive ones. In Control Tower, you can create and run two data management configurations to set the mechanism working by interacting with the CT and DS archive schemas:
Database data archival fetches data from the CT and DS archive tables and moves the data to a specified S3 storage in the ZIP file format.
Database data restoration retreives data from the CT and DS archive tables that you want to restore and merges it with the CT and DS schema tables.
Enable data archival and restoration
By default, the data archival and restoration mechanism is disabled. You need to enable it in installation files:
Connect to the Master server:
ssh wfuser@<master-server>Open or create the
config_override.ymlfile. The path to the file is as follows:/opt/workfusion/config_override.yml.To enable data archival and restoration, add the following property to the file:
data_archive_enabled: trueTo apply the changes, run the following commands:
./install.sh migrate migrations-data-archive -e @config_overrides.yml
./install.sh install init-s3 -e @config_overrides.yml
./install.sh configure control-tower -e @config_overrides.ymlOnce the configuration process is completed successfully, restart the related services if necessary.
Create data archival configuration
To create a Database data archive management configuration, follow the steps below:
Go to Advanced > Data Management.
Click the Configure button and choose Database data archive management.
On the Database data archive management configuration page, configure the settings as described below. Mind that all fields marked with an asterisk (*) are required.
Name: specify an arbitrary name for the new configuration. This is the name you see in the Name and description column on the Data Management list page.
Description: give details about the created configuration. After saving the configuration, you can see the details in the Name and description column on the Data Management list page.
In the Set up a schedule section, click Calendar to schedule an execution. This way, you are setting not a Cron job, but a one-time execution. When no schedule is set, you can only run the configuration manually from the Data Management list page. For details, see the Execute data management configuration from list section.
Procedure: choose a procedure for executing the configuration (typically, dp#purge_archive_obsolete_runs_sp) and configure its parameters.
Bucket: specify an S3 bucket where the archived data is to be saved in the form of a ZIP file.
Click the Save button. The new configuration is added to the list on the Data Management page.
Create data restoration configuration
To create a Database data restoration management configuration, follow the steps below:
Go to Advanced > Data Management.
Click the Configure button and choose Database data restoration management.
On the Database data restoration management configuration page, configure the settings as described below. Mind that all fields marked with an asterisk (*) are required.
Name: specify an arbitrary name for the new configuration. This is the name you see in the Name and description column on the Data Management list page.
Description: give details about the created configuration. After saving the configuration, you can see the details in the Name and description column on the Data Management list page.
In the Set up a schedule section, click Calendar to schedule an execution. This way, you are setting not a Cron job, but a one-time execution. When no schedule is set, you can only run the configuration manually from the Data Management list page. For details, see the Execute data management configuration from list section.
Achival files: choose the exact archive you need to restore. The drop-down list includes all archives created with Database data archive management configurations.
Click the Save button. The new configuration is added to the list on the Data Management page.
Execute data management configuration from list
To submit a data management configuration for execution from the Data Management list page, act as follows:
In Control Tower, go to Advanced > Data Management and find the configuration you want to execute.
Ensure that the configuration is active. Active configurations are displayed brightened and contain the Execute button in the last column.
Inactive configurations appear dimmed. To activate a configuration, select the checkbox next to its name and click Enable. The selected row becomes available.
Click the Execute button for the configuration. A popup appears, displaying the fields to set the execution parameters of the database data management configuration.
For a Database data management configuration, the displayed parameters are related to the underlying stored procedure.
For an S3 data management configuration, the popup displays the required parameters as shown below.
Specify the parameters and click Execute. The process is submitted for execution. After that, check the Status column on the list page to see the status of the latest run.
Schedule data management configuration with cron
Scheduling with a cron job is not available for Database data archive management and Database data restoration management configurations. They allow scheduling only one-time executions.
To schedule a data management configuration with a cron expression, follow the steps below:
Ensure that the configuration you want to schedule contains the cron expression:
- In Control Tower, go to Advanced > Data Management and find the configuration to schedule and click it to open the editing page.
- Click the Show cron expression link to check if the expression is populated.
If the expression is empty or incorrect, click Set up, configure the schedule in the dialog window, and click Save schedule.
On the Data Management page, activate the configuration.
Active configurations are displayed brightened and contain the Execute button in the last column. Inactive configurations appear dimmed. To activate a configuration, select the checkbox next to its name and click Enable. The selected row becomes available.
After you configure the cron expression and activate the configuration, it is executed by the cron job automatically as scheduled. On the Data Management page, you can check the status of the scheduled data management configuration and its last or next execution time.
Disable data management configuration
To stop the execution of a given configuration by the cron scheduler and turn off the possibility to run it manually, disable the data management configuration:
Go to the Data Management list page and find the configuration to deactivate.
In the first column, select the configuration checkbox.
Click Disable. The deactivated configuration becomes unavailable.
Track configuration changes
You can track the modifications and updates of data management configurations on the Activity Log page:
In Control Tower, go to System Settings > Activity Log.
Find the needed data management configuration in the list and click it.
The dialog window with details appears.
Run data management configuration via API
Two permissions are essential for working with Data Management API:
Run Data Management Configurations: enables using API methods to submit executions and to get statuses or events. Thus, third-party users can be granted only the Run Data Management Configurations permission to trigger data management configurations without access to sensitive system information.
Configure Data Management: allows utilizing the entire range of Data Management API methods.
To enable the permissions, follow the instructions in the Manage Control Tower roles and permissions guide.
To run data management configurations via API, you also need to acquire the token for the REST client according to the instruction. Afterward, you can use any API endpoints described in the Data Management API topic.
Purge Analytics
Purging Analytics can lead to the increased size of the transaction log file and low disk space.
Prepare for purge
Before starting the Analytics purging, stop all ETL processes:
Stop the ETL scheduler.
Run the ETL (extract, transmit, load) process manually using SQL Server Management Studio (SSMS) and ensure it is completed successfully. The script must run without errors.
exec [etl#process_sp]The following script returns 0 rows if there are no errors within the last ETL execution.
select id, duration_sec , source_name, target_name, rows_number, notes
from etl_log
where notes <> 'ok' and
group_date = (select max(group_date) from etl_log)
Set parameters and run procedure
The stored master purge_data_analytics_sp procedure is the primary one and starts the data purging process. The procedure is to be run manually in SSMS.
The default parameters are 30 days back from now.
In the procedure, define the following:
- The period for the data to be available in the Analytics database. You can set up the period type (
day,hour,minute) as well as the period value or count. - The batch size, meaning the count of rows to delete at a time, and the sleep time between batch purges.
--example to run
declare @cnt int
exec @cnt = purge_data_analytics_sp 'day',30
[purge_data_analytics_sp]
create or alter procedure purge_data_analytics_sp
@metric_type nvarchar(10) = 'day' -- day/hour/minute - type of range
, @metric_value int = 30 -- value determining how far to go back to delete rows, pairs with @metric_type
as
/********
SAMPLE:
exec purge_data_analytics_sp 'day',30
CHANGELOG: 10.1-rev
2019-12-12 added [hpath] to order set of tables
2019-08-02 created
********/
begin
set nocount on
; /*parameters that define the execution sql command string*/
declare @sp_name nvarchar(255) -- the name of the stored procedure to purge the table
declare @tb_name nvarchar(255) -- the table name where rows are to be deleted
declare @fl_name nvarchar(255) -- the name of the field (datetime2) used to calculate the range for deleting if needed
declare @id_name nvarchar(255) -- used for deleting in ct_* tables to identify a specific id/table
declare @group nvarchar(8) -- pm,rpa,ct
declare @batch_size int -- the size of the batch to delete (in rows)
declare @sleep_time float -- the sleep time to delay the next step
/*parameters that store information to put into the log table*/
declare @start_date datetime2 -- the start date and time for each deletion operation
declare @end_date datetime2 -- the end date and time of each deletion operation
declare @duration_sec bigint -- the duration in seconds, the difference between @start_date and @end_date
declare @rows_cnt bigint -- the count of deleted rows
declare @status nvarchar(max) -- the resulting 'ok' or error message
declare @purge_run_id int -- the parameter to distinguish different executions of the master SP
/*inner parameters*/
declare @sql nvarchar(max) -- the SQL statement to run the next-level SP to delete rows
declare @date_from datetime2 -- the upper limit for deleting process_execution
declare @temp_table nvarchar(32) --
set @purge_run_id = (select coalesce(max(purge_run_id),0) + 1 from [dp_log]) -- each execution of the master SP is marked
;
if object_id('tempdb..##dp_ct_ids',N'U') is not null drop table ##dp_ct_ids
--table to collect all ids in ct_* tables to delete
create table ##dp_ct_ids
( process_execution_id bigint not null,
[start_date] datetime2(7),
task_id bigint not null,
task_instance_id bigint not null,
task_item_id bigint not null,
task_execution_id bigint null,
task_transaction_id bigint null,
plugin_id bigint null)
;
if object_id('tempdb..##dp_rpa_sessions_ids',N'U') is not null drop table ##dp_rpa_sessions_ids
create table ##dp_rpa_sessions_ids
(bot_session_id bigint not null)
;
if object_id('tempdb..##dp_rpa_status_ids',N'U') is not null drop table ##dp_rpa_status_ids
create table ##dp_rpa_status_ids
(bot_status_id bigint not null)
;
if object_id('tempdb..##dp_ml_stat_ids',N'U') is not null drop table ##dp_ml_stat_ids
create table ##dp_ml_stat_ids
(mls_id bigint not null)
;
-- get the date starting from which the process execution data is to be stored (or the upper limit for deleting)
set @date_from = case @metric_type when 'day' then dateadd(day,-@metric_value,getdate())
when 'hour' then dateadd(hour,-@metric_value,getdate())
when 'minute' then dateadd(minute,-@metric_value,getdate())
end
; -- collect all ids for ct_*,rpa_*,ml_* tables
exec full_dp_ids_sp @date_from
-- cursor inside which the process runs on the [dp_config] table and executes data purge or deletion for a specific table with exact parameters - steps
declare cur_purge_sp cursor for
with D_SRC as
(
select id
, isnull(prev_id,0) prev_id
from dp_config
)
, D_TREE( id, prev_id, hpath) as
(
select s.id
, s.prev_id
, cast(s.id as nvarchar(4000)) as hpath
from D_SRC s
where s.prev_id = 0
union all
select s.id
, s.prev_id
, concat( t.hpath, '/', s.id) as hpath
from D_SRC s
inner join D_TREE t on s.prev_id = t.id
)
select
[sp_name]
, [table_name]
, [date_field]
, [id_field]
, case [group_name] when 'ct' then '##dp_ct_ids'
when 'ml' then '##dp_ml_stat_ids'
when 'rpa' then case [id_field] when 'bot_status_id' then '##dp_rpa_status_ids' else '##dp_rpa_sessions_ids' end
else ''
end as temp_table
, [group_name]
, [batch_size]
, [sleep_time]
from [dp_config] dp
join D_TREE dt on dp.id = dt.id
where [is_taken] = 1
order by dt.hpath
open cur_purge_sp
while (1=1) --while the last row is read
begin
fetch next from cur_purge_sp
into @sp_name, @tb_name, @fl_name, @id_name, @temp_table, @group, @batch_size, @sleep_time
if @@fetch_status != 0
break
set @start_date = getdate() --define the start of the step
begin try
begin tran
; -- form sql statement
set @sql = N'exec @row_cnt = ' + @sp_name + ' @tb_name, @fl_name,@id_name, @temp_table, @group, @date_from, @batch_size, @sleep_time '
;
exec sp_executesql @sql, N'@tb_name nvarchar(255), @fl_name nvarchar(255), @id_name nvarchar(255), @temp_table nvarchar(32), @group nvarchar(8), @date_from datetime2, @batch_size int, @sleep_time float, @row_cnt int output',
@tb_name = @tb_name
, @fl_name = @fl_name
, @date_from = @date_from
, @id_name = @id_name
, @temp_table = @temp_table
, @group = @group
, @batch_size = @batch_size
, @sleep_time = @sleep_time
, @row_cnt = @rows_cnt output
commit tran
set @status = 'ok'
end try
begin catch --if error
if @@trancount <> 0
rollback transaction
;
declare @errormessage nvarchar(4000) = error_message()
declare @errorseverity int = error_severity()
declare @errorstate int = error_state()
-- save status
set @status = formatmessage( '[fail] %i: %s', error_number(), @errormessage)
;
raiserror(@errormessage, @errorseverity, @errorstate)
;
end catch
set @end_date = getdate() --define the end of the step
set @duration_sec = datediff(s, @start_date, @end_date)
-- fill in the log table
insert into [dp_log] ([sp_name], [table_name], [date_field], [purge_run_id], [rows_cnt], [duration_sec], [start_date], [end_date], [status])
values (@sp_name, @tb_name, @fl_name, @purge_run_id, @rows_cnt, @duration_sec, @start_date, @end_date,@status)
;
end
-- close cursor
close cur_purge_sp
deallocate cur_purge_sp
-- clean up the temp table with ct_* ids
if object_id('tempdb..##dp_ct_ids',N'U') is not null drop table ##dp_ct_ids
if object_id('tempdb..##dp_rpa_sessions_ids',N'U') is not null drop table ##dp_rpa_sessions_ids
if object_id('tempdb..##dp_rpa_status_ids',N'U') is not null drop table ##dp_rpa_status_ids
if object_id('tempdb..##dp_ml_stat_ids',N'U') is not null drop table ##dp_ml_stat_ids
end
For troubleshooting tips, refer to the following support guides: