Data table use condition analysis method and device, computer equipment and storage medium

Abstract

The invention relates to a data table use condition analysis method and device, computer equipment and a storage medium. The method comprises the steps that metadata is extracted from a Gaussian database of a business production cluster, the metadata comprises a first data table, a user storage process and a database function, the user storage process and the database function are analyzed, a second data table associated with the user storage process and the database function is determined, the first data table and the second data table are compared, and the first data table and the second data table are compared. And obtaining a data table comparison result, and determining data used by the data table based on the data table comparison result. By adopting the method, the use condition of the data table in the Gaussian database can be analyzed more accurately.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, apparatus, computer device, and storage medium for analyzing data table usage. Background Art

[0002] With the development of information technology, the amount of data is growing exponentially, and this data is stored in various database systems. Among them, Gaussian Database is an enterprise-level distributed relational database, suitable for high data volume and high concurrency scenarios. However, how to analyze data in Gaussian Database has become one of the problems to be solved.

[0003] In traditional solutions, Gaussian Database itself does not provide analysis tools to manage data tables and other data. Instead, it can only analyze the user's data table usage through some statements defined by the user during the use of Gaussian Database or by introducing additional extended tools.

[0004] However, user-defined statements cannot accurately and comprehensively reflect the user's usage of the data table, and introducing additional extension tools may consume additional system resources. The analysis results are also easily affected by the extension tools. Therefore, traditional solutions are difficult to accurately analyze the user's use of the data table. Summary of the Invention

[0005] Therefore, it is necessary to provide a data table usage analysis method, apparatus, computer equipment, computer-readable storage medium, and computer program product that can improve the accuracy of data table usage analysis in response to the above-mentioned technical problems.

[0006] Firstly, this application provides a method for analyzing the usage of data tables. The method includes:

[0007] Metadata is extracted from the Gaussian database of the business production cluster, and the metadata includes a first data table, user stored procedures, and database functions.

[0008] Parse the user stored procedure and the database function to determine the second data table associated with the user stored procedure and the database function;

[0009] Compare the first data table and the second data table to obtain the data table comparison results;

[0010] Based on the comparison results of the data tables, the data used in the data tables is determined.

[0011] In one embodiment, parsing the user stored procedure and the database function to determine the second data table associated with the user stored procedure and the database function includes:

[0012] The preset Gaussian parsing engine is invoked to parse the user stored procedure and database function, and to determine the data source table, data target table, and temporary data table associated with the user stored procedure and database function;

[0013] The preset Gaussian parsing engine is determined based on the Gaussian SQL syntax infrastructure, and the second data table includes the data source table, the data target table, and the data temporary table.

[0014] In one embodiment, when the preset Gaussian parsing engine is invoked, the following steps are performed:

[0015] The user stored procedure is parsed into multiple basic syntax structures;

[0016] The database function and the parsed multiple basic syntax structures are matched with the Gaussian SQL syntax structure to obtain the matching results;

[0017] Based on the matching results, the data source table, data target table, and temporary data table associated with the user stored procedure and the database function are determined.

[0018] In one embodiment, determining the data source table, data target table, and temporary data table associated with the user stored procedure and the database function based on the matching result includes:

[0019] Based on the matching results, determine the SQL syntax tree of the user stored procedure and the database function;

[0020] Based on the SQL syntax tree, determine the data source table, data target table, and temporary data table associated with the user stored procedure and the database function.

[0021] In one embodiment, the user stored procedure includes a table creation statement, and the method further includes:

[0022] Based on the table creation statement, determine the update time of the second data table;

[0023] Based on the update time, determine the degree of impact of updating the second data table on the data used in the data table.

[0024] In one embodiment, the method further includes:

[0025] Receive SQL files uploaded by the client;

[0026] Parse the SQL file to determine the third data table associated with the SQL file;

[0027] Compare the third data table with the first data table to obtain the SQL file data table comparison results;

[0028] Based on the comparison results of the SQL file data tables, update the data used in the data tables.

[0029] Secondly, this application also provides a data table usage analysis device. The device includes:

[0030] The data extraction module is used to extract metadata from the Gaussian database of the business production cluster. The metadata includes a first data table, user stored procedures, and database functions.

[0031] The data parsing module is used to parse the user stored procedure and the database function to determine the second data table associated with the user stored procedure and the database function;

[0032] The data comparison module is used to compare the first data table and the second data table to obtain the data table comparison result.

[0033] The data analysis module is used to determine the data to be used in the data table based on the comparison results of the data table.

[0034] Thirdly, this application also provides a computer device. The computer device includes a memory and a processor, the memory storing a computer program, and the processor executing the computer program to implement the steps in the above-described data table usage analysis method embodiments.

[0035] Fourthly, this application also provides a computer-readable storage medium. The computer-readable storage medium stores a computer program thereon, which, when executed by a processor, implements the steps in the above-described data table usage analysis method embodiments.

[0036] Fifthly, this application also provides a computer program product. The computer program product includes a computer program that, when executed by a processor, implements the steps in the above-described data table usage analysis method embodiments.

[0037] The aforementioned data table usage analysis method, apparatus, computer equipment, storage medium, and computer program product extract metadata directly from the Gaussian database of the business production cluster. This metadata includes user stored procedures, database functions, and a first data table. Then, by parsing the user stored procedures and database functions, a second data table associated with them can be determined. Because stored procedure data allows users to encapsulate complex business logic for execution at the database level, user stored procedures and database functions can more accurately reflect which data tables the user has used, created, or modified, resulting in a more accurate second data table. This second data table is then compared with the first data table in the Gaussian database. Finally, based on the data table comparison data, the usage data of the data tables can be accurately analyzed and determined, thereby achieving a more accurate analysis of the data table usage in the Gaussian database. Attached Figure Description

[0038] Figure 1 is an application environment diagram of a data table usage analysis method in one embodiment;

[0039] Figure 2 This is a flowchart illustrating a data table usage analysis method in one embodiment;

[0040] Figure 3 This is a flowchart illustrating the steps of parsing a user stored procedure in one embodiment;

[0041] Figure 4 This is a flowchart illustrating the steps of parsing a user stored procedure in another embodiment;

[0042] Figure 5 This is a flowchart illustrating the data table usage analysis method in another embodiment;

[0043] Figure 6 This is a flowchart illustrating the data table usage analysis method in a detailed embodiment;

[0044] Figure 7 This is a structural block diagram of a data table usage analysis device in one embodiment;

[0045] Figure 8 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0046] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0047] The data table usage analysis method provided in this application embodiment can be applied to the application environment shown in Figure 1. The terminal 102 communicates with the server 104 via a network. The data storage system can store the data that the server 104 needs to process. The Gaussian database can be integrated onto the server 104, or it can be located in the cloud or on another network server.

[0048] Specifically, analysts can extract metadata from the Gaussian database of the business production cluster via terminal 102 and upload the metadata to server 104. This metadata includes a first data table, user stored procedures, and database functions. Then, server 104 parses the user stored procedures and database functions, determines the second data table associated with them, compares the first and second data tables to obtain a comparison result, and finally, based on the comparison result, determines the data used in each data table.

[0049] The terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, and smart in-vehicle systems. Portable wearable devices can include smartwatches, smart bracelets, and head-mounted devices. The server 104 can be implemented using a standalone server or a server cluster consisting of multiple servers.

[0050] In one embodiment, such as Figure 2 As shown, a method for analyzing the usage of data tables is provided. Taking the application of this method to server 104 in Figure 1 as an example, the method includes the following steps:

[0051] S100 extracts metadata from the Gaussian database of the business production cluster. The metadata includes the first data table, user stored procedures, and database functions.

[0052] The business production cluster refers to a computing cluster composed of multiple servers in an enterprise's production environment. This cluster may include multiple data nodes and management nodes for efficient processing of business data and applications. Gaussian Database is a distributed database system that supports standard SQL syntax and provides metadata access functionality. Metadata is data used to describe data, including but not limited to table structure, data attributes, and data relationships, such as field names, field types, and functions and statements related to user operations. The first data table is the data table stored in Gaussian Database, representing the data tables provided by Gaussian Database to users in the actual business process. User stored procedures are a set of user-defined SQL logical operations to complete specific functions, such as SQL structures related to SQL syntax like create / update procedure (function), which can be encapsulated as callable programs and stored in Gaussian Database. Database functions are the functions used by users during operations on Gaussian Database, reflecting the user's usage of Gaussian Database.

[0053] For example, the datax component can be used to periodically (e.g., daily) extract metadata from the Gaussian database on the business production cluster. Alternatively, metadata can be queried and extracted from the Gaussian database through a specific metadata call interface. It should be noted that the metadata may contain some non-essential information; pre-defined filtering logic can be used to retain only the first data table, user stored procedures, and database functions from the extracted metadata. The extracted metadata can be stored in temporary storage space for subsequent parsing and analysis.

[0054] S200, parse user stored procedures and database functions to determine the second data table associated with the user stored procedures and database functions.

[0055] The second data table refers to the data table that actually participates in user data operations, identified by parsing stored procedures and database functions. These data tables may overlap with the first data table, or they may include data tables not included in the first data table.

[0056] For example, the server can first extract SQL definition information or logical information from user stored procedures and database functions, or it can use a specific parsing engine to decompose the syntax structure of user stored procedures and database functions. The parsing engine can decompose user stored procedures and database functions based on SQL syntax rules to extract the SQL syntax structure and semantic information, such as extracting and analyzing the SQL statements used in user stored procedures and database functions, and key information in the SQL statements (such as table names, field names, operation types, etc.), and then identify the second data table that actually participates in user data operations.

[0057] S300, compare the first data table and the second data table to obtain the data table comparison result.

[0058] The data table comparison results are used to describe the differences between the first data table and the second data table in terms of the number of data tables, types, etc. For example, the second data table may include data tables that do not exist in the first data table, and the first data table may not be a complete subset of the second data table.

[0059] For example, set operations can be used to compare the first and second data tables, calculating their intersection (their common parts) and difference (tables not referenced in the first data table, and tables in the second data table that do not exist in the first data table). It should be noted that if the first data table contains tables not included in the second data table, this difference may indicate that these tables are redundant and not referenced during user operations. Similarly, if the second data table contains tables not included in the first data table, this difference may indicate that these tables are new tables created by the user or tables referenced by the user from storage space outside the Gaussian database.

[0060] S400, based on the data table comparison results, determines the data to be used in the data table.

[0061] The data table uses data to represent the actual usage of the data table during the user's operation based on the Gaussian database, including but not limited to the number of operations on the data table and the type of operation.

[0062] For example, based on the data table comparison results, redundant tables (tables that are infrequently accessed or not accessed by users) and high-frequency tables, user-created tables, or tables referenced through external dependencies in the Gaussian database can be identified. Redundant tables can be removed from the Gaussian database to save storage resources. For high-frequency tables, indexes and query methods can be added and optimized. For user-created tables or tables referenced through external dependencies, similar tables can be pre-stored in the Gaussian database for easy reference during subsequent operations.

[0063] The aforementioned data table usage analysis method extracts metadata directly from the Gaussian database of the business production cluster. This metadata includes user stored procedures, database functions, and the first data table. Then, by parsing the user stored procedures and database functions, the associated second data table can be determined. Because stored procedure data allows users to encapsulate complex business logic for execution on the database side, compared to user-defined statements, user stored procedures and database functions can more accurately reflect which data tables users have used, created, or modified, resulting in a more accurate second data table. This second data table is then compared with the first data table in the Gaussian database. Finally, based on the data table comparison data, the usage data of the data tables can be accurately analyzed and determined, thus achieving a more accurate analysis of the data table usage in the Gaussian database.

[0064] In one embodiment, S200 includes: invoking a preset Gaussian parsing engine to parse user stored procedures and database functions, and determining the data source table, data target table, and temporary data table associated with the user stored procedures and database functions.

[0065] The pre-defined Gaussian parsing engine is based on the Gaussian SQL syntax infrastructure. The second data table includes a data source table, a data target table, and a temporary data table. The Gaussian parsing engine is used to parse SQL statements in the Gaussian database. It is based on the Gaussian SQL syntax infrastructure, including but not limited to operators, common characters (single quotes, double quotes, commas, semicolons, newlines, etc.), keywords (846 such as CREATE, ADD, ADMIN, ALL, ALSO, AND, AS, etc.), and basic structures (SQL body, inline comments, paragraph comments, etc.). Therefore, the Gaussian parsing engine can perform SQL syntax and semantic analysis on user stored procedures and database functions.

[0066] The data source table is the table used as data input in user stored procedures or database functions, typically referenced in statements such as `SELECT sth FROM`, `UPDATE`, `DELETE`, `ALTER`, and `MERGE` (using). The data target table is the table where the user writes the operation results, typically referenced in statements such as `INSERT`, `COMMIT`, `MERGE`, `UPDATE`, `DELETE`, `ALTER`, and `RENAME`. Temporary data tables are tables created and used temporarily during user operations, typically referenced in statements such as `CREATE TIME` or `Temporary TABLE(AS)`, used for storing intermediate results, and their lifespan is limited to the current session or transaction. For example, the server calls a pre-defined Gaussian parsing engine to analyze the SQL statements in user stored procedures and database functions in detail, locating specific statements within the SQL statements to identify the three types of data tables involved in the user's operation.

[0067] In this embodiment, by calling the Gaussian parsing engine to parse user stored procedures and database functions, the data tables operated on during the user's operation can be comprehensively sorted out. Furthermore, the Gaussian parsing engine has the ability to handle complex SQL syntax, which can improve the accuracy of the parsing results and thus improve the reliability of the data table usage obtained from the final analysis.

[0068] In one embodiment, such as Figure 3 As shown, S200 includes:

[0069] S210 parses the user stored procedure into multiple syntax infrastructures.

[0070] S220 matches the database functions and the parsed multiple basic syntax structures with the Gaussian SQL syntax structure to obtain the matching results.

[0071] S230, Based on the matching results, determine the data source table, data target table, and temporary data table associated with the user stored procedure and database function.

[0072] The basic syntax structure is an abstract representation of the smallest syntactic unit of an SQL statement. Taking the `CREATE PROCEDURE` syntax for user stored procedures as an example, the `CREATE PROCEDURE` syntax can be parsed into the keyword `CREATE`, an optional keyword list (`OR`, `REPLACE`), an optional syntax structure temporary syntax, the keyword `PROCEDURE`, an object name structure, a stored procedure syntax list structure, etc. The optional list refers to any one or more items that can be parsed into the list. The optional syntax structure means that the corresponding structure may or may not exist in the structure. The object name structure refers to the nouns that appear in the user stored procedure, which is the name of the user stored procedure in this case.

[0073] For example, the Gaussian parsing engine is invoked to decompose the user stored procedure into multiple syntactic basic structures. Furthermore, if the database function is complex, it can also be parsed into multiple syntactic basic structures before subsequent matching. Specifically, the parsed syntactic basic structures and database functions can be matched one by one with the Gaussian SQL syntactic basic structures, comparing their syntax types, table references, field references, etc., to obtain matching results. These matching results can characterize the operation logic of the user stored procedure and database function on the data table. Further, the data table operation information in the user stored procedure and database function is extracted from the matching results. Based on this information, the second data table involved in the user operation is determined, and according to the specific data table operation statements, the second data table is divided into a data source table, a data target table, and a temporary data table.

[0074] In this embodiment, by matching the Gaussian SQL syntax basic structure and parsing the user stored procedure to obtain multiple syntax basic structures and database functions, the operation information such as the user's operation type and number of operations on the data table can be effectively and accurately identified, providing a reliable basis for subsequent data usage analysis.

[0075] In one embodiment, such as Figure 4 As shown, S230 includes:

[0076] S231, Based on the matching results, determine the SQL syntax tree for user stored procedures and database functions.

[0077] S232, Based on the SQL syntax tree, determine the data source table, data target table, and temporary data table associated with user stored procedures and database functions.

[0078] The SQL syntax tree, also known as the abstract syntax tree, is a structured representation of SQL statements. It describes the syntactic relationships of SQL statements through hierarchical nodes, such as operands, operation types, and operators.

[0079] For example, based on the matching results, the server organizes the basic syntax structures obtained from parsing user stored procedures and database functions into an SQL syntax tree. Each basic syntax structure corresponds to a node in the SQL statement, such as operation type, table name, field name, etc. Based on the contextual relationships of each basic syntax structure, the branching structure of the SQL syntax tree can be generated. Furthermore, based on the SQL syntax tree, the user's statements operating on the data tables can be identified. When statements such as insert, comment, merge, update, delete, alter, and rename are identified, the data table corresponding to the table name in these SQL statements is the target table. When statements such as create temp / temporary table (as) are identified, the data table corresponding to the table name in these SQL statements is the temporary table. When statements such as select sth from, update, delete, alter, and merge (using) are identified, the data table corresponding to the table name in these SQL statements is the source table.

[0080] In this embodiment, by generating an SQL syntax tree, user stored procedures and database functions can be represented in a standardized manner, providing a global perspective. Furthermore, the SQL syntax tree can clearly present complex nested syntax, thereby enabling a more comprehensive and accurate identification of the overall structure and logic of user stored procedures and database functions, providing a reliable basis for subsequent analysis of data table usage.

[0081] In one embodiment, the user stored procedure includes a table creation statement, and the method further includes: determining the update time of the second data table based on the table creation statement, and determining the degree of impact of updating the second data table on the data used by the data table based on the update time.

[0082] The table creation statement refers to the SQL statement used to define a data table, such as `CREATE TABLE` or `CREATE TEMPORARY TABLE`. The update time is the time when the data table was last modified, which can be obtained based on metadata analysis or through table log analysis.

[0083] For example, the server can periodically (e.g., daily) identify table creation statements from user stored procedures, then extract information such as the table name and table field definitions from the table creation statements. By querying the table log of the corresponding table, the update time of the second table can be determined. In addition, the specific updated fields of the table can be determined by querying the table log.

[0084] Furthermore, when a recent update to a data table is detected, if other user operations require access to that table, the changes to table fields may cause the data table access to fail, thus affecting data usage. For example, if the name of table A is modified on the same day, and a user's pre-deployed operation in the business production cluster involves table A, subsequent execution of that operation may result in a failure to access table A, impacting data usage.

[0085] In this embodiment, by periodically checking the table creation statements in user stored procedures and database functions, the modification and update status of the relevant database can be identified. This allows for the rapid and accurate identification of the impact of the second data table update on other operating procedures in the business production cluster, thereby quickly and accurately capturing changes in data table usage.

[0086] In one embodiment, such as Figure 5 As shown, the method also includes:

[0087] The S500 receives an SQL file uploaded by the client, parses the SQL file, and determines the third data table associated with the SQL file.

[0088] S600 compares the third data table with the first data table to obtain the comparison results of the SQL file data tables. Based on the comparison results of the SQL file data tables, the data used in the data tables is updated.

[0089] For example, in this embodiment, in addition to analyzing user stored procedures and database functions in the business production cluster, users can also upload SQL files to be analyzed via the client. Similar to the parsing of user stored procedures and database functions, the server can also parse the SQL files uploaded by the client's front-end page and analyze them to obtain a third data table associated with the SQL file. The third data table includes, but is not limited to, a data source table, a data target table, and a temporary data table. Furthermore, by comparing the first data table and the third data table, the differences between the data table actually operated by the user and the data table in the Gaussian database can be identified, and the data table usage data can be updated accordingly.

[0090] In this embodiment, users are allowed to upload SQL files through the client's front-end page. Compared to analyzing only user stored procedures or database functions in the business production cluster, combining the SQL files provided by the user with the analysis of data table usage can provide a more comprehensive and accurate analysis of the data tables involved in the user's actual operations, thereby obtaining more accurate data table usage data.

[0091] To provide a clearer explanation of the data table usage analysis method provided in this application, the following is in conjunction with the appendix. Figure 6A detailed embodiment will be explained below, which includes the following steps:

[0092] S601 extracts the first data table, user stored procedures, and database functions from the Gaussian database of the business production cluster, and parses the user stored procedures into multiple basic syntax structures.

[0093] S602 matches the database functions and the parsed multiple basic syntax structures with the Gaussian SQL syntax structure to obtain the matching results. Based on the matching results, the SQL syntax tree of the user stored procedures and database functions is determined.

[0094] S603, based on the SQL syntax tree, determines the data source table, data target table, and temporary data table associated with user stored procedures and database functions.

[0095] S604. Compare the data source table, data target table, and temporary data table with the second data table to obtain the data table comparison results. Based on the data table comparison results, determine the data to be used in the data table.

[0096] S605: Based on the table creation statement of the user stored procedure, determine the update time of the second data table, and based on the update time, determine the degree of impact of updating the second data table on the data used by the data table.

[0097] S606 receives an SQL file uploaded by the client, parses the SQL file, and determines the third data table associated with the SQL file.

[0098] S607: Compare the third data table with the first data table to obtain the comparison result of the SQL file data table. Update the data table using the data based on the comparison result of the SQL file data table.

[0099] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0100] Based on the same inventive concept, this application also provides a data table usage analysis device for implementing the data table usage analysis method described above. The solution provided by this device is similar to the solution described in the above method; therefore, the specific limitations of one or more data table usage analysis device embodiments provided below can be found in the limitations of the data table usage analysis method described above, and will not be repeated here.

[0101] In one embodiment, such as Figure 7 As shown, a data table usage analysis device 700 is provided, including: a data extraction module 701, a data parsing module 702, a data comparison module 703, and a data analysis module 704, wherein:

[0102] The data extraction module 701 is used to extract metadata from the Gaussian database of the business production cluster. The metadata includes the first data table, user stored procedures, and database functions.

[0103] The data parsing module 702 is used to parse user stored procedures and database functions to determine the second data table associated with the user stored procedures and database functions.

[0104] The data comparison module 703 is used to compare the first data table and the second data table to obtain the data table comparison results.

[0105] Data analysis module 704 is used to determine the data to be used in a data table based on the comparison results of the data tables.

[0106] In one embodiment, the data parsing module 702 is further configured to invoke a preset Gaussian parsing engine to parse user stored procedures and database functions, and determine the data source table, data target table, and data temporary table associated with the user stored procedures and database functions. The preset Gaussian parsing engine is determined based on the Gaussian SQL syntax basic structure, and the second data table includes the data source table, data target table, and data temporary table.

[0107] In one embodiment, the data parsing module 702 is further configured to parse the user stored procedure into multiple basic syntax structures, match the database function and the parsed multiple basic syntax structures with the Gaussian SQL basic syntax structure to obtain matching results, and determine the data source table, data target table and data temporary table associated with the user stored procedure and the database function based on the matching results.

[0108] In one embodiment, the data parsing module 702 is further configured to determine the SQL syntax tree of the user stored procedure and database function based on the matching result, and determine the data source table, data target table, and data temporary table associated with the user stored procedure and database function based on the SQL syntax tree.

[0109] In one embodiment, the data table usage analysis device 700 is further configured to determine the update time of the second data table based on the table creation statement, and determine the degree of impact of updating the second data table on the data table usage data based on the update time.

[0110] In one embodiment, the data table usage analysis device 700 is further configured to receive an SQL file uploaded by a client, parse the SQL file, determine a third data table associated with the SQL file, compare the third data table with the first data table to obtain the SQL file data table comparison result, and update the data table usage data based on the SQL file data table comparison result.

[0111] The modules in the aforementioned data table usage analysis device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of a computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0112] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 8 As shown, this computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media. The database stores metadata and other data. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When the computer program is executed by the processor, it implements a data table usage analysis method.

[0113] Those skilled in the art will understand that Figure 8 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than shown in the figure, or combine certain components, or have a different component arrangement.

[0114] In one embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above-described data table usage analysis method embodiment.

[0115] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, which, when executed by a processor, implements the steps in the above-described data table usage analysis method embodiment.

[0116] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above-described data table usage analysis method embodiment.

[0117] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of related data must comply with the relevant laws, regulations and standards of the relevant countries and regions.

[0118] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments described above. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0119] The technical features of the above embodiments can be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0120] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A method for analyzing data table usage, characterized in that: The method comprises: Extract metadata from the Gaussian database of the business production cluster, where the metadata includes a first data table, a user stored procedure, and a database function; Parsing the user stored procedure and the database function to determine a second data table associated with the user stored procedure and the database function; Comparing the first data table with the second data table to obtain a data table comparison result; Based on the data table comparison result, the data table usage data is determined.

2. The method according to claim 1, characterized in that The step of parsing the user stored procedure and the database function to determine a second data table associated with the user stored procedure and the database function includes: Calling a preset Gaussian parsing engine to parse the user stored procedure and database function, and determining a data source table, a data target table, and a data temporary table associated with the user stored procedure and the database function; The preset Gaussian parsing engine is determined based on the Gaussian SQL grammar basic structure, and the second data table includes the data source table, the data target table and the data temporary table.

3. The method according to claim 1, characterized in that When the preset Gaussian parsing engine is called, the following steps are performed: Parsing the user stored procedure into a plurality of grammatical basic structures; Matching the database function and the multiple parsed grammar basic structures with the Gaussian SQL grammar basic structure to obtain a matching result; According to the matching result, a data source table, a data target table, and a data temporary table associated with the user stored procedure and the database function are determined.

4. The method according to claim 3, characterized in that The step of determining the data source table, the data target table, and the data temporary table associated with the user stored procedure and the database function according to the matching result includes: Determine the SQL syntax tree of the user stored procedure and the database function according to the matching result; According to the SQL syntax tree, a data source table, a data target table, and a data temporary table associated with the user stored procedure and the database function are determined.

5. The method according to any one of claims 1 to 4, characterized in that: The user stored procedure includes a table creation statement, and the method further includes: Determining the update time of the second data table according to the table creation statement; According to the update time, the degree of influence of updating the second data table on the data used in the data table is determined.

6. The method according to any one of claims 1 to 4, characterized in that: The method further comprises: Receive SQL files uploaded by the client; Parsing the SQL file to determine a third data table associated with the SQL file; Compare the third data table with the first data table to obtain a SQL file data table comparison result; According to the comparison result of the SQL file data table, the data table usage data is updated.

7. A data table usage analysis device, characterized in that: The device comprises: A data extraction module, used to extract metadata from the Gaussian database of the business production cluster, wherein the metadata includes a first data table, a user stored procedure, and a database function; A data parsing module, used for parsing the user stored procedure and the database function, and determining a second data table associated with the user stored procedure and the database function; A data comparison module, used for comparing the first data table with the second data table to obtain a data table comparison result; The data analysis module is used to determine the data table usage data based on the data table comparison result.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images