MySQL Document Store - How to replace a NoSQL database by MySQL without effort but with a lot gains ?

ROOUG, December 2017 MySQL Document Store How to replace a NoSQL database by MySQL without effort but with a lot of gains ? Frédéric Descamps - MySQL Community Manager - Oracle 3 / 110

Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 4 / 110

Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 11 / 110

Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 12 / 110

Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant transactions Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 13 / 110

Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant transactions SQL Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 14 / 110

Relational Databases Data Integrity normalization constraints (foreign keys, ...) Atomicity, Consistency, Isolation, Durability ACID compliant transactions SQL powerfull query language Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 15 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 19 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 20 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 21 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 22 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 23 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 24 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON close to frontend Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 25 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON close to frontend native in JS Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 26 / 110

NoSQL or Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure embedded arrays or objects valid solution when natural data can't be modelized optimaly into a relational model objects persistance without the use of any ORM - mapping oobject-oriented JSON close to frontend native in JS easy to learn Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 27 / 110

How DBAs see data How Developers see data { "GNP" : 249704, "Name" : "Belgium", "government" : { "GovernmentForm" : "Constitutional Monarchy, Federation", "HeadOfState" : "Philippe I" }, "_id" : "BEL", "IndepYear" : 1830, "demographics" : { "Population" : 10239000, "LifeExpectancy" : 77.8000030517578 }, "geography" : { "Region" : "Western Europe", "SurfaceArea" : 30518, "Continent" : "Europe" } } Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 29 / 110

Relational Model Higher upfront effort during design stage data modeling and normalization physical schema definition Database ensures integrity (invalid data rejected) Data types Constraints (foreign keys etc.) Relational Databases vs Document Store Systems Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 30 / 110

Relational Model Higher upfront effort during design stage data modeling and normalization physical schema definition Database ensures integrity (invalid data rejected) Data types Constraints (foreign keys etc.) Document Model Lower upfront effort data structure defined in application code Application responsible for data integrity possibility for bad data to creep into the system (because of bugs, application changes etc.) Relational Databases vs Document Store Systems Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 31 / 110

Relational Model Steeper learning curve requires knowledge of SQL Query language allows optimizer to execute complex queries efficiently (specially for JOINs) Schema changes can be problematic (ex upgrades) Relational Databases vs Document Store Systems (2) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 32 / 110

Relational Model Steeper learning curve requires knowledge of SQL Query language allows optimizer to execute complex queries efficiently (specially for JOINs) Schema changes can be problematic (ex upgrades) Document Model Easier to get started with Limited or no JOINs, simple optimizer - complexity off-loaded to application Simpler deployment, different versions of the document can coexist Relational Databases vs Document Store Systems (2) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 33 / 110

Developers: [x] schemaless [x] rapid prototying/simpler APIs [x] document model [x] transactions Operations: [x] performance management/visibility [x] robust replication, backup, restore [x] comprehensive tooling ecosystem [x] simpler application schema upgrades Business Owner: [x] don't lose my data == ACID trx [x] capture all my data = extensible/schemaless [x] product on schedule/time to market = rapid developement A solution for all Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 36 / 110

MySQL as Document Store: requirements 1. support of JSON datatype 2. CRUD Operations 3. X-Plugin & X Protocol extends and replaces the traditional MySQL protocol Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 42 / 110

MySQL as Document Store: requirements 1. support of JSON datatype 2. CRUD Operations 3. X-Plugin & X Protocol extends and replaces the traditional MySQL protocol supports asynchronous communication to the server Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 43 / 110

MySQL as Document Store: requirements 1. support of JSON datatype 2. CRUD Operations 3. X-Plugin & X Protocol extends and replaces the traditional MySQL protocol supports asynchronous communication to the server supports multiple API calls like Javascript, Python, SQL... Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 44 / 110

MySQL as Document Store: requirements 1. support of JSON datatype 2. CRUD Operations 3. X-Plugin & X Protocol extends and replaces the traditional MySQL protocol supports asynchronous communication to the server supports multiple API calls like Javascript, Python, SQL... based on Google Protocol Buffers Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 45 / 110

MySQL as Document Store: requirements 1. support of JSON datatype 2. CRUD Operations 3. X-Plugin & X Protocol extends and replaces the traditional MySQL protocol supports asynchronous communication to the server supports multiple API calls like Javascript, Python, SQL... based on Google Protocol Buffers 4. MySQL Shell Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 46 / 110

Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: We need to dump the data to a file and we will use MySQL Shell with the Python interpreter to load the data. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 52 / 110

Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: We need to dump the data to a file and we will use MySQL Shell with the Python interpreter to load the data. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 53 / 110

Dump and load using MySQL Shell & Python this example is inspired by @datacharmer's work: https://www.slideshare.net/datacharmer/mysql-documentstore $ mongo --quiet --eval 'DBQuery.shellBatchSize=30000; db.restaurants. nd().shellPrint()' | perl -pe 's/(?:ObjectId|ISODate)(("[^"]+"))/ $1/g' > all_recs.json Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 54 / 110

Dump and load using MySQL Shell & Python this example is inspired by @datacharmer's work: https://www.slideshare.net/datacharmer/mysql-documentstore $ mongo --quiet --eval 'DBQuery.shellBatchSize=30000; db.restaurants. nd().shellPrint()' | perl -pe 's/(?:ObjectId|ISODate)(("[^"]+"))/ $1/g' > all_recs.json Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 55 / 110

Syntax slightly different than MongoDB > db.restaurants. nd({"cuisine": "French", "borough": { $not: /^Manhattan/} }, {"_id":0, "name": 1,"cuisine": 1, "borough": 1}).limit(2) { "borough" : "Queens", "cuisine" : "French", "name" : "La Baraka Restaurant" } { "borough" : "Queens", "cuisine" : "French", "name" : "Air France Lounge" } Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 63 / 110

8.0.3 what´s new ? WL10446 - Customizable prompts WL10568 - Handling of Ctrl+C has been improved on many scenarios WL10708 and WL10823 - NodeSession disappeared in favor of simply Session WL10912 - Changes on connection options handling: case insensitive: i.e. ssl-mode == Ssl-Mode renamed from camel case to word dashed format: sslMode --> ssl-mode WL11054 - Admin API supports automatic turn of of super-read-only option on several functions. WL10682 - Mysqlx.CRUD.Update on top level document Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 65 / 110

8.0.3 what´s new ? (part 2) WL10645 - X Protocol Crud.Find with row locking WL10734 - Spatial index type WL10744 - Full-Text Index Type WL10797 - Mysqlx.Crud.Update: MERGE_PATCH WL10447 - auto-completion by hitting in the middle of typing a shell command, database object, object function, ... Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 66 / 110

JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 73 / 110

JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH,...) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 74 / 110

JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH,...) Generated/virtual columns Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 75 / 110

JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH,...) Generated/virtual columns Indexing JSON data Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 76 / 110

JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH,...) Generated/virtual columns Indexing JSON data Foreign Keys to JSON data Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 77 / 110

JSON datatype is behind the scene native datatype (since 5.7.8) JSON values are stored in MySQL tables using UTF8MB4 Conversion from "native" SQL types to JSON values JSON manipulation functions (JSON_EXTRACT, JSON_KEYS, JSON_SEARCH,...) Generated/virtual columns Indexing JSON data Foreign Keys to JSON data SQL Views to JSON data Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 78 / 110

Mapping to SQL examples createCollection('mycollection') CREATE TABLE `test`.`mycoll` ( doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS (doc->>'$._id') STORED PRIMARY KEY ) CHARSET utf8mb4; Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 81 / 110

Mapping to SQL examples createCollection('mycollection') CREATE TABLE `test`.`mycoll` ( doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS (doc->>'$._id') STORED PRIMARY KEY ) CHARSET utf8mb4; mycollection.add({'test': 1234}) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 82 / 110

Mapping to SQL examples createCollection('mycollection') CREATE TABLE `test`.`mycoll` ( doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS (doc->>'$._id') STORED PRIMARY KEY ) CHARSET utf8mb4; mycollection.add({'test': 1234}) INSERT INTO `test`.`mycoll` (doc) VALUES ( JSON_OBJECT('_id','663807fe367ee6114e0e5458bdac28bf', 'test',1234) ); Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 83 / 110

Mapping to SQL examples (2) mycollection.find("test > 100") SELECT doc FROM `test`.`mycoll` WHERE (JSON_EXTRACT(doc,'$.test') > 100); Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 85 / 110

schemaless flexible data structure easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Data integrity ACID Compliant SQL Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 109 / 110

MySQL Document Store - How to replace a NoSQL database by MySQL without effort but with a lot gains ?

More Related Content

What's hot

Similar to MySQL Document Store - How to replace a NoSQL database by MySQL without effort but with a lot gains ?

More from Frederic Descamps

Recently uploaded

MySQL Document Store - How to replace a NoSQL database by MySQL without effort but with a lot gains ?