--Deheng Niu, CTO, Aiphone New Media
What is USQL?
USQL is a scalable, low-cost serverless SQL analytic computing engine, which can easily complete data modeling for massive data, and SQL can complete data query and analysis, greatly reducing the threshold of using big data, and eliminating the need for database administrators and operation and maintenance personnel, significantly reducing the dependence on big data engineers.
USQL's performance in the case of Aiphone Media
Calculation cost reduction of 97.5%
Compared to the thousands of dollars that Aiphone Media now spends per month on the data warehouse UDW (used for temporary storage of data), the cost of processing the same data can be controlled by USQL to a few tens of dollars per month because USQL is billed in accordance with the actual amount of analyzed data, per GB. The price of data analysis is extremely low and is not billed when not in use.
Shortening the task cycle by 55.6%
Under the existing architecture of EPRO Media, the average processing time for data import and analysis is 1.8 days for variable data requirements, while USQL can eliminate the step of data import, reduce the workload of operation and maintenance, and drastically shorten the time to complete each task.
Analysis efficiency increased by 5 times
AiphoneXinMedia has landed all the real business SQL, in which the most time-consuming SQL analysis time can be reduced from 600 seconds to 118 seconds, and the overall efficiency of SQL analysis is significantly improved.
Big Data Engineer Input Reduced to 0
Currently, 20 person-days of Big Data Engineer are required per month. Using USQL products, business analysts can complete data analysis in object storage UFile directly through SQL, which greatly reduces the dependence on engineers, and the limited human resources can be better utilized.
About Aipu New Media
Established in 2010, Aipu New Media is a high-tech company focusing on mobile Internet product development and new media integration and marketing, with more than 100 boutique software, covering daily life, efficiency tools, articles and information, etc., and mainly focusing on the promotion business with the comprehensive self-media matrix such as Weather Forecast, Quick Tour and the promotion business with the Cloud Magic Square It is mainly engaged in the promotion business based on the comprehensive self-media matrix such as Weather Forecast and Quick Tour, and the advertisement placement business based on the DSP mobile Internet advertisement distribution platform.
The data challenges faced
The data scale of Aipu New Media's advertising business reaches hundreds of TB, with a daily growth of about 1TB, and the business has many daily irregular analysis needs. Under the existing big data processing program, the data department needs to invest 20 man-days of big data engineers per month, spend thousands of additional dollars to maintain a data warehouse cluster, and the average processing time for each demand is 1.8 days. Based on the existing architecture, the data department compresses the advertisement log data and stores it in the object storage UFile. After receiving the irregular data demand from the business analysts, the raw data used for analysis is then temporarily loaded into the data warehouse UDW, and the clearing operation is implemented after completing the SQL analysis.
Figure: Existing Architecture of Aipu New Media
Business Analysts' Complaints
For the business analysts, the scale of the data reaches hundreds of terabytes, which makes it impossible for them to complete the analysis on their own, and they must rely on the big data engineers to a great extent. In addition, when there are doubts about the analysis results, it is not possible to check the original data for verification.
The troubles of the data department
The business has a lot of irregular data analysis needs every month, which can't be accomplished independently, taking up the data department's limited technical human resources; the number of times of reworking for changes in needs leads to a lot of repetitive work; and with the increasing daily growth in data size, the cost of GreenPlum, which is used for temporary storage of irregularly demanded data, has been increasing. increasing with the daily growth in data size.
Product Requirements
Based on the current situation, the product requirements of EPCOM were clear and unambiguous:
Support hundreds of terabytes of data analysis
Business analysts are able to independently complete the work of analyzing irregular requirements
Have strong Ad-Hoc capability
Shorten the time of each requirement process
Reduce Computing cost investment and operation and maintenance investment
Choose USQL product
With the above demands, EPRO New Media noticed the USQL product launched by UCloud, and was y interested in its product concept of no operation and maintenance, low cost, and low threshold, and immediately contacted the architects of UCloud to express the willingness to try it out.
In the process of communicating with their data department, UCloud architects found that the other party was pragmatic and open to learning, and that they had always been extremely curious about cloud computing, and that they had been exposed to the concepts of data lakes and Serverless, which laid a good foundation for the two parties to communicate. In addition, in the existing architecture, computing and storage are separated, and the raw data is not strongly coupled with GreenPlum, which facilitates the implementation of the program to replace the analytics engine.
USQL Replaces GreenPlum
The new architecture uses USQL to replace GreenPlum, which was used to load data temporarily, eliminating the process of importing data from UFile to GreenPlum and enabling business analysts to analyze the massive amount of data in UFile directly through SQL without the need for the involvement of big data engineers. The whole process does not require the participation of big data engineers.
Figure: new architecture of EPNew Media
In addition, the data docking found that EPNew Media's data format is JSON and compressed by GZIP format, UCloud understands that within a week after the completion of the upgrade of the USQL product, to support these two data formats, reducing the obstacles in the docking and assisting the EPNew Media to re-arrange the existing data, and the EPNew Media's actual business SQL has been completely landed, and at the same time, the business analysts can directly analyze the massive data in UFile through SQL without the participation of big data engineers. At present, the actual business SQL of Aipu New Media has been fully landed, and at the same time, the product training and on-site demonstration have been completed.
Figure: Actual business SQL example
The result shows that the analysis efficiency can be improved by 5 times. After the CTO watched the example demo of USQL, he measured the cost on the spot, and felt that its performance in cost reduction, efficiency improvement, and manpower reduction exceeded the expectation, and he has decided to put all the offline computing business on USQL.
If you are also troubled by the cost of big data analytics, welcome to join our data analytics group*** to discuss with us!
How USQL helped Aiphone Media reduce costs by 80% and increase the speed of data analysis by 50%
Tags: architecture capabilities examples curiosity price gzip internet products extraimage