![Page 1: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/1.jpg)
SQL to NoSQL: Top 6 QuestionsGlynn BirdDeveloper Advocate @ IBM
@glynn_bird
![Page 2: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/2.jpg)
Agenda
2
• Top 6 Questions When Moving to NoSQL1. Why NoSQL?2. Rows and Tables Become ... What?3. Will I Have to Rebuild My App?4. How do I query data?5. What's _rev?6. Does it replicate?
• Live Q&A
![Page 3: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/3.jpg)
1. Why NoSQL?
3
![Page 4: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/4.jpg)
But, What Is NoSQL, Really?
4
• Umbrella term for databases using non-SQL query languages• Key-Value stores• Column-family stores• Document stores• Graph stores
• Some also say "non-relational," because data is not decomposed into separate tables, rows, and columns • It’s still possible to represent relationships in NoSQL
• The question is, are these relationships always necessary?
![Page 5: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/5.jpg)
NoSQL Document Stores
5
• That's databases like MongoDB, Apache CouchDB™, Cloudant, and Dynamo
• Optimized for "semi-structured" or "schema-optional" data• People say "unstructured," but that's inaccurate
• Each document has its own structure
![Page 6: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/6.jpg)
6
2.0
multi-node clusteringCloudant Geo Cloudant
Query (Mango)Cloudant Search (Lucene)
Dashboard
![Page 7: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/7.jpg)
Schema Flexibility
7
• Cloudant uses JavaScript Object Notation (JSON) as its data format• Cloudant is based on Apache CouchDB. In both systems, a "database" is simply
a collection of JSON documents{ "docs": [ { "_id": "df8cecd9809662d08eb853989a5ca2f2", "_rev": "1-8522c9a1d9570566d96b7f7171623270", "Movie_runtime": 162, "Movie_rating": "PG-13", "Person_name": "Zoe Saldana", "Actor_actor_id": "0757855", "Movie_genre": "AVYS", "Movie_name": "Avatar", "Actor_movie_id": "0499549", "Movie_earnings_rank": "1", "Person_pob": "New Jersey, USA", "Person_id": "0757855", "Movie_id": "0499549", "Movie_year": 2009, "Person_dob": "1978-06-19" } ]}
![Page 8: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/8.jpg)
The Cloudant Data Layer
8
• Distributed NoSQL data persistence layer
• Available as a fully-managed DBaaS, or managed by you on-premises
• Transactional JSON document database with REST API
• Spreads data across data centers & devices for scale & high availability
• Ideal for apps that require:• Massive, elastic scalability• High availability• Geo-location services• Full-text search• Offline-first design for occasionally
connected users
![Page 9: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/9.jpg)
Not One DB Server; a Cluster of Servers• A Cloudant cluster
• Horizontal scale
• Redundant load balancers backed by multiple DB servers
• Designed for durability• Saves multiple copies of data
• Spreads copies across cluster
• All replicas do reads & writes
• Access Cloudant over the Web• Developers get an API
• Cloudant manages it all behind the scenes
9
![Page 10: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/10.jpg)
Horizontal Scaling
• Shard across many commodity servers vs. few expensive ones• Performance improves linearly with cost, not exponentially
10
![Page 11: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/11.jpg)
2. Rows and Tables Become ... What?
11
![Page 12: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/12.jpg)
... This!
SQL Terms/Conceptsdatabase -->
table -->
row -->
column -->
materialized view -->
primary key -->
table JOIN operations -->
Document Store Terms/Conceptsdatabase
bunch of documents
document
field
index/database view/secondary index
"_id":
entity relations
12
![Page 13: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/13.jpg)
Rows --> Documents
13
• Use some field to group documents by schema• Example:
"type":"user" or "type":"book"
"_id":"user:456" or "_id":"book:9988"
![Page 14: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/14.jpg)
Tables --> Databases
14
• Put all tables in one database; use "type": to distinguish• Model entity relationships with secondary indexes
• http://wiki.apache.org/couchdb/EntityRelationship
![Page 15: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/15.jpg)
3. How do you query NoSQL
15
![Page 16: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/16.jpg)
Indexes and Queries
16
• An "index" in Cloudant is not strictly a performance optimization• Instead, more akin to "materialized view" in RDBMS terms• Index also called a "database view" in Cloudant
• Index, then query• You need one before you can do the other
• Create index, then query by URL• Can create a secondary index on any field within a document• You get primary index (based on reserved "_id": field) by default
• Indexes precomputed, updated in real time• Indexes are updated using incremental MapReduce• You don't need to rebuild the entire index every time a document is changed,
added, or deleted• Performant at big-honkin' scale
![Page 17: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/17.jpg)
One Cloudant DB, Many Indexes
17
The Cloudant API
![Page 18: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/18.jpg)
Cloudant Query
18
curl -X POST 'https://<accountname>.cloudant.com/users/_find' -d'{
"selector": {"age": {
"$gt": 25,"$lte": 50
}}
}'
![Page 19: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/19.jpg)
4. Will I Have to Rebuild My App?
19
![Page 20: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/20.jpg)
Yes
20
By ripping out the bad parts:• Extract, Transform, Load
• Schema migrations
• JOINs that don't scale
![Page 21: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/21.jpg)
Each of My Tables Becomes a Different Type of JSON Document?
21
![Page 22: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/22.jpg)
No• Fancy explanation:
• Best practice is to denormalize data into 3rd normal form
• Or, less fancy:• Smoosh relationships for each
entry all together into one JSON doc
• Denormalization• Approach to data modeling that
shards well and scales well
• Works well with data that is somewhat static, or infrequently updated
22
A smooshed and griddled cheese sandwich
![Page 23: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/23.jpg)
23
Example{ "_id": "[email protected]", "_rev": "12-89e6128fb2d3e2e14559e796b6a71c9d", "name": "John Smith", "title": "Technical Sales Manager", "products": [ "Cloudant", "Information Server"], "languages": [ "English" ], "geolocation": { "coordinates": [ -122.18258, 37.880058 ], "type": "point" }, "address": { "street": "63 Citron knoll", "city": "Orinda", "state": "CA", "country": "USA" }}
![Page 24: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/24.jpg)
5. Does it replicate?
24
{ "_id": "[email protected]", "_rev": "12-89e6128fb2d3e2e14559e796b6a71c9d", "name": "John Smith", "title": "Technical Sales Manager", "products": [ "Cloudant", "Information Server",], "languages": [ "English" ], "geolocation": { "coordinates": [ -122.18258, 37.880058 ], "type": "point" }, "address": { "street": "63 Citron knoll", "city": "Orinda", "state": "CA", "country": "USA" }}
![Page 25: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/25.jpg)
Replication targets
25
• Apache CouchDB• IBM Cloudant• PouchDB (client & server)• Cloudant Sync Libraries
![Page 26: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/26.jpg)
26
www.glynnbird.com
• My home page
• Cloudant database of articles
• Replicated to PouchDB
• Appcache for offline first
• http://www.glynnbird.com/
26
![Page 27: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/27.jpg)
6. How do I get data in and out?
27
• Yes• https://cloudant.com/for-developers/migrating-data/
• But every use case is different and everyone’s data is different
• Lots of DIY tools on github that could work for you
• Cloudant’s Homegrown CSV --> JSON Tools• python: https://github.com/claudiusli/csv-import
• Java: https://github.com/cavanaugh-ibm/db-data-loader
• Node: https://github.com/glynnbird/couchimport
![Page 28: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/28.jpg)
Simple Data Pipe
28
• https://github.com/ibm-cds-labs/pipes
![Page 29: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/29.jpg)
Simple Search Service
29
https://developer.ibm.com/clouddataservices/simple-search-service/
![Page 30: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/30.jpg)
Glynn BirdDeveloper Advocate, Cloud Data [email protected]@glynn_birdgithub.com/glynnbird
![Page 31: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/31.jpg)
Legal Slide #1
31
© "Apache", "CouchDB", "Apache CouchDB", "Apache Lucene," "Lucene", and the CouchDB logo are trademarks or registered trademarks of The Apache Software Foundation. All other brands and trademarks are the property of their respective owners.
![Page 32: SQL To NoSQL - Top 6 Questions Before Making The Move](https://reader035.vdocuments.us/reader035/viewer/2022081605/5871d0f21a28ab423c8b5a93/html5/thumbnails/32.jpg)
Legal Slide #2
32
© Copyright IBM Corporation 2016
IBM and the IBM Cloudant logo are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at ibm.com/legal/copytrade.shtml