MariaDB Cloud – kicking the tires with openflights data

Written by Written by Robert Silén Leave a comment on MariaDB Cloud – kicking the tires with openflights data
MariaDB Clouds & openflights data

MariaDB plc recently acquired SkySQL and rebranded it MariaDB Cloud that offers serverless MariaDB as a service. Kaj Arnö interviewed the technical father, and now it is time for a practical, technical kick-the-tires look at MariaDB Cloud.

In this tutorial, I’ll show developers how to set up a MariaDB Cloud database and load sample data in under 10 minutes. Perfect for showcasing simple SQL commands and MariaDB features with our public data set openflights – which incidentally is what one of the tracks in our ongoing hackathon is about. I’ll be doing this in my macOS terminal with the mariadb command line client. 

Preamble

Expectations and experience level

  1. I understand the basic idea of databases and SQL.
  2. I have limited experience setting up MariaDB: I have previously set up a MariaDB Docker image locally and loaded the openflights data into it.
  3. I have tried some cloud infrastructure before, but thought it was unnecessarily complicated. .
  4. I expect minimal hassle and that I will not need to think about database management as my data and query amounts grow.

Prerequisites

  1. I’m running a MacBook Air with macOS Tahoe 26.0
  2. I will use the Terminal app in macOS
  3. I have installed homebrew to install necessary packages (see how)
  4. I have installed the MariaDB command line client with brew install mariadb (see how)
  5. I have installed Git with brew install git (see how)

Steps I’ll be showing 

  1. Downloading the openflights data
  2. Creating a database in MariaDB Cloud 
  3. Connecting to the database with the mariadb command line client
  4. Uploading openflights data to the MariaDB cloud database
  5. Showing some example queries to show data has been imported
  6. Conclusions and pondering next steps

1. Downloading the openflights repository

We need the openflights files available locally, so let’s download MariaDB’s openflights repo. To copy it from GitHub, we use git clone, because that’s Git’s command for downloading a repository from a remote location. Run git clone https://github.com/mariadb/openflights locally in your terminal. This will create a folder openflights with the repo and should look like this:

git clone https://github.com/mariadb/openflights
Cloning into 'openflights'...
remote: Enumerating objects: 10038, done.
remote: Counting objects: 100% (1406/1406), done.
remote: Compressing objects: 100% (333/333), done.
Receiving objects: 100% (10038/10038), 64.71 MiB | 2.21 MiB/s, done.
remote: Total 10038 (delta 1249), reused 1078 (delta 1073), pack-reused 8632 (from 3)
Resolving deltas: 100% (6410/6410), done.

To see the content of the folder, change directory by cd openflights and then ls. The sql and data folders contain the files we will import into the MariaDB database. 

2. Creating a MariaDB Cloud database with QuickLaunch

Go to mariadbcloud.com or skysql.com and click the “Get Started” links to sign up and create an account. Personally, I use Google’s single sign on with my Google account to log in. 

If you don’t get the Quick Launch dialogue automatically, click “Quick Launch” in the top menu.

Choose a service provider and click “Quick Launch Now”. I went for AWS as it had the closest location in Europe.

The dashboard instantly shows me a database instance. It says “Healthy”, but by the looks of the graphs, it is not active yet. 

After quite exactly two minutes, the graphs start showing information and I presume the database is now running. I also got an email with the subject “Your service <id> is now ready!”.

3. Connecting to the MariaDB database from the macOS command line

So how do  you connect to the database? In the macOS Terminal, we will use the simple mariadb command line client as mentioned in the prerequisites.

To connect, you will need the database’s details like address, username and password, so click CONNECT in MariaDB cloud’s dashboard and you will get the below window with the settings.

There is a handy prefilled command under ”Connecting using MariaDB CLI” so I click the copy icon to copy it to my clipboard.  I paste the command in the terminal and press enter. 

Note: Make sure you connect with the mariadb command while you are in the openflights folder as mentioned in the first step of the tutorial – this is necessary later. If you aren’t in the correct folder, switch to it now with cd openflights

You will be asked for the password, so copy that too from the MariaDB Cloud dashboard in the browser and paste it in the terminal. After you press enter, there can be a wait before getting a confirmation about being connected, which will look like this: 

Run the SQL command STATUS; to see some details about the database. 

In my case I can see the MariaDB version is 11.8.2 which is the latest LTS (Long Term Support) version as of writing – so that’s good. 

Don’t be surprised if you see an uptime of several hours when you first connect – this is the uptime of the managed server infrastructure, not your specific database instance.

Let’s see what databases MariaDB contains by running SHOW DATABASES; 

These are system databases that MariaDB uses internally, plus ‘sky’ – a default working database created by MariaDB Cloud. We’ll be creating our own database called flightdb2 for the openflights data.

4. Uploading openflights data to the MariaDB cloud database

The openflights repo contains two files, create.sql and load-data.sql. The first one creates the database and tables, and the second loads data from the .dat files into the tables. 

In the terminal with the MariaDB command line client connected to the MariDB cloud database, do the following. – Note: make sure the connection was opened in your local openflights folder as has been mentioned in step 1 and 3, otherwise the load-data.sql relative paths will not work.

Create the database and tables by running:

SOURCE sql/create.sql

To load the data run: 

SOURCE sql/load-data.sql

Success! 

5. Example queries

Let’s access the database and check what tables are in it with: 

USE flightdb2;
SHOW TABLES;

Yay, the tables are found. Let’s explore more by looking at a single table: 

DESC airports

Let’s do a query on data from a table: 

SELECT * FROM airports LIMIT 5;

And another, more specific query: 

SELECT name, city, country FROM airports LIMIT 5;

All works!

6. Conclussions and what next?

Well, that worked out quite nicely!

Preparing this tutorial, I was getting an error with the original openflights sql files – I learnt that I can’t do GRANT ALL PRIVILEGES, but instead needs to grant individual rights. This is apparently a “normal” thing with cloud databases, but it might surprise somebody that has prepared their data locally. After fixing this in the openflights sql files, it definitely feels like a smooth, intuitive process to use MariaDB Cloud!

So what else could be interesting to try out and showcase after setting up the database with the openflights data? Perhaps:

  • Doing more advanced queries including combining tables – as well as showing how to get an overview of openflights’ data’s tables, common keys, etc
  • Understanding differences in user and database settings between running MariaDB Cloud and a local instance or Docker image – what restrictions in MariaDB cloud do I need to be aware of?
  • Connecting to the database with python 
  • Connecting to the database from an app with suitable user privileges
  • Growing the data and queries to try out how MariaDB Cloud reacts and scales
  • Trying out specific MariaDB (Cloud) features and writing use case examples on how to use them