Step 1: Prepare low-cost storage of business data and DLA tables

  • OSS (www.aliyun.com/product/oss) is the preferred solution for low cost data stored on the cloud
  • DLA (www.aliyun.com/product/dat)… It is a low-cost, serverless solution that supports OSS data query and analysis on the cloud

Refer to the following document use cases for OSS on business data storage and DLA table: yq.aliyun.com/articles/62…

This step takes about 5 minutes.

Step 2: Use the DataV to access the DLA to create a large data screen

  • DataV (data.aliyun.com/visual/data…). It is the optimal solution for big data visualization on the cloud

1. Prepare DataV

Take the business data in the first step as an example to build a large screen of enterprise sales data, which mainly involves three tables:

  • Orders table, sales order data;
  • Customer table, customer record data;
  • Nation table, national record data;

Log on to the console DataV console: datav.aliyun.com/data to purchase the basic version:



You can connect to the DLA service using a “mysql-compatible” mode, as the base version does in this example.

2. Prepare a DLA data source

Click my Data, Add Data



Edit data source:

  • Select compatible MySQL database type.
  • Name as required;
  • According to link information on DLA console datalakeanalytics.console.aliyun.com/overview (classic network) and in ali cloud station letter received a user name and password information, fill in the corresponding column, select the target TPCH database, save.



3. Prepare a large-screen template

Click My Visualization, New Visualization



Select “Sales Real-time Monitoring Template” and click “Create”



In this sample large screen, the target is to display sales data for each country. Remove the components in the red box below.



Then reposition a component on the canvas for layout and aesthetics.



In order to display the sales data of each country, you need a world map, delete the existing China map component, and then select “3D Flat World Map” from “Map” in the navigation bar.



4. Configure data for components on the large screen

4.1 Configure data for the map

Render map data as follows:

  • Select the map, in the data TAB page, “Data source type database select existing data source my data **” in the CONFIGURATION of DLA data source;
  • In SQL, fill in the following SQL to calculate the data sorted by country sales;
select sum(o_totalprice) total_price, n_nationkey, n_name, n_id
from orders 
join customer on o_custkey = c_custkey 
join nation on c_nationkey = n_nationkey 
group by n_nationkey, n_name, n_id 
order by total_price desc;
Copy the code

  • Insert the n_ID and total_price columns, respectively, into the above SQL return column;
  • According to service data update requirements (see Attachment 1: Architecture Diagram), select “Automatic update request” for large-screen data, for example, once every 60 seconds;
  • Then click “Refresh Data”.



4.2 Configure data for total sales

Configure data for total sales as follows:

  • Select the total sales component, in the data TAB page, “Data source type database select existing data source my data **” DLA data source configuration;
  • In SQL, fill in the following SQL to calculate the total sales data;
select sum(o_totalprice) total_price
from orders;
Copy the code

  • Insert the total_price column returned by SQL above into the value column;
  • According to service data update requirements (see Attachment 1: Architecture Diagram), select “Automatic update request” for large-screen data, for example, once every 60 seconds;
  • Then click “Refresh Data”.



4.3 Configure data by country sales rank

Configure data for total sales as follows:

  • Select the sales country ranking component, in the data TAB page, “Data source type database select existing data source my data **” DLA data source configuration;
  • In SQL, fill in the following SQL to calculate the data sorted by country sales;
select sum(o_totalprice) total_price, n_nationkey, n_name from orders join customer on o_custkey = c_custkey join nation on c_nationkey = n_nationkey group by n_nationkey,  n_name order by total_price desc;Copy the code

  • SQL > total_price and n_NAME;
  • According to service data update requirements (see Attachment 1: Architecture Diagram), select “Automatic update request” for large-screen data, for example, once every 60 seconds;
  • Then click “Refresh Data”.



5. Preview and publish large screens

Click “Preview” in the upper right corner to see what the larger screen will look like.



After confirmation, you can publish:



This step is expected to take 10 minutes.

Attached: schematic architecture

Compared with using traditional database and DataV to build large screen, DataV + DLA + OSS is another low-cost option. In most large screen scenarios where data is updated with low frequency, DataV + DLA + OSS is far cheaper than DataV + traditional database. Combined with the generation of business data, the overall architecture is as follows:

Possible service data output of large screen data refresh link:

  • Incremental data generated by service applications is directly uploaded to the OSS and updated to the service data screen through periodic query.
  • Service logs generated by service applications are collected from the log service, delivered to the OSS periodically (with a delay of at least 5 minutes), and updated to the service data screen through periodic query.



# Ali Cloud open Hi shopping season # Lucky draw gift!

Click on this lucky draw: [Ali Cloud] open Hi shopping season, lucky draw good gift


The original link

This article is the original content of the cloud habitat community, shall not be reproduced without permission.