DB4AI-Snapshots for Data Version Management
Tags: opengauss
Published: 2023-04-13 10:57:56

DB4AI-Snapshots is used by the DB4AI module to manage dataset versions. With the DB4AI-Snapshots component, developers can easily and quickly perform data preprocessing operations such as feature filtering and type conversion. In addition, developers can perform version control on training datasets like Git. After a data table snapshot is created, it can be used as a view. However, once the data table snapshot is released, it is fixed as static data. To modify the content of this data table snapshot, you need to create a data table snapshot with a different version number.

**Lifecycle of DB4AI-Snapshots**
DB4AI-Snapshots can be published, archived, or purged. After being published, DB4AI-Snapshots can be used. Archived DB4AI-Snapshots are in the archiving period and will not be used to train new models. Instead, old data is used to verify new models. Purged DB4AI-Snapshots will not be found in the database system.

Note that the DB4AI-Snapshots function is used to provide unified training data for users. Team members can use the specified training data to retrain the machine learning models, facilitating collaboration between users. Therefore, the DB4AI-Snapshots feature is not supported in scenarios where user data conversion is not supported, such as **private users** and **separation of duty** (**enableSeparationOfDuty** set to **ON**).

You can run the **CREATE SNAPSHOT** statement to create a data table snapshot. The created snapshot is in the published state by default. You can create a table snapshot in either **MSS** or **CSS** mode, which can be configured using the GUC parameter **db4ai_snapshot_mode**. For the MSS mode, it is realized by materialization algorithm where data entity of original datasets is stored. The CSS mode is implemented based on a relative calculation algorithm where incremental data information is stored. The metadata of the data table snapshot is stored in the system directory of DB4AI. You can view it in the **db4ai.snapshot** system catalog.

You can run the **ARCHIVE SNAPSHOT** statement to mark a data table snapshot as archived, and run the **PUBLISH SNAPSHOT** statement to mark it as published again. The state of a data table snapshot is marked to help data scientists work together as a team.

If a table snapshot is no longer useful, you can run the **PURGE SNAPSHOT** statement to permanently delete it and restore the storage space.

**DB4AI-Snapshots Usage Guide**
1. Create a table and insert table data.

If a data table exists in the database, you can create a data table snapshot based on the existing data table. To facilitate subsequent demonstration, create a data table named **t1** and insert test data into the table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l9jrgpd4rrutbd280ybv.png)

Run the following SQL statement to query the content of the collocation data table:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j4ut62efyflbbds99hx0.png)

2. Use DB4AI-Snapshots.

2.1 Create DB4AI-Snapshots.

2.1.1 Example 1: CREATE SNAPSHOT… AS

In the following example, the default version separator is an at sign (@), and the default subversion separator is a period (.). You can set the separator by setting the **db4ai_snapshot_version_delimiter** and **db4ai_snapshot_version_separator** parameters.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yeq0l3rdxrzcj44q2xe9.png)

The command output indicates that a snapshot has been created for data table **s1** and the version number is **1.0**. A created data table snapshot can be queried in the same way as a common view, but cannot be updated using the **INSERT INTO** statement. For example, you can use any of the following statements to query the content of data table snapshot **s1** of version 1.0:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7gqoikjwuhaewqt66daf.png)

You can run the following SQL statement to modify the content of the **t1** data table:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xxjyg2uzdqer641ihss9.png)

When content of data table **t1** is retrieved again, it is found that although the content of data table t1 has changed, the query result of data table snapshot s1@1.0 does not change. Because data in data table t1 has changed, to use content of the current data table as a version 2.0, you can create snapshot s1@2.0 by using the following SQL statement:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8zlovowr9t5selxqf9nk.png)

According to the foregoing example, it can be found that the data table snapshot can solidify content of a data table, to avoid instability during training of a machine learning model caused by data modification in the process, and can also avoid a lock conflict caused when multiple users access and modify the same table at the same time.

2.1.2 Example 2: CREATE SNAPSHOT… FROM

You can run an SQL statement to inherit a created data table snapshot and generate a new data table snapshot based on the data modification. Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jxppfc6r3lc8herak08o.png)

Where, @ is the data table snapshot version separator and the from clause is followed by the existing data table snapshot, in the format of @ + version number. You can add an operation keyword (such as **INSERT**, **UPDATE**, **DELETE**, or **ALTER**) after the **USING** keyword. In the **INSERT INTO** and **DELETE FROM** statements, clauses related to data table snapshot names, such as **INTO** and **FROM**, can be omitted. For details, see AI Feature Functions.

In the example, based on the s1@1.0 snapshot, insert two pieces of data and delete one piece of data to generate a new snapshot s1@3.0. Then, retrieve s1@3.0.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6elxi3lum62jc1afytqq.png)

2.2 Delete the data table snapshot **SNAPSHOT**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c1umfjqavnoe0b2dbrtz.png)

At this time, no data can be retrieved from s1@3.0, and the records of the data table snapshot in the **db4ai.snapshot** view are cleared. Deleting the data table snapshot of this version does not affect the data table snapshots of other versions.

2.3 Sample from a data table snapshot.

Example: Use the sampling rate 0.5 to extract data from snapshot **s1**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1c7x478sohheigo5cqru.png)

You can use this function to create a training set and a test set. For example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvge2no5k2ppvsw267z3.png)

2.4 Publish a data table snapshot.

Run the following SQL statement to mark the data table snapshot s1@2.0 as published:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sb4ac2qblphxooye26mj.png)

2.5 Archive a data table snapshot.

Run the following statement to mark the data table snapshot as archived:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b1ynrsmz6rl6r355blps.png)

3. Perform troubleshooting in case of exceptions.

3.1 The data table or DB4AI snapshot does not exist.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9qa028jvhw0li2wnzvj5.png)

3.2 Before deleting a snapshot, ensure that other snapshots that depend on it have been deleted.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qdn9ady9bgmnln8i3qzq.png)

4. Set GUC parameters.

4.1 db4ai_snapshot_mode:

There are two snapshot modes: MSS (materialized mode, used to store data entities) and CSS (computing mode, used to store incremental information). The snapshot mode can be switched between MSS and CSS. The default snapshot mode is MSS.

4.2 db4ai_snapshot_version_delimiter:

Used to set the data table snapshot version separator. The at sign (@) is the default data table snapshot version separator.

4.3 db4ai_snapshot_version_separator

Used to set the data table snapshot subversion separator. The period (.) is the default data table snapshot subversion separator.

5. View the snapshot details of a data table in the DB4AI schema by using **db4ai.snapshot**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3wa275cjs2qm49izii4e.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jungq92msa2o566atc7x.png)