MySQL Document Store

In this episode, Lois Houston and Nikita Abraham are joined by MySQL Developer Advocate Scott Stroz to talk about MySQL Document Store, a NoSQL solution built on top of MySQL.   Oracle MyLearn: https://mylearn.oracle.com/   Oracle University Learning Community: https://education.oracle.com/ou-community   MySQL: https://dev.mysql.com/doc/   Oracle MySQL Blog: https://blogs.oracle.com/mysql/   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   Twitter: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   ---------------------------------------------------------   Episode Transcript:   00;00;00;00 - 00;00;38;19 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let's get started! Hello and welcome to the Oracle University Podcast. I'm Lois Houston, Director of Product Innovation and Go to Market Programs with Oracle University. And with me is Nikita Abraham, Principal Technical Editor.   00;00;38;22 - 00;00;59;15 Hi, everyone! For the last two weeks, we've been talking about MySQL and NoSQL. And in today's special episode, we're going to dive a little deeper and focus on MySQL Document Store with MySQL Developer Advocate Scott Stroz. Hi, Scott! Thanks for being here today. Why don't you tell us a little more about yourself? Hi, Niki. Hi, Lois.   00;00;59;19 - 00;01;16;10 I'm happy to be here with you guys. Like you said, I'm a developer advocate for MySQL. I've been a software developer for over 20 years. In that time frame, MySQL is the only thing in my development stack that hasn't changed. I used MySQL in my first job as a web developer, and I still use it today.   00;01;16;12 - 00;01;41;26 And for those who may not know, the best way to describe what a developer advocate does is our job is to make developers better at their job. Scott, we discussed NoSQL last week, but for anyone who missed that episode, can you give us a high-level explanation of what NoSQL means? Before I can explain NoSQL, we should probably go over what we mean by a relational database.   00;01;41;27 - 00;02;06;10 In a relational database, data is stored in tables. Each table consists of multiple columns, and each column holds a specific data type - a string, a number, a date, etc. In many cases, the data in one table relates to data in another table. This is where the relational part comes from and data is stored in rows or records. In a relational database, data is often very structured.   00;02;06;12 - 00;02;31;29 SQL or structured query language is used to retrieve, update, add, or delete rows from the database, and NoSQL database at its most basic level is a storage mechanism that does not use the table structure I just mentioned. Data is often stored as JSON documents, as a blob of text. Our audience may find it interesting that NoSQL does not necessarily mean there is no SQL used at all.   00;02;32;01 - 00;02;58;25 In some cases, NoSQL actually stands for not only SQL. Interesting. So, what are JSON documents? JSON is an acronym for JavaScript Object Notation and it is a textual representation of a data structure. JSON objects are wrapped in curly braces and consist of key-value pairs. The values can be simple, such as strings or numbers, or they can also be other JSON objects or arrays.   00;02;58;28 - 00;03;21;09 JSON arrays are wrapped in brackets and consist of comma-separated values that can be simple values again, such as numbers or strings. But they can also be other JSON objects or other arrays. This means that data in JSON objects can be nested with many levels. The best thing about JSON is that it's ubiquitous and can be used in almost any programing language.   00;03;21;11 - 00;03;41;21 I say almost every because I've not used every programing language. So, I'm covering myself just in case there's one out there that doesn't have JSON support. That's pretty good. Okay. It's easy to pick up on how to read it as well. When I first started using JSON, it was like trying to read The Matrix. But now I can read JSON just as easy as I can read a book.   00;03;41;22 - 00;04;03;08 Why would a developer choose to use a NoSQL solution? Can you give us a few examples of that? That is a great question, Niki. When starting out a new project, when a data structure doesn't exist, it may make sense to use a NoSQL solution. In other words, if the schema changes frequently, it may make sense not to have a schema.   00;04;03;10 - 00;04;22;25 Then, once the scheme is matured, the data can be parsed out into a relational database model. I come from the school of thought that all data should be in tables and columns with the proper relationships defined and be very structured. But here's the thing that took me a while to accept. Not all data is structured and not all data needs to be related to other data.   00;04;23;00 - 00;04;49;12 Things like application configuration or user preferences most likely don't need to be stored in a relational database and may work best being stored as JSON. One of the biggest uses of storing JSON is ingesting data from third-party sources. Many applications use external APIs to retrieve data. In those cases, we have no control over the schema that's used for that data.   00;04;49;15 - 00;05;08;28 In trying to account for changes in the schema that will inevitably come is going to be a difficult task. So, storing that data in JSON makes a lot more sense. That makes sense. And then you can handle the JSON as you need to. Okay, let's get to our main topic of discussion for today. What is MySQL Document Store?   00;05;09;00 - 00;05;35;09 MySQL Document Store is a NoSQL implementation built on top of MySQL. JSON documents are stored in a MySQL database table using the InnoDB storage engine. CRUD operations - create, retrieve, update, and delete - are abstracted from the developer through an easy-to-use API. Application developers, whether it's web applications, mobile applications, or native operating system applications, communicate with MySQL Document Store over the X-protocol, which uses port 33060 instead of the standard port 3306.   00;05;35;11 - 00;06;00;10 The nomenclature of NoSQL databases differs from relational databases, right? Can you explain some of the basic terms that are used? Developers who come from a relational database background may initially be confused by some of the terms used to describe the structure where the documents are stored.   00;06;00;12 - 00;06;23;04 I know I was. We use three main terms to describe the structure of MySQL document store – schema, collection, and document. In relational database parlance, a schema would be akin to a database. A collection would be the same as a table, and a document, the actual JSON that we're storing, would be like a row in that table.   00;06;23;07 - 00;06;56;07 So, what happens under the covers when using MySQL Document Store? So, any time we use the document store API, the commands are turned into SQL commands that are then executed on the database server. When developers use the MySQL Document Store API to create a new schema, behind the scenes, MySQL creates a new database, which should be the same as running a SQL query to create a new database. When a new collection is created, MySQL creates a new table in the database using a create table query, and it adds three columns to that table.   00;06;56;09 - 00;07;24;09 The first is _id. This column serves as the primary key when a document is saved to the database, and the key named _id is not provided. MySQL autogenerates the id, saves it to this column, and then also injects it into the JSON document. The next column is doc. This column stores the JSON documents using the JSON data type. And then the last column is _json_schema.   00;07;24;12 - 00;07;57;09 And it's used to validate the schema of documents that are added to the collection. CRUD operations follow the same process. For instance, when we make a call to the API to retrieve documents, on the backend, that command is converted into a SELECT statement using native JSON functions to return the document. If developers want to see what commands are executed when using MySQL Document Store, they can enable the general log setting and then view the log after executing API commands.   00;07;57;12 - 00;08;25;29 Are you attending Oracle CloudWorld 2023? Learn from experts, network with peers, and find out about the latest innovations when Oracle CloudWorld returns to Las Vegas from September 18 through 21. CloudWorld is the best place to learn about Oracle solutions from the people who build and use them. In addition to your attendance at CloudWorld, your ticket gives you access to Oracle MyLearn and all of the cloud learning subscription content as well as three free certification exam credits.   00;08;26;03 - 00;08;53;11 This is valid from the week you register through 60 days after the conference. So, what are you waiting for? Register today! Learn more about Oracle CloudWorld at www.oracle.com/cloudworld. Welcome back! Scott, just before the break, you mentioned something about schema validation. Isn't being schema-less one of the advantages of using a NoSQL solution?   00;08;53;15 - 00;09;16;22 Being schema-less is one of the features of NoSQL databases that developers like more than others. There may be times when we must ensure that documents added to a collection match a certain syntax or schema. For example, we may want to ensure that documents always have a specific key or that a particular key, if it exists, is numeric or some other data type.   00;09;16;24 - 00;09;38;20 When the collection is created, we can define those rules using a JSON object with a specific syntax. On the backend, MySQL will create a check constraint using that JSON and any time a document is saved to a collection, it's validated to ensure it matches the rules or schema we define. If the document does not adhere to that schema, MySQL will throw an error.   00;09;38;22 - 00;10;00;13 What do developers need to do to start using MySQL Document Store.  In terms of configuring MySQL? They don't need to do anything. The X-plugin, which is what's used for communication between the server and the client, has been installed by default since version 8.0.1. So, if they're using a newer version of MySQL 8, they already have access to Document Store.   00;10;00;15 - 00;10;24;28 You may need to make some changes to the network infrastructure to allow traffic over port 33060, but for a network administrator, that should be easy to accomplish. MySQL Document Store is also available on all editions. It's available in Enterprise Edition and the Community Edition as well. And I should note that Oracle Cloud Infrastructure is currently the only cloud provider supporting MySQL Document Store for their MySQL cloud implementations.   00;10;25;00 - 00;10;48;27 Scott, what programing languages are supported for use with MySQL Document Store? There are quite a few languages that are supported. We have connectors or SDKs, as some people call them, or Java, which also works with other Java-based languages, such as Groovy and Kotlin. We also have connectors for C++, Python, PHP, .Net, Node.js and MySQL Shell.   00;10;49;00 - 00;11;14;18 Our listeners have probably heard of most of these with the exception of MySQL Shell. What is that? MySQL Shell is a command line interface that allows us to connect to and manage MySQL database instances. We can use it to create document store schemas and collections easily, but it can do so much more. We can manage to configure MySQL instances, including creating and configuring server replication and clustering.   00;11;14;20 - 00;11;39;15 It even offers a sandbox feature where we can quickly spin up MySQL instances for testing, replication, and clustering configuration without the need to stand up full MySQL server instances. There are three modes in MySQL Shell. By default, MySQL Shell starts in JavaScript mode where the commands we use follow JavaScript syntax. There is a Python mode where the commands we use follow Python syntax.   00;11;39;17 - 00;12;05;17 And finally, there is SQL mode where we can run standard SQL queries. SQL mode functions very much like the older MySQL command line client. And what are the advantages of using MySQL Document Store? I think the best feature of MySQL Document Store is that because the documents are stored in a database table using the JSON data type, we can use native SQL to run complex queries for reports and analytics.   00;12;05;19 - 00;12;27;13 MySQL has quite a few native functions for working with JSON, which can help extract data from a document store easier than in other solutions. Another big advantage is that MySQL Document Store is fully ACID compliant because the JSON documents are stored using the InnoDB storage engine. What does it mean for a database to be ACID compliant?   00;12;27;15 - 00;12;55;27 In databases, data is updated, added, deleted, etc. in transactions or steps. Sometimes, these transactions are a single query. Other times they may be multiple queries run in succession. Thezacronym ACID, which stands for atomicity, consistency, isolation, and durability, ensures that these transactions are processed in a reliable manner. Atomicity guarantees that each transaction is treated as a single unit.   00;12;55;29 - 00;13;30;24 If one part of the transaction fails, the entire transaction fails. Consistency ensures that every part of the transaction follows all database constraints. If the data in every part of the transaction violates these constraints, the entire transaction fails. Isolation means that transactions are run in isolation so that they do not interfere with each other. And finally, durability means that once a transaction is committed, meaning all parts of the transaction is succeeded, that the data is written to the database. Database is considered ACID compliant when it adheres to all of this.   00;13;30;26 - 00;13;55;16 Before we let you go, if people want more information about MySQL Document Store, where can they find it? I think the best place to get more information is from the documentation on the MySQL site at dev.mysql.com/doc. There are also quite a few posts about MySQL Document Store on the MySQL blog at blogs.oracle.com/mysql.   00;13;55;19 - 00;14;15;06 Wonderful! Thank you so much, Scott, for taking the time to be with us today. Oh, thanks for having me. Well, folks, that brings us to the end of this episode. We hope you've learned something new and that you'll join us next week for a discussion on Oracle Cloud Infrastructure's maximum security architecture. Until then, this is Lois Houston and Nikita Abraham signing off.   00;14;15;09 - 00;16;57;25 That's all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We'd also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.