Three steps from SQL to a document database
The Process
The three steps that I identified above assume that the relational database exists, that a database connection has already been established, and the source table exists. Before starting the migration process, I'm assuming that a connection to the database has already been made and that the following instances already exist:
# Session instance my_session = mysqlx.get_session( <URI> ) my_database = my_session.get_schema('my_db')
The first step in the migration process is to create the target collection:
doc_collection1 = my_database.create_collection('my_collection1')
In the statement above, I've created a collection object named my_collection1
using the create_collection()
method and assigned it to doc_collection1
, which will be the target document collection. I will subsequently add, update, or remove documents as necessary.
In the next step, I need to extract my metadata from the source SQL database. This metadata is in a table named sql_table
. To extract data in a table row, I execute the statements below:
table_result = sql_table.select().execute() table_2document = table_result.fetch_one_object()
In the code above, the select()
method is analogous to the SELECT
statement in SQL. It returns a result that is a list of rows. Next, I need to fetch each row, convert it to a JSON object, and add it to my collection. The fetch_one_object()
method fetches a row from the table as a JSON object.
The table_2document
result object shows the key:value strings of the metadata fetched from one row of the table (see Figure 2). While there is a fetch_one()
method that could fetch a table row, the result is not a JSON object and therefore cannot be added directly to a document. Note that SQL statements executed through the X DevAPI must end with the execute()
function because they are executed only when that function is called. If omitted, the statement will be ignored.
In the third step, each table_2document
fetched from my_table
is then added to doc_collection1
with the add()
method which adds a JSON document to a collection:
doc_collection1.add(table_2document).execute()
Build Collection
With these three basic steps, I can add a single document to doc_collection1
. To migrate the entire SQL table to a collection, the code below iterates through each row in the table with the add()
method (see Listing 1)
Listing 1
Migrate a Complete SQL Table
table_result = sql_table.select().execute() table_2document = table_result.fetch_one_object() while table_2document: doc_collection1.add(table_2document).execute() table_2document = table_result.fetch_one_object()
The result in Figure 3 shows two of the five documents added to doc_collection1
. You will notice that there is an extra _id
field that is automatically added to each document. It is a virtual index that MySQL automatically adds to each document in a collection. See the MySQL 8.0 manual [1] for more information on document indexing.
Document Updates
After I have migrated my SQL table to the document collection, I will continue to either build on it with new metadata documents, update incorrect or incomplete documents, or remove documents. As we have already seen, I can use the add()
method to add new documents to my library or simply add new fields (key-value pairs), effectively changing the schema on the fly.
If, for example, I need to change the spelling of an artist's name, I can use the doc_collection1.modify()
method. Note that the percent (%
) wildcard can be used in the search condition string for these methods, as illustrated with the remove()
method in the example below. In addition, note that I have used explicit strings in the modify()
and set()
methods to simplify the examples and to keep the focus on function. It is however, good practice to use parameterized placeholders instead of explicit strings.
doc_collection1.modify("Artist = 'Santana'").set("Artist", "Carlos Santana") doc_collection1.remove("Artist like 'Quincy%'")
For a more comprehensive list of available create, remove, update, and delete (CRUD) methods, see the MySQL 8.0 Reference Manual.
« Previous 1 2 3 Next »
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Gnome 47.1 Released with a Few Fixes
The latest release of the Gnome desktop is all about fixing a few nagging issues and not about bringing new features into the mix.
-
System76 Unveils an Ampere-Powered Thelio Desktop
If you're looking for a new desktop system for developing autonomous driving and software-defined vehicle solutions. System76 has you covered.
-
VirtualBox 7.1.4 Includes Initial Support for Linux kernel 6.12
The latest version of VirtualBox has arrived and it not only adds initial support for kernel 6.12 but another feature that will make using the virtual machine tool much easier.
-
New Slimbook EVO with Raw AMD Ryzen Power
If you're looking for serious power in a 14" ultrabook that is powered by Linux, Slimbook has just the thing for you.
-
The Gnome Foundation Struggling to Stay Afloat
The foundation behind the Gnome desktop environment is having to go through some serious belt-tightening due to continued financial problems.
-
Thousands of Linux Servers Infected with Stealth Malware Since 2021
Perfctl is capable of remaining undetected, which makes it dangerous and hard to mitigate.
-
Halcyon Creates Anti-Ransomware Protection for Linux
As more Linux systems are targeted by ransomware, Halcyon is stepping up its protection.
-
Valve and Arch Linux Announce Collaboration
Valve and Arch have come together for two projects that will have a serious impact on the Linux distribution.
-
Hacker Successfully Runs Linux on a CPU from the Early ‘70s
From the office of "Look what I can do," Dmitry Grinberg was able to get Linux running on a processor that was created in 1971.
-
OSI and LPI Form Strategic Alliance
With a goal of strengthening Linux and open source communities, this new alliance aims to nurture the growth of more highly skilled professionals.