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
-
Red Hat Adds New Deployment Option for Enterprise Linux Platforms
Red Hat has re-imagined enterprise Linux for an AI future with Image Mode.
-
OSJH and LPI Release 2024 Open Source Pros Job Survey Results
See what open source professionals look for in a new role.
-
Proton 9.0-1 Released to Improve Gaming with Steam
The latest release of Proton 9 adds several improvements and fixes an issue that has been problematic for Linux users.
-
So Long Neofetch and Thanks for the Info
Today is a day that every Linux user who enjoys bragging about their system(s) will mourn, as Neofetch has come to an end.
-
Ubuntu 24.04 Comes with a “Flaw"
If you're thinking you might want to upgrade from your current Ubuntu release to the latest, there's something you might want to consider before doing so.
-
Canonical Releases Ubuntu 24.04
After a brief pause because of the XZ vulnerability, Ubuntu 24.04 is now available for install.
-
Linux Servers Targeted by Akira Ransomware
A group of bad actors who have already extorted $42 million have their sights set on the Linux platform.
-
TUXEDO Computers Unveils Linux Laptop Featuring AMD Ryzen CPU
This latest release is the first laptop to include the new CPU from Ryzen and Linux preinstalled.
-
XZ Gets the All-Clear
The back door xz vulnerability has been officially reverted for Fedora 40 and versions 38 and 39 were never affected.
-
Canonical Collaborates with Qualcomm on New Venture
This new joint effort is geared toward bringing Ubuntu and Ubuntu Core to Qualcomm-powered devices.