DB4AI: 数据库驱动AI

DB4AI是指利用数据库的能力驱动AI任务,实现数据存储、技术栈的同构。通过在数据库内集成AI算法,令openGauss具备数据库原生AI计算引擎、模型管理、AI算子、AI原生执行计划的能力,为用户提供普惠AI技术。不同于传统的AI建模流程,DB4AI“一站式”建模可以解决数据在各平台的反复流转问题,同时简化开发流程,并可通过数据库规划出最优执行路径,让开发者更专注于具体业务和模型的调优上,具备同类产品不具备的易用性与性能优势。

一、原生DB4AI引擎

二、全流程AI

一、原生DB4AI引擎

openGauss当前版本支持了原生DB4AI能力,通过引入原生AI算子,简化操作流程,充分利用数据库优化器、执行器的优化与执行能力,获得高性能的数据库内模型训练能力。更简化的模型训练与预测流程、更高的性能表现,让开发者在更短时间内能更专注于模型的调优与数据分析上,而避免了碎片化的技术栈与冗余的代码实现。

关键字解析

表 1 DB4AI语法及关键字

名称

描述

语法

CREATE MODEL

创建模型并进行训练,同时保存模型。

PREDICT BY

利用已有模型进行推断。

DROP MODEL

删除模型。

关键字

TARGET

训练/推断任务的目标列名。

FEATURES

训练/推断任务的数据特征列名。

MODEL

训练任务的模型名称。

使用指导

  1. 本版本支持的算法概述。

    当前版本的DB4AI新增支持算法如下:

    表 2 支持算法

    优化算法

    算法

    GD

    logistic_regression

    linear_regression

    svm_classification

    PCA

    multiclass

    Kmeans

    kmeans

    xgboost

    xgboost_regression_logistic

    xgboost_binary_logistic

    xgboost_regression_squarederror

    xgboost_regression_gamma

  2. 模型训练语法说明。

    • CREATE MODEL

      使用“CREATE MODEL”语句可以进行模型的创建和训练。模型训练SQL语句,选用公开数据集鸢尾花数据集iris。

    • 以multiclass为例,训练一个模型。从tb_iris训练集中指定sepal_length, sepal_width,petal_length,petal_widt为特征列,使用multiclass算法,创建并保存模型iris_classification_model。

      openGauss=# CREATE MODEL iris_classification_model USING xgboost_regression_logistic FEATURES sepal_length, sepal_width,petal_length,petal_width TARGET target_type < 2 FROM tb_iris_1 WITH nthread=4, max_depth=8;
      MODEL CREATED. PROCESSED 1
      

      上述命令中:

      • “CREATE MODEL”语句用于模型的训练和保存。
      • USING关键字指定算法名称。
      • FEATURES用于指定训练模模型的特征,需根据训练数据表的列名添加。
      • TARGET指定模型的训练目标,它可以是训练所需数据表的列名,也可以是一个表达式,例如: price > 10000。
      • WITH用于指定训练模型时的超参数。当超参未被用户进行设置的时候,框架会使用默认数值。

        针对不同的算子,框架支持不同的超参组合:

        表 3 算子支持的超参

        算子

        超参

        GD

        (logistic_regression、linear_regression、svm_classification)

        optimizer(char); verbose(bool); max_iterations(int); max_seconds(double); batch_size(int); learning_rate(double); decay(double); tolerance(double)

        其中,SVM限定超参lambda(double)

        Kmeans

        max_iterations(int); num_centroids(int); tolerance(double); batch_size(int); num_features(int); distance_function(char); seeding_function(char); verbose(int);seed(int)

        GD(pca)

        batch_size(int);max_iterations(int);max_seconds(int);tolerance(float8);verbose(bool);number_components(int);seed(int)

        GD(multiclass)

        classifier(char)

        注意:multiclass的其他超参种类取决于选择的分类器中类

        xgboost_regression_logistic、xgboost_binary_logistic、xgboost_regression_squarederror、xgboost_regression_gamma

        batch_size(int);booster(char);tree_method(char);eval_metric(char*);seed(int);nthread(int);max_depth(int);gamma(float8);eta(float8);min_child_weight(int);verbosity(int)

        当前各个超参数设置的默认值和取值范围如下:

        表 4 超参的默认值以及取值范围

        算子

        超参(默认值)

        取值范围

        超参描述

        GD:

        logistic_regression、linear_regression、svm_classification、pca

        optimizer = gd(梯度下降法)

        gd/ngd(自然梯度下降)

        优化器

        verbose = false

        T/F

        日志显示

        max_iterations = 100

        (0, 10000]

        最大迭代次数

        max_seconds = 0 (不对运行时长设限制)

        [0,INT_MAX_VALUE]

        运行时长

        batch_size = 1000

        (0, 1048575]

        一次训练所选取的样本数

        learning_rate = 0.8

        (0, DOUBLE_MAX_VALUE]

        学习率

        decay = 0.95

        (0, DOUBLE_MAX_VALUE]

        权值衰减率

        tolerance = 0.0005

        (0, DOUBLE_MAX_VALUE]

        公差

        seed = 0(对seed取随机值)

        [0, INT_MAX_VALUE]

        种子

        just for linear、SVM:kernel = “linear”

        linear/gaussian/polynomial

        核函数

        just for linear、SVM:components = MAX(2*features, 128)

        [0, INT_MAX_VALUE]

        高维空间维数

        just for linear、SVM:gamma = 0.5

        (0, DOUBLE_MAX_VALUE]

        gaussian核函数参数

        just for linear、SVM:degree = 2

        [2, 9]

        polynomial核函数参数

        just for linear、SVM:coef0 = 1.0

        [0, DOUBLE_MAX_VALUE]

        polynomial核函数的参数

        just for SVM:lambda = 0.01

        (0, DOUBLE_MAX_VALUE)

        正则化参数

        just for pca: number_components

        (0,INT_MAX_VALUE]

        降维的目标维度

        GD:

        multiclass

        classifier=“svm_classification”

        svm_classification\logistic_regression

        多分类任务的分类器

        Kmeans

        max_iterations = 10

        [1, 10000]

        最大迭代次数

        num_centroids = 10

        [1, 1000000]

        簇的数目

        tolerance = 0.00001

        (0,1]

        中心点误差

        batch_size = 10

        [1,1048575]

        一次训练所选取的样本数

        num_features = 2

        [1, INT_MAX_VALUE]

        输入样本特征数

        distance_function = “L2_Squared”

        L1\L2\L2_Squared\Linf

        正则化方法

        seeding_function = “Random++”

        “Random++”\“KMeans||”

        初始化种子点方法

        verbose = 0U

        { 0, 1, 2 }

        冗长模式

        seed = 0U

        [0, INT_MAX_VALUE]

        种子

        xgboost:

        xgboost_regression_logistic、

        xgboost_binary_logistic、

        xgboost_regression_gamma、xgboost_regression_squarederror

        n_iter=10

        (0, 10000]

        迭代次数

        batch_size=10000

        (0, 1048575]

        一次训练所选取的样本数

        booster=“gbtree”

        gbtree\gblinear\dart

        booster种类

        tree_method=“auto”

        auto\exact\approx\hist\gpu_hist

        注意:gpu_hist参数需要相应的库GPU版本,否则DB4AI平台不支持该值。

        树构建算法

        eval_metric=“rmse”

        rmse\rmsle\map\mae\auc\aucpr

        验证数据的评估指标

        seed=0

        [0, 100]

        种子

        nthread=1

        (0, MAX_MEMORY_LIMIT]

        并发量

        max_depth=5

        (0, MAX_MEMORY_LIMIT]

        树的最大深度,该超参仅对树型booster生效。

        gamma=0.0

        [0, 1]

        叶节点上进行进一步分区所需的最小损失减少

        eta=0.3

        [0, 1]

        更新中使用的步长收缩,以防止过拟合

        min_child_weight=1

        [0, INT_MAX_VALUE]

        孩子节点中所需的实例权重的最小总和

        verbosity=1

        0 (silent)\1 (warning)\2 (info)\3 (debug)

        打印信息的详细程度

        MAX_MEMORY_LIMIT = 最大内存加载的元组数量

        GS_MAX_COLS = 数据库单表最大属性数量

    • 模型保存成功,则返回创建成功信息:

      MODEL CREATED. PROCESSED x
      
  3. 查看模型信息。

    当训练完成后模型会被存储到系统表gs_model_warehouse中。系统表gs_model_warehouse可以查看到关于模型本身和训练过程的相关信息。

    关于模型的详细描述信息以二进制的形式存储在系统表中,用户可用过使用函数gs_explain_model完成对模型的查看,语句如下:

    openGauss=# select * from gs_explain_model("iris_classification_model");
     DB4AI MODEL
    -------------------------------------------------------------
     Name: iris_classification_model
     Algorithm: xgboost_regression_logistic
     Query: CREATE MODEL iris_classification_model
     USING xgboost_regression_logistic
     FEATURES sepal_length, sepal_width,petal_length,petal_width
     TARGET target_type < 2
     FROM tb_iris_1
     WITH nthread=4, max_depth=8;
     Return type: Float64
     Pre-processing time: 0.000000
     Execution time: 0.001443
     Processed tuples: 78
     Discarded tuples: 0
     n_iter: 10
     batch_size: 10000
     max_depth: 8
     min_child_weight: 1
     gamma: 0.0000000000
     eta: 0.3000000000
     nthread: 4
     verbosity: 1
     seed: 0
     booster: gbtree
     tree_method: auto
     eval_metric: rmse
     rmse: 0.2648450136
     model size: 4613
    
  4. 利用已存在的模型做推断任务。

    使用“SELECT”和“PREDICT BY”关键字利用已有模型完成推断任务。

    查询语法:SELECT…PREDICT BY…(FEATURES…)…FROM…;

    openGauss=# SELECT id, PREDICT BY iris_classification (FEATURES sepal_length,sepal_width,petal_length,petal_width) as "PREDICT" FROM tb_iris limit 3;
         
    id  | PREDICT
    -----+---------
      84 |       2
      85 |       0
      86 |       0
    (3 rows)
    

    针对相同的推断任务,同一个模型的结果是大致稳定的。且基于相同的超参数和训练集训练的模型也具有稳定性,同时AI模型训练存在随机成分(每个batch的数据分布、随机梯度下降),所以不同的模型间的计算表现、结果允许存在小的差别。

  5. 查看执行计划。

    使用explain语句可对“CREATE MODEL”和“PREDICT BY”的模型训练或预测过程中的执行计划进行分析。Explain关键字后可直接拼接CREATE MODEL/ PREDICT BY语句(子句),也可接可选的参数,支持的参数如下:

    表 5 EXPLAIN支持的参数

    参数名

    描述

    ANALYZE

    布尔型变量,追加运行时间、循环次数等描述信息

    VERBOSE

    布尔型变量,控制训练的运行信息是否输出到客户端

    COSTS

    布尔型变量

    CPU

    布尔型变量

    DETAIL

    布尔型变量,不可用。

    NODES

    布尔型变量,不可用

    NUM_NODES

    布尔型变量,不可用

    BUFFERS

    布尔型变量

    TIMING

    布尔型变量

    PLAN

    布尔型变量

    FORMAT

    可选格式类型:TEXT / XML / JSON / YAML

    示例:

    openGauss=# Explain CREATE MODEL patient_logisitic_regression USING logistic_regression FEATURES second_attack, treatment TARGET trait_anxiety > 50 FROM patients WITH batch_size=10, learning_rate = 0.05;
                                   QUERY PLAN
    -------------------------------------------------------------------------
     Train Model - logistic_regression  (cost=0.00..0.00 rows=0 width=0)
       ->  Materialize  (cost=0.00..41.08 rows=1776 width=12)
             ->  Seq Scan on patients  (cost=0.00..32.20 rows=1776 width=12)
    (3 rows)
    
  6. 异常场景。

    • 训练阶段。

      • 场景一:当超参数的设置超出取值范围,模型训练失败,返回ERROR,并提示错误,例如:

        openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment TARGET trait_anxiety  FROM patients WITH optimizer='aa';
        ERROR:  Invalid hyperparameter value for optimizer. Valid values are: gd, ngd.
        
      • 场景二:当模型名称已存在,模型保存失败,返回ERROR,并提示错误原因,例如:

        openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment TARGET trait_anxiety  FROM patients;
        ERROR:  The model name "patient_linear_regression" already exists in gs_model_warehouse.
        
      • 场景三:FEATURE或者TARGETS列是*,返回ERROR,并提示错误原因,例如:

        openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES *  TARGET trait_anxiety  FROM patients;
        ERROR:  FEATURES clause cannot be *
        -----------------------------------------------------------------------------------------------------------------------
        openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment TARGET *  FROM patients;
        ERROR:  TARGET clause cannot be *
        
      • 场景四:对于无监督学习方法使用TARGET关键字,或者在监督学习方法中不适用TARGET关键字,均会返回ERROR,并提示错误原因,例如:

        openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment FROM patients;
        ERROR:  Supervised ML algorithms require TARGET clause
        -----------------------------------------------------------------------------------------------------------------------------
        CREATE MODEL patient_linear_regression USING linear_regression TARGET trait_anxiety  FROM patients;   
        ERROR:  Supervised ML algorithms require FEATURES clause
        
      • 场景五:当进行分类任务时TARGET列的分类只有1种情况,会返回ERROR,并提示错误原因,例如:

        openGauss=# CREATE MODEL ecoli_svmc USING multiclass FEATURES f1, f2, f3, f4, f5, f6, f7 TARGET cat FROM (SELECT * FROM db4ai_ecoli WHERE cat='cp');
        ERROR:  At least two categories are needed
        
      • 场景六:DB4AI在训练过程中会过滤掉含有空值的数据,当参与训练的模型数据为空的时候,会返回ERROR,并提示错误原因,例如:

        openGauss=# create model iris_classification_model using xgboost_regression_logistic features message_regular target error_level from error_code;
        ERROR:  Training data is empty, please check the input data.
        
      • 场景七:DB4AI的算法对于支持的数据类型是有限制的。当数据类型不在支持白名单中,会返回ERROR,并提示非法的oid,可通过pg_type查看OID确定非法的数据类型,例如:

        openGauss=# CREATE MODEL ecoli_svmc USING multiclass FEATURES f1, f2, f3, f4, f5, f6, f7, cat TARGET cat FROM db4ai_ecoli ;
        ERROR:  Oid type 1043 not yet supported
        
      • 场景八:当GUC参数statement_timeout设置了时长,训练超时执行的语句将被终止:执行CREATE MODEL语句。训练集的大小、训练轮数(iteration)、提前终止条件(tolerance、max_seconds)、并行线程数(nthread)等参数都会影响训练时长。当时长超过数据库限制,语句被终止模型训练失败。

    • 模型解析。

      • 场景九:当模型名在系统表中查找不到,数据库会报ERROR,例如:

        openGauss=# select gs_explain_model("ecoli_svmc");
        ERROR:  column "ecoli_svmc" does not exist
        
    • 推断阶段。

      • 场景十:当模型名在系统表中查找不到,数据库会报ERROR,例如:

        openGauss=# select id, PREDICT BY patient_logistic_regression (FEATURES second_attack,treatment) FROM patients;
        ERROR:  There is no model called "patient_logistic_regression".
        
      • 场景十一:当做推断任务FEATURES的数据维度和数据类型与训练集存在不一致,将报ERROR,并提示错误原因,例如:

        openGauss=# select id, PREDICT BY patient_linear_regression (FEATURES second_attack) FROM patients;
        ERROR:  Invalid number of features for prediction, provided 1, expected 2
        CONTEXT:  referenced column: patient_linear_regression_pred
        -------------------------------------------------------------------------------------------------------------------------------------
        openGauss=# select id, PREDICT BY patient_linear_regression (FEATURES 1,second_attack,treatment) FROM patients;
        ERROR:  Invalid number of features for prediction, provided 3, expected 2
        CONTEXT:  referenced column: patient_linear_regression_pre
        

DB4AI: 数据库驱动AI

说明: DB4AI特性需要读取数据参与计算,不适用于密态数据库等情况。 

二、全流程AI

传统的AI任务往往具有多个流程,如数据的收集过程包括数据的采集、数据清洗、数据存储等,在算法的训练过程中又包括数据的预处理、训练、模型的保存与管理等。其中,对于模型的训练过程,又包括超参数的调优过程。诸如此类机器学习模型生命周期的全过程,可大量集成于数据库内部。在距离数据存储侧最近处进行模型的训练、管理、优化等流程,在数据库端提供SQL语句式的开箱即用的AI全声明周期管理的功能,称之为全流程AI.

openGauss实现了部分全流程AI的功能,将在本章节中详细展开。

  • PLPython Fenced模式

  • DB4AI-Snapshots数据版本管理

①PLPython Fenced模式

在fenced模式中添加plpython非安全语言。在数据库编译时需要将python集成进数据库中,在configure阶段加入–with-python选项。同时也可指定安装plpython的python路径,添加选项–with-includes='/python-dir=path'。

在启动数据库之前配置GUC参数unix_socket_directory ,指定unix_socket进程间通讯的文件地址。用户需要提前在user-set-dir-path下创建文件夹,并将文件夹权限修改为可读可写可执行状态。

unix_socket_directory = '/user-set-dir-path'

配置完成,启动数据库。

将plpython加入数据库编译,并设置好GUC参数unix_socket_directory后,在启动数据库的过程中,自动创建fenced-Master进程。在数据库不进行python编译的情况下,fenced模式需要手动拉起master进程,在GUC参数设置完成后,输入创建master进程命令。

启动fenced-Master进程,命令为:

gaussdb --fenced -k /user-set-dir-path -D /user-set-dir-path &

完成fence模式配置,针对plpython-fenced UDF数据库将在fenced-worker进程中执行UDF计算。

使用指导

  • 创建extension

    • 当编译的plpython为python2时:

      openGauss=# create extension plpythonu;
      CREATE EXTENSION
      
    • 当编译的plpython为python3时:

      openGauss=# create extension plpython3u;
      CREATE EXTENSION
      

    下面示例是以python2为例。

  • 创建plpython-fenced UDF

    openGauss=# create or replace function pymax(a int, b int)
    openGauss-# returns INT
    openGauss-# language plpythonu fenced
    openGauss-# as $$
    openGauss$# import numpy
    openGauss$# if a > b:
    openGauss$#     return a;
    openGauss$# else:
    openGauss$#     return b;
    openGauss$# $$;
    CREATE FUNCTION
    
  • 查看UDF信息

    openGauss=# select * from pg_proc where proname='pymax';
    -[ RECORD 1 ]----+--------------
    proname          | pymax
    pronamespace     | 2200
    proowner         | 10
    prolang          | 16388
    procost          | 100
    prorows          | 0
    provariadic      | 0
    protransform     | -
    proisagg         | f
    proiswindow      | f
    prosecdef        | f
    proleakproof     | f
    proisstrict      | f
    proretset        | f
    provolatile      | v
    pronargs         | 2
    pronargdefaults  | 0
    prorettype       | 23
    proargtypes      | 23 23
    proallargtypes   |
    proargmodes      |
    proargnames      | {a,b}
    proargdefaults   |
    prosrc           |
                     | import numpy
                     | if a > b:
                     |     return a;
                     | else:
                     |     return b;
                     |
    probin           |
    proconfig        |
    proacl           |
    prodefaultargpos |
    fencedmode       | t
    proshippable     | f
    propackage       | f
    prokind          | f
    proargsrc        |
    
  • 运行UDF

    • 创建一个数据表:

      openGauss=# create table temp (a int ,b int) ;
      CREATE TABLE
      openGauss=# insert into temp values (1,2),(2,3),(3,4),(4,5),(5,6);
      INSERT 0 5
      
    • 运行UDF:

      openGauss=# select pymax(a,b) from temp;
       pymax
      -------
           2
           3
           4
           5
           6
      (5 rows)

②DB4AI-Snapshots数据版本管理

DB4AI-Snapshots是DB4AI模块用于管理数据集版本的功能。通过DB4ai-Snapshots组件,开发者可以简单、快速地进行特征筛选、类型转换等数据预处理操作,同时还可以像git一样对训练数据集进行版本控制。数据表快照创建成功后可以像视图一样进行使用,但是一经发布后,数据表快照便固化为不可变的静态数据,如需修改该数据表快照的内容,需要创建一个版本号不同的新数据表快照。

DB4AI-Snapshots的生命周期

DB4AI-Snapshots的状态包括published、archived以及purged。其中,published可以用于标记该DB4AI-Snapshots 已经发布,可以进行使用。archived表示当前 DB4AI-Snapshots 处于“存档期”,一般不进行新模型的训练,而是利用旧数据对新的模型进行验证。purged则是该DB4AI-Snapshots 已经被删除的状态,在数据库系统中无法再检索到。

需要注意的是快照管理功能是为了给用户提供统一的训练数据,不同团队成员可以使用给定的训练数据来重新训练机器学习模型,方便用户间协同。为此私有用户三权分立状态(enableSeparationOfDuty=ON)等涉及不支持用户数据转写等情况将不支持Snapshot特性。

用户可以通过“CREATE SNAPSHOT”语句创建数据表快照,创建好的快照默认即为published状态。可以采用两种模式创建数据表快照,即为MSS以及CSS模式,它们可以通过GUC参数db4ai_snapshot_mode进行配置。对于MSS模式,它是采用物化算法进行实现的,存储了原始数据集的数据实体;CSS则是基于相对计算算法实现的,存储的是数据的增量信息。数据表快照的元信息存储在DB4AI的系统目录中。可以通过db4ai.snapshot 系统表查看到。

可以通过“ARCHIVE SNAPSHOT”语句将某一个数据表快照标记为archived状态,可以通过“PUBLISH SNAPSHOT”语句将其再度标记为published状态。标记数据表快照的状态,是为了帮助数据科学家进行团队合作使用的。

当一个数据表快照已经丧失存在价值时,可以通过“PURGE SNAPSHOT”语句删除它,以便永久删除其数据并恢复存储空间。

DB4AI-Snapshots使用指导

  1. 创建表以及插入表数据。

    数据库内存在已有的数据表,可根据该已有的数据表创建对应的数据表快照。为了后续演示,在此处新建一个名为 t1 的数据表,并向其中插入测试数据。

    create table t1 (id int, name varchar);
    insert into t1 values (1, 'zhangsan');
    insert into t1 values (2, 'lisi');
    insert into t1 values (3, 'wangwu');
    insert into t1 values (4, 'lisa');
    insert into t1 values (5, 'jack');
    

    通过SQL语句,查询搭配数据表内容。

    SELECT * FROM t1;
    id |   name
    ----+----------
      1 | zhangsan
      2 | lisi
      3 | wangwu
      4 | lisa
      5 | jack
    (5 rows)
    
  2. 使用DB4AI-Snapshots。

    • 创建DB4AI-Snapshots

      • 示例1:CREATE SNAPSHOT…AS

        示例如下,其中,默认版本分隔符为 “@”, 默认子版本分割符为 “.”,该分割符可以分别通过GUC参数db4ai_snapshot_version_delimiter以及db4ai_snapshot_version_separator进行设置。

        create snapshot s1@1.0 comment is 'first version' as select * from t1;
        schema |  name
        --------+--------
         public | s1@1.0
        (1 row)
        

        上述结果提示已经创建了数据表 s1的快照,版本号为 1.0。创建好后的数据表快照可以像使用一般视图一样进行查询,但不支持通过“INSERT INTO”语句进行更新。例如下面几种语句都可以查询到数据表快照s1的对应版本 1.0的内容:

        SELECT * FROM s1@1.0;
        SELECT * FROM public.s1@1.0;
        SELECT * FROM public . s1 @ 1.0;
        id |   name
        ----+----------
          1 | zhangsan
          2 | lisi
          3 | wangwu
          4 | lisa
          5 | jack
        (5 rows)
        

        可以通过下列SQL语句修改数据表t1的内容:

        UPDATE t1 SET name = 'tom' where id = 4;
        insert into t1 values (6, 'john');
        insert into t1 values (7, 'tim');
        

        再检索数据表t1的内容时,发现虽然数据表t1的内容已经发生变化,但是数据表快照 s1@1.0 版本的查询结果并未发生变化。由于数据表t1的数据已经发生了改变,如果将当前数据表的内容作为版本2.0,则可创建快照s1@2.0,创建的SQL语句如下:

        create snapshot s1@2.0 as select * from t1;
        

        通过上述例子,我们可以发现,数据表快照可以固化数据表的内容,避免中途对数据的改动造成机器学习模型训练时的不稳定,同时可以避免多用户同时访问、修改同一个表时造成的锁冲突。

      • 示例2:CREATE SNAPSHOT…FROM

        SQL语句可以对一个已经创建好的数据表快照进行继承,利用在此基础上进行的数据修改产生一个新的数据表快照。例如:

        create snapshot s1@3.0 from @1.0 comment is 'inherits from @1.0' using (INSERT VALUES(6, 'john'), (7, 'tim'); DELETE WHERE id = 1);
        schema |  name
        --------+--------
         public | s1@3.0
        (1 row)
        

        其中,“@”为数据表快照的版本分隔符,from子句后加上已存在的数据表快照,用法为“@”+版本号,USING关键字后加入可选的几个操作关键字(INSERT …/UPDATE …/DELETE …/ALTER …),其中 “INSERT INTO”以及“DELETE FROM”语句中的“INTO”、“FROM”等与数据表快照名字相关联的子句可以省略,具体可以参考AI特性函数。

        示例中,基于前述s1@1.0快照,插入2条数据,删除1条新的数据,新生成的快照s1@3.0,检索该s1@3.0:

        SELECT * FROM s1@3.0;
        id |   name
        ----+----------
          2 | lisi
          3 | wangwu
          4 | lisa
          5 | jack
          6 | john
          7 | tim
        (7 rows)
        
    • 删除数据表快照SNAPSHOT

      purge snapshot s1@3.0;
      schema |  name
      --------+--------
       public | s1@3.0
      (1 row)
      

      此时,已经无法再从s1@3.0 中检索到数据了,同时该数据表快照在db4ai.snapshot视图中的记录也会被清除。删除该版本的数据表快照不会影响其他版本的数据表快照。

    • 从数据表快照中采样

      示例:从snapshot s1中抽取数据,使用0.5抽样率。

      sample snapshot s1@2.0 stratify by name as nick at ratio .5;
      schema |    name
      --------+------------
       public | s1nick@2.0
      (1 row)
      

      可以利用该功能创建训练集与测试集,例如:

      SAMPLE SNAPSHOT s1@2.0  STRATIFY BY name AS _test AT RATIO .2, AS _train AT RATIO .8 COMMENT IS 'training';
      schema |      name
      --------+----------------
       public | s1_test@2.0
       public | s1_train@2.0
      (2 rows)
      
    • 发布数据表快照

      采用下述SQL语句将数据表快照 s1@2.0 标记为published 状态:

      publish snapshot s1@2.0;
      schema |  name
      --------+--------
       public | s1@2.0
      (1 row)
      
    • 存档数据表快照

      采用下述语句可以将数据表快照标记为 archived 状态:

      archive snapshot s1@2.0;
      schema |  name
      --------+--------
       public | s1@2.0
      (1 row)
      

      可以通过db4ai-snapshots提供的视图查看当前数据表快照的状态以及其他信息:

      select * from db4ai.snapshot;
      id | parent_id | matrix_id | root_id | schema |    name    | owner  |                 commands                 | comment | published | archived |          created           | row_count
      ----+-----------+-----------+---------+--------+------------+--------+------------------------------------------+---------+-----------+----------+----------------------------+-----------
        1 |           |           |       1 | public | s1@2.0     | omm | {"select *","from t1 where id > 3",NULL} |         | t         | f        | 2021-04-17 09:24:11.139868 |         2
        2 |         1 |           |       1 | public | s1nick@2.0 | omm | {"SAMPLE nick .5 {name}"}                |         | f         | f        | 2021-04-17 10:02:31.73923  |         0
      
  3. 异常场景

    • 数据表或db4ai-snapshots不存在时。

      purge snapshot s1nick@2.0;
      publish snapshot s1nick@2.0;
      ---------
      ERROR:  snapshot public."s1nick@2.0" does not exist
      CONTEXT:  PL/pgSQL function db4ai.publish_snapshot(name,name) line 11 at assignment
               
      archive snapshot s1nick@2.0;
      ----------
      ERROR:  snapshot public."s1nick@2.0" does not exist
      CONTEXT:  PL/pgSQL function db4ai.archive_snapshot(name,name) line 11 at assignment
      
    • 删除snapshot时,有依赖该快照的其他snapshot,需先确保删除对本快照所依赖的其他快照。

      purge snapshot s1@1.0;
      ERROR:  cannot purge root snapshot 'public."s1@1.0"' having dependent snapshots
      HINT:  purge all dependent snapshots first
      CONTEXT:  referenced column: purge_snapshot_internal
      SQL statement "SELECT db4ai.purge_snapshot_internal(i_schema, i_name)"
      PL/pgSQL function db4ai.purge_snapshot(name,name) line 71 at PERFORM
      
  4. 相关GUC参数

    • db4ai_snapshot_mode:

      Snapshot有2种模式:MSS(物化模式,存储数据实体)和CSS(计算模式,存储增量信息)。Snapshot可在MSS和CSS之间切换快照模式,默认是MSS模式。

    • db4ai_snapshot_version_delimiter:

      该参数为数据表快照版本分隔符。“@”为数据表快照的默认版本分隔符。

    • db4ai_snapshot_version_separator

      该参数为数据表快照子版本分隔符。“.”为数据表快照的默认版本分隔符。

  5. DB4AI Schema下的数据表快照详情db4ai.snapshot。

    openGauss=# \d db4ai.snapshot
                           Table "db4ai.snapshot"
      Column   |            Type             |         Modifiers
    -----------+-----------------------------+---------------------------
     id        | bigint                      |
     parent_id | bigint                      |
     matrix_id | bigint                      |
     root_id   | bigint                      |
     schema    | name                        | not null
     name      | name                        | not null
     owner     | name                        | not null
     commands  | text[]                      | not null
     comment   | text                        |
     published | boolean                     | not null default false
     archived  | boolean                     | not null default false
     created   | timestamp without time zone | default pg_systimestamp()
     row_count | bigint                      | not null
    Indexes:
        "snapshot_pkey" PRIMARY KEY, btree (schema, name) TABLESPACE pg_default
        "snapshot_id_key" UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default
    

DB4AI: 数据库驱动AI

 说明: 命名空间DB4AI是本功能的私有域,不支持在DB4AI的命令空间下创建函数索引(functional index)。 

 — END —

版权声明:程序员胖胖胖虎阿 发表于 2022年8月30日 下午8:08。
转载请注明:DB4AI: 数据库驱动AI | 胖虎的工具箱-编程导航

相关文章

暂无评论

暂无评论...