The entire data analysis system consists of data collection, data storage and calculation, data query and analysis and data application.

Original architecture diagram:

class=”rich_pages wxw-img js_insertlocalimg” src=”https://mmbiz.qpic.cn/mmbiz_png/Sq4ia0xXeMC5svjOx12UQ7HiaaCU6QddoDmqD5qTrdicgLk7DCUMic7LYoQ3OzANgbmQ6a5Go5aWibCopSwA87ziaBSA/640?wx_fmt=png”>

Data acquisition

Data storage and computing

offline data processing

: Hive’s highly scalable batch processing capabilities are used to undertake all the ETL and data model processing work of offline data warehouses.

Data query and analysis

The data sharing layer mainly provides the underlying data storage and query sharing interface of external services. The data after offline ETL is written to the RDBMS or MPP database, which is oriented to a variety of downstream services, providing OLAP query and analysis capabilities for different scenarios such as Tableau BI, multi-dimensional fixed reports, and Adhoc ad hoc queries. The application side perfectly serves the BI reporting platform, ad-hoc query and analysis platform and data visualization platform (Control Tower) The

data application layer of the

data application layer

is mainly for management and operation personnel, and the query requires low latency response and the demand is also iterating. Ad hoc queries for data analysts require OLAP engines to support complex SQL processing and the ability to quickly select data from massive data.

Pros

Cons

The core data of channel warehouse distribution management (SEC) comes from two blocks: one is the consumer business; The second is SMB small and medium-sized enterprises (Think, Yangtian). Based on these data, according to the needs of different business scenarios, relevant business statistical indicators are summarized and query and analysis services are provided to the outside world.

Before the introduction of DorisDB, the

original solution

used a large number of Hive tasks to clean and process business logic, and part of the data after cleaning and processing was retained in Hive, and part of the data was written to MySQL/SQL Server to achieve data landing. The front-end BI connects to Hive, MysSQL, SQL Server, etc. through the Presto calculation engine to achieve report analysis and data visualization.

Technical pain points

The original architecture mainly has the following two problems:

Therefore, we hope to optimize the original system, the core idea is to use an OLAP engine to unify this layer, the requirements for the OLAP engine are relatively high:

After a lot of research, DorisDB is a good fit for the overall requirements of the data center. DorisDB’s efficient query capabilities can provide integrated services for data center data reporting. The new architecture has the following advantages:

DorisDB-based solution

post-upgrade architecture diagram:

datasheet design

1) Data Model Design

DorisDB itself provides three data models: detail model/aggregation model/update model. For SEC business, the current detailed model is the mainstay, and if there are other scenarios in the future, consider applying other models.

2) Data partitioning/bucket splitting

DorisDB provides data partitioning and bucket splitting functions, which can improve the performance of detailed query in historical inventory and turnover scenarios. For example, a common query scenario for historical inventory query is to query the inventory turnover in a certain period of time in the past, we can partition according to the outbound time in DorisDB, filter out unnecessary partitioned data, reduce the amount of data in the entire query for rapid positioning, minimize the data range covered by the query statement, partitioning, bucketing, prefix indexing and other capabilities, which can greatly improve the concurrency of point checks. These features are also of great significance for business growth and facing high concurrency scenarios that may occur in the future. Querying the historical trajectory data of a material barcode (SN) can quickly retrieve all the historical storage trajectory information of the barcode, helping us efficiently complete the full life cycle of the supply chain.

Materialized

view

We use DorisDB materialized view to build in real time and on demand, flexibly increase the characteristics of deletion and transparency, and establish a materialized view based on inventory material SN granularity, product type characteristic granularity, warehouse granularity, and distributor granularity. Based on these materialized views, queries can be greatly accelerated.

Data

import data import DorisDB

here uses two solutions:

1) On the basis of the Broker Load provided by DorisDB, the tables of the offline data warehouse Hive are imported into DorisDB.

2) Import data from SQL Server and MySQL to DorisDB through DataX tools.

DorisDB uses effects

flexible modeling to improve development efficiency

Combined with the wide table model and the star model, the wide table and materialized view can ensure report performance and concurrency, while the star model can allow the AP to model as in TP, directly carry out association queries, do not need to rely on wide table preparation in all scenarios, and improve data consistency and development efficiency. In addition, many tables are in MySQL, and we expose queries through DorisDB external tables, eliminating the process of data import, greatly reducing the development and migration cycle of the business side. DorisDB’s distributed Join capability is very strong, combined with the ability of View to build a unified view layer, query different BI reports under the surface, improve the consistency of indicator caliber, and reduce repeated development.

The BI experience is excellent

The early part of the BI visualization was built based on SQL Server and MySQL. After the continuous optimization and enrichment of requirements of some Kanban boards, coupled with multi-dimensional flexible condition filtering, each loading is very slow, and some Tableau reports take a long time to load, which is unacceptable to the business. After the introduction of DorisDB, we use DataX to import SQL Server data into DorisDB, where we use the DorisDB-Writer plugin, the underlying encapsulated Stream-Load interface, and the vectorization import efficiency is very high. MySQL can be imported into select streaming through foreign tables, or it can be directly queried by foreign tables, which is very convenient. Tableau charts are out in seconds, and the experience has made a qualitative leap.

Low O&M costs

Data centers are a very core online service, so they have very high requirements for high availability and flexible capacity expansion. DorisDB supports multiple copies of data, and the simple architecture of FE and BE is only two roles, which can ensure the high availability of the entire cluster in the event of a single node failure. In addition, DorisDB can be elastically scaled online under the scale of big data, and there is no Down Time when expanding, which will not affect online business, which is also very needed.

Lenovo began to investigate DorisDB in April this year (2021), and the POC testing phase used 1/4 of the resources, which perfectly replaced the Presto cluster of dozens of nodes, and DorisDB has been online and running stably. After the introduction of DorisDB, it realizes the unification of data services, greatly simplifies the offline data processing link, and also ensures the query delay requirements, which will be used to improve data services and query capabilities in more business scenarios. Finally, thanks to Keystone Technology for its strong support, I also hope that DorisDB will become better and better as a new generation MPP database leader with strong performance!

END

.”