Download as PDF, PPTX
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Export data for importing into MySQL • This example is inspired by @datacharmer's work: https://www.slideshare.net/datacharmer/mysql-documentstore • And also: https://www.slideshare.net/lefred.descamps/mysql-document-store-how-to-replace-a- nosql-database-by-mysql-without-effort-but-with-a-lot-gains $ mongo --quiet --eval 'DBQuery.shellBatchSize=30000; db.restaurants.find().shellPrint()' | perl -pe 's/(?:ObjectId|ISODate)(("[^"]+"))/ $1/g' > DocStore/demo/all_recs.json](https://image.slidesharecdn.com/docstoredemo-181002181955/75/MySQL-NoSQL-JSON-JS-Python-Document-Store-demo-6-2048.jpg)
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | • Now the data is imported, let's query it: db.restaurants.find() db.restaurants.find().limit(1) db.restaurants.find().fields(["_id","na me","cuisine"]).limit(2) • Using sql still? For whatever reason: session.sql("show create table restaurants") session.sql("select * from restaurants") session.sql("select * from restaurants limit 2") • Let's update an attribute of the collection, using the id: db.restaurants.modify("_id='55cba2476c 522cafdb053add'").set("cuisine","Itali an") db.restaurants.find("_id='55cba2476c52 2cafdb053add'") Querying data](https://image.slidesharecdn.com/docstoredemo-181002181955/75/MySQL-NoSQL-JSON-JS-Python-Document-Store-demo-8-2048.jpg)
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | • Getting data out of the Collection, dynamically: ALTER TABLE restaurants ADD COLUMN borough VARCHAR(20) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.bo rough'))) VIRTUAL; • same as: ALTER TABLE restaurants ADD COLUMN borough VARCHAR(20) GENERATED ALWAYS AS (doc->>"$.borough") VIRTUAL; • Using MySQL 8.0 CTE: 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 restaurants) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 30; Querying data (cont.)](https://image.slidesharecdn.com/docstoredemo-181002181955/75/MySQL-NoSQL-JSON-JS-Python-Document-Store-demo-10-2048.jpg)
Uploaded byKeith Hollman
MySQL NoSQL JSON JS Python "Document Store" demo
The document provides a guide for developers on installing and using MySQL Document Store and MongoDB. It outlines installation steps, data import/export procedures, and examples of querying and managing data in both systems. Additionally, it includes links to further resources and examples for deeper understanding.
MySQL NoSQL JSON JS Python "Document Store" demo
- 1. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Document Store dirigido a Desarrolladores Keith Hollman MySQL Principal Solution Architect keith.hollman@oracle.com DEMO
- 2. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes 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 upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
- 3. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Install MySQL 8.0 $ export PATH=$PATH:/usr/local/mysql/mysql-shell-commercial-8.0.11-linux- glibc2.12-x86-64bit/bin:/usr/local/mysql/mysql-commercial-8.0.11-linux- glibc2.12-x86_64/bin $ cd /usr/local/mysql/mysql-commercial-8.0.11-linux-glibc2.12-x86_64/bin $ mysqld --defaults-file=my.cnf --initialize-insecure $ mysqld --defaults-file=my.cnf &
- 4. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Download & install Mongo $ tar zxvf mongodb-linux-x86_64-4.0.0.tar.gz $ cd /usr/local/mongodb-linux-x86_64-4.0.0 $ export PATH=$PATH:/usr/local/mongodb-linux-x86_64-4.0.0/bin $ mkdir -p /opt/mongo/data mongo/log $ vi mongod.conf systemLog: destination: file path: "/opt/mongo/log/mongod.log" logAppend: true storage: dbPath: "/opt/mongo/data" ... $ mongod --config /usr/local/mongodb-linux-x86_64-4.0.0/mongod.conf
- 5. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Download data to import into mongo • https://docs.mongodb.com/manual/tutorial/geospatial-tutorial/index.html • File to be downloaded is: – https://raw.githubusercontent.com/mongodb/docs- assets/geospatial/restaurants.json $ mongoimport DocStore/demo/restaurants.json -c restaurants
- 6. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Export data for importing into MySQL • This example is inspired by @datacharmer's work: https://www.slideshare.net/datacharmer/mysql-documentstore • And also: https://www.slideshare.net/lefred.descamps/mysql-document-store-how-to-replace-a- nosql-database-by-mysql-without-effort-but-with-a-lot-gains $ mongo --quiet --eval 'DBQuery.shellBatchSize=30000; db.restaurants.find().shellPrint()' | perl -pe 's/(?:ObjectId|ISODate)(("[^"]+"))/ $1/g' > DocStore/demo/all_recs.json
- 7. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | • Before we start loading data, let's tail the general_log: $ cd /opt/mysql/8011/data $ tail -100f tail -100f khollman_8011.log • Create the environment within MySQL: $ cd DocStore/demo $ mysqlsh --uri root@localhost session.createSchema('test') use test db.createCollection('restaurants') py import json import re with open ('all_recs.json', 'r') as json_data: for line in json_data: skip = re.match('Type', line) if not skip: rec = json.loads(line) db.restaurants.add(rec).execute() Loading data into MySQL
- 8. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | • Now the data is imported, let's query it: db.restaurants.find() db.restaurants.find().limit(1) db.restaurants.find().fields(["_id","na me","cuisine"]).limit(2) • Using sql still? For whatever reason: session.sql("show create table restaurants") session.sql("select * from restaurants") session.sql("select * from restaurants limit 2") • Let's update an attribute of the collection, using the id: db.restaurants.modify("_id='55cba2476c 522cafdb053add'").set("cuisine","Itali an") db.restaurants.find("_id='55cba2476c52 2cafdb053add'") Querying data
- 9. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Querying data (cont.) • We're ACID, and can still do transactions: session.startTransaction() db.restaurants.remove("_id='55cba2476c522cafdb053add'") db.restaurants.find("_id='55cba2476c522cafdb053add'") db.restaurants.modify("_id ='55cba2476c522cafdb053ade'").set("name", "The Hybrid Lunch") db.restaurants.find("_id = '55cba2476c522cafdb053ade'") session.rollback() db.restaurants.find("_id='55cba2476c522cafdb053add'") db.restaurants.find("_id = '55cba2476c522cafdb053ade'") • Using SQL and formatting: SELECT doc->>"$.name" AS name, doc->>"$.cuisine" AS cuisine FROM restaurants where doc->>"$.cuisine" is not null;
- 10. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | • Getting data out of the Collection, dynamically: ALTER TABLE restaurants ADD COLUMN borough VARCHAR(20) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.bo rough'))) VIRTUAL; • same as: ALTER TABLE restaurants ADD COLUMN borough VARCHAR(20) GENERATED ALWAYS AS (doc->>"$.borough") VIRTUAL; • Using MySQL 8.0 CTE: 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 restaurants) SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score) AS `rank` FROM cte1 ORDER BY `rank`, avg_score DESC LIMIT 30; Querying data (cont.)
- 11. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | More examples & Information • https://elephantdolphin.blogspot.com/2018/06/mongodb-versus-mysql- document-store.html • https://mysqlserverteam.com/mysql-8-0-from-sql-tables-to-json- documents-and-back-again/ • https://www.mysql.com/news-and-events/web-seminars/nosql- development-for-mysql-document-store-using-java/
- 12. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Preguntas?
- 13. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. |