ClickHouse 如何结合自家的GNDT算法库CatBoost来做机器学习

作者|Altinity
译者|Liu Zhiyong
编辑|Cai Fangfang

AI前线导语： ClickHouse是俄罗斯第一大搜索引擎Yandex开发的列式储存数据库。据测试报告，这个列式储存数据库的性能大幅超越了很多商业MPP数据库软件，比如Vertica（HP Vertica成为MPP列式存储商业数据库的高性能代表，Facebook就购买了Vertica数据用于用户行为分析。）、InfiniDB。2016年6月15日，ClickHouse由Yandex开源。
2017年7月21日，Yandex开源了CatBoost，这是一种支持类别特征，基于梯度提升决策树的机器学习方法。它是MartixNet算法的继承者。它能够在数据稀疏的情况下教机器学习。特别是在没有像视频、文本、图像这类感官性数据时，CatBoost也能根据事务性数据或历史数据进行操作。CatBoost采用了Apache许可证，它相较其他框架的最大优点是测试精准度高，而且只需少量调试，且性能良好。
今天我们就来看看Altinity前几日撰写的教程[1]，给我们演示了ClickHouse如何结合自家开源的GBDT算法库CatBoost来做机器学习。Altinity是一家ClickHouse的服务供应商。

更多干货内容请关注微信公众号“AI 前线”，（ID：ai-front）

ClickHouse非常灵活，可用于各种用例。现在最有意思的技术领域之一就是机器学习，而ClickHouse非常适合作为非常快速的数据源。几个月前，ClickHouse团队实现了对机器学习算法的支持，这使得在ClickHouse数据上运行机器算法变得更容易、更快捷。他们从开源的Yandex CatBoost[2]算法开始，但它可以在未来扩展到其他算法。在本文中，我们发布了关于如何使用ClickHouse来运行CatBoost模型的教程。

CatBoost

CatBoost是最先进的开源梯度提升决策树算法库。

CatBoost是由Yandex研究人员和工程师开发的，在公司内部广泛应用于排列任务、预测和提出建议。Yandex称其是通用的，可以应用于广泛的领域和各种各样的问题。

CatBoost与传统的梯度提升决策树有什么不同？

• 精确性：在标准基准测试[3]中引入竞争；
• 健壮性：减少了对大量超参数调优的需求；
• 易用性：提供了与scikit集成的Python接口，以及R和命令行接口；
• 实用性：可直接地、以及可伸缩性地使用分类特征；
• 可扩展性：允许指定自定义损失函数。

如需获取更详细的信息，请参阅TechCrunch发布的文章《Yandex开源CatBoost》[4]。

CatBoost结合ClickHouse

现在让我们看看CatBoost如何结合ClickHouse。本文其余部分转载自GitHub上的clickhouse-presentations项目提供的教程[5]，ClickHouse方面，由Yandex工程师Nikolai Kochetov负责此项目。那么让我们开始吧。

启动容器

本教程使用Docker容器。启动容器的命令如下：

$ sudo docker run -it -p 8888:8888 yandex/tutorial-catboost-clickhouse

结果，您可以通过http://localhost:8888来访问Jupyter Notebook中的CatBoost和ClickHouse教程：

• kaggle_amazon_catboost.ipynb - 使用Amazon Employee Access Challenge数据的CatBoost功能演示。
• catboost_with_clickhouse.ipynb - 在ClickHouse（当前文档）中应用CatBoost模型。

VirtualBox 映像

可以使用VirtualBox映像[6]替代Docker容器。在启动后，可以在http://localhost:8888上使用相同的材料。

命令提示符

您需要使用命令提示符才能使用ClickHouse。您可以使用Docker容器或VirtualBox映像中的命令提示符。另一个选项是使用jupyter notebook命令提示符。打开主页并选择New→Terminal。所有其他命令都可以直接复制到终端窗口中。

使用ClickHouse

ClickHouse支持多种不同的接口，包括用于流行编程语言的HTTP，JDBC，ODBC和许多第三方库。但是，本教程是通过TCP使用本地客户端。

命令行界面

ClickHouse服务器已经在Docker容器中运行。要连接到服务器，请键入以下命令：

$ clickhouse client --host 127.0.0.1

结果，ClickHouse显示一个输入的邀请：

:)

尝试写一个“Hello,world!”查询：

:) SELECT 'Hello, world!'

如果一切正常，那就进入下一个步骤。

使用ClickHouse作为计算器

运行简单的计算查询：

:) SELECT 2 + 2 * 2
:) SELECT cos(pi() / 3)
:) SELECT pow(e(), pi())

system.numbers表有一个单独的列，称为number。该列存储从0开始的整数。我们来看前10个：

:) SELECT number FROM system.numbers LIMIT 10

现在计算前100个整数的平方和：

:) SELECT sum(pow(number, 2))
FROM 
(
    SELECT *
    FROM system.numbers 
    LIMIT 101
)

最后一个例子是使用system.numbers表中的子查询创建的。首先选择数字从0到100的单数列，然后计算平方，并使用求和聚合函数计算平方和。

创建一个表并插入数据

为训练样本创建一个表格：

:) CREATE TABLE amazon_train
(
    date Date MATERIALIZED today(), 
    ACTION UInt8, 
    RESOURCE UInt32, 
    MGR_ID UInt32, 
    ROLE_ROLLUP_1 UInt32, 
    ROLE_ROLLUP_2 UInt32, 
    ROLE_DEPTNAME UInt32, 
    ROLE_TITLE UInt32, 
    ROLE_FAMILY_DESC UInt32, 
    ROLE_FAMILY UInt32, 
    ROLE_CODE UInt32
)
ENGINE = MergeTree(date, date, 8192);

为了将数据插入到ClickHouse中，您需要使用Linux命令行。使用Crtl+C来退出ClickHouse（或者，键入“quit”、“logout”、“exit”、“quit”、“q”或等价命令）。然后运行以下命令：

$ clickhouse client --host 127.0.0.1 --query 'INSERT INTO amazon_train FORMAT CSVWithNames' < ~/amazon/train.csv

检查数据是否被插入：

$ clickhouse client --host 0.0.0.0
:) SELECT count() FROM amazon_train

计算ACTION列的平均值：

:) SELECT avg(ACTION) FROM amazon_train

使用已训练的模型

用模型配置创建一个配置文件：

<models>
    <model>
        <!-- Model type. Now catboost only. -->
        <type>catboost</type>
        <!-- Model name. -->
        <name>amazon</name>
        <!-- Path to trained model. -->
        <path>/home/catboost/tutorial/catboost_model.bin</path>
        <!-- Update interval. -->
        <lifetime>0</lifetime>
    </model>
</models>

ClickHouse配置文件应该已经有这样的设置：

<models_config>/home/catboost/models/*_model.xml</models_config>

要验证一下，运行以下命令：

tail /etc/clickhouse-server/config.xml

让我们确保模型正在工作。计算表中前10行的预测值：

:) SELECT 
    modelEvaluate('amazon', 
                  RESOURCE,
                  MGR_ID,
                  ROLE_ROLLUP_1,
                  ROLE_ROLLUP_2,
                  ROLE_DEPTNAME,
                  ROLE_TITLE,
                  ROLE_FAMILY_DESC,
                  ROLE_FAMILY,
                  ROLE_CODE) > 0 AS prediction, 
    ACTION AS target
FROM amazon_train
LIMIT 10;

现在我们来预测一下概率：

:) SELECT 
    modelEvaluate('amazon', 
                  RESOURCE,
                  MGR_ID,
                  ROLE_ROLLUP_1,
                  ROLE_ROLLUP_2,
                  ROLE_DEPTNAME,
                  ROLE_TITLE,
                  ROLE_FAMILY_DESC,
                  ROLE_FAMILY,
                  ROLE_CODE) AS prediction,
    1\. / (1 + exp(-prediction)) AS probability, 
    ACTION AS target
FROM amazon_train
LIMIT 10;

计算样本上的LogLoss：

:) SELECT -avg(tg * log(prob) + (1 - tg) * log(1 - prob)) AS logloss
FROM 
(
    SELECT 
        modelEvaluate('amazon', 
                      RESOURCE,
                      MGR_ID,
                      ROLE_ROLLUP_1,
                      ROLE_ROLLUP_2,
                      ROLE_DEPTNAME,
                      ROLE_TITLE,
                      ROLE_FAMILY_DESC,
                      ROLE_FAMILY,
                      ROLE_CODE) AS prediction,
        1\. / (1\. + exp(-prediction)) AS prob, 
        ACTION AS tg
    FROM amazon_train
);

与CatBoost集成

我们不需要为测试样本创建一个表，而是使用catBoostPool表函数。列描述在/home/catboost/tutorial/amazon/test.cd文件中，它看起来像这样：

0    DocId    id
1    Categ    RESOURCE
2    Categ    MGR_ID
3    Categ    ROLE_ROLLUP_1
4    Categ    ROLE_ROLLUP_2
5    Categ    ROLE_DEPTNAME
6    Categ    ROLE_TITLE
7    Categ    ROLE_FAMILY_DESC
8    Categ    ROLE_FAMILY
9    Categ    ROLE_CODE

看看从catBoostPool返回的临时表结构：

:) DESCRIBE TABLE catBoostPool('amazon/test.cd', 'amazon/test.tsv')
┌─name─────────────┬─type───┬─default_type─┬─default_expression─┐
│ Categ0           │ String │              │                    │
│ Categ1           │ String │              │                    │
│ Categ2           │ String │              │                    │
│ Categ3           │ String │              │                    │
│ Categ4           │ String │              │                    │
│ Categ5           │ String │              │                    │
│ Categ6           │ String │              │                    │
│ Categ7           │ String │              │                    │
│ Categ8           │ String │              │                    │
│ DocId            │ String │              │                    │
│ id               │ String │ ALIAS        │ DocId              │
│ RESOURCE         │ String │ ALIAS        │ Categ0             │
│ MGR_ID           │ String │ ALIAS        │ Categ1             │
│ ROLE_ROLLUP_1    │ String │ ALIAS        │ Categ2             │
│ ROLE_ROLLUP_2    │ String │ ALIAS        │ Categ3             │
│ ROLE_DEPTNAME    │ String │ ALIAS        │ Categ4             │
│ ROLE_TITLE       │ String │ ALIAS        │ Categ5             │
│ ROLE_FAMILY_DESC │ String │ ALIAS        │ Categ6             │
│ ROLE_FAMILY      │ String │ ALIAS        │ Categ7             │
│ ROLE_CODE        │ String │ ALIAS        │ Categ8             │
└──────────────────┴────────┴──────────────┴────────────────────┘

该表包含了列描述文件中的每个特性和别名的列。计算前10行的概率：

:) SELECT 
    id,
    modelEvaluate('amazon', *) AS prediction,
    1\. / (1\. + exp(-prediction)) AS probability
FROM catBoostPool('amazon/test.cd', 'amazon/test.tsv')
LIMIT 10;

在从catBoostPool读取数据时，可以使用*代替modelEvaluate的参数。计算测试样本的答案并将结果写入文件：

SELECT 
    toUInt32(id) AS Id, 
    1\. / (1 + exp(-modelEvaluate('amazon', *))) AS Action
FROM catBoostPool('amazon/test.cd', 'amazon/test.tsv') 
INTO OUTFILE 'submission.tsv'
FORMAT CSVWithNames;

在Kaggle[7]上提交结果（您可以使用jupyter notebook界面从Docker容器中下载submission.tsv）看看你在排行榜上排名如何？