Applying a Catboost Model in ClickHouse

    With this instruction, you will learn to apply pre-trained models in ClickHouse by running model inference from SQL.

    To apply a CatBoost model in ClickHouse:

    1. Create a Table.
    2. .
    3. Integrate CatBoost into ClickHouse (Optional step).
    4. .

    For more information about training CatBoost models, see Training and applying models.

    You can reload CatBoost models if the configuration was updated without restarting the server using and RELOAD MODELS system queries.

    If you do not have the yet, install it.

    Note

    Docker is a software platform that allows you to create containers that isolate a CatBoost and ClickHouse installation from the rest of the system.

    Before applying a CatBoost model:

    1. Pull the from the registry:

    This Docker image contains everything you need to run CatBoost and ClickHouse: code, runtime, libraries, environment variables, and configuration files.

    2. Make sure the Docker image has been successfully pulled:

    2. REPOSITORY                            TAG                 IMAGE ID            CREATED             SIZE
    3. yandex/tutorial-catboost-clickhouse   latest              622e4d17945b        22 hours ago        1.37GB

    3. Start a Docker container based on this image:

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

    To create a ClickHouse table for the training sample:

    1. Start ClickHouse console client in the interactive mode:

    1. $ clickhouse client

    The ClickHouse server is already running inside the Docker container.

    2. Create the table using the command:

    3. Exit from ClickHouse console client:

    1. :) exit

    To insert the data:

    1. Run the following command:

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

    2. Start ClickHouse console client in the interactive mode:

    1. $ clickhouse client

    3. Make sure the data has been uploaded:

    Note

    Optional step. The Docker image contains everything you need to run CatBoost and ClickHouse.

    To integrate CatBoost into ClickHouse:

    1. Build the evaluation library.

    The fastest way to evaluate a CatBoost model is compile libcatboostmodel.<so|dll|dylib> library. For more information about how to build the library, see CatBoost documentation.

    2. Create a new directory anywhere and with any name, for example, data and put the created library in it. The Docker image already contains the library data/libcatboostmodel.so.

    3. Create a new directory for config model anywhere and with any name, for example, models.

    5. Describe the model configuration:

    1. <models>
    2.     <model>
    3.         <!-- Model type. Now catboost only. -->
    4.         <!-- Model name. -->
    5.         <name>amazon</name>
    6.         <!-- Path to trained model. -->
    8.         <!-- Update interval. -->
    9.         <lifetime>0</lifetime>
    10.     </model>
    11. </models>

    6. Add the path to CatBoost and the model configuration to the ClickHouse configuration:

    1. <!-- File etc/clickhouse-server/config.d/models_config.xml. -->
    2. <catboost_dynamic_library_path>/home/catboost/data/libcatboostmodel.so</catboost_dynamic_library_path>
    3. <models_config>/home/catboost/models/*_model.xml</models_config>

    Note

    You can change path to the CatBoost model configuration later without restarting server.

    For test model run the ClickHouse client $ clickhouse client.

    Let’s make sure that the model is working:

    1. :) SELECT
    2.     modelEvaluate('amazon',
    3.                 RESOURCE,
    4.                 MGR_ID,
    5.                 ROLE_ROLLUP_1,
    6.                 ROLE_ROLLUP_2,
    7.                 ROLE_DEPTNAME,
    8.                 ROLE_TITLE,
    9.                 ROLE_FAMILY_DESC,
    10.                 ROLE_CODE) > 0 AS prediction,
    12. FROM amazon_train
    13. LIMIT 10

    Note

    Function returns tuple with per-class raw predictions for multiclass models.

    Let’s predict the probability:

    Note

    More info about exp() function.

    Let’s calculate LogLoss on the sample:

    1. :) SELECT -avg(tg * log(prob) + (1 - tg) * log(1 - prob)) AS logloss
    2. FROM
    3. (
    4.     SELECT
    5.         modelEvaluate('amazon',
    6.                     RESOURCE,
    7.                     MGR_ID,
    8.                     ROLE_ROLLUP_1,
    9.                     ROLE_ROLLUP_2,
    10.                     ROLE_DEPTNAME,
    11.                     ROLE_TITLE,
    12.                     ROLE_FAMILY_DESC,
    13.                     ROLE_FAMILY,
    14.                     ROLE_CODE) AS prediction,
    15.         1. / (1. + exp(-prediction)) AS prob,
    16.         ACTION AS tg

    Note

    More info about and log() functions.