Download to read offline
![List / Drop a Collection db.getCollections() [ <Collection:items_table>, <Collection:meetup_collection> ] -- drop collection db.dropCollection('meetup_collection') db.getCollections() [ <Collection:items_table> ]](https://image.slidesharecdn.com/nosqlinsql-lioraltarescu-191128084540/75/Working-with-NoSQL-in-a-SQL-Database-XDevApi-23-2048.jpg)
Uploaded byLior Altarescu
Working with NoSQL in a SQL Database (XDevApi)
The document discusses X DevAPI, which allows MySQL to be used as both a relational and non-relational (schema-less) database. It provides NoSQL and SQL functionality in a single database using CRUD operations. The presenter worked with X DevAPI at Wix, where they initially used ProxySQL for failover but it did not support X DevAPI. They were able to use InnoDB cluster instead to gain its automatic failover capabilities while taking advantage of X DevAPI.
Working with NoSQL in a SQL Database (XDevApi)
- 1. Working with NoSQL ina SQL Database (XDevApi) Lior Altarescu Lioral@wix.com linkedin/lioraltarescu github.com/lioraltarescu
- 2. ▪ 7 yearsa DBA… ▪ Oracle, MySQL, MongoDB, Elasticsearch, Cassandra, and PostgreSQL ▪ Proud data nerd
- 3. TODAY What X DevAPIis Nosql and SQL in a single database CRUD operations Our experience with it
- 4.
- 5. X 130M One bigJSON
- 6. X 130M One bigJSON Team (microservice) #1 Team (microservice) #2
- 7.
- 8. How developers seedata How DBAs see data
- 9.
- 10.
- 11.
- 12. X DevAPI MySQL C++ Java .Net Node.js Python PHP XPlugin Async CRUD operations
- 13. X DevAPI MySQL C++ Java .Net Node.js Python PHP XPlugin Async CRUD operations session Transparently scale to a multiple server environment
- 14.
- 15. MySQL It’s a document datastore. Schemaless NoSQL, fullyACID Keeps data integrity Allows SQL & CRUD Max Document size 1G Very secure GTID Replication transaction_isolation
- 16. MySQL ID #1 JSON Collection A Acollection of… documents.
- 17. NoSQL and SQL ina single database 02
- 18. Storage Optimizer MySQL connection handling Application MySQL Connector /ODBC Classic MySQL protocol MySQL
- 19. Application MySQL Connector /ODBCX DevAPI X DevAPI X DevAPI SQL result MySQL Storage Optimizer MySQL connection handling X Plugin Classic MySQL protocolX protocol
- 20. Application MySQL Connector /ODBC MySQL Shell Node.js Application Windows Application JavaScript / Python MySQL C / Node.js MySQL connector / Net X DevAPI X DevAPI X DevAPI SQL result MySQL Storage Optimizer MySQL connection handlingX PluginX Plugin Classic MySQL protocolX protocol
- 21. CRUD operations with XDevAPI 03
- 22.
- 23. List / Dropa Collection db.getCollections() [ <Collection:items_table>, <Collection:meetup_collection> ] -- drop collection db.dropCollection('meetup_collection') db.getCollections() [ <Collection:items_table> ]
- 24. A New Document with SQL: insertinto items_table values(‘phone’,21443,’blue’); insert into items_table values(‘phone’,567734,’pink’); with X DevAPI: db.items_table.add({"name":"phone","price":21443,"color":"blue"}). add({"name":"tablet","price":567734,"color":"pink"})
- 25. A New Document with SQL: insertinto items_table values(‘phone’,21443,’blue’); insert into items_table values(‘phone’,567734,’pink’); with X DevAPI: db.items_table.add({"name":"phone","price":21443,"color":"blue"}). add({"name":"tablet","price":567734,"color":"pink"}) //OR db.items_table.add({"name":"phone","price":21443,"color":"blue"}) db.items_table.add({"name":"tablet","price":567734,"color":"pink"})
- 26. Find a Document with SQL: select* from items_table where price > 25000; with X DevAPI: db.items_table.find("price>25000") db.items_table.find("price>:param").bind(‘param’,’25000’)
- 27. Modify a Document with SQL: updateitems_table set price=11111 where name = 'phone’; with X DevAPI: db.items_table.modify("name=phone").set("price","11111") db.items_table.find("name='phone'") { "_id": "00005dc476b00000000000000009", "name": "phone", "color": "blue", "price": 11111 }
- 28. Remove a Document with SQL: deletefrom items_table where name = 'phone' with X DevAPI: db.items_table.remove("name='phone'")
- 29. A Full Data Transaction varmyColl = db.createCollection('dummy_collection'); session.startTransaction(); myColl.add({name: 'Rohit', age: 18, height: 1.76}).execute(); session.setSavepoint('sp1') myColl.add({name: 'Misaki', age: 24, height: 1.65}).execute(); myColl.add({name: 'Leon', age: 39, height: 1.9}).execute(); session.rollbackTo('sp1') session.commit();
- 30.
- 31.
- 33.
- 34. Does NOT support theX protocol
- 35. Mysql connectors asa replacement to proxySQL ? Connector XdevAPI Failover Load Balancing Java ✓ ✓ ✓ Nodejs ✓ ✓ ✘ Python ✓ ✓ ✘ Net ✓ ✓ ✓ PHP ✓ ✓ ✓ C++ ✓ ✘ ✘
- 36. Play the WaitingGame Solution #1
- 38.
- 39. Mysql InnoDB cluster Mysql replication Primaryelection when the current primary failure VS Need to have a script or additional infrastructure for automatic failover Advances AdminAPI for administrative purpose No AdminAPI, regular commands Easy to scale with innodb cluster requires some effort to scale Not mature Solution Mature Solution VS
- 41. X DevAPI allowsyou to work with MySQL as a document store (both relational and schema-less data). Takeaways
- 42. X DevAPI allowsyou to work with MySQL as a document store (both relational and schema-less data). Takeaways ProxySQL doesn’t support it, but we got around it with InnoDB cluster.
- 43.
- 44. Thank You Lioral@wix.com linkedin/lioraltarescugithub.com/lioraltarescu