Data table processing method, apparatus, device, and medium

Abstract

The present disclosure provides a data table processing method, which can be applied to the field of computers and big data. The data table processing method comprises: parsing a query statement to obtain a parsing result; determining table relationship information and table field information corresponding to the query statement according to the parsing result, wherein the table relationship information records the names of a plurality of data tables having a correlation relationship, and the table field information records the name of a data table and the correlation field of the data table; determining a transaction correlation weight of each query statement according to the number of online transactions corresponding to different query statements in a preset time period; determining a data table weight of the table relationship information and a correlation field weight of the table field information according to the correspondence between each transaction correlation weight, the table relationship information and the table field information and the query statement; and storing a plurality of data tables in fragments according to the data table weight and the correlation field weight. The present disclosure also provides a data table processing device, equipment, storage medium and program product.

Description

Technical Field

[0001] This disclosure relates to the fields of computer technology and big data technology, and specifically to a data table processing method, apparatus, device, medium, and program product. Background Technology

[0002] With the development of computer technology, multiple data tables can be stored in different database shards. Based on the relationships between the data tables, multiple data tables can be queried. Alternatively, the same data table can be split and stored in multiple database shards, and information from the same data table can be queried based on the relationships between related fields.

[0003] In the process of realizing the inventive concept of this disclosure, the inventors found that the efficiency of data table query was low and could not meet the query needs of relevant users. Summary of the Invention

[0004] In view of the above problems, this disclosure provides data table processing methods, apparatus, devices, media and program products.

[0005] According to a first aspect of this disclosure, a data table processing method is provided, comprising:

[0006] Parse the query statement to obtain the parsing results, which include the relationships between multiple data tables in the query statement and the association fields used to associate the data tables.

[0007] Based on the above analysis results, the table relationship information and table field information corresponding to the above query statement are determined. The table relationship information records the names of multiple data tables that are related, and the table field information records the names of the data tables and the related fields of the data tables.

[0008] Based on the number of online transactions corresponding to different query statements within a preset time period, determine the transaction association weight of each query statement.

[0009] Based on the correspondence between each of the above transaction association weights, the above table relationship information, the above table field information, and the above query statement, determine the data table weights of the above table relationship information and the association field weights of the above table field information.

[0010] Based on the weights of the aforementioned data tables and the weights of the associated fields, the aforementioned data tables are sharded and stored.

[0011] According to embodiments of this disclosure, the data table processing method further includes:

[0012] According to the preset response rules, the response association weight of each of the above query statements is determined, wherein the preset response rules are determined based on the preset response time of the batch transaction;

[0013] Based on the correspondence between each of the above response association weights, the above table relationship information, the above table field information and the above query statement, update the above table relationship information and the above table field information to obtain the updated data table weights and updated association field weights.

[0014] According to embodiments of this disclosure, determining the response association weight for each of the above query statements according to preset response rules includes:

[0015] According to the above-mentioned preset response rules, the preset response time for batch transactions is determined, wherein the above-mentioned batch transactions correspond to at least one of the above-mentioned query statements;

[0016] Based on the preset response time of the different batch transactions mentioned above, determine the batch transaction weight of each batch transaction mentioned above;

[0017] Based on the correspondence between the aforementioned batch transactions and the aforementioned query statements, the batch transaction weight of the aforementioned batch transactions is determined as the response association weight of the aforementioned query statements.

[0018] According to embodiments of this disclosure, the transaction association weight of each of the above-mentioned query statements is determined based on the number of online transactions corresponding to different query statements within a preset time period, including:

[0019] The number of online transactions corresponding to each query statement within the preset time period is normalized to obtain the transaction association weight of each query statement within the preset time period.

[0020] According to embodiments of this disclosure, the aforementioned preset time periods include multiple time periods;

[0021] The transaction association weight for each of the above query statements is determined based on the number of online transactions corresponding to different query statements within a preset time period, and further includes:

[0022] The transaction association weights corresponding to each identical query statement within multiple preset time periods are summed to obtain the transaction association weights for each query statement within the multiple preset time periods.

[0023] According to embodiments of this disclosure, the data table weight of the table relationship information and the associated field weight of the table field information are determined based on the correspondence between each of the above-mentioned transaction association weights, the above-mentioned table relationship information, and the above-mentioned table field information and the above-mentioned query statement, including:

[0024] Based on the associated weight of each of the above transactions, and the correspondence between the above table relationship information and the above query statements, determine the initial data table weight of each of the above table relationship information.

[0025] The weights of the initial data tables that record the same data table name are summed to obtain the data table weights of the above table relationship information.

[0026] Based on the associated weight of each of the above transactions and the correspondence between the above table field information and the above query statement, determine the initial associated field weight of each of the above table field information.

[0027] The weights of the initial related fields that record the same table name and the same related fields are summed to obtain the related field weights of the above table field information.

[0028] According to embodiments of this disclosure, storing multiple data tables in shards based on the aforementioned data table weights and the aforementioned associated field weights includes:

[0029] The weights of the data tables and the weights of the related fields mentioned above are processed using a clustering algorithm to obtain the first clustering result and the second clustering result;

[0030] Based on the first clustering result and the second clustering result, the data tables are stored in shards, including vertical sharding and horizontal sharding.

[0031] According to embodiments of this disclosure, the data table processing method further includes:

[0032] Based on the data table weights of the aforementioned table relationship information, the page displays the first degree of association information between multiple related data tables; and

[0033] Based on the weights of the associated fields in the above table information, the second degree of association information between the above data table and its associated fields is displayed on the above page.

[0034] A second aspect of this disclosure provides a data table processing apparatus, comprising:

[0035] The parsing module is used to parse the query statement and obtain the parsing results. The parsing results include the relationship between multiple data tables in the query statement and the association fields used to associate the data tables.

[0036] The first determining module is used to determine the table relationship information and table field information corresponding to the above query statement based on the above parsing results. The table relationship information records the names of multiple data tables that have an association relationship, and the table field information records the names of the data tables and the associated fields of the data tables.

[0037] The transaction association weight determination module is used to determine the transaction association weight of each of the above query statements based on the number of online transactions corresponding to different query statements within a preset time period.

[0038] The second determining module is used to determine the data table weight of the table relationship information and the associated field weight of the table field information based on the correspondence between each of the above-mentioned transaction association weights, the above-mentioned table relationship information, and the above-mentioned table field information and the above-mentioned query statement; and

[0039] The sharded storage module is used to shard and store multiple data tables according to the weights of the data tables and the weights of the associated fields.

[0040] A third aspect of this disclosure provides an electronic device comprising: one or more processors; and a memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors perform the data table processing method described above.

[0041] A fourth aspect of this disclosure also provides a computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, cause the processor to perform the data table processing method described above.

[0042] The fifth aspect of this disclosure also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described data table processing method. Attached Figure Description

[0043] The foregoing contents, as well as other objects, features, and advantages of this disclosure, will become clearer from the following description of embodiments with reference to the accompanying drawings, in which:

[0044] Figure 1 This diagram illustrates an application scenario of a data table processing method and apparatus according to embodiments of the present disclosure.

[0045] Figure 2 A flowchart illustrating a data table processing method according to an embodiment of the present disclosure is shown schematically.

[0046] Figure 3 A flowchart illustrating the determination of data table weights for table relationship information and associated field weights for table field information according to embodiments of the present disclosure is shown.

[0047] Figure 4 A flowchart illustrating a data table processing method according to another embodiment of the present disclosure is shown schematically;

[0048] Figure 5 This diagram illustrates an application scenario of a data table processing method according to another embodiment of the present disclosure.

[0049] Figure 6 This diagram illustrates an application scenario of a data table processing method according to another embodiment of the present disclosure.

[0050] Figure 7 A schematic block diagram of a data table processing apparatus according to embodiments of the present disclosure is shown; and

[0051] Figure 8 A block diagram schematically illustrates an electronic device suitable for implementing a data table processing method according to an embodiment of the present disclosure. Detailed Implementation

[0052] The embodiments of the present disclosure will now be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the disclosure. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of the present disclosure for ease of explanation. However, it will be apparent that one or more embodiments may be practiced without these specific details. Furthermore, descriptions of well-known structures and techniques are omitted in the following description to avoid unnecessarily obscuring the concepts of the present disclosure.

[0053] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit this disclosure. The terms “comprising,” “including,” etc., as used herein indicate the presence of the stated features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.

[0054] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein are to be interpreted in a manner consistent with the context of this specification, and not in an idealized or overly rigid way.

[0055] When using expressions such as "at least one of A, B, and C", they should generally be interpreted in accordance with the meaning that is commonly understood by a person skilled in the art (e.g., "a system having at least one of A, B, and C" should include, but is not limited to, a system having A alone, a system having B alone, a system having C alone, a system having A and B, a system having A and C, a system having B and C, and / or a system having A, B, and C, etc.).

[0056] With the development of computer technology, multiple data tables can be stored in different database shards. Based on the relationships between the data tables, multiple tables can be queried. Alternatively, the same data table can be split across multiple database shards for storage. Information from the same data table can be queried based on the relationships between related fields. Data tables can be vertically or horizontally sharded according to actual business needs. However, sharded storage of data tables may result in longer query execution times, leading to lower query efficiency and failing to meet actual business requirements.

[0057] Embodiments of this disclosure provide a data table processing method, including:

[0058] The query statement is parsed to obtain the parsing results, which include the relationships between multiple data tables in the query statement and the association fields used to associate the data tables. Based on the parsing results, the table relationship information and table field information corresponding to the query statement are determined. The table relationship information records the names of the multiple data tables with relationships, and the table field information records the names of the data tables and the association fields of the data tables. Based on the number of online transactions corresponding to different query statements within a preset time period, the transaction association weight of each query statement is determined. Based on the correspondence between each transaction association weight, table relationship information, table field information, and query statement, the data table weight of the table relationship information and the association field weight of the table field information are determined. Based on the data table weight and association field weight, the multiple data tables are sharded and stored.

[0059] According to embodiments of this disclosure, the transaction association weight of each query statement can be determined by statistically analyzing the proportion of online transactions corresponding to the query statements. Since a query statement can correspond to relationships between data tables and related fields contained in the data tables, the data table weights of the table relationship information and the related field weights of the table field information can be determined based on each transaction association weight. Data table weights characterize the degree of association between data tables, and related field weights characterize the degree of association between a data table and its related fields. Therefore, by horizontally and vertically sharding multiple data tables based on their data table weights and related field weights, the query speed between data tables can be improved, the query execution time for retrieving data tables through query statements can be reduced, and the execution efficiency of database operations can be improved.

[0060] In the technical solution disclosed herein, the acquisition, storage, and application of user personal information comply with the provisions of relevant laws and regulations, necessary confidentiality measures have been taken, and there is no violation of public order and good morals.

[0061] In the technical solution disclosed herein, the user's authorization or consent is obtained before acquiring or collecting the user's personal information.

[0062] Figure 1 The illustration shows an application scenario of the data table processing method and apparatus according to embodiments of the present disclosure.

[0063] like Figure 1 As shown, application scenario 100 according to this embodiment may include terminal devices 101, 102, and 103, network 104, and server 105. Network 104 is used as a medium to provide a communication link between terminal devices 101, 102, and 103 and server 105. Network 104 may include various connection types, such as wired or wireless communication links or fiber optic cables, etc.

[0064] Users can use terminal devices 101, 102, and 103 to interact with server 105 via network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, and 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social media platform software, etc. (for example only).

[0065] Terminal devices 101, 102, and 103 can be various electronic devices with displays and web browsing capabilities, including but not limited to smartphones, tablets, laptops, and desktop computers.

[0066] Server 105 can be a server that provides various services, such as a backend management server that supports websites browsed by users using terminal devices 101, 102, and 103 (for example only). The backend management server can analyze and process data such as received user requests, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal devices.

[0067] It should be noted that the data table processing method provided in this embodiment can generally be executed by server 105. Correspondingly, the data table processing apparatus provided in this embodiment can generally be located in server 105. The data table processing method provided in this embodiment can also be executed by a server or server cluster that is different from server 105 and capable of communicating with terminal devices 101, 102, 103 and / or server 105. Correspondingly, the data table processing apparatus provided in this embodiment can also be located in a server or server cluster that is different from server 105 and capable of communicating with terminal devices 101, 102, 103 and / or server 105.

[0068] It should be understood that Figure 1 The number of terminal devices, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of terminal devices, networks, and servers can be included.

[0069] The following will be based on Figure 1 The described scene, through Figures 2-6 The data table processing method of the disclosed embodiments will be described in detail.

[0070] Figure 2 A flowchart illustrating a data table processing method according to an embodiment of the present disclosure is shown schematically.

[0071] like Figure 2 As shown, the data table processing method may include operations S201 to S205.

[0072] In operation S201, the query statement is parsed to obtain the parsing results. The parsing results include the relationships between multiple data tables in the query statement and the association fields used to associate the data tables.

[0073] According to embodiments of this disclosure, multiple data tables can be associated with the same association field. Correspondingly, the same data table can contain multiple different association fields, so that the data table can be associated with different data tables through different association fields.

[0074] According to embodiments of this disclosure, the same query statement can retrieve multiple different data tables through the associated fields contained in the data tables. This allows multiple different data tables, as well as the associated fields between different data tables, to have a corresponding relationship with the query statement. Therefore, parsing the query statement can yield a parsing result that includes the relationships between multiple data tables in the query statement and the associated fields used to associate the data tables.

[0075] In operation S202, based on the parsing results, the table relationship information and table field information corresponding to the query statement are determined. The table relationship information records the names of multiple data tables that are related, and the table field information records the names of the data tables and the related fields of the data tables.

[0076] According to embodiments of this disclosure, table relationship information may include information recording the names of different data tables with related relationships. For example, parsing query statement SQL1, the parsed result is included in query statement SQL1, and the names of the data tables with related relationships may include (T1, T2, ..., T...). i ). T i This indicates the name of the data table. The parsed result may also include join fields (C1, C2, ..., C...) used to link data tables across multiple tables. i ), C i This represents the related fields. The table relationship information obtained from the parsing results is in RSQL. 1mIt can be represented as (T1, T m ), representing data table T1 and data table T m Related. Table field information CSQL 1m It can be represented as (T1, C) m ), representing the relationship between data table T1 and the associated field C. m With data table T m Related. Table relationship information RSQL 1m CSQL table field information 1m Corresponding to the query statement SQL1.

[0077] It should be understood that, based on the different parsing results obtained after parsing different query statements, multiple different table relationship information and multiple different table field information can be obtained. These multiple different table relationship information may contain records with the same table name. For example, the table relationship information corresponding to query statement SQL1 is RSQL. 1m It can be represented as (T1, T m ), and the table relationship information corresponding to the query statement SQL2 in RSQL 2m It can also be represented as (T1, T) m ).

[0078] In operation S203, the transaction association weight of each query statement is determined based on the number of online transactions corresponding to different query statements within a preset time period.

[0079] According to embodiments of this disclosure, online transactions may include, for example, transfer transactions and repayment transactions. A single online transaction can be completed using one or more query statements. A single query statement may correspond to one or more online transactions. Therefore, within a preset time period, the number of online transactions corresponding to different query statements may be the same or different. The transaction association weight of each query statement is determined based on the number of online transactions corresponding to it. The transaction association weight can characterize the importance of the query statement in relation to online transactions within a preset time period. The transaction association weight can be represented numerically.

[0080] It should be noted that the number of online transactions within a preset time period can be the total number of online transactions within that preset time period, or, if there are multiple preset time periods, the number of online transactions within a preset time period can be the average number of online transactions within each preset time period.

[0081] In operation S204, based on the correspondence between each transaction association weight, table relationship information, table field information and query statement, the data table weight of the table relationship information and the association field weight of the table field information are determined.

[0082] In operation S205, multiple data tables are sharded and stored according to the data table weight and the weight of the associated fields.

[0083] According to embodiments of this disclosure, for example, if the transaction association weight of query statement SQL1 is represented as 0.3, the table relationship information RSQL corresponding to query statement SQL1 can be... 1m Data table weights and table field information in CSQL 1m The weight of the related field is determined to be 0.3. Based on the table weights of the relationship information for each table, the sum of the table weights of the relationship information for tables with the same table name can be determined. Furthermore, based on the weights of the related fields of each table field, the sum of the weights of the related fields for tables with the same name and related fields can be determined. Thus, the table weights between different tables, and the related field weights between a table and its related fields, can be determined.

[0084] According to embodiments of this disclosure, the transaction association weight of each query statement can be determined by statistically analyzing the proportion of online transactions corresponding to the query statements. Since a query statement can correspond to relationships between data tables and related fields contained in the data tables, the data table weights of the table relationship information and the related field weights of the table field information can be determined based on each transaction association weight. Data table weights characterize the degree of association between data tables, and related field weights characterize the degree of association between a data table and its related fields. Therefore, by horizontally and vertically sharding multiple data tables based on their data table weights and related field weights, the query speed between data tables can be improved, the query execution time for retrieving data tables through query statements can be reduced, and the execution efficiency of database operations can be improved.

[0085] According to an embodiment of this disclosure, operation S203, determining the transaction association weight of each query statement based on the number of online transactions corresponding to different query statements within a preset time period, may include the following operations.

[0086] The number of online transactions corresponding to each query statement within a preset time period is normalized to obtain the transaction association weight of each query statement within the preset time period.

[0087] For example, within a preset time period, the online transactions include query statements SQL1 and SQL2. Query SQL1 corresponds to 100 online transactions, and query SQL2 corresponds to 50 online transactions. Normalizing the number of online transactions for each query statement within this preset time period yields a transaction association weight of 0.66 for query SQL1 and 0.34 for query SQL2. By normalizing the number of online transactions for each query statement within the preset time period, the range of transaction association weights can be narrowed, which is beneficial for determining the subsequent weights of data tables and associated fields.

[0088] According to embodiments of this disclosure, the preset time period includes multiple time periods.

[0089] Operation S203, which determines the transaction association weight of each query statement based on the number of online transactions corresponding to different query statements within a preset time period, may also include the following operations.

[0090] The transaction association weights corresponding to each identical query statement within multiple preset time periods are summed to obtain the transaction association weights for each query statement within the multiple preset time periods.

[0091] For example, within a first preset time period, the online exchange includes queries SQL1 and SQL2. Within this first preset time period, the transaction association weight of query SQL1 is 0.66, and the transaction association weight of query SQL2 is 0.34. Within a second preset time period, the online exchange includes queries SQL2 and SQL3. Within this second preset time period, the transaction association weight of query SQL2 is 0.34, and the transaction association weight of query SQL3 is 0.66. Therefore, for multiple preset time periods—the first preset time period and the second preset time period—the transaction association weight of query SQL1 is 0.66, the transaction association weight of query SQL2 is 1, and the transaction association weight of query SQL3 is 0.66.

[0092] According to embodiments of this disclosure, the transaction association weights corresponding to each identical query statement within multiple preset time periods are summed. The number of online transactions within multiple preset time periods can comprehensively reflect the degree of association between each query statement and online transactions. The transaction association weights characterize this degree of association, laying the foundation for the subsequent generation of data table weights and associated field weights.

[0093] Figure 3 A flowchart illustrating the determination of data table weights for table relationship information and associated field weights for table field information according to an embodiment of the present disclosure is shown.

[0094] like Figure 3 As shown, operation S204, which determines the data table weight of the table relationship information and the associated field weight of the table field information based on the correspondence between each transaction association weight, table relationship information and table field information and the query statement, may include operations S301 to S304.

[0095] In operation S301, the initial data table weight of each table relationship information is determined based on the weight of each transaction association and the correspondence between table relationship information and query statements.

[0096] In operation S302, the weights of the initial data tables that record the same data table name are summed to obtain the data table weights of the table relationship information.

[0097] According to embodiments of this disclosure, for example, the transaction association weight of query statement SQL1 is 1.1, and the table relationship information RSQL corresponding to query statement SQL1... 1m It can be represented as (T1, T m ), table relationship information RSQL 1m The initial data table weights can be the transaction association weight of query SQL1 (1.1). The table relationship information corresponding to query SQL2 is also provided in RSQL. 2m It can be represented as (T1, T m The transaction association weight of query SQL2 is 1.9, and the table relationship information is in RSQL. 2m The initial table weights can be 1.9, which is the transaction association weight of the SQL2 query statement. This is because the table relationship information is derived from RSQL. 1m Relationship information between tables in RSQL 2m Records with the same data table name (T1, T...) m Therefore, the table relationship information is displayed in RSQL. 1m Relationship information between tables in RSQL 2m Summing the initial data table weights yields the result represented as (T1, T...). m The table weight for the table relationship information is 3. Furthermore, for ease of representation, the table weight and the table name in the table relationship information can be represented by a vector (T1, T...). m LJ 1m ) indicates that LJ 1m Represents table relation information (T1, T) m The weight of the data table.

[0098] In operation S303, the initial associated field weight of each table field is determined based on the associated weight of each transaction and the correspondence between table field information and query statements.

[0099] In operation S304, the initial associated field weights of records with the same data table name and the same associated field are summed to obtain the associated field weights of the table field information.

[0100] According to embodiments of this disclosure, for example, the transaction association weight of query statement SQL1 is 1.1, and the table field information CSQL corresponding to query statement SQL1... 1m It can be represented as (T1, C) m ), table field information CSQL 1m The initial association field weights can be the transaction association weight of query SQL1 (1.1). The table field information corresponding to query SQL2 (CSQL) is also included. 2m It can be represented as (T1, C) m The transaction association weight of the query statement SQL2 is 1.9, and the table field information is CSQL. 2m The initial association field weight can be 1.9, the transaction association weight of the SQL2 query statement. This is because the table field information is CSQL... 1m CSQL table field information 2m Records with the same table name and the same associated fields (T1, T...) m Therefore, the table field information is stored in CSQL. lm CSQL table field information 2m Summing the weights of the associated fields yields an expression (T1, T...). m The weight of the associated fields in the table field information is 3.

[0101] Figure 4 A flowchart illustrating a data table processing method according to another embodiment of the present disclosure is shown schematically.

[0102] like Figure 4 As shown, the data table processing method may also include operations S301 to S302.

[0103] In operation S401, the response association weight of each query statement is determined according to the preset response rules, wherein the preset response rules are determined based on the preset response time of the batch transaction.

[0104] In operation S402, based on the correspondence between each response's association weight, table relationship information, and table field information and the query statement, the table relationship information and table field information are updated to obtain the updated data table weight and the updated association field weight.

[0105] According to an embodiment of this disclosure, operation S401, determining the response association weight of each query statement according to preset response rules, includes the following operations.

[0106] According to the preset response rules, the preset response time of batch transactions is determined, wherein each batch transaction corresponds to at least one query statement; based on the preset response time of different batch transactions, the batch transaction weight of each batch transaction is determined; based on the correspondence between batch transactions and query statements, the batch transaction weight of the batch transactions is determined as the response association weight of the query statement.

[0107] According to embodiments of this disclosure, batch transactions may include transactions processed according to preset batch processing rules, such as reconciliation transactions. A single batch transaction may correspond to multiple query statements, and for the same query statement, there may be one or more batch transactions. The preset response time may include the response time for executing batch transactions. Different preset response times can be set based on the actual needs and importance of different batch transactions. Preset response rules are determined based on the preset response time, and the response association weights corresponding to the batch transactions can be determined. Based on the correspondence between batch transactions and query statements, the corresponding association weights of the batch transactions can be determined as the response association weights of the query statements. The data table weights and associated field weights are updated based on the correspondence between query statements and table information, and the correspondence between query statements and table field information. This allows the data table weights and associated field weights to reflect the preset response time corresponding to the batch transactions, providing a basis for subsequently sharding and storing multiple data tables. For example, data tables corresponding to batch transactions with shorter preset response times can be stored in the same data shard to meet the preset response time of the batch transactions.

[0108] According to embodiments of this disclosure, for example, the response association weight of query statement SQL1 is 1.1, and the table relationship information RSQL corresponding to query statement SQL1... 1m It can be represented as (T1, T m The response association weight for query SQL2 is 1.9, and the table relationship information corresponding to query SQL2 is RSQL. 2m It can be represented as (T1, T m Due to table relationship information in RSQL 1m Relationship information between tables in RSQL 2m Records with the same data table name (T1, T...) m Therefore, the table relationship information is displayed in RSQL. 1m Relationship information between tables in RSQL 2m After summing the response association weights, update the table relationship information (T1, T... m LJ 1m The data table weights, and the updated table relationship information can be represented as (T1, T...). m LJ 1m PL 1m ), (LJ 1m PL1m ) can represent the updated weight of the data table, or (LJ 1m +PL 1m This can also represent the updated weight of the data table. It should be understood that the updated weights of related fields can be obtained using the same or similar methods.

[0109] According to embodiments of this disclosure, storing multiple data tables in shards based on data table weights and associated field weights includes:

[0110] Clustering algorithms are used to process the weights of data tables and related fields to obtain the first clustering result and the second clustering result;

[0111] Based on the first and second clustering results, multiple data tables are stored in shards, including vertical sharding and horizontal sharding.

[0112] According to embodiments of this disclosure, clustering algorithms may include, for example, the K-Means algorithm, the mean-shift clustering algorithm, etc. Processing data table weights using clustering algorithms can involve using the weights of table relationship information as input to the clustering algorithm. Since the table relationship information records the names of data tables with related relationships, the first clustering result output by the clustering algorithm can identify multiple data tables with close relationships as the same group, indicating that data tables in the same group can be stored in the same shard. Based on the first clustering result, the number of shards for vertical sharding can be determined, and multiple data tables with close relationships can be stored in the same database shard.

[0113] Using the same or similar methods, the degree of association between the data table and the associated fields can be characterized based on the second clustering results, and the data table and the associated fields can be horizontally sharded and stored based on the second clustering results to meet the query requirements of the database.

[0114] It should be noted that those skilled in the art can also use the first and second clustering results as a reference to facilitate the manual application of vertical and horizontal sharding strategies to multiple data tables.

[0115] According to embodiments of this disclosure, the data table processing method may further include the following operations.

[0116] Based on the table weights in the table relationship information, the page displays the first degree of association between multiple related tables; and based on the weights of the related fields in the table field information, the page displays the second degree of association between the related fields of the tables.

[0117] According to embodiments of this disclosure, the first correlation information can be the updated data table weights. Multiple data tables with related relationships displayed on the page can be represented by nodes, and the first correlation information can be represented as the edge relationships between the multiple data tables. Specifically, for example, the first correlation information can be represented by the width of a line segment used to characterize the edge relationship.

[0118] Accordingly, the second association information can characterize the edge relationship between the data table and the associated field, and can be characterized by the line segment width used to characterize the edge relationship.

[0119] Figure 5 The diagram illustrates an application scenario of a data table processing method according to another embodiment of the present disclosure.

[0120] like Figure 5 As shown, page 500 may include nodes Node1, Node2, Node3, and Node4. Node1, Node2, Node3, and Node4 represent data tables T1, T2, T3, and T4, which have related relationships, respectively. Data table T1 and data table T2 can be related through the association field C2. The initial relationship between data table T1 and data table T2 can be represented by the line segment width of edge relationship 512. Data table T1 and data table T3 can be related through the association field C3. The initial relationship between data table T1 and data table T3 can be represented by the line segment width of edge relationship 513. Data table T1 and data table T4 can be related through the association field C4. The initial relationship between data table T1 and data table T4 can be represented by the line segment width of edge relationship 514.

[0121] On page 500, the width of the line segments displaying the edge relationship represents the first degree of association between data tables with related relationships. This can intuitively show the degree of association between data tables to relevant personnel, thus providing an intuitive basis for vertically partitioning and storing multiple data tables.

[0122] Figure 6 The diagram illustrates an application scenario of a data table processing method according to another embodiment of the present disclosure.

[0123] like Figure 6 As shown, page 600 may include nodes Node1, Node2, Node3, and Node4. Node1 can represent data table T1, and nodes Node2, Node3, and Node4 can represent the associated fields C2, C3, and C4 of data table T1, respectively. Data table T1 and data table T2 can be associated through the associated field C2, data table T1 and data table T3 can be associated through the associated field C3, and data table T1 and data table T4 can be associated through the associated field C4.

[0124] The second degree of association between data table T1 and associated field C2 can be represented by the line segment width of edge relationship 612. The second degree of association between data table T1 and associated field C3 can be represented by the line segment width of edge relationship 613. The second degree of association between data table T1 and associated field C4 can be represented by the line segment width of edge relationship 614.

[0125] On page 600, the width of the line segment representing the edge relationship is used to represent the second degree of association between the data table and the associated field. This can intuitively show the degree of association between the data table and the associated field to relevant personnel, thus providing an intuitive basis for horizontally sharding and storing multiple data tables.

[0126] Based on the above data table processing method, this disclosure also provides a data table processing apparatus. The following will be combined with... Figure 7 The device is described in detail.

[0127] Figure 7 A schematic block diagram of a data table processing apparatus according to an embodiment of the present disclosure is shown.

[0128] like Figure 7 As shown, the data table processing device 700 of this embodiment includes a parsing module 710, a first determination module 720, a transaction association weight determination module 730, a second determination module 740, and a sharded storage module 750.

[0129] The parsing module 710 is used to parse the query statement and obtain the parsing result, which includes the relationship between multiple data tables in the query statement and the association field used to associate the data tables in the multiple data tables.

[0130] The first determining module 720 is used to determine the table relationship information and table field information corresponding to the query statement based on the parsing results. The table relationship information records the names of multiple data tables that are related, and the table field information records the names of the data tables and the related fields of the data tables.

[0131] The transaction association weight determination module 730 is used to determine the transaction association weight of each query statement based on the number of online transactions corresponding to different query statements within a preset time period.

[0132] The second determining module 740 is used to determine the data table weight of the table relationship information and the associated field weight of the table field information based on the correspondence between each transaction association weight, table relationship information and table field information and the query statement.

[0133] The sharded storage module 750 is used to shard and store multiple data tables according to the data table weight and the weight of the associated fields.

[0134] According to embodiments of this disclosure, the data table processing method may further include: a response association weight determination module and a data table weight update module.

[0135] The response association weight determination module is used to determine the response association weight of each query statement according to preset response rules, wherein the preset response rules are determined based on the preset response duration of batch transactions.

[0136] The data table weight update module is used to update the table relationship information and table field information based on the correspondence between each response association weight, table relationship information and table field information and the query statement, so as to obtain the updated data table weight and the updated association field weight.

[0137] According to embodiments of this disclosure, the response association weight determination module may include: a response duration determination unit, a batch transaction weight determination unit, and a response association weight determination unit.

[0138] The response duration determination unit is used to determine the preset response duration of batch transactions according to preset response rules, wherein the batch transaction corresponds to at least one query statement.

[0139] The batch transaction weight determination unit is used to determine the batch transaction weight of each batch transaction based on the preset response time of different batch transactions.

[0140] The response association weight determination unit determines the batch transaction weight of the batch transaction as the response association weight of the query statement based on the correspondence between the batch transaction and the query statement.

[0141] According to embodiments of this disclosure, the transaction association weight determination module may include: a first transaction association weight determination unit.

[0142] The first transaction association weight determination unit is used to normalize the number of online transactions corresponding to each query statement within a preset time period to obtain the transaction association weight of each query statement within the preset time period.

[0143] According to embodiments of this disclosure, the preset time period includes multiple time periods.

[0144] The transaction association weight determination module may also include: a second transaction association weight determination unit.

[0145] The second transaction association weight determination unit is used to sum the transaction association weights corresponding to each identical query statement within multiple preset time periods, so as to obtain the transaction association weight of each query statement within multiple preset time periods.

[0146] According to embodiments of this disclosure, the second determining module may include: a first initial determining unit, a data table weight determining unit, a second initial determining unit, and an associated field weight determining unit.

[0147] The first initial determination unit is used to determine the initial data table weight of each table relationship information based on the weight of each transaction association and the correspondence between table relationship information and query statements.

[0148] The data table weight determination unit is used to sum the initial data table weights that record the same data table name to obtain the data table weights of the table relationship information.

[0149] The second initial determination unit is used to determine the initial associated field weight of each table field information based on the associated weight of each transaction and the correspondence between table field information and query statements.

[0150] The associated field weight determination unit is used to sum the initial associated field weights of the table field information by summing the names of the tables with the same data and the same associated fields.

[0151] According to embodiments of this disclosure, the sharded storage module may include a clustering processing unit and a sharded storage unit.

[0152] The clustering processing unit is used to process the weights of the data table and the weights of the associated fields using a clustering algorithm to obtain the first clustering result and the second clustering result.

[0153] The sharded storage unit is used to shard and store multiple data tables according to the first clustering result and the second clustering result, wherein the sharded storage includes vertical sharded storage and horizontal sharded storage.

[0154] According to embodiments of this disclosure, the data table processing apparatus may further include: a first display module and a second display module.

[0155] The first display module is used to display the first degree of association information between multiple data tables with related relationships on the page, based on the data table weights of the table relationship information.

[0156] The second display module is used to display the second degree of association between data tables and related fields on the page based on the weight of the associated fields in the table field information.

[0157] According to embodiments of this disclosure, any plurality of modules among the parsing module 710, the first determining module 720, the transaction association weight determining module 730, the second determining module 740, and the fragmented storage module 750 can be merged into one module, or any one of these modules can be split into multiple modules. Alternatively, at least part of the functionality of one or more of these modules can be combined with at least part of the functionality of other modules and implemented in one module. According to embodiments of this disclosure, at least one of the parsing module 710, the first determining module 720, the transaction association weight determining module 730, the second determining module 740, and the fragmented storage module 750 can be at least partially implemented as hardware circuitry, such as a field-programmable gate array (FPGA), a programmable logic array (PLA), a system-on-a-chip, a system-on-a-substrate, a system-on-package, an application-specific integrated circuit (ASIC), or implemented in hardware or firmware by any other reasonable means of integrating or packaging circuitry, or implemented in software, hardware, or firmware, or in any suitable combination of any of these three implementation methods. Alternatively, at least one of the parsing module 710, the first determining module 720, the transaction association weight determining module 730, the second determining module 740, and the fragmented storage module 750 can be at least partially implemented as a computer program module, which can perform corresponding functions when the computer program module is run.

[0158] Figure 8 A block diagram schematically illustrates an electronic device suitable for implementing a data table processing method according to an embodiment of the present disclosure.

[0159] like Figure 8 As shown, an electronic device 800 according to an embodiment of this disclosure includes a processor 801, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 802 or a program loaded from a storage portion 808 into a random access memory (RAM) 803. The processor 801 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or an associated chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 801 may also include onboard memory for caching purposes. The processor 801 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of this disclosure.

[0160] RAM 803 stores various programs and data required for the operation of electronic device 800. Processor 801, ROM 802, and RAM 803 are interconnected via bus 804. Processor 801 performs various operations of the method flow according to embodiments of the present disclosure by executing programs in ROM 802 and / or RAM 803. It should be noted that the programs may also be stored in one or more memories other than ROM 802 and RAM 803. Processor 801 may also perform various operations of the method flow according to embodiments of the present disclosure by executing programs stored in said one or more memories.

[0161] According to embodiments of this disclosure, the electronic device 800 may further include an input / output (I / O) interface 805, which is also connected to a bus 804. The electronic device 800 may also include one or more of the following components connected to the I / O interface 805: an input section 806 including a keyboard, mouse, etc.; an output section 807 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 808 including a hard disk, etc.; and a communication section 809 including a network interface card such as a LAN card, modem, etc. The communication section 809 performs communication processing via a network such as the Internet. A drive 810 is also connected to the I / O interface 805 as needed. A removable medium 811, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 810 as needed so that computer programs read from it can be installed into the storage section 808 as needed.

[0162] This disclosure also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or it may exist independently and not assembled into the device / apparatus / system. The computer-readable storage medium carries one or more programs that, when executed, implement the method according to the embodiments of this disclosure.

[0163] According to embodiments of this disclosure, the computer-readable storage medium may be a non-volatile computer-readable storage medium, such as including, but not limited to: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this disclosure, the computer-readable storage medium may be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. For example, according to embodiments of this disclosure, the computer-readable storage medium may include ROM 802 and / or RAM 803 and / or one or more memories other than ROM 802 and RAM 803 described above.

[0164] Embodiments of this disclosure also include a computer program product comprising a computer program containing program code for performing the methods shown in the flowchart. When the computer program product is run on a computer system, the program code enables the computer system to implement the data table processing method provided in the embodiments of this disclosure.

[0165] When the computer program is executed by the processor 801, it performs the functions defined in the system / apparatus of this disclosure embodiments. According to embodiments of this disclosure, the systems, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0166] In one embodiment, the computer program may rely on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may also be transmitted and distributed in the form of signals over a network medium, and may be downloaded and installed via the communication section 809, and / or installed from a removable medium 811. The program code contained in the computer program can be transmitted using any suitable network medium, including but not limited to: wireless, wired, etc., or any suitable combination thereof.

[0167] In such an embodiment, the computer program can be downloaded and installed from a network via communication section 809, and / or installed from removable medium 811. When the computer program is executed by processor 801, it performs the functions defined in the system of this disclosure embodiment. According to embodiments of this disclosure, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0168] According to embodiments of this disclosure, program code for executing the computer programs provided in embodiments of this disclosure can be written in any combination of one or more programming languages. Specifically, these computational programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, languages ​​such as Java, C++, Python, "C", or similar programming languages. The program code can execute entirely on the user's computing device, partially on the user's device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0169] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this disclosure. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0170] Those skilled in the art will understand that the features described in the various embodiments and / or claims of this disclosure can be combined or combined in various ways, even if such combinations or combinations are not explicitly described in this disclosure. In particular, the features described in the various embodiments and / or claims of this disclosure can be combined or combined in various ways without departing from the spirit and teachings of this disclosure. All such combinations and / or combinations fall within the scope of this disclosure.

[0171] The embodiments of this disclosure have been described above. However, these embodiments are for illustrative purposes only and are not intended to limit the scope of this disclosure. Although various embodiments have been described above, this does not mean that the measures in the various embodiments cannot be used advantageously in combination. The scope of this disclosure is defined by the appended claims and their equivalents. Various substitutions and modifications can be made by those skilled in the art without departing from the scope of this disclosure, and all such substitutions and modifications should fall within the scope of this disclosure.

Claims

1. A data table processing method, comprising: Parse the query statement to obtain the parsing result, wherein the parsing result includes the relationship between multiple data tables in the query statement and the association field used to associate the data tables in the multiple data tables; Based on the parsing results, the table relationship information and table field information corresponding to the query statement are determined. The table relationship information records the names of multiple data tables that are related, and the table field information records the names of the data tables and the related fields of the data tables. The number of online transactions corresponding to each query statement within a preset time period is normalized to obtain the transaction association weight of each query statement within the preset time period. The transaction association weight represents the importance of each query statement to online transactions of transfer transactions and repayment transactions within the preset time period. Based on the transaction association weight and the correspondence between the table relationship information and the query statement, determine the initial data table weight of each table relationship information; The weights of the initial data tables that record the same data table names are summed to obtain the data table weights of the table relationship information. Based on the associated weight of each transaction and the correspondence between the table field information and the query statement, the initial associated field weight of each table field information is determined; The weights of the initial associated fields that record the names of the same data tables and the same associated fields are summed to obtain the associated field weights of the table field information. The weights of the data tables are processed using a clustering algorithm to obtain a first clustering result, and the multiple data tables are vertically partitioned and stored based on the first clustering result. The weights of the associated fields are processed using a clustering algorithm to obtain a second clustering result, and the multiple data tables and the associated fields are horizontally sharded and stored according to the second clustering result; The method further includes: According to preset response rules, the preset response time of the reconciliation transaction as a batch transaction is determined, wherein the batch transaction corresponds to at least one of the query statements; The batch transaction weight of each batch transaction is determined based on the preset response time of different batch transactions; Based on the correspondence between the batch transactions and the query statement, the batch transaction weight of the batch transactions is determined as the response association weight of the query statement; Based on the correspondence between each response association weight, the table relationship information, the table field information, and the query statement, the table relationship information and the table field information are updated to obtain the updated data table weight and the updated association field weight. The updated data table weight and the updated association field weight represent the preset response time corresponding to the batch transaction.

2. The method according to claim 1, wherein, The preset time period includes multiple periods; Determining the transaction association weight of each query statement based on the number of online transactions corresponding to different query statements within a preset time period also includes: The transaction association weights corresponding to each identical query statement within multiple preset time periods are summed to obtain the transaction association weights for each query statement within the multiple preset time periods.

3. The method according to claim 1, further comprising: Based on the data table weights of the table relationship information, the first degree of association information between multiple data tables with related relationships is displayed on the page; as well as Based on the weights of the associated fields in the table field information, the second degree of association information between the data table and the associated fields of the data table is displayed on the page.

4. A data table processing apparatus, comprising: The parsing module is used to parse the query statement and obtain the parsing result, wherein the parsing result includes the relationship between multiple data tables in the query statement and the association field used to associate the data tables in the multiple data tables; The first determining module is used to determine the table relationship information and table field information corresponding to the query statement based on the parsing result, wherein the table relationship information records the names of multiple data tables with related relationships, and the table field information records the names of the data tables and the related fields of the data tables; The transaction association weight determination module is used to determine the transaction association weight of each query statement based on the number of online transactions corresponding to different query statements within a preset time period. The second determining module is used to determine the data table weight of the table relationship information and the associated field weight of the table field information based on the correspondence between each transaction association weight, the table relationship information, and the table field information and the query statement; and The sharded storage module is used to shard and store multiple data tables according to the data table weight and the weight of the associated field; The sharded storage module is configured as follows: The weights of the data table and the weights of the associated fields are processed using a clustering algorithm to obtain a first clustering result and a second clustering result; Based on the first clustering result, the multiple data tables are vertically partitioned and stored. Based on the second clustering result, the multiple data tables and the associated fields are horizontally partitioned and stored; The fragmented storage module is configured as follows: Based on the transaction association weight and the correspondence between the table relationship information and the query statement, determine the initial data table weight of each table relationship information; The weights of the initial data tables that record the same data table names are summed to obtain the data table weights of the table relationship information. Based on the associated weight of each transaction and the correspondence between the table field information and the query statement, the initial associated field weight of each table field information is determined; The weights of the initial associated fields that record the names of the same data tables and the same associated fields are summed to obtain the associated field weights of the table field information. The transaction association weight determination module is further configured to: normalize the number of online transactions corresponding to each query statement within a preset time period to obtain the transaction association weight of each query statement within the preset time period; The device further includes: The response association weight determination module is used to determine the response association weight of each query statement according to a preset response rule, wherein the preset response rule is determined based on the preset response duration of the batch transaction; The data table weight update module is used to update the table relationship information and table field information according to the correspondence between each response association weight, the table relationship information and the table field information and the query statement, so as to obtain the updated data table weight and the updated association field weight; The response association weight determination module includes: The response duration determination unit is used to determine the preset response duration of the batch transaction according to the preset response rules, wherein the batch transaction corresponds to at least one of the query statements; A batch transaction weight determination unit is used to determine the batch transaction weight of each batch transaction based on the preset response time of different batch transactions; The response association weight determination unit is used to determine the batch transaction weight of the batch transaction as the response association weight of the query statement based on the correspondence between the batch transaction and the query statement.

5. An electronic device, comprising: One or more processors; Storage device for storing one or more programs. Wherein, when the one or more programs are executed by the one or more processors, the one or more processors perform the method according to any one of claims 1 to 3.

6. A computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, cause the processor to perform the method according to any one of claims 1 to 3.

7. A computer program product comprising a computer program that, when executed by a processor, implements the method according to any one of claims 1 to 3.

Images