7CCSMBDT – Big Data Technologies Practical
MongoDB
The practical commands are to be executed in the terminal or mongoDB shell (NOT inside Cloudera Quickstart VM).
Preparing the mongodb installation
Open one terminal (in the lab’s pc, not inside Cloudera) and execute the following commands:
mkdir mongodb; cd mongodb mkdir -p data/db
mongod –dbpath=./data/db
WAIT for “waiting for connections on port 27017”.
Keep this terminal open throughout the lab. Only at the end of the lab press CTRL_C to
close this terminal.
A. Dataset download and import
* Download the dataset primer-dataset.json from KEATS. Alternatively, download it from https://www.dropbox.com/s/3dkm4hk0fqtsfzd/primer-dataset.json?dl=0
* In a terminal, execute
mongoimport –db test –collection restaurants –drop –file ~/downloads/primer-dataset.json
Here the command assumes that you have downloaded primer-dataset.json in the directory ~/downloads. Replace it with the directory in which you saved primer- dataset.json , if you have saved the dataset in a different directory.
The mongoimport connects to a mongod instance running on localhost on port number 27017. The –file option provides the path to the data to import
7CCSMBDT – Big Data Technologies Practical
You must see output like that: 2018-02-19T07:30:16.282-0800 2018-02-19T07:30:16.283-0800 2018-02-19T07:30:17.462-0800
B. Starting the mongo shell
1) Open a new terminal and type
mongo
You should see
connected to: localhost dropping: test.restaurants imported 25359 documents
MongoDB shell version v.3.6.2
connecting to: mongodb://127.0.0.1:27017 MongoDB server version: 3.6.2
[SOME WARNINGS you should ignore]
>
The “>” is the command prompt of the mongo shell you should see. 2) Execute
show dbs
You must see a database called test
3) Execute
use test
You must see: switched to db test 4) Execute
show collections
You must see a collections called restaurants 5) Execute
db.restaurants.find()
This will show all documents in the collection restaurants.
7CCSMBDT – Big Data Technologies Practical
We will explore find() later. 6) Execute
cls
This will clear the screen.
7) Execute the following, to insert a new document
db.restaurants.insert( {
“address” : {
“street” : “2 Avenue”,
“zipcode” : “10075”,
“building” : “1480”,
“coord” : [ -73.9557413, 40.7720266 ]
},
“borough” : “Manhattan”, “cuisine” : “Italian”, “grades” : [
{
“date” : ISODate(“2014-10-01T00:00:00Z”), “grade” : “A”,
“score” : 11
}, {
} ],
“name” : “Vella”,
“restaurant_id” : “41704620” }
)
You must see the contents of the document that was inserted after … e.g.,
… “name” : “Vella”,
… “restaurant_id” : “41704620”
… } … )
“date” : ISODate(“2014-01-16T00:00:00Z”), “grade” : “B”,
“score” : 17
7CCSMBDT – Big Data Technologies Practical
and then
WriteResult({ “nInserted” : 1 })
Since we have not specified an _id for the document that we have inserted above,
the mongo shell automatically adds the field to the document and sets the field’s value to a generated ObjectId.
FIND
1) Read the text for the find() command:
https://docs.mongodb.com/manual/reference/method/db.collection.find/#db.collection.find
To see the “_id”, along with all other fields of the document we inserted before, execute:
db.restaurants.find( { “restaurant_id”: “41704620” } )
In this case, the find command had as argument a top-level field (i.e., restaurant_id) and one value (41704620). This command finds all information about the restaurant with the desired restaurant_id.
We could omit the quotes, from the field name but not from the value. To see this, verify that the following yields the same result:
db.restaurants.find( { restaurant_id: “41704620” } )
Notice that there are two restaurants called “Vella”. All fields except “_id” of these restaurants are the same. One of these restaurants was inserted by the insert command above, and the other was contained in the restaurants collection that we imported with mongoimport
2) Notice from the results of find() that address is an embedded document (i.e., it is contained inside the main restaurant document, and it has its own fields street, zipcode etc.). To query a field in an embedded document, we need to use the dot notation, which requires quotes around the whole dotted field name.
Execute
db.restaurants.find( { “address.street” : “2 Avenue”} )
You will see many documents (ignore the message:Type “it” for more).
7CCSMBDT – Big Data Technologies Practical
3) Read about the count() command
https://docs.mongodb.com/manual/reference/method/db.collection.count/
which we can use to count documents satisfying certain criteria. Write two different commands for counting the restaurants whose address has “zipcode” equal to “10075”.
4) We can impose additional restrictions to our query, by asking for the logical “AND” of two conditions.
Execute
db.restaurants.find({“address.zipcode”:”10005″,cuisine:”Italian”})
This finds all information about restaurants with zipcode 10005 and Italian cuisine.
Now read about the AND operator
https://docs.mongodb.com/manual/reference/operator/query/and/#op._S_and
and write a command that uses this operator and has the same output as db.restaurants.find({“address.zipcode”:”10005″,cuisine:”Italian”})
5) We can also ask using the logical “OR”.
https://docs.mongodb.com/manual/reference/operator/query/or/#op._S_or
Execute
db.restaurants.find( { $or: [ { “cuisine”: “Italian” }, { “address.zipcode”: “10005” } ] } )
to obtain the restaurants with Italian cuisine or address.zipcode=10005. Read about the comparison operator $ne
https://docs.mongodb.com/manual/reference/operator/query/#query-selectors
Write a query to retrieve the restaurants with italian cuisine, zipcode 10005 and whose address building is NOT 26.
6) To sort the results, the sort() method is applied (“appended”) to the result of a query. The method sort() contains the field(s) to sort by and the corresponding sort type, e.g. 1 for ascending and -1 for descending. Read about sort() at https://docs.mongodb.com/manual/reference/method/cursor.sort/
7CCSMBDT – Big Data Technologies Practical
To get the restaurants returned by find in descending order with respect to name, execute: db.restaurants.find({“address.zipcode”:”10005″,cuisine:”Italian”}).sort({“name”:-1})
7) To project on some of the fields, we use an additional clause {} within find().
To obtain only the name and address coordinates of the restaurants in the last query, execute:
db.restaurants.find({“address.zipcode”:”10005″,cuisine:”Italian”},{name:1,”address.coord”: 1}).sort({“name”:-1})
Read the sections: “Specify the Fields to Return” and “Explicitly Excluded Fields” from
https://docs.mongodb.com/manual/reference/method/db.collection.find/
to learn more about projection.
8) To limit the results to a specified number n, the command limit(n), is appended to a query result.
Execute:
db.restaurants.find({“address.zipcode”:”10005″,cuisine:”Italian”},{name:1,”address.coord”: 1}).sort({“name”:-1}).limit(2)
to get the first two restaurants from those obtained above.
FIND WITH QUERY SELECTORS
Read the query selectors section from
https://docs.mongodb.com/manual/reference/operator/query/
1) Write a query to find all information about the restaurants that achieved a score more than 80. Since score is an element in an array, use the $elemMatch selector.
Since we are looking for a score more than 80, use the $gt selector.
In your result, each restaurant needs to have at least one score more than 80
(i.e., among multiple scores, some may be below 80).
2) Modify the last query to project only on the scores of the restaurants.
7CCSMBDT – Big Data Technologies Practical
3) Write a query to find all information about the restaurants which locate in latitude value less than -95.754168
Hint: Use the selector $lt
4) Write a query to find all information about the restaurants which have Italian cuisine and score greater than or equal to 80 and latitude less than -65
5) Write a query to find the names of the restaurants which do NOT have Italian cuisine but have score greater than or equal to 80 and latitude less than -65
6) Write a query to find the name and restaurant_id of the restaurants whose 2nd element of grades array contains a grade “A” and score greater than 10 on an ISODate “2014-08- 11”
Hint:
Read https://docs.mongodb.com/manual/tutorial/query-arrays/
and https://docs.mongodb.com/manual/tutorial/query-array-of-documents/
7) Write a query to find the name and borough of restaurants whose name starts with “Mad”. Your search must be case-insensitive.
HINT: See https://docs.mongodb.com/manual/reference/operator/query/regex/#syntax-restrictions
REMOVE
Read the syntax of the remove() method
https://docs.mongodb.com/manual/reference/method/db.collection.remove/
1) Write a query that removes the restaurants with name Caffe Reggio and
borough Manhattan
You must see WriteResult({ “nRemoved” : 1 }) after a successful remove operation.
7CCSMBDT – Big Data Technologies Practical
UPDATE
Read the documentation for the update command, paying special attention to the “multi” and “upsert” parameters https://docs.mongodb.com/manual/reference/method/db.collection.update/#db.collection. update
Read about the $set operator
https://docs.mongodb.com/manual/reference/operator/update/set/#up._S_set
1) Write a query to find the name and restaurant_id of all restaurants whose name starts with “Junior”. Your search must be case-insensitive.
2) Change the restaurant_id of the restaurant “Juniors” to restaurant_id 40719736 to 10 You must see WriteResult({ “nMatched” : 1, “nUpserted” : 0, “nModified” : 1 }) after a successful update operation.
3) Execute again the query that finds the name and restaurant_id of all restaurants whose name starts with “Junior” to see the change.
4) Change the restaurant_id of all the restaurants whose name is “Juniors” to restaurant_id 11
You must see WriteResult({ “nMatched” : 3, “nUpserted” : 0, “nModified” : 3 }) HINT: Use the multi parameter.
5) Read in the documentation of update (Section Upsert behavior in
https://docs.mongodb.com/manual/reference/method/db.collection.update/#db.collection. update
) what happens if upsert is true and there is no matching document
6) Update the restaurant_id of a restaurant with name Juniors_new to 12, using upsert:true
You must see WriteResult({
“nMatched” : 0,
“nUpserted” : 1,
“nModified” : 0,
“_id” : ObjectId(“586bf4e452d130e807d0afac”)
})
Hint: See the upsert parameter
7CCSMBDT – Big Data Technologies Practical
AGGREGATE
Read the documentation for Aggregate
https://docs.mongodb.com/getting-started/shell/aggregation/
1) Write a query to display the number of restaurants in each borough, along with the name of the borough
Hint: use aggregate $group and $count
2) Write a query to display the number of restaurants in each zipcode, along with the zipcode
INDEXES and EXECUTION PLANS
Read about the explain command
https://docs.mongodb.com/v3.2/reference/explain-results/#executionstats
Pay attention to executionStats section.
1) Execute
db.restaurants.find({“grades.score”:{$gt:90}},{“grades.score”:1}).explain(“executionStats”)
What is the output of explain.executionStats ?
2) Read about indexes https://docs.mongodb.com/manual/core/index-single/ Pay attention to the section “Create an Index on an Embedded Field”
Create an index on grades.score 3) Execute
db.restaurants.getIndexes()
to verify that you have created the index. 4) Exedcute again:
db.restaurants.find({“grades.score”:{$gt:90}},{“grades.score”:1}).explain(“executionStats”)
What is the output of explain.executionStats? Why?