1
5/5/20
—
Practical Database Concepts Lecture 12: NoSQL and MongoDB
Santha Sumanasekara June 2020
1
A (not so) short history of DBMS
Ø http://upload.wikimedia.org/wikipedia/commons/2/22/RDBMS_tim eline.svg
2
2
1
3
5/5/20
Re-invent the wheel?
Ø In modern times, there were only very few re-inventions that made a great impact!
Ø Jet engine/ aircraft Ø Digital Camera
Ø …
Ø Is NoSQL the database equivalent to the jet engine in the aeronautical world?
Ø Why do you reinvent this new database model? 3
Re-invent the wheel?
Ø Data is everywhere. The digital transformation drives us to the frontiers that never reached.
Ø The digital transformation demands for rich content and big data!
Ø The structured database model, that catered the data management needs for almost half a century, has its inherent limitations.
4
4
2
5
5/5/20
An Example
Ø Consider the following example: what if we have a table that defines some structure and for a specific row we want to have some extra data?
Ø This is not easily done with a relational database model. 5
Another Example
Ø Consider the following two AirBnB listings:
Ø https://www.airbnb.com.au/experiences/79594 Ø https://www.airbnb.com.au/rooms/21087620
Ø How different are they?
Ø Can you easily store them in a table in a relational database?
6
6
3
7
5/5/20
Another Example
https://docs.atlas.mongodb.com/sample-data/sample-airbnb/#sample- document
7
NoSQL – Not just one model
https://en.wikipedia.org/wiki/NoSQL Ø Document databases
Ø Graph stores
Ø Key-value stores
Ø Wide-column stores
8
8
4
9
5/5/20
NoSQL Database Types
Ø Document databases: pair each key with a complex data structure known as a document. Documents can contain many different key-value pairs, or key- array pairs, or even nested documents.
9
{
“_id”: “10006546”,
“listing_url”: “https://www.airbnb.com/rooms/10006546”, “name”: “Ribeira Charming Duplex”,
“summary”: “Fantastic duplex apartment with three bedrooms,
located in the historic area of Porto, Ribeira (Cube)…”, “interaction”: “Cot – 10 € / night Dog – € 7,5 / night”, “house_rules”: “Make the house your home…”,
“property_type”: “House”,
“room_type”: “Entire home/apt”, …
…
Benefits of NoSQL
Ø Dynamic Schemas
Ø Relational databases require that schemas be defined before
you can add data.
Ø NoSQL databases are built to allow the insertion of data without a predefined schema.
Ø Nicely fits with agile development process.
Ø Data validation is delegated to the application.
10
10
5
11
5/5/20
Benefits of NoSQL
Ø Auto-sharding
Ø Partitioning can be done in the relational database systems, however, they require quite sophisticated mechanisms to ensure joins etc are executed efficiently.
Ø NoSQL databases, on the other hand, usually support auto- sharding, meaning that they natively and automatically spread data across an arbitrary number of servers, without requiring the application to even be aware of the composition of the server pool.
1 1
Benefits of NoSQL
Ø Replication
Ø Most NoSQL databases also support automatic database replication to maintain availability in the event of outages or planned maintenance events.
Ø Unlike relational databases, NoSQL databases generally have no requirement for separate applications or expensive add- ons to implement replication.
12
12
6
13
5/5/20
The name of the database was derived from the
word humongous to support the idea of processing large amount of data.
MongoDB
What a name for a database!
13
MongoDB
Ø MongoDB is a cross-platform document-oriented database
Ø MongoDB uses JSON-like documents. A document is a record (sort of like a row in a structured table). For example, if we have a database for users, a document would be one individual user.
Ø A document is made out of a list of key-value pairs.
14
14
7
5/5/20
MongoDB – similar but different!
Relational
database
table
row
column
CREATE TABLE people ( user_id Varchar(30), age Number,
status char(1), PRIMARY KEY (user_id)
);
MongoDB
database
collection
document
field
db.people.insertOne( { user_id: “abc123”, age: 55,
status: “A”
})
15
15
—
Demo time!
MongoDB MongoDB Compass
16
8
17
5/5/20
Insert a document
Ø In SQL:
Ø In MongoDB Shell:
INSERT INTO people (user_id, age, status) VALUES (‘A1234’,23,’A’);
db.people.insertOne(
{ user_id: ”A1234″, age: 23, status: “A” }
)
17
Insert a document
Ø In SQL:
Ø In MongoDB Compass:
INSERT INTO people (user_id, age, status) VALUES (‘A1234’,23,’A’);
18
18
9
5/5/20
A complex insertion
Ø In SQL: We cannot insert composite objects or arrays into tables
INSERT INTO people (user_id, name, address, age, sports) VALUES (‘A1234’,’Sam’, {1}{Happy St}{Happyville}{3999},
23,[’AFL’, ‘Cricket’, ’Rugby’]);
Ø In MongoDB Shell:
Address is a composite
19
db.people.insertOne(
{ user_id: ”A1234″, name: ”Sam”,
address: { no: 28, street: “Happy St”, suburb: “Happyville”},
object. Sports is an array.
age: 23, sports: [“AFL”, “Cricket”, “Rugby”] } )
19
A complex insertion
Address is a composite object. Sports is an array.
Ø In MongoDB Compass:
20
20
10
21
5/5/20
Find a document
Ø In SQL we use SELECT to find/ retrieve data. In MongoDB, we use find() method.
db.<
This specifies the conditions. Similar to WHERE clause in SQL
Ø Next few slides demonstrate how queries and projections are formed.
This specifies which fields to
display. Similar to SELECT 21 clause in SQL.
Find a document
Ø In SQL:
Ø In MongoDB Shell: db.people.find()
SELECT *
FROM people;
22
22
11
23
5/5/20
Find some fields in a document
Ø In SQL:
Ø In MongoDB Shell:
Leave this empty, as there isn’t any filtering condition.
SELECT ROWID, user_id, status
FROM people;
db.people.find(
{ },
{ user_id: 1, status: 1 }
)
If you do not want to see the
document ID, add _id: 0 to the projection (field list).
23
Find a document
Ø In SQL:
Ø In MongoDB Compass:
SELECT ROWID, user_id, status
FROM people;
24
24
12
5/5/20
Filter a document
filtering condition.
Ø In SQL:
Ø In MongoDB Shell:
SELECT user_id, status
FROM people
WHERE status = ‘A’;
db.people.find(
{ status: “A” },
{ user_id: 1, status: 1, _id: 0 }
)
25
25
Filter a document
Ø In SQL:
Ø In MongoDB Compass:
SELECT user_id, status
FROM people
WHERE status = ‘A’;
26
26
13
27
5/5/20
Filter a document – with multiple conditions
Ø In SQL:
Ø In MongoDB Shell:
AND is the default.
SELECT *
FROM people
WHERE status = ‘A’ AND
age = 25;
db.people.find(
{ status: “A”,
age: 25 } )
27
Filter a document
Ø In SQL:
Ø In MongoDB Compass:
SELECT *
FROM people
WHERE status = ‘A’ AND
age = 25;
28
28
14
29
5/5/20
Filter a document – with OR conditions
Ø In SQL:
SELECT *
FROM people
WHERE status = ‘A’ OR
age = 25;
Ø In MongoDB Shell:
OR is applied to an array of conditions. [ ] denotes an array.
db.people.find(
{ $or: [ { status: “A” } , { age: 25 } ] }
)
29
Filter a document – with OR conditions
Ø In SQL:
Ø In MongoDB Compass:
SELECT *
FROM people
WHERE status = ‘A’ OR
age = 25;
30
30
15
5/5/20
Filter a document – with “Not Equal”
Ø In SQL:
Ø In MongoDB Shell:
$ne means “not equal”
SELECT *
FROM people
WHERE age <> 25;
db.people.find(
{ age: { $ne: 25 } }
)
31
31
Filter a document – middle-aged people!
Ø In SQL:
Ø In MongoDB Shell:
db.people.find(
{ age: { $gt: 35, $lte: 60 }}
$gt: greater than
$gte: greater than or equal $lt: less than
$lte: less than or equal
SELECT *
FROM people
WHERE age > 35 AND age <= 60;
)
32
32
16
33
5/5/20
Filter a document – Partial Matches
Ø In SQL:
Ø In MongoDB Shell:
db.people.find(
{name: /Sam/}
)
More complex regular expressions are possible. To be discussed later. E.g.
{name: {$regex: /Sam/ } }
SELECT *
FROM people
WHERE name LIKE ‘%Sam%;
33
Filter a document – Using Composite Fields
Ø In SQL: Cannot be done!
Ø In MongoDB Shell:
SELECT *
FROM people
WHERE address.suburb = ‘Happyville’;
db.people.find(
{“address.suburb”: “Happyville”}
)
Make sure to use double quotes.
34
34
17
5/5/20
Filter a document – Using Array Values
Ø In SQL: Cannot be done!
Ø In MongoDB Shell:
SELECT *
FROM people
WHERE sport = [‘AFL’, ‘Cricket’]
db.people.find(
{sports:"AFL", "Cricket"}
)
Return documents that exactly contain this array.
35
35
Aggregations -- count()
Ø In SQL:
Ø In MongoDB Shell: db.people.find().count()
SELECT COUNT(*)
FROM people;
Object-oriented. Two ways to do it:
db.collection.find().count() db.collection.count()
36
36
18
37
5/5/20
Sorting
Ø In SQL:
Ø In MongoDB Shell: db.people.find().sort({name: 1})
db.people.find().sort({name: -1})
37
Descending order
SELECT *
FROM people
ORDER BY name;
Sorting
Ø In SQL:
Ø In MongoDB Compass:
SELECT *
FROM people
ORDER BY name;
38
38
19
39
5/5/20
Further Readings
Ø https://docs.mongodb.com/manual/reference/sql-comparison/ Ø https://docs.mongodb.com/manual/tutorial/insert-documents/ Ø https://docs.mongodb.com/manual/tutorial/query-documents/
39
Life after coronavirus/ Practical Database Concepts!
Ø Let’s recap what we have achieved (academically/ life experience). Ø Also what’s not achieved.
40
40
20
41
5/5/20
—
Menti-time!
Menti.com
Code: to be supplied
—
Questions?
42
21