Движки таблиц `Executable` и `ExecutablePool`

Движки таблиц Executable и ExecutablePool позволяют определить таблицу, строки которой генерируются заданным вами скриптом (путём записи строк в stdout). Исполняемый скрипт хранится в каталоге user_scripts и может читать данные из любого источника.

Таблицы Executable: скрипт запускается при каждом запросе
Таблицы ExecutablePool: поддерживают пул постоянных процессов и используют процессы из этого пула для чтения

При необходимости можно указать один или несколько входных запросов, результаты которых передаются в stdin, откуда их читает скрипт.

Создание таблицы `Executable`

Для движка таблицы Executable требуются два параметра: имя скрипта и формат входных данных. При необходимости можно также передать один или несколько входных запросов:

Executable(script_name, format, [input_query...])

Вот соответствующие настройки для таблицы Executable:

send_chunk_header
- Описание: отправляет количество строк в каждом фрагменте перед передачей фрагмента на обработку. Эта настройка позволяет писать скрипт эффективнее, заранее выделяя некоторые ресурсы
- Значение по умолчанию: false
command_termination_timeout
- Описание: тайм-аут завершения команды в секундах
- Значение по умолчанию: 10
command_read_timeout
- Описание: тайм-аут чтения данных из stdout команды в миллисекундах
- Значение по умолчанию: 10000
command_write_timeout
- Описание: тайм-аут записи данных в stdin команды в миллисекундах
- Значение по умолчанию: 10000

Рассмотрим пример. Следующий скрипт Python называется my_script.py и сохранён в папке user_scripts. Он считывает число i и выводит i случайных строк, причём перед каждой строкой выводится число, отделённое символом табуляции:

#!/usr/bin/python3

import sys
import string
import random

def main():

    # Считать входное значение
    for number in sys.stdin:
        i = int(number)

        # Сгенерировать несколько случайных строк
        for id in range(0, i):
            letters = string.ascii_letters
            random_string =  ''.join(random.choices(letters ,k=10))
            print(str(id) + '\t' + random_string + '\n', end='')

        # Сбросить результаты в stdout
        sys.stdout.flush()

if __name__ == "__main__":
    main()

Следующая таблица my_executable_table строится на основе вывода my_script.py, который генерирует 10 случайных строк каждый раз, когда вы выполняете SELECT из my_executable_table:

CREATE TABLE my_executable_table (
   x UInt32,
   y String
)
ENGINE = Executable('my_script.py', TabSeparated, (SELECT 10))

При создании таблицы операция завершается сразу и скрипт не вызывается. При выполнении запроса к my_executable_table скрипт вызывается:

SELECT * FROM my_executable_table

┌─x─┬─y──────────┐
│ 0 │ BsnKBsNGNH │
│ 1 │ mgHfBCUrWM │
│ 2 │ iDQAVhlygr │
│ 3 │ uNGwDuXyCk │
│ 4 │ GcFdQWvoLB │
│ 5 │ UkciuuOTVO │
│ 6 │ HoKeCdHkbs │
│ 7 │ xRvySxqAcR │
│ 8 │ LKbXPHpyDI │
│ 9 │ zxogHTzEVV │
└───┴────────────┘

Передача результатов запроса в скрипт

Пользователи сайта Hacker News оставляют комментарии. В Python есть библиотека для обработки естественного языка (nltk) с SentimentIntensityAnalyzer, который позволяет определять, являются ли комментарии положительными, отрицательными или нейтральными, а также присваивать им значение от -1 (очень отрицательный комментарий) до 1 (очень положительный комментарий). Давайте создадим таблицу Executable, которая вычисляет тональность комментариев Hacker News с помощью nltk. В этом примере используется таблица hackernews, описанная здесь. Таблица hackernews включает столбец id типа UInt64 и столбец comment типа String. Начнем с определения таблицы Executable:

CREATE TABLE sentiment (
   id UInt64,
   sentiment Float32
)
ENGINE = Executable(
    'sentiment.py',
    TabSeparated,
    (SELECT id, comment FROM hackernews WHERE id > 0 AND comment != '' LIMIT 20)
);

Несколько комментариев о таблице sentiment:

Файл sentiment.py сохраняется в папке user_scripts (это папка по умолчанию для настройки user_scripts_path)
Формат TabSeparated означает, что наш Python-скрипт должен генерировать строки исходных данных со значениями, разделёнными табуляцией
Запрос выбирает два столбца из hackernews. Python-скрипту потребуется разобрать значения этих столбцов из входящих строк

Вот определение sentiment.py:

#!/usr/local/bin/python3.9

import sys
import nltk
from nltk.sentiment import SentimentIntensityAnalyzer

def main():
    sentiment_analyzer = SentimentIntensityAnalyzer()

    while True:
        try:
            row = sys.stdin.readline()
            if row == '':
                break

            split_line = row.split("\t")

            id = str(split_line[0])
            comment = split_line[1]

            score = sentiment_analyzer.polarity_scores(comment)['compound']
            print(id + '\t' + str(score) + '\n', end='')
            sys.stdout.flush()
        except BaseException as x:
            break

if __name__ == "__main__":
    main()

Несколько комментариев о нашем Python-скрипте:

Чтобы это работало, вам нужно выполнить nltk.downloader.download('vader_lexicon'). Это можно было бы добавить в скрипт, но тогда загрузка выполнялась бы при каждом запросе к таблице sentiment, а это неэффективно
Каждое значение row будет отдельной строкой в результирующем наборе запроса SELECT id, comment FROM hackernews WHERE id > 0 AND comment != '' LIMIT 20
Входящая строка разделена символами табуляции, поэтому мы разбираем id и comment с помощью функции Python split
Результат polarity_scores — это объект JSON с несколькими значениями. Мы решили просто взять значение compound из этого объекта JSON
Помните, что таблица sentiment в ClickHouse использует формат TabSeparated и содержит два столбца, поэтому функция print разделяет эти столбцы табуляцией

Каждый раз, когда вы пишете запрос, выбирающий строки из таблицы sentiment, выполняется запрос SELECT id, comment FROM hackernews WHERE id > 0 AND comment != '' LIMIT 20, а его результат передаётся в sentiment.py. Давайте проверим:

SELECT *
FROM sentiment

Ответ будет выглядеть так:

┌───────id─┬─sentiment─┐
│  7398199 │    0.4404 │
│ 21640317 │    0.1779 │
│ 21462000 │         0 │
│ 25168863 │         0 │
│ 25168978 │   -0.1531 │
│ 25169359 │         0 │
│ 25169394 │   -0.9231 │
│ 25169766 │    0.4137 │
│ 25172570 │    0.7469 │
│ 25173687 │    0.6249 │
│ 28291534 │         0 │
│ 28291669 │   -0.4767 │
│ 28291731 │         0 │
│ 28291949 │   -0.4767 │
│ 28292004 │    0.3612 │
│ 28292050 │    -0.296 │
│ 28292322 │         0 │
│ 28295172 │    0.7717 │
│ 28295288 │    0.4404 │
│ 21465723 │   -0.6956 │
└──────────┴───────────┘

Создание таблицы `ExecutablePool`

Синтаксис ExecutablePool похож на Executable, но у таблицы ExecutablePool есть несколько важных настроек, которые доступны только для неё:

pool_size
- Описание: Размер пула процессов. Если размер равен 0, ограничения по размеру отсутствуют
- Значение по умолчанию: 16
max_command_execution_time
- Описание: Максимальное время выполнения команды в секундах
- Значение по умолчанию: 10

Мы можем легко преобразовать приведённую выше таблицу sentiment, чтобы использовать ExecutablePool вместо Executable:

CREATE TABLE sentiment_pooled (
   id UInt64,
   sentiment Float32
)
ENGINE = ExecutablePool(
    'sentiment.py',
    TabSeparated,
    (SELECT id, comment FROM hackernews WHERE id > 0 AND comment != '' LIMIT 20000)
)
SETTINGS
    pool_size = 4;

ClickHouse будет по мере необходимости поддерживать 4 процесса при выполнении клиентом запросов к таблице sentiment_pooled.

​Создание таблицы Executable

​Передача результатов запроса в скрипт

​Создание таблицы ExecutablePool

Создание таблицы `Executable`

Передача результатов запроса в скрипт

Создание таблицы `ExecutablePool`