Scenario This section describes the development process of AnalyticDB MySQL data. More AnalyticDB MySQL related to Ali Cloud experience lab

Introduction to the

The goal is to let users and developers of data warehouse on the cloud experience the main process of building cloud native data warehouse based on AnalyticDB MySQL edition and DMS through simple steps. The activities will include instance opening, structure and data initialization, report development, report visualization and other links. Three specific application scenarios are used to experience the interactive query and ETL calculation speed of AnalyticDB MySQL edition in the new retail scenario, as well as the process of data warehouse data report development by DMS.

The data set provided in this scenario is simulated data in a retail scenario, including customer information, order records, goods information, and country and region information. The total amount of data is 10GB, and the maximum number of data table records is 59.99 million.

Product introduction

AnalyticDB MySQL edition is a new generation of cloud native data warehouse that supports high concurrency and low delay query. It is highly compatible with MySQL protocol and SQL:2003 syntax standard. AnalyticDB can carry out real-time multidimensional analysis perspective and business exploration on massive data, and quickly build data warehouse on enterprise cloud. View product details.

Data management DMS is a cloud version based on the database service platform of Alibaba Group for more than ten years. It provides a web database management terminal free of installation, free of operation and maintenance, out-of-the-box, unified with multiple database types and multiple environments. It can quickly copy and build the same safe, efficient and standardized database DevOps research and development process and number warehouse development solution as Ali Group for enterprise users. View product details.

Scenario data query and analysis

Scenario data query and analysis

Address: dms.aliyun.com/ Path: All Functions -SQLConsole -Sql database query scenario The following is the SQL script:

#### 1) Make statistics on the composition of global sales sum in each region within half a year. 0.5 seconds, using a pie chart. Select r_name, sum(o_totalprice) totalprice from (select r_name, o_totalprice from customer ,orders ,nation ,region where c_custkey = o_custkey and c_nationkey=n_nationkey and N_regionkey = r_regionKey and o_orderDate < date '1993-09-23' And o_orderDate > date '1993-03-23') a group by r_name order by r_name; #### 2) Count the daily order quantity and order amount of certain items and sort by date. 1 second. Select o_orderDate, count(distinct o_orderKey), Sum (l_extendedprice*(1-l_discount)) as revenue from customer,orders,lineitem where c_mktsegment = And C_CUSTKey = O_CUSTKey and L_ORDERKey = o_ORDERKey and O_ORDERDate < date '1995-03-23' and O_orderdate > date '1996-02-23' // and l_shipdate > date '1996-03-23' and L_shipdate < date '1996-03-23' group O_orderdate order by o_orderDate; #### 3) Collect the income of parts suppliers in a certain region. 1 second, shown in a bar chart. Select n_name, // region sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, Region // Join six tables where C_CUSTKey = O_CUSTKey and L_ORDERKey = O_ORDERKey and L_suppKey = s_suppKey and c_nationKey = S_nationkey and s_nationKey = n_nationKey and n_regionKey = r_regionKey and r_name = 'EUROPE' And o_orderDATE >= date '1996-01-01' // And o_ORDERDATE < date '1996-01-01' + interval '1' year and L_shipdate > date '1996-02-23' and L_shipdate < date '1996-03-23' group by n_name ORDER by // TopN: 1996-03-23Copy the code

Data development

Data development

The query analysis data is stored in a new table, and subsequent query analysis of related data can be done with a simple SQL

Address: dms.aliyun.com/?#to=data-a… Path: All Features – Data Factory – Task Choreography, new task flow instance, select the “Single instance SQL” node to create a task. After the task is configured, click Try run to run it. At the same time, we can choose to click the blank for the configuration of periodic scheduling operation. The SQL script for task nodes is as follows:

#### 1) Make statistics on the composition of global sales sum in each region within half a year. 0.5 seconds, using a pie chart. Create table sum_region_price as select r_name // region,sum(o_totalprice) totalprice from (select r_name, o_totalprice from customer ,orders ,nation ,region where c_custkey = o_custkey and c_nationkey=n_nationkey and N_regionkey = r_regionKey and o_orderDate < date '1993-09-23' And o_orderDate > date '1993-03-23') a group by r_name order by r_name; #### 2) Count the daily order quantity and order amount of certain items and sort by date. 1 second. Create table items_revenue as select o_orderDate, count(distinct o_orderkey) as order_num, Sum (l_extendedprice*(1-l_discount)) as revenue from customer,orders,lineitem where c_mktsegment = And C_CUSTKey = O_CUSTKey and L_ORDERKey = o_ORDERKey and O_ORDERDate < date '1995-03-23' and O_orderdate > date '1996-02-23' // and l_shipdate > date '1996-03-23' and L_shipdate < date '1996-03-23' group  by o_orderdate order by o_orderdate; #### 3) Collect the income of parts suppliers in a certain region. 1 second, shown in a bar chart. create table region_seller_cash as select n_name, // sum(l_extendedprice * (1-l_discount)) as revenue from customer, orders, lineitem, supplier, nation, Region // Join six tables where C_CUSTKey = O_CUSTKey and L_ORDERKey = O_ORDERKey and L_suppKey = s_suppKey and c_nationKey = S_nationkey and s_nationKey = n_nationKey and n_regionKey = r_regionKey and r_name = 'EUROPE' And o_orderDATE >= date '1996-01-01' // And o_ORDERDATE < date '1996-01-01' + interval '1' year and L_shipdate > date '1996-02-23' and L_shipdate < date '1996-03-23' group by n_name ORDER by // TopN: Sort by descending income, pay attention to the grouping and sorting clause different revenue descCopy the code

Data visualization

Data visualization

Visualization of data development report graphical presentation of data visualization Visit address: dms.aliyun.com/?#to=data-v… Recommended steps:

Enter dms:dms.aliyun.com. After executing the following SQL query one by one through SQLConsole, click data visualization to enter the chart editing interface. The SQL script is as follows:

#### 1) Make statistics on the composition of global sales sum in each region within half a year. 0.5 seconds, using a pie chart. select * from sum_region_price; #### 2) Count the daily order quantity and order amount of certain items and sort by date. 1 second. Select * from items_revenue; #### 3) Collect the income of parts suppliers in a certain region. 1 second, shown in a bar chart. select * from region_seller_cash;Copy the code

Click the “Configure New Kanban” button in the upper right corner of the interface to automatically save the chart and add it to the dashboard.Make four charts in turn and place them in the newly created dashboard to get the image below.