<jupyter_start><jupyter_text>Lesson Walkthrough This notebook walks through the material for each of the lesson topics, in order, to ensure they run. This notebook is *not* the "source" for the lesson material, e.g. through `pandoc` conversion. It is rather an environment to quickly sketch out and test ideas for lesson development.  Introduction (to the lesson overall) This lesson introduces MongoDB, a document-oriented database that departs from the relational nature of SQL. Because SQL is so well-established and prevalent as a database technology, the terms "relational database" and "SQL" are often synonymous. Furthermore, the term "NoSQL" has come to encompass a whole class of database technologies that are non-relational in nature. There are other "NoSQL" technologies such as key-value stores and column stores that differ from MongoDB's document/collection approach, but we will only cover MongoDB today.MongoDB trades off the strictness of SQL for a degree of simplicity and flexibility often desirable for scientists who MongoDB trades off the strictness of SQL for a degree of simplicity andflexibility often desirable for scientists who are often not certain about thebest schema design for new/changing data sets. Whereas SQL lends itself well toenforcing a schema and thus ensuring data validation at the database level, asystem like MongoDB does not require setting/migrating schemas to get startedwith data management. However, this means that application-level datavalidation (e.g. before adding to the database) is particularlyimportant. MongoDB can pick up on implicit schema in your data through thecreation of indexes, which will speed up queries significantly for large datasets.This lesson was originally developed at the [Lawrence Berkeley National Laboratory](http://lbl.gov), where many scientists use MongoDB servers hosted by [NERSC](http://nersc.gov) to [manage workflows](https://pythonhosted.org/FireWorks/) and [process data](https://pythonhosted.org/pymatgen-db/) on supercomputing clusters. The example data for this lesson is from the [Materials Project](https://materialsproject.org), which hosts computed information for tens of thousands of known and predicted inorganic crystalline compounds.  Prerequisites  Getting Ready  Introduction/Setup I'd like at least two challenges per topic. A challenge should be a multiple-choice question (MCQ) or a Parson problem. Likely will be two MCQs. Come up with these first, then base the topic material on it.  Connect to / Import into a Mock Database To accommodate folks who cannot get a real database server running locally on their system, and also to demonstrate the utility of "mocking" for testing real access patterns without an external dependency, we will use the `mongomock` Python library to walk through MongoDB ideas for the first few topics. If you have a Mongo server running, feel free to use `pymongo.MongoClient` instead. First, let's connect to the (mock) server an get a handle for our client.<jupyter_code>import json
from mongomock import MongoClient
from pymongo import MongoClient

def reset_materials():
    client = MongoClient()
    db = client.swc
    db.materials.drop()
    with open('data/mongo-novice-materials.json') as f:
        db.materials.insert_many(json.load(f))
from mongomock import MongoClient
from pymongo import MongoClient

client = MongoClient()<jupyter_output><empty_output><jupyter_text>A MongoDB instance can host multiple databases, which are created dynamically. Here, we will supply a database name as an attribute of the client object, which will prompt MongoDB to create the database with that name if it doesn't exists.<jupyter_code># Refer to the Software Carpentry ("swc") database
db = client.swc
print(db)<jupyter_output>Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'swc')<jupyter_text>In Python, we can `print` something to look at it's string representation, which is often designed to be human-readable and can give us an idea of what's going on. We see here that what we're calling `db` is a Database with the name "swc" that we're accessing through a Mongo client connected to localhost on port 27017.<jupyter_code>print(db.materials)<jupyter_output>Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'swc'), 'materials')<jupyter_text>A MongoDB database is organized as a set of collections, each of which contains a set of documents. To first order, you can for now think of a collection as corresponding to a table in SQL and a collection document as corresponding to a table row in SQL.Just as with databases themselves, database collections are created dynamically in MongoDB. Above, we created a `materials` collection in our database simply by referring to it by name.Now, let's load data from a file and import it as documents into our collection:<jupyter_code>import json<jupyter_output><empty_output><jupyter_text>We first import the `json` module from the Python standard library. `JSON`, which stands for "Javascript Object Notation", is a way to express simple data structures that is widely used in web-based applications. We'll go over the format in the next topic when we construct a document to insert into our collection, but for now let's focus on importing data that we're given.<jupyter_code>db.materials.drop()
with open('data/mongo-novice-materials.json') as f:
    db.materials.insert_many(json.load(f))<jupyter_output><empty_output><jupyter_text>We are using a Python context manager to open a file and ensure that it is closed when we are done processing the file contents. In this case, we use the `json` module to load the file contents as Python-native data structures, which we then hand off to the `insert_many` method of database collections to insert all of the loaded documents.<jupyter_code>db.materials.count()<jupyter_output><empty_output><jupyter_text>To confirm we have the data loaded, we use the `count()` method of a collection object and see that we have more than zero documents in our `materials` collection.<jupyter_code>with open('data/mongo-novice-materials.json') as f:
    dataset = json.load(f)
print(type(dataset))
print(type(dataset[0]))
db.publications.count()<jupyter_output><empty_output><jupyter_text>Insert Data<jupyter_code>!wget "https://raw.githubusercontent.com/materialsproject/pymatgen/master/pymatgen/core/periodic_table.json" -O data/periodic_table.json
import json
with open('data/periodic_table.json') as f:
    periodic_table = json.load(f)
from operator import itemgetter
def pluck(key, mappings):
    return map(itemgetter(key), mappings)
db.elements.insert_many([v for v in periodic_table.values()])
def project_only(*keys):
    doc = {"_id": 0}
    doc.update({k: 1 for k in keys})
    return doc
periodic_table['Cu']
[d for d in db.elements.find({}, project_only('Name', 'Atomic no')).limit(3)]
[d for d in db.materials.find({}, project_only('material_id', 'pretty_formula', 'nelements')).limit(3)]
db.materials.find_one({"chemsys": "Na-O"})
from datetime import datetime
material = {"fake": True,
            "elements": ["Na", "O"],
            "band_gap": 1.736,
            "last_updated": datetime.utcnow(),
            "spacegroup": {"crystal_system": "hexagonal", "number": 189}}
result = db.materials.insert_one(material)
result.inserted_id
print(result.inserted_id.generation_time)
from bson import json_util
print(json_util.dumps(db.materials.find_one(result.inserted_id),
                      indent=2))
result = db.materials.delete_many({"fake": True})
result.deleted_count<jupyter_output><empty_output><jupyter_text>Find data You can use the `find()` method to issue a query to retrieve data from a collection in MongoDB. All queries in MongoDB have the scope of a single collection.Queries can return all documents in a collection or only the documents that match a specified filter or criteria. You can specify the filter or criteria in a document and pass it as a parameter to the `find()` method.The `find()` method returns query results in a cursor, which is an iterable object that yields documents.  Gotta catch 'em all To return all documents in a collection, call the `find()` method without a criteria document.<jupyter_code>cursor = db.materials.find()<jupyter_output><empty_output><jupyter_text>Let's iterate over the cursor and print a few material ids.<jupyter_code>how_many = 5
counter = 0
for document in cursor:
    if counter < how_many:
        print(document['material_id'])
        counter += 1
    else:
        break<jupyter_output>mp-568345
mp-12671
mp-1703
mp-5152
mp-569624<jupyter_text>There's an easier way to limit how many documents are yielded by a cursor:<jupyter_code>for document in cursor.limit(5):
    print(document['material_id'])<jupyter_output>mp-552787
mp-188
mp-600216
mp-2310
mp-780541<jupyter_text>Query by a top-level field The following operation finds documents whose **nelements** field equals **3**:<jupyter_code>cursor = db.materials.find({"nelements": 3})<jupyter_output><empty_output><jupyter_text>Let's print ("pretty print", for nice indentation) a few of the results:<jupyter_code>from pprint import pprint

for doc in cursor.limit(3):
    pprint(doc)<jupyter_output>{'_id': ObjectId('56ce4c367943f62692beb002'),
 'chemsys': 'Er-O-S',
 'elasticity': None,
 'elements': ['Er', 'O', 'S'],
 'material_id': 'mp-12671',
 'nelements': 3,
 'pretty_formula': 'Er2SO2',
 'spacegroup': {'crystal_system': 'trigonal',
                'hall': '-P 3 2=',
                'number': 164,
                'point_group': '-3m',
                'source': 'spglib',
                'symbol': 'P-3m1'}}
{'_id': ObjectId('56ce4c367943f62692beb004'),
 'chemsys': 'La-O-Si',
 'elasticity': None,
 'elements': ['La', 'O', 'Si'],
 'material_id': 'mp-5152',
 'nelements': 3,
 'pretty_formula': 'La2SiO5',
 'spacegroup': {'crystal_system': 'monoclinic',
                'hall': '-P 2yab',
                'number': 14,
                'point_group': '2/m',
                'source': 'spglib',
                'symbol': 'P2_1/c'}}
{'_id': ObjectId('56ce4c367943f62692beb007'),
 'chemsys': 'Cl-Fe-O',
 'elasticity': {'G_Reuss': 4.800058831100799,
                'G_VRH': 15.695815587428928,
    [...]<jupyter_text>Projection to select fields That last query returned all fields for each document. We can use a projection, specified as JSON, to indicate which fields we want. The `_id` field is included by default -- we must be explicit if we don't want it returned.<jupyter_code>cursor = db.materials.find({"nelements": 3},
                           {"material_id": 1, "pretty_formula": 1, "_id": 0})

for doc in cursor.limit(3):
    pprint(doc)<jupyter_output>{'material_id': 'mp-12671', 'pretty_formula': 'Er2SO2'}
{'material_id': 'mp-5152', 'pretty_formula': 'La2SiO5'}
{'material_id': 'mp-552787', 'pretty_formula': 'FeClO'}<jupyter_text>Query by a field in an embedded document To specify a condition on a field within an embedded document, use dot notation. Dot notation requires quotes around the whole dotted field name.<jupyter_code>cursor = db.materials.find({"spacegroup.crystal_system": "cubic"})

print(cursor.count())<jupyter_output>9408<jupyter_text>Projection can take advantage of the same dot notation:<jupyter_code>cursor = db.materials.find({"nelements": 2}, {"spacegroup.crystal_system": 1, "elements": 1, "_id": 0})

for doc in cursor.limit(3):
    pprint(doc)<jupyter_output>{'elements': ['Yb', 'Zn'], 'spacegroup': {'crystal_system': 'cubic'}}
{'elements': ['Cr', 'Hf'], 'spacegroup': {'crystal_system': 'hexagonal'}}
{'elements': ['B', 'Lu'], 'spacegroup': {'crystal_system': 'cubic'}}<jupyter_text>Query by a field in an array How many materials in our collection contain iron? When a field is an array, testing for membership has the same form as testing for equality:<jupyter_code>db.materials.find({"elements": "Fe"}).count()<jupyter_output><empty_output><jupyter_text>If you supply an array as the value under test, we can see that four polymorphs of iron are present in our collection:<jupyter_code>db.materials.find({"elements": ["Fe"]}).count()<jupyter_output><empty_output><jupyter_text>Challenge: Dot Notation and Projections Which query below yields documents containing the crystal system and spacegroup number for all binary compounds?<jupyter_code>cursor = db.materials.find({"nelements": 2}, {"spacegroup": {"crystal_system": 1, "number": 1}})
for doc in cursor.limit(3):
    pprint(doc)
cursor = db.materials.find({"nelements": 2}, {"spacegroup.crystal_system": 1, "spacegroup.number": 1})
for doc in cursor.limit(3):
    pprint(doc)<jupyter_output>{'_id': ObjectId('56ce4c367943f62692beb003'),
 'spacegroup': {'crystal_system': 'cubic', 'number': 221}}
{'_id': ObjectId('56ce4c367943f62692beb005'),
 'spacegroup': {'crystal_system': 'hexagonal', 'number': 194}}
{'_id': ObjectId('56ce4c367943f62692beb006'),
 'spacegroup': {'crystal_system': 'cubic', 'number': 221}}<jupyter_text>Challenge: Combining Conditions Which query below returns the number of binary oxides (oxygen-containing, two-element materials) in our collection?<jupyter_code>db.materials.find({"elements": "O"}).limit(2).count()
db.materials.find({"elements": "O", "nelements": 2}).count()
db.materials.find({"elements": ["O"], "nelements": 2}).count()<jupyter_output><empty_output><jupyter_text>Specify Conditions with Operators<jupyter_code>COMMON_PROJ = {
    "material_id": 1,
    "pretty_formula": 1,
    "_id": 0,
}

def print_a_few_for(query):
    proj = {k: 1 for k in query}
    proj.update(COMMON_PROJ)
    for doc in db.materials.find(query, proj).limit(5):
        pprint(doc)

query = {"nelements": {"$gte": 3}}
print_a_few_for(query)
print_a_few_for({"nelements": {"$lt": 3}})
db.materials.find({"chemsys": "Fe-O", "spacegroup.crystal_system": "cubic"}).count()
db.materials.find({
    "$or": [{"nelements": 2}, {"nelements": 4}]
}).count()
print_a_few_for({"elasticity": {"$exists": True}})
print_a_few_for({"elasticity": {"$ne": None}})<jupyter_output>{'elasticity': {'G_Reuss': 64.86603926197118,
                'G_VRH': 66.1852057595004,
                'G_Voigt': 67.50437225702963,
                'K_Reuss': 189.54199905990907,
                'K_VRH': 189.5741939942109,
                'K_Voigt': 189.60638892851273,
                'calculations': {'energy_cutoff': 700.0,
                                 'kpoint_density': 7000,
                                 'pseudopotentials': ['Hf_pv', 'Cr_pv']},
                'elastic_anisotropy': 0.2037075326032598,
                'elastic_tensor': [[295.8188907773902,
                                    133.82065045871653,
                                    140.1170929005653,
                                    0.0,
                                    0.0,
                                    -0.036991826666666644],
                                   [133.82065045871653,
                                    299.3255081549423,
                                    141.6470726644416,
       [...]<jupyter_text>Sorting<jupyter_code>import pymongo
cursor = db.materials.find().sort([
    ("borough", pymongo.ASCENDING),
    ("address.zipcode", pymongo.DESCENDING)
])
COMMON_PROJ = {
    "material_id": 1,
    "pretty_formula": 1,
    "spacegroup.number": 1,
    "_id": 0,
}

def cursor_with_minimal_projection(query):
    proj = {k: 1 for k in query}
    proj.update(COMMON_PROJ)
    cursor = db.materials.find(query, proj)
    return cursor

def print_a_few_for(query):
    cursor = cursor_with_minimal_projection(query)
    for doc in cursor.limit(5):
        pprint(doc)
filt = {"elasticity": {"$ne": None}}
proj = {"elasticity.poisson_ratio": 1}
proj.update(COMMON_PROJ)
cursor = db.materials.find(filt, proj).sort("elasticity.poisson_ratio")
for doc in cursor.limit(5):
    pprint(doc)
cursor = db.materials.find(filt, proj).sort("elasticity.poisson_ratio", -1)
for doc in cursor.limit(5):
    pprint(doc)
cursor = db.materials.find(filt, proj).sort([
        ("nelements", -1),
        ("elasticity.poisson_ratio", -1),
    ])
for doc in cursor.limit(5):
    pprint(doc)
cursor = db.materials.find({
        "nelements": {"$lt": 3}
    }, {
        "_id": 0,
        "nelements": 1,
        "elasticity.K_VRH": 1,
        "pretty_formula": 1,
        "spacegroup.crystal_system": 1,
        "material_id": 1,
    })

cursor = cursor.sort([("nelements", 1),("elasticity.K_VRH", -1)])

for doc in cursor.limit(5):
    pprint(doc)
cursor = db.materials.find({
        "nelements": {"$lt": 3}
    }, {
        "_id": 0,
        "nelements": 1,
        "elasticity.K_VRH": 1,
        "pretty_formula": 1,
        "spacegroup.crystal_system": 1,
        "material_id": 1,
    }, sort=[
        ("nelements", 1),
        ("elasticity.K_VRH", -1)
    ])

for doc in cursor.limit(5):
    pprint(doc)
systems = filter(None, db.materials.distinct("spacegroup.crystal_system"))
for s in systems:
    materials = list(db.materials.find(
        {"spacegroup.crystal_system": s}, {"spacegroup.number": 1}
    ).sort("spacegroup.number"))
    print(s, materials[0]["spacegroup"]["number"], materials[-1]["spacegroup"]["number"])<jupyter_output>cubic 195 230
trigonal 143 167
monoclinic 3 15
hexagonal 169 194
orthorhombic 16 74
tetragonal 75 142
triclinic 1 2<jupyter_text>Update<jupyter_code>import json

db.elements.drop()
with open('data/periodic_table.json') as f:
    for symbol, rest in json.load(f).items():
        doc = {"Symbol": symbol}
        doc.update(rest)
        db.elements.insert_one(doc)
halogens = ["F", "Cl", "Br", "I", "At"]
def halide_systems():
    for halogen in halogens:
        X = db.elements.find_one({"Symbol": halogen})["X"]
        for doc in db.elements.find({"X": {"$lt": X}}):
            yield "-".join(sorted([doc["Symbol"], halogen]))
[s for s in list(halide_systems()) if 'Cu' in s]
reset_materials()
db.materials.find({"chemsys": "Cu-F"}).count()
result = db.materials.update_one(
    {"chemsys": "Cu-F"},
    {
        "$set": {
            "tags": ["halide"]
        },
        "$currentDate": {"lastModified": True}
    }
)
result.matched_count
result.modified_count
db.materials.find_one({"chemsys": "Cu-F"},
                      {"tags": 1, "lastModified": 1, "material_id": 1})
result = db.materials.update_one(
    {"material_id": "mp-1229"},
    {"$set": {"elasticity.calculations.source": "Private communication"}}
)

print(result.matched_count, result.modified_count)
result = db.materials.update_many(
    {"chemsys": {"$in": list(halide_systems())}},
    {"$addToSet": {"tags": "halide"}}
)
result.matched_count, result.modified_count
db.materials.find({"tags": "halide"}).count()
result = db.materials.update_one(
    {"material_id": "mp-NaN"},
    {"$set": {"elasticity.calculations.source": "Private communication"}}
)
result.matched_count, result.modified_count
result = db.materials.update_one(
    {"material_id": "mp-NaN"},
    {"$set": {"elasticity.calculations.source": "Private communication"}},
    upsert=True
)
result.matched_count, result.modified_count, result.upserted_id
db.materials.find_one({"material_id": "mp-NaN"})<jupyter_output><empty_output><jupyter_text>Removing<jupyter_code>result = db.materials.delete_many({"material_id": "mp-NaN"})
result = db.materials.delete_many({"nelements": 1})
result.deleted_count
result = db.materials.delete_many({})
result.deleted_count
db.materials.count()
db.materials.drop()<jupyter_output><empty_output><jupyter_text>Re(Importing)<jupyter_code>import json
from mongomock import MongoClient
#from pymongo import MongoClient

def reset_materials():
    client = MongoClient()
    db = client.swc
    db.materials.drop()
    with open('data/mongo-novice-materials.json') as f:
        db.materials.insert_many(json.load(f))
%timeit reset_materials()
with open('data/materials.mongoimportable.json', 'w') as outfile:
    with open('data/mongo-novice-materials.json') as infile:
        docs = json.load(infile)
        outfile.writelines([json.dumps(doc)+'\n' for doc in docs])
db.materials.drop()
!mongoimport --db swc --collection materials < data/materials.mongoimportable.json<jupyter_output>connected to: 127.0.0.1
2016-02-25T10:46:31.004-0800 			53700	17900/second
2016-02-25T10:46:31.600-0800 check 9 66140
2016-02-25T10:46:31.607-0800 imported 66140 objects<jupyter_text>Aggregation<jupyter_code>cursor = db.materials.aggregate(
    [
        {"$group": {"_id": "$nelements", "count": {"$sum": 1}}}
    ]
)
for doc in cursor:
    print(doc)
cursor = db.materials.aggregate(
    [
        {"$match": {"elements": "O"}},
        {"$group": {"_id": "$nelements", "count": {"$sum": 1}}}
    ]
)

for doc in cursor:
    print(doc)
cursor = db.materials.aggregate(
    [
        {"$match": {"elements": "O"}},
        {"$group": {"_id": "$nelements", "count": {"$sum": 1}}},
        {"$sort": {"count": -1}}
    ]
)

for doc in cursor:
    print(doc)
db.materials.find_one()
cursor = db.materials.aggregate(
    [
        {"$group": {
                "_id": "$spacegroup.number",
                "count": {"$sum": 1},
                "symbol": {"$first": "$spacegroup.symbol"},
            }},
        {"$sort": {"count": -1}},
        {"$limit": 10}
    ]
)

for doc in cursor:
    print(doc)
def get_chemistry(doc):
    anion = db.elements.find(
        {"Symbol": {"$in": doc["elements"]}},
        {"Symbol": 1, "X": 1, "_id": 0}
    ).sort([('X', -1)])[0]["Symbol"]
    if anion == "O":
        return "Oxide"
    elif anion == "S":
        return "Sulfide"
    elif anion == "F":
        return "Fluoride"
    elif anion == "Cl":
        return "Chloride"
    return None
for doc in db.materials.find({"elements": {"$in": ["O", "S", "F", "Cl"]}}, {"elements": 1}):
    anion = get_chemistry(doc)
    db.materials.update_one({"_id": doc["_id"]}, {"$set": {"anion_chemistry": anion}})
cursor = db.materials.aggregate(
    [
        {"$match": {"anion_chemistry": "Oxide"}},
        {"$group": {
                "_id": "$spacegroup.number",
                "count": {"$sum": 1},
                "symbol": {"$first": "$spacegroup.symbol"},
            }},
        {"$sort": {"count": -1}},
        {"$limit": 10}
    ]
)

for doc in cursor:
    print(doc)<jupyter_output>{'_id': 14, 'count': 4013, 'symbol': 'P2_1/c'}
{'_id': 1, 'count': 3424, 'symbol': 'P1'}
{'_id': 2, 'count': 3039, 'symbol': 'P-1'}
{'_id': 15, 'count': 1856, 'symbol': 'C2/c'}
{'_id': 62, 'count': 1702, 'symbol': 'Pmnb'}
{'_id': 12, 'count': 1415, 'symbol': 'C2/m'}
{'_id': 8, 'count': 975, 'symbol': 'Cm'}
{'_id': 11, 'count': 877, 'symbol': 'P2_1/m'}
{'_id': 4, 'count': 759, 'symbol': 'P2_1'}
{'_id': 166, 'count': 606, 'symbol': 'R-3m'}<jupyter_text>Indexes<jupyter_code>%%timeit

db.materials.distinct("spacegroup.crystal_system")
db.materials.create_index([("spacegroup.crystal_system", 1)])
pprint(db.materials.index_information())
%%timeit

db.materials.distinct("spacegroup.crystal_system")
%timeit db.materials.find_one({"material_id": "mp-49"}, {"pretty_formula": 1, "_id": 0})
db.materials.create_index([("material_id", 1), ("pretty_formula", 1)])
%timeit db.materials.find_one({"material_id": "mp-49"}, {"pretty_formula": 1, "_id": 0})
db.materials.drop_index([("material_id", 1), ("pretty_formula", 1)])
db.materials.create_index([("pretty_formula", 1), ("material_id", 1)])
%timeit db.materials.find_one({"material_id": "mp-49"}, {"pretty_formula": 1, "_id": 0})<jupyter_output>100 loops, best of 3: 12.1 ms per loop
{'fraction_non_alphanumeric': 0.14226689951867785, 'fraction_numerical': 0.03526856071900158, 'mean_word_length': 4.145299145299146, 'pattern_counts': {'":': 168, '<': 97, '<?xml version=': 0, '>': 95, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '591961', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 8347, 'n_tokens_neox': 7446, 'n_words': 2152, 'path': 'lesson_walkthrough.ipynb', 'repo_name': 'dwinston/pymongo-novice-materials', 'size': '23477'}