Frederic Descamps, profile picture
Uploaded byFrederic Descamps
PDF, PPTX585 views

Python and MySQL 8.0 Document Store

This document provides an overview of MySQL and its capabilities for storing and querying JSON documents. Key points include: - MySQL has native JSON support with a JSON data type and over 30 JSON functions. It also supports JSON paths. - MySQL 8.0's document store functionality allows it to be used as a schema-flexible document store without needing to define tables or columns in advance. - The document store is built on MySQL's existing technologies like InnoDB and provides full ACID compliance for JSON documents. - MySQL Connector/Python provides APIs for interacting with MySQL as both a relational database and a document store from Python applications in a uniform way.

Download as PDF, PPTX
1 / 111
2 / 111
3 / 111
4 / 111
5 / 111
6 / 111
7 / 111
8 / 111
9 / 111
10 / 111
11 / 111
12 / 111
13 / 111
14 / 111
15 / 111
16 / 111
17 / 111
18 / 111
19 / 111
20 / 111
21 / 111
22 / 111
23 / 111
24 / 111
25 / 111
26 / 111
27 / 111
28 / 111
29 / 111
30 / 111
31 / 111
32 / 111
33 / 111
34 / 111
35 / 111
36 / 111
37 / 111
38 / 111
39 / 111
40 / 111
41 / 111
42 / 111
43 / 111
44 / 111
45 / 111
46 / 111
47 / 111
48 / 111
49 / 111
50 / 111
51 / 111
52 / 111
53 / 111
54 / 111
55 / 111
56 / 111
57 / 111
58 / 111
59 / 111
60 / 111
61 / 111
62 / 111
63 / 111
64 / 111
65 / 111
66 / 111
67 / 111
68 / 111
69 / 111
70 / 111
71 / 111
72 / 111
73 / 111
74 / 111
75 / 111
76 / 111
77 / 111
78 / 111
79 / 111
80 / 111
81 / 111
82 / 111
83 / 111
84 / 111
85 / 111
86 / 111
87 / 111
88 / 111
89 / 111
90 / 111
91 / 111
92 / 111
93 / 111
94 / 111
95 / 111
96 / 111
97 / 111
98 / 111
99 / 111
100 / 111
101 / 111
102 / 111
103 / 111
104 / 111
105 / 111
106 / 111
107 / 111
108 / 111
109 / 111
110 / 111
111 / 111
1 / 111
Python and MySQL 8.0 Document Store DEV5966 Frédéric Descamps Community Manager MySQL September 2019 2 / 111
  Safe Harbor 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, timing and pricing of any features or functionality described for Oracle's product may change and remains at the sole discretion of Oracle Corporation. Statement in this presentation relating to Oracle's future plans, expectations, beliefs, intentions and ptospects are "forward-looking statements" and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that a ect our business is contained in Oracle's Securities and Exchange Commission (SEC) lings, including our most recent reports on Form 10-K and Form 10-Q under the heading "Risk Factors". These lings are available on the SEC's website or on Oracle's website at h p://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Copyright @ 2019 Oracle and/or its affiliates. 3 / 111
about.me/lefred Who am I ? Copyright @ 2019 Oracle and/or its affiliates. 4 / 111
Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.23 devops believer living in Belgium 🇧🇪 h ps://lefred.be Copyright @ 2019 Oracle and/or its affiliates. 5 / 111
MySQL and JSON How MySQL works with JSON Copyright @ 2019 Oracle and/or its affiliates. 6 / 111
JavaScript Object Notation (JSON) { "_id": "5ad5b645f88c5bb8fe3fd337", "name": "Morris Park Bake Shop", "grades": [ { "date": "2014-03-03T00:00:00Z", "grade": "A", "score": 2 }, ], "address": { "coord": [ -73.856077, 40.848447 ], "street": "Morris Park Ave", "zipcode": "10462", "building": "1007" }, "borough": "Bronx", "cuisine": "Bakery", "restaurant_id": "30075445" } Copyright @ 2019 Oracle and/or its affiliates. 7 / 111
8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Copyright @ 2019 Oracle and/or its affiliates. 8 / 111
8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Copyright @ 2019 Oracle and/or its affiliates. 9 / 111
8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Support for partial updates in MySQL 8.0 Copyright @ 2019 Oracle and/or its affiliates. 10 / 111
8.0 MySQL has Native JSON Support JSON data type since MySQL 5.7 Stored as binary object Support for partial updates in MySQL 8.0 30 JSON functions Copyright @ 2019 Oracle and/or its affiliates. 11 / 111
8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document Copyright @ 2019 Oracle and/or its affiliates. 12 / 111
8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator Copyright @ 2019 Oracle and/or its affiliates. 13 / 111
8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: Copyright @ 2019 Oracle and/or its affiliates. 14 / 111
8.0 ... and support JSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: .* all members in the object [*] all values in the array [pre x]**su x path beginning with pre x and ending with su x Copyright @ 2019 Oracle and/or its affiliates. 15 / 111
MySQL Document Store the Solution Copyright @ 2019 Oracle and/or its affiliates. 16 / 111
Built on the MySQL JSON Data type and Proven MySQL Server Technology Provides a schema exible JSON Document Store No SQL required No need to de ne all possible a ributes, 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. 17 / 111
X Protocol Connectors Copyright @ 2019 Oracle and/or its affiliates. 18 / 111
X DevAPI Developed from scratch for modern development Supports: SQL NoSQL - JSON documents NoSQL - SQL tables Uniform API across programming languages We provide connectors for C++, Java, .Net, Node.js, Python, PHP working with Communities to help them supporting it too Supported in the MySQL Shell Full ACID support Savepoints Connection pools (as of 8.0.13) Copyright @ 2019 Oracle and/or its affiliates. 19 / 111
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 parallel importTABLE data Copyright @ 2019 Oracle and/or its affiliates. 20 / 111
Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. 21 / 111
Migration from MongoDB to MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. 22 / 111
Copyright @ 2019 Oracle and/or its affiliates. 23 / 111
Copyright @ 2019 Oracle and/or its affiliates. 24 / 111
Let's query Copyright @ 2019 Oracle and/or its affiliates. 25 / 111
Let's query That's too much records to show on this slide... Copyright @ 2019 Oracle and/or its affiliates. 26 / 111
Let's query That's too much records to show on this slide... Copyright @ 2019 Oracle and/or its affiliates. 27 / 111
Let's query That's too much records to show on this slide... Let's add a limit to it Copyright @ 2019 Oracle and/or its affiliates. 28 / 111
  Copyright @ 2019 Oracle and/or its affiliates. 29 / 111
Some more examples Copyright @ 2019 Oracle and/or its affiliates. 30 / 111
Some more examples Let's add a selection criteria: Copyright @ 2019 Oracle and/or its affiliates. 31 / 111
Using IN... Copyright @ 2019 Oracle and/or its affiliates. 32 / 111
Syntax slightly different than MongoDB Copyright @ 2019 Oracle and/or its affiliates. 33 / 111
Syntax slightly different than MongoDB Copyright @ 2019 Oracle and/or its affiliates. 34 / 111
CRUD operations Copyright @ 2019 Oracle and/or its affiliates. 35 / 111
CRUD operations for collections Add a document Copyright @ 2019 Oracle and/or its affiliates. 36 / 111
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. 37 / 111
CRUD operations for collections Modify a document Copyright @ 2019 Oracle and/or its affiliates. 38 / 111
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. 39 / 111
CRUD operations for collections Remove a document Copyright @ 2019 Oracle and/or its affiliates. 40 / 111
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. 41 / 111
MySQL Document Store Objects Summary Copyright @ 2019 Oracle and/or its affiliates. 42 / 111
All you need to know is here: h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2019 Oracle and/or its affiliates. 43 / 111
MySQL Connector Python The Python connector for MySQL Copyright @ 2019 Oracle and/or its affiliates. 44 / 111
NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Copyright @ 2019 Oracle and/or its affiliates. 45 / 111
NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Copyright @ 2019 Oracle and/or its affiliates. 46 / 111
NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Copyright @ 2019 Oracle and/or its affiliates. 47 / 111
NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 Copyright @ 2019 Oracle and/or its affiliates. 48 / 111
NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Copyright @ 2019 Oracle and/or its affiliates. 49 / 111
NoSQL SQL   MySQL Connector/Python 8 GA since April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Table and JSON Document support Copyright @ 2019 Oracle and/or its affiliates. 50 / 111
MySQL Connector/Python 8 - 3 APIs Choice of Three APIs API Python Module Comment PEP249 Python Database API mysql.connector The traditional API C Extension API _mysql_connector Similar to the MySQL C API MySQL X DevAPI mysqlx New in 8.0, both SQL and NoSQL   All you need to know is here: h ps://dev.mysql.com/doc/connector-python/en/ Copyright @ 2019 Oracle and/or its affiliates. 51 / 111
 DATABASES = { 'default': { 'NAME': 'user_data', 'ENGINE': 'mysql.connector.django', 'USER': 'mysql_user', 'PASSWORD': 'password', 'OPTIONS': { 'autocommit': True, }, } } MySQL Connect/Python and Django MySQL Connector/Python includes also a mysql.connector.django module that provides a Django back end for MySQL Copyright @ 2019 Oracle and/or its affiliates. 52 / 111
MySQL Connector/Python 8 - installation easiest, use pip: use a MySQL repository for your distribution (fedora, ubuntu, debian, ...) Copyright @ 2019 Oracle and/or its affiliates. 53 / 111
MySQL Connector/Python 8 - installation or go to h ps://dev.mysql.com/downloads/connector/python/ Copyright @ 2019 Oracle and/or its affiliates. 54 / 111
What time is it ? Time to code ! Copyright @ 2019 Oracle and/or its affiliates. 55 / 111
Some coding... import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('all_recs') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant session.close() Copyright @ 2019 Oracle and/or its affiliates. 56 / 111
If it was not yet obvious, you can now use MySQL without SQL ! Copyright @ 2019 Oracle and/or its affiliates. 57 / 111
OK we have Document Store, CRUD and ACID but what makes MySQL Document Store unique ? Copyright @ 2019 Oracle and/or its affiliates. 58 / 111
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. 59 / 111
NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. 60 / 111
NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. 61 / 111
NoSQL as SQL - aggregation Copyright @ 2019 Oracle and/or its affiliates. 62 / 111
import mysqlx session = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('all_recs') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant result = 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.all_recs) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score DESC) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10''').execute() for restaurant in result.fetch_all(): print "%s - %s - %d " % (restaurant[0], restaurant[1], restaurant[2]) session.close() Copyright @ 2019 Oracle and/or its affiliates. 63 / 111
Output Copyright @ 2019 Oracle and/or its affiliates. 64 / 111
And back to JSON And of course it's possible to return the result as a JSON document: result = session.sql('''select JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT( "name", name, "cuisine", cuisine, "score", avg_score))) from (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.all_recs) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score DESC) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10) as t''').execute() row = result.fetch_one() print row[0] Copyright @ 2019 Oracle and/or its affiliates. 65 / 111
  And back to JSON - output Copyright @ 2019 Oracle and/or its affiliates. 66 / 111
some extras... The hidden part of the iceberg Copyright @ 2019 Oracle and/or its affiliates. 67 / 111
_id Every document has a unique identi er called the document ID. It can be manually assigned when adding a document or generated and assigned to the document automatically ! Since MySQL 8.0.11, the _id is generated by the server. To get the list of automatically generated IDs use the result.getGeneratedIDs() method. The _id is made of 3 parts, all hex encoded: pre x timestamp when the MySQL Server instance was started auto-increment counter Globally unique IDs Optimized for InnoDB storage Examples 00005cc17b700000000000000003 Copyright @ 2019 Oracle and/or its affiliates. 68 / 111
Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: Copyright @ 2019 Oracle and/or its affiliates. 69 / 111
Document Store Full ACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2019 Oracle and/or its affiliates. 70 / 111
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. 71 / 111
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. 72 / 111
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. 73 / 111
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. 74 / 111
What time is it now ? Time for some more examples ! Copyright @ 2019 Oracle and/or its affiliates. 75 / 111
More fun with The Walking Dead Copyright @ 2019 Oracle and/or its affiliates. 76 / 111
Example: All Episodes of Season 1 Copyright @ 2019 Oracle and/or its affiliates. 77 / 111
Example: All First Episodes of Each Season Copyright @ 2019 Oracle and/or its affiliates. 78 / 111
Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. 79 / 111
Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. 80 / 111
Example: Speed Up Episode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. 81 / 111
Example: Transaction Copyright @ 2019 Oracle and/or its affiliates. 82 / 111
Example: Do more with SQL List the amount of episodes by season: Copyright @ 2019 Oracle and/or its affiliates. 83 / 111
Example: Do more with SQL (2) Episode statistics for each season: Copyright @ 2019 Oracle and/or its affiliates. 84 / 111
Example: Do more with SQL (3) Number of days between episodes: Copyright @ 2019 Oracle and/or its affiliates. 85 / 111
JSON Data validation It's possible to have constraints between collections (or tables): Copyright @ 2019 Oracle and/or its affiliates. 86 / 111
JSON Data validation (2) Let's see in SQL how the 2 collections look like: Copyright @ 2019 Oracle and/or its affiliates. 87 / 111
JSON Data validation (3) Let's add one virtual column in each collection: Copyright @ 2019 Oracle and/or its affiliates. 88 / 111
JSON Data validation (4) Now we can index the reference and create the constraint: Copyright @ 2019 Oracle and/or its affiliates. 89 / 111
JSON Data validation (4) Copyright @ 2019 Oracle and/or its affiliates. 90 / 111
JSON Data validation (5) And now even in pure NoSQL CRUD, we can see the data validation in action: Copyright @ 2019 Oracle and/or its affiliates. 91 / 111
JSON Schema validation - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. 92 / 111
JSON Schema validation - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. 93 / 111
JSON Schema validation - NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. 94 / 111
JSON Schema validation (2) - NEW 8.0.17 New JSON_SCHEMA_VALID function: Copyright @ 2019 Oracle and/or its affiliates. 95 / 111
JSON Schema validation (2) - NEW 8.0.17 New JSON_SCHEMA_VALID function: Copyright @ 2019 Oracle and/or its affiliates. 96 / 111
JSON Schema validation (3) - NEW 8.0.17 Knowing why thanks to the new JSON_SCHEMA_VALIDATION_REPORT function: Copyright @ 2019 Oracle and/or its affiliates. 97 / 111
JSON Schema validation (4) - NEW 8.0.17 And nally, mixing JSON_SCHEMA_VALID with CHECK CONSTRAINT: Copyright @ 2019 Oracle and/or its affiliates. 98 / 111
JSON Schema validation (4) - NEW 8.0.17 And nally, mixing JSON_SCHEMA_VALID with CHECK CONSTRAINT: Copyright @ 2019 Oracle and/or its affiliates. 99 / 111
what do I gain ? Conclusion Copyright @ 2019 Oracle and/or its affiliates. 100 / 111
schemaless exible 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. 101 / 111
8.0 http://lefred.be/content/top-10-reasons-for- nosql-with-mysql/ Copyright @ 2019 Oracle and/or its affiliates. 102 / 111
8.0 h ps://www.slideshare.net/lefred.descamps/mysql- shell-the-best-dba-tool h ps://github.com/lefred/mysql-shell- mydba h ps://github.com/lefred/mysql-shell- innotop More with MySQL Shell & Python Copyright @ 2019 Oracle and/or its affiliates. 103 / 111
MySQL Shell Reporting Framework - 8.0.16 Now it's also possible to create user-de ned reports directly in the Shell. They can be wri en in Python and called the same way on every operating systems. No need for extra libraries ! More Info: h ps://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-reporting.html h ps://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/ h ps://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-de ned-reports/ Copyright @ 2019 Oracle and/or its affiliates. 104 / 111
MySQL Shell Extentions & Plugins - 8.0.17 Since 8.0.17, MySQL Shell has been extended to allow you to write your own extensions and plugins ! More Info: h ps://mysqlserverteam.com/mysql-shell-plugins-introduction/ h ps://lefred.be/content/overview-on-mysql-shell-8-0-17-extensions-plugins-and- how-to-write-yours/ Copyright @ 2019 Oracle and/or its affiliates. 105 / 111
Credits Thank you Olivier Dasini for some TV Show examples: h p://dasini.net/blog/2019/04/02/mysql-json-document-store/ Thank you Jesper Wisborg Krogh for inspiration extending the MySQL Shell Copyright @ 2019 Oracle and/or its affiliates. 106 / 111
107 / 111
Thank you ! Copyright @ 2019 Oracle and/or its affiliates. 108 / 111
109 / 111
Upgrade to MySQL 8.0 It's time to upgrade to MySQL 8.0, the fastest MySQL adoption release ever ! Copyright @ 2019 Oracle and/or its affiliates. 110 / 111
Session Survey Help us make the content even be er. Please complete the session survey in the Mobile App. Copyright @ 2019 Oracle and/or its affiliates. 111 / 111

More Related Content

PDF
How to operate MySQL InnoDB Cluster with MySQL Shell
byFrederic Descamps
 
PDF
MySQL Shell: the best DBA tool !
byFrederic Descamps
 
PDF
MySQL 8.0 InnoDB Cluster - Easiest Tutorial
byFrederic Descamps
 
PDF
MySQL 8.0 : High Availability Solution for Everybody
byFrederic Descamps
 
PDF
State of the Dolphin 2020 - 25th Anniversary of MySQL with 8.0.20
byFrederic Descamps
 
PDF
MySQL Shell - the best DBA tool ?
byFrederic Descamps
 
PDF
Oracle Open World Middle East - MySQL 8 a Giant Leap for SQL
byFrederic Descamps
 
PDF
OpenExpo Virtual Experience 2020: MySQL 8.0 Document Store - Discovery of a n...
byFrederic Descamps
 
How to operate MySQL InnoDB Cluster with MySQL Shell
byFrederic Descamps
 
MySQL Shell: the best DBA tool !
byFrederic Descamps
 
MySQL 8.0 InnoDB Cluster - Easiest Tutorial
byFrederic Descamps
 
MySQL 8.0 : High Availability Solution for Everybody
byFrederic Descamps
 
State of the Dolphin 2020 - 25th Anniversary of MySQL with 8.0.20
byFrederic Descamps
 
MySQL Shell - the best DBA tool ?
byFrederic Descamps
 
Oracle Open World Middle East - MySQL 8 a Giant Leap for SQL
byFrederic Descamps
 
OpenExpo Virtual Experience 2020: MySQL 8.0 Document Store - Discovery of a n...
byFrederic Descamps
 

What's hot

PDF
Another MySQL HA Solution for ProxySQL Users, Easy and All Integrated: MySQL ...
byFrederic Descamps
 
PDF
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorial
byFrederic Descamps
 
PDF
MySQL InnoDB Cluster and Group Replication in a Nutshell
byFrederic Descamps
 
PDF
MySQL : State of the Dolphin May 2019
byFrederic Descamps
 
PDF
MySQL InnoDB Cluster - Advanced Configuration & Operations
byFrederic Descamps
 
PDF
MySQL Day Virtual: Best Practices Tips - Upgrading to MySQL 8.0
byFrederic Descamps
 
PDF
DataOpsbarcelona 2019: Deep dive into MySQL Group Replication... the magic e...
byFrederic Descamps
 
PDF
MySQL InnoDB Cluster and Group Replication in a nutshell hands-on tutorial
byFrederic Descamps
 
PDF
preFOSDEM MySQL Day - Best Practices to Upgrade to MySQL 8.0
byFrederic Descamps
 
PDF
Hash join in MySQL 8
byErik Frøseth
 
PDF
MySQL Group Replication: Handling Network Glitches - Best Practices
byFrederic Descamps
 
PDF
MySQL Tech Café #8: MySQL 8.0 for Python Developers
byFrederic Descamps
 
PDF
MySQL Shell - the best DBA tool !
byFrederic Descamps
 
PDF
Looking Inside the MySQL 8.0 Document Store
byFrederic Descamps
 
PDF
UAE MySQL Users Group Meet-up : MySQL Shell Document Store & more...
byFrederic Descamps
 
PDF
Cloud native - Why to use MySQL 8.0 and how to use it on oci with MDS
byFrederic Descamps
 
PDF
the State of the Dolphin - October 2020
byFrederic Descamps
 
PDF
Oracle Open World 2018 / Code One : MySQL 8.0 Document Store
byFrederic Descamps
 
PDF
MySQL Database Service Webinar - Installing WordPress in OCI with MDS
byFrederic Descamps
 
PDF
Percona Live Europe 2018 MySQL Group Replication... the magic explained
byFrederic Descamps
 
Another MySQL HA Solution for ProxySQL Users, Easy and All Integrated: MySQL ...
byFrederic Descamps
 
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorial
byFrederic Descamps
 
MySQL InnoDB Cluster and Group Replication in a Nutshell
byFrederic Descamps
 
MySQL : State of the Dolphin May 2019
byFrederic Descamps
 
MySQL InnoDB Cluster - Advanced Configuration & Operations
byFrederic Descamps
 
MySQL Day Virtual: Best Practices Tips - Upgrading to MySQL 8.0
byFrederic Descamps
 
DataOpsbarcelona 2019: Deep dive into MySQL Group Replication... the magic e...
byFrederic Descamps
 
MySQL InnoDB Cluster and Group Replication in a nutshell hands-on tutorial
byFrederic Descamps
 
preFOSDEM MySQL Day - Best Practices to Upgrade to MySQL 8.0
byFrederic Descamps
 
Hash join in MySQL 8
byErik Frøseth
 
MySQL Group Replication: Handling Network Glitches - Best Practices
byFrederic Descamps
 
MySQL Tech Café #8: MySQL 8.0 for Python Developers
byFrederic Descamps
 
MySQL Shell - the best DBA tool !
byFrederic Descamps
 
Looking Inside the MySQL 8.0 Document Store
byFrederic Descamps
 
UAE MySQL Users Group Meet-up : MySQL Shell Document Store & more...
byFrederic Descamps
 
Cloud native - Why to use MySQL 8.0 and how to use it on oci with MDS
byFrederic Descamps
 
the State of the Dolphin - October 2020
byFrederic Descamps
 
Oracle Open World 2018 / Code One : MySQL 8.0 Document Store
byFrederic Descamps
 
MySQL Database Service Webinar - Installing WordPress in OCI with MDS
byFrederic Descamps
 
Percona Live Europe 2018 MySQL Group Replication... the magic explained
byFrederic Descamps
 

Similar to Python and MySQL 8.0 Document Store

PDF
PyConX - Python & MySQL 8.0 Document Store
byFrederic Descamps
 
PDF
MySQL 8.0 - What's New ?
byOlivier DASINI
 
PDF
MySQL 8.0, what's new ? - Forum PHP 2018
byOlivier DASINI
 
PDF
MySQL Document Store for Modern Applications
byOlivier DASINI
 
PDF
MySQL Document Store (Oracle Code Warsaw 2018)
byVittorio Cioe
 
PDF
Node.js and the MySQL Document Store
byRui Quelhas
 
PPTX
A Step by Step Introduction to the MySQL Document Store
byDave Stokes
 
PDF
20190713_MySQL開発最新動向
byMachiko Ikoma
 
PDF
MySQL 8.0 Introduction to NoSQL + SQL
byManuel Contreras
 
PDF
MySQL Day Paris 2018 - What’s New in MySQL 8.0 ?
byOlivier DASINI
 
PDF
20171104 hk-py con-mysql-documentstore_v1
byIvan Ma
 
PDF
Python and the MySQL Document Store
byJesper Wisborg Krogh
 
PDF
MySQL Document Store and Node.JS
byReggie Burnett
 
PDF
MySQL 8 loves JavaScript
bySanjay Manwani
 
PDF
MySQL Day Paris 2018 - MySQL JSON Document Store
byOlivier DASINI
 
PDF
MySQL Document Store
byMario Beck
 
PDF
MySQL Document Store - A Document Store with all the benefts of a Transactona...
byOlivier DASINI
 
PDF
[OSC 2020 Online/Nagoya] MySQLドキュメントストア
byRyusuke Kajiyama
 
PDF
MySQL Day Paris 2016 - MySQL as a Document Store
byOlivier DASINI
 
PDF
MySQL Document Store -- SCaLE 17x Presentation
byDave Stokes
 
PyConX - Python & MySQL 8.0 Document Store
byFrederic Descamps
 
MySQL 8.0 - What's New ?
byOlivier DASINI
 
MySQL 8.0, what's new ? - Forum PHP 2018
byOlivier DASINI
 
MySQL Document Store for Modern Applications
byOlivier DASINI
 
MySQL Document Store (Oracle Code Warsaw 2018)
byVittorio Cioe
 
Node.js and the MySQL Document Store
byRui Quelhas
 
A Step by Step Introduction to the MySQL Document Store
byDave Stokes
 
20190713_MySQL開発最新動向
byMachiko Ikoma
 
MySQL 8.0 Introduction to NoSQL + SQL
byManuel Contreras
 
MySQL Day Paris 2018 - What’s New in MySQL 8.0 ?
byOlivier DASINI
 
20171104 hk-py con-mysql-documentstore_v1
byIvan Ma
 
Python and the MySQL Document Store
byJesper Wisborg Krogh
 
MySQL Document Store and Node.JS
byReggie Burnett
 
MySQL 8 loves JavaScript
bySanjay Manwani
 
MySQL Day Paris 2018 - MySQL JSON Document Store
byOlivier DASINI
 
MySQL Document Store
byMario Beck
 
MySQL Document Store - A Document Store with all the benefts of a Transactona...
byOlivier DASINI
 
[OSC 2020 Online/Nagoya] MySQLドキュメントストア
byRyusuke Kajiyama
 
MySQL Day Paris 2016 - MySQL as a Document Store
byOlivier DASINI
 
MySQL Document Store -- SCaLE 17x Presentation
byDave Stokes
 

More from Frederic Descamps

PDF
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
PDF
MySQL Shell for DBAs
byFrederic Descamps
 
PDF
Percona Live 2022 - MySQL Architectures
byFrederic Descamps
 
PDF
Percona Live 2022 - MySQL Shell for Visual Studio Code
byFrederic Descamps
 
PDF
Deploying Magento on OCI with MDS
byFrederic Descamps
 
PDF
MySQL Day Roma - MySQL Shell and Visual Studio Code Extension
byFrederic Descamps
 
PDF
State of the Dolphin - May 2022
byFrederic Descamps
 
PDF
Pi Day 2022 - from IoT to MySQL HeatWave Database Service
byFrederic Descamps
 
PDF
Percona Live 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
PDF
MySQL Router REST API
byFrederic Descamps
 
PDF
MySQL User Group NL - MySQL 8
byFrederic Descamps
 
PDF
State of The Dolphin - May 2021
byFrederic Descamps
 
PDF
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
PDF
MySQL Innovation & Cloud Day - Document Store avec MySQL HeatWave Database Se...
byFrederic Descamps
 
PDF
RivieraJUG - MySQL Indexes and Histograms
byFrederic Descamps
 
PDF
RivieraJUG - MySQL 8.0 - What's new for developers.pdf
byFrederic Descamps
 
PDF
FOSDEM 2022 MySQL Devroom: MySQL 8.0 - Logical Backups, Snapshots and Point-...
byFrederic Descamps
 
PDF
Open Source 101 2022 - MySQL Indexes and Histograms
byFrederic Descamps
 
PDF
LinuxFest Northwest 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
PDF
Confoo 2022 - le cycle d'une instance MySQL
byFrederic Descamps
 
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
MySQL Shell for DBAs
byFrederic Descamps
 
Percona Live 2022 - MySQL Architectures
byFrederic Descamps
 
Percona Live 2022 - MySQL Shell for Visual Studio Code
byFrederic Descamps
 
Deploying Magento on OCI with MDS
byFrederic Descamps
 
MySQL Day Roma - MySQL Shell and Visual Studio Code Extension
byFrederic Descamps
 
State of the Dolphin - May 2022
byFrederic Descamps
 
Pi Day 2022 - from IoT to MySQL HeatWave Database Service
byFrederic Descamps
 
Percona Live 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
MySQL Router REST API
byFrederic Descamps
 
MySQL User Group NL - MySQL 8
byFrederic Descamps
 
State of The Dolphin - May 2021
byFrederic Descamps
 
Les nouveautés de MySQL 8.0
byFrederic Descamps
 
MySQL Innovation & Cloud Day - Document Store avec MySQL HeatWave Database Se...
byFrederic Descamps
 
RivieraJUG - MySQL Indexes and Histograms
byFrederic Descamps
 
RivieraJUG - MySQL 8.0 - What's new for developers.pdf
byFrederic Descamps
 
FOSDEM 2022 MySQL Devroom: MySQL 8.0 - Logical Backups, Snapshots and Point-...
byFrederic Descamps
 
Open Source 101 2022 - MySQL Indexes and Histograms
byFrederic Descamps
 
LinuxFest Northwest 2022 - The Evolution of a MySQL Database System
byFrederic Descamps
 
Confoo 2022 - le cycle d'une instance MySQL
byFrederic Descamps
 

Recently uploaded

PDF
MathML Core in WebKit
byIgalia
 
PDF
ODSC West 2025 OpenMetadata Workshop.pdf
byOpenMetadata
 
PPTX
2025 - AI terms & Meanings for Marketers
byashishav29
 
PDF
Fighting CVEs in Embedded Linux with the Yocto Project and OTA Updates
byLeon Anavi
 
PDF
Unleash AI power with the Dell Pro 14 Plus - Interactive PDF
byPrincipled Technologies
 
PPTX
UiPath Automation Suite_Community Session_3/3
bysuhanisingh58689
 
PDF
GDG Cloud Southlake #47: Bala Desikan: Build Autonomous Agentic AI Apps on GCP
byJames Anderson
 
PDF
MicroPython presentation - PyConFr Lyon 2025 - Amine Bendahmane
byAmine Bendahmane
 
PDF
Q8 DIGITAL IDENTITY HUB - WSO2 Oxygenate Italy 2025
byProfesia Srl, Lynx Group
 
PDF
API Days Australia - API Management in the AI Era
byNilesh Gule
 
PDF
TrustArc Webinar - It’s Time to Rethink Privacy
byTrustArc
 
PDF
What's New in PostgreSQL 18 | Mydbops MyWebinar 47
byMydbops
 
PDF
GDG Boise - Innovating with Google Cloud Artificial Intelligence
byJoey Brown
 
PPTX
Assignment Review and Accessibility Audit Findings.pptx
byJulia Undeutsch
 
PDF
From Pixels to Insights: Getting Started with Imagery in FME
bySafe Software
 
PPTX
RAPTOR_Intro_Exercises_Presentation.pptx
bySwatiMalik36
 
PDF
The Smol Training Playbook: The Secrets to Building World-Class LLMs
byRazin Mustafiz
 
PDF
Transform Your Online Store with AltiusNxt AI Enriched data
byAltiusNxt Technologies
 
PDF
Yelp Data Scraping for Local Services Market Insights
bycreativeclicks1733
 
PDF
NPTEL Assignment Completed PDF FILE with ans
byadultme43
 
MathML Core in WebKit
byIgalia
 
ODSC West 2025 OpenMetadata Workshop.pdf
byOpenMetadata
 
2025 - AI terms & Meanings for Marketers
byashishav29
 
Fighting CVEs in Embedded Linux with the Yocto Project and OTA Updates
byLeon Anavi
 
Unleash AI power with the Dell Pro 14 Plus - Interactive PDF
byPrincipled Technologies
 
UiPath Automation Suite_Community Session_3/3
bysuhanisingh58689
 
GDG Cloud Southlake #47: Bala Desikan: Build Autonomous Agentic AI Apps on GCP
byJames Anderson
 
MicroPython presentation - PyConFr Lyon 2025 - Amine Bendahmane
byAmine Bendahmane
 
Q8 DIGITAL IDENTITY HUB - WSO2 Oxygenate Italy 2025
byProfesia Srl, Lynx Group
 
API Days Australia - API Management in the AI Era
byNilesh Gule
 
TrustArc Webinar - It’s Time to Rethink Privacy
byTrustArc
 
What's New in PostgreSQL 18 | Mydbops MyWebinar 47
byMydbops
 
GDG Boise - Innovating with Google Cloud Artificial Intelligence
byJoey Brown
 
Assignment Review and Accessibility Audit Findings.pptx
byJulia Undeutsch
 
From Pixels to Insights: Getting Started with Imagery in FME
bySafe Software
 
RAPTOR_Intro_Exercises_Presentation.pptx
bySwatiMalik36
 
The Smol Training Playbook: The Secrets to Building World-Class LLMs
byRazin Mustafiz
 
Transform Your Online Store with AltiusNxt AI Enriched data
byAltiusNxt Technologies
 
Yelp Data Scraping for Local Services Market Insights
bycreativeclicks1733
 
NPTEL Assignment Completed PDF FILE with ans
byadultme43
 

Python and MySQL 8.0 Document Store

  • 1.
  • 2.
    Python and MySQL8.0 Document Store DEV5966 Frédéric Descamps Community Manager MySQL September 2019 2 / 111
  • 3.
      Safe Harbor The followingis 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, timing and pricing of any features or functionality described for Oracle's product may change and remains at the sole discretion of Oracle Corporation. Statement in this presentation relating to Oracle's future plans, expectations, beliefs, intentions and ptospects are "forward-looking statements" and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that a ect our business is contained in Oracle's Securities and Exchange Commission (SEC) lings, including our most recent reports on Form 10-K and Form 10-Q under the heading "Risk Factors". These lings are available on the SEC's website or on Oracle's website at h p://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Copyright @ 2019 Oracle and/or its affiliates. 3 / 111
  • 4.
    about.me/lefred Who am I? Copyright @ 2019 Oracle and/or its affiliates. 4 / 111
  • 5.
    Frédéric Descamps @lefred MySQL Evangelist ManagingMySQL since 3.23 devops believer living in Belgium 🇧🇪 h ps://lefred.be Copyright @ 2019 Oracle and/or its affiliates. 5 / 111
  • 6.
    MySQL and JSON HowMySQL works with JSON Copyright @ 2019 Oracle and/or its affiliates. 6 / 111
  • 7.
    JavaScript Object Notation(JSON) { "_id": "5ad5b645f88c5bb8fe3fd337", "name": "Morris Park Bake Shop", "grades": [ { "date": "2014-03-03T00:00:00Z", "grade": "A", "score": 2 }, ], "address": { "coord": [ -73.856077, 40.848447 ], "street": "Morris Park Ave", "zipcode": "10462", "building": "1007" }, "borough": "Bronx", "cuisine": "Bakery", "restaurant_id": "30075445" } Copyright @ 2019 Oracle and/or its affiliates. 7 / 111
  • 8.
    8.0 MySQL has NativeJSON Support JSON data type since MySQL 5.7 Copyright @ 2019 Oracle and/or its affiliates. 8 / 111
  • 9.
    8.0 MySQL has NativeJSON Support JSON data type since MySQL 5.7 Stored as binary object Copyright @ 2019 Oracle and/or its affiliates. 9 / 111
  • 10.
    8.0 MySQL has NativeJSON Support JSON data type since MySQL 5.7 Stored as binary object Support for partial updates in MySQL 8.0 Copyright @ 2019 Oracle and/or its affiliates. 10 / 111
  • 11.
    8.0 MySQL has NativeJSON Support JSON data type since MySQL 5.7 Stored as binary object Support for partial updates in MySQL 8.0 30 JSON functions Copyright @ 2019 Oracle and/or its affiliates. 11 / 111
  • 12.
    8.0 ... and supportJSON Paths $.address.coord[*] $ the root of the document Copyright @ 2019 Oracle and/or its affiliates. 12 / 111
  • 13.
    8.0 ... and supportJSON Paths $.address.coord[*] $ the root of the document . path leg separator Copyright @ 2019 Oracle and/or its affiliates. 13 / 111
  • 14.
    8.0 ... and supportJSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: Copyright @ 2019 Oracle and/or its affiliates. 14 / 111
  • 15.
    8.0 ... and supportJSON Paths $.address.coord[*] $ the root of the document . path leg separator * wildcard: .* all members in the object [*] all values in the array [pre x]**su x path beginning with pre x and ending with su x Copyright @ 2019 Oracle and/or its affiliates. 15 / 111
  • 16.
    MySQL Document Store theSolution Copyright @ 2019 Oracle and/or its affiliates. 16 / 111
  • 17.
    Built on the MySQL JSONData type and Proven MySQL Server Technology Provides a schema exible JSON Document Store No SQL required No need to de ne all possible a ributes, 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. 17 / 111
  • 18.
    X Protocol Connectors Copyright@ 2019 Oracle and/or its affiliates. 18 / 111
  • 19.
    X DevAPI Developedfrom scratch for modern development Supports: SQL NoSQL - JSON documents NoSQL - SQL tables Uniform API across programming languages We provide connectors for C++, Java, .Net, Node.js, Python, PHP working with Communities to help them supporting it too Supported in the MySQL Shell Full ACID support Savepoints Connection pools (as of 8.0.13) Copyright @ 2019 Oracle and/or its affiliates. 19 / 111
  • 20.
    PY JS SQL > MySQL 8.0 Upgrade Checker AutoCompletion & 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 parallel importTABLE data Copyright @ 2019 Oracle and/or its affiliates. 20 / 111
  • 21.
    Migration from MongoDBto MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. 21 / 111
  • 22.
    Migration from MongoDBto MySQL DS For this example, I will use the well known restaurants collection: Copyright @ 2019 Oracle and/or its affiliates. 22 / 111
  • 23.
    Copyright @ 2019Oracle and/or its affiliates. 23 / 111
  • 24.
    Copyright @ 2019Oracle and/or its affiliates. 24 / 111
  • 25.
    Let's query Copyright @2019 Oracle and/or its affiliates. 25 / 111
  • 26.
    Let's query That's toomuch records to show on this slide... Copyright @ 2019 Oracle and/or its affiliates. 26 / 111
  • 27.
    Let's query That's toomuch records to show on this slide... Copyright @ 2019 Oracle and/or its affiliates. 27 / 111
  • 28.
    Let's query That's toomuch records to show on this slide... Let's add a limit to it Copyright @ 2019 Oracle and/or its affiliates. 28 / 111
  • 29.
      Copyright @ 2019Oracle and/or its affiliates. 29 / 111
  • 30.
    Some more examples Copyright@ 2019 Oracle and/or its affiliates. 30 / 111
  • 31.
    Some more examples Let'sadd a selection criteria: Copyright @ 2019 Oracle and/or its affiliates. 31 / 111
  • 32.
    Using IN... Copyright @2019 Oracle and/or its affiliates. 32 / 111
  • 33.
    Syntax slightly differentthan MongoDB Copyright @ 2019 Oracle and/or its affiliates. 33 / 111
  • 34.
    Syntax slightly differentthan MongoDB Copyright @ 2019 Oracle and/or its affiliates. 34 / 111
  • 35.
    CRUD operations Copyright @2019 Oracle and/or its affiliates. 35 / 111
  • 36.
    CRUD operations forcollections Add a document Copyright @ 2019 Oracle and/or its affiliates. 36 / 111
  • 37.
    CRUD operations forcollections 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. 37 / 111
  • 38.
    CRUD operations forcollections Modify a document Copyright @ 2019 Oracle and/or its affiliates. 38 / 111
  • 39.
    CRUD operations forcollections 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. 39 / 111
  • 40.
    CRUD operations forcollections Remove a document Copyright @ 2019 Oracle and/or its affiliates. 40 / 111
  • 41.
    CRUD operations forcollections Remove a document collection.remove('name = :name') .bind('name', 'fred') .sort('age ASC') .limit(1) .execute() Copyright @ 2019 Oracle and/or its affiliates. 41 / 111
  • 42.
    MySQL Document StoreObjects Summary Copyright @ 2019 Oracle and/or its affiliates. 42 / 111
  • 43.
    All you needto know is here: h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2019 Oracle and/or its affiliates. 43 / 111
  • 44.
    MySQL Connector Python ThePython connector for MySQL Copyright @ 2019 Oracle and/or its affiliates. 44 / 111
  • 45.
    NoSQL SQL   MySQL Connector/Python 8 GAsince April 2018 Copyright @ 2019 Oracle and/or its affiliates. 45 / 111
  • 46.
    NoSQL SQL   MySQL Connector/Python 8 GAsince April 2018 Maintained by Oracle Copyright @ 2019 Oracle and/or its affiliates. 46 / 111
  • 47.
    NoSQL SQL   MySQL Connector/Python 8 GAsince April 2018 Maintained by Oracle Dual license Copyright @ 2019 Oracle and/or its affiliates. 47 / 111
  • 48.
    NoSQL SQL   MySQL Connector/Python 8 GAsince April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 Copyright @ 2019 Oracle and/or its affiliates. 48 / 111
  • 49.
    NoSQL SQL   MySQL Connector/Python 8 GAsince April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Copyright @ 2019 Oracle and/or its affiliates. 49 / 111
  • 50.
    NoSQL SQL   MySQL Connector/Python 8 GAsince April 2018 Maintained by Oracle Dual license Support MySQL Server 5.5, 5.6, 5.7 and 8.0 SQL and NoSQL support Table and JSON Document support Copyright @ 2019 Oracle and/or its affiliates. 50 / 111
  • 51.
    MySQL Connector/Python 8- 3 APIs Choice of Three APIs API Python Module Comment PEP249 Python Database API mysql.connector The traditional API C Extension API _mysql_connector Similar to the MySQL C API MySQL X DevAPI mysqlx New in 8.0, both SQL and NoSQL   All you need to know is here: h ps://dev.mysql.com/doc/connector-python/en/ Copyright @ 2019 Oracle and/or its affiliates. 51 / 111
  • 52.
     DATABASES = { 'default':{ 'NAME': 'user_data', 'ENGINE': 'mysql.connector.django', 'USER': 'mysql_user', 'PASSWORD': 'password', 'OPTIONS': { 'autocommit': True, }, } } MySQL Connect/Python and Django MySQL Connector/Python includes also a mysql.connector.django module that provides a Django back end for MySQL Copyright @ 2019 Oracle and/or its affiliates. 52 / 111
  • 53.
    MySQL Connector/Python 8- installation easiest, use pip: use a MySQL repository for your distribution (fedora, ubuntu, debian, ...) Copyright @ 2019 Oracle and/or its affiliates. 53 / 111
  • 54.
    MySQL Connector/Python 8- installation or go to h ps://dev.mysql.com/downloads/connector/python/ Copyright @ 2019 Oracle and/or its affiliates. 54 / 111
  • 55.
    What time isit ? Time to code ! Copyright @ 2019 Oracle and/or its affiliates. 55 / 111
  • 56.
    Some coding... import mysqlx session= mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('all_recs') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant session.close() Copyright @ 2019 Oracle and/or its affiliates. 56 / 111
  • 57.
    If it wasnot yet obvious, you can now use MySQL without SQL ! Copyright @ 2019 Oracle and/or its affiliates. 57 / 111
  • 58.
    OK we haveDocument Store, CRUD and ACID but what makes MySQL Document Store unique ? Copyright @ 2019 Oracle and/or its affiliates. 58 / 111
  • 59.
    Challenge: list thebest 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. 59 / 111
  • 60.
    NoSQL as SQL- aggregation Copyright @ 2019 Oracle and/or its affiliates. 60 / 111
  • 61.
    NoSQL as SQL- aggregation Copyright @ 2019 Oracle and/or its affiliates. 61 / 111
  • 62.
    NoSQL as SQL- aggregation Copyright @ 2019 Oracle and/or its affiliates. 62 / 111
  • 63.
    import mysqlx session =mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': 'fred', 'password': 'fred' }) db = session.get_schema('docstore') col = db.get_collection('all_recs') restaurants = col. nd("cuisine='Italian'"). elds("name, cuisine, borough") .limit(3).execute() for restaurant in restaurants.fetch_all(): print restaurant result = 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.all_recs) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score DESC) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10''').execute() for restaurant in result.fetch_all(): print "%s - %s - %d " % (restaurant[0], restaurant[1], restaurant[2]) session.close() Copyright @ 2019 Oracle and/or its affiliates. 63 / 111
  • 64.
    Output Copyright @ 2019Oracle and/or its affiliates. 64 / 111
  • 65.
    And back toJSON And of course it's possible to return the result as a JSON document: result = session.sql('''select JSON_PRETTY(JSON_ARRAYAGG(JSON_OBJECT( "name", name, "cuisine", cuisine, "score", avg_score))) from (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.all_recs) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score DESC) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 10) as t''').execute() row = result.fetch_one() print row[0] Copyright @ 2019 Oracle and/or its affiliates. 65 / 111
  • 66.
      And back toJSON - output Copyright @ 2019 Oracle and/or its affiliates. 66 / 111
  • 67.
    some extras... The hiddenpart of the iceberg Copyright @ 2019 Oracle and/or its affiliates. 67 / 111
  • 68.
    _id Every documenthas a unique identi er called the document ID. It can be manually assigned when adding a document or generated and assigned to the document automatically ! Since MySQL 8.0.11, the _id is generated by the server. To get the list of automatically generated IDs use the result.getGeneratedIDs() method. The _id is made of 3 parts, all hex encoded: pre x timestamp when the MySQL Server instance was started auto-increment counter Globally unique IDs Optimized for InnoDB storage Examples 00005cc17b700000000000000003 Copyright @ 2019 Oracle and/or its affiliates. 68 / 111
  • 69.
    Document Store FullACID ! It relies on the proven MySQL InnoDB's strength & robustness: Copyright @ 2019 Oracle and/or its affiliates. 69 / 111
  • 70.
    Document Store FullACID ! It relies on the proven MySQL InnoDB's strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2019 Oracle and/or its affiliates. 70 / 111
  • 71.
    Document Store FullACID ! 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. 71 / 111
  • 72.
    Document Store FullACID ! 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. 72 / 111
  • 73.
    Document Store FullACID ! 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. 73 / 111
  • 74.
    Document Store FullACID ! 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. 74 / 111
  • 75.
    What time isit now ? Time for some more examples ! Copyright @ 2019 Oracle and/or its affiliates. 75 / 111
  • 76.
    More fun withThe Walking Dead Copyright @ 2019 Oracle and/or its affiliates. 76 / 111
  • 77.
    Example: All Episodesof Season 1 Copyright @ 2019 Oracle and/or its affiliates. 77 / 111
  • 78.
    Example: All FirstEpisodes of Each Season Copyright @ 2019 Oracle and/or its affiliates. 78 / 111
  • 79.
    Example: Speed UpEpisode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. 79 / 111
  • 80.
    Example: Speed UpEpisode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. 80 / 111
  • 81.
    Example: Speed UpEpisode's Name Lookup Copyright @ 2019 Oracle and/or its affiliates. 81 / 111
  • 82.
    Example: Transaction Copyright @2019 Oracle and/or its affiliates. 82 / 111
  • 83.
    Example: Do morewith SQL List the amount of episodes by season: Copyright @ 2019 Oracle and/or its affiliates. 83 / 111
  • 84.
    Example: Do morewith SQL (2) Episode statistics for each season: Copyright @ 2019 Oracle and/or its affiliates. 84 / 111
  • 85.
    Example: Do morewith SQL (3) Number of days between episodes: Copyright @ 2019 Oracle and/or its affiliates. 85 / 111
  • 86.
    JSON Data validation It'spossible to have constraints between collections (or tables): Copyright @ 2019 Oracle and/or its affiliates. 86 / 111
  • 87.
    JSON Data validation(2) Let's see in SQL how the 2 collections look like: Copyright @ 2019 Oracle and/or its affiliates. 87 / 111
  • 88.
    JSON Data validation(3) Let's add one virtual column in each collection: Copyright @ 2019 Oracle and/or its affiliates. 88 / 111
  • 89.
    JSON Data validation(4) Now we can index the reference and create the constraint: Copyright @ 2019 Oracle and/or its affiliates. 89 / 111
  • 90.
    JSON Data validation(4) Copyright @ 2019 Oracle and/or its affiliates. 90 / 111
  • 91.
    JSON Data validation(5) And now even in pure NoSQL CRUD, we can see the data validation in action: Copyright @ 2019 Oracle and/or its affiliates. 91 / 111
  • 92.
    JSON Schema validation- NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. 92 / 111
  • 93.
    JSON Schema validation- NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. 93 / 111
  • 94.
    JSON Schema validation- NEW 8.0.16 Since MySQL 8.0.16, CHECK constraints are now supported ! Copyright @ 2019 Oracle and/or its affiliates. 94 / 111
  • 95.
    JSON Schema validation(2) - NEW 8.0.17 New JSON_SCHEMA_VALID function: Copyright @ 2019 Oracle and/or its affiliates. 95 / 111
  • 96.
    JSON Schema validation(2) - NEW 8.0.17 New JSON_SCHEMA_VALID function: Copyright @ 2019 Oracle and/or its affiliates. 96 / 111
  • 97.
    JSON Schema validation(3) - NEW 8.0.17 Knowing why thanks to the new JSON_SCHEMA_VALIDATION_REPORT function: Copyright @ 2019 Oracle and/or its affiliates. 97 / 111
  • 98.
    JSON Schema validation(4) - NEW 8.0.17 And nally, mixing JSON_SCHEMA_VALID with CHECK CONSTRAINT: Copyright @ 2019 Oracle and/or its affiliates. 98 / 111
  • 99.
    JSON Schema validation(4) - NEW 8.0.17 And nally, mixing JSON_SCHEMA_VALID with CHECK CONSTRAINT: Copyright @ 2019 Oracle and/or its affiliates. 99 / 111
  • 100.
    what do Igain ? Conclusion Copyright @ 2019 Oracle and/or its affiliates. 100 / 111
  • 101.
    schemaless exible data structure easyto 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. 101 / 111
  • 102.
  • 103.
    8.0 h ps://www.slideshare.net/lefred.descamps/mysql- shell-the-best-dba-tool h ps://github.com/lefred/mysql-shell- mydba hps://github.com/lefred/mysql-shell- innotop More with MySQL Shell & Python Copyright @ 2019 Oracle and/or its affiliates. 103 / 111
  • 104.
    MySQL Shell ReportingFramework - 8.0.16 Now it's also possible to create user-de ned reports directly in the Shell. They can be wri en in Python and called the same way on every operating systems. No need for extra libraries ! More Info: h ps://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-reporting.html h ps://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/ h ps://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-de ned-reports/ Copyright @ 2019 Oracle and/or its affiliates. 104 / 111
  • 105.
    MySQL Shell Extentions& Plugins - 8.0.17 Since 8.0.17, MySQL Shell has been extended to allow you to write your own extensions and plugins ! More Info: h ps://mysqlserverteam.com/mysql-shell-plugins-introduction/ h ps://lefred.be/content/overview-on-mysql-shell-8-0-17-extensions-plugins-and- how-to-write-yours/ Copyright @ 2019 Oracle and/or its affiliates. 105 / 111
  • 106.
    Credits Thank you OlivierDasini for some TV Show examples: h p://dasini.net/blog/2019/04/02/mysql-json-document-store/ Thank you Jesper Wisborg Krogh for inspiration extending the MySQL Shell Copyright @ 2019 Oracle and/or its affiliates. 106 / 111
  • 107.
  • 108.
    Thank you ! Copyright@ 2019 Oracle and/or its affiliates. 108 / 111
  • 109.
  • 110.
    Upgrade to MySQL8.0 It's time to upgrade to MySQL 8.0, the fastest MySQL adoption release ever ! Copyright @ 2019 Oracle and/or its affiliates. 110 / 111
  • 111.
    Session Survey Help usmake the content even be er. Please complete the session survey in the Mobile App. Copyright @ 2019 Oracle and/or its affiliates. 111 / 111