Detailed explanation of the implementation of data processing and conversion process of MySQL MaxCompute and AnalyticDB

1. Row to column function

odps The row-to-column function wm_concat converts column information into row display, and can specify a separator.

Syntax format

wm_concat(separator, string) , where separator is a separator, supports string deduplication, that is, distinct.

select wm_concat('|',sku_id)
from t_business_order_odps
where
create_time >= '2022-09-30 00:00:00';

ads The row-to-column function group_concat in ads, which converts column information into rows to display, and is separated by default. For example, query all column names of the table in mysql.

select group_concat(COLUMN_NAME)
from information_schema.COLUMNS
where
TABLE_NAME = 't_business_order';

2. Decision function

The empty function coalesce in odps, if there is no freight, it will be displayed as 0.

select coalesce(freight_amount,0)
from t_business_order_odps
where
create_time >= '2022-09-30 00:00:00';

The judging function decode in odps, if there is no freight, it will be displayed as 0.

Syntax format

decode(expression, search, result[, search, result]…[, default])

Represents: ternary operator, that is, the function of implementing IF-THEN-ELSE branch selection

select decode(order_amount,freight_amount,0)
from t_business_order_odps
where
create_time >= '2022-09-30 00:00:00';

Ifnull in ads, ifnull is displayed as 0 if there is no freight.

select ifnull(freight_amount,0)
from t_business_order
where
create_time >= '2022-09-30 00:00:00';

3. Time function

In odps, the time field type is datetime, and the current time function is getdate().

select coalesce(freight_amount,0),getdate() as etl_time
from t_business_order_odps
where
create_time >= '2022-09-30 00:00:00';

The time field type in ads is timestamp, and the current time function is now().

select ifnull(freight_amount,0),now() as etl_time
from t_business_order
where
create_time >= '2022-09-30 00:00:00';

4. Table creation statement

In odps, the string field type is string and the time field type is datetime.

CREATE TABLE t_business_order_odps (
	prov_name string COMMENT 'Province Name',
	city_name string COMMENT 'City name',
	cty_name string COMMENT 'City name',
	order_no string COMMENT 'Order number',
	order_amount bigint COMMENT 'freight',
	freight bigint COMMENT 'freight',
	insert_month bigint COMMENT 'Insert month',
	created_time datetime COMMENT 'Create time'
)
COMMENT 'Business Order Master Table;

In ads, the string field type is varchar and the time field type is timestamp.

CREATE TABLE t_business_orde (
	prov_name varchar COMMENT 'Province Name',
	city_name varchar COMMENT 'City name',
	cty_name varchar COMMENT 'City name',
	order_no varchar COMMENT 'Order number',
	order_amount bigint COMMENT 'freight',
	freight bigint COMMENT 'freight',
	insert_month bigint COMMENT 'Insert month',
	created_time timestamp COMMENT 'Create time'
)
PARTITION BY HASH KEY (order_no) PARTITION NUM 128
TABLEGROUP ads_sc_rpt
OPTIONS (UPDATETYPE='batch')
COMMENT 'Business Order Master Table';

Knowledge sorting

There are two types of OPTIONS

1) Batch batch table, that is, data batch storage, suitable for synchronization from odps to ads;

2) Realtime real-time table, that is, a single data entry into the database, suitable for single logging and other information entry into the database.

This is the article about the detailed explanation of the data processing and conversion process of MySQL MaxCompute and AnalyticDB implementation. For more related contents of MySQL MaxCompute and AnalyticDB, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!