Three steps from SQL to a document database
Tutorial – SQL Database Migration
Use a Python API to migrate a music library from SQL to a NoSQL document database.
In this article, I will show you how I used a Python application programming interface (API) to migrate my music library from an SQL relational database to a NoSQL document database. Using the Python X DevAPI in the MySQL Shell application, I will highlight some basics about document databases, the Python methods that I used, and the database tool that enables migration. Readers who should get the most out of this article are those that have some basic familiarity with the structured query language (SQL) and with the Python programming language.
Why Migrate from SQL to Document?
I have my existing personal music library in an SQL relational database containing music metadata – artist, song title, album title, track number, genre, release year – that I want to migrate to a document database. For the purpose of this article, I will use a few examples from my library (Figure 1).
SQL relational databases have been dominant for decades, making up 60 percent of the database market in 2019 according to a ScaleGrid Database Trends report. In recent years, use of document databases has increased, largely driven by the requirements of big data. One of the criticisms of relational databases is that their schema is rigid. All data fields must be defined in advance with identical fields in every row in a table, making it difficult to make schema changes later.
By contrast, document store databases, sometimes referred to as NoSQL, do not have a fixed schema. Document databases do not require each document to have the same fields (though they can). In fact, it is possible to have different fields in each document throughout the database. That flexibility is one of the key advantages of a document store over a relational store. It is the reason I decided to migrate, because it allows me to easily add new metadata to my music library. That could include metadata such as artist background information, song credits, and/or other miscellaneous metadata that may not be immediately available.
What Is a JSON Document?
In many document store systems, documents are JavaScript Object Notation (JSON) objects, or JSON-like objects. JSON is becoming increasingly popular as a standard for data interchange and storage and is beginning to replace the Extensible Markup Language (XML) as a dominant data exchange format, particularly for music metadata. JSON documents are lightweight, language-independent, and human readable. In short, JSON documents are elegant in their simplicity. Many popular music APIs provide JSON-formatted metadata. These APIs include Amazon, Apple Music, Spotify, SoundCloud, and others.
The JSON format eases development since it is object-oriented and easier to parse than XML, because JSON documents are comprised of a comma-separated list of one or more key-value pairs. The simplest form of a JSON document is {key:value}
. You will note that this is the same form as a Python dictionary. From a software development perspective, JSON is well suited to object-oriented programming languages such as Python, JavaScript, and others.
Let's consider that we have a simple document case containing an artist name and album name. The document instance would be defined as follows:
{"Artist" : "Quincy Jones", "Album" : "Q's Jook Joint"}
A group of related documents is referred to as a collection. As an analogy between a relational database and a document database, a table in a relational database is equivalent to a collection in a document database. Each row in a table is equivalent to a document in the collection, and each field name (column) in a table is equivalent to a key in a document.
API Methods for Database Migration
Now that we know what a document store is, I'll show you how to use a Python API to migrate a relational database to a document database. My source database was created with MySQL, so I used the X DevAPI interface in the MySQL Shell 8.0 application, which allows me to access both tables and documents in a database.
To accomplish my goal, I'll use table methods to access data in tables, and document methods to build and verify my documents. Three steps are required to migrate data: 1) create a collection, 2) fetch rows from a table, and 3) add fetched rows to the document collection.
Table methods used:
# Returns a dataset with rows table.select() # Returns a dictionary/json object result.fetch_one_object()
Document methods used:
# Inserts a document into a collection collection.add() # Returns dataset with all documents in a collection collection.find()
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
-
ESET Discovers New Linux Malware
WolfsBane is an all-in-one malware that has hit the Linux operating system and includes a dropper, a launcher, and a backdoor.
-
New Linux Kernel Patch Allows Forcing a CPU Mitigation
Even when CPU mitigations can consume precious CPU cycles, it might not be a bad idea to allow users to enable them, even if your machine isn't vulnerable.
-
Red Hat Enterprise Linux 9.5 Released
Notify your friends, loved ones, and colleagues that the latest version of RHEL is available with plenty of enhancements.
-
Linux Sees Massive Performance Increase from a Single Line of Code
With one line of code, Intel was able to increase the performance of the Linux kernel by 4,000 percent.
-
Fedora KDE Approved as an Official Spin
If you prefer the Plasma desktop environment and the Fedora distribution, you're in luck because there's now an official spin that is listed on the same level as the Fedora Workstation edition.
-
New Steam Client Ups the Ante for Linux
The latest release from Steam has some pretty cool tricks up its sleeve.
-
Gnome OS Transitioning Toward a General-Purpose Distro
If you're looking for the perfectly vanilla take on the Gnome desktop, Gnome OS might be for you.
-
Fedora 41 Released with New Features
If you're a Fedora fan or just looking for a Linux distribution to help you migrate from Windows, Fedora 41 might be just the ticket.
-
AlmaLinux OS Kitten 10 Gives Power Users a Sneak Preview
If you're looking to kick the tires of AlmaLinux's upstream version, the developers have a purrfect solution.
-
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.