openGauss kernel ~ openGauss是一个开源的关系型数据库管理系统

# *DB4AI Snapshots* for relational dataset versioning ## 1. Introduction The module *DB4AI Snapshots* provides a robust and efficient framework that allows any database user to manage multiple versions of relational datasets through a convenient API. ### 1.1 Compact storage with efficient access Its main benefits are automated management of dependencies among large, versioned datasets and their compact storage. Therefore, *DB4AI Snapshots* leverages redundancies among different versions of data for providing a high level of compaction while minimizing adverse effects on performance. At the same time, snapshot data remains efficiently accessible using the full expressiveness of standard SQL. ### 1.2 Immutable snapshot data The snapshot API prevents the user from changing versioned data. Similar to a code repository, every change of data will generate a new version, i.e. a new snapshot. ### 1.3 Performance In addition to compact data representation, the primary goal of *DB4AI Snapshots* is high read performance, i.e. when used in highly repetitive and concurrent read operations for serving as training data sets for concurrent training of multiple ML models. As a secondary performance goal, *DB4AI Snapshots* provides efficient data manipulation for creating and manipulation huge volumes of versioned relational data. ### 1.4 Documentation and Automation In addition, *DB4AI Snapshots* maintains a full documentation of the origin of any dataset, providing lineage and provenance for data, e.g. to be used in the context of reproducible training of ML models. In addition, *DB4AI Snapshots* facilitates automation, i.e. when applying repetitive transformation steps in data cleansing, or for automatically updating an existing training dataset with new data. ## 2. Quick start ### 2.1 Setup *DB4AI Snapshots* is automatically installed in every new database instance of openGauss. Therefore the CREATE DATABASE procedure creates the database schema *db4ai* within the new database and populates it with objects required for managing snapshot data. After successful database creation, any user may start exploring the snapshot functionality. No additional privileges are required. ### 2.2 Examples Set snapshot mode to compact storage model *CSS* (**C**omputed **S**nap**S**hot mode). SET db4ai_snapshot_mode = CSS; Create a snapshot 'm0@1.0.0' from existing data, where stored in table 'test_data'. The SQL statement may use arbitrary joins and mappings for defining the snapshot. CREATE SNAPSHOT m0 AS SELECT a1, a3, a6, a8, a2, pk, a1 b9, a7, a5 FROM test_data; Create snapshot 'm0@2.0.0' from snapshot 'm0@1.0.0' by applying arbitrary DML and DDL statements. The new version number indicates a snapshot schema revision by means of at least one ALTER statement. CREATE SNAPSHOT m0 FROM @1.0.0 USING ( UPDATE SNAPSHOT SET a1 = 5 WHERE pk % 10 = 0; ALTER SNAPSHOT ADD " u+ " INTEGER, ADD "x <> y"INT DEFAULT 2, ADD t CHAR(10) DEFAULT '', DROP a2, DROP COLUMN IF EXISTS b9, DROP COLUMN IF EXISTS b10; UPDATE SNAPSHOT SET "x <> y" = 8 WHERE pk < 100; ALTER SNAPSHOT DROP " u+ ", DROP IF EXISTS " x+ "; DELETE FROM SNAPSHOT WHERE pk = 3 ); Create snapshot 'm0@2.0.1' from snapshot 'm0@2.0.0' by UPDATE while using a reference to another table. The new version number indicates an update operation (minor data patch). This example uses an AS clause for introducing 'i' as the snapshot's custom correlation name for joining with tables during the UPDATE operation. CREATE SNAPSHOT m0 FROM @2.0.0 USING ( UPDATE SNAPSHOT AS i SET a5 = o.a2 FROM test_data o WHERE i.pk = o.pk AND o.a3 % 8 = 0 ); Create snapshot 'm0@2.1.0' from snapshot 'm0@2.0.1' by DELETE while using a reference to another table. The new version number indicates a data revision. This example uses the snapshot's default correlation name 'SNAPSHOT' for joining with another table. CREATE SNAPSHOT m0 FROM @2.0.1 USING ( DELETE FROM SNAPSHOT USING test_data o WHERE SNAPSHOT.pk = o.pk AND o.A7 % 2 = 0 ); Create snapshot 'm0@2.2.0' from snapshot 'm0@2.1.0' by inserting new data. The new version number indicates another data revision. CREATE SNAPSHOT m0 FROM @2.1.0 USING ( INSERT INTO SNAPSHOT SELECT a1, a3, a6, a8, a2, pk+1000 pk, a7, a5, a4 FROM test_data WHERE pk % 10 = 4 ); The SQL syntax was extended with the new @ operator in relation names, allowing the user to specify a snapshot with version throughout SQL. Internally, snapshots are stored as views, where the actual name is generated according to GUC parameters on arbitrary level, e.g. on database level using the current setting of the version delimiter: -- DEFAULT db4ai_snapshot_version_delimiter IS '@' ALTER DATABASE <name> SET db4ai_snapshot_version_delimiter = '#'; Similarly the version separator can be changed by the user: -- DEFAULT db4ai_snapshot_version_separator IS ’.’ ALTER DATABASE <name> SET db4ai_snapshot_version_separator = ’_’; Independently from the GUC parameter settings mentioned above, any snapshot version can be accessed: -- standard version string @schema.revision.patch: SELECT * FROM public.data@1.2.3; -- user-defined version strings: SELECT * FROM accounting.invoice@2021; SELECT * FROM user.cars@cleaned; -- quoted identifier for blanks, keywords, special characters, etc.: SELECT * FROM user.cars@"rev 1.1"; -- or string literal: SELECT * FROM user.cars@'rev 1.1'; Alternative, using internal name (depends on GUC settings): -- With internal view name, using default GUC settings SELECT * FROM public."data@1.2.3"; -- With internal view name, using custom GUC settings, as above SELECT * FROM public.data#1_2_3; ## 3. Privileges All members of role **PUBLIC** may use **DB4AI Snapshots**. ## 4. Supported Systems and SQL compatibility modes The current version of *DB4AI Snapshots* is tested in openGauss SQL compatibility modes A, B and C. ## 5. Portability *DB4AI Snapshots* uses standard SQL for implementing its functionality. ## 6. Dependencies None. ## 7. Reference & Documentation ### 7.1 Configuration parameters **DB4AI Snapshots** exposes several configuration parameters, via the system's global unified configuration (GUC) management. Configuration parameters may be set on the scope of functions (CREATE FUNCTION), transactions (SET LOCAL), sessions (SET), user (ALTER USER), database (ALTER DATABASE), or on system-wide scope (postgresql.conf). SET [SESSION | LOCAL] configuration_parameter { TO | = } { value | 'value' } CREATE FUNCTION <..> SET configuration_parameter { TO | = } { value | 'value' } ALTER DATABASE name SET configuration_parameter { TO | = } { value | 'value' } ALTER USER name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | 'value' } The following snapshot configuration parameters are currently supported: #### db4ai_snapshot_mode = { MSS | CSS } This snapshot configuration parameter allows to switch between materialized snapshot (*MSS*) mode, where every new snapshot is created as compressed but fully materialized copy of its parent's data, or computed snapshot (*CSS*) mode. In *CSS* mode, the system attempts to exploit redundancies among dependent snapshot versions for minimizing storage requirements. The setting of *db4ai_snapshot_mode* may be adjusted at any time and it will have effect on subsequent snapshot operations within the scope of the new setting. Whenever *db4ai_snapshot_mode* is not set in the current scope, it defaults to *MSS*. ##### Example SET db4ai_snapshot_mode = CSS; #### db4ai_snapshot_version_delimiter = value This snapshot configuration parameter controls the character that delimits the *snapshot version* postfix within snapshot names. In consequence, the character used as *db4ai_snapshot_version_delimiter*