Updated on 2025-05-29 GMT+08:00

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;