<jupyter_start><jupyter_text>Тестирование производительности OLAP-хранилищ Clickhouse и VerticaВ рамках исследования были рассмотрены Clickhouse и Vertica.  Запуск<jupyter_code>!docker-compose up -d<jupyter_output>/usr/lib/python3/dist-packages/requests/__init__.py:89: RequestsDependencyWarning: urllib3 (1.26.5) or chardet (3.0.4) doesn't match a supported version!
  warnings.warn("urllib3 ({}) or chardet ({}) doesn't match a supported "
Creating network "olap_default" with the default driver
Creating volume "olap_ugc_bench_clickhouse_data" with default driver
Creating volume "olap_ugc_bench_vertica_data" with default driver
Creating olap_clickhouse_1 ... 
Creating olap_vertica_1    ... 
[2Bting olap_clickhouse_1 ... [32mdone[0m[2A[2K<jupyter_text>Загрузка тестовых данныхСтруктура тестовой таблицы| ----------- | ----------- || id      | UUID       || user_id   | UUID        || movie_id   | UUID        || viewed_frame   | Int        |<jupyter_code>!export PYTHONPATH="${PYTHONPATH}:${PWD}/../.."<jupyter_output><empty_output><jupyter_text>Загрузка тестовых данных в Clickhouse<jupyter_code>from multiprocessing import Pool

import tqdm as tqdm
from clickhouse_driver import Client

from utils.fake_data_gen import Row, generate_fake_data
from config import CLICKHOUSE_HOST, NUMBER_OF_BATCHES, UPLOAD_BATCH_SIZE

client = Client(CLICKHOUSE_HOST)


def upload_batch(batch):
    columns = ', '.join(Row._fields)
    client.execute(
        f'INSERT INTO views ({columns}) VALUES',
        batch
    )
test_data = generate_fake_data(UPLOAD_BATCH_SIZE, NUMBER_OF_BATCHES)

with Pool() as pool:
    r = list(tqdm.tqdm(
        pool.imap(upload_batch, test_data),
        total=NUMBER_OF_BATCHES
    ))<jupyter_output>100%|██████████| 1000/1000 [00:49<00:00, 20.08it/s]<jupyter_text>Загрузка тестовых данных в Vertica<jupyter_code>from multiprocessing import Pool

import tqdm
import vertica_python

from utils.fake_data_gen import Row, generate_fake_data
from config import NUMBER_OF_BATCHES, UPLOAD_BATCH_SIZE, VERTICA_CONNECTION_PARAMS


def upload_batch(batch):
    with vertica_python.connect(**VERTICA_CONNECTION_PARAMS) as connection:
        columns = ', '.join(Row._fields)
        placeholders = ', '.join(['%s'] * len(Row._fields))
        cursor = connection.cursor()
        cursor.executemany(
            f'INSERT INTO views ({columns}) VALUES ({placeholders})',
            batch
        )
test_data = generate_fake_data(UPLOAD_BATCH_SIZE, NUMBER_OF_BATCHES)

with Pool() as pool:
    r = list(tqdm.tqdm(
        pool.imap(upload_batch, test_data),
        total=NUMBER_OF_BATCHES
    ))<jupyter_output>100%|██████████| 1000/1000 [01:35<00:00, 10.51it/s]<jupyter_text>Выполнение тестовых запросов Использовались следующие тестовые запросы:<jupyter_code>from utils.test_queries import QUERIES

for name, query in QUERIES.items():
    print(f'{name}')
    print(f'{query}')
    print("----------")<jupyter_output>average_movies_per_user
select avg(movies_watched) from ( select count(movie_id) as movies_watched from views group by user_id    ) as movies_count;
----------
average_view_times
select avg(viewed_frame) from views;
----------
top_20_users_by_total_view_time
select user_id, sum(viewed_frame) as view_time from views group by user_id order by view_time desc limit 20;
----------
top_20_movies_by_view_time
select movie_id, max(viewed_frame) as view_time from views group by movie_id order by view_time desc limit 20;
----------
unique_movies_count
select count(distinct movie_id) from views;
----------
unique_users_count
select count(distinct user_id) from views;
----------<jupyter_text>Тестирование Clickhouse<jupyter_code>from clickhouse_driver import Client

from utils.test_queries import QUERIES
from utils.timer import timer
from config import BENCHMARK_ITERATIONS, CLICKHOUSE_HOST

client = Client(CLICKHOUSE_HOST)


@timer(BENCHMARK_ITERATIONS)
def execute_query(query: str):
    client.execute(query)
for name, query in QUERIES.items():
    print(f'{name}')
    execute_query(query)<jupyter_output>average_movies_per_user
Average execution time (over 10 runs): 0.3282 seconds

average_view_times
Average execution time (over 10 runs): 0.0179 seconds

top_20_users_by_total_view_time
Average execution time (over 10 runs): 0.2414 seconds

top_20_movies_by_view_time
Average execution time (over 10 runs): 0.1241 seconds

unique_movies_count
Average execution time (over 10 runs): 0.0636 seconds

unique_users_count
Average execution time (over 10 runs): 0.1454 seconds<jupyter_text>Тестирование Vertica<jupyter_code>import vertica_python

from utils.test_queries import QUERIES
from utils.timer import timer
from config import BENCHMARK_ITERATIONS, VERTICA_CONNECTION_PARAMS


@timer(BENCHMARK_ITERATIONS)
def execute_query(query: str):
    with vertica_python.connect(**VERTICA_CONNECTION_PARAMS) as connection:
        cursor = connection.cursor()
        cursor.execute(query)
for name, query in QUERIES.items():
    print(f'{name}')
    execute_query(query)<jupyter_output>average_movies_per_user
Average execution time (over 10 runs): 0.4929 seconds

average_view_times
Average execution time (over 10 runs): 0.2027 seconds

top_20_users_by_total_view_time
Average execution time (over 10 runs): 0.4802 seconds

top_20_movies_by_view_time
Average execution time (over 10 runs): 1.2334 seconds

unique_movies_count
Average execution time (over 10 runs): 0.6284 seconds

unique_users_count
Average execution time (over 10 runs): 0.4870 seconds<jupyter_text>ВыводClickhouse показывает **лучшую производительность** во всех проведенных тестах!  Остановка<jupyter_code>!docker-compose down -v<jupyter_output>/usr/lib/python3/dist-packages/requests/__init__.py:89: RequestsDependencyWarning: urllib3 (1.26.5) or chardet (3.0.4) doesn't match a supported version!
  warnings.warn("urllib3 ({}) or chardet ({}) doesn't match a supported "
Stopping olap_vertica_1    ... 
Stopping olap_clickhouse_1 ... 
[2Bping olap_vertica_1    ... [32mdone[0m[2A[2KRemoving olap_vertica_1    ... 
Removing olap_clickhouse_1 ... 
[1BRemoving network olap_default32mdone[0m
Removing volume olap_ugc_bench_clickhouse_data
Removing volume olap_ugc_bench_vertica_data
{'fraction_non_alphanumeric': 0.1023495333118764, 'fraction_numerical': 0.031219826198905697, 'mean_word_length': 4.964491362763916, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '494990', 'license': 'MIT', 'n_tokens_mistral': 2345, 'n_tokens_neox': 2194, 'n_words': 488, 'path': 'services/movies_ugc/benchmarks/olap/BENCHMARKS.ipynb', 'repo_name': 'fuodorov/yacinema', 'size': '6214'}