[ACCEPTED]-What is a columnar database?-database

How do columnar databases work? The defining concept of a column-store 22 is that the values of a table are stored 21 contiguously by column. Thus the classic 20 supplier table from CJ Date's supplier and 19 parts database:

SNO  STATUS CITY    SNAME
---  ------ ----    -----
S1       20 London  Smith
S2       10 Paris   Jones
S3       30 Paris   Blake
S4       20 London  Clark
S5       30 Athens  Adams

would be stored on disk or 18 in memory something like:

S1S2S3S4S5;2010302030;LondonParisParisLondonAthens;SmithJonesBlakeClarkAdams 

This is in contrast 17 to a traditional rowstore which would store 16 the data more like this:

S120LondonSmith;S210ParisJones;S330ParisBlake;S420LondonClark;S530AthensAdams

From this simple 15 concept flows all of the fundamental differences 14 in performance, for better or worse, between 13 a column-store and a row-store. For example, a 12 column store will excel at doing aggregations 11 like totals and averages, but inserting 10 a single row can be expensive, while the 9 inverse holds true for row-stores. This 8 should be apparent from the above diagram.

How do they differ from relational databases? A 7 relation database is a logical concept. A 6 columnar database, or column-store, is a 5 physical concept. Thus the two terms are 4 not comparable in any meaningful way. Column- oriented 3 DMBSs may be relational or not, just as 2 row-oriented DBMS's may adhere more or less 1 to relational principles.

How do Columnar Databases work?
Columnar database is a concept rather a particular 40 architecture/implementation. In other words, there isn't one particular 39 description on how these databases work; indeed, several 38 are build upon traditional, row-oriented, DBMS, simply 37 storing the info in tables with one (or 36 rather often two) columns (and adding the 35 necessary layer to access the columnar data 34 in an easy fashion).

How do they differ from relational databases? They generally differ 33 from traditional (row-oriented) databases 32 with regards to ...

• performance...
• storage requirements ...
• ease of modification of the schema ...

...in specific use cases of DBMSes.
In particular they 31 offer advantages in the areas mentioned 30 when the typical use is to compute aggregate 29 values on a limited number of columns, as 28 opposed to try and retrieve all/most columns 27 for a given entity.

Is there a trial version of a columnar database I can install to play around? (I am on Windows 7) Yes, there are commercial, free 26 and also open-source implementation of columnar 25 databases. See the list at the end of the 24 Wikipedia article for starter.
Beware that several of these 23 implementations were introduced to address 22 a particular need (say very small footprint, highly compressible 21 distribution of data, or spare matrix emulation 20 etc.) rather than provide a general purpose 19 column-oriented DBMS per-se.

Note: The remark 18 about the "single purpose orientation" of 17 several columnar DBMSes is not a critique 16 of these implementations, but rather an 15 additional indication that such an approach 14 for DBMSes strays from the more "natural" (and 13 certainly more broadly used) approach to 12 storing record entities. As a result, this 11 approach is used when the row-oriented approach 10 isn't satisfactory, and therefore and tends 9 to
a) be targeted for a particular purpose b) receive 8 less resources/interest than work on "General 7 Purpose", "Tried and Tested", tabular 6 approach.

Tentatively, the Entity-Attribute-Value (EAV) data model, may 5 be an alternative storage strategy which 4 you may want to consider. Although distinct 3 from the "pure" Columnar DB model, EAV 2 shares several of the characteristics of 1 Columnar DBs.

I would say the best candidate to understand 4 about column oriented databases is to check 3 HBase (Apache Hbase) . You an checkout the code and 2 explore further to find out about the implementation 1 .

Also, Columnar DBs have a built in affinity 10 for data compression, and the loading process 9 is unique. Here's an article I wrote in 2008 that 8 explains a bit more.

You may also be interested 7 in a new report from IDC's Carl Olofson 6 on 3rd generation DBMS technology. It discusses 5 columnar, et al. If you're not an IDC client 4 you can get it free on our site. He's doing 3 a webinar on June 16th, too (also on our 2 site).

(BTW, one comment above lists asterdata 1 but I don't think they are columnar.)

To understand what is column oriented database, it 19 is better to contrast it with row oriented 18 database.

Row oriented databases (e.g. MS SQL Server and SQLite) are 17 designed to efficiently return data for 16 an entire row. It does it by storing all 15 the columns values of a row together. Row-oriented 14 databases are well-suited for OLTP systems 13 (e.g., retail sales, and financial transaction 12 systems).

Column oriented databases are designed to efficiently return 11 data for a limited number of columns. It 10 does it by storing all of the values of 9 a column together. Two widely used Column 8 oriented databases are Apache Hbase and 7 Google BigTable (used by Google for its 6 Search, Analytics, Maps and Gmail). They 5 are suitable for the big data projects. A 4 column oriented database will excel at read 3 operations on a limited number of columns, however 2 write operation will be expensive compared 1 to row oriented databases.

For more: https://en.wikipedia.org/wiki/Column-oriented_DBMS

Product information. This may help. These 1 were to featured products on a Google search.

http://www.vertica.com/

http://www.paraccel.com/

http://www.asterdata.com/index.php

kx is another columnar database, for example 4 used in the financial sector. The licence 3 is somewhat $50K last time I checked, though. No 2 optimisation needed, no index needed, because 1 kx has powerful operators (matlab equivalents: .*, kron, bsxfun, ...).