Getting Started with MariaDB SkySQL and Smart Transactions
MariaDB SkySQL is the only database-as-a-service (DBaaS) that supports transactions, analytics and smart transactions (HTAP) for MariaDB. For developers, this means access to a wider range of data, enabling you to build modern applications that are engaging and deliver a better customer experience. In this blog, I’ll show you how easy it is to start building modern applications using smart transactions and MariaDB SkySQL.
SkySQL: The Ultimate MariaDB Cloud
MariaDB Corporation recently released SkySQL, a fully managed DBaaS for MariaDB Platform. Let’s not make any bones about it, SkySQL is the ultimate MariaDB cloud. But what makes SkySQL unique? Above all else, unlike other hosted cloud implementations of MariaDB, it provides all of the features MariaDB Platform has to offer, and is backed by the engineering expertise that created the popular MariaDB database, used by 75% of the Fortune 500.
SkySQL allows users to provision three different types of database services: transactional, analytical and both, or what we describe as smart transactions. To get started with SkySQL for transactions or analytics, check out my other blogs: Launching an Application with MariaDB SkySQL and Getting Started with MariaDB SkySQL for Analytics.
Smart Transactions
Smart transactions, also known as hybrid transaction/analytical processing (HTAP), are standard transactions enhanced with the use of real-time analytics before, during and/or after these transactions to provide a better customer experience. MariaDB Platform enables smart transactions by storing data in both row and columnar formats so a single database can access both row and columnar data within the same transaction – row for CRUD operations, columnar for analytical queries. For more about smart transactions on MariaDB Platform see this blog by Shane Johnson.
SkySQL for Smart Transactions
As previously mentioned, SkySQL unleashes the full capabilities of MariaDB Platform, including smart transactions or united transactions and analytics. Hybrid transactional and analytical systems leverage the power of in-memory computing to bring Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) processing onto one data platform.
Based on that description, hybrid transactions and analytics may seem more intimidating than it actually is. Within the context of SkySQL the MariaDB Platform supports smart transactions through its combination of MariaDB Enterprise Server, MariaDB ColumnStore and MariaDB MaxScale. Put even more simply, MariaDB combines the power of row-based and column-based storage engines by including both within a single database server.
(As a side note, if you wanted to use both transactions and analytics with AWS, you would have to use data management services to move data from RDS to Redshift to get decent analytics and performance – batch and slow at best.)
No ETL on SkySQL
At this point you’re probably thinking, “OK, cool concept and all, but how does this benefit me?”
Before in-memory computing, transactional and analytical processes were split onto two separate data platforms to reduce load on the transactional database. While OLTP ran on an operational database, the transactional data was periodically loaded into a separate analytics database via an extract, transform, and loading (ETL) process where often long-running OLAP queries could be run without slowing the transactional database performance.
However, modern applications have created modern problems. Gone are the days of users being satisfied with apps that show a limited portion of historical/analytical data.
Likewise, we developers hope for, nay, expect near real-time analytics at our fingertips and MariaDB SkySQL delivers it.
With SkySQL, transactions are first written to the transactional database that uses the InnoDB storage engine – a traditional row-based storage engine. The transactions are then immediately replicated via MaxScale to the analytics version of the same database. The analytics database uses the ColumnStore columnar storage engine. Columnar storage engines are designed for analytics workloads processing – ad hoc queries on large data sets – without requiring indexes. With the data in both databases, MaxScale transparently routes application queries to the appropriate database providing analytics without ETL. Data can also be replicated to a separate data warehouse for internal use.
Getting Started with SkySQL and Smart Transactions
Now it’s time to get our hands dirty, and try MariaDB SkySQL first-hand to see just how easy it is to get started. If you haven’t already, be sure to check out this post for a more detailed guide for getting started with SkySQL.
For the purposes of this post, I’m going to assume you’ve already set up a MariaDB SkySQL account, and we’re just going to dive directly into the steps for setting up, configuring and integrating with a SkySQL Smart Transactions service instance.
Start a Service and Connect
Start a smart transactions instance
If it’s your first time getting started with SkySQL, you’ll be prompted to start a new service directly.
Otherwise you will need to either use the “Launch New Service” button located at the top right portion of the “Services” view or navigate directly to the “Launch Service” view by using the navigation menu on the left.
After selecting a service you’ll be prompted to set up a configuration that best suits your needs.
For our example, we’ll use a basic configuration:
- Single Node topology
- Sky-04×15, 4 Core VCPU with 15 GB of memory, the smallest instance size
- 100 GB of storage, the smallest option available
- In a region of your choice
From there you just need to select a name for your new HTAP service and click the “Launch Service” button. Your database service will be “Pending” while it is being provisioned. While you wait, you can whitelist your IP address.
Add your IP to the whitelist
SkySQL database services are protected by a firewall. The firewall is configured to reject all unapproved connections. So, to connect to your database service, you’ll need to whitelist the IP address of the system you’re connecting with.
To add one or more IP addresses, click on a database name and select the pencil icon inline with the “Whitelisted IP Addresses” section label.
Then fill in the IP address, click the “Add” button. Repeat for any additional addresses. When you’ve finished adding addresses, click the “Submit” button.
After adding your IP address to the whitelist, you’re ready to connect to SkySQL!
Download the Certificate Authority and retrieve the connection information
Click the database name and the “Show Credentials” button to reveal a modal displaying a variety of connection information and criteria. Click the “Download” link to download the Certificate Authority Chain (CA). (The database service shown below has been deleted.)
Connect to SkySQL
For the sake of simplicity, you’re going to connect to SkySQL using the MariaDB client. If you don’t have the MariaDB client installed on your machine, you can find more information about downloading and installing it here. If you want to connect using a different database client, you can find more information on how to connect using some of the most popular database clients in the MariaDB documentation.
To connect to SkySQL via the MariaDB client, simply copy the CLI command in #1 of the Credentials modal as shown above, paste into the terminal, and execute. Note: the command assumes that the CA file resides in the “Downloads” folder. If you’ve placed it elsewhere, you’ll need to adjust for that.
Huzzah! You’re now connected to MariaDB SkySQL!
Creating a schema and loading data
After establishing a connection it’s time to create a schema. For this I’ve removed the burden of having to manually add databases and tables. All you’ll need to do is the following steps:
1. Clone a repository.
$ git clone https://github.com/mariadb-corporation/dev-example-skysql-htap-quickstart
2. Change directories to dev-example-skysql-htap-quickstart
3. Copy the CA into the current directory, for example:
$ cp ../Downloads/skysql_chain.pem skysql_chain.pem
4. Execute the shell script file named “create_and_load.sh”
$ ./create_and_load.sh <host_address> <port_number> <user> <password>
Note: Remember to wrap argument values in single quotes if they contain special characters (e.g. !)
For example:
$ ./create_and_load.sh sky000XXXX.mdb0001390.db.skysql.net 5001 DB00001617 ‘skysql_rocks!’
And that’s it! Your MariaDB SkySQL Smart Transactions database instance now contains two databases and four tables, three of which have been filled with data.
Setting up replication
SkySQL provides the capability to asynchronously replicate data between tables. In our example we’ll be replicating data between the travel.flights and travel_history.flights, and all you’ll need to do is execute one command to set it up.
Connect to your MariaDB SkySQL database service instance and execute the following.
SELECT SET_HTAP_REPLICATION('flights','travel','travel_history');
To test that replication is set up properly start by inserting a record into travel.flights.
INSERT INTO travel.flights VALUES(2020,4,3,1,'2020-04-03','DL','N9999A',100,'ORD','LAX','0600','0600',0);
And then confirm that the record has been inserted into travel.flights and replicated to travel_history.flights by comparing the results of the following two queries.
SELECT * FROM travel.flights WHERE fl_date = '2020-04-03' AND tail_num = 'N9999A';
SELECT * FROM travel_history.flights WHERE fl_date = '2020-04-03' AND tail_num = 'N9999A';
Cross-engine querying
Lastly, let’s look at one of my favorite features of MariaDB Smart Transactions databases, cross-engine queries. Cross-engine querying or joins means exactly what you think it means, being able to query both transactional and analytical within a single query.
Test it out by selecting the number of flights for each airline.
SELECT a.airline, COUNT(*) FROM travel.airlines a INNER JOIN travel_history.flights f ON a.iata_code = f.carrier GROUP BY a.airline;
Cranking it up a notch
While this was a simple example, it provides a basic understanding of the core functionality of MariaDB SkySQL and hybrid transactional/analytical processing. What can you accomplish in your applications when you can combine the power and reliability of transactional information with the performance and scalability of analytical information?
Please let me know by reaching to me on Twitter (@probablyrealrob) or on Github (@rhedgpeth)!
For an overview of SkySQL capabilities
For more about SkySQL Analytics with a sample app
