openGauss数据库源码解析系列文章——AI技术（三）

def forecast(args):
    …
    # 如果没有指定预测方式，则默认使用’auto_arima’算法
    if not args.forecast_method:
        forecast_alg = get_instance('auto_arima')
    else:
        forecast_alg = get_instance(args.forecast_method)
    # 指标预测功能函数
    def forecast_metric(name, train_ts, save_path=None):
        …
            forecast_alg.fit(timeseries=train_ts)
            dates, values = forecast_alg.forecast(
                period=TimeString(args.forecast_periods).standard)
        date_range = "{start_date}~{end_date}".format(start_date=dates[0],
                                               end_date=dates[-1])
        display_table.add_row(
            [name, date_range, min(values), max(values), sum(values) / len(values)]
        )
# 校验存储路径
        if save_path:
            if not os.path.exists(os.path.dirname(save_path)):
                os.makedirs(os.path.dirname(save_path))
            with open(save_path, mode='w') as f:
                for date, value in zip(dates, values):
                    f.write(date + ',' + str(value) + '\n')
    # 从本地sqlite中抽取需要的数据
    with sqlite_storage.SQLiteStorage(database_path) as db:
        if args.metric_name:
            timeseries = db.get_timeseries(table=args.metric_name, period=max_rows)
            forecast_metric(args.metric_name, timeseries, args.save_path)
        else:
# 获取sqlite中所有的表名
            tables = db.get_all_tables()
            # 从每个表中抽取训练数据进行预测
for table in tables:
                timeseries = db.get_timeseries(table=table, period=max_rows)
                forecast_metric(table, timeseries)
# 输出结果
    print(display_table.get_string())

# 代码远程部署
def deploy(args):
    print('Please input the password of {user}@{host}: '.format(user=args.user, host=args.host))
# 格式化代码远程部署指令
    command = 'sh start.sh --deploy {host} {user} {project_path}' \
        .format(user=args.user,
                host=args.host,
                project_path=args.project_path)
    # 判断指令执行情况
if subprocess.call(shlex.split(command), cwd=SBIN_PATH) == 0:
        print("\nExecute successfully.")
    else:
        print("\nExecute unsuccessfully.")
… 
# 展示当前监控的参数
def show_metrics():
…

# 项目总入口
def main():
   …

class Daemon:
    """
    This class implements the function of running a process in the background."""
    
    def __init__(self):
        …
def daemon_process(self):
        # 注册退出函数
        atexit.register(lambda: os.remove(self.pid_file))
        signal.signal(signal.SIGTERM, handle_sigterm)
# 启动进程
    @staticmethod
    def start(self):
        try:
            self.daemon_process()
        except RuntimeError as msg:
            abnormal_exit(msg)

        self.function(*self.args, **self.kwargs)
    # 停止进程
    def stop(self):
        if not os.path.exists(self.pid_file):
            abnormal_exit("Process not running.")

        read_pid = read_pid_file(self.pid_file)
        if read_pid > 0:
            os.kill(read_pid, signal.SIGTERM)
        if read_pid_file(self.pid_file) < 0:
            os.remove(self.pid_file)

def agent_main():
…
    # 初始化通道管理器
cm = ChannelManager()
# 初始化数据源
    source = DBSource()
    http_sink = HttpSink(interval=params['sink_timer_interval'], url=url, context=context)
    source.channel_manager = cm
    http_sink.channel_manager = cm
    # 获取参数文件里面的功能函数
    for task_name, task_func in get_funcs(metric_task):
        source.add_task(name=task_name,
                        interval=params['source_timer_interval'],
                        task=task_func,
                        maxsize=params['channel_capacity'])
    source.start()
    http_sink.start()

# 通过时间戳获取最近一段时间的数据
def select_timeseries_by_timestamp(self, table, period):
…
# 通过编号获取最近一段时间的数据
def select_timeseries_by_number(self, table, number):
     …

class AlgModel(object):
    """
    This is the base class for forecasting algorithms.
    If we want to use our own forecast algorithm, we should follow some rules.
    """

    def __init__(self):
        pass

    @abstractmethod
    def fit(self, timeseries):
        pass

    @abstractmethod
    def forecast(self, period):
        pass

    def save(self, model_path):
        pass

    def load(self, model_path):
        pass

python main.py start –role collector

python main.py stop –role collector

python main.py start --user xxx --host xxx.xxx.xxx.xxx –project-path xxx –role collector

python main.py stop --user xxx --host xxx.xxx.xxx.xxx –project-path xxx –role collector

python main.py show_metrics

python main.py forecast –metric-name io_read –forecast-periods 60S –save-path predict_result

python main.py deploy –user xxx –host xxx.xxx.xxx.xxx –project-path xxx

static void ModelTrainInternal(const char* templateName, const char* modelName, ModelAccuracy** mAcc)
{
  …
    /* 对于树形模型调用对应的训练接口 */
    char* trainResultJson = TreeModelTrain(modelinfo, labels);
    /* 解析返回结果 */
    …
    ModelTrainInfo* info = GetModelTrainInfo(jsonObj);
    cJSON_Delete(jsonObj);
    /* 更新模型信息 */
    Relation modelRel = heap_open(OptModelRelationId, RowExclusiveLock);
   …
    UpdateTrainRes(values, datumsMax, datumsAcc, nLabel, mAcc, info, labels);

    HeapTuple modelTuple = SearchSysCache1(OPTMODEL, CStringGetDatum(modelName));
   …
    HeapTuple newTuple = heap_modify_tuple(modelTuple, RelationGetDescr(modelRel), values, nulls, replaces);
    simple_heap_update(modelRel, &newTuple->t_self, newTuple);
CatalogUpdateIndexes(modelRel, newTuple);
…
}

char* TreeModelTrain(Form_gs_opt_model modelinfo, char* labels)
{
    char* filename = (char*)palloc0(sizeof(char) * MAX_LEN_TEXT);
    char* buf = NULL;
    /* configure阶段 */
    ConfigureModel(modelinfo, labels, &filename);

    /* 将编码好的数据写入临时文件 */
    SaveDataToFile(filename);

    /* Train阶段 */
    buf = TrainModel(modelinfo, filename);
    return buf;
}

 def fit(self, filename):
        keras.backend.clear_session()
        set_session(self.session)
        with self.graph.as_default():
            # 根据模型入参和出参维度变化情况，判断是否需要初始化模型
            feature, label, need_init = self.parse(filename) 
            os.environ['CUDA_VISIBLE_DEVICES'] = '0'
            epsilon = self.model_info.make_epsilon()
            if need_init: # 冷启动训练
                epoch_start = 0
                self.model = self._build_model(epsilon)
            else: # 增量训练
                epoch_start = int(self.model_info.last_epoch)
                ratio_error = ratio_error_loss_wrapper(epsilon)
                ratio_acc_2 = ratio_error_acc_wrapper(epsilon, 2)
                self.model = load_model(self.model_info.model_path,
                                        custom_objects={'ratio_error': ratio_error, 'ratio_acc': ratio_acc_2})
            self.model_info.last_epoch = int(self.model_info.max_epoch) + epoch_start
            self.model_info.dump_dict()
            log_path = os.path.join(settings.PATH_LOG, self.model_info.model_name + '_log.json')
            if not os.path.exists(log_path):
                os.mknod(log_path, mode=0o600)
            # 训练日志记录回调函数
            json_logging_callback = LossHistory(log_path, self.model_info.model_name, self.model_info.last_epoch)
            # 数据分割
            X_train, X_val, y_train, y_val = \
                train_test_split(feature, label, test_size=0.1)
            # 模型训练
            self.model.fit(X_train, y_train, epochs=self.model_info.last_epoch,
                           batch_size=int(self.model_info.batch_size), validation_data=(X_val, y_val),
                           verbose=0, initial_epoch=epoch_start, callbacks=[json_logging_callback])
            # 记录模型checkpoint
            self.model.save(self.model_info.model_path)
            val_pred = self.model.predict(X_val)
            val_re = get_ratio_errors_general(val_pred, y_val, epsilon)
            self.model_logger.debug(val_re)
            del self.model
            return val_re

char* TreeModelPredict(const char* modelName, char* filepath, const char* ip, int port)
{
    …
    if (!TryConnectRemoteServer(conninfo, &buf)) {
        DestroyConnInfo(conninfo);
        ParseResBuf(buf, filepath, "AI engine connection failed.");
        return buf;
    }

    switch (buf[0]) {
        case '0': {
            ereport(NOTICE, (errmodule(MOD_OPT_AI), errmsg("Model setup successfully.")));
            break;
        }
        case 'M': {
            ParseResBuf(buf, filepath, "Internal error: missing compulsory key.");
            break;
        }
…
    }
    /* Predict阶段 */
    …
    if (!TryConnectRemoteServer(conninfo, &buf)) {
        ParseResBuf(buf, filepath, "AI engine connection failed.");
        return buf;
    }
    switch (buf[0]) {
        case 'M': {
            ParseResBuf(buf, filepath, "Internal error: fail to load the file to predict.");
            break;
        }
        case 'S': {
            ParseResBuf(buf, filepath, "Internal error: session is not loaded, model setup required.");
            break;
        }
        default: {
            break;
        }
    }
    return buf;
}

class RnnModel():
    def _build_model(self, epsilon):
        model = Sequential()
        model.add(LSTM(units=int(self.model_info.hidden_units), return_sequences=True, input_shape=(None, int(self.model_info.feature_length))))
        model.add(LSTM(units=int(self.model_info.hidden_units), return_sequences=False))
        model.add(Dense(units=int(self.model_info.hidden_units), activation='relu'))
        model.add(Dense(units=int(self.model_info.hidden_units), activation='relu'))
        model.add(Dense(units=int(self.model_info.label_length), activation='sigmoid'))
        optimizer = keras.optimizers.Adadelta(lr=float(self.model_info.learning_rate), rho=0.95)
        ratio_error = ratio_error_loss_wrapper(epsilon)
        ratio_acc_2 = ratio_error_acc_wrapper(epsilon, 2)
        model.compile(loss=ratio_error, metrics=[ratio_acc_2], optimizer=optimizer)
        return model

INSERT INTO gs_opt_model VALUES(‘rlstm’, ‘model_name’, ‘host_ip’, ‘port’);

enable_resource_track = on;
enable_resource_record = on;

SELECT gather_encoding_info('db_name');

SELECT model_train_opt('template_name', 'model_name');

SELECT track_train_process('host_ip', 'port');

EXPLAIN (..., predictor 'model_name') SELECT ...

Row Adapter  (cost=110481.35..110481.35 rows=100 p-time=99..182 width=100) (actual time=375.158..375.160 rows=2 loops=1)

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT,
    list_of_features_to_exclude TEXT,
    weights                     TEXT
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.gbdt_train($1, $2, $3, $4, $5, $6, $7, 30::INTEGER);
$$ LANGUAGE sql VOLATILE;

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT,
    list_of_features_to_exclude TEXT
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.gbdt_train($1, $2, $3, $4, $5, $6, NULL::TEXT);
$$ LANGUAGE sql VOLATILE;

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.gbdt_train($1, $2, $3, $4, $5, NULL::TEXT);
$$ LANGUAGE sql VOLATILE;

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.gbdt_train(
    training_table_name         TEXT,
    output_table_name           TEXT,
    id_col_name                 TEXT,
    dependent_variable          TEXT,
    list_of_features            TEXT,
    list_of_features_to_exclude TEXT,
    weights                     TEXT,
    num_trees                   INTEGER,
    num_random_features         INTEGER,
    max_tree_depth              INTEGER,
    min_split                   INTEGER,
    min_bucket                  INTEGER,
    num_bins                    INTEGER,
    null_handling_params        TEXT,
    is_classification           BOOLEAN,
    predict_dt_prob             TEXT,
    learning_rate               DOUBLE PRECISION,
    verbose                     BOOLEAN,
    sample_ratio                DOUBLE PRECISION
)
RETURNS VOID AS $$
PythonFunction(gbdt, gbdt, gbdt_fit)
$$ LANGUAGE plpythonu VOLATILE;

def gbdt_fit(schema_madlib,training_table_name, output_table_name,
        id_col_name, dependent_variable, list_of_features,
        list_of_features_to_exclude, weights,
        num_trees, num_random_features,
        max_tree_depth, min_split, min_bucket, num_bins,
        null_handling_params, is_classification,
        predict_dt_prob = None, learning_rate = None, 
        verbose=False, **kwargs):
     …
    plpy.execute("""ALTER TABLE {training_table_name} DROP COLUMN IF EXISTS gradient CASCADE
                """.format(training_table_name=training_table_name))
                            
    create_summary_table(output_table_name, null_proxy, bins['cat_features'], 
                        bins['con_features'], learning_rate, is_classification, predict_dt_prob, 
                        num_trees, training_table_name)

def gbdt_predict(schema_madlib, test_table_name, model_table_name, output_table_name, id_col_name, **kwargs):

    num_tree = plpy.execute("""SELECT COUNT(*) AS count FROM {model_table_name}""".format(**locals()))[0]['count']
    if num_tree == 0:
        plpy.error("The GBDT-method has no trees")
    

    elements = plpy.execute("""SELECT * FROM {model_table_name}_summary""".format(**locals()))[0]
…

DROP TABLE IF EXISTS houses;
CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,  size INT, lot INT);
INSERT INTO houses VALUES
(1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100),
(2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000),
(3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500),
 …
(12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000),
(13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000),
(14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000),
(15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000);

SET search_path="$user",public,madlib;
SET behavior_compat_options = 'bind_procedure_searchpath';

DROP TABLE IF EXISTS houses_svm, houses_svm_summary; 
SELECT madlib.svm_classification('public.houses','public.houses_svm','price < 100000','ARRAY[1, tax, bath, size]');

\x on
SELECT * FROM houses_svm;
\x off

-[ RECORD 1 ]------+-----------------------------------------------------------------
coef               | {.113989576847,-.00226133300602,-.0676303607996,.00179440841072}
loss               | .614496714256667
norm_of_gradient   | 108.171180769224
num_iterations     | 100
num_rows_processed | 15
num_rows_skipped   | 0
dep_var_mapping    | {f,t}

DROP TABLE IF EXISTS houses_pred; 
SELECT madlib.svm_predict('public.houses_svm','public.houses','id','public.houses_pred');

SELECT *, price < 100000 AS actual FROM houses JOIN houses_pred USING (id) ORDER BY id;

id | tax  | bedroom | bath | price  | size |  lot  | prediction | decision_function | actual
----+------+---------+------+--------+------+-------+------------+-------------------+--------
  1 |  590 |       2 |    1 |  50000 |  770 | 22100 | t          |      .09386721875 | t
  2 | 1050 |       3 |    2 |  85000 | 1410 | 12000 | t          |     .134445058042 | t
 …
 14 | 2070 |       2 |    3 | 148000 | 1550 | 14000 | f          |  -1.9885277913972 | f
 15 |  650 |       3 |  1.5 |  65000 | 1450 | 12000 | t          |   1.1445697772786 | t
(15 rows)

SELECT COUNT(*) FROM houses_pred JOIN houses USING (id) WHERE houses_pred.prediction != (houses.price < 100000);

count
-------
     3
(1 row)

DROP TABLE IF EXISTS houses_svm_gaussian, houses_svm_gaussian_summary, houses_svm_gaussian_random; 
SELECT madlib.svm_classification( 'public.houses','public.houses_svm_gaussian','price < 100000','ARRAY[1, tax, bath, size]','gaussian','n_components=10', '', 'init_stepsize=1, max_iter=200' );

DROP TABLE IF EXISTS houses_pred_gaussian; 
SELECT madlib.svm_predict('public.houses_svm_gaussian','public.houses','id', 'public.houses_pred_gaussian');
SELECT COUNT(*) FROM houses_pred_gaussian JOIN houses USING (id) WHERE houses_pred_gaussian.prediction != (houses.price < 100000);

count 
-------+    
0 
(1 row)