Introduction to Alibaba Cloud DMS: Task Orchestration and Practical Data Operations

Introduction to Task Orchestration

Scenario 1: Data Archiving

Scenario 2: Data Integration

Scenario 3: Data Processing

Scenario 4: Scheduled Operations

Practical Data Operations — Periodic Archiving of Database Data

Background and Requirements

create table orders(
order_id bigint,
product_name varchar(32),
price double,
total_amount double,
created_date date
);

Prerequisites

  1. You have purchased Alibaba Cloud DLA, and the DLA instance is in the same region as the target ApsaraDB RDS for MySQL instance, such as China (Hangzhou).
  2. You have activated [Alibaba Cloud OSS](https://www.alibabacloud.com/product/, and the OSS instance is in the same region as the DLA and target ApsaraDB RDS for MySQL instances.
  3. You have purchased Alibaba Cloud DMS.
  4. The DLA instance has been recorded to DMS. For more information, see Cloud Database Instance Recording.

Task Flow Configuration

CREATE DATABASE demo_schema
WITH DBPROPERTIES (
catalog = 'oss',
location = 'oss://xxxxxx/dla_demo/'
)
  1. Creating an RDS schema in DLA
  2. Creating an OSS backup table in DLA to store historical data
  3. Backing up data in ApsaraDB RDS for MySQL from the previous day
CREATE SCHEMA IF NOT EXISTS dla_mysql_rds WITH DBPROPERTIES (
CATALOG = 'mysql',
LOCATION = 'jdbc:mysql://xxxxxx.rds.aliyuncs.com:3306/dmstest',
USER = 'dmstest',
PASSWORD = 'xxxxxxxxx',
INSTANCE_ID = 'xxxxxx'
);
MSCK REPAIR DATABASE dla_mysql_rds;
CREATE EXTERNAL TABLE oss_orders (
order_id bigint,
product_name varchar(32),
price double,
total_amount double,
created_date date)
PARTITIONED BY (y string, m string, d string)
STORED AS TEXTFILE
LOCATION 'oss://xxxxxx/dla_demo/';
/* Create a temporary table. */
CREATE EXTERNAL TABLE oss_orders_tmp (
order_id bigint,
product_name varchar(32),
price double,
total_amount double,
created_date date)
STORED AS TEXTFILE
LOCATION 'oss://xxxxxx/dla_demo/y=${year}/m=${month}/d=${day}'
TBLPROPERTIES('auto.create.location'= 'true');
/* Back up daily data. */
insert into oss_orders_tmp
SELECT * FROM dla_mysql_rds.orders
where created_date = '${bizdate}';

/* Update the backup table partition information and delete the temporary table. */
msck repair table oss_orders;
drop table oss_orders_tmp;
  1. Create an OSS temporary table in DLA. The oss_orders_tmp temporary table maps to the year/month/day directory of oss_orders in OSS. Data in the temporary table will become a partition of oss_orders.
  2. Back up daily data. Use the insert-select SQL statement to read data from the orders table in ApsaraDB RDS for MySQL (corresponding to dla_mysql_rds.orders in DLA) and write it to the temporary table in OSS.
  3. Update partition information and delete the temporary table. Update metadata information in oss_orders and delete the oss_orders_tmp temporary table.

Configuring Task Flow Scheduling

Quick Task Flow Creation with a Template

Summary

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com