MySQL Document Store - How to use MySQL without SQL

This session was presented at Techorama NL 2024.

MySQL is also a NoSQL JSON Document Store thanks to the X DEV API. Check this session to learn more !

Transcript

2. Frédéric Descamps Community Manager Oracle MySQL Techorama - October 2024 MySQL Document Store: how to use MySQL without SQL MySQL for Developers
3. Who am I ? about. me/ lefred Copyright @ 2024 Oracle and/or its affiliates. 3
4. @lefred MySQL Evangelist using MySQL since version 3.20 devops believer likes living in h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 4
5. MySQL 8.0 / 9.0 Document Store discovery of a new world Copyright @ 2024 Oracle and/or its affiliates. 5
6. NoSQL Document Store Schemaless Copyright @ 2024 Oracle and/or its affiliates. 6
7. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... Copyright @ 2024 Oracle and/or its affiliates. 6
8. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Copyright @ 2024 Oracle and/or its affiliates. 6
9. NoSQL Document Store Schemaless no schema design, no normalization, no foreign keys, no data types, ... very quick initial development Flexible data structure Copyright @ 2024 Oracle and/or its affiliates. 6
10. NoSQL 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 @ 2024 Oracle and/or its affiliates. 6
11. NoSQL 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 @ 2024 Oracle and/or its affiliates. 6
12. NoSQL 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 object-oriented Copyright @ 2024 Oracle and/or its affiliates. 6
13. NoSQL 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 object-oriented JSON Copyright @ 2024 Oracle and/or its affiliates. 6
14. NoSQL 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 object-oriented JSON close to frontend Copyright @ 2024 Oracle and/or its affiliates. 6
15. NoSQL 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 object-oriented JSON close to frontend easy to learn Copyright @ 2024 Oracle and/or its affiliates. 6
16. How DBAs see data Copyright @ 2024 Oracle and/or its affiliates. 7
17. How DBAs see data How Developers see data { { "GNP" "GNP" : : 249704 249704, , "Name" "Name" : : "Belgium" "Belgium", , "government" "government" : : { { "GovernmentForm" "GovernmentForm" : : "Constitutional Monarchy, Federation" "Constitutional Monarchy, Federation", , "HeadOfState" "HeadOfState" : : "Philippe I" "Philippe I" } }, , "_id" "_id" : : "BEL" "BEL", , "IndepYear" "IndepYear" : : 1830 1830, , "demographics" "demographics" : : { { "Population" "Population" : : 10239000 10239000, , "LifeExpectancy" "LifeExpectancy" : : 77.8000030517578 77.8000030517578 } }, , } } Copyright @ 2024 Oracle and/or its affiliates. 7
18. And they still need to do Analytics SQL SQL SQL Copyright @ 2024 Oracle and/or its affiliates. 8
19. ... mmm but...how ? ? SQL SQL SQL Copyright @ 2024 Oracle and/or its affiliates. 9
20. Migrating the data (ETL) SQL SQL SQL ETL: Extraction-Transformation-Load Copyright @ 2024 Oracle and/or its affiliates. 10
21. What if there was a way to provide both SQL and NoSQL on one platform that has proven stability on well know technology with a large Community and a diverse ecosystem ? Copyright @ 2024 Oracle and/or its affiliates. 11
22. RDBMS or NoSQL ? Copyright @ 2024 Oracle and/or its affiliates. 12
23. RDBMS or NoSQL ? Why not both ? Copyright @ 2024 Oracle and/or its affiliates. 12
24. The MySQL Document Store ! Where SQL is now optional ! Copyright @ 2024 Oracle and/or its affiliates. 13
25. Is SQL now optional ?! Copyright @ 2024 Oracle and/or its affiliates. 14
26. Using MySQL Document Store ! SQL SQL SQL Copyright @ 2024 Oracle and/or its affiliates. 15
27. The Solution MySQL Document Store Copyright @ 2024 Oracle and/or its affiliates. 16
28. 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 Compatible with all existing MySQL solutions Copyright @ 2024 Oracle and/or its affiliates. 17
29. Table Columns* _id varbinary(32) doc JSON _json_schema JSON * others if you add indexes Schema Schema - database Collection Table Document Row in table a Record Anatomy of MySQL Document Store Copyright @ 2024 Oracle and/or its affiliates. 18
30. X Protocol Connectors Copyright @ 2024 Oracle and/or its affiliates. 19
31. Installation & Setup MySQL Document Store Copyright @ 2024 Oracle and/or its affiliates. 20
32. Installing MySQL Document Store install MySQL 8. x or 9.0 Copyright @ 2024 Oracle and/or its affiliates. 21
33. Installing MySQL Document Store install MySQL 8. x or 9.0 install MySQL Shell Copyright @ 2024 Oracle and/or its affiliates. 21
34. Installing MySQL Document Store install MySQL 8. x or 9.0 install MySQL Shell install MySQL Connector for your programming language Copyright @ 2024 Oracle and/or its affiliates. 21
35. Installing MySQL Document Store install MySQL 8. x or 9.0 install MySQL Shell install MySQL Connector for your programming language Connector/J for Java php-pecl-mysql-xdevapi for PHP mysql-connector-python for Python, ... Copyright @ 2024 Oracle and/or its affiliates. 21
36. Installing MySQL Document Store install MySQL 8. x or 9.0 install MySQL Shell install MySQL Connector for your programming language Connector/J for Java php-pecl-mysql-xdevapi for PHP mysql-connector-python for Python, ... Simply ensure that your rewall se ings permit connections via port 33060 (X Protocol); no additional installations or plugins are required. Copyright @ 2024 Oracle and/or its affiliates. 21
37. MySQL HeatWave Database Service MySQL HeatWave Database Service on OCI is the only MySQL DBaaS including X Protocol. Copyright @ 2024 Oracle and/or its affiliates. 22
38. Migration from MongoDB to MySQL DS For this example, I am using the well known restaurants collection: $ mongoexport $ mongoexport -c -c restaurants restaurants > > restaurants.json restaurants.json connected to: connected to: 127.0 127.0.0.1 .0.1 exported exported 25359 25359 records records Copyright @ 2024 Oracle and/or its affiliates. 23
39. Migration from MongoDB to MySQL DS For this example, I am using the well known restaurants collection: $ mongoexport $ mongoexport -c -c restaurants restaurants > > restaurants.json restaurants.json connected to: connected to: 127.0 127.0.0.1 .0.1 exported exported 25359 25359 records records JS JS> > util util. .importJson importJson( ('restaurants.json' 'restaurants.json', ,{ {convertBsonOid convertBsonOid: : true true} }) ) Importing from file Importing from file "restaurants.json" "restaurants.json" to collection to collection ` `docstore docstore` `. .` `restaurants restaurants` ` in in MySQL Server at localhost MySQL Server at localhost: :33060 33060 . .. . 25359. 25359.. . 25359 25359 Processed Processed 15.60 15.60 MB MB in in 25359 25359 documents documents in in 0.9976 0.9976 sec sec ( (25 25. .36K documents 36K documents/ /s s) ) Total successfully imported documents Total successfully imported documents 25359 25359 ( (25 25. .36K documents 36K documents/ /s s) ) Copyright @ 2024 Oracle and/or its affiliates. 23
40. Copyright @ 2024 Oracle and/or its affiliates. 24
41. Copyright @ 2024 Oracle and/or its affiliates. 25
42. Copyright @ 2024 Oracle and/or its affiliates. 26
43. Copyright @ 2024 Oracle and/or its affiliates. 27
44. Let's make a query JS > restaurants.find() That's too much records to show in here... let's limit it Copyright @ 2024 Oracle and/or its affiliates. 28
45. Copyright @ 2024 Oracle and/or its affiliates. 29
46. Another example Copyright @ 2024 Oracle and/or its affiliates. 30
47. With a selection lter: Copyright @ 2024 Oracle and/or its affiliates. 31
48. With a selection lter (2): Copyright @ 2024 Oracle and/or its affiliates. 32
49. Syntax slightly di erent than MongoDB Copyright @ 2024 Oracle and/or its affiliates. 33
50. Syntax slightly di erent than MongoDB Copyright @ 2024 Oracle and/or its affiliates. 33
51. And for developers ? Copyright @ 2024 Oracle and/or its affiliates. 34
52. And for developers ? $session $session = = mysql_xdevapi mysql_xdevapi\ \getSession getSession( ("mysqlx://fred:MyP@ssw0rd%@localhost" "mysqlx://fred:MyP@ssw0rd%@localhost") ); ; $schema $schema = = $session $session-> ->getSchema getSchema( ("docstore" "docstore") ); ; $collection $collection = = $schema $schema-> ->getCollection getCollection( ("restaurants" "restaurants") ); ; $results $results = = $collection $collection-> ->find find( ($search $search) )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; ... ... foreach foreach ( ($results $results as as $doc $doc) ) { { echo echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>" "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; ; echo echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>" "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; ; } } Copyright @ 2024 Oracle and/or its affiliates. 35
53. And for developers ? $session $session = = mysql_xdevapi mysql_xdevapi\ \getSession getSession( ("mysqlx://fred:MyP@ssw0rd%@localhost" "mysqlx://fred:MyP@ssw0rd%@localhost") ); ; $schema $schema = = $session $session-> ->getSchema getSchema( ("docstore" "docstore") ); ; $collection $collection = = $schema $schema-> ->getCollection getCollection( ("restaurants" "restaurants") ); ; $results $results = = $collection $collection-> ->find find( ($search $search) )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; ... ... foreach foreach ( ($results $results as as $doc $doc) ) { { echo echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>" "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; ; echo echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>" "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; ; } } Easy, using only CRUD operations ! Copyright @ 2024 Oracle and/or its affiliates. 35
54. And for developers ? $session $session = = mysql_xdevapi mysql_xdevapi\ \getSession getSession( ("mysqlx://fred:MyP@ssw0rd%@localhost" "mysqlx://fred:MyP@ssw0rd%@localhost") ); ; $schema $schema = = $session $session-> ->getSchema getSchema( ("docstore" "docstore") ); ; $collection $collection = = $schema $schema-> ->getCollection getCollection( ("restaurants" "restaurants") ); ; $results $results = = $collection $collection-> ->find find( ($search $search) )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; ... ... foreach foreach ( ($results $results as as $doc $doc) ) { { echo echo "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>" "<tr><td><a href='?id=${doc[_id]}'>${doc[name]}</a></td>"; ; echo echo "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>" "<td>${doc[borough]}</td><td>${doc[cuisine]}</td></tr>"; ; } } Easy, using only CRUD operations ! Not a single SQL statement ! Copyright @ 2024 Oracle and/or its affiliates. 35
55. With import import * *; ; class class Main Main { { public public static static void void main main( (String String args args[ [] ]) ) { { Session Session mySession mySession = = new new SessionFactory SessionFactory( () ) . .getSession getSession( ("mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!" "mysqlx://localhost:33060/docstore?user=resto&password=Passw0rd!") ); ; Schema Schema myDb myDb = = mySession mySession. .getSchema getSchema( ("docstore" "docstore") ); ; Collection Collection myColl myColl = = myDb myDb. .getCollection getCollection( ("restaurants" "restaurants") ); ; DocResult DocResult myDocs myDocs = = myColl myColl. .find find( ("name like :param" "name like :param") ). .limit limit( (1 1) ) . .bind bind( ("param" "param", , "Green%" "Green%") ). .execute execute( () ); ; System System. .out out. .println println( (myDocs myDocs. .fetchOne fetchOne( () )) ); ; mySession mySession. .close close( () ); ; } } } } Copyright @ 2024 Oracle and/or its affiliates. com com. .mysql mysql. .cj cj. .xdevapi xdevapi. . 36
56. Li le Demo $ javac $ javac -classpath -classpath "lib/*" "lib/*" -d -d bin src/Resto.java bin src/Resto.java $ $ java java -classpath -classpath "bin:lib/*" "bin:lib/*" Resto Resto Connecting to database Connecting to database.. ... . Enter a string to search Enter a string to search in in the database: fred the database: fred Searching Searching for for 'fred' 'fred' in in the database the database.. ... . Name: Name: | | Borough: Borough: Cafe Cafe 212 212/Columbia Catering Kitchen - Alfred Lerner Hall /Columbia Catering Kitchen - Alfred Lerner Hall | | Manhattan Manhattan Ferris Booth Commons - Alfred Lerner Hall Ferris Booth Commons - Alfred Lerner Hall | | Manhattan Manhattan Cafe East - Alfred Lerner Hall Cafe East - Alfred Lerner Hall | | Manhattan Manhattan Alfredo Alfredo 100 100 | | Manhattan Manhattan Copyright @ 2024 Oracle and/or its affiliates. 37
57. CRUD operations The computer acronym CRUD (for Create, Read, Update, Delete) designates the four basic operations for data persistence, in particular the storage of information in a database. Copyright @ 2024 Oracle and/or its affiliates. 38
58. CRUD operations on collections Add a document collection collection. .add add( ({ { name name: : 'fred' 'fred', , age age: : 46 46 } }) ) . .add add( ({ { name name: : 'scott' 'scott', , age age: : 47 47 } }) ) . .execute execute( () ) collection collection. .add add( ([ [ { { name name: : 'dimo' 'dimo', , age age: : 50 50 } }, , { { name name: : 'kenny' 'kenny', , age age: : 25 25 } } ] ]) ). .execute execute( () ) Copyright @ 2024 Oracle and/or its affiliates. 39
59. collection collection. .modify modify( ('name = :name' 'name = :name') ) . .bind bind( ('name' 'name', , 'fred' 'fred') ) . .set set( ('age' 'age', , 43 43) ) . .sort sort( ('name ASC' 'name ASC') ) . .limit limit( (1 1) ) . .execute execute( () ) collection collection. .modify modify( ('name = :name' 'name = :name') ) . .bind bind( ('name' 'name', , 'fred' 'fred') ) . .patch patch( ({ { age age: : 43 43, , active active: : false false } }) ) . .sort sort( ('name DESC' 'name DESC') ) . .limit limit( (1 1) ) . .execute execute( () ) CRUD operations on collections Modify a document Copyright @ 2024 Oracle and/or its affiliates. 40
60. CRUD operations on collections Delete of a document collection collection. .remove remove( ('name = :name' 'name = :name') ) . .bind bind( ('name' 'name', , 'fred' 'fred') ) . .sort sort( ('age ASC' 'age ASC') ) . .limit limit( (1 1) ) . .execute execute( () ) Copyright @ 2024 Oracle and/or its affiliates. 41
61. MySQL Document Store Objects Summary Copyright @ 2024 Oracle and/or its affiliates. 42
62. All you need to know about the X Dev API is here: h ps://dev.mysql.com/doc/x-devapi-userguide/en/crud-operations-overview.html Copyright @ 2024 Oracle and/or its affiliates. 43
63. MySQL Document Store is full ACID compliant we do care about your data Copyright @ 2024 Oracle and/or its affiliates. 44
64. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: Copyright @ 2024 Oracle and/or its affiliates. 45
65. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: innodb_ ush_log_at_trx_commit = 1 Copyright @ 2024 Oracle and/or its affiliates. 45
66. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON Copyright @ 2024 Oracle and/or its affiliates. 45
67. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 Copyright @ 2024 Oracle and/or its affiliates. 45
68. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Copyright @ 2024 Oracle and/or its affiliates. 45
69. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Durability is important. Copyright @ 2024 Oracle and/or its affiliates. 45
70. Document Store Full ACID ! The solution relies on the proven MySQL InnoDB strength & robustness: innodb_ ush_log_at_trx_commit = 1 innodb_doublewrite = ON sync_binlog = 1 transaction_isolation = REPEATABLE-READ|READ-COMMITTED|... Durability is important. We do care about your data ! Copyright @ 2024 Oracle and/or its affiliates. 45
71. ACID - Support of Transactions Copyright @ 2024 Oracle and/or its affiliates. 46
72. ACID - Support of Transactions Copyright @ 2024 Oracle and/or its affiliates. 47
73. OK we have Doc Store, CRUD & ACID but what makes MySQL Document Store unique? Copyright @ 2024 Oracle and/or its affiliates. 48
74. Challenge: list the best restaurant of each type of food and show the top 10, with the best one rst ! don't forget that all these restaurants are just JSON documents Copyright @ 2024 Oracle and/or its affiliates. 49
75. Copyright @ 2024 Oracle and/or its affiliates. 50
76. NoSQL as SQL - aggregation Copyright @ 2024 Oracle and/or its affiliates. 51
77. NoSQL as SQL - aggregation Copyright @ 2024 Oracle and/or its affiliates. 52
78. NoSQL as SQL - aggregation Copyright @ 2024 Oracle and/or its affiliates. 53
79. NoSQL as SQL - aggregation Copyright @ 2024 Oracle and/or its affiliates. 54
80. NoSQL or SQL You have the possibility to write clean and neat code: Copyright @ 2024 Oracle and/or its affiliates. 55
81. NoSQL or SQL You have the possibility to write clean and neat code: $results $results = = $collection $collection-> ->find find( ('cuisine like "italian"' 'cuisine like "italian"') )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; Copyright @ 2024 Oracle and/or its affiliates. 55
82. NoSQL or SQL You have the possibility to write clean and neat code: $results $results = = $collection $collection-> ->find find( ('cuisine like "italian"' 'cuisine like "italian"') )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; And use SQL only when it's necessary: Copyright @ 2024 Oracle and/or its affiliates. 55
83. NoSQL or SQL You have the possibility to write clean and neat code: $results $results = = $collection $collection-> ->find find( ('cuisine like "italian"' 'cuisine like "italian"') )-> ->execute execute( () )-> ->fetchAll fetchAll( () ); ; And use SQL only when it's necessary: $results $results = = $session $session-> ->sql sql( (' select * from (with cte1 as (select doc->>"$.name" as name, ' select * from (with cte1 as (select doc->>"$.name" as name, doc->>"$.cuisine" as cuisine, ( select avg(score) from json_table(doc, "$.grades[*]" doc->>"$.cuisine" as cuisine, ( select avg(score) from json_table(doc, "$.grades[*]" columns (score int path "$.score")) as r) as avg_score from restaurants) columns (score int path "$.score")) as r) as avg_score from restaurants) select *, row_number() over ( partition by cuisine order by avg_score desc) as `rank` select *, row_number() over ( partition by cuisine order by avg_score desc) as `rank` from cte1 order by `rank`, avg_score desc) b where `rank`=1' from cte1 order by `rank`, avg_score desc) b where `rank`=1') )-> ->execute execute( () ); ; Copyright @ 2024 Oracle and/or its affiliates. 55
84. All in the same MySQL X Session ! Copyright @ 2024 Oracle and/or its affiliates. 56
85. You can mix NoSQL & SQL as you want: Copyright @ 2024 Oracle and/or its affiliates. 57
86. Best of Both Worlds: JSON_TABLE What are the maximum 10 ratings ever given to a restaurant? Copyright @ 2024 Oracle and/or its affiliates. 58
87. Best of Both Worlds: JSON_TABLE What are the maximum 10 ratings ever given to a restaurant? Cool... but my app only processes JSON ! Copyright @ 2024 Oracle and/or its affiliates. 58
88. Best of Both Worlds: JSON_TABLE (2) With JSON output: Copyright @ 2024 Oracle and/or its affiliates. 59
89. Best of Both Worlds: JSON_TABLE (3) Remember the challenge ? Copyright @ 2024 Oracle and/or its affiliates. 60
90. Best of Both Worlds: JSON_TABLE (3) Remember the challenge ? Copyright @ 2024 Oracle and/or its affiliates. 60
91. Best of Both Worlds: JOINS You know now the di erence between a collection and a table: Copyright @ 2024 Oracle and/or its affiliates. 61
92. Best of Both Worlds: JOINS You know now the di erence between a collection and a table: Copyright @ 2024 Oracle and/or its affiliates. 61
93. Best of Both Worlds: JOINS (2) And of course with MySQL Document Store we can also JOIN them together! Copyright @ 2024 Oracle and/or its affiliates. 62
94. Best of Both Worlds: JOINS (2) And of course with MySQL Document Store we can also JOIN them together! Copyright @ 2024 Oracle and/or its affiliates. 62
95. Never satisfied, always wanting more! Copyright @ 2024 Oracle and/or its affiliates. 63
96. Never satis ed, always wanting more! Copyright @ 2024 Oracle and/or its affiliates. 64
97. From total mess (schemaless) Copyright @ 2024 Oracle and/or its affiliates. 65
98. From total mess (schemaless) To organized "mess" Copyright @ 2024 Oracle and/or its affiliates. 65
99. Cleaning the mess with some rules Best of both worlds: mixing constraints and JSON validation: Copyright @ 2024 Oracle and/or its affiliates. 66
100. Cleaning the mess with some rules Best of both worlds: mixing constraints and JSON validation: Result in action: Copyright @ 2024 Oracle and/or its affiliates. 66
101. JSON Validation: best practice It's nicer to validate the JSON at the collection level: Copyright @ 2024 Oracle and/or its affiliates. 67
102. JSON Validation: best practice Copyright @ 2024 Oracle and/or its affiliates. 68
103. JSON Validation: best practice And now we see an usage's illustration of _json_schema: Copyright @ 2024 Oracle and/or its affiliates. 68
104. Another Example with validation Copyright @ 2024 Oracle and/or its affiliates. 69
105. Another Example with validation (2) Copyright @ 2024 Oracle and/or its affiliates. 70
106. Another Example with validation (2) Copyright @ 2024 Oracle and/or its affiliates. 71
107. Acceleration with MySQL HeatWave MySQL HeatWave Database Service supports the Dev X API allowing the X Protocol, but if you want to also bene t from HeatWave Cluster, query accelerator, you need to make a change to your collection: MySQL MySQL > > ALTER ALTER TABLE TABLE restaurants restaurants MODIFY MODIFY _id _id CHAR CHAR( (28 28) ) CHARSET CHARSET latin1 latin1 GENERATED ALWAYS GENERATED ALWAYS AS AS ( (JSON_UNQUOTE JSON_UNQUOTE( (JSON_EXTRACT JSON_EXTRACT( (` `doc doc` `, ,_utf8mb4 _utf8mb4'$._id' '$._id') )) )) ) STORED STORED NOT NOT NULL NULL; ; Copyright @ 2024 Oracle and/or its affiliates. 72
108. Acceleration with MySQL HeatWave (2) Let's have a look at our collection: JS JS > > db db. .restaurants restaurants. .count count( () ) 2821923 2821923 Copyright @ 2024 Oracle and/or its affiliates. 73
109. Acceleration with MySQL HeatWave (2) Let's have a look at our collection: JS JS > > db db. .restaurants restaurants. .count count( () ) 2821923 2821923 We have 2,821,923 documents ! Copyright @ 2024 Oracle and/or its affiliates. 73
110. SQL SQL > > select select doc doc- ->> >>"$.borough" "$.borough" borough borough, , count count( (* *) ) tot tot, , max max( (length length( (doc doc- ->> >>"$.name" "$.name") )) ) longest_name longest_name, , min min( (length length( (doc doc- ->> >>"$.name" "$.name") )) ) shortest_name shortest_name, , round round( (avg avg( (length length( (doc doc- ->> >>"$.name" "$.name") )) ), ,2 2) ) avg_name_length avg_name_length from from restaurants restaurants where where doc doc- ->> >>"$.cuisine" "$.cuisine" collate collate utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci like like 'belgian' 'belgian' group group by by borough borough order order by by tot tot desc desc limit limit 10 10; ; . .. .. .. . 10 10 rows rows in in set set ( (1.1713 1.1713 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 74
111. Acceleration with MySQL HeatWave (3) We can now load the collection to HeatWave Cluster: MySQL MySQL > > CALL CALL sys sys. .heatwave_load heatwave_load( (JSON_ARRAY JSON_ARRAY( ('docstore' 'docstore') ), , NULL NULL) ); ; . .. .. . + +-------------------------------------------------------------------------------+ -------------------------------------------------------------------------------+ | | LOAD LOAD SUMMARY SUMMARY | | + +-------------------------------------------------------------------------------+ -------------------------------------------------------------------------------+ | | | | | | SCHEMA SCHEMA TABLES TABLES TABLES TABLES COLUMNS COLUMNS LOAD LOAD | | | | NAME LOADED FAILED LOADED DURATION NAME LOADED FAILED LOADED DURATION | | | | ------ ------ ------ ------- -------- | ------ ------ ------ ------- -------- | | | ` `docstore docstore` ` 1 1 0 0 2 2 11.41 11.41 s s | | | | | | + +-------------------------------------------------------------------------------+ -------------------------------------------------------------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 75
112. Acceleration with MySQL HeatWave (4) For example with 2,162,520 documents we go from 1.1713 seconds to 0.1922 seconds when using HeatWeave Cluster. . .. .. .. . 10 10 rows rows in in set set ( (0.1922 0.1922 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 76
113. MySQL Document Store is compatible with all MySQL Architectures Copyright @ 2024 Oracle and/or its affiliates. 77
114. Ready for more ? MySQL Document Store on steroïd, the future is called: MRS Copyright @ 2024 Oracle and/or its affiliates. 78
115. MySQL REST Service (MRS) The MySQL REST SERVICE (MRS) adds full support for JSON/Relational Duality by enabling fast and secure HTTPS access for your MySQL data. Copyright @ 2024 Oracle and/or its affiliates. 79
116. MySQL REST Service (MRS) (2) Copyright @ 2024 Oracle and/or its affiliates. 80
117. MySQL REST Service (MRS) (3) Do you remember the JOIN between our collection and two tables ? Copyright @ 2024 Oracle and/or its affiliates. 81
118. MySQL REST Service (MRS) (3) Do you remember the JOIN between our collection and two tables ? Let's create a VIEW: Copyright @ 2024 Oracle and/or its affiliates. 81
119. MySQL REST Service (MRS) (4) Copyright @ 2024 Oracle and/or its affiliates. 82
120. MySQL REST Service (MRS) (5) Copyright @ 2024 Oracle and/or its affiliates. 83
121. Conclusion what do I gain ? Copyright @ 2024 Oracle and/or its affiliates. 84
122. RDMBS Data integrity ACID Compliant Transactions SQL Conclusion This is the best of the two worlds in one product ! Copyright @ 2024 Oracle and/or its affiliates. 85
123. RDMBS Data integrity ACID Compliant Transactions SQL NoSQL schemaless exible data structure easy to start (CRUD) Conclusion This is the best of the two worlds in one product ! Copyright @ 2024 Oracle and/or its affiliates. 85
124. Conclusion (2) And with MRS, MySQL serves data to client applications via a HTTPS REST interface. Copyright @ 2024 Oracle and/or its affiliates. 86
125. NoSQL + SQL = MySQL Copyright @ 2024 Oracle and/or its affiliates. 87
126. NoSQL + SQL = MySQL Copyright @ 2024 Oracle and/or its affiliates. 87
127. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 88
128. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 89
129. Copyright @ 2024 Oracle and/or its affiliates. 90