Oracle Code Roma: NoSQL + SQL = MySQL

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 3 / 100

Use Objects / Documents Developers want just to use objects that´s why they usually love what DBAs hate the most ORMs !). They want to deal with these objects easily (CRUD operations) and they don´t want to think about schema design (slows down the initial development process). Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 12 / 100

Use Objects / Documents Developers want just to use objects that´s why they usually love what DBAs hate the most ORMs !). They want to deal with these objects easily (CRUD operations) and they don´t want to think about schema design (slows down the initial development process). But they also want to keep their data safe and use transactions. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 13 / 100

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

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

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

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

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

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

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

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 28 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 29 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 30 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 31 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 32 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 33 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 34 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 36 / 100

What if there was a way to provide both SQL and NoSQL on one stable platform that has proven stability on well know technology with a large Community and a diverse ecosystem ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 37 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 42 / 100

Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema flexible JSON Document Store No SQL required No need to define all possible attributes, tables, etc. Uses new X DevAPI Can leverage generated column to extract JSON values into materialized columns that can be indexed for fast SQL searches. Document can be ~1GB It's a column in a row of a table It cannot exceed max_allowed_packet Allows use of modern programming styles No more embedded strings of SQL in your code Easy to read Also works with relational Tables Proven MySQL Technology Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 44 / 100

X DevAPI We provide connectors for C++, Java, .Net, Node.js, Python, PHP working with Communities to help them supporting it too New MySQL Shell Command Completion Python, JavaScrips & SQL modes Admin functions New Util object A new high-level session concept that can scale from single MySQL Server to a multiple server environment Non-blocking, asynchronous calls follow common language patterns Supports CRUD operations Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 46 / 100

PY JS SQL > MySQL 8.0 Upgrade Checker Auto Completion & Command History Output Formats (table, json, tabs) Prompt Themes Batch Execution 5.7 8.0 JS Document Store X dev API SQL CLI InnoDB Cluster importJSON JSJS Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 47 / 100

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 @ 2019 Oracle and/or its affiliates. All rights reserved. 59 / 100

And for developers ? $session = mysql_xdevapigetSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection-> nd($search)->execute()->fetchAll(); ... foreach ($results as $doc) { echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 61 / 100

And for developers ? $session = mysql_xdevapigetSession("mysqlx://fred:MyP@ssw0rd%@localhost"); $schema = $session->getSchema("docstore"); $collection = $schema->getCollection("restaurants"); $results = $collection-> nd($search)->execute()->fetchAll(); ... foreach ($results as $doc) { echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; } Easy, using only CRUD operations ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 62 / 100

CRUD operations for collections Add a document collection.add({ name: 'fred', age: 42 }) .add({ name: 'dave', age: 23 }) .execute() collection.add([ { name: 'dimo', age: 50 }, { name: 'kenny', age: 25 } ]).execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 65 / 100

CRUD operations for collections Modify a document collection.modify('name = :name') .bind('name', 'fred') .set('age', 43) .sort('name ASC') .limit(1) .execute() collection.modify('name = :name') .bind('name', 'fred') .patch({ age: 43, active: false }) .sort('name DESC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 67 / 100

CRUD operations for collections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 69 / 100

Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 75 / 100

Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 76 / 100

Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ- COMMITTED|... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 77 / 100

Document Store Full ACID ! It relies on the proven MySQL InnoDB´s strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ- COMMITTED|... We do care about your data ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 78 / 100

Challenge: list the best restaurant of each type of food and show the top 10, with the best one first ! don't forget that all these restaurants are just JSON documents Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 82 / 100

NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection-> nd('cuisine like "italian"')->execute()->fetchAll(); Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 87 / 100

NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection-> nd('cuisine like "italian"')->execute()->fetchAll(); Or use SQL only when really needed: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 88 / 100

NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection-> nd('cuisine like "italian"')->execute()->fetchAll(); Or use SQL only when really needed: $results = $session->sql('WITH cte1 AS (SELECT doc->>"$.name" AS name, doc->>"$.cuisine" AS cuisine, (SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" COLUMNS (score INT PATH "$.score")) AS r) AS avg_score FROM docstore.restaurants) SELECT *, RANK() OVER ( PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10;')->execute(); Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 89 / 100

NoSQL or SQL You have the possibility to write clean and neat code: $results = $collection-> nd('cuisine like "italian"')->execute()->fetchAll(); Or use SQL only when really needed: $results = $session->sql('WITH cte1 AS (SELECT doc->>"$.name" AS name, doc->>"$.cuisine" AS cuisine, (SELECT AVG(score) FROM JSON_TABLE(doc, "$.grades[*]" COLUMNS (score INT PATH "$.score")) AS r) AS avg_score FROM docstore.restaurants) SELECT *, RANK() OVER ( PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10;')->execute(); All in the same MySQL X Session ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 90 / 100

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

May 9th, 2019 Hotel NH Collection Roma Giustiniano Via Virgilio, 1 E/F/G, 0019 ROMA MySQL Day Roma You are invited to listen last news on MySQL 8.0, watch demos and be in contact with the MySQL Italian Team! http://bit.ly/MySQLDayRoma2019 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 98 / 100

Oracle Code Roma: NoSQL + SQL = MySQL

More Related Content

What's hot

Similar to Oracle Code Roma: NoSQL + SQL = MySQL

More from Frederic Descamps

Recently uploaded

Oracle Code Roma: NoSQL + SQL = MySQL