Introduction

We have been describing document stores as one of the four main kinds of NoSQL databases.
We said document stores are a particular variant on key-value stores where the value associated with a key is semi-structure data, for example, JSON.
On Monday, we described using MongoDB as our example how documents stores can be used to create collections of documents within a database, how new documents can be added to a collection, and how collections can be queried.
We said each collection in a database can be imagined as having a speciall document associated with which has an id for the name of the collection and a document associated with it listing all the ids of keys stored in the collection.
This allows in addition to pure look up of documents associated with a key, the ability to iterate over all documents in a collection, so document stores can do many of the same kinds of queries, aggregations, and indexing associated with a traditional RDBMS as long as they involve only one collection.
We said there were different approaches to allowing operations on multiple collections, such as using embedded documents, or programmatically nesting queries using an external application.
We next scratch the surface of a more natural approach to these kinds of complex queries using MapReduce.

Map Reduce and Document Stores

MapReduce is a framework developed at Google (Dean and Ghemawat (2004)) for doing parallel computations using key value pairs. I.e., suitable for key-value stores and document stores.
A map reduce job consists of several rounds each consisting of three phases:
1. MapPhase, key/value pairs are read from the input and the map function is applied to each of them individually. The function is of the general form:
  `map: langle k, v rangle |-> langle langle k_1, v_1 rangle, langle k_2, v_2 rangle, ...,rangle`
2. Shuffle Phase, the pairs produced during the map phase are sorted by their key, and all values for the same key are grouped together.
3. Reduce Phase, the reduce function is applied to each key and its values.
  `\r\e\d\u\c\e: langle k, langle v_1, v_2, ... rangle rangle |-> langle k, langle v_1', v_2', ... rangle rangle`
  That is, for each key the reduce function processes the list of associated values and outputs another list of values. The output values and their number may not be the same as the input values.
MongoDB natively supports MapReduce jobs where the map and reduce functions are computed by their variant of Javascript.

Map Reduce Example in MongoDB - Set up collection

What follows is an example of MapReduce in MongoDB from the MongoDB website used to compute the total sum of orders of customers for some store.

Below is an example collection of orders:

db.orders.insertMany([
   { _id: 1, cust_id: "Ant O. Knee", ord_date: new Date("2020-03-01"),  price: 25, 
     items: [ { sku: "oranges", qty: 5, price: 2.5 }, { sku: "apples", qty: 5, price: 2.5 } ], 
     status: "A" },
   { _id: 2, cust_id: "Ant O. Knee", ord_date: new Date("2020-03-08"), price: 70, 
      items: [ { sku: "oranges", qty: 8, price: 2.5 }, { sku: "chocolates", qty: 5, price: 10 } ], 
      status: "A" },
   { _id: 3, cust_id: "Busby Bee", ord_date: new Date("2020-03-08"), price: 50, 
     items: [ { sku: "oranges", qty: 10, price: 2.5 }, { sku: "pears", qty: 10, price: 2.5 } ], 
     status: "A" },
   { _id: 4, cust_id: "Busby Bee", ord_date: new Date("2020-03-18"), price: 25, 
     items: [ { sku: "oranges", qty: 10, price: 2.5 } ], 
     status: "A" },
   { _id: 5, cust_id: "Busby Bee", ord_date: new Date("2020-03-19"), price: 50, 
     items: [ { sku: "chocolates", qty: 5, price: 10 } ], 
     status: "A"},
   { _id: 6, cust_id: "Cam Elot", ord_date: new Date("2020-03-19"), price: 35, 
     items: [ { sku: "carrots", qty: 10, price: 1.0 }, { sku: "apples", qty: 10, price: 2.5 } ], 
     status: "A" },
   { _id: 7, cust_id: "Cam Elot", ord_date: new Date("2020-03-20"), price: 25, 
     items: [ { sku: "oranges", qty: 10, price: 2.5 } ],
     status: "A" },
   { _id: 8, cust_id: "Don Quis", ord_date: new Date("2020-03-20"), price: 75, 
     items: [ { sku: "chocolates", qty: 5, price: 10 }, { sku: "apples", qty: 10, price: 2.5 } ], 
     status: "A" },
   { _id: 9, cust_id: "Don Quis", ord_date: new Date("2020-03-20"), price: 55, 
     items: [ { sku: "carrots", qty: 5, price: 1.0 }, { sku: "apples", qty: 10, price: 2.5 }, 
     { sku: "oranges", qty: 10, price: 2.5 } ], 
     status: "A" },
   { _id: 10, cust_id: "Don Quis", ord_date: new Date("2020-03-23"), price: 25, 
     items: [ { sku: "oranges", qty: 10, price: 2.5 } ], 
     status: "A" }
]);

Map Reduce Example in MongoDB - Map and Reduce Functions

Our mapper is as follows:
```
var mapFunction1 = function() {
   emit(this.cust_id, this.price);
};
```
Here this refers to the document being processed, emit is the function to output a key-value pair.
Our reducer is as follows:
```
var reduceFunction1 = function(keyCustId, valuesPrices) {
   return Array.sum(valuesPrices);
};
```
Prices with the same customer id will all be sent as an array to this reduce function, which merely computes their sum.
So far we have only defined two functions we haven't run a map reduce job yet.

Map Reduce Example in MongoDB - mapReduce function

To run this map reduce job we can use the command:

db.orders.mapReduce(
   mapFunction1,
   reduceFunction1,
   { out: "map_reduce_example" }
)
Here map_reduce_example is the collection to which the resulting key value pairs are output.

We could query this using:

db.map_reduce_example.find().sort( { _id: 1 } )

This would output:

{"_id" : "Ant O. Knee", "value" : 95 }
{ "_id" : "Busby Bee", "value" : 125 }
{ "_id" : "Cam Elot", "value" : 60 }
{ "_id" : "Don Quis", "value" : 155 }

It is completely possible that map_reduce_example previously had data in it (which may have come from a different map reduce job on a different collection.
MongoDB supports three ways of combining old, existing data in map_reduce_example. These can be specified using an action attribute in the argument in the mapReduce call.
- replace (delete old insert new),
- merge (add new key value pairs),
- reduce (run reducer on key value pairs present grouped with new key value pairs).
For example,
```
{ 
   action: "reduce",
   out: "map_reduce_example" 
}
```
This combining of multiple outputs allows us to carry out more sophisticated join like operations.

In-Class Exercise

Modify our map reduce job above so that it computes for each customer the average price of the items they purchased.
Post your solutions to the Sep 30 In-Class Exercise Thread.

Column-Oriented Databases - Motivation

Because of technologies like consistent hashing, key-value stores are good for horizontal scaling. I.e., distributing data over more machines.
The relational model though is good for maintaining consistency of data with respect to constraints on the data and transactions.
Sometimes, though, in the relational setting, we might have tables with many attributes, many of which are null, and for which the most common queries only return a few columns.

For example, we might have the table:

Id   Genre      Title              Price   "Audiobook price"
1    fantasy    "My first book"    20      30
2    education  "Beginners Guide"  10      null
3    education  "SQL strikes back" 40      null
4    fantasy    "The rise of SQL"  10      null

This has a fair number of nulls.
Also, you could imagine a typical query might only involve Title and Price.

Column-Oriented Databases - What they are

We might have an index on price in the relational setting so that we could compute queries involving books between from price range.
Such an index for Price might look like: Price: [10:2,4], [20:1], [40:3].
Notice each possible value of Price is a key-value pair with the value being an array of integer IDs of items with that price.
Over the last fifty years many techniques to compress such arrays of int's have been developed, that would allow us to store this index efficiently.
We could imagine storing each column of a table using such an index into a key-value store.
If we did this, we'd get a column-oriented database.

Cassandra

Cassandra is an example of such a column-oriented database developed at Facebook for its inbox feature.
It was open-sourced in 2008 and is now a top-level Apache project.
In Cassandra, the notion of a database is called a keyspace. When you make a keyspace, you define how you would like the key-value store associated with it to be set up:
```
CREATE KEYSPACE mykeyspace 
    WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};
```
There is also a 'Network Topology Strategy'.
You can create tables with a syntax that seems very familiar to the relational model:
```
CREATE TABLE EMPLOYEE ( id int PRIMARY KEY, name text,
   city text, salary varint, phone varint
);
```
A primary key must be specified, though, so that we can store things in a column-oriented fashion.

Inserts should also look familiar:

INSERT INTO EMPLOYEE (id, name, city, phone, salary) VALUES 
   (1, 'bob', 'San Jose', 4089995555, 75000);

Likewise, querying is done using a syntax very much like standard SQL:
```
SELECT * FROM EMPLOYEE;
```

Graph-Oriented Databases

We now look at a fourth kind of NoSQL database: Graph-Oriented Databases.
Recall from your discrete math class at some point back in time that a graph is a collection of nodes (aka vertices) together with a set of edges (which are potentially labeled) between them.
These can be represented graphically using notations like:
Graphs like the above could be used to represent social networks.
We can also use graph edges to represent Subject Predicate Object triples as say coming from RDF, or to express thing like: "X recommends Y to Z".
Using a traditional RDBMS, queries like: "is city V reachable from city W?" in a table expressing a graph of cities and road distances between them, either involve many joins or are not expressible.
Graph-oriented databases are designed to handle exactly these kind of queries.

Neo4j

Neo4j is a popular graph-oriented database.
It was first released in 2010 and is developed by Neo4j, Inc.
Neo4j makes use of the query language CYPHER.
It has a APIs for a variety of application languages.
In particular, there is a Java Driver.
It also has a command line shell called cypher-shell which can be downloaded from the neo4j site separately or installed using brew/apt/choco.
You don't need this if you are just messing around, within the GUI, you can just create a new database, then click open to launch the database browser, which lets you enter CYPHER commands.

Neo4j - cypher-shell

If you do run cypher-shell at command line, it will prompt you for a user name and password (default: neo4j, test).
You can then run CYPHER command.

Here are some useful shell commands/CYPHER commands:

:help #gives list of shell commands
:exit #leave shell
:use db_name   #set db to use to db_name
:source file_name #read cypher commands from file file_name
SHOW DATABASES; #give a list of the current neo4j databases 
CREATE DATABASE some_db IF NOT EXISTS; # creates the database some_db if it doesn't exist
DROP DATABASE some_db;  # delete database some_db

CYPHER

To store a node into Neo4j we can use a command like:

CREATE (Bart:Reader {name:'Bart Baesens', age:32})
CREATE (Seppe:Reader {name:'Seppe vanden Broucke', age:30})

Notice both Bart and Seppe are JSON Reader objects.

The general syntax is x:T where x is the object T is the type. If we omit T and allow an arbitrary object we write x, if we want an object of type T but don't name it, we write :T.

To store a sequence of comma separated edges into a Neo4j database we can use a command like:

CREATE
  (b01)-[:IS_GENRE]->(Education),
  (b02)-[:IS_GENRE]->(Thriller), 
  (b03)-[:IS_GENRE]->(Education)

An undirected edge is represented with code like: -[:KNOWS]- without the > (can't use undirected edges in a CREATE statement).

Queries have the format MATCH - WHERE - RETURN - ORDER BY - LIMIT:

// return all Book nodes
MATCH (b:Book) 
RETURN b;

// return the first 20 Book nodes ordered by price
MATCH (b:Book) 
RETURN b
ORDER BY b.price DESC 
LIMIT 20;

// return Book nodes with title Beginning Neo4j
MATCH (b:Book)
WHERE b.title = "Beginning Neo4j" 
RETURN b;

//customer names of customers over 30 who purchase a book by Wilfried Lemahieu 
MATCH (c:Customer)-[p:PURCHASED]->(b:Book)<- [:WRITTEN_BY]-(a:Author)
WHERE a.name = "Wilfried Lemahieu"
AND c.age > 30
AND p.type = "cash"
RETURN DISTINCT c.name;

// Return titles of books that are in a sub-genre of Programming
//*0.. below means 0 or more PARENT traversals
MATCH (b:Book)-[:IN_GENRE]->(:Genre) 
              -[:PARENT*0..]-(:Genre {name:"Programming"})
RETURN b.title;

CYPHER from Java

Below is an example Java program that queries a Neo4j database:

import org.neo4j.driver.v1.*;
import static org.neo4j.driver.v1.Values.parameters;

import java.util.List;
import static java.util.Arrays.asList;
import static java.util.Collections.singletonMap;

public class Social {
    public static void main(String...args) {     
        Config noSSL = Config.build().withEncryptionLevel(
              Config.EncryptionLevel.NONE).toConfig();
        //Connect to a neo4j database as user neo4j with password test
        Driver driver = GraphDatabase.driver("bolt://localhost",
              AuthTokens.basic("neo4j","test"),noSSL); // <password>
        try (Session session = driver.session()) {
              //Create a list of pairs to insert into database
              List data = 
              asList(asList("Jim","Mike"),asList("Jim","Billy"),
              asList("Anna","Jim"), asList("Anna","Mike"),
             asList("Sally","Anna"),asList("Joe","Sally"),
              asList("Joe","Bob"),asList("Bob","Sally"));
    
              String insertQuery = "UNWIND {pairs} as pair " +
               "MERGE (p1:Person {name:pair[0]}) " +
               "MERGE (p2:Person {name:pair[1]}) " +
               "MERGE (p1)-[:KNOWS]-(p2);";
      
               session.run(insertQuery,
                   singletonMap("pairs",data)).consume();
          
               StatementResult result;
            
               String foafQuery = 
                  " MATCH (person:Person)-[:KNOWS]-(friend)-[:KNOWS]-(foaf) "+
                  " WHERE person.name = {name} " +
                  "   AND NOT (person)-[:KNOWS]-(foaf) " +
                  " RETURN foaf.name AS name ";
               result = session.run(foafQuery, parameters("name","Joe"));
               while (result.hasNext()) {
                  System.out.println(result.next().get("name"));
               }
               String commonFriendsQuery =
                   "MATCH (user:Person)-[:KNOWS]-(friend)-[:KNOWS]-(foaf:Person) " +
                   " WHERE user.name = {from} AND foaf.name = {to} " +
                   " RETURN friend.name AS friend";
               result = session.run(commonFriendsQuery,
                   parameters("from","Joe","to","Sally"));
               while (result.hasNext()) {
                   System.out.println(result.next().get("friend"));
               }
               String connectingPathsQuery =
                   "MATCH path = shortestPath(" +
                   " (p1:Person)-[:KNOWS*..6]-(p2:Person)) " +
                   " WHERE p1.name = {from} AND p2.name = {to} " +
                   " RETURN [n IN nodes(path) | n.name] as names";
               result = session.run(connectingPathsQuery, 
                   parameters("from","Joe","to","Billy"));
               while (result.hasNext()) {
                   System.out.println(result.next().get("names"));
               }
        }
    }
}

MapReduce, Column-oriented Database, Graph Databases

Outline