SNAPSHOT

Description

Controls data in a unified manner for multiple users.

Precautions

• The GUC parameter db4ai_snapshot_mode classifies the snapshot storage models into MSS and CSS. The GUC parameter db4ai_snapshot_version_delimiter specifies the version separator and its default value is @. The GUC parameter db4ai_snapshot_version_separator specifies the sub-version separator and its default value is ..
• When the incremental storage mode is used for snapshots, the snapshots are dependent on each other. Snapshots must be deleted in the dependency sequence.
• The snapshot feature is used to maintain data between team members, involving data transcription between administrators and common users. Therefore, the snapshot feature is not supported in the separation-of-duties scenario (enableSeparationOfDuty is set to ON).
• When you need a stable and available snapshot for tasks such as AI training, you need to publish the snapshot.

Syntax

1. Create a snapshot.

   You can use the CREATE SNAPSHOT... AS and CREATE SNAPSHOT... FROM statements to create a data table snapshot.

   • CREATE SNAPSHOT AS

     CREATE SNAPSHOT <qualified_name> [@ <version | ident | sconst>]
         [COMMENT IS <sconst>}
         AS query;

   • CREATE SNAPSHOT FROM

     CREATE SNAPSHOT <qualified_name> [@ <version | ident | sconst>]
         FROM @ <version | ident | sconst>
         [COMMENT IS <comment>}
             USING (
             { INSERT [INTO SNAPSHOT] ...
               | UPDATE [SNAPSHOT] [AS <alias>] SET ... [FROM ...] [WHERE ...]
               | DELETE [FROM SNAPSHOT] [AS <alias>] [USING ...] [WHERE ...]
               | ALTER [SNAPSHOT] { ADD ... | DROP ... } [, ...]
           } [; …]
         );

2. Delete a snapshot.
   PURGE SNAPSHOT

   PURGE SNAPSHOT <qualified_name> @ <version | ident | sconst>;

3. Sample snapshots.
   SAMPLE SNAPSHOT

   SAMPLE SNAPSHOT <qualified_name> @ <version | ident | sconst>
       [STRATIFY BY <attr_list>]
       { AS <label> AT RATIO <num> [COMMENT IS <comment>] } [, ...]

4. Publish snapshots.
   PUBLISH SNAPSHOT

   PUBLISH SNAPSHOT <qualified_name> @ <version | ident | sconst>;

5. Archive snapshots.
   ARCHIVE SNAPSHOT

   ARCHIVE SNAPSHOT <qualified_name> @ <version | ident | sconst>;

6. Query snapshots.

   SELECT * FROM DB4AISHOT(<qualified_name> @ <version | ident | sconst> );

Parameters

• qualified_name

  Name of the snapshot to be created

  Value range: a string that complies with the Identifier Naming Conventions.

• version

  (Optional) Version number of a snapshot. This parameter is optional. The system automatically extends the sequence number.

  Value range: string, consisting of numbers and separators.

• ident

  Identifies the snapshot suffix number (or major version).

  Value range: an integer.

• sconst

  Marks the version number with a floating-point number. The feature replaces the decimal point with the separator specified by the GUC parameter db4ai_snapshot_version_separator in the background.

  Value range: a floating-point number.

• comment

  Specifies the comment to be added.

  Value range: a string that complies with the Identifier Naming Conventions.

• alias

  Specifies the alias of the current object.

  Value range: a string that complies with the Identifier Naming Conventions.

• attr_list

  Specifies the list of target objects.

  Value range: a string that complies with the Identifier Naming Conventions.

• label

  Alias of the sampled data column.

  Value range: a string that complies with the Identifier Naming Conventions.

• num

  Specifies the ratio.

  Its value is a digit.

Examples

-- Create data table t1.
gaussdb=# CREATE TABLE t1 (id int, name varchar);

-- Insert data.
gaussdb=# INSERT INTO t1 VALUES (1, 'zhangsan');
gaussdb=# INSERT INTO t1 VALUES (2, 'lisi');
gaussdb=# INSERT INTO t1 VALUES (3, 'wangwu');
gaussdb=# INSERT INTO t1 VALUES (4, 'lisa');
gaussdb=# INSERT INTO t1 VALUES (5, 'jack');

-- Create a snapshot.
gaussdb=# CREATE SNAPSHOT s1@1.0 comment is 'first version' AS SELECT * FROM t1;

-- Create snapshots in iteration mode.
gaussdb=# CREATE SNAPSHOT s1@2.0 FROM @1.0 comment is 'inherits from @1.0' USING (INSERT VALUES(6, 'john'), (7, 'tim'); DELETE WHERE id = 1);

-- View snapshot content.
gaussdb=# SELECT * FROM DB4AISHOT(s1@1.0);
 id |   name   
----+----------
  1 | zhangsan
  2 | lisi
  3 | wangwu
  4 | lisa
  5 | jack
(5 rows)
gaussdb=# SELECT * FROM DB4AISHOT(s1@2.0);
 id |  name  
----+--------
  2 | lisi
  3 | wangwu
  4 | lisa
  5 | jack
  6 | john
  7 | tim
(6 rows)
-- Sample snapshots.
gaussdb=# SAMPLE SNAPSHOT s1@2.0 stratify by name as nick at ratio .5;

-- Delete snapshots.
gaussdb=# PURGE SNAPSHOT s1@2.0;
gaussdb=# PURGE SNAPSHOT s1nick@2.0;
gaussdb=# PURGE SNAPSHOT s1@1.0;

-- Drop table t1.
gaussdb=# DROP TABLE t1;