Streaming calculation dimension table data processing method and device, electronic equipment and medium

Abstract

The invention relates to a streaming computing dimension table data processing method and device, electronic equipment and a medium, and belongs to the technical field of data processing.The method comprises the steps that a dimension table and a fact table to be associated are obtained; partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; mapping the partitioned dimension table and a cache server to obtain a mapping relationship, and putting the partitioned dimension table into the cache server; partitioning the fact table according to a second preset rule to obtain a partitioned fact table; reading the partitioned fact table on the basis of a streaming computing framework, distributing the partitioned fact table to an operator for processing, and associating the partitioned fact table with the dimension table in the corresponding cache server in the operator on the basis of the mapping relation. According to the method, the dimension table is partitioned and respectively stored in the cache servers, and the fact table in the operator takes the corresponding cache server according to the mapping table to associate the dimension table data, so that the processing efficiency of the fact table and the dimension table is improved.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, electronic device and medium for processing dimension table data in stream computing. Background Art

[0002] Modern e-commerce has more extreme requirements for user classification and profiling. It uses anti-fraud and risk control assessment methods to screen professional users for participation in activities and provide assessment models for its financial businesses. On the one hand, the industry's requirements for data processing emphasize the real-time nature of data processing, and on the other hand, data processing in business is more complex.

[0003] Traditionally, data processing involves fewer dimension tables, smaller data volumes, and stable data, which are usually easier to process. However, the business scale and business forms of the e-commerce industry are changing rapidly, and the dimension table data that needs to be processed is large in volume and updated quickly. The data processing of business fact tables and dimension tables has gradually become a performance bottleneck. The current technical solution adopts a streaming computing approach, which first collects unified data in an incremental manner, then distributes the collected data to each computing unit based on a distributed computing approach, and finally reads dimension table data from the cache in the computing unit for associated processing according to the needs of business logic.

[0004] In summary, the problems existing in the existing technologies are: 1) As the amount of data in the dimension table gradually increases, the query performance decreases; 2) When a large number of dimension tables are updated, the response speed of the overall system is affected; 3) Once the cache server fails to respond, it will cause a system avalanche, affecting all computing tasks.

[0005] In summary, the prior art lacks a method to divide and conquer dimension tables, thereby improving the efficiency of fact table and dimension table processing. Summary of the invention

[0006] In view of this, it is necessary to provide a method, device, electronic device and medium for processing dimension table data in a streaming computing manner, so as to achieve the purpose of improving the processing efficiency of fact tables and dimension tables.

[0007] In order to achieve the above object, the present invention provides a method for processing dimension table data by stream computing, comprising: Get the dimension table and fact table to be associated; Partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; Mapping the partitioned dimension table with the cache server to obtain a mapping relationship, and storing the partitioned dimension table in the cache server; Partitioning the fact table according to a second preset rule to obtain a partitioned fact table; The partitioned fact table is read based on the streaming computing framework and dispatched to the operator for processing. In the operator, the partitioned fact table is associated with the dimension table in the corresponding cache server based on the mapping relationship.

[0008] In a possible implementation, the fields of the fact table include: one or more of order number, commodity sku, commodity unit, purchase quantity, payment method, payment amount, and purchase time.

[0009] In a possible implementation, the dimension table includes one or more of a time dimension table, a supplier dimension table, and a product dimension table.

[0010] In a possible implementation manner, each data in the dimension table has an identification number, and partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table includes: Sorting the dimension table according to the identification number of each data in the dimension table to obtain a sorted dimension table; The sorted dimension table is partitioned according to the identification numbers in the sorted dimension table to obtain partitioned dimension tables, wherein each dimension table in the partitioned dimension tables contains a preset amount of data.

[0011] In a possible implementation manner, the mapping relationship includes: a one-to-one relationship and a many-to-one relationship.

[0012] In a possible implementation, each data in the fact table has an identification number, and partitioning the fact table according to a second preset rule to obtain a partitioned fact table includes: Sorting the fact table according to the identification number of each data in the fact table to obtain a sorted fact table; The sorted fact table is partitioned according to the identification number in the sorted fact table to obtain partitioned fact tables, each of which contains a preset amount of data.

[0013] In a possible implementation, the stream computing framework includes: one or more of Apache Storm, Apache Spark Streaming, and Apache Flink.

[0014] On the other hand, the present invention also provides a device for processing dimension table data by stream computing, comprising: A data acquisition module is used to acquire the dimension table and fact table to be associated; A dimension table partitioning module, used for partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; A mapping relationship acquisition module, used for mapping the partitioned dimension table with the cache server to obtain a mapping relationship, and storing the partitioned dimension table in the cache server; A fact table partitioning module, used for partitioning the fact table according to a second preset rule to obtain a partitioned fact table; The data association module is used to read the partitioned fact table based on the streaming computing framework and dispatch it to the operator for processing, and associate the partitioned fact table with the dimension table in the corresponding cache server based on the mapping relationship in the operator.

[0015] On the other hand, the present invention also provides an electronic device, including a memory and a processor, wherein: The memory is used to store programs; The processor is coupled to the memory and is used to execute the program stored in the memory to implement the steps in the method for processing dimension table data in a stream computing manner described in any one of the above implementations.

[0016] On the other hand, the present invention also provides a computer-readable storage medium for storing computer-readable programs or instructions, which, when executed by a processor, can implement the steps in a method for processing dimension table data in a streaming computing manner described in any of the above-mentioned implementations.

[0017] The beneficial effects of the present invention are as follows: a method for processing dimension table data by streaming computing provided by the present invention first obtains a dimension table and a fact table to be associated, and then partitions the dimension table according to a first preset rule to obtain a partitioned dimension table, so that the dimension table of big data is separated into multiple dimension tables, and further maps the partitioned dimension table with a cache server to obtain a mapping relationship, and puts the partitioned dimension table into the cache server, thereby realizing divide and conquer of the dimension table, and further partitions the fact table according to a second preset rule to obtain a partitioned fact table, thereby realizing partition reading and improving reading efficiency, and finally reading the partitioned fact table based on a streaming computing framework and dispatching it to an operator for processing, and in the operator, the partitioned fact table is associated with the dimension table in the corresponding cache server based on the mapping relationship, and the present invention partitions the dimension table and stores it in the cache server respectively, so that when the fact table is associated with the dimension table, it is only necessary to go to the corresponding cache server to associate the dimension table data according to the corresponding mapping table, thereby improving the efficiency of fact table and dimension table processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1 A method flow chart of an embodiment of a method for processing dimension table data in stream computing provided by the present invention; Figure 2 For the present invention Figure 1A schematic flow chart of an embodiment of S102; Figure 3 A schematic flow chart of an embodiment of a device for processing dimension table data by stream computing provided by the present invention; Figure 4 A schematic structural diagram of an embodiment of an electronic device provided by the present invention. DETAILED DESCRIPTION

[0019] The preferred embodiments of the present invention are described in detail below in conjunction with the accompanying drawings, wherein the accompanying drawings constitute a part of this application and are used together with the embodiments of the present invention to illustrate the principles of the present invention, but are not used to limit the scope of the present invention.

[0020] Before presenting the embodiments, the following terms are explained.

[0021] Fact table, in a multidimensional data warehouse, a table that holds detailed values ​​or facts of a measure is called a "fact table". Fact tables usually contain a large number of rows. The main feature of a fact table is that it contains numeric data (facts), and this numeric information can be summarized to provide data about the unit as history. Each fact table contains an index consisting of multiple parts, which contains the primary key of the dependency dimension table as a foreign key, and the dimension table contains the characteristics of the fact record. Dimension tables can be regarded as windows for users to analyze data. Dimension tables contain the characteristics of fact records in fact tables. Some characteristics provide descriptive information, and some characteristics specify how to summarize fact table data to provide useful information to analysts. Dimension tables contain hierarchical structures of characteristics that help summarize data. For example, a dimension table containing product information usually contains a hierarchy that divides products into several categories such as food, beverages, and non-consumer products. Each of these categories of products is further subdivided several times until each product reaches the lowest level.

[0022] Stream computing, also known as real-time computing, is a computing mode that performs real-time analysis and processing on continuous data streams. It is different from batch processing mode, which analyzes and processes data within a certain period of time, while stream computing processes data generated in real time. Stream computing can provide real-time data analysis results and support rapid decision-making and response.

[0023] SKU (Stock Keeping Unit) is defined as the smallest available unit for inventory control. For a product, when its brand, model, configuration, grade, color, packaging capacity, unit, production date, shelf life, use, price, origin and other attributes are different from other products, it can be called a single product. In chain retail stores, a single product is sometimes called a SKU, which is translated into Chinese as the smallest stock unit. For example, a SKU in textiles usually indicates specifications, colors and styles.

[0024] The present invention provides a method, device, electronic device and medium for processing dimension table data in stream computing, which are respectively described below.

[0025] Figure 1 A flow chart of an embodiment of the method for processing dimension table data by stream computing provided by the present invention is as follows: Figure 1 As shown, the method for processing dimension table data in stream computing includes: S101, obtaining the dimension table and fact table to be associated; S102, partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; S103, mapping the partitioned dimension table with the cache server to obtain a mapping relationship, and storing the partitioned dimension table in the cache server; S104, partitioning the fact table according to a second preset rule to obtain a partitioned fact table; S105. Read the partitioned fact table based on the streaming computing framework and dispatch it to the operator for processing. In the operator, associate the partitioned fact table with the dimension table in the corresponding cache server based on the mapping relationship.

[0026] Compared with the prior art, the present embodiment provides a method for processing dimension table data by streaming computing, which first obtains the dimension table and fact table to be associated, and then partitions the dimension table according to a first preset rule to obtain a partitioned dimension table, so that the dimension table of big data is separated into multiple dimension tables, and further maps the partitioned dimension table with a cache server to obtain a mapping relationship, and puts the partitioned dimension table into the cache server, thereby realizing divide and conquer of the dimension table, and further partitions the fact table according to a second preset rule to obtain a partitioned fact table, thereby realizing partitioned reading and improving reading efficiency, and finally reading the partitioned fact table based on the streaming computing framework and dispatching it to an operator for processing, and in the operator, the partitioned fact table is associated with the dimension table in the corresponding cache server based on the mapping relationship, and the present invention partitions the dimension table and stores it in the cache server respectively, so that when the fact table is associated with the dimension table, it only needs to go to the corresponding cache server to associate the dimension table data according to the corresponding mapping table, thereby improving the efficiency of fact table and dimension table processing.

[0027] It should be noted that the data processed in the embodiment of the present invention is e-commerce data, and the amount of e-commerce data is large. It is understandable that the present invention is also applicable to big data processing in other scenarios.

[0028] In some embodiments of the present invention, the fields of the fact table include one or more of: order number, commodity sku, commodity unit, purchase quantity, payment method, payment amount and purchase time.

[0029] In some embodiments of the present invention, the dimension table includes one or more of a time dimension table, a supplier dimension table, and a product dimension table.

[0030] In some embodiments of the present invention, each data in the dimension table has an identification number. In step S102, Figure 2 As shown, partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table includes: S201, sorting the dimension table according to the identification number of each data in the dimension table to obtain a sorted dimension table; S202: partition the sorted dimension table according to the identification numbers in the sorted dimension table to obtain partitioned dimension tables, wherein each dimension table in the partitioned dimension tables contains a preset amount of data.

[0031] In a specific embodiment of the present invention, the dimension table is partitioned as follows: assuming that the identification code of each data in the dimension table is 1-10000, the dimension table is now partitioned according to 100 data in each partition, that is, the first partition is the dimension table with identification numbers 1-100, the second partition is the dimension table with identification numbers 101-200, the third partition is the dimension table with identification numbers 201-300, and so on.

[0032] In some embodiments of the present invention, the mapping relationship includes: a one-to-one relationship and a many-to-one relationship. Specifically, the one-to-one relationship is that the dimension table of the first partition is stored in the first cache server, the dimension table of the second partition is stored in the second cache server, and so on. Specifically, the many-to-one relationship is that the dimension table of the first partition and the dimension table of the second partition are placed in the first cache server, and the dimension table of the third partition and the dimension table of the fourth partition are placed in the second cache server.

[0033] In some embodiments of the present invention, each data in the fact table has an identification number, and partitioning the fact table according to a second preset rule to obtain a partitioned fact table includes: Sorting the fact table according to the identification number of each data in the fact table to obtain a sorted fact table; The sorted fact table is partitioned according to the identification number in the sorted fact table to obtain partitioned fact tables, each of which contains a preset amount of data.

[0034] In some embodiments of the present invention, the streaming computing framework includes: one or more of Apache Storm, Apache Spark Streaming and Apache Flink.

[0035] In a specific embodiment of the present invention, in step S105, the fact table is associated with the dimension table, including query and update.

[0036] In a specific embodiment of the present invention, a real-time stream calculation of an order fact table of a platform-based e-commerce is taken as an example: the fields of an order fact table are roughly as follows: Order number, product sku, product unit, purchase quantity, payment method, payment amount and order time.

[0037] Assume that the demand requires real-time statistics of product sales in multiple dimensions, including product sku, order time period, supplier, etc. From the perspective of product sku as a dimension, in addition to the platform's own operations, there are many brand suppliers on large e-commerce platforms, which leads to a very large number of sku. For example, if the spu is iphone15, then the models, colors, memory sizes, and supply channels contained therein are combined to form a large number of sku, such as Telecom Edition-Silver-128G-Self-operated is an independent sku. In this case, statistics on the sku dimension can be implemented as follows.

[0038] Step 1: Determine the data volume of the dimension tables corresponding to the analysis dimensions involved in the fact table, and select the largest one or more; There are many dimensions in the fact table. For example, in the order fact table, orders can be counted by time dimension, product dimension, supplier dimension or other dimensions. The dimension selected in this embodiment is the dimension with the largest amount of data. For example, in the supplier dimension, there may be 100,000 suppliers, and one supplier can supply 100,000 SKUs. Then the number of SKUs must be more than the number of suppliers, so SKU can be selected as the classification dimension.

[0039] Step 2: Divide the dimension table intervals according to preset rules, such as the maximum number of dimensions. Take the product sku dimension table as an example. Use the sku code interval as the partition boundary and synchronize the partitioned data to different caches regularly for subsequent stream computing processes. For example, for the product dimension table, use every 100,000 sku numbers as the interval standard.

[0040] Step 3: Map the partition numbering interval to the cache server as a basis for subsequent local queries and updates.

[0041] The cache server IP is mapped to the dimension range, for example, sku number 00000001-00100000 corresponds to IP 192.168.0.1. Every 100,000 or 1 million dimension data is stored in one server.

[0042] To be more specific, each cache server can store multiple dimensional intervals. For example, the cache server with IP address 192.168.0.1 not only stores the sku dimension information numbered 00000001-00100000, but also stores all supplier information or other information in the South China region.

[0043] Step 4: Use the same partitioning rules to read partitions when streaming computing reads the fact table, and dispatch it to subsequent operators for processing.

[0044] Step 5: During the calculation process, the operator will find the corresponding data based on the mapping relationship between the number interval and the cache server when reading and calculating the dimension. Specifically, assuming that sku is used as the dimension partition standard, each time a fact table data is read, its sku number is obtained, the sku number is compared with the interval in the mapping table, the corresponding operator server is found, and the current fact table data is sent to the corresponding operator server for calculation.

[0045] For example: Mapping table sku interval cache server corresponds to operator server 0001-1000192.168.0.1172.168.100.1 / 2 In summary, the present invention provides a method for processing dimension table data in big data streaming computing, by pre-identifying large data volume dimension tables, isolating and caching them according to preset classification rules, and then in the streaming computing process, the same rules are adopted when pulling fact tables for data governance calculations, and the tables are distributed to computing nodes with specific caches for calculations, thereby dividing the large data volume problem.

[0046] In order to better implement a method for processing dimension table data in a stream computing in an embodiment of the present invention, based on a method for processing dimension table data in a stream computing, correspondingly, Figure 3 As shown, an embodiment of the present invention further provides a device for processing dimension table data by stream computing. The device 300 for processing dimension table data by stream computing includes: The data acquisition module 301 is used to acquire the dimension table and fact table to be associated; A dimension table partitioning module 302, configured to partition the dimension table according to a first preset rule to obtain a partitioned dimension table; A mapping relationship acquisition module 303 is used to map the partitioned dimension table with the cache server to obtain a mapping relationship, and put the partitioned dimension table into the cache server; A fact table partitioning module 304 is used to partition the fact table according to a second preset rule to obtain a partitioned fact table; The data association module 305 is used to read the partitioned fact table based on the streaming computing framework and dispatch it to the operator for processing, and associate the partitioned fact table with the dimension table in the corresponding cache server based on the mapping relationship in the operator.

[0047] The device 300 for processing dimension table data by streaming computing provided in the above embodiment can implement the technical solution described in the above embodiment of the method for processing dimension table data by streaming computing. The specific implementation principles of the above modules or units can refer to the corresponding contents in the above embodiment of the method for processing dimension table data by streaming computing, which will not be repeated here.

[0048] like Figure 4 As shown, the present invention also provides an electronic device 400. The electronic device 400 includes a processor 401, a memory 402 and a display 403. Figure 4 Only some components of the electronic device 400 are shown, but it should be understood that it is not required to implement all of the components shown, and more or fewer components may be implemented instead.

[0049] In some embodiments, the processor 401 may be a central processing unit (CPU), a microprocessor or other data processing chip, used to run program codes or process data stored in the memory 402, such as a method for processing dimension table data in a streaming computing manner in the present invention.

[0050] In some embodiments, processor 401 may be a single server or a server group. The server group may be centralized or distributed. In some embodiments, processor 401 may be local or remote. In some embodiments, processor 401 may be implemented in a cloud platform. In some embodiments, the cloud platform may include a private cloud, a public cloud, a hybrid cloud, a community cloud, a distributed cloud, an internal cloud, a multi-cloud, etc., or any combination thereof.

[0051] In some embodiments, the memory 402 may be an internal storage unit of the electronic device 400, such as a hard disk or memory of the electronic device 400. In other embodiments, the memory 402 may also be an external storage device of the electronic device 400, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc., equipped on the electronic device 400.

[0052] Furthermore, the memory 402 may include both an internal storage unit of the electronic device 400 and an external storage device. The memory 402 is used to store application software installed in the electronic device 400 and various data.

[0053] In some embodiments, the display 403 may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, an OLED (Organic Light-Emitting Diode) touch device, etc. The display 403 is used to display information on the electronic device 400 and to display a visual user interface. The components 401-403 of the electronic device 400 communicate with each other via a system bus.

[0054] In one embodiment, when the processor 401 executes a program for processing dimension table data in a stream computing process in the memory 402, the following steps may be implemented: Get the dimension table and fact table to be associated; Partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; Mapping the partitioned dimension table with the cache server to obtain a mapping relationship, and putting the partitioned dimension table into the cache server; Partitioning the fact table according to a second preset rule to obtain a partitioned fact table; The partitioned fact table is read based on the streaming computing framework and dispatched to the operator for processing. In the operator, the partitioned fact table is associated with the dimension table in the corresponding cache server based on the mapping relationship.

[0055] It should be understood that: when the processor 401 executes a program for processing dimension table data in a stream computing in the memory 402, in addition to the above functions, other functions may also be implemented. For details, please refer to the description of the corresponding method embodiment above.

[0056] Furthermore, the embodiment of the present invention does not specifically limit the type of the electronic device 400 mentioned, and the electronic device 400 may be a portable electronic device such as a mobile phone, a tablet computer, a personal digital assistant (PDA), a wearable device, a laptop computer, etc. Exemplary embodiments of portable electronic devices include but are not limited to portable electronic devices equipped with IOS, Android, Microsoft or other operating systems. The above-mentioned portable electronic devices may also be other portable electronic devices, such as a laptop computer with a touch-sensitive surface (e.g., a touch panel). It should also be understood that in some other embodiments of the present invention, the electronic device 400 may not be a portable electronic device, but a desktop computer with a touch-sensitive surface (e.g., a touch panel).

[0057] Those skilled in the art will appreciate that all or part of the processes of the above-mentioned embodiments can be implemented by instructing related hardware through a computer program, and the program can be stored in a computer-readable storage medium, wherein the computer-readable storage medium is a disk, an optical disk, a read-only storage memory, or a random access memory, etc.

[0058] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by any technician familiar with the technical field within the technical scope disclosed by the present invention should be covered within the protection scope of the present invention.

Claims

1. A method for processing dimension table data by stream computing, characterized in that: include: Get the dimension table and fact table to be associated; Partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; Mapping the partitioned dimension table with the cache server to obtain a mapping relationship, and storing the partitioned dimension table in the cache server; Partitioning the fact table according to a second preset rule to obtain a partitioned fact table; The partitioned fact table is read based on the streaming computing framework and dispatched to the operator for processing. In the operator, the partitioned fact table is associated with the dimension table in the corresponding cache server based on the mapping relationship.

2. The method for processing dimension table data by stream computing according to claim 1, characterized in that: The fields of the fact table include: order number, product sku, product unit, purchase quantity, One or more of payment method, payment amount, and purchase time.

3. The method for processing dimension table data by stream computing according to claim 1, characterized in that: The dimension table includes: one or more of a time dimension table, a supplier dimension table and a product dimension table.

4. The method for processing dimension table data by stream computing according to claim 1, characterized in that: Each data in the dimension table has an identification number, and partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table includes: Sorting the dimension table according to the identification number of each data in the dimension table to obtain a sorted dimension table; The sorted dimension table is partitioned according to the identification numbers in the sorted dimension table to obtain partitioned dimension tables, wherein each dimension table in the partitioned dimension tables contains a preset amount of data.

5. The method for processing dimension table data by stream computing according to claim 4, characterized in that: The mapping relationship includes: a one-to-one relationship and a many-to-one relationship.

6. The method for processing dimension table data by stream computing according to claim 1, characterized in that: Each data in the fact table has an identification number, and the fact table is partitioned according to a second preset rule to obtain a partitioned fact table, including: Sorting the fact table according to the identification number of each data in the fact table to obtain a sorted fact table; The sorted fact table is partitioned according to the identification number in the sorted fact table to obtain partitioned fact tables, each of which contains a preset amount of data.

7. The method for processing dimension table data by stream computing according to claim 1, characterized in that: The streaming computing framework includes: one or more of Apache Storm, Apache Spark Streaming and Apache Flink.

8. A device for processing dimension table data by stream computing, characterized in that: include: A data acquisition module is used to acquire the dimension table and fact table to be associated; A dimension table partitioning module, used for partitioning the dimension table according to a first preset rule to obtain a partitioned dimension table; A mapping relationship acquisition module, used for mapping the partitioned dimension table with the cache server to obtain a mapping relationship, and putting the partitioned dimension table into the cache server; A fact table partitioning module, used for partitioning the fact table according to a second preset rule to obtain a partitioned fact table; The data association module is used to read the partitioned fact table based on the streaming computing framework and dispatch it to the operator for processing, and associate the partitioned fact table with the dimension table in the corresponding cache server based on the mapping relationship in the operator.

9. An electronic device, characterized in that: comprising a memory and a processor, wherein: The memory is used to store programs; The processor is coupled to the memory and is used to execute the program stored in the memory to implement the steps in the method for processing dimension table data in a streaming computing manner as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: Used to store computer-readable programs or instructions, which, when executed by a processor, can implement the steps in a method for processing dimension table data in a streaming computing manner as described in any one of claims 1 to 7 above.

Images