Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What exactly is a wide column store?

Googling for a definition either returns results for a column oriented DB or gives very vague definitions.

My understanding is that wide column stores consist of column families which consist of rows and columns. Each row within said family is stored together on disk. This sounds like how row oriented databases store their data. Which brings me to my first question:

How are wide column stores different from a regular relational DB table? This is the way I see it:

* column family        -> table * column family column -> table column * column family row    -> table row 

This image from Database Internals simply looks like two regular tables:

Two column families, contents, and anchors

The guess I have as to what is different comes from the fact that "multi-dimensional map" is mentioned along side wide column stores. So here is my second question:

Are wide column stores sorted from left to right? Meaning, in the above example, are the rows sorted first by Row Key, then by Timestamp, and finally by Qualifier?

like image 651
Moo Avatar asked May 25 '20 20:05

Moo


People also ask

What is wide column storage?

A wide-column database is a NoSQL database that organizes data storage into flexible columns that can be spread across multiple servers or database nodes, using multi-dimensional mapping to reference data by column, row, and timestamp.

What are column stores good for?

The main benefit of a columnar database is faster performance compared to a row-oriented one. That's because it accesses less memory to output data. Because a columnar database stores data by columns instead of rows, it can store more data in a smaller amount of memory.

Which of the following is an example of wide column store database?

Some of the examples of wide-column Store databases are Cassandra, HBase, Microsoft Azure Cosmos, Druid, Hypertable etc.

Why Cassandra is called wide column database?

In contrast to a document store, a wide column store is less specialized. A Cassandra wide column store, for example, supports simple numeric and string data types, but it can also use a collection or list as a column data type for storing multiple values or even nested values.


2 Answers

Let's start with the definition of a wide column database.

Its architecture uses (a) persistent, sparse matrix, multi-dimensional mapping (row-value, column-value, and timestamp) in a tabular format meant for massive scalability (over and above the petabyte scale).

A relational database is designed to maintain the relationship between the entity and the columns that describe the entity. A good example is a Customer table. The columns hold values describing the Customer's name, address, and contact information. All of this information is the same for each and every customer.

A wide column database is one type of NoSQL database.

Maybe this is a better image of four wide column databases.

Wide column databases

My understanding is that the first image at the top, the Column model, is what we called an entity/attribute/value table. It's an attribute/value table within a particular entity (column).

For Customer information, the first wide-area database example might look like this.

Customer ID    Attribute    Value -----------    ---------    ---------------      100001    name         John Smith      100001    address 1    10 Victory Lane      100001    address 3    Pittsburgh, PA  15120 

Yes, we could have modeled this for a relational database. The power of the attribute/value table comes with the more unusual attributes.

Customer ID    Attribute    Value -----------    ---------    ---------------      100001    fav color    blue      100001    fav shirt    golf shirt 

Any attribute that a marketer can dream up can be captured and stored in an attribute/value table. Different customers can have different attributes.

The Super Column model keeps the same information in a different format.

Customer ID: 100001 Attribute    Value ---------    -------------- fav color    blue fav shirt    golf shirt 

You can have as many Super Column models as you have entities. They can be in separate NoSQL tables or put together as a Super Column family.

The Column Family and Super Column family simply gives a row id to the first two models in the picture for quicker retrieval of information.

like image 107
Gilbert Le Blanc Avatar answered Sep 22 '22 09:09

Gilbert Le Blanc


Most (if not all) Wide-column stores are indeed row-oriented stores in that every parts of a record are stored together. You can see that as a 2-dimensional key-value store. The first part of the key is used to distribute the data across servers, the second part of the key lets you quickly find the data on the target server.

Wide-column stores will have different features and behaviors. However, Apache Cassandra, for example, allows you to define how the data will be sorted. Take this table for example:

| id | country | timestamp  | message | |----+---------+------------+---------| | 1  | US      | 2020-10-01 | "a..."  | | 1  | JP      | 2020-11-01 | "b..."  | | 1  | US      | 2020-09-01 | "c..."  | | 2  | CA      | 2020-10-01 | "d..."  | | 2  | CA      | 2019-10-01 | "e..."  | | 2  | CA      | 2020-11-01 | "f..."  | | 3  | GB      | 2020-09-01 | "g..."  | | 3  | GB      | 2020-09-02 | "h..."  | |----+---------+------------+---------| 

If your partitioning key is (id) and your clustering key is (country, timestamp), the data will be stored like this:

[Key 1] 1:JP,2020-11-01,"b..." | 1:US,2020-09-01,"c..." | 1:US,2020-10-01,"a..." [Key2] 2:CA,2019-10-01,"e..." | 2:CA,2020-10-01,"d..." | 2:CA,2020-11-01,"f..." [Key3] 3:GB,2020-09-01,"g..." | 3:GB,2020-09-02,"h..." 

Or in table form:

| id | country | timestamp  | message | |----+---------+------------+---------| | 1  | JP      | 2020-11-01 | "b..."  | | 1  | US      | 2020-09-01 | "c..."  | | 1  | US      | 2020-10-01 | "a..."  | | 2  | CA      | 2019-10-01 | "e..."  | | 2  | CA      | 2020-10-01 | "d..."  | | 2  | CA      | 2020-11-01 | "f..."  | | 3  | GB      | 2020-09-01 | "g..."  | | 3  | GB      | 2020-09-02 | "h..."  | |----+---------+------------+---------| 

If you change the primary key (composite of partitioning and clustering key) to (id, timestamp) WITH CLUSTERING ORDER BY (timestamp DESC) (id is the partitioning key, timestamp is the clustering key in descending order), the result would be:

[Key 1] 1:US,2020-09-01,"c..." | 1:US,2020-10-01,"a..." | 1:JP,2020-11-01,"b..."  [Key2] 2:CA,2019-10-01,"e..." | 2:CA,2020-10-01,"d..." | 2:CA,2020-11-01,"f..." [Key3] 3:GB,2020-09-01,"g..." | 3:GB,2020-09-02,"h..." 

Or in table form:

| id | country | timestamp  | message | |----+---------+------------+---------| | 1  | US      | 2020-09-01 | "c..."  | | 1  | US      | 2020-10-01 | "a..."  | | 1  | JP      | 2020-11-01 | "b..."  | | 2  | CA      | 2019-10-01 | "e..."  | | 2  | CA      | 2020-10-01 | "d..."  | | 2  | CA      | 2020-11-01 | "f..."  | | 3  | GB      | 2020-09-01 | "g..."  | | 3  | GB      | 2020-09-02 | "h..."  | |----+---------+------------+---------| 
like image 21
Lewis Diamond Avatar answered Sep 24 '22 09:09

Lewis Diamond