What makes columnar databases suitable for data science?

Question

What are some of the advantages of columnar data-stores which make them more suitable for data science and analytics?

Answer 1

A column-oriented database (=columnar data-store) stores the data of a table column by column on the disk, while a row-oriented database stores the data of a table row by row.

There are two main advantages of using a column-oriented database in comparison with a row-oriented database. The first advantage relates to the amount of data one’s need to read in case we perform an operation on just a few features. Consider a simple query:

SELECT correlation(feature2, feature5)
FROM records

A traditional executor would read the entire table (i.e. all the features):

Instead, using our column-based approach we just have to read the columns which are interested in:

The second advantage, which is also very important for large databases, is that column-based storage allows better compression, since the data in one specific column is indeed homogeneous than across all the columns.

The main drawback of a column-oriented approach is that manipulating (lookup, update or delete) an entire given row is inefficient. However the situation should occur rarely in databases for analytics (“warehousing”), which means most operations are read-only, rarely read many attributes in the same table and writes are only appends.

Some RDMS offer a column-oriented storage engine option. For example, PostgreSQL has natively no option to store tables in a column-based fashion, but Greenplum has created a closed-source one (DBMS2, 2009). Interestingly, Greenplum is also behind the open-source library for scalable in-database analytics, MADlib (Hellerstein et al., 2012), which is no coincidence. More recently, CitusDB, a startup working on high speed, analytic database, released their own open-source columnar store extension for PostgreSQL, CSTORE (Miller, 2014). Google’s system for large scale machine learning Sibyl also uses column-oriented data format (Chandra et al., 2010). This trend reflects the growing interest around column-oriented storage for large-scale analytics. Stonebraker et al. (2005) further discuss the advantages of column-oriented DBMS.

Two concrete use cases: How are most datasets for large-scale machine learning stored?

(most of the answer comes from Appendix C of: BeatDB: An end-to-end approach to unveil saliencies from massive signal data sets. Franck Dernoncourt, S.M, thesis, MIT Dept of EECS)

Answer 2

It depends on what you do.

Column stores have two key benefits:

• whole columns can be skipped
• run-length compression works better on columns (for certain data types; in particular with few distinct values)

However they also have drawbacks:

• many algorithms will need all columns, and only record at a time (e.g. k-means) or may even need to compute a pairwise distance matrix
• compression techniques only work well on sparse data types and factors, but not well on double-valued continuous data
• appends on column stores are expensive, so it is not ideal for streaming / changing data

Columnar storage is really popular for OLAP aka "stupid analytics" (Michael Stonebraker) and of course for preprocessing where you may indeed be interested in discarding whole columns (but you would need to have structured data first - you don't store JSONs in columnar format). Because the columnar layout is really nice for e.g. counting how many apples you have sold last week.

For much of the scientific / data science use cases, array databases appear to be the way to go (plus, of course, unstructured input data). E.g. SciDB and RasDaMan.

In many cases (e.g. deep learning), matrixes and arrays are the data types you need, not columns. MapReduce etc. can still be useful in preprocessing, of course. Maybe even column data (but array database usually support a column-like compression, too).

Answer 3

I haven't used a columnar database, but I've used an open source columnar file format called Parquet, and I think the benefits are probably the same -- faster processing of data when you only need to query a small subset of a large number of columns. I had a query running on about 50 terabytes of Avro files (a row oriented file format) with 673 columns that took about an hour and a half on a 140 node Hadoop cluster. With Parquet, the same query took about 22 minutes because I only needed 5 columns.

If you had a small number of columns or were using a large proportion of your columns, I don't think a columnar database would make much of a difference vs a row oriented one because you would still have to basically scan all of your data. I believe columnar databases store columns separately whereas row oriented databases store rows separately. Your query will be faster any time you're able to read less data from disk.

This link explains more of the details.

Answer 4

Note: This is my question, and I'm really thankful for the wonderful answers here, which helped me grasp the concept.

So, I would explain the concept the way which I have understood:

Generally, the data in the databases are stored in the memory in the following formats:

Consider this datum:

           X1         X2
1   0.7091409 -1.4061361
2  -1.1334614 -0.1973846
3   2.3343391 -0.4385071

In a relational row-based store, it is stored like this:

1, 0.7091409, -1.4061361, 2, -1.1334614, -0.1973846, 3, 2.3343391, -0.4385071 

in the form of rows.

In the columnar store, it would be stored like this:

1, 2, 3, 0.7091409 ,-1.1334614, 2.3343391, -1.4061361, -0.1973846, -0.4385071

in the form of columns.

So, what does this mean?

This means that insertion(and updating) and deletions are fast in the row-based column store as it is just removal of the last few values or the first few values. However, it is not the case in columnar stores as the value in each block store needs to be removed.

However, when there is the need for columnar aggregates and operations, the columnar stores have an edge over their row-based counterparts, as they are stored column-wise, and as a result, accessing individual columns is very easy.