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
-
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.
-
Kodi 21.0 Open-Source Entertainment Hub Released
After a year of development, the award-winning Kodi cross-platform, media center software is now available with many new additions and improvements.
-
Linux Usage Increases in Two Key Areas
If market share is your thing, you'll be happy to know that Linux is on the rise in two areas that, if they keep climbing, could have serious meaning for Linux's future.
-
Vulnerability Discovered in xz Libraries
An urgent alert for Fedora 40 has been posted and users should pay attention.
-
Canonical Bumps LTS Support to 12 years
If you're worried that your Ubuntu LTS release won't be supported long enough to last, Canonical has a surprise for you in the form of 12 years of security coverage.
-
Fedora 40 Beta Released Soon
With the official release of Fedora 40 coming in April, it's almost time to download the beta and see what's new.
-
New Pentesting Distribution to Compete with Kali Linux
SnoopGod is now available for your testing needs
-
Juno Computers Launches Another Linux Laptop
If you're looking for a powerhouse laptop that runs Ubuntu, the Juno Computers Neptune 17 v6 should be on your radar.