A data table processing method, device, equipment and storage medium

Abstract

The application provides a data table processing method and device, equipment and a storage medium. It relates to the technical field of big data analysis. The method comprises the following steps: a first device acquires a first data table, the first data table being determined according to a plurality of second data tables to be processed, the first data table comprising data of a first type, the first type being a data type included in the plurality of second data tables; the first device determines a first processing rule according to the first type and a first correspondence relationship, the first correspondence relationship comprising a correspondence relationship between the first type and the first processing rule; and the first device processes the first data table according to the first processing rule.

Description

Technical Field

[0001] This application relates to the field of big data analytics technology, specifically to a data table processing method, apparatus, device, and storage medium. Background Technology

[0002] Currently, in the field of big data analytics, especially in multi-table query analysis, columnar database management systems (ClickHouse, CK) are generally used for online analytical processes (OLAP). However, when performing JOIN queries on multiple tables, CK typically first calculates the right table, loads the result into memory, and then matches and aggregates it with the left table. When the amount of data in the right table to be calculated is large, traditional CK cannot meet the needs of rapid analysis of a large number of tables, resulting in low efficiency in data table analysis.

[0003] Therefore, the efficiency of data table analysis needs to be improved. Summary of the Invention

[0004] This application provides a data table processing method, apparatus, device, and storage medium to improve the efficiency of data table analysis.

[0005] In a first aspect, embodiments of this application provide a data table processing method, including:

[0006] A first device acquires a first data table, which is determined based on a plurality of second data tables to be processed. The first data table includes data of a first type, which is a data type included in all the plurality of second data tables. The first device determines a first processing rule based on the first type and a first correspondence, whereby the first correspondence includes the correspondence between the first type and the first processing rule. The first device processes the first data table according to the first processing rule.

[0007] According to this method, different types can be obtained by processing multiple second data tables. The same type can be determined as the first type based on multiple data types. The data to be queried corresponding to the first type can be determined based on the first type. By combining specific processing rules, data query and analysis can be performed, which can improve the efficiency of querying or analyzing data tables.

[0008] In one possible embodiment, the method includes: the first device using data of a second type in the plurality of second data tables as data corresponding to the data of the first type in the first data table, wherein the second type does not include the first type.

[0009] According to this method, the first device can determine that the first type is the same data type in multiple second data tables to be processed, determine the different data types in the multiple second data tables as the second type, obtain the data of the first type and the data of the second type, and obtain a wide data table with multiple types, which can improve the efficiency of obtaining multiple types of data tables.

[0010] In one possible embodiment, the first data table is further determined based on at least one third data table to be processed, wherein the third data table does not include data of the first type, and both the third data table and at least one second data table include data of the third type; the method further includes: the first device determining the first processing rule based on the first type, the third type and the first correspondence, wherein the first correspondence includes the correspondence between the first type, the third type and the first processing rule.

[0011] According to this method, based on the correspondence between the third type and the first processing rule, a first data table of the first type is obtained through processing. By processing the first correspondence based on the first type and the third type, the accuracy of querying the first correspondence can be improved.

[0012] In one possible embodiment, the method further includes: the first device using data of a fourth type in the third data table as data corresponding to the second type in the first data table, wherein the second type does not include the first type.

[0013] According to this method, the first device can determine the first type based on multiple different types, obtain the first correspondence corresponding to the first type, and improve the accuracy of obtaining the first correspondence.

[0014] In one possible embodiment, the method further includes: deduplicating the data of the first type in the first data table.

[0015] According to this method, the first device can count the number of types corresponding to the deduplicated first data table, and expand and filter the types of the first data table based on the statistical results of the number of types, which can improve the accuracy of querying or analyzing the first data table.

[0016] In one possible embodiment, the first type of data includes customer ID data, and the first processing rule is used to count the number of customers; the method further includes: the first device determining the number of customer IDs in the customer ID data according to the first processing rule.

[0017] According to this method, the first device can obtain the type and the quantity of the type according to the first processing rule, perform deduplication of the quantity of the type on the distribution node, and return the data to the aggregation node after deduplication to obtain the quantity of any type, which can improve the accuracy of obtaining the type.

[0018] In one possible embodiment, the method further includes: the first data table corresponds to a first data set, the customer number data in the first data set is not duplicated with the customer number data in a second data set, and the second data set corresponds to a second device.

[0019] According to this method, the first device can be the first distributed device, which obtains the first data set. The second device can be the second distributed node, which is a different local distributed node from the first device. The second device can obtain the second data set. The first data set and the second data set are the same as the summarized data, without having to summarize the data and then summarize the data again after returning duplicate data, which can improve the efficiency of summarizing the data.

[0020] Secondly, this application provides a data table processing apparatus, the apparatus comprising:

[0021] The acquisition module is used to acquire a first data table, which is determined based on a plurality of second data tables to be processed. The first data table includes data of a first type, which is a type included in all of the plurality of second data tables. The processing module is used to determine a first processing rule based on the first type and a first correspondence, whereby the first correspondence includes the correspondence between the first type and the first processing rule. The processing module is also used to process the first data table according to the first processing rule.

[0022] In one possible embodiment, the processing module is specifically used to: treat the data of the second type in the plurality of second data tables as the data corresponding to the data of the first type in the first data table, wherein the second type does not include the first type.

[0023] In one possible embodiment, the first data table is further determined based on at least one third data table to be processed, wherein the third data table does not include data of the first type, and both the third data table and at least one second data table include data of the third type; the processing module is specifically used to: determine the first processing rule based on the first type, the third type and the first correspondence, wherein the first correspondence includes the correspondence between the first type, the third type and the first processing rule.

[0024] In one possible embodiment, the acquisition module is specifically used to: take the data of the fourth type in the third data table as the data corresponding to the data of the second type in the first data table, wherein the second type does not include the first type.

[0025] In one possible embodiment, the acquisition module is specifically used to: perform deduplication processing on the data of the first type in the first data table.

[0026] In one possible embodiment, the first type of data includes customer ID data, and the first processing rule is used to count the number of customers; the processing module is specifically used to: determine the number of customer IDs in the customer ID data according to the first processing rule.

[0027] In one possible embodiment, the first data table corresponds to a first data set, where the customer ID data in the first data set is not duplicated with the customer ID data in a second data set, and the second data set corresponds to a second device.

[0028] Thirdly, this application provides an electronic device, comprising:

[0029] Memory, used to store program instructions;

[0030] A processor is configured to invoke program instructions stored in the memory and execute the steps of the method described in any one of the first aspects according to the obtained program instructions.

[0031] Fourthly, this application provides a computer-readable storage medium storing a computer program, the computer program including program instructions that, when executed by a computer, cause the computer to perform the method described in any one of the first aspects.

[0032] Fifthly, this application provides a computer program product comprising: computer program code, which, when run on a computer, causes the computer to perform the method described in any one of the first aspects. Attached Figure Description

[0033] Figure 1 A schematic diagram of an architecture provided for an embodiment of this application;

[0034] Figure 2 A flowchart illustrating a data table processing method provided in an embodiment of this application;

[0035] Figure 3 Another flowchart provided for an embodiment of this application;

[0036] Figure 4Another flowchart provided for an embodiment of this application;

[0037] Figure 5 Another flowchart provided for an embodiment of this application;

[0038] Figure 6 A schematic diagram of a device structure provided in an embodiment of this application;

[0039] Figure 7 This is a schematic diagram of another device structure provided in an embodiment of this application. Detailed Implementation

[0040] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application. Unless otherwise specified, the embodiments and features in the embodiments of this application can be arbitrarily combined with each other. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0041] The terms "first" and "second" in the specification, claims, and accompanying drawings of this application are used to distinguish different objects, not to describe a specific order. Furthermore, the term "comprising" and any variations thereof are intended to cover non-exclusive protection. For example, a process, method, system, product, or device that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products, or devices. The term "multiple" in this application can mean at least two, for example, two, three, or more, and the embodiments of this application do not impose limitations.

[0042] The data collection, dissemination, and use in this application all comply with relevant national laws and regulations.

[0043] Before introducing a data table processing method provided by the embodiments of this application, the technical background of the embodiments of this application will be described in detail below for ease of understanding.

[0044] Currently, in the field of big data analytics, especially in the area of ​​querying and analyzing multiple data tables, columnar database management systems such as ClickHouse (CK) are generally used for online analytical processing (OLAP). Therefore, CK can serve as the platform's query and analysis engine. OLAP is a software technology that enables analysts to quickly and interactively observe information to achieve a deeper understanding of the data.

[0045] In this application, data table processing may include processes such as querying or analyzing the data table. It can be understood that the data table query process refers to retrieving specific data from the data table. The analysis process refers to analyzing the characteristics of the data table or the data in the data table, such as analyzing the quantity of a certain data item or the number of customers with different customer IDs. Currently, data queries can generally be divided into join queries and union queries. A join query refers to querying multiple data tables, joining all data types from the multiple data tables, where data types can be repeated, resulting in a single data table containing multiple data types. A union query refers to querying the common types of multiple data tables, retaining one type for each common type, resulting in a single data table without duplicate types.

[0046] When performing join queries on multiple tables, Cheat Engine (CK) typically calculates the right table first, loads the result into memory, and then matches and aggregates it with the left table. When the right table contains a large amount of data, traditional CK cannot meet the demands of rapid analysis of a vast number of tables. Furthermore, because general business intelligence (BI) platforms cannot perfectly adapt to business analysis scenarios, the standard structured query language (SQL) generated by BI platforms does not perform query syntax optimizations for individual database characteristics or data types.

[0047] SQL allows users to work on data structures at different levels, using the same language as the input interface to manage different databases with different structures, offering great flexibility. Currently, online analytical processing (OLAP) generally uses SQL for querying and analysis. SQL can perform data queries based on multiple tables, essentially concatenating multiple tables by type to create a large, wide table. The table being concatenated can be on the left, while the table used for concatenation is generally placed on the right; the former is called the left table, and the latter the right table.

[0048] Currently, the basic logic of BI platforms generally involves parsing user drag-and-drop operations and then generating standard SQL syntax. Due to the versatility of BI platforms and the relatively high barrier to entry for writing SQL, existing SQL implementations cannot fully leverage CK's high-performance distributed queries, and the efficiency of querying or analyzing data tables needs improvement.

[0049] To address the above problems, this application proposes a data table processing method, apparatus, device, and storage medium to improve the efficiency of data table analysis.

[0050] It is understood that the data table processing method provided in this application can be executed by a first device. For example, the first device can be middleware, such as the view-CK adapter or the view-CK adapter module. To support business analysis scenarios and match the characteristics of CK queries, the view-CK adapter module provided in this application fully utilizes the high throughput, distributed processing, and flexible and powerful SQL syntax of CK, solving problems such as low efficiency of multi-table joins, difficulty in controlling computing resources, and unreasonable distributed query execution plans. It also enables functions such as customizable indicator dimensions, configurable addition of indicators, and interactive multi-dimensional analysis.

[0051] The view-CK adapter module provided in this application belongs to the indicator analysis and retrieval engine. The overall architecture of the indicator analysis and retrieval engine is as follows: Figure 1 As shown in the diagram, the architecture includes a front-end, a business layer, a database layer, and a base server.

[0052] The front-end includes modules for customizing metrics, viewing and downloading report results, and saving and reusing task templates. The business layer (or business logic layer) includes modules for metadata querying, view-CK adapters, and task scheduling. The database layer includes modules for the CK cluster, MySQL (MySQL) database, and file storage. The CK cluster stores detailed business data, providing high-performance write and query services for massive amounts of data. The MySQL database stores system status data, ensuring transaction consistency and atomicity. The file system stores report results for the front-end to view and download. The basic service layer provides general big data services, including data processing, distributed storage, and data warehousing. This architecture also provides customized pages to facilitate users in customizing metrics, viewing task status, reusing task templates, and downloading report results.

[0053] The view-CK adapter module serves as the business layer in the indicator analysis and retrieval engine architecture. This module can acquire multiple types of tabular data and provide query and write services for this data. For example, it can query and write detailed business data, storing this data in the CK cluster.

[0054] The view-CK adapter module enables functions such as multi-table association optimization, aggregate query optimization, business scenario scalability, and self-service analysis. Association refers to concatenating multiple data tables based on data types to obtain a concatenated data table. Aggregation refers to filtering each data type and removing duplicate or blank data corresponding to different data types in the data tables through misaligned aggregation queries, resulting in an optimized data table. The business scenario is scalable; based on user needs, a new data table, called a connection intermediate table, can be obtained from the aggregated data table and a data table with new data types, including data of the new data types. After deduplication of the connection intermediate table, the final physical table for self-service analysis is obtained. The self-service analysis can then process the final physical table according to user-defined filtering conditions, statistical ranges, and other methods. The data table processing in this application may include querying and / or analysis, and is not specifically limited.

[0055] like Figure 2 As shown, the specific steps of a data table processing method provided in this application embodiment include:

[0056] Step 101: The first device acquires a first data table. The first data table is determined based on multiple second data tables to be processed. The first data table includes data of a first type, which is a data type included in all of the multiple second data tables.

[0057] The second data table can be a physical table, and the first data table can be an aggregated table obtained by aggregating multiple physical tables.

[0058] In one possible embodiment, the tables, intermediate tables, aggregate tables, and physical tables in the embodiments of this application all belong to data tables.

[0059] In one possible embodiment, the present application embodiment can achieve association optimization through misaligned aggregation (misaligned aggregation, misaligned UNION): that is, by using aggregation and CK conditional (if) functions, multiple second data tables to be processed can be misaligned and aggregated into a first data table, which can realize the data table connection logic.

[0060] In one possible embodiment, the first device may obtain a first data table based on a plurality of second data tables using one or more of the following processes (1), (2), and (3). Wherein, if the first data table is obtained through processes (1) and (2), the first data table is a misaligned UNION wide table, and the second data table may be one or more physical tables. If the first data table is obtained through processes (1), (2), and (3), the first data table is a final result table, and the second data table may be one or more physical tables.

[0061] Process (1)

[0062] In one possible implementation, for example, the first device can obtain multiple physical tables (or smaller tables) on the local node where the device is located. Physical tables such as customer and employee relationship table, customer basic information table, asset under management (AUM) product daily report table, and cash flow table are shown in Tables 1-4, respectively.

[0063] Table 1

[0064]

[0065] Table 2

[0066]

[0067] Table 3

[0068]

[0069] Table 4

[0070]

[0071] Optionally, the second data table may include one or more of the above physical tables, such as Tables 1 to 4. The first device may acquire multiple physical tables to be processed in process (1). Each physical table to be processed may include multiple data of the second type and corresponding to the second type. The second data table corresponds to the second type. For example, the second type may be data types such as customer manager number, customer age, average monthly customer AUM, and cash inflow amount.

[0072] Process (2)

[0073] In one possible embodiment, the first device can aggregate data from multiple physical tables by type to obtain a single data table. This data table may be referred to as a misaligned UNION wide table.

[0074] As shown in Table 5, the first device can aggregate data of different types (i.e., second types) in multiple physical tables based on data of the same type (i.e., first type) in multiple physical tables to be processed, and retain blank data corresponding to different types, thereby obtaining a misaligned UNION wide table.

[0075] For example, physical tables such as customer and employee relationship tables, customer basic information tables, AUM product daily reports, and cash flow statements all have three types: table number, customer number, and date. The first device can identify the table number, customer number, and date as the first type, and the customer manager number, customer age, average monthly AUM of the customer, and cash inflow amount as the second type.

[0076] In one possible embodiment, the first device can aggregate data corresponding to multiple physical tables according to table number, customer number, and date. The first device can then align the aggregated data tables according to data type, retaining blank data corresponding to the type.

[0077] For example, if customer age data is not available in the customer and employee relationship table, the first device can use syntax such as "SELECT 1AS table_id,cst_id,age,"AS level" to align the data.

[0078] Table 5

[0079]

[0080] Optionally, in process (2), the second data table can be multiple physical tables with some of the same type, and the first data table can be a misaligned UNION wide table.

[0081] Alternatively, the first device can obtain the aggregated intermediate table shown in Table 6 based on the misaligned UNION wide table, i.e., remove duplicate and blank data. For example, the first device can compress the data types in the misaligned UNION table into the same row according to the syntax such as "sumIf(aum,table_id=2ANDdata_dt='2022-10-31')" to meet further row-level filtering requirements.

[0082] Table 6

[0083]

[0084]

[0085] Process (3)

[0086] In one possible embodiment, the first device may acquire a third data table, which does not include data of the first type, and both the third data table and at least one second data table include data of the third type. The first device can then merge the first data table (or multiple second data tables to be processed) and the third data table to obtain a merged first data table. In the third data table, data types that are the same type as those in the second data tables but different from the first type are classified as the third type. Furthermore, data types in the third data table that are different from the data types in any of the second data tables are classified as the fourth type.

[0087] Optionally, the third data table can be an employee personnel system table, containing data such as account manager ID and account manager tenure. Since both the third data table and the second data table shown in Table 1 include the account manager ID data type, and the second data tables shown in Tables 1 to 4 do not include the account manager tenure data type, the third type can include the account manager ID, and the fourth type can include the account manager tenure. The first device can merge the third data table with multiple unprocessed second data tables, misaligned UNION wide tables, or aggregated intermediate tables to form a JOIN intermediate table, where the account manager tenure data from the employee personnel system table is used as data in the JOIN intermediate table. For example, the JOIN intermediate table is shown in Table 7.

[0088] Table 7

[0089]

[0090] Optionally, the first device can also filter the types in the JOIN intermediate table using a left-JOIN to obtain the final result table. For example, if the first device obtains an employee human resources system table, and this table does not contain customer IDs, then the employee human resources system table cannot be directly aggregated and queried with other second data tables using the CK conditional aggregation function. If data from the employee human resources system table needs to be added, then a partial type from the employee human resources system table needs to be added to the JOIN intermediate table using a left-JOIN statement in SQL. The first device can also add the type "number of customers" to the JOIN intermediate table, and after adding this type, further calculate the data corresponding to this type in the data table to obtain the final result table, as shown in Table 8.

[0091] Table 8

[0092] Account Manager Number Customer AUM Monthly Average Summary of capital inflows Customers A001 5000 2800 2

[0093] Optionally, in process (3), if the first data table before merging is a misaligned UNION wide table or an aggregated intermediate table, and the third data table is an employee human resources system table, then the first data table after merging with the third data table is a JOIN intermediate table, or the first data table after merging is the final result table.

[0094] In one possible embodiment, when duplicate types exist in the first data table, the first device can first identify the duplicate types and retain only one column or row of data for each identical type, i.e., the first device deduplicates the data table. The first device can then delete the remaining duplicate types and their corresponding data, and then count information such as the number of the first type.

[0095] In one possible embodiment, the first device can be customized for CK and indicator analysis and retrieval scenarios, extracting data from the business logic layer (such as third-type data), and reserving interfaces (such as data interfaces) to support access and switching of more business scenarios.

[0096] In one possible embodiment, the first data table corresponds to a first data set, where the customer ID data in the first data set is unique to the customer ID data in a second data set, and the second data set corresponds to a second device. The method by which the second device obtains the second data table and the second data set can be the same as the method by which the first data table and the first data set are obtained.

[0097] For example, currently, local table nodes typically deduplicate customer IDs and count the number of customers based on unique customer IDs. Then, each local table aggregates the deduplicated customer IDs and customer counts to the distributed table nodes, where the distributed nodes perform a second deduplication, counting the deduplicated customer IDs and customer counts. This statistical process is as follows: Figure 3The process before optimization is shown below. In this application, data with the same customer ID is first distributed to the same local node according to the sharding rules. That is, there are no duplicate customer IDs in the data processed by different local nodes. Secondly, each local table node deduplicates and counts the customer IDs, and then summarizes the customer count to the distributed node. The distributed node does not need to perform deduplication again; it only needs to analyze the customer counts of each local node to obtain the total customer count. Therefore, in the original scheme, the local table nodes first deduplicate the customer IDs, and then each local table summarizes the deduplicated customer IDs to the distributed table node, which then performs a second deduplication and counts the customer count. After optimization, it is necessary to design the data sharding rules reasonably to avoid secondary calculations by the distributed node. In this scenario, the customer ID can be used as the sharding key to ensure that the data corresponding to the same customer ID is distributed only on one local node. After the sharding rules are determined, the local table nodes directly complete the deduplication and counting of customer IDs. Then, only the counting results are returned to the summing node (without transmitting the customer ID set), and the summing node simply sums them up. The first device can optimize the SQL syntax by using a distributed fetch-all statement to send requests to the local table, thereby reducing the query time in specific business scenarios from 5.1 seconds to 0.8 seconds.

[0098] Specifically, the first data table corresponds to the first data set. The first device can use different data source websites as sharding sites, and each sharding site can obtain multiple first data tables. The first type of the first data table can be used as the sharding key. The first device can divide the data into a first data set composed of multiple first data tables obtained from the same node. For example, the first device can use the customer number as the sharding key to ensure that the data of the same customer is only distributed on one local node or a distribution node. The sharding rule can also be random distribution, uniform distribution, or distribution by organization number, etc. After the sharding rule is determined, the distribution node of the first device can directly perform deduplication and counting of customer numbers. Then, the distribution node only returns the counting result to the summing node (i.e., it no longer transmits customer numbers), and the summing node simply sums them up.

[0099] Step 102: Determine the first processing rule based on the first type query first correspondence relationship. The first correspondence relationship includes the correspondence relationship between the first type and the first processing rule.

[0100] In this application, the processing of the data table includes processes such as data querying and analysis. For example, for the querying process, the first processing rule is the first query rule; for the analysis process, the first processing rule is the first analysis rule.

[0101] The first correspondence can include multiple correspondences between data types and processing rules. Therefore, the corresponding processing rules can be determined based on the data type to process the data table. For example, customer ID supports the analysis of customer quantity, and the corresponding analysis rule could be a customer quantity statistics rule. Similarly, data types such as account manager ID support query rules.

[0102] In one possible embodiment, the first device can query the first correspondence based on the first type and / or the third type. For example, in the first data table shown in Table 7, the first type is customer ID, and the first device can query the first correspondence based on the first type. As another example, in the first data table shown in Table 8, the third type is customer manager ID, and the first device can query the first correspondence based on the third type. Alternatively, the first correspondence can be queried from Table 7, where the first type is customer ID and the third type is customer manager ID.

[0103] For example, if the first data type is customer ID, then the corresponding first processing rule could be an SQL statement used to count the number of customers based on their customer ID.

[0104] In one possible embodiment, the first processing rule includes SQL statements.

[0105] In one possible embodiment, for the same data type, different processing rules can be corresponding to combining at least one of the following: data filtering date range, data filtering conditions, data statistical range, statistical dimension, and statistical indicator.

[0106] In one example of determining the first processing rule in step 102, such as Figure 4 As shown, the first device can acquire multiple analytical indicators, specifically including: employee analytical indicators, customer analytical indicators, or other analytical indicators. Employee analytical indicators can be of the employee ID data type (i.e., a third data type), and customer analytical indicators can be of the customer ID data type (i.e., a first data type). Further, the first device can, optionally, combine at least one of the following filtering information—data filtering date range, data filtering conditions, data statistical range, statistical dimensions, and statistical indicators—based on the customer ID and employee ID data types to determine processing rules, i.e., obtain an SQL statement. Optionally, this SQL statement can be used to process data tables. For example, an SQL statement for querying can be used to query data in a data packet; an SQL statement for data statistics can be used to perform analyses such as customer quantity statistics. Optionally, the first device can also generate a final query SQL statement based on multiple recursive methods, whereby the SQL statement represents the processing rules corresponding to the employee ID and customer ID.

[0107] In one possible embodiment, the first device may optionally extract the similarity of the syntax at each level of the SQL statement through a designed generic interface, and generate nested sub-table query SQL statements using a recursive method. For example, the statement could be a "generatorSQL" statement, based on which the number of data columns of the table can be obtained.

[0108] like Figure 5 As shown, the first device can recursively query the data table multiple times based on SQL statements: In the first recursion, the first device obtains multiple physical tables (i.e., the second data table to be processed). In the second recursion, the first device calls the data from multiple unit tables and combines them according to certain types. If a certain type does not have corresponding data in one of the tables, the data table in the misaligned UNION table is empty, and the corresponding position is left empty. In the third recursion, a JOIN table is generated from the misaligned UNION table. The first device uses SQL statements as processing rules, and continuously queries the local node or local device according to the SQL statements based on the misaligned UNION table to retrieve the data corresponding to the specified type, filters or expands the type, and generates a distributed aggregate table, that is, generates the first data table.

[0109] Step 103: The first device processes the first data table according to the first processing rule.

[0110] Specifically, if the processing procedure is a query procedure, the result of processing the first data table can be the query result. If the processing procedure is an analysis procedure, the result of processing the first data table can be the analysis result.

[0111] based on Figure 2 The process shown involves expanding or filtering multiple data tables to be processed. Querying and analyzing the first data table means simultaneously querying and analyzing data from multiple data tables, thus improving the efficiency of querying or analyzing data tables.

[0112] In one possible embodiment, the first device can configure different aggregation filtering conditions according to different types or specific indicators to obtain aggregation function syntax. Specific indicators include: atomic indicators (i.e., aggregation conditions), derived indicators (i.e., filtering conditions), and derived indicators (i.e., logical operations). Based on specific indicators, data statistical results corresponding to a certain indicator can be filtered out. Data analysis can be performed based on the statistical results, improving the diversity of analysis and enabling self-service analysis. Currently, the basic logic of mainstream BI platforms on the market is to parse user drag-and-drop operations, generate standard SQL syntax, connect to the database through Java Database Connectivity (JDBC), and send the SQL to the computing engine. However, this automatically generated, unoptimized SQL syntax cannot fully utilize the high-performance distributed query capabilities of CK. The first device of this application can generate SQL syntax corresponding to business application scenarios based on different types or specific indicators, optimizing CK's parallel computing and distributed query at the SQL syntax level, increasing the flexibility and ease of use of analysis.

[0113] It is understandable that the larger the data volume, the more obvious the advantages of the data table processing method provided in this application become. For example, as the amount of data processed increases, the data table processing efficiency of this application is higher. The data table processing method provided in this application can implement the design of JOIN logic, reduce the JOIN process when obtaining the first data table, alleviate the performance shortcomings of CK's JOIN, and also avoid problems such as wide table processing and complex changes.

[0114] In one possible embodiment, this application also includes a full-link technical solution. This solution includes using asynchronous queue statements to address CK resource isolation issues and integrating stream and batch data access. Stream and batch integration refers to stream processing and batch processing, which are two different data processing methods. Specifically, data can be divided into historical data and real-time data based on its generation time, or into streaming data and sliced ​​data based on the level of detail. Data processing methods can be divided into streaming processing and batch processing based on window size, or into real-time processing and offline processing based on processing latency. Based on the above technical solution, the first device can form a full-link technical solution from data source access to page retrieval, realizing an indicator analysis and retrieval engine based on CK to generate flexible SQL tasks, thereby improving data analysis efficiency.

[0115] In one possible embodiment, as in Figure 1The overall architecture shown in this application also includes a self-service analysis solution. The first device loads an optional first type from the metadata management module; the first type can also be an analysis indicator type. The first device can switch analysis scenarios and perform attribute or type filtering, which supports arbitrary combinations of tree structures. The first device can set the data type to the first type, or supports existing data types or custom data types as the first type. After setting the first type, the first device can calculate statistical indicators and obtain specified aggregation methods, such as summation, averaging, or finding maximum / minimum values. The first device also instructs different aggregation filtering conditions to be configured for different indicators. After creating a task, the first device can query the task execution status and display or download structured reports.

[0116] Based on the same inventive concept, embodiments of this application provide a data table processing apparatus, please refer to... Figure 6 The device includes:

[0117] The acquisition module 601 is used to acquire a first data table, which is determined based on a plurality of second data tables to be processed. The first data table includes data of a first type, which is a type included in all the plurality of second data tables. The processing module 602 is used to determine a first processing rule based on the first type and a first correspondence, whereby the first correspondence includes the correspondence between the first type and the first processing rule. The processing module 602 is also used to process the first data table according to the first processing rule.

[0118] In one possible embodiment, the processing module 602 is specifically used to: treat the data of the second type in the plurality of second data tables as the data corresponding to the data of the first type in the first data table, wherein the second type does not include the first type.

[0119] In one possible embodiment, the first data table is further determined based on at least one third data table to be processed, wherein the third data table does not include data of the first type, and both the third data table and at least one second data table include data of the third type; the processing module 602 is specifically used to: determine the first processing rule based on the first type, the third type and the first correspondence, wherein the first correspondence includes the correspondence between the first type, the third type and the first processing rule.

[0120] In one possible embodiment, the acquisition module 601 is specifically used to: take the data of the fourth type in the third data table as the data corresponding to the data of the second type in the first data table, wherein the second type does not include the first type.

[0121] In one possible embodiment, the acquisition module 601 is specifically used to: perform deduplication processing on the data of the first type in the first data table.

[0122] In one possible embodiment, the first type of data includes customer ID data, and the first processing rule is used to count the number of customers; the processing module 602 is specifically used to: determine the number of customer IDs in the customer ID data according to the first processing rule.

[0123] In one possible embodiment, the first data table corresponds to a first data set, where the customer ID data in the first data set is not duplicated with the customer ID data in a second data set, and the second data set corresponds to a second device.

[0124] Based on the same inventive concept, this application provides an electronic device that can perform the data table processing function described above. Please refer to... Figure 7 The device includes a memory 701 and a processor 702.

[0125] The memory 701 is used to store computer programs executed by the processor 702. The memory 701 may mainly include a program storage area and a data storage area. The program storage area may store the operating system and programs required to run instant messaging functions, etc.; the data storage area may store various instant messaging information and operation instruction sets, etc.

[0126] Memory 701 may be volatile memory, such as random-access memory (RAM); memory 701 may also be non-volatile memory, such as read-only memory, flash memory, hard disk drive (HDD), or solid-state drive (SSD); or memory 701 may be any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but is not limited thereto. Memory 701 may be a combination of the above-described memories.

[0127] The processor 702 may include one or more central processing units (CPUs) or digital processing units, etc. The processor 702 is used to implement the above-described method when calling a computer program stored in the memory 701.

[0128] This application embodiment does not limit the specific connection medium between the memory 701 and the processor 702 described above. As an example, this application embodiment... Figure 7 The memory 701 and the processor 702 are connected via a bus 703, and the bus 703 is in Figure 7 The connections between other components are shown in bold lines only and are not intended to be limiting. The 703 bus can be divided into address bus, data bus, control bus, etc. For ease of illustration, Figure 7 The bus is represented by a single thick line, but this does not mean that there is only one bus or one type of bus.

[0129] Optionally, the processor 702 can be used to execute any one or more of the actions performed by the acquisition module 601 and the processing module 602.

[0130] Based on the same inventive concept, embodiments of this application provide a computer-readable storage medium. The computer program product includes computer program code, which, when executed on a computer, causes the computer to perform any of the data table processing methods discussed above. Since the principle by which the above-described computer-readable storage medium solves the problem is similar to that of the data table processing method, the implementation of the above-described computer-readable storage medium can be found in the implementation of the method; repeated details will not be elaborated further.

[0131] Based on the same inventive concept, this application also provides a computer program product, which includes computer program code. When the computer program code is run on a computer, it causes the computer to execute any of the data table processing methods discussed above. Since the principle by which the above-described computer program product solves the problem is similar to that of the data table processing method, the implementation of the above-described computer program product can be referred to the implementation of the method, and repeated details will not be described again.

[0132] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0133] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0134] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0135] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of user-operated steps to be executed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the functions specified in one or more boxes. Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, this application also intends to include such modifications and variations if they fall within the scope of the claims of this application and their equivalents.

Claims

1. A data table processing method characterized by, The method comprises: A first device acquires a first data table, the first data table being determined by misaligning and aggregating data of a second type in a plurality of second data tables to be processed using an aggregation and a conditional function of CK, and reserving blank data corresponding to different types, the first data table comprising data of a first type, the first type being a data type included in the plurality of second data tables; and the second type being a data type other than the first type in the second data tables; The first device determines a first processing rule according to the first type and a first correspondence relationship, the first correspondence relationship comprising a correspondence relationship between the first type and the first processing rule; wherein the first processing rule comprises an SQL statement; The first data table is processed according to the first processing rule; The data of the first type comprises customer number data, and the first processing rule is used to count the number of customers; and the first data table is processed according to the first processing rule, comprising: The number of customer numbers in the customer number data is determined according to the first processing rule, and the number of customer numbers is sent to a summary node, so that the summary node sums the number of customer numbers sent by the first device and the number of customer numbers sent by a second device; The first device and the second device are different distributed nodes; the number of customer numbers sent by the second device is determined based on a second data set, the customer number data in the second data set is not repeated with the customer number data in a first data set, and the first data set corresponds to the first data table.

2. The method of claim 1, wherein, The first device acquires a first data table, comprising: The first device regards the data of the second type in the plurality of second data tables as data corresponding to the data of the first type in the first data table, and the second type does not include the first type.

3. The method of claim 1, wherein, The first data table is further determined according to at least one third data table to be processed, the third data table does not include the data of the first type, and the third data table and at least one second data table both include data of a third type; The first device determines a first processing rule according to the first type and a first correspondence relationship, comprising: The first device determines the first processing rule according to the first type, the third type and the first correspondence relationship, and the first correspondence relationship comprises a correspondence relationship between the first type, the third type and the first processing rule.

4. The method of claim 3, wherein, The first device acquires a first data table, comprising: The first device regards the data of the fourth type in the third data table as data corresponding to the data of the second type in the first data table, and the second type does not include the first type.

5. The method of any one of claims 1-4, wherein, The first device acquires a first data table, comprising: The data of the first type in the first data table is processed for deduplication.

6. A data table processing apparatus characterized by comprising: The method comprises: The acquisition module is configured to acquire a first data table, the first data table being determined by performing misaligned aggregation on second-type data in a plurality of second data tables to be processed using an aggregation and CK conditional function, and by retaining blank data corresponding to different types, the first data table including first-type data, the first type being a data type included in each of the plurality of second data tables, and the second type being a data type other than the first type in the second data tables; The processing module is configured to determine a first processing rule according to the first type and a first correspondence relationship, the first correspondence relationship including a correspondence relationship between the first type and the first processing rule, and the first processing rule including an SQL statement; The processing module is further configured to process the first data table according to the first processing rule. The first-type data includes customer number data, and the first processing rule is used to count the number of customers; and the processing module is specifically configured to: determine the number of customer numbers in the customer number data according to the first processing rule, and send the number of customer numbers to a summary node, so that the summary node sums the number of customer numbers sent by a first device and the number of customer numbers sent by a second device; The first device and the second device are different distributed nodes, the number of customer numbers sent by the second device is determined based on a second data set, customer number data in the second data set is not repeated with customer number data in a first data set, and the first data set corresponds to the first data table.

7. The apparatus of claim 6, wherein, The processing module is specifically configured to: treat the second-type data in the plurality of second data tables as data corresponding to the first-type data in the first data table, the second type not including the first type.

8. The apparatus of claim 6, wherein, The first data table is further determined according to at least one third data table to be processed, the third data table not including the first-type data, and the third data table and at least one second data table both including third-type data; The processing module is specifically configured to: determine the first processing rule according to the first type, the third type, and the first correspondence relationship, the first correspondence relationship including a correspondence relationship between the first type, the third type, and the first processing rule.

9. The apparatus of claim 8, wherein, The acquisition module is specifically configured to: treat fourth-type data in the third data table as data corresponding to the second-type data in the first data table, the second type not including the first type.

10. The apparatus of any one of claims 6-9, wherein, The acquisition module is specifically configured to: perform deduplication processing on the first-type data in the first data table.

11. An electronic device, comprising: The memory is configured to store program instructions; The processor is configured to invoke the program instructions stored in the memory and execute the method according to any one of claims 1-5. The computer-readable storage medium stores a computer program, the computer program including program instructions, the program instructions causing the computer to execute the method according to any one of claims 1-5 when executed by the computer.

12. A computer-readable storage medium, characterized in that, ​ 13. A computer program product, characterised in that, The computer program product comprises computer program code which, when run on a computer, causes the computer to perform the above-mentioned method according to any one of claims 1-5.

Images