Every once in a while, I get asked about how to select between different types of databases. Generally, this comment is as a result of a product vendor or consultant making a recommendation to evolve towards a Big Data solution. The issue is twofold in that companies seek to understand what the next generation data platform looks like; AND, how or if their current environment can evolve. This involves understanding the pros and cons of the current product set and to what degree they can exist with newer approaches – Hadoop being the current platform people talk about.
The following is a list of data persistence approaches that helps at least define the options. This was done some time ago, so I am sure the vendors shown have evolved. However, think of it as a starting point to frame the discussion.
In general, one wants to anchor these discussions in some defined criteria that can help frame the discussion within the context of business drivers. In the following figure, the goal is to show that as data sources and consumers of your data expand to include increasingly complex data structures and “contexts,” there is a need to evolve approaches beyond the traditional relational database (RDBMS) approaches. Different organizations will have different criteria. I provide this as a rubric that has worked before – you will need to create an approach that works for your organization or client.
A number of data persistence approaches support the functional components as defined. These are described below.
| Defined | Pros / Cons | Vendor examples | |
| Relational Databases
(Row Orientation) |
Traditional normalized data models optimized for efficiently storing data | Relational structures are best used when the data structures are known and change infrequently. Relational designs often present challenges for analysts when queries and joins are executed that are incompatible with the design schema and / or indexing approach. This incompatibility creates processing bottlenecks, and resource challenges resulting in delays for data management teams. This approach is challenged when dealing with complex semantic data where multiple levels of parent / child relationships exist.
Advantages: This approach is best for transactional data where the relationships between the data and the use cases driving how data is accessed and used are stable. In uses where relational integrity is important and must be enforced in a consistent manner, this approach can work well. In a row based approach, contention on record locking are easier to manage than other methods. Disadvantages: As the relationships between data and relational integrity are enforced through the application of a rigid data model, this approach is inflexible, and changes can be hard to implement. |
All major database vendors: IBM – DB2; Oracle; MS SQL and others |
| Columnar Databases
(Column Oriented) |
Data organized or indexed around columns; can be implemented in SQL or a NoSQL environments. | Advantages: Columnar data designs lend themselves to analytical tasking involving large data sets where rapid search, retrieval and aggregation type queries are performed on large data tables. A columnar approach inherently creates vertical partitioning across the datasets stored this way. It is efficient and scalable.
Disadvantages: efficiencies can be offset by the need to join many queries to obtain the desired result. |
•Sybase IQ
•InfoBright •Vertica (HP) •Par Accel •MS SQL 2012 |
| Defined | Pros / Cons | Vendor examples | |
| RDF Triple Stores / Databases | Data stored organized around RDF triples (Actor-action-object OR Subject-predicate-Object); can be implemented in SQL or a NoSQL environments. | Advantages: A semantic organization of data lends itself to analytical and knowledge management tasks where the understanding of complex and evolving relationships is key. This is especially the case where ontologies or SKOS (1) type relationships are required to organize entities and their relationships to one another: corporate hierarchies/networks; insider trading analysis for example. This approach to organizing data is often represented in the context of the “semantic web” whose organizing constructs are RDF and OWL. when dealing with complex semantic data where multiple levels of parent / child relationships exist, this approach is more efficient that RDBMS
Disadvantages: This approach to storing data is often not as efficient as relational approaches. It can be complicated to write queries to traverse complex networks – however, this is often not much easier in relational databases either. Note: these can be implemented with XML formatting or in some other form. |
Native XML / RDF Databases
•Marklogic (COTS) •OpenLink Virtuoso (COTS) •Stardog (o/s, COTS) •BaseX (o/s) •eXist (o/s) •Sedna (o/s) XML Enabled Databases •IBM DB2 •MS SQL •Oracle •PostgrSQL XML enabled databases deal with XML as a CLOB in a table or organized into tables based on a schema |
| Graph Databases | A database that uses graph structures to store data. | See XML / RDF Stores / Databases. Graph Databases are a variant on this theme.
Advantages: Used primarily to store information on networks. Optimized for iterative joins; often in a recursive process (2).. Disadvantages: Storage challenges – these are large datasets; builds through iterative joins – very processor intensive. |
•ArangoDB
•OrientDB •Cayley •Aurelius Titan •Aurelius Faunus •Stardog •Neo4J •AllegroGraph |
(1) SKOS = Simple Knowledge Organization Structure. Relationships can be expressed as triples; examples are “is part of”; “is similar to”
(2) Recursion versus iteration
| Defined | Pros / Cons | Vendor Examples | |
| NoSQL
File based storage – HDFS |
Data structured to expose insights through the use of “key pairs” | This has many of the characteristics of the XML, Columnar and Graph approaches. In this instance, the data is loaded, and key value pair (KVP) files created external to the data. Think of the KVP as an index with a pointer back to the source data. This approach is generally associated with the Hadoop / MapReduce capabilities, and the definition here assumes that KVP files are queried using the capabilities available in the Hadoop ecosystem
Advantages: flexibility; MPP capabilities; speed; schema-less; scalable; Great at creating views of data; and performing simple calculations across Big Data; significant open source community – especially through the Apache Foundation. Shared nothing architecture optimizes the read process. However, it creates challenges in meeting ACID (1) requirements. File based storage systems adhere to the BASE (2) requirements Disadvantages: Share nothing architecture creates complexity in uses where sequencing of transactions or writing data is important – especially when multiple nodes are involved; complex metadata requirement; few tool “packages” available to support production environments; relatively immature product set. |
Document Store
•Mongo DB •Couch DB Column Store •Cassandra •Hbase •Accumulo Key Value Pair •Redis •Riak |
(1) ACID = Atomicity; Consistent; Isolated; Durable. Used for Transaction processing systems.
(2) BASE = Basic Availability, Soft State; Eventual Consistency. Used for distributed parallel processing systems where maintaining complete consistency is often prohibitively expensive
| Defined | Pros / Cons | Vendor examples | |
| In-Memory Approaches | Data approaches where the data is loaded into active memory to improve efficiency | Note that multiple persistence approaches can be implemented in memory
Advantages: Speed; flexibility – ability to virtualize views and calculated / derived tables; think of Datamarts in the traditional BI context Disadvantages: Hardware, cost |
•SAP HANA
•SAS High Performance Analytics •VoltDB |
| The classes of tools below are presented as they provide alternatives for capabilities that are likely to be required. Many of the capabilities are resident in some of the tool sets already discussed. | |||
| Data Virtualization | The ability to produce tables or views without going through an ETL process | Data virtualization is a capability built into other products. Any In- Memory product inherently virtualizes data. Likewise a number of the Enterprise BI tools allow data – generally in the form of “cubes” to be virtualized. Denodo Technologies is the major pure play vendor. The others vendors generally provide products that are part of larger suites of tools. | •Composite Software (Cisco)
•Denodo Technologies •Informatica •IBM •MS •SAP •Oracle |
| Search Engines | Data management components that are used to search structured and unstructured data | Search engines and appliances perform functions as simple as indexing data, and as complex as Natural Language Processing (NLP) and entity extraction. They are referenced here as the functionality can be implemented as stand alone capability and may be considered as part of the overall capability stack. | •Google Search Appliance
•Elastic Search |
| Defined | Pros / Cons | Vendor examples | |
| Hybrid Approaches | Data products that implement both SQL and NoSQL approaches | These are traditional SQL database approaches that have been partnered with one or more of the approaches defined above. Teradata acquired Aster to create a “bolt on” to a traditional SQL Db; IBM has Db2/Netezza/Big Insights. SAS uses a file based storage system and has created “Access Modules” that work though Apache HIVE to apply analytics within either an HDFS environment, or the SAS environment.
Another hybrid approach is exemplified by Cassandra that incorporates elements of a data model within a HDFS based system. One also sees organizations implementing HDFS / RDBMS solutions for different functions. For example acquiring, landing and staging data using an HDFS approach, and then once requirements and the business use is known creating structured data models to facilitate and control delivery Advantages: Integrated solutions; ability to leverage legacy; more developed toolkits to support production operations. Compared to open source, production ready solutions require less configuration and code development. Disadvantages: Tend to be costly; architecture tends to be inflexible – all or nothing mindset. |
•Teradata
•EMC •SAS •IBM •Cassandra (Apache) |
analyticaltern 