MDB.LOCAL19 - Chicago: From SQL to NoSQL -- Changing Your Mindset

by Joe Karlsson

Slide 1

Slide 1 text

#MDBlocal [Jumpstart] From SQL to NoSQL: Changing your Mindset @JoeKarlsson1 Joe Karlsson

Slide 2

Slide 2 text

Did you know that the two main bottlenecks for web app performance are: @JoeKarlsson1 Source: https://medium.com/@addyosmani/the-cost-of-javascript-in-2018-7d8950fbb5d4 * Network speed * Database execution speed

Slide 3

Slide 3 text

We can only control one of these things @JoeKarlsson1 Source: https://medium.com/@addyosmani/the-cost-of-javascript-in-2018-7d8950fbb5d4

Slide 4

Slide 4 text

@JoeKarlsson1

Slide 5

Slide 5 text

{ name: “Joe Karlsson”, company: “MongoDB”, pronouns: “He/They”, title: [ “Developer Advocate”, “Software Engineer” ], twitter: “@JoeKarlsson1”, website: “joekarlsson.com”, }

Slide 6

Slide 6 text

@JoeKarlsson1 MagicMirror^2 @JoeKarlsson1

Slide 7

Slide 7 text

@JoeKarlsson1 Moodlite @JoeKarlsson1

Slide 8

Slide 8 text

@JoeKarlsson1 Want to watch me make stuff? https://www.twitch.tv/joe_karlsson @JoeKarlsson1

Slide 9

Slide 9 text

Before we get started… @JoeKarlsson1

Slide 10

Slide 10 text

Thank you so much for coming! @JoeKarlsson1

Slide 11

Slide 11 text

Who’s this talk for? @JoeKarlsson1

Slide 12

Slide 12 text

@JoeKarlsson1

Slide 13

Slide 13 text

How’s this talk going to work? @JoeKarlsson1

Slide 14

Slide 14 text

Feel free to ask questions at anytime @JoeKarlsson1

Slide 15

Slide 15 text

Want to follow along? @JoeKarlsson1 http://bit.ly/FromSQLtoNoSQLChicago

Slide 16

Slide 16 text

Get the slides/ resources on my Twitter after the talk: @JoeKarlsson1 @JoeKarlsson1

Slide 17

Slide 17 text

Quick Audience Poll @JoeKarlsson1

Slide 18

Slide 18 text

@JoeKarlsson1

Slide 19

Slide 19 text

Agenda 1.Map terms & concepts from SQL to MongoDB 2.Discover the 4 humongous advantages of MongoDB 3.Change your mindset in 3 key ways @JoeKarlsson1

Slide 20

Slide 20 text

Agenda 1.Map terms & concepts from SQL to MongoDB 2.Discover the 4 humongous advantages of MongoDB 3.Change your mindset in 3 key ways @JoeKarlsson1 Row Column Table Database Index Join Join Left Outer Join Recursive Common Table Expressions View Transaction Document Field Collection Database Index Embedding Database References $lookup $graphLookup View Transaction

Slide 21

Slide 21 text

@JoeKarlsson1 MongoDB stores data in documents

Slide 22

Slide 22 text

@JoeKarlsson1 MongoDB stores data in documents { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

@JoeKarlsson1 Modeling data in MongoDB vs SQL { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } ID first_name surname cell city location_x location_y 1 Paul Miller 447557505611 London 45.123 47.232 Users

Slide 32

Slide 32 text

@JoeKarlsson1 Modeling data in MongoDB vs SQL { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } ID user_id profession 10 1 banking 11 1 finance 12 1 trader Professions ID first_name surname cell city location_x location_y 1 Paul Miller 447557505611 London 45.123 47.232 Users

Slide 33

Slide 33 text

@JoeKarlsson1 Modeling data in MongoDB vs SQL { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } ID user_id model year 20 1 Bentley 1973 21 1 Rolls Royce 1965 Cars ID user_id profession 10 1 banking 11 1 finance 12 1 trader Professions ID first_name surname cell city location_x location_y 1 Paul Miller 447557505611 London 45.123 47.232 Users

Slide 34

Slide 34 text

@JoeKarlsson1 Modeling data in MongoDB vs SQL { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } ID user_id model year 20 1 Bentley 1973 21 1 Rolls Royce 1965 Cars ID user_id profession 10 1 banking 11 1 finance 12 1 trader Professions ID first_name surname cell city location_x location_y 1 Paul Miller 447557505611 London 45.123 47.232 Users

Slide 35

Slide 35 text

@JoeKarlsson1 Collections vs Tables { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", } Users Users ID first_name surname cell city location_ x location_y 1 Paul Miller 447557505611 London 45.123 47.232 2 Joe Karlsson 1235552222 Minneapolis NULL NULL 3 Sydney Schaefer NULL Minneapolis NULL NULL

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Don’t panic! Use schema validation. Flexible schema  database

Slide 39

Slide 39 text

Document Row(s) ID a ... 1 b ... 2 ... ... 3 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... { ...  a: “b”  ... }

Slide 40

Slide 40 text

Field ID a ... 1 b ... 2 c ... 3 ... ... { ...  a: “b”  ... } { ...  a: “c”  ... } Column

Slide 41

Slide 41 text

Table Collection { ... } ... ... ... ... ... ... ... ... ... ... ... ... { ... } { ... }

Slide 42

Slide 42 text

@JoeKarlsson1 Time for a terrible joke

Slide 43

Slide 43 text

@JoeKarlsson1 So a DBA walks  into a NoSQL bar… But immediately leaves because they couldn’t find table.

Slide 44

Slide 44 text

@JoeKarlsson1

Slide 45

Slide 45 text

Database Database ... ... ... ... ... ... ... ... ... ... ... ... { ... } { ... } { ... } { ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

Slide 46

Slide 46 text

Index Index { ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

Slide 47

Slide 47 text

View View { ... } ... ... ... ... ... ... ... ... ... ... ... ... { ... } { ... }

Slide 48

Slide 48 text

Embedding { ...  a: “b”,  ... c: {  d: “e”  ...  },  ... } ID a ... 1 b ... 2 ... ... 3 ... ... ... d ... 1 e ... ... ... ... Join

Slide 49

Slide 49 text

Multi-Record ACID Transaction Multi-Document ACID Transaction { ... } { ... } { ... } { ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

Slide 50

Slide 50 text

@JoeKarlsson1 The journey from SQL to MongoDB

Slide 51

Slide 51 text

@JoeKarlsson1 Row Column Table Database Index Join Join Left Outer Join Recursive Common Table Expressions View Transaction Document Field Collection Database Index Embedding Database References $lookup $graphLookup View Transaction The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB

Slide 52

Slide 52 text

Agenda 1.Map terms & concepts from SQL to MongoDB 2.Discover the 4 humongous advantages of MongoDB 3.Change your mindset in 3 key ways @JoeKarlsson1 1.Program faster 2.Pivot easier 3.Query faster 4.Scale Cheaper

Slide 53

Slide 53 text

@JoeKarlsson1 Program faster! Update Your Profile Documents map to data structures in most popular languages.

Slide 54

Slide 54 text

@JoeKarlsson1 Program faster! { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], } ID first_name surname cell city location_x location_y 1 Paul Miller 447557505611 London 45.123 47.232 Users ID user_id profession 10 1 banking 11 1 finance 12 1 trader Professions

Slide 55

Slide 55 text

import pymongo  from pymongo import MongoClient # CONNECT TO THE DB client = MongoClient() client =  pymongo.MongoClient("mongodb+srv://  username:password@cluster0nsdia.  mongodb.net/test?retryWrites=true") db = client.fabapp   # THE ID OF THE USER WHOSE PROFILE WE WILL BE RETRIEVING AND UPDATING userId = 1 Program faster! import mysql.connector   # CONNECT TO THE DB mydb = mysql.connector.connect(  host="localhost",  user=”username",  passwd=”password",  database=”fabapp”  ) mycursor = mydb.cursor()   # THE ID OF THE USER WHOSE PROFILE WE WILL BE RETRIEVING AND UPDATING userId = 1

Slide 56

Slide 56 text

# GET THE USER'S PROFILE INFORMATION ### Pull the info from the Users table & put it in the user dictionary sql = "Select * FROM Users WHERE Users.ID=%s”  values = (userId,)  mycursor.execute(sql, values)  result = mycursor.fetchone() user = {  "first_name": result[1],  "surname": result[2],  "cell": result[3],  "city": result[4],  "location_x": result[5],  "location_y": result[6]  } # GET THE USER'S PROFILE INFORMATION ## We can pull all of the info from the same document since we used embedding user = db['Users'].find_one({"_id": userId}) Program faster! ### Pull the info from the Professions table & put it in the user dictionary sql = "Select * FROM Professions WHERE  Professions.user_id=%s”  values = (userId,)  mycursor.execute(sql, values)  results = mycursor.fetchall() professions = []  for result in results:  professions.append(result[2])  user["professions"] = professions

Slide 57

Slide 57 text

# UPDATE THE USER DICTIONARY BASED ON USER INPUT IN THE APP ### We'll update the user dictionary manually for simplicity user = {  "first_name": "NewFirst",  "surname": "NewSurname",  "cell": "123-456-7890",  "city": "NewCity",  "location": [40.762, -73.979],  "professions": [”Manager", "Engineer"]  } Program faster! # UPDATE THE USER DICTIONARY BASED ON USER INPUT IN THE APP ### We'll update the user dictionary manually for simplicity user = {  "first_name": "NewFirst",  "surname": "NewSurname",  "cell": "123-456-7890",  "city": "NewCity",  "location_x": 40.762,   "location_y": 73.979,  "professions": [”Manager", "Engineer"]  }

Slide 58

Slide 58 text

# UPDATE THE USER'S PROFILE IN THE DATABASE ### First update what is stored in the Users table sql = "UPDATE Users SET first_name=%s, surname=%s, cell=%s, city=%s, location_x=%s, location_y=%s WHERE (ID=%s)"  values = (  user["first_name"],   user["surname"],   user["cell"],   user["city"],   user["location_x"],   user["location_y"],   userId)  mycursor.execute(sql, values) mydb.commit() # UPDATE THE USER'S PROFILE IN THE DATABASE ### Since the user's data is stored in a single document, we only have to make one update result = db['Users'].update_one(  {"_id": userId}, {"$set": user})   Program faster! ### Delete existing records in Professions table and add new ones sql = "DELETE FROM Professions WHERE user_id=%s”  values = (userId,)  mycursor.execute(sql, values)  mydb.commit() if(len(user["professions"]) > 0):  sql = "INSERT INTO Professions   (user_id,profession) VALUES (%s, %s)”  values = []  for profession in user["professions"]:  values.append((userId, profession))  mycursor.executemany(sql,values)  mydb.commit()

Slide 59

Slide 59 text

30 lines of code Program faster! 73 lines of code

Slide 60

Slide 60 text

@JoeKarlsson1 Pivot easier! Easily change the shape of your data as your app evolves. SQL MongoDB {  a: “b”,  c: “one”,  e: “f” } {  a: “b”,  c: 1,  new: “no biggee” } ALTER TABLE `mydb`.`letters_table` DROP COLUMN `e`, ADD COLUMN `New` VARCHAR(45) NULL AFTER `C`, CHANGE COLUMN `C` `C` INT NULL DEFAULT NULL ;

Slide 61

Slide 61 text

@JoeKarlsson1 Query faster! Stop doing expensive joins to get your data. SQL MongoDB ID a ... 1 b ... 2 ... ... 3 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... { ...  a: “b”,  ... c: {  d: “e”  ...  },  ... }

Slide 62

Slide 62 text

@JoeKarlsson1 Scale cheaper! As the size of your server grows, scale horizontally.

Slide 63

Slide 63 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB

Slide 64

Slide 64 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 1. Program faster # UPDATE THE USER'S PROFILE IN THE DATABASE ### First update what is stored in the Users table sql = "UPDATE Users SET first_name=%s, surname=%s, cell=%s, city=%s, location_x=%s, location_y=%s WHERE (ID=%s)"  values = (  user["first_name"],   user["surname"],   user["cell"],   user["city"],   user["location_x"],   user["location_y"],   userId)  mycursor.execute(sql, values) mydb.commit() # UPDATE THE USER'S PROFILE IN THE DATABASE ### Since the user's data is stored in a single document, we only have to make one update result = db['Users'].update_one(  {"_id": userId}, {"$set": user})  

Slide 65

Slide 65 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 2. Scale cheaper 1. Program faster

Slide 66

Slide 66 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 3. Query faster 2. Scale cheaper 1. Program faster

Slide 67

Slide 67 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 4. Pivot easier 3. Query faster 2. Scale cheaper 1. Program faster

Slide 68

Slide 68 text

Agenda 1.Map terms & concepts from SQL to MongoDB 2.Discover the 4 humongous advantages of MongoDB 3.Change your mindset in 3 key ways @JoeKarlsson1 1.Not all documents in a collection need to have the same fields. 2.Data that is accessed together should be stored together. 3.Stop using transactions regularly.

Slide 69

Slide 69 text

@JoeKarlsson1 Not all documents in a collection need to have the same fields.

Slide 70

Slide 70 text

@JoeKarlsson1 Not all documents in a collection need to have the same fields. The Polymorphic Pattern { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", }

Slide 71

Slide 71 text

@JoeKarlsson1 Not all documents in a collection need to have the same fields. The Outlier Pattern { _id: ”JoeKarlsson1", displayName: ”JoeKarlsson1”,   numFollowers: 2095 followers: [  “jessicaewest”,  “wesley83”,   “GraceFr”  ... ] } { _id: ”KimKardashian", displayName: ”Kim Kardashian West",   numFollowers: 62104245 followers: [  “c_hotaling”,  “IAmJerdog”,   “ChloeCondon”  ... ], has_extras: true { _id: ”KimKardashian_1", twitter_id: “KimKardashian”,  is_overflow: true, followers: [  “StephenAtHome”,  “TheEllenShow”,   “hulu”  ... ] }

Slide 72

Slide 72 text

@JoeKarlsson1 Data that is accessed together should be stored together.

Slide 73

Slide 73 text

@JoeKarlsson1 Storage vs Developer Costs 0 25000 50000 75000 100000 1985 2017 Storage Cost per GB Developer Salary Data that is accessed together should be stored together.

Slide 74

Slide 74 text

@JoeKarlsson1 Data that is accessed together should be stored together. Don’t normalize your data for the sake of normalizing it. {  a: “b”, c: {  d: “e”  ...  }, f: [“g”, “h”, “i”], j: [   { k: “l” },   { m: “n” } ] }

Slide 75

Slide 75 text

@JoeKarlsson1 Stop using transactions regularly. Relying on transactions is a bad design smell. { ... } { ... } { ... } { ... } { ... } { ... } { ... }

Slide 76

Slide 76 text

@JoeKarlsson1 Stop using transactions regularly. Relying on transactions is a bad design smell. { ... } { ... } { ... } { ... } { ... } { ... } { ... }

Slide 77

Slide 77 text

@JoeKarlsson1 The journey from SQL to MongoDB

Slide 78

Slide 78 text

Slide 79

Slide 79 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB

Slide 80

Slide 80 text

Slide 81

Slide 81 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 2. Scale cheaper 1. Program faster

Slide 82

Slide 82 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 3. Query faster 2. Scale cheaper 1. Program faster

Slide 83

Slide 83 text

Slide 84

Slide 84 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 3) Change your mindset in 3 key ways

Slide 85

Slide 85 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 3) Change your mindset in 3 key ways 1. Not all documents in a collection need to have the same fields. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", }

Slide 86

Slide 86 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 3) Change your mindset in 3 key ways 1. Not all documents in a collection need to have the same fields. 2. Data that is accessed together should be stored together. {  a: “b”, c: {  d: “e”  ...  }, f: [“g”, “h”, “i”], j: [   { k: “l” },   { m: “n” } ] }

Slide 87

Slide 87 text

@JoeKarlsson1 The journey from SQL to MongoDB 1) Map terms & concepts from SQL to MongoDB 2) Discover the 4 humongous advantages of MongoDB 3) Change your mindset in 3 key ways 1. Not all documents in a collection need to have the same fields. 2. Data that is accessed together should be stored together. 3. Stop using transactions regularly. { ... } { ... } { ... } { ... } { ... } { ... } { ... }

Slide 88

Slide 88 text

What’s next? @JoeKarlsson1

Slide 89

Slide 89 text

MongoDB Community • MongoDB University: • university.mongodb.com  • Recommended Course: • M001: MongoDB Basics @JoeKarlsson1

Slide 90

Slide 90 text

http://bit.ly/MongoDBChicago2019

Slide 91

Slide 91 text

Recommended Sessions: • A MongoDB Journey: Moving from a Relational Database to MongoDB Atlas [Northwestern Mutual] • 9:30am - 10:15am: Ballroom 2 • Tutorial: Practical Data Modeling for MongoDB • 12:30pm - 2:15pm: Ballroom 3 • Tips and Tricks++ for Querying and Indexing MongoDB [MongoDB] • 2:35pm - 3:20pm: Ballroom 5 @JoeKarlsson1

Slide 92

Slide 92 text

@JoeKarlsson1 Thank you!

Slide 93

Slide 93 text

{ name: “Joe Karlsson”, company: “MongoDB”, title: [ “Developer Advocate”, “Software Engineer” ], twitter: “@JoeKarlsson1”, website: “joekarlsson.com”, }

Slide 94

Slide 94 text

#MDBlocal [Jumpstart] From SQL to NoSQL - Changing your Mindset https://www.surveymonkey.com/r/6NS88JY Every session you rate enters you into a drawing for a $250 Visa gift card, sponsored by

Slide 95

Slide 95 text

Additional Resources •http://bit.ly/MongoDBChicago2019 •$200 in free Atlas credits • Advanced Schema Design Patterns (webinar) • Building with Patterns: A Summary (blog series) • M320: Data Modeling (MongoDB University Course) @JoeKarlsson1

Slide 96

Slide 96 text

Additional Resources @JoeKarlsson1 • The MongoDB Docs • JSON Schema Validation – Locking down your model the smart way • JSON Schema Validation - Checking Your Arrays • M121: The MongoDB Aggregation Framework

Slide 97

Slide 97 text

No content