Cross-source synchronization table index creation method, device, equipment, medium and program product

Abstract

The application provides a cross-source synchronization table index creation method and device, equipment, storage medium and program product, which can be applied to the technical field of big data. The method comprises the following steps: receiving a query statement for obtaining data from a data warehouse; extracting a query field set and a condition field set from the query statement; obtaining data from the data warehouse according to the query statement, and synchronizing the obtained data to a target table in an online database, wherein the target table comprises data columns corresponding to the query field set; merging the condition field set into a candidate index field set of the target table; calculating a dispersion index of each field in the candidate index field set based on the condition value distribution corresponding to each field in the candidate index field set; selecting a target field set from the candidate index field set based on the dispersion index; and creating an index for each field in the target field set on the target table.

Description

Technical Field

[0001] This application relates to the field of big data technology, specifically to the field of cross-source data synchronization, and more specifically to a method, apparatus, device, medium, and program product for creating a cross-source synchronized table index. Background Technology

[0002] In big data applications, different data processing engines are typically used to meet different application scenarios, such as offline analytical processing and online querying. Data warehouse engines are mainly used in online analytical processing scenarios, suitable for both big data processing and offline analysis. They are characterized by relatively low concurrency, large data volumes, and long analysis times; however, the query speed of these engines cannot meet the efficiency requirements of online queries. Although some engines with higher query efficiency have emerged in big data scenarios, significantly improving query performance, they are still not suitable for high-concurrency scenarios. To meet the high-concurrency, fast query needs of business users, online databases are commonly used in practice to provide data query services. However, while online databases offer high query efficiency, their data storage capacity is limited, often requiring synchronization of data from data warehouses and other data sources within distributed big data processing systems. In such cross-source data synchronization scenarios, online databases lacking suitable indexes cannot efficiently handle query requests for massive amounts of data. Therefore, how to reasonably create indexes to improve query efficiency becomes a pressing technical problem. Summary of the Invention

[0003] In view of the above problems, embodiments of this application provide a method, apparatus, device, medium, and program product for creating a cross-source synchronized table index.

[0004] According to a first aspect of this application, a method for creating a cross-source synchronized table index is provided, comprising: receiving a query statement for retrieving data from a data warehouse; extracting a query field set and a condition field set from the query statement; retrieving data from the data warehouse according to the query statement and synchronizing the retrieved data to a target table in an online database, the target table including data columns corresponding to the query field set; merging the condition field set into a candidate index field set of the target table; calculating a dispersion index for each field based on the distribution of condition values ​​corresponding to each field in the candidate index field set of the target table; selecting a target field set from the candidate index field set based on the dispersion index; and creating an index on the target table for each field in the target field set.

[0005] According to an embodiment of this application, determining the target field set to be indexed based on the dispersion index includes: including fields whose dispersion index exceeds a preset threshold in the target field set.

[0006] According to an embodiment of this application, before creating an index on the target table for each field in the target field set, the cross-source synchronized table index creation method further includes: incorporating a preset index field into the target field set.

[0007] According to an embodiment of this application, before creating an index for each field in the target field set on the target table, the cross-source synchronized table index creation method further includes: when the number of fields in the target field set exceeds a preset upper limit, the preset index fields of the target field set are retained first, and then the other fields in the target field set are sorted from high to low according to the dispersion index and then truncated so that the total number of the target field set does not exceed the upper limit.

[0008] According to an embodiment of this application, before synchronizing the acquired data to the target table in the online database, the cross-source synchronized table index creation method further includes: extracting the source table identifier from the query statement; generating a target table identifier based on the source table identifier and the query field set; determining whether a historical target table corresponding to the target table identifier exists in the online database; if no historical target table exists, creating a target table in the online database; if a historical target table exists, using the historical target table as the target table.

[0009] According to an embodiment of this application, the method for generating a target table identifier includes: performing a hash operation on the source table identifier and the set of query fields to obtain a unique hash value as the target table identifier.

[0010] According to an embodiment of this application, before incorporating the set of condition fields into the set of candidate index fields of the target table, the cross-source synchronized table index creation method further includes obtaining the set of candidate index fields, specifically including: when the historical target table is used as the target table, obtaining the set of historical target fields in the historical target table that have been indexed; and using the set of historical target fields as the set of candidate index fields.

[0011] According to an embodiment of this application, the data warehouse belongs to a distributed big data processing system; the online database is a relational database.

[0012] According to another aspect of this application, a cross-source synchronized table index creation apparatus is provided, comprising: a query statement receiving module for receiving a query statement for retrieving data from a data warehouse; a field extraction module for extracting a query field set and a condition field set from the query statement; a data synchronization module for retrieving data from the data warehouse according to the query statement and synchronizing the retrieved data to a target table in an online database, the target table including data columns corresponding to the query field set; a candidate index field set module for merging the condition field set into the candidate index field set of the target table; a dispersion index calculation module for calculating the dispersion index of each field based on the distribution of condition values ​​corresponding to each field in the candidate index field set of the target table; a target field set module for selecting a target field set from the candidate index field set based on the dispersion index; and an index creation module for creating an index on the target table for each field in the target field set.

[0013] According to another aspect of this application, an electronic device is provided, comprising: one or more processors; and a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the method described above.

[0014] According to another aspect of this application, a computer-readable storage medium is also provided, on which a computer program or instructions are stored, which, when executed by a processor, implement the steps of the above-described method.

[0015] According to another aspect of this application, a computer program product is also provided, including a computer program or instructions that, when executed by a processor, implement the steps of the above-described method.

[0016] The cross-source synchronized table index creation method of this application addresses query needs after data is synchronized from an external data source to an online database. By extracting condition fields from the query statement and calculating a dispersion index based on the actual distribution of condition values ​​in the target table for filtering, it achieves automatic index creation, avoiding the inefficiency of manual configuration. Simultaneously, the dispersion-based filtering mechanism allows the index to better support actual query needs, thereby improving the query efficiency of the online database and avoiding the impact of excessive indexes on write performance and storage space. Attached Figure Description

[0017] The above-mentioned contents, other objects, features and advantages of this application will become clearer from the following description of embodiments with reference to the accompanying drawings, in which:

[0018] Figure 1 This illustration schematically depicts an application scenario of the cross-source synchronized table index creation method according to an embodiment of this application.

[0019] Figure 2 A flowchart illustrating a method for creating a cross-source synchronized table index according to an embodiment of this application is shown schematically.

[0020] Figure 3 This illustration schematically shows a flowchart of the historical target table determination method for a cross-source synchronized table index creation method according to an embodiment of this application;

[0021] Figure 4 This illustration schematically shows a flowchart of obtaining the candidate index field set in the cross-source synchronized table index creation method according to an embodiment of this application;

[0022] Figure 5 This schematically illustrates a structural block diagram of a cross-source synchronized table index creation apparatus according to an embodiment of the present application;

[0023] Figure 6 A block diagram schematically illustrates an electronic device suitable for implementing a cross-source synchronized table index creation method according to an embodiment of this application. Detailed Implementation

[0024] The embodiments of this application 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 this application. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of this application for ease of explanation. However, it will be apparent that one or more embodiments may be implemented without these specific details. Furthermore, descriptions of well-known structures and technologies are omitted in the following description to avoid unnecessarily obscuring the concepts of this application.

[0025] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit the scope of this application. 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.

[0026] 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.

[0027] In the technical solution of this application, the user information (including but not limited to user personal information, user image information, user device information, such as location information) and data (including but not limited to data used for analysis, stored data, and displayed data) involved are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of related data all comply with relevant laws, regulations, and standards, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entry points for users to choose to authorize or refuse.

[0028] In scenarios where personal information is used for automated decision-making, the methods, devices, and systems provided in this application all provide users with corresponding operation entry points for users to choose to agree to or reject the automated decision results; if the user chooses to reject, the process enters the expert decision-making process.

[0029] As used herein, the term "database" can refer to a system that organizes, stores, and manages data according to a certain data structure. Databases can include various types, among which a "data warehouse" refers to a subject-oriented, integrated, and relatively stable collection of data, characterized by large data volume, relatively low concurrency, and long query and analysis times, suitable for online analytical processing (OLAP) scenarios. It should be noted that an "online database" refers to a database system that supports high-concurrency online transaction processing, capable of quickly responding to transaction processing requests and providing real-time data access. Relational databases are a typical example of online databases, organizing data through table structures and supporting structured query languages ​​for data manipulation. It should be understood that a "distributed big data processing system" is a system architecture that uses multiple computers to collaboratively perform distributed storage and computation / query processing of massive amounts of data. It features strong scalability and high fault tolerance, capable of handling massive amounts of data that traditional single-machine systems cannot handle. In the embodiments described herein, "cross-source data synchronization" refers to the process of synchronizing data from one data source to another, such as synchronizing data from a data warehouse in a distributed big data processing system to an online database to provide business personnel with fast query services.

[0030] Figure 1 The illustration shows an application scenario of the cross-source synchronized table index creation method according to an embodiment of this application.

[0031] like Figure 1As shown, application scenario 100 according to an embodiment of this application may include a first terminal device 101, a second terminal device 102, a data warehouse 103, an online database 104, a network 105, and a server 106. The network 105 serves as a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the data warehouse 103, the online database 104, and the server 106. The network 105 may include various connection types, such as wired or wireless communication links or fiber optic cables. For example, a user can use the first terminal device 101 and the second terminal device 102 to interact with the server 106 through the network 105 to send query requests or receive query results, etc.

[0032] The first terminal device 101 and the second terminal device 102 can be electronic devices such as smartphones, wearable devices, personal computers, intelligent voice interaction devices, smart home appliances, intelligent vehicles, in-vehicle terminals, aircraft, unmanned vending terminals, and extended reality devices. Extended reality devices can include virtual reality devices, augmented reality devices, and mixed reality devices. A client application for the target application can be installed and run on the terminal device. This target application can include, but is not limited to, financial transaction applications, payment applications, shopping applications, web browser applications, search applications, instant messaging tools, email clients, and social media platform software (these are just examples). Furthermore, this application embodiment does not limit the form of the target application, including but not limited to applications, mini-programs, etc., installed on the terminal device, and can also be in web page form.

[0033] Server 106 can be a server providing various services, such as a backend management server supporting data query requests initiated by users using the first terminal device 101 and the second terminal device 102 (this is just an example). The backend management server can parse and process the received query statements and other data, retrieve data from the data warehouse 103 and synchronize it to the online database 104, create an index in the online database 104 according to the method of this application embodiment, and finally feed back the query results to the terminal device. The server can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks, and basic cloud computing services such as big data. The server can be the backend server of the above-mentioned target application, used to provide backend services to the clients of the target application.

[0034] Data warehouse 103 is a data storage system built on a distributed big data processing system, used to store and manage massive amounts of business data. Data warehouse 103 supports offline analysis and batch processing of large-scale data, possessing powerful data storage and complex data analysis capabilities. In this application scenario, data warehouse 103 can be connected to server 106 via a communication link. Server 106 can retrieve the required data from data warehouse 103 based on query requests from first terminal device 101 and second terminal device 102.

[0035] Online database 104 is a database system used to provide high-concurrency, fast query services. It can be a relational database or other database that supports online transaction processing. Online database 104 has the ability to quickly respond to query requests and is suitable for the real-time data access needs of business systems. In this application scenario, online database 104 can be connected to server 106 via a communication link. Server 106 synchronizes data obtained from data warehouse 103 to online database 104 and creates indexes in online database 104 according to the method of this application embodiment, thereby achieving efficient support for the query needs of business personnel.

[0036] It should be noted that the cross-source synchronized table index creation method provided in this application embodiment can generally be executed by server 106. Accordingly, the cross-source synchronized table index creation device provided in this application embodiment can generally be set in server 106.

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

[0038] Figure 2 A flowchart illustrating a method for creating a cross-source synchronized table index according to an embodiment of this application is shown. Figure 2 As shown, the cross-source synchronized table index creation method 200 according to the embodiments of this application may include steps S210 to S270.

[0039] In step S210, a query statement for retrieving data from the data warehouse is received.

[0040] In this embodiment, a query request for retrieving data from a data warehouse can be received. This query request can be a query statement. The query statement is a query language statement adapted to the data warehouse, used to specify the data content to be retrieved from the data warehouse and the query conditions. In this embodiment, the distributed big data processing system refers to a platform system that includes distributed storage, resource scheduling, and distributed computing / query capabilities, used for storing and processing massive amounts of data. It may include a data warehouse, which is used to organize and manage data and provide query capabilities. Specifically, users can configure data query jobs according to their needs. These jobs include query statements, source distributed big data processing systems, source data warehouse information, and target online database information. Query statements typically include query field information and condition field information. The query fields specify the data columns to be retrieved and displayed, while the condition fields specify the conditions for filtering the data. For example, in a banking scenario, business personnel may need to query customer information under specific conditions. In this case, a query statement can be used to specify the customer's name, account status, etc., and set city, account opening date, and age as filtering conditions. After receiving the query statement, it will be parsed to extract relevant information, thereby preparing for subsequent data retrieval and index creation.

[0041] In step S220, the query field set and the condition field set are extracted from the query statement.

[0042] In this embodiment, the received query statement is parsed to extract the query field set and the condition field set. Specifically, the syntax structure of the query statement can be analyzed to identify its various components. The query field set refers to the data fields specified in the query statement that need to be returned; these fields correspond to the data columns that need to be displayed to the user in the query results. The condition field set refers to the condition fields used to filter data in the query statement; these fields typically appear in the WHERE clause of the query statement and are used to limit the range of data to be retrieved.

[0043] In this embodiment, the received query statement is parsed to extract the query field set and the condition field set. Specifically, the syntax structure of the query statement can be analyzed to identify its components, including the SELECT clause, FROM clause, and WHERE clause. The query field set refers to the data fields specified in the SELECT clause that need to be returned; these fields correspond to the data columns that need to be displayed to the user in the query results. The condition field set refers to the condition fields used in the WHERE clause to filter data and limit the range of data to be retrieved. Further identification of the FROM clause reveals the source table name of the data. Based on the parsed and identified data, the query field set, condition field set, and other relevant data can be extracted.

[0044] For example, for a query statement:

[0045] SELECT Customer Name, Account Status, Account Opening Date

[0046] FROM User Information Form

[0047] WHERE City IN ('Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen') AND Age > 30 AND Account Opening Date > '2025-01-01'

[0048] By parsing, the query field set can be extracted as {customer name, account status, account opening date}, and the condition field set is {city, age, account opening date}.

[0049] In one embodiment, the parsed information can be recorded in corresponding data structures for subsequent processing. These data structures may include job tables, query field tables, and condition field tables, used to record job numbers, information about the distributed big data processing system and its source data warehouse, target online database information, query fields, condition fields, and other related information.

[0050] Table 1 shows an example of a job table, which records the job number and the corresponding source data warehouse type and target online database type. For example, job number JOB001 represents a job that synchronizes data from data warehouse A1 to online database B1, and job number JOB002 represents a job that synchronizes data from data warehouse A2 to online database B2.

[0051] Table 1. Examples of work assignments

[0052]

[0053] Table 2 shows an example of a query field table, which records the job number, source table name, and the query fields corresponding to the job. For example, the source table corresponding to job JOB001 is the customer information table, and the fields to be queried include customer name and deposit amount.

[0054] Table 2 Example of query field representation

[0055]

[0056] Table 3 shows an example of a conditional field table, which records the job number and its corresponding conditional fields. For example, the conditional fields for job JOB001 include city and age, which will be used to filter data records that meet the conditions.

[0057] Table 3. Examples of Condition Field Representations

[0058]

[0059] In this way, information from multiple query jobs can be managed and traced in a unified manner, which facilitates subsequent data synchronization and index creation operations.

[0060] In step S230, data is retrieved from the data warehouse according to the query statement, and the retrieved data is synchronized to the target table in the online database. The target table includes the data columns corresponding to the query field set.

[0061] In embodiments of this application, when a query involves a user's personal information, the user's consent or authorization can be obtained before retrieving the user information from the data warehouse. For example, a request to retrieve user information can be sent to the user before executing step S230. If the user consents or authorizes the retrieval of user information, step S230 is executed to retrieve the relevant data from the data warehouse. For example, in a banking scenario, when it is necessary to query a customer's name, deposit amount, account opening date, or other personal information, the customer's explicit consent or authorization should be obtained beforehand to ensure that data acquisition and use comply with relevant privacy protection laws and regulations.

[0062] In this embodiment, the required data is retrieved from the data warehouse based on a query statement. After submitting the query statement to the data warehouse, the data warehouse executes the query operation and returns data records that meet the query conditions. Upon receiving the query results, this data is synchronized to the target table in the online database. The structure of the target table is determined by the set of query fields; that is, the target table includes data columns corresponding to the set of query fields, and may also contain related data corresponding to the condition fields to support subsequent data filtering and index creation based on these condition fields. The data synchronization process can involve inserting the query results into a newly created target table, or appending or updating the new query results to an existing historical target table. Through this cross-source data synchronization method, data from the distributed big data processing system is transferred to the online database, laying the foundation for providing business personnel with fast data query services.

[0063] In the embodiments of this application, the data warehouse is a distributed big data processing system with strong data storage and processing capabilities, and is suitable for offline analysis scenarios of massive data; the online database is a relational database with the ability to quickly respond to query requests, and is suitable for high-concurrency online query scenarios.

[0064] In step S240, the set of condition fields is merged into the set of candidate index fields of the target table.

[0065] In this embodiment, the set of condition fields extracted from the query statement is incorporated into the candidate index field set of the target table. Condition fields are those used by users to filter data during queries; indexing these fields can significantly improve data retrieval efficiency. The candidate index field set is used to summarize all fields that may require indexing, facilitating subsequent dispersion analysis and filtering.

[0066] Figure 3 The flowchart illustrating the historical target table determination method of the cross-source synchronized table index creation method according to an embodiment of this application is shown.

[0067] In order to improve resource utilization efficiency and avoid repeatedly creating the same target table, in some embodiments of this application, the cross-source synchronized table index creation method may further include steps S310 to S330 before synchronizing the acquired data to the target table in the online database.

[0068] In step S310, the source table identifier in the query statement is extracted.

[0069] In step S320, a target table identifier is generated based on the source table identifier and the set of query fields.

[0070] In this embodiment, a source table identifier is extracted from the query statement. This source table identifier indicates the name of the source table from which the data originates. The source table identifier typically appears in the FROM clause of the query statement; for example, in the example query statement above, the source table identifier is "User Information Table". Based on the extracted source table identifier and the set of query fields, a unique target table identifier is generated. Since the data content of the target table is mainly determined by the source table and the query fields, the source table identifier and the set of query fields can be used as the basis for generating the target table identifier.

[0071] Specifically, the target table identifier can be generated by performing a hash operation on the source table identifier and the set of query fields to obtain a unique hash value as the target table identifier. In this way, a mapping relationship can be established between the source table, the query fields, and the target table identifier.

[0072] Table 4 shows an example of the relationship between target table identifiers and fields. As shown in Table 4, the same target table identifier (e.g., HASH_A) can be associated with multiple condition fields and their different condition values ​​in different batches of query jobs. For example, the condition field "City" corresponds to the condition values ​​"Beijing" and "Shanghai," and the condition field "Age" corresponds to the condition values ​​"31" and "35," etc. This indicates that in different batches of query jobs, when the target table identifier is the same but the query conditions are different, relevant information can be recorded under the same target table identifier. The target table identifier is used to identify the target table; therefore, when the target table identifiers are the same, the corresponding target table can be reused, thereby reducing the duplication of target table creation and facilitating unified management.

[0073] Table 4. Relationship between target table identifiers and fields

[0074]

[0075] In step S330, it is determined whether a historical target table corresponding to the target table identifier exists in the online database; if no historical target table exists, a target table is created in the online database; if a historical target table exists, the historical target table is used as the target table.

[0076] In this embodiment, based on the generated target table identifier, a query is performed in the online database to check if a historical target table corresponding to that identifier exists. If the target table corresponding to that identifier does not exist in the online database, it indicates a new query requirement, and a new target table needs to be created in the online database to store the synchronized data. If a historical target table corresponding to that identifier already exists in the online database, it means that a target table has already been created for the same source table and query field combination, and this historical target table can be directly reused as the current target table. In this way, when subsequent query requests with the same source table and query fields but different query conditions are received, the newly queried data can be appended or updated to the existing target table without repeatedly creating a new table.

[0077] For example, the first query:

[0078] SELECT Customer Name, Deposit Amount

[0079] FROM Customer Information Form

[0080] WHERE city = 'Beijing' AND age > 30

[0081] At this point, a target table with the target identifier HASH_A was created. Then, a query was performed:

[0082] SELECT Customer Name, Deposit Amount

[0083] FROM Customer Information Form

[0084] WHERE City IN ('Shanghai', 'Shenzhen') AND Account Opening Date > '2025-01-01'

[0085] Since the source table and query fields are the same, the generated hash value is still HASH_A. The system will find that a corresponding historical target table already exists, so it will reuse that table and synchronize the new query results to it to update the target table. At this time, the condition value of the "City" field changes from "Beijing" to include "Beijing", "Shanghai", and "Shenzhen", and a new condition field "Account Opening Date" and its corresponding condition value are added. This design avoids creating duplicate tables for the same query requirements, improving resource utilization efficiency.

[0086] Figure 4 The flowchart illustrating the process of obtaining the candidate index field set in the cross-source synchronized table index creation method according to an embodiment of this application is shown.

[0087] Furthermore, in the embodiments of this application, when the target table is reused from a historical target table, some indexes may have already been created in the historical target table, and these existing index fields should be included in the candidate scope for re-evaluation. Therefore, before incorporating the set of condition fields into the candidate index field set of the target table, the cross-source synchronized table index creation method may also include the step of obtaining the candidate index field set, specifically including steps S410 to S420.

[0088] In step S410, if the historical target table is used as the target table, the set of historical target fields with indexes created in the historical target table is obtained.

[0089] In step S420, the historical target field set is used as the candidate index field set.

[0090] In this embodiment, when step S330 determines that a historical target table exists and uses it as the current target table, the index metadata information of the historical target table can be queried to obtain the fields for which indexes have been created, forming a historical target field set. These historical index fields were created based on the data characteristics and query patterns at the time of previous queries. They may still be applicable to the current query requirements, or they may need to be adjusted due to changes in data distribution. The obtained historical target field set is used as the initial candidate index field set. In this way, existing index fields are included in the candidate range. In the subsequent dispersion calculation and filtering process, these historical index fields will be evaluated together with new condition fields to determine whether to retain, adjust, or add indexes. This design can dynamically optimize the index configuration based on new query requirements while reusing the historical target table.

[0091] It should be noted that steps S310-S330 and S410-S420 are executed under specific circumstances. When a historical target table needs to be reused, steps S310-S330 are executed first to determine and reuse the historical target table, and then steps S410-S420 are executed to obtain the historical index fields. When the target table is a newly created table, there is no historical target table or historical index fields. In this case, the candidate index field set is initially empty, and step S240 is executed directly to incorporate the condition field set. In either case, after determining the initial candidate index field set, the subsequent dispersion calculation and index filtering steps need to be executed.

[0092] In step S250, the dispersion index of each field is calculated based on the distribution of condition values ​​corresponding to each field in the candidate index field set in the target table.

[0093] In this embodiment, based on the actual data in the target table, the distribution of condition values ​​for each field in the candidate index field set is analyzed, and the corresponding dispersion index is calculated. The dispersion index measures the degree of dispersion of a field's values. Higher dispersion indicates a greater number of different values ​​for that field and a more even distribution, resulting in a better indexing effect. Specifically, for each field in the candidate index field set, all different condition values ​​for that field in the target table and their frequencies are statistically analyzed to form a condition value distribution. For example, for the "account opening date" field, if the target table contains hundreds of different date values ​​with a relatively dispersed distribution, the dispersion of this field is high, making it suitable for indexing; while for the "gender" field, if there are only two values, male and female, the dispersion is low, and the indexing effect is not significant. In some embodiments, the information entropy algorithm can be used to calculate the dispersion index. The formula for calculating information entropy is:

[0094]

[0095] Where k represents the number of different condition values ​​for this field. This represents the proportion of the i-th condition value among all condition values ​​in the field, and the summation range is all distinct condition values ​​of the field. A higher information entropy indicates a more dispersed data distribution and a higher degree of dispersion. This quantitative calculation method allows for an objective assessment of whether each candidate field is suitable for indexing, providing data support for subsequent screening decisions. It should be noted that the calculation method for the dispersion index is not limited to information entropy; other statistical indicators that reflect the degree of data dispersion can also be used, such as the number of distinct values, the Gini coefficient, and variance. This application does not limit the specific calculation method used.

[0096] In step S260, the target field set is selected from the candidate index field set based on the dispersion index.

[0097] In this embodiment of the application, fields whose dispersion index exceeds a preset threshold are included in the target field set.

[0098] In this embodiment, based on the calculated dispersion index, suitable fields for indexing are selected from the candidate index field set to form a target field set. The preset threshold can be set according to actual application scenarios and experience. For example, the information entropy threshold can be set to n, meaning fields with a dispersion index greater than n are considered to have sufficient data dispersion and are suitable for indexing. By setting thresholds for filtering, fields where investing resources in indexing can automatically identify those that can significantly improve query performance, while filtering out fields with low dispersion and insignificant indexing effects. This dynamic filtering mechanism based on dispersion makes index creation more intelligent and precise, avoiding the problems of improper index configuration that may arise from relying on manual experience in traditional methods, while also improving efficiency.

[0099] In step S270, an index is created on the target table for each field in the target field set.

[0100] In this embodiment of the application, before creating an index on each field in the target field set on the target table, the cross-source synchronized table index creation method further includes: incorporating a preset index field into the target field set.

[0101] In this embodiment of the application, before creating an index for each field in the target field set on the target table, the cross-source synchronized table index creation method further includes: when the number of fields in the target field set exceeds a preset upper limit, the preset index fields of the target field set are retained first, and then the other fields in the target field set are sorted from high to low according to the dispersion index and then truncated so that the total number of the target field set does not exceed the upper limit.

[0102] Specifically, preset index fields refer to fields that must be indexed based on business experience or rules. For example, date fields are often key query conditions in data processing, so they can be set as preset index fields. Regardless of their dispersion, they will be included in the target field set to ensure that the indexing of key business fields is guaranteed. Furthermore, the total number of fields in the target field set can be controlled. When the number of fields in the target field set exceeds the preset limit, the preset index fields are retained first. Then, the other fields in the target field set are sorted from highest to lowest dispersion and truncated to ensure that the total number of fields in the target field set does not exceed the limit. For example, if the preset limit is 5 indexes, there are 2 preset index fields, and 6 fields are obtained through dispersion filtering, then, based on retaining the 2 preset index fields, the top 3 fields are selected from the 6 candidate fields according to their dispersion from highest to lowest, resulting in a final target field set containing 5 fields. While indexes can improve query efficiency, each index consumes storage space, and the index structure needs to be updated synchronously during data writing. Too many indexes can negatively impact write performance and storage resources; therefore, the number of indexes needs to be controlled. By setting a reasonable upper limit for the number of indexes and combining it with discrete sorting, it is possible to control the resource overhead of indexes while ensuring query performance, achieving a balance between performance and resources. After the indexes are created, the target table can provide efficient query services. Business users can quickly retrieve the required data by accessing the target table through online database access using these indexes.

[0103] Based on the above-described method for creating cross-source synchronized table indexes, embodiments of this application also provide a device for creating cross-source synchronized table indexes. The following will be combined with... Figure 5 The device is described in detail.

[0104] Figure 5 A schematic block diagram of a cross-source synchronized table index creation apparatus according to an embodiment of this application is shown.

[0105] like Figure 5 As shown, the cross-source synchronized table index creation device 500 of this embodiment includes a query statement receiving module 510, a field extraction module 520, a data synchronization module 530, a candidate index field set module 540, a dispersion index calculation module 550, a target field set module 560, and an index creation module 570.

[0106] The query statement receiving module 510 is used to receive query statements for retrieving data from the data warehouse. In one embodiment, the query statement receiving module 510 can be used to execute step S210 described above, which will not be repeated here.

[0107] The field extraction module 520 is used to extract the query field set and the condition field set from the query statement. In one embodiment, the field extraction module 520 can be used to perform step S220 described above, which will not be repeated here.

[0108] The data synchronization module 530 is used to retrieve data from the data warehouse according to the query statement and synchronize the retrieved data to the target table in the online database. The target table includes data columns corresponding to the query field set. In one embodiment, the data synchronization module 530 can be used to perform step S230 described above, which will not be repeated here.

[0109] The candidate index field set module 540 is used to incorporate the condition field set into the candidate index field set of the target table. In one embodiment, the candidate index field set module 540 can be used to perform step S240 described above, which will not be repeated here.

[0110] The dispersion index calculation module 550 is used to calculate the dispersion index of each field based on the distribution of condition values ​​corresponding to each field in the candidate index field set of the target table. In one embodiment, the dispersion index calculation module 550 can be used to execute step S250 described above, which will not be repeated here.

[0111] The target field set module 560 is used to filter the target field set from the candidate index field set based on the dispersion index. In one embodiment, the target field set module 560 can be used to perform step S260 described above, which will not be repeated here.

[0112] The index creation module 570 is used to create an index on the target table for each field in the target field set. In one embodiment, the index creation module 570 may be used to perform step S270 described above, which will not be repeated here.

[0113] In some embodiments, the cross-source synchronized table index creation apparatus further includes: a preset index field module, used to include preset index fields in the target field set before creating an index on each field in the target field set on the target table; a target field control module, used to, before creating an index on each field in the target field set on the target table, when the number of fields in the target field set exceeds a preset upper limit, prioritize retaining the preset index fields of the target field set, and then sorting the other fields in the target field set from high to low according to the dispersion index and truncating them so that the total number of fields in the target field set does not exceed the upper limit; and a historical target table module, used to, before synchronizing the acquired data to the target table in the online database, extract the source table identifier from the query statement; generate a target table identifier based on the source table identifier and the query field set; determine whether a historical target table corresponding to the target table identifier exists in the online database; if no historical target table exists, create a target table in the online database; if a historical target table exists, use the historical target table as the target table. The candidate index field set update module is used to obtain the candidate index field set before merging the condition field set into the candidate index field set of the target table. Specifically, it includes: obtaining the historical target field set with indexes created in the historical target table when the historical target table is used as the target table; and using the historical target field set as the candidate index field set.

[0114] According to embodiments of this application, any and multiple modules among the query statement receiving module 510, field extraction module 520, data synchronization module 530, candidate index field set module 540, dispersion index calculation module 550, target field set module 560, and index creation module 570 can be merged into one module, or any one of these modules can be split into multiple modules. Alternatively, at least some of the functions of one or more of these modules can be combined with at least some of the functions of other modules and implemented in one module. According to embodiments of this application, at least one of the query statement receiving module 510, field extraction module 520, data synchronization module 530, candidate index field set module 540, dispersion index calculation module 550, target field set module 560, and index creation module 570 can be at least partially implemented as hardware circuits, such as field-programmable gate arrays, programmable logic arrays, systems-on-a-chip, systems-on-a-substrate, systems-on-package, application-specific integrated circuits, or any other reasonable means of integrating or packaging circuits, or implemented in software, hardware, or firmware, or in any appropriate combination of any of these three implementation methods. Alternatively, at least one of the query statement receiving module 510, field extraction module 520, data synchronization module 530, candidate index field set module 540, dispersion index calculation module 550, target field set module 560, and index creation module 570 can be at least partially implemented as a computer program module, which can perform corresponding functions when the computer program module is run.

[0115] Figure 6 A block diagram schematically illustrates an electronic device suitable for implementing a cross-source synchronized table index creation method according to an embodiment of this application.

[0116] like Figure 6 As shown, an electronic device 600 according to an embodiment of this application includes a processor 601, which can perform various appropriate actions and processes according to a program stored in a read-only memory 602 or a program loaded from a storage portion 608 into a random access memory 603. The processor 601 may include, for example, a general-purpose microprocessor, an instruction set processor and / or an associated chipset and / or a dedicated microprocessor. The processor 601 may also include onboard memory for caching purposes. The processor 601 may include a single processing unit or multiple processing units for executing different steps of the method flow according to an embodiment of this application.

[0117] Random access memory 603 stores various programs and data required for the operation of electronic device 600. Processor 601, read-only memory 602, and random access memory 603 are interconnected via bus 604. Processor 601 executes various steps of the method flow according to embodiments of this application by executing programs in read-only memory 602 and / or random access memory 603. It should be noted that the programs may also be stored in one or more memories other than read-only memory 602 and random access memory 603. Processor 601 may also execute various steps of the method flow according to embodiments of this application by executing programs stored in said one or more memories.

[0118] According to embodiments of this application, the electronic device 600 may further include an input / output interface 605, which is also connected to a bus 604. The electronic device 600 may also include one or more of the following components connected to the input / output interface 605: an input section 606 including a keyboard, mouse, etc.; an output section 607 including a cathode ray tube, liquid crystal display, etc., and a speaker, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card, such as a local area network card, modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the input / output interface 605 as needed. A removable medium 611, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 610 as needed so that computer programs read from it can be installed into the storage section 608 as needed.

[0119] Embodiments of this application also provide 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, which, when executed, implement the method according to the embodiments of this application.

[0120] According to embodiments of this application, the computer-readable storage medium can be a non-volatile computer-readable storage medium, such as including but not limited to: portable computer disks, hard disks, random access memory, read-only memory, erasable programmable read-only memory, portable compact disk read-only memory, optical storage devices, magnetic storage devices, or any suitable combination thereof. In embodiments of this application, the computer-readable storage medium can be any tangible medium containing or storing 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 application, the computer-readable storage medium may include the read-only memory 602 described above, and / or random access memory 603, and / or one or more memories other than read-only memory 602 and random access memory 603.

[0121] Embodiments of this application 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 is used to cause the computer system to implement the methods provided in the embodiments of this application.

[0122] 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 downloaded and installed via the communication section 609, and / or installed from the removable medium 611. 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.

[0123] In embodiments of this application, the computer program can be downloaded and installed from a network via communication section 609, and / or installed from removable medium 611. When the computer program is executed by processor 601, it performs the functions defined in the system of embodiments of this application. According to embodiments of this application, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0124] According to embodiments of this application, program code for executing the computer programs provided in the embodiments of this application 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. The program code can be executed 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).

[0125] 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 application. 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.

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

Claims

1. A method for creating a cross-source synchronized table index, characterized in that, include: Receive query statements used to retrieve data from the data warehouse; Extract the query field set and the condition field set from the query statement; Data is retrieved from the data warehouse according to the query statement, and the retrieved data is synchronized to the target table in the online database, the target table including the data columns corresponding to the query field set; The set of condition fields is incorporated into the set of candidate index fields of the target table; Based on the distribution of condition values ​​corresponding to each field in the candidate index field set in the target table, calculate the dispersion index of each field; Based on the dispersion index, a target field set is selected from the candidate index field set; Create an index on the target table for each field in the target field set.

2. The method according to claim 1, characterized in that, The process of determining the target field set to be indexed based on the dispersion index includes: Fields whose dispersion index exceeds a preset threshold are included in the target field set.

3. The method according to claim 1 or 2, characterized in that, Before creating an index on the target table for each field in the target field set, the method further includes: Include the preset index fields into the target field set.

4. The method according to claim 3, characterized in that, Before creating an index on the target table for each field in the target field set, the method further includes: When the number of fields in the target field set exceeds the preset upper limit, the preset index field of the target field set is retained first, and then the other fields in the target field set are sorted from high to low according to the dispersion index and then truncated so that the total number of the target field set does not exceed the upper limit.

5. The method according to claim 1, characterized in that, Before synchronizing the acquired data to the target table in the online database, the method further includes: Extract the source table identifier from the query statement; Generate a target table identifier based on the source table identifier and the query field set; Determine whether a historical target table corresponding to the target table identifier exists in the online database; If the historical target table does not exist, the target table is created in the online database; if the historical target table exists, the historical target table is used as the target table.

6. The method according to claim 5, characterized in that, The methods for generating the target table identifier include: A hash operation is performed on the source table identifier and the set of query fields to obtain a unique hash value, which is used as the target table identifier.

7. The method according to claim 5, characterized in that, Before incorporating the set of condition fields into the set of candidate index fields of the target table, the method further includes obtaining the set of candidate index fields, specifically including: When the historical target table is used as the target table, obtain the set of historical target fields in the historical target table that have been indexed; and The set of historical target fields is used as the set of candidate index fields.

8. The method according to claim 1, characterized in that: The data warehouse is a distributed big data processing system; The online database is a relational database.

9. A cross-source synchronized table index creation device, characterized in that, The device includes: The query statement receiving module is used to receive query statements used to retrieve data from the data warehouse. The field extraction module is used to extract the query field set and the condition field set from the query statement; The data synchronization module is used to retrieve data from the data warehouse according to the query statement and synchronize the retrieved data to the target table in the online database, wherein the target table includes data columns corresponding to the query field set; The candidate index field set module is used to incorporate the condition field set into the candidate index field set of the target table; The dispersion index calculation module is used to calculate the dispersion index of each field based on the distribution of condition values ​​corresponding to each field of the candidate index field set in the target table. The target field set module is used to filter out a target field set from the candidate index field set based on the dispersion index; An index creation module is used to create an index on the target table for each field in the target field set.

10. An electronic device, comprising: One or more processors; Memory, used to store one or more computer programs. The characteristic feature is that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 8.

11. A computer-readable storage medium having a computer program or instructions stored thereon, characterized in that, When the computer program or instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 8.

12. A computer program product, comprising a computer program or instructions, characterized in that, When the computer program or instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 8.

Images