×
Community Blog Managing RDS Databases With DMS

Managing RDS Databases With DMS

In this blog post, we'll introduce Alibaba Cloud's Data Management Service (DMS) and show you how DMS can be used to create tables, import data, and run queries on an Alibaba Cloud RDS database.

By Jeremy Pedersen

One of Alibaba Cloud's many neat built-ins is a tool called DMS (Data Management Service).

DMS is a handy way to manage database permissions and users, import and export data, create and update tables, and more.

Let's take a look at two of the simplest use-cases for DMS: Importing data in CSV format, and executing SQL commands via the DMS web console.

Creating an RDS instance

Before we can get started with DMS, we need to create a database. For the purposes of this blog post I'll be setting up a Pay-As-You-Go RDS instance in Singapore, but RDS (and DMS) are available in all Alibaba Cloud regions.

After logging into my Alibaba Cloud Account, I navigate to the RDS console, click on Instances, choose the Singapore region from the dropdown in the upper left, and then click on Create Instance:

rds_create_instance

This will open up a new tab, where you can select the type of RDS instance you want to purchase. To keep costs low, we'll choose the Basic RDS instance type, and choose Standard SSD as our disk type. Make sure your settings match the ones shown here, then click on Next: Instance Configuration:

rds_buy

On the next page, you'll need to choose a VPC and VSwitch to house your RDS instance (if you don't know what these are, just go with the default settings the console offers you). You'll also have to choose your MySQL database engine type (you can safely choose the default, InnoDB):

rds_buy_2

After clicking Next: Confirm Order, you'll be taken to the (final) step in the purchase process:

rds_buy_3

Verify the RDS configuration settings shown at the top of the page, tick the box to agree to the RDS terms of service, then click Pay Now.

Note that the price you see will probably be higher than the one shown in my screenshots. I used an internal Alibaba Cloud test account, and that account is subject to a special discount!

If everything worked, you should see an order confirmation page like this one:

rds_confirm

You can close that page and return to the RDS console. It might take a few minutes for the RDS instance to start up. Once it does, you should see something like this:

rds_overview

Creating a User and a Database

We're not (quite) done with our RDS instance, though. Before we move further, we need to create a database and create a user with permission to access that database and create, read, and write tables.

Note that each RDS instance can host multiple databases and can support multiple users.

Per the instructions in the screenshot above, click on the RDS instance's ID to navigate to the Basic Info page for the instance, which looks like this:

rds_inst_overview

The first thing we want to do is create a new user, so click on Accounts, which should take you here:

rds_accounts

Click on Create Account to start the account creation process.

RDS allows us to create two different types of accounts. Privileged and Standard accounts. Privileged accounts are just that: they have super-user privileges. Standard accounts are given a more limited set of permissions, typically restricted to a single database.

We will create a Privileged account, like this:

privileged_account

That's it! We should now see the account in the accounts list:

account_complete

Next, we need to click on Databases, then Create Database, as shown here:

database_tab

create_database

Give the database a name (I chose ecommerce_data), and choose the encoding utf-8, then click Create:

create_db_window

If the database has been created successfully, we should see our new database showing up on the Databases page:

db_success

Now we're ready to log into the new database with DMS, and try uploading some data!

Logging into DMS

First, click on Log On to Database, at the top of the Databases page:

dms_login

This will open up DMS in a new tab. If this is your first time using DMS, it will prompt you to choose a database type, select an Alibaba Cloud Region (Singapore, in my case), and enter an RDS username and password:

dms_login_win

The username and password will be the ones we set earlier in this tutorial, when we created our database user. We can test that we've set everything correctly by clicking on Test Connection:

dms_login_win_test

If all the provided information is correct, you should see a message like this one:

dms_conn_test

What do I do if the connection test fails, but my username and password are correct? This can happen if the DMS service IP address range hasn't been added to your RDS database's IP address whitelist. If this happens, don't panic: DMS will give you the option to add the IP address range to your RDS instance's whitelist automatically.

You'll notice there are also some green check marks next to each of the pieces of information we provided. If there is an issue with one of these fields, the green check mark will not appear, so we can identify the problem quickly.

Click on OK, then click on Login:

dms_login_button

This takes us to the DMS homepage. By default, DMS will open up a SQL interface that allows us to run queries on our database. We don't have any tables yet, so the first thing we need to do is run some DDL statements to create a few new tables.

For the purposes of this article, we'll be using some pre-prepared E-Commerce example data from the Alibaba Cloud Academy GitHub page. We will create 3 tables: users, products, and orders, holding sample data from that repository (click the link above and take a look in the "data" folder, which contains the three .csv files we'll be uploading in the next section).

Copy-paste the following SQL code into the DMS SQL command window, replacing the SELECT statement that is there by default:

CREATE TABLE `users` (
    `user_id` bigint NOT NULL,
    `name` varchar(32) NULL,
    `age` bigint NULL,
    `sex` varchar(32) NULL,
    `country` varchar(32) NULL,
    `country_code` varchar(32) NULL,
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8;

CREATE TABLE `products` (
    `product_id` bigint NOT NULL,
    `product_name` varchar(32) NULL,
    `price` float NULL,
    PRIMARY KEY (`product_id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8;

CREATE TABLE `orders` (
    `order_id` bigint NULL,
    `year` varchar(32) NULL,
    `month` varchar(32) NULL,
    `product_code` bigint NULL,
    `quantity` bigint NULL,
    `user_id` bigint NULL
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8;

Finally, click on Execute:

execute_table_create

If all three create statements succeed, 3 new tabs should appear at the bottom of the window:

execute_success

If we click on the Refresh icon as shown in the screenshot above, we should see our three new tables appear to the left of the SQL command window:

new_tables

We can now start importing our data.

Importing data

To save time, we can use this pre-prepared example E-Commerce dataset, from the Alibaba Cloud Academy GitHub page:

repo_home

data_page

Download the 3 .csv files above and save them somewhere on your computer.

Next, click on Data Import at the top of the DMS console:

dms_import_button

This will open up a new tab, where we specify the target database and target table:

set_target

We then choose a CSV file to upload (we will start with orders.csv):

upload_file_button

select_file

Next, we hit Submit to start the import job. Note that DMS is designed for multi-user scenarios, so typically after submitting a task, we need to set a schedule for the task and get approval from a DMS administrator. In this case, we are the administrator, so we can both submit and approve the task ourselves, like so:

submit_file

start_import_job

Clicking Execute Change will start the import job. We have the option to schedule the job to occur later, or run it right away. We will run it right now:

import_schedule

After the job is started, we can check its status here:

running_task

After a while, we can refresh the page, and we should now see the status of the task has changed to Executed. This indicates that the import job has succeeded. We can click on Details to see the logs generated by the import task:

completed_task

task_log

Now we simply repeat this process for the other two tables, users and products.

Running queries

Got everything imported? Great! Let's try running some SQL queries. If you've closed the Data Import tabs and cleared out the CREATE TABLE SQL code, you should be looking at a nice, empty SQL query window, like this one:

empty_query_window

Try executing the following SQL code:

SELECT * FROM users LIMIT 10;
SELECT * FROM products LIMIT 10;
SELECT * FROM orders LIMIT 10;

After clicking on Execute(F8) at the top of the window, you should have three new tabs appear at the bottom of the window, to the right of the Execution History tab:

query_results

Take a look through these tabs, to confirm that the user, product, and order data has been imported successfully. In each tab you should see 10 rows of data from each of our three tables.

That's it! You now know how to use DMS to create tables, import data, and run queries.

Next Steps

Wondering what to try next? Here are a few suggestions:

  1. Try out Alibaba Cloud Academy's DMS Class.
  2. Check out this blog post, where we import the data from this tutorial into DataWorks, Alibaba Cloud's Big Data Platform, and run some basic analyses.
  3. Try out the Academy's comprehensive Big Data Certification Course, which covers both Alibaba Cloud's Big Data platform and open source tools like Python's Pandas.

For more helpful content, browse your way over to the Alibaba Cloud YouTube Channel or take a look at our blog on Medium

0 0 0
Share on

JDP

12 posts | 7 followers

You may also like

Comments

JDP

12 posts | 7 followers

Related Products