Quartz 5

09 NoSQL databases

7 min read

NoSQL databases

NoSQL databases

  • “Not only SQL”
  • Stores data differently from relational tables
  • Variety of types (based on their data model):
  • Key-value databases
  • Document databases
  • Wide-column databases
  • Graph databases
  • Provide flexible schemas and scale easily with large amounts of big data and high user loads

Shortcomings of RDBMS vs NoSQL advantages

Relational databasesNoSQL database
Scalabilityscale vertically (adding more powerful hardware)
⇒ expensive + limitations		most are designed for horizontal scaling (i.e. can inc. capacity by adding more computers to the system)
⇒ database can distribute data across multiple servers ⇒ easier and more cost-effective to handle large-scale data
Flexibilityrequire a fixed schema
⇒ challenging to handle rapidly changing data models		support schema-less or flexible schemas
⇒ allows for dynamic and heterogeneous data structures
⇒ useful for applications that need to evolve quickly (supports real-time applications)
Big data handlingstruggle with storing and querying very large datasets, esp if data does not fit well into a tabular formatdistributed file storage systems are well-suited for managing large datasets (high-volume) and efficiently handles queries on distributed data
(Big data: extremely large and diverse collections of structured, unstructured, and semi-structured data that continues to grow exponentially over time)
Semi-structured & unstructured data handlingprimarily designed for structured data that fits into predefined tables and columnscan handle structured or semi-structured data (e.g. MongoDB’s document-based model ⇒ store unstructured and semi-structured data; JSON-like documents can represent complex hierarchical relationships directly within the document itself)

Applications of RDBMS

  • Relational databases are ideal for applications requiring structured data, consistency, and complex querying
  • Financial systems (e.g. Banking, Payment gateway)
  • Strong ACID compliance ⇒ accurate transaction processing
  • E-Commerce and Point of Sale (POS) Systems
  • E.g. Amazon, Shopify, retail chain POS systems
  • Manages product catalogs, order processing, and payment systems with transactional integrity

Applications of NoSQL

  • NoSQL databases excel in handling unstructured or semi-structured data, scaling horizontally, and supporting real-time or high-volume applications
  • Big data analytics
  • Handles massive datasets efficiently
  • Optimised for distributed data storage and real-time processing
  • Content management systems
  • Store diverse content types (text, images, videos) without requiring rigid schemas
  • E-commerce platforms
  • Flexible schema supports dynamic product catalogs and handles massive traffic spikes during sales or events

Terms

MongoDBSQL
Database
CollectionTable
DocumentRow / record
FieldColumn / field

Document / Document-oriented database / Document store

  • Stores information in documents

C:\Users\Andrea\Downloads\mongodb-win32-x86_64-3.4.9 >> bin folder

  • mongod.exe: server
  • Don’t put cursor on it ⇒ freezes application (press “Enter” to unfreeze)
  • mongo.exe: shell
  • No need to access in exam
  • Interactive JavaScript shell to manage a MongoDB server
  • Provides a set of commands to create, update, delete, and query MongoDB database
  • show dbs ⇒ show database names
from pprint import pprint
for db in client.list_database_names(): # returns list of database names in MongoDB server
pprint(db)
  • help
  • use <dbname>
bookdb = client[“bookdb”]
  • db.person.insert({“name”:“andrea”, “class”:“25S6C”, “hobbies”:[“sleeping”, “scrolling”]})
  • show collections
  • Collection is automatically created when a document is added
  • Shows collections in current database
coll = bookdb[“book”]
with open(‘books.json’) as jsonfile:
documents = json.load(jsonfile)
result = coll.insert_many(documents)
db = client.mydatabase
for document in db.mycollection.find(): # or find_one() to give the first (or only) match of a given query
print(document)
  • database.<collection name>.drop()
  • db.person.find({})
  • Select everything (fields + records) from person
  • {} means no field specified
  • E.g. of field specified:
    db.person.find({“class”:”25S6C”})
  • db.person.find({}).pretty() ⇒ find documents
  • db.dropDatabase()
  • db.customers.count_documents({})
  • exit/quit()
  • An interactive JavaScript shell to manage a MongoDB server

On cmd: start mongo shell

Insert data:

Import Database:

  • C:\mongodb\bin>mongoimport —db bookwwdb —collection book —file c:\Data\books.json
  • Check that there are no existing bookdb first

DB-API

pymongo

## import pymongo
from pymongo import MongoClient
client = MongoClient(‘localhost’, 27017)

Connection to a database

  • Query the customers collection in ‘mydatabase’ database
  • Use the method count_documents to return no. of documents in the collection
db = client.mydatabase
db.customers.count_documents({})
mydb = client[“mydatabase”]
coll = mydb[“customers”]
myquery ={"$or":[ {"pageCount": 0},{"isbn":{"$exists": False}} ] }
doc_count = db.books.count_documents(myquery)
print(f"Documents without isbn or with page count=0 :{doc_count}")
doc_count = db.books.count_documents({})
print(f"Total number of documents in the collection: {doc_count}")

Read data – all (no condition)

db.collection.find(query)
db.coll.find({}) # find all documents in collection
documents = coll.find({})
for doc in documents:
print(doc)

Read data (with condition(s))

db.coll.find({“name”:”Ken”, “class”:”S66”}) # show documents with name that contains ‘Ken’ AND class that equals ‘S66
query = {“name”:”Ken”, “class”:”S66”}
documents = coll.find(query)
for doc in documents:
print(doc)

Read data

db.collection.find(query, projection)
  • ‘_id’ field is always included unless explicitly excluded
  • _id field is primary key for every document
  • Specifying a field for inclusion implicitly excludes all other fields except the ‘_id’ field
db.coll.find({“class”:”S66”},{“name”:1,”class”:1}) # show name AND class in document with query in person collection [alternative: $and]

Query condition is the where clause
Projection is fields to be retrieved (i.e. like SELECT statements)
query = {“class”:”S66”}
proj = {“name”:1, “class”:1} // 1 means include, 0 means exclude the field (?)
documents = coll.find(query, proj)
for doc in documents:
print(doc)

Read data using operators

Equal to$eq
Greater than$gt
Less than or equal to$lte
Not equal$ne
In$in
Not in$nin
And$and
Or$or
Exists (matches document that contains or do not contain a specific field, including documents where the field value is null)$exists
Regex$regex
  • Examples
  • docs = coll.find({“qty”: {“$eq”:10}}) # find documents with qty = 10
  • docs = coll.find({“class”: {“$nin”:[“S62”, “S63”]}})
  • docs = coll.find({“$and”:[{“class”:“S66”}, {“name”:“Ken”}]})
  • docs = coll.find({“$and”:[{“class”:{“$eq”:“S66”}},{“name”:{“$eq”:“Ken”}}]})
  • docs = coll.find({“qty”:{“$exists”:True}}) # find documents with qty field
  • docs = coll.find({“qty”:{“$exists”:True, “$in”:[5,10]}})
  • query = {“title”:{“$regex”:“Hadoop”}}

limit() method

  • Returns stated number of documents
  • E.g. ….limit(3) ⇒ only retrieve 3 documents

sort() method

  • Sorts by fields in ascending or descending order
  • ….sort([(“name”,1), (“qty”, -1)]) ⇒ sort by name (ascending) and qty (descending order)

Embedded document

  • = documents that are nested within other documents
  • Allows complex data structures to be represented in a single document
  • E.g.
{“name”:
{
“given”:””Jeanne”,
“family”:”Ang”
}
},
{“name”:
{
“given”:””John”,
“family”:”Tan”
}
},
  • Query exact matches on embedded document
db.person.find({“name”:{“given”:”Jeanne”, “family”:”Ang”})
OR
db.person.find({“name.given”:”Jeanne”})
db.person.find({“name.family”:”Ang”})

Array elements

  • E.g.
  • Query for an array element
QueryReturns documents where array field subject contains:
db.person.find({“subjects”:”CP”})The element “CP”
db.person.find({“subjects”:[“CP”]})Only “CP” element
db.person.find({“subjects”:{$in:[“CP”,”PH”]}})The element “CP” or “PH”

Array of embedded documents

  • E.g.
db.person.find({“vaccination.manufacturer”:”Moderna”})
// returns documents where vaccination array contains the element with manufacturer field equals “Moderna”
  • Use $elemMatch operator to specify multiple criteria on an array element
db.person.find({“vaccination”:{“$elemMatch”: {“date”:{“$lte”: new Date(“2022-06-01”)}, “manufacturer”:“Phfizer-BioNTech”}}})
// returns documents where vaccination array contains at least one element with both the date field less than or equal to 2022-06-01 and the manufacturer = “Pfizer-BioNTech”

Collection level operations: Collection level operations - PyMongo 4.16.0 documentation

db.coll._______()

.insert_one() method

  • Inserts a new document into the collection
  • Returns an instance of InsertOneResult(), which has a property, inserted_id, that holds the id of the inserted document
db = client.<database>
document = {“name”:”Peter”, “address”:”abc def”} # dictionary
result = db.<collection>.insert_one(document)
print(result.inserted_id)

.insert_many() method

  • Inserts an array of documents into the collection
  • Returns an instance of InsertManyResult, which has a properly, inserted_ids, that holds the list of ids of the inserted document
custList =[
{“name”: “Amy”, “address”:“Apple ST 652”},
{“name”: “Hannah”, “address”:“Montain 21”},
{“name”: “Michael”, “address”:“Valley 345”},
{“name”: “Sandy”, “address”:“Ocean blvd 2”},
{“name”: “Betty”, “address”:“Green Grass 1”},
{“name”: “Richard”, “address”:“Sky st 331”}
]
db = client.<database>
result = db.<collection>.insert_many(custList)
print(result.inserted_ids)

.update_one() / .update_many() method

  • Updates the 1st document that satisfies the query in a collection
  • Returns an instance of UpdateResult
  • .update_one(filter, update, options)
  • Filter: criteria, conditions to select document
  • Update operators
$currentDateset value to field to current data – Date or Timestamp
$incincrements value of the field by a specified amount
$setsets value of a field in a document
$unsetremoves specified field from a document
  • Options:
  • Upsert – if set to true, creates a new document when no document matches the query criteria. Else, does not insert a new document when no match is found
E.g. db.coll.update_many(
{“name”:“John”}, {“$set”: {“class”:“S6C”}},
{“upsert”: true}
)
myquery = {“address”: “Valley 345”}
newval = {“$set”:{“address”:”Canyon 123”}}
db = client.<database>
res = db.<collection>.update_one(myquery, newval)
OR res = db.<collection>.update_many(myquery, newval, )
for document in db.<collection>.find():
print(document)
print(f”No. of documents updated: {res.modified_count}”)

db.<collection>.delete_one() / _many() methods

  • delete_one(): delete the 1st document that satisfies the query in a collection
  • Returns an instance of DeleteResult
db = client.<database>
query = {“address”:”Canyon 123”}
db.<collection>.delete_one(query)
for document in mycoll.find():
print(document)
# delete document with ObjectId = ‘67…’
db.coll.delete_one({“_id”:“ObjectId(‘67a1b1e47b2fe53e07204df2’)”})
query = {“name”:”Minnie”}
x = db.<collection>.delete_many(query)
print(“No. of documents deleted: {}”.format(x.deleted_count))
# i.e. print(f"No. of documents deleted: {x.deleted_count}")
  • db.coll.delete_many({}) deletes all documents in the collection

See bookmarks ⇒ CPs ⇒ pymongo ⇒ total 5 .ipynb jupyter notebook exercises

Graph View

Backlinks