Setup NoSQL - MySQL db

Objectives

I’ll be working this project at times on the cloud and at times on the local machine. We’ll start with these steps:

import data into a MongoDB database.
query data in a MongoDB database.
export data from MongoDB.

Setup Cloud Environment

Create MongoDB Instance

user: root
host: 172.21.76.25
ps: BfAQbcHKljcUh0mPX4LFot4G

New Terminal

Check if mongoimport is installed
Check if mongoexport is installed

~:/home/project$ mongoimport --version
mongoimport version: 100.10.0
git version: 6d4f001be3fcf673de04d20176e90ee02ef233a9
Go version: go1.21.12
   os: linux
   arch: amd64
   compiler: gc
   
~/home/project$ mongoexport --version
mongoexport version: 100.10.0
git version: 6d4f001be3fcf673de04d20176e90ee02ef233a9
Go version: go1.21.12
   os: linux
   arch: amd64
   compiler: gc

Download json File

Download the catalog json file from URL

~:/home/project$ curl -O https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0321EN-SkillsNetwork/nosql/catalog.json
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 37503  100 37503    0     0   313k      0 --:--:-- --:--:-- --:--:--  315k

Import Data into Mongo

Create db: catalog
Create collection: electronics
From file catalog.json

~/home/project$ mongoimport --host 172.21.76.25 -u root -p BfAQbcHKljcUh0mPX4LFot4G --authenticationDatabase admin --db catalog --collection electronics --file catalog.json
2024-11-15T12:08:41.956-0500    connected to: mongodb://172.21.76.25/
2024-11-15T12:08:41.989-0500    438 document(s) imported successfully. 0 document(s) failed to import.

Open MongoDB CLI

List DBs

------
   The server generated these startup warnings when booting
   2024-11-15T12:38:20.498+0000: 
   2024-11-15T12:38:20.498+0000: ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
   2024-11-15T12:38:20.498+0000: **          See http://dochub.mongodb.org/core/prodnotes-filesystem
   
test> show dbs
admin    80.00 KiB
catalag  40.00 KiB
catalog  40.00 KiB
config   96.00 KiB
local    64.00 KiB

List Collections

test> use catalog
switched to db catalog
catalog> show collections
electronics
catalog>

Create Index

Create an index on the field “type”

catalog> db.electronics.createIndex({"type":1})
type_1
catalog>

List Indexes

catalog> db.electronics.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_', ns: 'catalog.electronics' },
  { v: 2, key: { type: 1 }, name: 'type_1', ns: 'catalog.electronics' }
]
catalog>

Query

Count # of Laptops

With type being the column name, find the number of laptops in the collection

catalog> db.electronics.find({type:"laptop"}).count()
389

db.electronics.countDocuments({
        type: { $eq: "laptop"}
})

Count Based on 2 Conditions

Write a query to find the number of smart phones which is in column type
And with screen size of 6 inches which is in column screen size

# Let's break it into two queries one for each condition
catalog> b.electronics.countDocuments({
        "screen size": { $eq: 6}
})
8

catalog> db.electronics.countDocuments({
        type : { $eq: "smart phone"}
})
24

catalog> db.electronics.countDocuments({
        type : { $eq: "smart phone"},
        "screen size": { $eq: 6}        
})
8

catalog> db.electronics.countDocuments({
...         type : { $eq: "smart phone"},
...         "screen size": { $eq: 6}        
... })
8

Avg Screen Size

Query db to find the average screen size of smart phones
Remember: smart phone is from type column
Screen size is from screen size column

catalog> db.electronics.aggregate([
        {$group: {_id:"$type", avg_size: { $avg: "$screen size"}}}
])

[
  { _id: 'television', avg_size: 39.8 },
  { _id: 'laptop', avg_size: 14.568123393316196 },
  { _id: 'smart phone', avg_size: 6 }
]

catalog> db.electronics.aggregate([
        {$match: { "type":"smart phone"}},
        {$group: {_id: null, avg_screen_size: { $avg: "$screen size"}}}
        
# OUTPUT
[ { _id: null, avg_screen_size: 6 }]

Export to CSV

Export the fields _id, type, model from the electronics collection to a file named: electronics.csv

~:/home/project$
mongoexport --host 172.21.76.25 -u root -p BfAQbcHKljcUh0mPX4LFot4G --authenticationDatabase admin --db catalog --collection electronics --type=csv --fields=_id,type,model --out electronics.csv

2024-11-15T14:02:17.645-0500    connected to: mongodb://172.21.76.25/
2024-11-15T14:02:17.659-0500    exported 438 records