serhii.net

In the middle of the desert you can say anything you want

UNLISTED

10 Jan 2023

DB Leistungsnachweis

Task 1: Hello world

a)

test> use test
already on db test

Create collection, set map “id” to “_id” and make it the only index.

test> db.createCollection("images", {autoIndexId: true})
{ ok: 1 }
test> db.images.insertMany([{
...     _id: 1,
...     name: "fish.jpg",
...     time: "17:46",
...     user: "bob",
...     camera: "nikon",
...     tags: ['tuna', 'shark'],
...     info: {
...         width: 100,
...         height: 200,
...         size: 12345
...     }
... }, {
...     _id: 2,
...     name: "trees.jpg",
...     time: "17:57",
...     user: "john",
...     camera: "canon",
...     tags: ['oak'],
...     info: {
...         width: 30,
...         height: 250,
...         size: 32091
...     }
... }])
{ acknowledged: true, insertedIds: { '0': 1, '1': 2 } }

Print all inserted documents:

test> db.images.find()
[
  {
    _id: 1,
    name: 'fish.jpg',
    time: '17:46',
    user: 'bob',
    camera: 'nikon',
    tags: [ 'tuna', 'shark' ],
    info: { width: 100, height: 200, size: 12345 }
  },
  {
    _id: 2,
    name: 'trees.jpg',
    time: '17:57',
    user: 'john',
    camera: 'canon',
    tags: [ 'oak' ],
    info: { width: 30, height: 250, size: 32091 }
  }
]

b) Usage with a programming language

from pymongo import MongoClient
from pymongo.collection import Collection
from pymongo.database import Database
from pprint import pp

REMAINING_FOUR_DOCUMENTS = [
    {
        'id': 3,
        'name': "snow.png",
        'time': "17:56",
        'user': "john",
        'camera': "canon",
        'tags': ["tahoe", "powder"],
        'info': {'width': 64, 'height': 64, 'size': 1253},
    },
    {
        '_id': 4,
        'name': "hawaii.jpg",
        'time': "17:59",
        'user': "john",
        'camera': "nikon",
        'tags': ["maui", "tuna"],
        'info': {'width': 128, 'height': 64, 'size': 92834},
    },
    {
        '_id': 5,
        'name': "hawaii.gif",
        'time': "17:58",
        'user': "bob",
        'camera': "canon",
        'tags': ["maui"],
        'info': {'width': 320, 'height': 128, 'size': 49287},
    },
    {
        '_id': 6,
        'name': "island.gif",
        'time': "17:43",
        'user': "zztop",
        'camera': "nikon",
        'tags': ["maui"],
        'info': {'width': 640, 'height': 480, 'size': 50398},
    },
]
# TODO - add two more

DB_NAME = "test"
COLLECTION_NAME = "images"


def run():
    client = MongoClient()
    db = client[DB_NAME]
    collection = db[COLLECTION_NAME]
    insert_remaining_four_documents(collection=collection)
    print_docs(collection=collection)
    delete_docs(db=db, collection_name=COLLECTION_NAME)


def insert_remaining_four_documents(collection: Collection) -> None:
    res =  collection.insert_many(documents=REMAINING_FOUR_DOCUMENTS)
    return res


def print_docs(collection: Collection) -> None:
    print("Documents:")
    docs = collection.find()
    for d in docs:
        pp(d)

def delete_docs(db: Database,  collection_name: str) -> None:
    print(f"Deleting collection...")
    db.drop_collection(collection_name)
    print(f"Deleted.")

    

if __name__ == "__main__":
    run()

Program output:

12:11:05 ~/Uni/db/lnw/ 0
> python3 python_task1.py
Documents:
{'_id': 1,
 'name': 'fish.jpg',
 'time': '17:46',
 'user': 'bob',
 'camera': 'nikon',
 'tags': ['tuna', 'shark'],
 'info': {'width': 100, 'height': 200, 'size': 12345}}
{'_id': 2,
 'name': 'trees.jpg',
 'time': '17:57',
 'user': 'john',
 'camera': 'canon',
 'tags': ['oak'],
 'info': {'width': 30, 'height': 250, 'size': 32091}}
{'_id': ObjectId('63c3df5070d9df3076e79927'),
 'id': 3,
 'name': 'snow.png',
 'time': '17:56',
 'user': 'john',
 'camera': 'canon',
 'tags': ['tahoe', 'powder'],
 'info': {'width': 64, 'height': 64, 'size': 1253}}
{'_id': 4,
 'name': 'hawaii.jpg',
 'time': '17:59',
 'user': 'john',
 'camera': 'nikon',
 'tags': ['maui', 'tuna'],
 'info': {'width': 128, 'height': 64, 'size': 92834}}
{'_id': 5,
 'name': 'hawaii.gif',
 'time': '17:58',
 'user': 'bob',
 'camera': 'canon',
 'tags': ['maui'],
 'info': {'width': 320, 'height': 128, 'size': 49287}}
{'_id': 6,
 'name': 'island.gif',
 'time': '17:43',
 'user': 'zztop',
 'camera': 'nikon',
 'tags': ['maui'],
 'info': {'width': 640, 'height': 480, 'size': 50398}}
Deleting collection...
Deleted.

Task 2 - query language

Importing etc.

12:17:24 ~/Uni/db/lnw/ 0
> mongoimport -d test -c zips zips.json
2023-01-15T12:17:27.363+0100	connected to: mongodb://localhost/
2023-01-15T12:17:27.454+0100	continuing through error: E11000 duplicate key error collection: test.zips index: _id_ dup key: { _id: "32350"}
2023-01-15T12:17:27.567+0100	continuing through error: E11000 duplicate key error collection: test.zips index: _id_ dup key: { _id: "63673"}
2023-01-15T12:17:27.691+0100	continuing through error: E11000 duplicate key error collection: test.zips index: _id_ dup key: { _id: "42223"}
2023-01-15T12:17:27.745+0100	29467 document(s) imported successfully. 3 document(s) failed to import.

a)

SELECT city, pop, state
FROM zips
WHERE id = 35203

```js
test> db.zips.find({_id:'35203'})
[
  {
    _id: '35203',
    city: 'BIRMINGHAM',
    loc: [ -86.806626, 33.520994 ],
    pop: 4064,
    state: 'AL'
  }
]

b)

SELECT *
FROM zips
WHERE pop > 29778
AND (latitude > -86.4 OR longitude < 40.2)

db.zips.find({
    $and: [{
        $or: [{
            'loc.0': {
                $gt: -86.4
            }
        }, {
            'loc.1': {
                $lt: 40.2
            }
        }]
    }, {
        'pop': {
            $gt: 29778
        }
    }]
})

Output:

[
  {
    _id: '35020',
    city: 'BESSEMER',
    loc: [ -86.947547, 33.409002 ],
    pop: 40549,
    state: 'AL'
  },
  {
    _id: '35023',
    city: 'HUEYTOWN',
    loc: [ -86.999607, 33.414625 ],
    pop: 39677,
    state: 'AL'
  },
  {
    _id: '35055',
    city: 'CULLMAN',
    loc: [ -86.829777, 34.176146 ],
    pop: 31708,
    state: 'AL'
  },
  {
    _id: '35211',
    city: 'BIRMINGHAM',
    loc: [ -86.85904, 33.481565 ],
    pop: 35836,
    state: 'AL'
  },
  {
    _id: '35215',
    city: 'CENTER POINT',
    loc: [ -86.693197, 33.635447 ],
    pop: 43862,
    state: 'AL'
  },
  {
    _id: '35401',
    city: 'TUSCALOOSA',
    loc: [ -87.562666, 33.196891 ],
    pop: 42124,
    state: 'AL'
  },
  {
    _id: '35501',
    city: 'JASPER',
    loc: [ -87.249144, 33.871672 ],
    pop: 30600,
    state: 'AL'
  },
  {
    _id: '35601',
    city: 'DECATUR',
    loc: [ -86.98868, 34.589599 ],
    pop: 36696,
    state: 'AL'
  },
  {
    _id: '35611',
    city: 'ATHENS',
    loc: [ -86.970733, 34.803604 ],
    pop: 35441,
    state: 'AL'
  },
  {
    _id: '35630',
    city: 'FLORENCE',
    loc: [ -87.655985, 34.830547 ],
    pop: 38725,
    state: 'AL'
  },
  {
    _id: '35901',
    city: 'SOUTHSIDE',
    loc: [ -86.010279, 33.997248 ],
    pop: 44165,
    state: 'AL'
  },
  {
    _id: '35810',
    city: 'HUNTSVILLE',
    loc: [ -86.609063, 34.778378 ],
    pop: 32896,
    state: 'AL'
  },
  {
    _id: '36201',
    city: 'ANNISTON',
    loc: [ -85.838152, 33.653896 ],
    pop: 38370,
    state: 'AL'
  },
  {
    _id: '36116',
    city: 'MONTGOMERY',
    loc: [ -86.242056, 32.312943 ],
    pop: 32314,
    state: 'AL'
  },
  {
    _id: '36108',
    city: 'MONTGOMERY',
    loc: [ -86.352904, 32.341682 ],
    pop: 30780,
    state: 'AL'
  },
  {
    _id: '36301',
    city: 'TAYLOR',
    loc: [ -85.418036, 31.202888 ],
    pop: 32689,
    state: 'AL'
  },
  {
    _id: '36303',
    city: 'NAPIER FIELD',
    loc: [ -85.412462, 31.255239 ],
    pop: 32407,
    state: 'AL'
  },
  {
    _id: '36605',
    city: 'MOBILE',
    loc: [ -88.084646, 30.634117 ],
    pop: 31621,
    state: 'AL'
  },
  {
    _id: '36608',
    city: 'MOBILE',
    loc: [ -88.187784, 30.69636 ],
    pop: 37600,
    state: 'AL'
  },
  {
    _id: '36801',
    city: 'OPELIKA',
    loc: [ -85.358629, 32.627771 ],
    pop: 32808,
    state: 'AL'
  }
]
Type "it" for more

c) Working with indexes

Command used:

 db.zips.find({ $and: [{ $or: [{ 'loc.0': { $gt: -86.4 } }, { 'loc.1': { $lt: 40.2 } }] }, { 'pop': { $gt: 29778 } }] }).explain("executionStats")

It’s explain with additional execution stats to make it interesting.

Before optimization

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'test.zips',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [
        {
          '$or': [
            { 'loc.1': { '$lt': 40.2 } },
            { 'loc.0': { '$gt': -86.4 } }
          ]
        },
        { pop: { '$gt': 29778 } }
      ]
    },
    queryHash: '5A3BA56B',
    planCacheKey: '5A3BA56B',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'COLLSCAN',
      filter: {
        '$and': [
          {
            '$or': [
              { 'loc.1': { '$lt': 40.2 } },
              { 'loc.0': { '$gt': -86.4 } }
            ]
          },
          { pop: { '$gt': 29778 } }
        ]
      },
      direction: 'forward'
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1983,
    executionTimeMillis: 73,
    totalKeysExamined: 0,
    totalDocsExamined: 29467,
    executionStages: {
      stage: 'COLLSCAN',
      filter: {
        '$and': [
          {
            '$or': [
              { 'loc.1': { '$lt': 40.2 } },
              { 'loc.0': { '$gt': -86.4 } }
            ]
          },
          { pop: { '$gt': 29778 } }
        ]
      },
      nReturned: 1983,
      executionTimeMillisEstimate: 12,
      works: 29469,
      advanced: 1983,
      needTime: 27485,
      needYield: 0,
      saveState: 29,
      restoreState: 29,
      isEOF: 1,
      direction: 'forward',
      docsExamined: 29467
    }
  },
  command: {
    find: 'zips',
    filter: {
      '$and': [
        {
          '$or': [
            { 'loc.0': { '$gt': -86.4 } },
            { 'loc.1': { '$lt': 40.2 } }
          ]
        },
        { pop: { '$gt': 29778 } }
      ]
    },
    '$db': 'test'
  },
  serverInfo: {
    host: 'sh-HSA-temp',
    port: 27017,
    version: '6.0.3',
    gitVersion: 'f803681c3ae19817d31958965850193de067c516'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1
}

Adding the ‘pop’ index:

test> db.zips.ensureIndex({'pop':1})[ 'pop_1' ]
test> db.zips.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { pop: 1 }, name: 'pop_1' }
]

Another explain after that:

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'test.zips',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [
        {
          '$or': [
            { 'loc.1': { '$lt': 40.2 } },
            { 'loc.0': { '$gt': -86.4 } }
          ]
        },
        { pop: { '$gt': 29778 } }
      ]
    },
    queryHash: '5A3BA56B',
    planCacheKey: '059B66F3',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      filter: {
        '$or': [ { 'loc.0': { '$gt': -86.4 } }, { 'loc.1': { '$lt': 40.2 } } ]
      },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { pop: 1 },
        indexName: 'pop_1',
        isMultiKey: false,
        multiKeyPaths: { pop: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { pop: [ '(29778, inf.0]' ] }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1983,
    executionTimeMillis: 12,
    totalKeysExamined: 2288,
    totalDocsExamined: 2288,
    executionStages: {
      stage: 'FETCH',
      filter: {
        '$or': [ { 'loc.0': { '$gt': -86.4 } }, { 'loc.1': { '$lt': 40.2 } } ]
      },
      nReturned: 1983,
      executionTimeMillisEstimate: 4,
      works: 2289,
      advanced: 1983,
      needTime: 305,
      needYield: 0,
      saveState: 2,
      restoreState: 2,
      isEOF: 1,
      docsExamined: 2288,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 2288,
        executionTimeMillisEstimate: 0,
        works: 2289,
        advanced: 2288,
        needTime: 0,
        needYield: 0,
        saveState: 2,
        restoreState: 2,
        isEOF: 1,
        keyPattern: { pop: 1 },
        indexName: 'pop_1',
        isMultiKey: false,
        multiKeyPaths: { pop: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { pop: [ '(29778, inf.0]' ] },
        keysExamined: 2288,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    }
  },
  command: {
    find: 'zips',
    filter: {
      '$and': [
        {
          '$or': [
            { 'loc.0': { '$gt': -86.4 } },
            { 'loc.1': { '$lt': 40.2 } }
          ]
        },
        { pop: { '$gt': 29778 } }
      ]
    },
    '$db': 'test'
  },
  serverInfo: {
    host: 'sh-HSA-temp',
    port: 27017,
    version: '6.0.3',
    gitVersion: 'f803681c3ae19817d31958965850193de067c516'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1
}

Differences (and explanation of some interesting fields)

18c18
<     planCacheKey: '5A3BA56B',
---
>     planCacheKey: '059B66F3',
23c23
<       stage: 'COLLSCAN',
---
>       stage: 'FETCH',
25,33c25
<         '$and': [
<           {
<             '$or': [
<               { 'loc.1': { '$lt': 40.2 } },
<               { 'loc.0': { '$gt': -86.4 } }
<             ]
<           },
<           { pop: { '$gt': 29778 } }
<         ]
---
>         '$or': [ { 'loc.0': { '$gt': -86.4 } }, { 'loc.1': { '$lt': 40.2 } } ]
35c27,39
<       direction: 'forward'
---
>       inputStage: {
>         stage: 'IXSCAN',
>         keyPattern: { pop: 1 },
>         indexName: 'pop_1',
>         isMultiKey: false,
>         multiKeyPaths: { pop: [] },
>         isUnique: false,
>         isSparse: false,
>         isPartial: false,
>         indexVersion: 2,
>         direction: 'forward',
>         indexBounds: { pop: [ '(29778, inf.0]' ] }
>       }
42,44c46,48
<     executionTimeMillis: 73,
<     totalKeysExamined: 0,
<     totalDocsExamined: 29467,
---
>     executionTimeMillis: 12,
>     totalKeysExamined: 2288,
>     totalDocsExamined: 2288,
46c50
<       stage: 'COLLSCAN',
---
>       stage: 'FETCH',
48,56c52
<         '$and': [
<           {
<             '$or': [
<               { 'loc.1': { '$lt': 40.2 } },
<               { 'loc.0': { '$gt': -86.4 } }
<             ]
<           },
<           { pop: { '$gt': 29778 } }
<         ]
---
>         '$or': [ { 'loc.0': { '$gt': -86.4 } }, { 'loc.1': { '$lt': 40.2 } } ]
59,60c55,56
<       executionTimeMillisEstimate: 12,
<       works: 29469,
---
>       executionTimeMillisEstimate: 4,
>       works: 2289,
62c58
<       needTime: 27485,
---
>       needTime: 305,
64,65c60,61
<       saveState: 29,
<       restoreState: 29,
---
>       saveState: 2,
>       restoreState: 2,
67,68c63,90
<       direction: 'forward',
<       docsExamined: 29467
---
>       docsExamined: 2288,
>       alreadyHasObj: 0,
>       inputStage: {
>         stage: 'IXSCAN',
>         nReturned: 2288,
>         executionTimeMillisEstimate: 0,
>         works: 2289,
>         advanced: 2288,
>         needTime: 0,
>         needYield: 0,
>         saveState: 2,
>         restoreState: 2,
>         isEOF: 1,
>         keyPattern: { pop: 1 },
>         indexName: 'pop_1',
>         isMultiKey: false,
>         multiKeyPaths: { pop: [] },
>         isUnique: false,
>         isSparse: false,
>         isPartial: false,
>         indexVersion: 2,
>         direction: 'forward',
>         indexBounds: { pop: [ '(29778, inf.0]' ] },
>         keysExamined: 2288,
>         seeks: 1,
>         dupsTested: 0,
>         dupsDropped: 0
>       }

Most interesting ones:

  • winningPlan
    • stage:
      • Before: stage: COLLSCAN
      • After: FETCH + inputStage.stage is IXSCAN
      • Explanation:
        • ‘COLLSCAN’ implies the entire collection had to be scanned for this (= not optimized and slower)
        • ‘IXSCAN’ means it didn’t have to scan the entire collection but could use an index instead.
          • We additionally see that the index pop was used for this.
    • filter
      • It then used a more simple filter (no $and condition) on the much smaller number of remaining ones. The $and part of the filter gets taken care of by the index (It gets sorted by population on some level, so we don’t need to look at each individually), so only the lot/lan $or part remains.
  • executionStats:
    • We see in totalDocsExamined (29467 -> 2288) that using the index allowed it to get the documents with a certain population.
    • executionTimeMillis went from 73 to 12 - the execution was much faster

Task 3: Aggregation framework

Preparation



> mongorestore -d test dump/enron/messages.bson
2023-01-15T13:25:54.237+0100	checking for collection data in dump/enron/messages.bson
2023-01-15T13:25:54.244+0100	restoring test.messages from dump/enron/messages.bson
2023-01-15T13:25:55.671+0100	finished restoring test.messages (120477 documents, 0 failures)
2023-01-15T13:25:55.671+0100	120477 document(s) restored successfully. 0 document(s) failed to restore.

Top-3 senders of emails and how much they sent

db.messages.aggregate([{
    $group: {
        _id: '$headers.From',
        total: {
            $sum: 1
        }
    }
}, {
    $sort: {
        total: -1
    }
}, {
    $limit: 3
}])

Result:

[
  { _id: 'matthew.lenhart@enron.com', total: 2027 },
  { _id: 'debra.perlingiere@enron.com', total: 1927 },
  { _id: 'tana.jones@enron.com', total: 1916 }
]

Average number of sent emails per sender

We first group by sender and get total per sender, then the average of that number.

test> db.messages.aggregate([{ $group: { _id: '$headers.From', total: { $sum: 1 } } }, { $group: { _id: "_id", avgNumberPerSender: { $avg: "$total" } } }])
[ { _id: '_id', avgNumberPerSender: 16.074316210807204 } ]

Person who received the second biggest number of emails

Part 1 - we $unwind the “to” field, then we get more emails each with a single address in “to”. Then we group by “to” and sort:

db.messages.aggregate([{
    $unwind: '$headers.To'
}, {
    $project: {
        _id: 'id',
        to: '$headers.To',
        msgid: '$headers.Message-ID'
    }
}, {
    $group: {
        _id: '$to',
        total: {
            $sum: 1
        }
    }
}, {
    $sort: {
        total: -1
    }
}])

[
  { _id: 'jeff.dasovich@enron.com', total: 7093 },
  { _id: 'richard.shapiro@enron.com', total: 6226 },
  { _id: 'steven.kean@enron.com', total: 5034 },
  { _id: 'james.steffes@enron.com', total: 4320 },
  { _id: 'tana.jones@enron.com', total: 4033 },
  { _id: 'sara.shackleton@enron.com', total: 3491 },
  { _id: 'kay.mann@enron.com', total: 3468 },
  { _id: 'susan.mara@enron.com', total: 3342 },
  { _id: 'kate.symes@enron.com', total: 3198 },
  { _id: 'paul.kaufman@enron.com', total: 3170 },
  { _id: 'louise.kitchen@enron.com', total: 3078 },
  { _id: 'harry.kingerski@enron.com', total: 2822 },
  { _id: 'linda.robertson@enron.com', total: 2752 },
  { _id: 'tim.belden@enron.com', total: 2598 },
  { _id: 'alan.comnes@enron.com', total: 2532 },
  { _id: 'gerald.nemec@enron.com', total: 2491 },
  { _id: 'sandra.mccubbin@enron.com', total: 2480 },
  { _id: 'mark.taylor@enron.com', total: 2479 },
  { _id: 'recipients@enron.com', total: 2371 },
  { _id: 'john.lavorato@enron.com', total: 2361 }
]

Part 2 - “second biggest” - we skip the first and limit to 1, leading to the second one.

db.messages.aggregate([{
    $unwind: '$headers.To'
}, {
    $project: {
        _id: 'id',
        to: '$headers.To',
        msgid: '$headers.Message-ID'
    }
}, {
    $group: {
        _id: '$to',
        total: {
            $sum: 1
        }
    }
}, {
    $sort: {
        total: -1
    }
}, {
    $skip: 1
}, {
    $limit: 1
}])

[ { _id: 'richard.shapiro@enron.com', total: 6226 } ]
Nel mezzo del deserto posso dire tutto quello che voglio.
comments powered by Disqus
Nel mezzo del deserto posso dire tutto quello che voglio.