title: Ð¡ÑÐ°Ð²Ð½Ð¸ÑÐµÐ»ÑÐ½ÑÐ¹ Ð°Ð½Ð°Ð»Ð¸Ð· ÑÑÐµÐ´ÑÑÐ² Ð¡Ð£ÐÐ SQL Server Ð¸ ClickHouse Ð´Ð»Ñ Ð°Ð½Ð°Ð»Ð¸ÑÐ¸ÑÐµÑÐºÐ¾Ð¹ Ð¾Ð±ÑÐ°Ð±Ð¾ÑÐºÐ¸ Ð´Ð°Ð½Ð½ÑÑ
title: Ð²ÑÐ¿ÑÑÐºÐ½Ð°Ñ ÐºÐ²Ð°Ð»Ð¸ÑÐ¸ÐºÐ°ÑÐ¸Ð¾Ð½Ð½Ð°Ñ ÑÐ°Ð±Ð¾ÑÐ° Ð±Ð°ÐºÐ°Ð»Ð°Ð²ÑÐ°
titleType: Subtitle
title: Comparative analysis of SQL Server and ClickHouse DBMS tools for analytical data processing
Creators: name: ÐÐ¾Ð»ÑÑÐ°Ð»Ð¾Ð²Ð°, Ð®Ð»Ð¸Ñ
Publisher: name: Ð¡Ð°Ð½ÐºÑ-ÐÐµÑÐµÑÐ±ÑÑÐ³ÑÐºÐ¸Ð¹ Ð¿Ð¾Ð»Ð¸ÑÐµÑÐ½Ð¸ÑÐµÑÐºÐ¸Ð¹ ÑÐ½Ð¸Ð²ÐµÑÑÐ¸ÑÐµÑ ÐÐµÑÑÐ° ÐÐµÐ»Ð¸ÐºÐ¾Ð³Ð¾
Year: 2023
Subjects: Ð±Ð°Ð·Ñ Ð´Ð°Ð½Ð½ÑÑ
Ð¡Ð£ÐÐ
ÐºÐ¾Ð»Ð¾Ð½Ð¾ÑÐ½Ð°Ñ Ð±Ð°Ð·Ð° Ð´Ð°Ð½Ð½ÑÑ
ÑÐµÐ»ÑÑÐ¸Ð¾Ð½Ð½Ð°Ñ Ð±Ð°Ð·Ð° Ð´Ð°Ð½Ð½ÑÑ
NoSQL
SQL Server
ClickHouse
databases
DBMS
columnar database
relational database

description: Ð Ð´Ð°Ð½Ð½Ð¾Ð¹ ÑÐ°Ð±Ð¾ÑÐµ ÑÐ°ÑÑÐ¼Ð°ÑÑÐ¸Ð²Ð°ÑÑÑÑ Ð¾ÑÐ¾Ð±ÐµÐ½Ð½Ð¾ÑÑÐ¸ Ð°Ð½Ð°Ð»Ð¸Ð·Ð° Ð´Ð°Ð½Ð½ÑÑ Ð² ÐºÐ¾Ð»Ð¾Ð½Ð¾ÑÐ½Ð¾Ð¹ Ð¡Ð£ÐÐ ClickHouse Ð¸ Ð¿ÑÐ¾Ð²Ð¾Ð´Ð¸ÑÑÑ ÑÑÐ°Ð²Ð½ÐµÐ½Ð¸Ðµ ÐµÐµ ÑÑÑÐµÐºÑÐ¸Ð²Ð½Ð¾ÑÑÐ¸ Ñ ÑÐµÐ»ÑÑÐ¸Ð¾Ð½Ð½Ð¾Ð¹ Ð¡Ð£ÐÐ Microsoft SQL Server. ÐÐ°Ð´Ð°ÑÐ¸, ÐºÐ¾ÑÐ¾ÑÑÐµ ÑÐµÑÐ°Ð»Ð¸ÑÑ Ð² ÑÐ¾Ð´Ðµ ÑÐ°Ð±Ð¾ÑÑ: 1) ÐÐ·ÑÑÐµÐ½Ð¸Ðµ ÑÐµÐ¾ÑÐµÑÐ¸ÑÐµÑÐºÐ¸Ñ Ð¾ÑÐ½Ð¾Ð² Ð¸ Ð¿ÑÐ¸Ð½ÑÐ¸Ð¿Ð¾Ð² ÑÐ°Ð±Ð¾ÑÑ ÑÐµÐ»ÑÑÐ¸Ð¾Ð½Ð½ÑÑ Ð¸ ÐºÐ¾Ð»Ð¾Ð½Ð¾ÑÐ½ÑÑ Ð¡Ð£ÐÐ. 2) Ð¡Ð±Ð¾Ñ Ð¸ Ð¿Ð¾Ð´Ð³Ð¾ÑÐ¾Ð²ÐºÐ° ÑÐµÑÑÐ¾Ð²ÑÑ Ð´Ð°Ð½Ð½ÑÑ Ð´Ð»Ñ Ð¿ÑÐ¾Ð²ÐµÐ´ÐµÐ½Ð¸Ñ ÑÐºÑÐ¿ÐµÑÐ¸Ð¼ÐµÐ½ÑÐ°Ð»ÑÐ½Ð¾Ð³Ð¾ Ð¸ÑÑÐ»ÐµÐ´Ð¾Ð²Ð°Ð½Ð¸Ñ. 3) Ð Ð°Ð·ÑÐ°Ð±Ð¾ÑÐºÐ° ÑÐµÑÑÐ¾Ð²ÑÑ Ð·Ð°Ð¿ÑÐ¾ÑÐ¾Ð² Ð½Ð° Ð²ÑÐ±Ð¾ÑÐºÑ Ð´Ð°Ð½Ð½ÑÑ Ð¸ Ð°Ð³ÑÐµÐ³Ð¸ÑÐ¾Ð²Ð°Ð½Ð¸Ðµ Ð² Ð¾Ð±ÐµÐ¸Ñ Ð¡Ð£ÐÐ. 4) ÐÐ½Ð°Ð»Ð¸Ð· Ð¿Ð¾Ð»ÑÑÐµÐ½Ð½ÑÑ ÑÐµÐ·ÑÐ»ÑÑÐ°ÑÐ¾Ð² Ð¸ ÑÑÐ°Ð²Ð½ÐµÐ½Ð¸Ðµ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÐ¸ Ð¡Ð£ÐÐ SQL Server Ð¸ ClickHouse. 5) Ð¤Ð¾ÑÐ¼ÑÐ»Ð¸ÑÐ¾Ð²Ð°Ð½Ð¸Ðµ Ð²ÑÐ²Ð¾Ð´Ð¾Ð² Ð¸ ÑÐµÐºÐ¾Ð¼ÐµÐ½Ð´Ð°ÑÐ¸Ð¹ Ð¿Ð¾ Ð²ÑÐ±Ð¾ÑÑ Ð¡Ð£ÐÐ Ð´Ð»Ñ Ð°Ð½Ð°Ð»Ð¸ÑÐ¸ÑÐµÑÐºÐ¾Ð¹ Ð¾Ð±ÑÐ°Ð±Ð¾ÑÐºÐ¸ Ð´Ð°Ð½Ð½ÑÑ Ð² Ð·Ð°Ð²Ð¸ÑÐ¸Ð¼Ð¾ÑÑÐ¸ Ð¾Ñ ÐºÐ¾Ð½ÐºÑÐµÑÐ½Ð¾Ð¹ Ð·Ð°Ð´Ð°ÑÐ¸. ÐÐµÑÐ²Ð°Ñ Ð³Ð»Ð°Ð²Ð° Ð¿Ð¾ÑÐ²ÑÑÐµÐ½Ð° Ð¿Ð¾ÑÑÐ°Ð½Ð¾Ð²ÐºÐµ Ð·Ð°Ð´Ð°ÑÐ¸ Ð¸ Ð²Ð²ÐµÐ´ÐµÐ½Ð¸Ñ Ð² Ð¿ÑÐµÐ´Ð¼ÐµÑÐ½ÑÑ Ð¾Ð±Ð»Ð°ÑÑÑ.Â  ÐÑÐ¾ÑÐ°Ñ Ð³Ð»Ð°Ð²Ð° Ð²ÐºÐ»ÑÑÐ°ÐµÑ Ð² ÑÐµÐ±Ñ Ð¾Ð±Ð·Ð¾Ñ Ð¸ÑÑÐ»ÐµÐ´ÑÐµÐ¼ÑÑ Ð¡Ð£ÐÐ, Ð¾Ð¿Ð¸ÑÐ°Ð½Ð¸Ðµ Ð¸Ñ Ð¿ÑÐµÐ¸Ð¼ÑÑÐµÑÑÐ² Ð¸ Ð½ÐµÐ´Ð¾ÑÑÐ°ÑÐºÐ¾Ð² Ð² Ð°Ð½Ð°Ð»Ð¸ÑÐ¸ÑÐµÑÐºÐ¾Ð¹ Ð¾Ð±ÑÐ°Ð±Ð¾ÑÐºÐµ Ð´Ð°Ð½Ð½ÑÑ. Ð¢ÑÐµÑÑÑ Ð³Ð»Ð°Ð²Ð° ÑÐ²Ð»ÑÐµÑÑÑ Ð¿Ð¾Ð´Ð³Ð¾ÑÐ¾Ð²Ð¸ÑÐµÐ»ÑÐ½ÑÐ¼ ÑÑÐ°Ð¿Ð¾Ð¼ ÑÐºÑÐ¿ÐµÑÐ¸Ð¼ÐµÐ½ÑÐ°, Ð²ÐºÐ»ÑÑÐ°ÑÑÐ¸Ð¼ Ð² ÑÐµÐ±Ñ Ð¾Ð¿Ð¸ÑÐ°Ð½Ð¸Ðµ Ð½Ð°Ð±Ð¾ÑÐ° ÑÐµÑÑÐ¾Ð²ÑÑ Ð´Ð°Ð½Ð½ÑÑ, Ð° ÑÐ°ÐºÐ¶Ðµ Ð¾Ð±ÑÑÑ ÑÐ¿ÑÐ°Ð²ÐºÑ Ð¾ ÑÑÐ°Ð½ÐµÐ½Ð¸Ð¸ Ð¸ ÑÑÐµÐ½Ð¸Ð¸ Ð´Ð°Ð½Ð½ÑÑ Ð² Ð´Ð²ÑÑ Ð¡Ð£ÐÐ. Ð§ÐµÑÐ²ÐµÑÑÐ°Ñ Ð³Ð»Ð°Ð²Ð° Ð¾Ð¿Ð¸ÑÑÐ²Ð°ÐµÑ ÑÐºÑÐ¿ÐµÑÐ¸Ð¼ÐµÐ½Ñ Ð¸ Ð¿Ð¾Ð»ÑÑÐµÐ½Ð½ÑÐµ ÑÐµÐ·ÑÐ»ÑÑÐ°ÑÑ. ÐÐ° Ð¾ÑÐ½Ð¾Ð²Ðµ Ð¿Ð¾Ð»ÑÑÐµÐ½Ð½ÑÑ Ð´Ð°Ð½Ð½ÑÑ Ð±ÑÐ»Ð¸ ÑÐ´ÐµÐ»Ð°Ð½Ñ Ð²ÑÐ²Ð¾Ð´Ñ Ð¾ÑÐ½Ð¾ÑÐ¸ÑÐµÐ»ÑÐ½Ð¾ ÑÑÑÐµÐºÑÐ¸Ð²Ð½Ð¾ÑÑÐ¸ ÐºÐ°Ð¶Ð´Ð¾Ð¹ Ð¡Ð£ÐÐ Ð² Ð¾Ð¿ÑÐµÐ´ÐµÐ»ÐµÐ½Ð½ÑÑ Ð·Ð°Ð´Ð°ÑÐ°Ñ Ð°Ð½Ð°Ð»Ð¸ÑÐ¸ÑÐµÑÐºÐ¾Ð¹ Ð¾Ð±ÑÐ°Ð±Ð¾ÑÐºÐ¸ Ð´Ð°Ð½Ð½ÑÑ.
description: The given work is devoted to studying the features of data mining in the ClickHouse columnar DBMS and compares its effectiveness with the Microsoft SQL Server relational DBMS. The research set the following goals: 1) Studying of the theoretical foundations and principles of operation of relational and columnar DBMS. 2) Collection and preparation of test data for the experimental research. 3) Development of test queries for data sampling and aggregation in both DBMS. 4) Analysis of the results and comparison of the performance of SQL Server and ClickHouse DBMS. 5) Formulation of conclusions and recommendations on the choice of a DBMS for analytical data processing, depending on the specific task. The first chapter is dedicated to problem statement and introduction to the subject area. The second chapter includes an overview of the investigated database management systems, describing their advantages and disadvantages in analytical data processing. The third chapter serves as a preparatory stage of the experiment, encompassing the description of the test dataset and providing general information about data storage and retrieval in both DBMS. The fourth chapter describes the experiment and the obtained results. Based on the collected data, conclusions were drawn regarding the effectiveness of each DBMS in specific analytical data processing tasks.