<p>ClickHouse* is a open source column-oriented database management system designed primarily for the use of online analytical processing (OLAP).</p><p>In June 2016, the project was made available as open-source software under the Apache 2 license.</p><h2>What is ClickHouse good for?</h2><p>You might be wondering what ClickHouse is good for and why use a column-oriented database?</p><p>Column-oriented databases are more suited to OLAP applications since only a limited number of table columns must be read for an analytical query. You only read the data you need in a column-oriented database.</p><p>Data is simpler to compress since it is read in packets. It's also easier to compress data in columns. This decreases the I/O volume even further. More data fits in the system cache as a result of the decreased I/O.</p><p>ClickHouse works 100-1000x faster than traditional approaches, according to <a href="https://clickhouse.com/" target="_blank" rel="nofollow">ClickHouse's</a> official data. And, it can process over a billion rows and tens of gigabytes of data per server per second.</p><h2>When to use ClickHouse</h2><p>As a typical OLAP engine with column-based storage, ClickHouse is suitable for scenarios that satisfy the following requirements::</p><ul><li>Large amount of batch insertions</li><li>Little or no modification on existing data</li><li>Wide table with lots of columns</li><li>Aggregation computation on selected columns</li><li>Fast SELECT queries</li></ul><p>You need to be cautious when it involves the following:</p><ul><li>OLTP usage, which UDPATE and Transaction support is a must</li><li>Key-Value like storage and single row query</li><li>Over-normalized data, which requires many JOIN operations during query</li><li>Blob or document storage</li></ul><h2>ClickHouse Architecture</h2><p>Let's take a look at the architecture of ClickHouse.</p><p>ClickHouse is using shared-nothing architecture in which the computing resource and the storage resource are tightly coupled as shown in Figure 1:</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/what_is_clickhouse_1a.png" alt="Figure 1: ClickHouse is using shared-nothing architecture" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 1: ClickHouse is using shared-nothing architecture</div></div></div><p>Each machine has its own data and the data are not shared across different servers. The shared-nothing architecture has its pros and cons.</p><p>The advantage is that it's super fast because of local access. In theory the limitation can only be the hardware resource of servers. However, this architecture has two major disadvantages because the computing resource and the storage resource are tightly coupled.</p><p>First, shared-nothing architecture limit the elasticity since the computing resource and storage resource cannot be scaled independently.</p><p>For example, when we don't need some computing resources, we still need keep these servers on in order to make the data on them available unless we copy that data to other servers. The data copy cost can not be ignored which prevents the system from scaling according to the demand.</p><p>Second, the data cannot be shared among servers, which leads to bad scalability as shown in Figure 1. If the data of one query only exists in one server, the query performance can not be further improved even if we add more servers.</p><h2>Challenges</h2><p>At some large technology enterprises such as ByteDance, the application is expanding and changing extremely fast and we were worried about very strong requirement on cluster scaling. Let's take a look at two cases.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/what_is_clickhouse_2.png" alt="Figure 2: server movement" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 2: server movement</div></div></div><p>The first case is shown in Figure 2. When cluster 2 needs more resources and cluster 1 has need some free resources, cluster 2 can borrow some resources from cluster 1. For example, if 2 servers are moving from cluster 1 to cluster 2.</p><p>We need to move data C and D on the 2 servers to remaining servers in cluster 1. Then, after moving the 2 servers to cluster 2, rebalancing of data also needs be performed in order to use them. There is extra high data copy and rebalance cost on the server movement, we may miss the best time to do the cluster scaling.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/what_is_clickhouse_3.png" alt="Figure 3: data movement" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 3: data movement</div></div></div><p>The second case is shown in Figure 3. Instead of migrating servers, we want to migrate applications to from cluster 1 to cluster 2. On the shared-nothing architecture, we have to move its data C and D first and then migrate the applications. The migration cost is very high when the data size is large.</p><p>Can we utilise the shared storage architecture uh to solve this problem? Can we support the shared-nothing architecture natively? By keeping these questions in mind, we start to explore <a href="https://bytehouse.cloud/blog/shared-everything-vs-shared-nothing" target="_blank">shared-everything architecture</a>, which you can read <a href="https://bytehouse.cloud/blog/shared-everything-vs-shared-nothing" target="_blank">here</a>.</p><p><strong>Related eBook (free download): </strong><a href="https://go.bytehouse.cloud/ebook-cloud-clickhouse" target="_blank">Reimagine ClickHouse</a></p><p><strong><em>About the author:</em></strong><em> Niu Zhaojie obtained his PhD from Nanyang Technological University in Singapore. Currently, he is a senior software engineer at Bytedance and building analytics database in cloud.</em></p><p><em>*ClickHouse is a trademark of ClickHouse, Inc.</em></p>

Companies like Uber, eBay, Spotify enhanced query performances and significantly reduced costs.

What is ClickHouse?

What is ClickHouse? Leverage ClickHouse to speed up your data analysis

<p>We live in a data-driven world where data is prevalent in our society today. Some say data is the new oil while others say it is the new soil. At the end of the day, it is up to companies that make use of the data they have on hand to make quicker and smarter decisions to beat their competitors.</p><p>There are typically two types of data processing engines: Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP).</p><h2>OLTP vs. OLAP</h2><p><strong>OLTP is often used for real-time processing of a large number of small database transactions of events and people via client applications. </strong>Some examples include the e-commerce industry, internet banking, ATM transactions, hotel bankings, and so on.</p><p><strong>OLAP is often used for high speed multi-dimensional analysis on large volumes of historical data from a centralised data store such as data warehouse, data mart, or any centralised database management system. </strong>Newly improved OLAP engines like ByteHouse, ClickHouse*, Google Big Query, Azure Analytics Services, and so on, allow for real-time data analysis of streaming sources.</p><p>OLAP engines usually focus on use cases such as business intelligence reporting, trend analysis, and advanced data mining while OLTP engines are good at e-commerce payment, banking transactions, hotel bookings, and etc.</p><p>There are several distinguishing characteristics of OLAP engines. They tend to have complicated queries involving aggregation over multiple dimensions. And, typical OLAP engines prefer read (select) queries than write (insert) operations.</p><h2>OLTP for Reporting?</h2><p>Yes, you read it correctly. In the past, reports used to be generated from OLTP systems, even before the evolution of the OLAP and Data Warehouse systems.</p><p>These reports used to be more static in nature. With the evolution of OLAP systems gave more flexibility to build multidimensional models to perform more complex data analytics. This compromises, but is not limited to, roll up, drilldown, slice, and dice of data, exploratory, explanatory, and descriptive analysis using various analytical tools.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/olap_vs_oltp.png" alt="OLTP and OLAP Systems in a typical Data Architecture Design" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">OLTP and OLAP Systems in a typical Data Architecture Design</div></div></div><h2>Key differences of OLTP vs. OLAP</h2><p>The following table illustrates key feature comparisons between OLTP and OLAP systems.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/OLTP_vs_OLAP.png" alt="OLTP vs OLAP Key Differences" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">OLTP vs OLAP Key Differences</div></div></div><p>ClickHouse is an open source column-oriented database management system designed primarily for the use of OLAP. Read more on "<a href="https://bytehouse.cloud/blog/what-is-clickhouse" target="_blank">What is ClickHouse</a>" or download our eBook <a href="https://go.bytehouse.cloud/ebook-cloud-clickhouse" target="_blank">Reimagine ClickHouse</a>.</p><p><strong>Register your <a href="https://bytehouse.cloud/signup" target="_blank">free trial of ByteHouse cloud data warehouse here</a>; now you can get US$400 credit for free.</strong></p><p><strong><em>About the author: </em></strong><em>Sudarsan is a customer solution architect at ByteDance; and, he is a CDMP (DAMA) certified data architect with prior experiences with National University of Singapore, Development Bank of Singapore, Standard Chartered Bank and BNP Paribas.</em></p><p><em>*ClickHouse is a trademark of <a href="https://clickhouse.com" target="_blank">ClickHouse, Inc.</a>.</em></p>

OLTP is designed more to support business operations while OLAP is created for decision making.

OLTP vs. OLAP | Key differences

What are the key differences between OLAP and OLTP?

<p>A modernised cloud-based data warehouse architecture is designed to address the limitations and challenges of traditional data warehouse architecture. Migrating to a cloud data warehouse will give an enterprise the opportunity to leverage on the cloud’s benefits for data management. </p><p>The following section delves deeper into the differences between traditional and cloud data warehouse architectures.</p><h2><strong>Traditional on-premise Data Warehouse</strong></h2><p>A traditional Data Warehouse Architecture has unique characteristics like a three-tier architectural approach.</p><p>The following three-tier architecture approach is one of the most commonly found approaches to on-premises data warehousing.</p><h2>Top Tier</h2><p>The Top Tier consists of the Client-side semantic layer of the architecture. The Transformed and Logic applied information stored in the Data Warehouse will be used and acquired for Business purposes in this Tier. This tier helps business and business support community to perform standard and ad hoc reporting, data exploration, data analytics and mining.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/data_warehouse_architecture.png" alt="data warehouse architecture" style="max-width: 100%; max-height: 800px;"></div></div><h2>Middle Tier</h2><p>The Middle Tier consists of the OLAP (Online Analytical Processing) engine. As this tier is located in the middle, it rightfully interacts with the information present in the Bottom Tier and passes on the insights to the Top Tier tools, which process the available information.</p><h2>Bottom Tier</h2><p>The Bottom Tier mainly contains Data Sources, ETL Tools, and Data Warehouse.</p><p><strong>1. Data Sources</strong></p><p>The Data Sources layer consists of the Source Data that is pulled or queried and provided to the Staging and ETL tools for further processing.</p><p><strong>2. Extract, Transform &amp; Load(ETL) Tools</strong></p><ul><li>Extract, Transform &amp; Load (ETL) tools are very vital because they help in combining Logic, source Data, and Schema into one and loads the information to the Data Warehouse Or Data Marts.</li></ul><ul><li>In Bottom-Up or Kimball's approach, data gets loaded into the Data Marts first in ETL process, and then it is pushed into Enterprise Data Warehouse.</li></ul><ul><li>In Top-Down or Inman's approach, data gets loaded into the Data Warehouse first in ETL process, and then it is pushed into subjective data marts.</li></ul><h2>Cloud based Data Warehouse</h2><p>Cloud-based data warehouse architecture is relatively new when compared to traditional data warehouse approaches. Cloud-based architecture means that the actual data warehouses are accessed through the cloud.</p><p>There are wide variety of options, each of which has different architectures for the same benefits of integrating, analyzing, and acting on data from different sources. The difference between a cloud-based data warehouse approach compared to that of a traditional approach include:</p><ul><li><strong>Up-front Capital Expenses: </strong>The different components required for traditional, on-premises data warehouses mandate pricey up-front capital expenses. Since the components of cloud architecture are accessed through the cloud and there is no need to purchase physical hardware, these expenses don’t apply.</li></ul><ul><li><strong>Operating Expenses:</strong> While businesses with on-premise data warehouses must deal with upgrade and maintenance costs, the cloud offers a low, pay-per-use model.</li></ul><ul><li><strong>Time to market:</strong> Cloud-based data warehouse architecture is substantially faster than on-premises data warehouse.</li></ul><ul><li><strong>Indirect Cost: </strong>Potential downtimes and time to market delays.</li></ul><ul><li><strong>Scalability:</strong> The elastic nature of cloud resources makes it ideal for scaling of big datasets. Additionally, cloud-based data warehousing options allows you to scale up, down, out and in.</li></ul><p>Some of the more notable cloud data warehouses in the market include ByteHouse Cloud Data Platform, Amazon Redshift, Google BigQuery, Snowflake, and Microsoft Azure SQL Data Warehouse.</p><p>You can test drive ByteHouse cloud platform <a href="https://bytehouse.cloud/signup" target="_blank">here</a>. Or, find out about the <a href="https://go.bytehouse.cloud/ebook-cloud-clickhouse" target="_blank">architecture design consideration</a> behind ByteHouse.</p>

Differences between traditional and cloud data warehouse architectures.

Traditional Data Warehouse vs. Cloud Data Warehouse

<p>Today we'll explore how we handle advertising placement data through ClickHouse. </p><p>In general, the operation staff needs to view the real-time effect of the advertising placement. Due to the characteristics of the business, data collected in one day is often mixed with those collated from the past few days. This system was originally implemented based on Druid. However, we discovered that Druid had some flaws in this case.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/druid_vs_clickhouse_case_study.png" alt="Druid vs. ClickHouse" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Druid vs. ClickHouse</div></div></div><p>ClickHouse is able to resolve majority of the problems faced by Druid. However, there are some challenges left to be fixed.</p><h2>Problem #1: Buffer Engine is unable to work with ReplicatedMergeTree</h2><p><strong>Problem &amp; Challenge: </strong>Clickhouse<strong> </strong>community provides Buffer Engine to solve the problem of generating too many parts in a single write, but it does not work well with ReplicatedMergeTree. The Buffer written for different Replicas only caches the newly written data on their respective nodes, resulting in inconsistent queries.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/buffer_engine_chart.png" alt="Buffer Engine" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Buffer Engine</div></div></div><p><br></p><p><strong>Solution</strong></p><p>We made the following enhancements to the Buffer Engine:</p><ul><li>Combine the Kafka/Buffer/MergeTree tables together to provide APIs that are easier to use.</li></ul><ul><li>Buffer is built into the Kafka engine, it can be turned on/off as an option, which is easier to use.</li></ul><ul><li>Use internal pipeline mode in Buffer table to handle multiple blocks.</li></ul><ul><li>Supports queries in ReplicatedMergeTree Engine.</li></ul><p>First of all, it ensures that only one node is consuming for a pair of replicas, so only one out of two Buffer tables has data. If a query is sent to the unconsumed replica, a special query logic is built to read data from the Buffer table of the other replica.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/enhance_buffer_engine.png" alt="Enhance Buffer Engine" style="max-width: 100%; max-height: 800px;"></div></div><p><strong>Result</strong>: Enhance Buffer Engine to solve the problem of query consistency when Buffer Engine and ReplicatedMergeTree are used at the same time.</p><h2>Problem #2: Data loss and duplicate consumption issue after downtime</h2><p><strong>Challenge</strong>: ClickHouse lacks transactional support. During the service downtime, some parts of the batch write operations to ClickHouse might be lost or engaged in double consumption. This is due to the lack of transaction protection when the service is restarting.</p><p><strong>Solution:</strong></p><p>With reference to Druid's KIS solution, we manage Kafka's Offset by ourselves. To achieve the atomic semantics of single batch consumption/writing: the implementation chooses to bind Offset and Parts data together to enhance the stability of consumption. During each consumption, a transaction is created by default, and the transaction is responsible for writing Part data and Offset to disk together. If there is a failure, the transaction will roll back the Offset and the written part, then consume again.</p><p><strong>Result: </strong>Ensures the atomicity of each inserted data and enhances the stability of data consumption.</p>

A quick case study on handling advertising placement data through ClickHouse

Real-time Ad Placement Through ClickHouse (vs. Druid)

Case Study: Real-time Advertisement Placement Through ClickHouse

<p>Real-time analytics focuses on preparing and measuring data immediately after it enters the database. It allows business users to gain insights and are able to draw conclusions as soon as it enters the system.</p><p>Real-time analysis enables organisations to document live streams of data, process them at a fast pace, and extract insights or perform operations in real time. This allows businesses to react immediately if issues arise or seize opportunities to make key decisions.</p><p>Today, most real-time analytic systems support semi-structured data and other data formats to avoid ETL processes.</p><p>This helps businesses achieve low data latency and allows for the optimisation for computer performance. This reduces the resources required to steadily process incoming data and allows efficient query execution on high volumes of data.</p><p>In comparison, batch processing is an efficient way of processing large volumes of data. Data is collected, entered, and processed in the system to produce batch results.</p><h2>How does real-time analytics fit in an organisation's analytics strategy?</h2><p>Most companies adopt a variety of analytic approaches, depending on the types of data, workloads, and business issues they are trying to resolve. Analytics are classified into the following:</p><ul><li>Descriptive Analytics: Provides analysis based on high volumes of historical data</li></ul><ul><li>Diagnostic Analytics: Provides analysis to target specific business queries</li></ul><ul><li>Predictive Analysis: Analysis on current and historical data to make key business projections</li></ul><ul><li>Prescriptive Analytics: Analysis on current and historical data to make more informed business decisions</li></ul><ul><li>Cognitive Analytics: Analytical systems are stimulated to think like humans, allowing the system to learn and extract data patterns</li></ul><p>Predictive analytics are the entry point for “advanced analytics", where decision-making may be fueled by real-time information. Thus, predictive, prescriptive, and cognitive analytics are use cases that benefit from the capability of real-time data analytics.</p><h2>Examples and Use Cases</h2><p>In the following section, we will delve into some successful real-time data analytics use cases.</p><h2><strong>Marketing campaigns</strong></h2><p>When running a marketing campaign, most organizations rely on A/B testing for real-time optimisation. With the capability to access data instantly, marketing team can adjust campaign parameters to boost success.</p><p>For example, if a company decides to run an ad campaign and retrieves real-time data of the click-through-rate and conversion rate, they will be able to better adjust the parameters to target the audience directly.</p><h2><strong>Financial trading</strong></h2><p>Financial institutions often have to make key decisions within the millisecond.</p><p>With the help of real-time analytics, traders can take advantage of information from various sources like financial databases, new sources, social media, and more. They will gain real-time insight on the market, helping them to make better trading decisions.</p><h2><strong>Financial operations</strong></h2><p>Financial operations are key to the organisation. They help to ensure that financial statements are always accurate to help businesses make the best decisions. Real-time analytics helps businesses to spot errors and reduce operation risks.</p><p>Financial statements always must be accurate to help inform the best decisions for the business. Analytics in real-time helps to spot errors and can aid in reducing operation risks.</p><p>The software has the ability to match records through account reconciliation, store data securely in a centralised system, and transform raw data into valuable insights through real-time analytics.</p><p>This makes all the difference in a team's ability to remain accurate, agile, and ahead of the curve.</p><h2><strong>Credit scoring</strong></h2><p>Credit scores are important for any financial institution. With the help of real-time analytics, institutions can approve or deny loans almost instantly.</p><h2><strong>Healthcare</strong></h2><p>Real-time analytics has the potential to improve the patient-doctor relationship.</p><p>During hospitalisation, it is key for doctors to have constant updates on the vitals of the patients. This is done through the usage of wearable smart and IoT devices.</p><p><strong><em>About the author: </em></strong><em>Sudarsan is a customer solution architect at ByteDance; and, he is a CDMP (DAMA) certified data architect with prior experiences with National University of Singapore, Development Bank of Singapore, Standard Chartered Bank and BNP Paribas.</em></p>

Real-time analytics focuses on preparing and measuring data immediately after it enters the database

What is Real-Time Data Analytics?

What is Real-Time Analytics?

<p>Online Analytical Processing (OLAP) was introduced a couple of decades ago. Computation power was limited and business scaling was difficult without hardware component replacements</p><p>However, the introduction of OLAP was groundbreaking in the Business Intelligence industry. Users were able to perform multidimensional analysis on large volumes of data.</p><p>Before the integration of OLAP, it was always challenging to run queries on Data Warehouse. Drawing data from multiple tables was always tedious. Despite the usage of various joins in attempts to aggregate and group large volumes of data, it was still a huge setback for the team.</p><p>The execution of these queries took long periods of time to complete, and often affected the overall system performance.</p><p>With the introduction of OLAP, these problems were resolved by pulling data out of the data warehouse. All aggregate measures were pre-computed at every level for every dimension to build the multidimensional cubes.</p><p>This helps to quicken report running and enabled business users to run ad hoc queries, slice and dice data, drill down and mine data for uncovering patterns, and better analyse the data sets.</p><p>There are three types of OLAP engines: Relational OLAP (ROLAP), Multi-dimensional OLAP (MOLAP), and Hybrid OLAP (HOLAP).</p><h2><strong>Relational Online Analytical Processing (ROLAP)</strong></h2><p>ROLAP allows for relational database data storage in the form of rows and columns. It then dynamically presents the data to the end user in multidimensional cubes.</p><p>Data can be accessed by submitting queries to the ROLAP database.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/ROLAP_MOLAP_HOLAP_a.png" alt="ROLAP" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">ROLAP</div></div></div><p>Large volumes of data can be processed but the performance of the query is largely dependent on the size of the data retrieved. With an increase in data, comes the less accurate query performance.</p><h2><strong>Multi-dimensional Online Analytical Processing (MOLAP)</strong></h2><p>MOLAP retrieves its data from a Centralised Data Store (Data Warehouse/Data Mart) and other designated sources. It pre-computes all aggregate measures at every dimensional level to build the multidimensional cubes. After processing, the data is stored in MOLAP's database.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/ROLAP_MOLAP_HOLAP_b.png" alt="MOLAP" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">MOLAP</div></div></div><p>Business users can also access the multidimensional data for further analysis and reporting. Queries on MOLAP's database generally run faster due to batches of pre-computation processes applied.</p><h2><strong>Hybrid Online Analytical Processing (HOLAP)</strong></h2><p>HOLAP combines the capabilities of MOLAP and ROLAP to support its use cases.</p><p>HOLAP has a combination of pre-computed multidimensional cubes in MOLAP's database and relational data in ROLAP's database to support the various use cases of end users.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/ROLAP_MOLAP_HOLAP_c.png" alt="HOLAP" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">HOLAP</div></div></div><p>HOLAP stores data (multidimensional data and relational data) required by business users in its system.</p><p>Decisions have to be made to configure access for each OLAP system. All OLAP use cases have a predefined configuration for access and the ability to run queries.</p><h2><strong>Conclusion</strong></h2><p>Based on the current needs of your end users, data architects and designers can make the informed decision on choosing the best OLAP system that suits your needs.</p><p><strong>Also read: <a href="https://bytehouse.cloud/blog/what-is-clickhouse" target="_blank">What is ClickHouse</a>?</strong> (A fast open-source OLAP DBMS)</p><p><strong><em>About the author: </em></strong><em>Sudarsan is a customer solution architect at ByteDance; and, he is a CDMP (DAMA) certified data architect with prior experiences with National University of Singapore, Development Bank of Singapore, Standard Chartered Bank and BNP Paribas.</em></p>

3 types of OLAP engines: Relational OLAP (ROLAP), Multidimensional OLAP (MOLAP), Hybrid OLAP (HOLAP)

Difference Between ROLAP, MOLAP, HOLAP

Difference between ROLAP, MOLAP, and HOLAP

Difference between ROLAP, MOLAP and HOLAP

<p>What's a Virtual Warehouse? A virtual data warehouse refers to compute clusters powering modern data warehouses such as ByteHouse.</p><p>A virtual warehouse can scale out on demand. It can provide resources such as CPU, memory, and temporary storage. These resources are key in performing the following operations:</p><ul><li>Executing SQL statements that require compute resources (e.g. retrieving rows from tables and views) for short and long-running queries.</li></ul><ul><li>Performing Data Manipulation Language (DML) operations, such as:</li><ul><li>Updating rows in tables (DELETE , INSERT , UPDATE).</li></ul></ul><ul><li>Loading data into tables</li></ul><ul><li>Unloading data from tables</li></ul><p><em>You can register a free <a href="https://bytehouse.cloud/signup?utm_source=bh_blog&amp;utm_medium=post&amp;utm_campaign=bh_blogging" target="_blank" rel="nofollow">ByteHouse account here</a> and try creating a virtual warehouse on ByteHouse.</em></p><h2>Advantages of Virtual Warehouse</h2><p>Virtual warehouses have the following advantages:</p><p><strong>Read-write separation and resource separation. </strong>Separate Virtual Warehouses can be created to perform data-loading tasks, or run queries in real-time. Creating Virtual Warehouses also allow different departments or business lines to have clear separation of resources, avoiding potential impact on performances between different queries.</p><p><strong>One-click scaling.</strong> Virtual Warehouses can easily scale up or down according to business requirements for cost-effective resource utilisation.</p><p><strong>Access control. </strong>Virtual warehouses can also be granted to different permissions into various roles, thus users can achieve fine-grained access control.</p><p><em>Find out how to create and manage virtual data warehouse on Bytehouse <a href="https://docs.bytehouse.cloud/en/docs/virtual-warehouses" target="_blank">here</a>.</em></p><h2>Enterprise warehouse and virtual warehouse</h2><p>There are three models of data warehouses from the architecture point of view: enterprise warehouse, data mart, and virtual warehouse.</p><ul><li><strong>The enterprise warehouse </strong>gathers all of the data on topics that cover the whole business. It necessitates in-depth business modeling and might take years to develop and construct.</li><li><strong>A data mart </strong>is a subset of a company's data that is useful to a certain group of people. Its application is limited to a few chosen functions, such as marketing data marts.</li><li><strong>A virtual warehouse</strong> refers to a collection of practical database views. It is simple to set up, but it necessitates more capacity for operating database servers.</li></ul><h2>Data virtualisation vs. data warehouse</h2><p>The key benefit of data virtualisation is that it allows us to construct a solution in a fraction of the time it takes to establish a data warehouse.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/data_virtualization.png" alt="Data virtualization" style="max-width: 100%; max-height: 800px;"></div></div><p>Data virtualisation has alternative names such as physical data warehouse, data federation, virtual database, and decentralised data warehouse. Data virtualisation enables organizations to integrate data from various sources, keeping the data in-place so that you can generate reports and dashboards to create business value from the data. It is an alternative approach to building a data warehouse, where you collect data from various sources and store a copy of the data in a new data store.</p><p>Unlike a data warehouse, data virtualisation does not require data to be stored in a single database repository. It virtualises an organisation’s siloed data from multiple sources into a single, unified data view from which a variety of BI tools can draw insights. Data is accessed in its native or original form “as is” but appears as a unified data warehouse to users.</p><p>The birth of the relational data warehouse in the early 1990s drove an enormous amount of innovation and revolutionised the way businesses managed data. Making data available to users in one single database repository with a query interface (SQL) was a game-changer. The ability to draw insights from different physical systems created new opportunities to improve business operations and create value.</p><h2>Difference between distributed and virtual data warehouse</h2><p><strong>Virtual data warehouse or data virtualisation</strong> refers to a layer that sits on top of existing databases and enables the user to query all of them as if they were one database (although they are logically and physically separated).</p><p><strong>Distributed data warehouse</strong> refers to the physical architecture of a single database. The data in the warehouse is integrated across the enterprise, and an integrated view is used only at central location of the enterprise. The enterprise operates on a centralised business model.</p><p><em>Register a free <a href="https://bytehouse.cloud/signup?utm_source=bh_blog&amp;utm_medium=post&amp;utm_campaign=bh_blogging" target="_blank" rel="nofollow">ByteHouse account here</a> and try creating a virtual warehouse.</em></p><p><strong><em>About the author: </em></strong><em>Sudarsan is a customer solution architect at ByteDance; and, he is a CDMP (DAMA) certified data architect with prior experiences with National University of Singapore, Development Bank of Singapore, Standard Chartered Bank and BNP Paribas.</em></p>

A virtual warehouse can scale out on demand, providing resources such as CPU, memory, and storage. 

What is Virtual Data Warehouse?

What is Virtual Warehouse?

<p>In a previous post, we discussed the <a href="https://bytehouse.cloud/blog/what-is-clickhouse" target="_blank">basics of ClickHouse</a> and the challenges presented in two cases, following which we start to consider the shared storage architecture which is also called the disaggregated compute and storage architecture as shown in Figure 1.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/clickhouse_decoupled_storage.png" alt="Disaggregated compute and storage architecture" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 1. Disaggregated compute and storage architecture</div></div></div><p>As shown in this figure, the computing resources are isolated from the data storage.  There are many computing resources. From the perspective of the computing resources, they will see a global shared pool which is the data storage layer. This means all of the data in this pool can be shared across all computing resources.</p><p>Under this architecture it has three major advantages. </p><h2>Advantages of Shared-everything Architecture</h2><p><strong>First it has better elasticity</strong> because computing resource and the storage are isolated and they can be scaled independently based on the demand for the resources. If we want more computing resources, we can add more computing instance; if we need more data storage, we can expand the volume for the data storage layer. </p><p><strong>Secondly, it has better scalability. </strong>As the data can be shared, theoretically we can scale out to utilise as much computing resources as possible. </p><p><strong>Lastly, it's more user friendly for the cluster managers as they don't need to worry about the data consistency, </strong>the data replica, and the data charging; and all of these can be delegated to the data storage. For example, companies can utilize the Amazon S3 to store the data. </p><h2>Disadvantages of Shared-everything Architecture</h2><p>However, this disaggregated computing architecture does have its disadvantages. Because the computing and the data storage are separated, it has extra latency for the remote access. </p><p>Since each architecture has its pros and cons, how do we select the proper architecture for your company unique requirements. Let’s compare the two from four dimensions as shown in Table 1.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/shared_everything_vs_shared_nothing.png" alt="Share-nothing architecture vs. Shared-everything architecture" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Table 1: Share-nothing architecture vs. Shared-everything architecture</div></div></div><p><strong>Environment</strong>. For “shared-nothing” architecture, the on-premise hardware resources are relatively stable as you cannot increase or decrease your hardware resource very frequently. For “shared-everything” architecture, the cloud infrastructure allows much higher degree of elasticity based on users’ demand.</p><p><strong>Performance</strong>. The “shared-nothing” architecture has very good performance. While “shared-everything” architecture has good performance, users should be able to tolerate minor downgrade for cold data access. We will discuss about ways to improve this in the later part of the article.</p><p><strong>Workload</strong>. If the work load is evenly distributed at any time, you can utilise your resources fully and there is no waste. Then, you can consider the shared-nothing architecture.</p><p>If your workload often sees some spike with the peak and off-peak pattern, you should consider the shared everything architecture because you need to scale up or scale down very efficiently to best utilise your resources utilisation, which can significantly reduce the cost. </p><p><strong>Experience</strong>. Using ClickHouse requires some expertise with a strong background or terminology on the operation or maintenance of this cluster. In the shared-everything architecture, because you can delegate some challenges to the existing system, it doesn't require strong domain knowledge.</p><p>These are some basic principles for how to choose different architecture. Next, we'll explore <a href="https://bytehouse.cloud/blog/cloud-native-clickhouse" target="_blank">cloud-native ClickHouse</a> and how we implement it at ByteDance in the next post.</p><p><strong><em>About the author:</em></strong><em> Niu Zhaojie obtained his PhD from Nanyang Technological University in Singapore. Currently, he is a senior software engineer at Bytedance and building analytics database in cloud.</em></p>

See a comparison of the two architectures and basic principles for choosing the right one.

Shared-Everything vs. Shared-Nothing Architecture

Shared-Everything Architecture vs. Shared-Nothing Architecture

<p>In a previous post, we shared a <a href="https://bytehouse.cloud/blog/shared-everything-vs-shared-nothing" target="_blank">comparison of shared-everything and shared-nothing architecture</a> and basic principles for how to choose these two different architectures. In this post, let us share the cloud-native ClickHouse.</p><p>We have three main objectives for building cloud-native ClickHouse (CNCH).</p><p><strong>The first objective is to support the disaggregated compute and the storage architecture </strong>natively so that we can utilize the resources more efficiently, which can further reduce monetary cost.</p><p><strong>The secondary objective is containerisation. </strong>We want to make all our computing resources in containers so that we can utilise some container orchestration platforms like kubernetes to help us manage clusters. Also, containerisation is a major trend, it usually means better ecosystem. Imagine if we want to extend the system to different cloud providers like AWS or Azure, if they supports, kubernetes, then we don't have to care about the lower level environment of this system. We can easily extend our system to different environments.</p><p>Last but not least, we want to optimize our system to be <strong>more user friendly and reduce operation cost</strong>.</p><h2>Multi-layer Abstraction</h2><p>We have some abstractions on cloud-native ClickHouse and it consists three layers as shown in Figure 1.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/clickhouse_decoupled_1.png" alt="Figure 1. Disaggregated Compute and Storage" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 1. Disaggregated Compute and Storage</div></div></div><p>The first layer is a cloud service. User will directly communicate with this layer, we must guarantee it is always on so that the user can use the query service at any time.</p><p>The second layer is virtual warehouse layer. It is responsible for computing. This layer is on demand so that we can scale up based on users' requirement or the demand.</p><p>The last layer is data storage and it should be expanded according to the data volume.</p><h2>Implementations</h2><p>In this section, we introduce the implementation of CNCH. Figure 2 shows all components inside the system.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/cloud_native_clickhouse_overview.png" alt="Figure 2. Cloud Native ClickHouse Overview" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 2. Cloud Native ClickHouse Overview</div></div></div><p>The computing resources include the servers and the workers. The users will directly send queries to the server. After the server accepts the queries, the server is responsible for scheduling the queries and assign the tasks to different workers. There are two different kinds of workers: the READ worker and the WRITE workers. The READ worker is mainly responsible for SELECT and the WRITE worker is mainly responsible for INSERT. By having this disaggregated architecture, we can even have more kinds of workers, such as dedicated workers for background tasks.</p><p>We manage the metadata in the distributed key-value store. The data is stored in the shared storage. The shared storage in Figure 2 can be HDFS, S3, and other distributed storage systems.</p><p>Now let's take a look how a select query is executed in CNCH . It mainly consists of 3 steps as shown in Figure 3 below:</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/bytehouse_cloud_clickhouse_3.png" alt="Figure 3. SELECT Execution in Cloud ClickHouse" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 3. SELECT Execution in Cloud ClickHouse</div></div></div><ul><li>When CNCH server receives the query from the client, it first gets part metadata from the catalog and then distributes sub queries and parts to different workers for execution.</li><li>For each worker, after it receives the subqueries from the server, it reads data from Virtual File System (VFS), performs computing and send the result back to the server.</li><li>The server performs a final aggregation based on the results get from workers and send the final results to the client</li></ul><p>For the write query, it's the opposite process of select query which is shown in Figure 4,</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/bytehouse_cloud_clickhouse_4.png" alt="Figure 4. INSERT execution in Cloud-native ClickHouse" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 4. INSERT execution in Cloud-native ClickHouse</div></div></div><ul><li>When the server receives the INSERT SQL, it directly forwards to the write worker.</li><li>The write worker writes the data to the virtual file system (VFS).</li><li>Once the data is written to the VFS successfully, server commits those part to the catalog side and notify the client when finishes.</li></ul><p>In CNCH, users don't need to be aware of how the metadata&amp;data are managed. All of these are transparent and CNCH automatically handles everything for the users. So users only need to send the query to the CNCH and CNCH will be responsible for scheduling, allocation, execution and storage for this query.</p><h2>Challenges Introduced by The New Architecture</h2><p><strong>Remote access</strong></p><p>In previous sections, I mentioned the new architecture has some disadvantages such as performance downgrade caused by remote data access. For this issue, we leverage cache to solve it. We have metadata cache on the server side and data cache on the worker side.</p><p><strong>Virtual filesystem abstraction</strong></p><p>In order to support different underlying data storage systems in different clouds. We proposes a virtual filesystem layer on top of the underlying storage systems.</p><p><strong>Metadata Management</strong></p><p>In order to manage our metadata more efficiently and properly, we introduce transaction semantic in cloud-native ClickHouse and provide ACID guarantee in the metadata management.</p><div style="display: flex; justify-content: center;"><div><img src="https://lf19-static.bytepluscdn.com/obj/byteplus/cms/bytehouse_cloud_clickhouse_5.png" alt="Figure 5. Transaction Support" style="max-width: 100%; max-height: 800px;"><div class="arco-typography arco-typography-secondary" style="text-align: center;">Figure 5. Transaction Support</div></div></div><p>As shown in Figure 5, we have a large amount of data and all of the data will be translated to multiple parts. We manage the parts in a global shared metadata storage layer. How do we make sure all the parts can be seen at the same time is quite challenging. After we have the transaction support, we can guarantee the atomic properties. </p><p>We also provide concurrency control and read committed isolation level to guarantee the data correctness under the concurrent read and write scenarios. To further improve the performance for concurrent read and write, CNCH leverages multi version (MVCC) in metadata management.</p><h2>Conclusion</h2><p>On top of the new disaggregated compute and storage architecture, cloud-native ClickHouse can utilise resources more efficient and reduce the infrastructure cost. And, it performs query scheduling automatically to different virtual warehouse and leverage Kubernetes to manage the clusters, it significantly reduces the operation and maintenance cost. By leveraging cache, efficient metadata and data management, the cloud-native ClickHouse achieves very competitive performance as ClickHouse.</p><p><strong><em>About the author:</em></strong><em> Niu Zhaojie obtained his PhD from Nanyang Technological University in Singapore. Currently, he is a senior software engineer at Bytedance and building analytics database in cloud.</em></p>

Cloud-native ClickHouse can utilise resources more efficient and reduce the infrastructure cost. 

ByteHouse

ClickHouse

What is ClickHouse? Leverage ClickHouse to speed up your data analysis

Companies like Uber, eBay, Spotify enhanced query performances and significantly reduced costs.

Browse articles by

ClickHouse

What is ClickHouse? Leverage ClickHouse to speed up your data analysis

Companies like Uber, eBay, Spotify enhanced query performances and significantly reduced costs.

Real-time Analytics

What are the key differences between OLAP and OLTP?

OLTP is designed more to support business operations while OLAP is created for decision making.

Real-time Analytics

Traditional Data Warehouse vs. Cloud Data Warehouse

Differences between traditional and cloud data warehouse architectures.

Real-time Analytics

Case Study: Real-time Advertisement Placement Through ClickHouse

A quick case study on handling advertising placement data through ClickHouse

Real-time Analytics

What is Real-Time Analytics?

Real-time analytics focuses on preparing and measuring data immediately after it enters the database

Real-time Analytics

Difference between ROLAP, MOLAP and HOLAP

3 types of OLAP engines: Relational OLAP (ROLAP), Multidimensional OLAP (MOLAP), Hybrid OLAP (HOLAP)

Real-time Analytics

What is Virtual Warehouse?

A virtual warehouse can scale out on demand, providing resources such as CPU, memory, and storage.

ClickHouse

Shared-Everything Architecture vs. Shared-Nothing Architecture

See a comparison of the two architectures and basic principles for choosing the right one.

ClickHouse

Exploring Cloud-Native ClickHouse

Cloud-native ClickHouse can utilise resources more efficient and reduce the infrastructure cost.