Columnar Databases were first introduced in the early 1970s into the field of biology in the form of a software package called TAXIR, short for Taxonomic Information Retrieval. Since then, these Databases have found their significance in various domains. Today, these Columnar Databases have become even more prominent due to the increasing Cloud-based analytics workloads.
Historically, data had been stored in relational tables in a row-wise manner. However, this format becomes a bottleneck when there is a large quantity of data. This is the reason Columnar Databases were introduced. Column-based storage speeds up database query performance by significantly reducing I/O during query execution. The same fundamental feature has been powering Relational Warehouses such as Amazon Redshift and Snowflake.
Table of Contents
What is Columnar Database?
Unlike traditional databases, a Columnar Database stores data in columns rather than in rows. These databases are designed for efficiency and speed at scale during analytics workflows. The column-based structure also reduces the data storage costs while improving query performance significantly. For example, suppose a city is being surveyed, and it generates data with different categories such as the name of the person, address, gender, etc. In that case, these Databases store each Column Family separately while keeping all of the data for one category related to the same key. An application just has to read a single Column Family without reading through all of the data for an entity.
In a Columnar Database, the columns are divided into groups known as Column Families. Each Column Family has a logically connected set of columns that are generally fetched or changed together. Other data that is accessed separately can be stored in different Column Families. Within a Column Family, new columns can be added dynamically, and it is not necessary that a row needs to have a value for every column.
A Column-Family data store can resemble a Relational Database in its most basic form. The real difference here, and the advantage, is that Column-Family databases follow a denormalized approach to structuring sparse data. And, thanks to compression, the performances of analytical queries can improve dramatically. These Databases typically use dictionary compression, and then this data additionally compresses with run-length encoded (RLE) or Huffman-based compression. Furthermore, these compression techniques allowed the storage of large multi-TB databases in memory, which were historically stored only on a disk.
Columnar Database vs Row Based Database
- Columnar Database vs Row Database: Storage
Source: Self
Row-wise storage: 1-emp1-d1-2-emp2-d2-3-emp3-d3
Column-wise storage: 1-2-3-emp1-emp2-emp3-d1-d2-d3
Traditional Databases store data sequentially in a row-wise manner. For example, if one were to keep a track of all the employees in a company, then records would be in the order of a row — ID 1, name, and department are captured. This is followed by all the information for ID 2 in a new row. In this case, similar data are not next to each other, increasing the time to access information from storage drives.
Whereas, in a Columnar Database, the names of every employee are in a series or next to one another. All names in the “Name” column and names of the department in the “Department” columns are stored one after the other. This simplifies the process of extracting similar information as the entire column’s data is grouped together and stored in one go.
With Columnar Databases, one can quickly access the most relevant elements from the Database, unlike in traditional row-wise storage. This increases the speed of a query even if a database contains millions of records. Though Traditional Databases still offer a complete source of data, column-based architecture is relatively easier. The way data is organized, allows for faster results and more efficient analysis. That said, a Column Store, though, performs well with aggregations, inserting a single row can be expensive, unlike for row-wise storage which is one of the biggest disadvantages. Consequently, row-oriented storage is optimal for OLTP performance, where transactions frequently for reading, writing, and updating occur every millisecond. And the column-oriented approach is leveraged with OLAP systems for analytics.
2) Columnar Database vs Row Database: Performance
Columnar Databases scale using distributed clusters of low-cost hardware to increase throughput. Storing millions of rows of data in a Relational Database is the right choice for use cases such as financial transactions. But, when it comes to running data analytics chores, Columnar storage comes on top as it is optimized for fast retrieval of columns of data. Improved query performance is the result of reduced overall disk I/O requirements. As column-based data storage facilitates better compression, since the data in one specific column is homogeneous, it further decreases I/O overhead as column values are squeezed into each data page. Indeed, compressed columns are frequently tiny enough to be cached entirely in memory, resulting in lightning-fast query speed.
Key Columnar Storage Formats
As the amount of data grows, so does the expense of processing and storing. There are two widely used Columnar Database formats:
1) Parquet
Image Source
Parquet is a popular open-source file format designed for presenting flat Columnar Storage format of data in an efficient way compared to row-based files like CSV or TSV files. When querying column-based storage in Parquet format, one can skip over the non-relevant data very quickly. As a result, when compared to row-oriented Databases, aggregation queries take less time. This way of storage has translated into reduced hardware requirements while minimizing latency for accessing data.
2) Apache ORC
Image Source
The Optimized Row Columnar (ORC) is another widely used file format for column-oriented data, especially for storing Hive data. The Apache ORC community designed this format to overcome the limitations of the other Hive file formats. Created in 2013 to massively speed up Apache Hive and improve storage efficiency, ORC is a Columnar file format custom made for Hadoop workloads and optimized for large streaming reads.
For instance, Facebook uses ORC format to save many petabytes in their Data Warehouse, and it is faster than RC File or Parquet. Even Yahoo uses ORC to store its production data. As for the architecture, ORC files are divided into stripes, which are independent of each other. The columns are separated from one another within each stripe. This makes it easy to read only those columns that are required.
Pros and Cons of Columnar Database
Column-wise and row-wise Databases have their own advantages and disadvantages. While column-wise storage is popular for OLAP systems, row-wise storage is good for OLTP systems. And, if one wants to discard a few columns during preprocessing, then it is better to go for the column-wise storage. A Columnar file format such as Parquet enables faster processing of data as users only need to query a small subset of a large number of columns.
The main drawback of a column-oriented approach is that manipulating an entire row is inefficient. However, these Databases are chosen with the assumption that the situation warehousing analytics are read-only, and rarely does the situation arise where users have to read and write many attributes in the same table.
Columnar Database Advantages vs Disadvantages
Advantages
- Queries that involve only a few columns.
- Aggregation queries against vast amounts of data.
- Column-wise compression.
Disadvantages
- Incremental data loading.
- Online Transaction Processing (OLTP) usage.
- Queries against only a few rows.
Benefits of using a Columnar Database
The benefits of using a Columnar Database are as follows:
- Multipurpose: Columnar databases are gaining popularity in big data applications. They are also used for other things, such as running OLAP cubes, storing metadata, and performing real-time analytics. Columnar databases are ideal for these tasks because they excel at rapidly loading new data.
- Compressible data: Data that can be highly compressed in a Columnar Database. Columnar operations such as MIN, MAX, SUM, COUNT, and AVG can be performed quickly thanks to the compression.
- Self-indexing: Another advantage of a column-based DBMS is that it uses less disc space than an RDBMS with the same data.
- Speed and efficiency: Columnar databases perform analytical queries more quickly than other database methodologies. They are also quick and efficient when it comes to performing joins, which is a method of combining data from two tables in a relational database. Although it is a common method of combining data, a join can be inefficient and slow. A columnar database can quickly join any number of data sets and aggregate query results into a single output.
Columnar Database Example
All of the values in a column are physically grouped together in a columnar database. For example, all of the values in column 1 are grouped together, then all of the values in column 2 are grouped together, and so on. Because the data is stored in record order, the 100th entry in column 1 and the 100th entry in column 2 both belong to the same input record. Individual data elements, such as customer name, can now be accessed in columns as a group rather than row by row.
An example to illustrate the storage of data in columnar Databases:
| Account number | Last name | First name | Purchase (in dollars) |
| 0211 | Warne | Jim | 56.75 |
| 0212 | Down | Jolly | 240.00 |
| 0213 | Bing | Taylor | 120.88 |
In a columnar DBMS, the data would be stored as given below:
- 0211, 0212, 0213;
- Warne, Down, Bing;
- Jim, Joly, Taylor;
- 56.75, 240.00, 120.88
In a row-oriented DBMS, the data would be stored as given below:
- 0211, Warne, Jim, 56.75;
- 0212, Down, Joly, 240.00;
- 0213, Bing, Taylor, 120.88
Hevo Data, a No-code Data Pipeline, helps integrate data from various databases with 100+ other sources and load it in a data warehouse of your choice to visualize it in your desired BI tool. Hevo is fully managed and completely automates the process of loading data from your desired source and enriching the data and transforming it into an analysis-ready form without writing a single line of code. Its fault-tolerant architecture ensures that the data is handled securely and consistently with zero data loss.
Get Started with Hevo for Free
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using BI tools such as Tableau and many more.
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
Top 6 Columnar Databases
The following are some of the best Open Source Columnar Databases available in the market today:
1) Apache Druid
Image Source
Apache Druid is an open-source real-time analytics Database designed for running OLAP queries on large data sets at faster rates. Apache Druid is mostly used for real-time ingestion (up to millions of records/sec), fast query performance, and high uptime are critical. The speed boost to queries on Apache Druid is mainly due to its column-oriented storage. On Druid, each column is stored optimized for its particular data type, which supports fast scans and aggregations. Apache Druid is deployed in clusters of hundreds of servers and can offer high data ingestion rates, high retention of trillions of records, and query latencies as low as milliseconds.
For further information on Apache Druid, check the official website here.
2) Apache HBase
Image Source
Apache HBase is a column-oriented, distributed NoSQL database that works on the Apache Hadoop framework and is open-source. On top of the Hadoop Distributed File System (HDFS), Apache HBase is a column-oriented Non-Relational Database.
HBase allows users to group attributes together into Column Families, such that the elements of a Column Family are all stored together. With HBase, users must predefine the table schema and specify the Column Families. However, the schema is flexible as new columns can be added to the Column Families as per the application requirements.
For further information on Apache HBase, check the official website here.
3) Apache Kudu
Image Source
Apache Kudu is an open-source distributed data storage engine used for running fast analytics on ever-changing data. Apache Kudu’s column-based storage allows efficient encoding and compression. Thanks to techniques such as run-length encoding, differential encoding, and vectorized bit-packing, Kudu reads the data at a great pace as it is space-efficient at storing it. Using techniques such as lazy data materialization and predicate pushdown, Kudu can perform drill-down and needle-in-a-haystack queries over billions of rows and TBs of data in seconds.
For further information on Apache Kudu, check the official website here.
4) MonetDB
Image Source
Since 1993, MonetDB has been offering Column-Store solutions for high-performance Data Warehouses for business intelligence services. MonetDB’s storage model is based on vertical fragmentation. Its query execution architecture is tuned to modern computers and has a modular software architecture. MonetDB is based on SQL 2003 and has a run-time query optimization feature as well.
For further information on MonetDB, check the official website here.
5) ClickHouse
Image Source
ClickHouse is an open-source OLAP Database Management System that is quick and easy to use. It is column-oriented and allows the generation of analytical reports using SQL queries in real-time. With the same available I/O throughput and CPU capacity, ClickHouse processes common analytical queries two to three orders of magnitude faster than traditional row-oriented systems. The Column Storage format allows more hot data to be stored in RAM, resulting in faster usual response times.
For further information on ClickHouse, check the official website here.
6) MariaDB Column Store
Image Source
MariaDB Column Store is an open-source column-based storage engine that employs a parallel distributed data architecture designed for processing petabytes of data. MariaDB is linearly scalable and offers exceptional performance with real-time response to analytical queries. MariaDB leverages the benefits of column-based storage such as compression, just-in-time projection, and horizontal and vertical partitioning to deliver tremendous performance when analyzing large data sets.
For further information on MariaDB, check the official website here.
Google BigQuery: Best Columnar Storage Platform Designed for Performance
Dremel, Google’s Distributed Query Engine, gave birth to BigQuery. Dremel can process terabytes of data in seconds by utilizing distributed computing inside a Serverless Architecture, which involves processing complex queries using numerous servers in parallel to significantly speed up processing. The few key components of Google BigQuery are:
1) Tree Architecture
By arranging calculations as an execution tree, Dremel and BigQuery can scale to thousands of machines. A Root Server takes incoming requests and routes them through mixers, which change them before delivering them to leaf nodes known as Slots. The leaf nodes undertake the dirty work of reading and filtering input in parallel. The results make their way back down the tree, where the mixers combine them and transmit them to the root as the query’s answer.
2) Serverless Service
Organizations must identify and commit to the server hardware on which computations will perform in most Data Warehouse settings. Performance, Security, Elasticity, and Reliability must all be considered by Administrators. This limitation is overcome by using a serverless paradigm.
Processing is automatically dispersed among a large number of devices working in parallel in a serverless approach. Data Engineers and Database Administrators may focus less on infrastructure and more on providing servers and generating insights from data when they use BigQuery’s serverless approach.
3) SQL and Programming Language Support
BigQuery may be accessed using normal SQL, which many users are already familiar with. BigQuery also includes client libraries for accessing data in Java, Python, C#, Go, and other languages.
4) Real-Time Analytics
By combining BigQuery with other GCP services and Resources, it can process and report on real-time data. After data from numerous sources is consolidated and stored in batches throughout the day, Data Warehouses can facilitate analytics. BigQuery also offers streaming at a pace of millions of rows per second, in addition to Batch Processing.
Columnar Database Versus Row Database
Traditional d=Databases are row-oriented databases in which data is stored in rows. Each record’s fields are kept in a lengthy row in sequential order. For Example, “Customer 1: Name, Address, Date of Birth, and so on,”. Then, in a new row, all of the information for Customer 2 appears.
Every customer’s name appears in a “Name” column in a columnar database, and all addresses appear in an “Address” column, and so on.
Columnar Database Versus Relational Database
Since it contains rows of data, a relational database is perfect for Transactional Applications.
For Analytical Applications, a columnar database is chosen because it provides for quick retrieval of data columns. Because they scale using distributed clusters of low-cost technology to boost throughput, columnar databases are ideal for data warehousing and big data processing.
Use Case of Columnar Database
Big Data Processing, Business Intelligence (BI), and Analytics all benefit from columnar databases. High volumes of data are fed into data stores by IoT (Internet of Things) devices. Sine IoT records may have few data pieces and fresh records arrive in a continuous stream, evaluating the data would be suitable in a big data columnar database. For example, cellular telecommunications rely on the continuous connection between the handset and the towers. In order to discover difficulties with a tower, telecom operators must evaluate that data in near real-time.
Conclusion
In this article, you have gained a basic understanding of column-wise and row-wise Databases and got exposure to various column-based Databases.
Visit our Website to Explore Hevo
You can integrate many such databases using Hevo Data, a No-code Data Pipeline, which helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.
Want to take Hevo for a spin? Sign Up here for the 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!
Dharmendra Kumar is a specialist in freelance writing within the data industry, adept at generating informative and engaging content related to data science by blending his problem-solving capabilities.