<jupyter_start><jupyter_text>Mount to MongoDBMongoDB is a useful NoSQL database for storing large amounts of JSON. We can run this on a server, or locally if we have enough drive space. Compared with scanning the data files, MongoDB can allow us to index the data, this way we can pick which specific data we want to look at more easily.<jupyter_code>import pymongo
import json
import gzip
import os
import datetime
# initialise client class for interacting with db
client = pymongo.MongoClient()
# create or open database
db = client['unpaywall']
# create or open collection (table) within database
collection = db['snapshot']
# check
# collection.find_one()
filepath = r'C:\Users\aday\OneDrive - SAGE Publishing\DATA\Unpaywall\unpaywall_snapshot_2021-02-18T160139.jsonl.gz'<jupyter_output><empty_output><jupyter_text>Upserting Vs Inserting- Inserting is where we simply put a new record into our database. It is the fastest way to CREATE our database from scratch in 1 go. You can also use batch processes to do this faster (but below we only insert one at a time). - If we want to add to our database (i.e. create in multiple gos), or update it with a new snapshot, then we can upsert instead. This way, we check to see if our document already exists in the data.   - If not, then we insert it.  - If so, and our document has been updated, then we update the document in our database.  - If both documents are identical, we skip the update. Upserting avoids unecessarily writing the the database, which can be slow. __Note__ that to upsert data, you need to have an index on the doi field. This is done in the cell below the next one. Be aware that, the larger your collection, the slower creating the index will be.<jupyter_code>import datetime
import gzip
batch_size = 1000
batch = []
with gzip.open(filepath,'rb') as f:
    
    for i,line in enumerate(f):
        
        
        # INSERT DATA
#         if i<20000000:
#             continue
#         else:
#             try:
#                 collection.insert_one(json.loads(line))
#             except Exception as e:
#                 print('Error: ', e)
#                 print('Bad line: ', line)
#                 print()
#         if i%1000000==0:
#             print(i, 'records done')


        ## UPSERT DATA
        line = json.loads(line)
        doi = line['doi']
        
        
        batch.append(pymongo.UpdateOne({'doi':doi}, {'$set':line}, upsert = True))
        try:
            if i%batch_size==0:
                collection.bulk_write(batch)
                batch = []
                if i%(1000*batch_size)==0:
                    print(datetime.datetime.now(),i, 'records written to Mongo')
        except Exception as e:
            print('ERROR', e)
            print('Batch write failed! Try items individually')
            for item in batch:
                try:
                    collection.bulk_write([item])
                except Exception as e:
                    print('Error: ', e)
                    print('ITEM:', item)
                batch = []
    # Final batch
    if len(batch)>0:
        try:
            collection.bulk_write(batch)
        except:
            print('Batch write failed! Try items individually')
            for item in batch:
                try:
                    collection.write(item)
                except Exception as e:
                    print('Error: ', e)
                    print('ITEM:', item)
    else:
        print('No records to write. Skipping')<jupyter_output>2021-02-22 13:42:14.432223 20000000 records written to Mongo
2021-02-22 13:53:17.333130 21000000 records written to Mongo
2021-02-22 14:05:10.295292 22000000 records written to Mongo
2021-02-22 14:16:18.747638 23000000 records written to Mongo
2021-02-22 14:26:53.700182 24000000 records written to Mongo
2021-02-22 14:38:20.724211 25000000 records written to Mongo
2021-02-22 14:51:38.389285 26000000 records written to Mongo
2021-02-22 15:08:15.265702 27000000 records written to Mongo
2021-02-22 15:25:05.560059 28000000 records written to Mongo
2021-02-22 15:40:38.005620 29000000 records written to Mongo
2021-02-22 15:54:36.483126 30000000 records written to Mongo
2021-02-22 16:06:17.736212 31000000 records written to Mongo
2021-02-22 16:16:32.765132 32000000 records written to Mongo
2021-02-22 16:26:43.826653 33000000 records written to Mongo
2021-02-22 16:37:00.626381 34000000 records written to Mongo
2021-02-22 16:47:33.917943 35000000 records written to Mongo
2021-02-22 16:57:46.5819[...]<jupyter_text>Create Indexes<jupyter_code># create index (or simply confirm it's there already)
collection.create_index([( 'doi', pymongo.ASCENDING )])
collection.create_index([( 'year', pymongo.ASCENDING )])
collection.create_index([( 'publisher', pymongo.ASCENDING )])<jupyter_output><empty_output><jupyter_text>CheckHow quickly can we retrieve an item by its doi? Pick any DOI you like!<jupyter_code>%%time
collection.find_one({'doi': '10.1080/21645515.2017.1330236'})<jupyter_output>Wall time: 5.01 ms
{'fraction_non_alphanumeric': 0.08937101910828026, 'fraction_numerical': 0.10668789808917198, 'mean_word_length': 2.798185941043084, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '18764', 'license': 'MIT', 'n_tokens_mistral': 1875, 'n_tokens_neox': 1469, 'n_words': 534, 'path': '00c_mount_snapshot_mongodb.ipynb', 'repo_name': 'ad48/Unpaywall_EDA', 'size': '5024'}