How can USQL help Aipu New Media reduce the cost by 80% and improve the data analysis speed by 50%?

"Using USQL products, users can quickly query business data by using SQL based on the original data files and modeling data. In this way, the original data file is slightly changed, so that users do not have to pay attention to the distributed processing process of big data, and the business migration is convenient. Compared with our existing big data processing scheme, it saves 80% of the server cost, improves the data analysis speed by 50%, and shortens the development cycle of new services, which is worth recommending. "

—— Niu Deheng, CTO of Aipu New Media

What is USQL?

Data Lake Analysis (USQL) is a serverless SQL analysis and calculation engine with strong scalability and low cost. The data modeling of massive data can be easily completed, and SQL can complete data query and analysis, which greatly reduces the threshold of using big data, and does not need database administrators and operation and maintenance personnel, greatly reducing the dependence on big data engineers.

Performance of USQL under new media in EPP

The calculation cost is reduced by 97.5%

Compared with AMP's new media spending thousands of dollars in the data warehouse UDW (used to temporarily store data) every month, USQL can control the cost of processing the same data to tens of dollars every month, because USQL charges according to the actual amount of data analyzed, and the analysis price per GB of data is extremely low, so there is no charge when it is not used.

The mission cycle was shortened by 55.6%

Under the existing framework of EPP's new media, it can handle uncertain data requirements, and the average processing time of data import and analysis is 1.8 days, while USQL can save the step of data import, reduce the workload of operation and maintenance, and greatly shorten the completion time of various tasks.

The analysis efficiency is improved by 5 times.

All real business SQL of Aipu New Media has been realized, and the most time-consuming SQL analysis time can be reduced from 600 seconds to 1 18 seconds, which obviously improves the efficiency of SQL analysis.

Big data engineer investment drops to zero

At present, it is necessary to invest 20 person-days of big data engineers every month. Using USQL products, business analysts can directly complete data analysis in the object storage UFile through SQL, which greatly reduces the dependence on engineers and makes better use of limited human resources.

About Aipu New Media

Founded on 20 10, it is a high-tech company focusing on the research and development of mobile Internet products and integrated marketing of new media. It has more than 100 high-quality software, covering daily life, efficiency tools, articles and information. Mainly engaged in the promotion business based on integrated media matrix such as weather forecast and fast tour, and the advertising business based on cloud Rubik's cube DSP mobile Internet advertising distribution platform.

Data challenges faced

The data scale of Anpu's new media advertising business has reached hundreds of TB, with a daily increase of about 1tb, which requires more daily analysis. Under the existing big data processing scheme, the data department needs to invest 20 person-days of big data engineers every month and spend thousands of dollars to maintain a data warehouse cluster, and the average processing time of 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 uncertain data requirements of business analysts, the original data is temporarily loaded into the data warehouse UDW for analysis, and the clearing operation is performed after the SQL analysis is completed.

Figure: The existing architecture of Aipu New Media

Complaints from business analysts

For business analysts, the data scale reaches hundreds of TB, so they cannot complete the analysis independently and must rely heavily on big data engineers; Moreover, the processing cycle of each task is long, so if there are subsequent requirements changes or the analysis results are not up to expectations, the processing flow needs to be re-taken; In addition, when the analysis results are in doubt, the original data cannot be viewed.

Trouble in the data department

Business needs a lot of irregular data analysis every month, which cannot be completed independently, and needs to occupy the limited technical human resources of the data department; There are many rework times when the demand changes, which will lead to a lot of repetitive work; Moreover, with the increasing data scale, the cost of temporary storage of uncertain demand data by GreenPlum has been increasing.

Product attraction

Judging from the current situation, the product demand of Amp's new media is clear:

Support data analysis of hundreds of TB scale.

Business analysts can independently complete the analysis of uncertain requirements.

Have strong adaptability.

Shorten the processing time of each requirement.

Reduce the calculation cost and investment in operation and maintenance.

Select USQL products.

With the above demands, Aipu New Media noticed the USQL product launched by UCloud, and was deeply interested in its product concept of no operation and maintenance, low cost and low threshold, and immediately contacted UCloud architects to express their willingness to try it out.

In the process of communicating with its data department, UCloud architects found that the other party is pragmatic and has an open learning attitude, and is very curious about cloud computing. They have been exposed to the concepts of data lake and serverless, which laid a good foundation for the communication between the two sides. In addition, in the existing architecture, computing and storage are separated, and its original data is not strongly coupled with GreenPlum, which is convenient for replacing the analysis engine.

USQL replaced GreenPlum.

In the new architecture, USQL is used to replace GreenPlum, which was originally used to load data temporarily, which saves the process of importing data from UFile to GreenPlum, so that business analysts can directly analyze the massive data in UFile through SQL without the participation of big data engineers.

Figure: New Architecture of Aipu New Media

In addition, in the process of data docking, it is found that the data format of AMP new media is JSON and compressed in GZIP format. UCloud completed the product upgrade of USQL within one week after learning about it, which can support these two data formats, reduce the obstacles of docking, and assist AMP new media to reorganize its existing data. At present, the actual business SQL of AMP new media has been fully implemented, and product training and live demonstration have been completed at the same time.

Figure: Example of actual business SQL

The results show that the analysis efficiency can be improved by 5 times. After seeing the example demonstration of USQL, CTO calculated the cost on the spot, and felt that its performance in reducing cost, improving efficiency and reducing manpower exceeded expectations, and decided to put all offline computing services on USQL.

If you are also worried about the cost of big data analysis, welcome to join our data analysis group * * * to discuss!

How can USQL help Aipu New Media reduce the cost by 80% and improve the data analysis speed by 50%?

Tags: Architecture Capability Example Curiosity Price gzip Internet Products Additional Pictures