Database retrieval method, device and equipment based on table semantic annotation

Abstract

The invention discloses a database retrieval method, device and equipment based on table semantic annotation. The method comprises the steps of determining a target data file in response to an operation of selecting a data file from an accessed data source by a user; associating a plurality of data files included in the target data file, and integrating the plurality of data files into an initial report; according to a preset dictionary database and a dynamic splicing technology, converting the statistical calibers input by the user into SQL statements to generate index information; and generating a final report according to the field information selected by the user from the field information included in the initial report and the index information. Therefore, the simple statistical caliber input by the user is analyzed and spliced, the SQL statement is automatically generated to obtain the index information required by the user, the user can flexibly set the corresponding information in the final report on the basis of reducing the requirement on the programming capability of the user, and the user experience is improved. And searching the required report data in the database by the user to generate a final report.

Description

Technical Field

[0001] The present application relates to the technical field of database retrieval, and more specifically, to a database retrieval method, apparatus, and device based on table semantic annotation. Background Art

[0002] In related BI tools, users are required to input programming languages ​​(for example, SQL) in order to retrieve the required report data in the database based on the programming language, and thus generate the reports required by users based on these report data. This places certain requirements on the user's programming ability, and raises the threshold for users to search in the database. Summary of the Invention

[0003] In view of the above problems, this application proposes a database retrieval method, device and equipment based on table semantic annotation, which can effectively lower the usage threshold of BI tools and meet the user's dynamic analysis needs, thereby improving the user experience.

[0004] In a first aspect, an embodiment of the present application provides a database retrieval method based on table semantic annotations, the method comprising: determining a target data file in response to a user's operation of selecting a data file in an accessed data source; associating multiple data files included in the target data file according to a preset metadata access interface and a preset table association method to integrate the multiple data files into an initial report; converting the statistical caliber input by the user into an SQL statement according to a preset dictionary library and dynamic splicing technology to generate indicator information based on the target field information included in the initial report; generating a final report based on the field information and indicator information selected by the user from the field information included in the initial report.

[0005] In the second aspect, an embodiment of the present application also provides a database retrieval device based on table semantic annotations, which includes: a determination module for determining a target data file in response to a user's operation of selecting a data file in an accessed data source; an establishment module for associating multiple data files included in the target data file according to a preset metadata access interface and a preset table association method, so as to integrate multiple data files into an initial report; an execution module for converting the statistical caliber input by the user into an SQL statement according to a preset dictionary library and dynamic splicing technology, so as to generate indicator information according to the target field information included in the initial report; a generation module for generating a final report according to the field information and indicator information selected by the user from the field information included in the initial report.

[0006] In a third aspect, an embodiment of the present application also provides a database retrieval device based on table semantic annotations, comprising a processor, a memory, and one or more applications; the one or more applications are stored in the memory and configured to be executed by the processor to implement the above-mentioned database retrieval method based on table semantic annotations.

[0007] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, in which program code is stored, wherein when the program code is run by a processor, the above-mentioned database retrieval method based on table semantic annotation is executed.

[0008] The technical solution provided by this application includes: determining a target data file in response to a user selecting a data file from an accessed data source; associating multiple data files included in the target data file according to a preset metadata access interface and a preset table association method to integrate the multiple data files into an initial report; converting the statistical caliber input by the user into an SQL statement according to a preset dictionary library and dynamic splicing technology to generate indicator information based on the target field information included in the initial report; and generating a final report based on the field information and the indicator information selected by the user from the field information included in the initial report. Thus, based on the actual business needs, the user selects field information and enters a relatively simple statistical caliber based on the initial report after integrating multiple data files. By parsing and splicing the statistical caliber, an SQL statement is automatically generated to obtain indicator information that meets the user's actual needs, and the final report is generated by selecting the field information and indicator information. This reduces the requirements for user programming skills, and the required report data can be retrieved from the database to generate the final report. The user generates the required report according to the actual analysis needs, thereby improving the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] In order to more clearly illustrate the technical solutions in the embodiments of this application, the following is a brief introduction to the drawings required for describing the embodiments. Obviously, the drawings described below are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments and drawings obtained by ordinary technicians in this field without creative work are within the scope of protection of this invention.

[0010] Figure 1 A flowchart of a database retrieval method based on table semantic annotation provided in an embodiment of the present application is shown.

[0011] Figure 2 A schematic structural diagram of a database retrieval device based on table semantic annotations provided in an embodiment of the present application is shown.

[0012] Figure 3A schematic diagram of the structure of a database retrieval device based on table semantic annotation provided in an embodiment of the present application.

[0013] Figure 4 A schematic structural diagram of a computer-readable storage medium provided in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0014] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application.

[0015] In related BI tools, users are required to input programming languages ​​(for example, SQL) in order to retrieve the required report data in the database based on the programming language, and thus generate the reports required by users based on these report data. This places certain requirements on the user's programming ability, and raises the threshold for users to search in the database.

[0016] In order to improve the above-mentioned problems, the present application provides a database retrieval method, apparatus and device based on table semantic annotations, the method comprising: determining a target data file in response to a user's operation of selecting a data file in an accessed data source; associating multiple data files included in the target data file according to a preset metadata access interface and a preset table association method, so as to integrate the multiple data files into an initial report; converting the statistical caliber input by the user into an SQL statement according to a preset dictionary library and dynamic splicing technology, so as to generate indicator information according to the target field information included in the initial report; generating a final report according to the field information selected by the user from the field information included in the initial report and the indicator information.

[0017] Based on actual business needs, users can select field information and enter a relatively simple statistical caliber based on the initial report generated by integrating multiple data files. By parsing and splicing the statistical caliber, SQL statements are automatically generated to obtain the indicator information that meets the user's actual needs. The final report is generated by selecting field information and indicator information. This reduces the requirement for user programming skills, allowing users to retrieve the required report data from the database to generate the final report. Users can also generate the required report based on their actual analysis needs, thereby improving the user experience.

[0018] See also Figure 1 , Figure 1 FIG2 shows a flow chart of a database search method based on table semantic annotation provided by an embodiment of the present application. Figure 1 As shown, the database retrieval method based on table semantic annotation may include steps 110 to 140.

[0019] In step 110 , in response to a user selecting a data file from an accessed data source, a target data file is determined.

[0020] In some implementations, the database retrieval method based on table semantic annotations can be applied to an operating system for processing and generating required reports.

[0021] In some embodiments, the data source may be a structured file and a relational database. The structured file may include an Excel file and / or a CSV file. The relational database may be a MySQL database and / or a PostgreSQL database. It is understood that the relational database may include multiple interrelated Excel files and / or CSV files.

[0022] In some embodiments, the data file may be an Excel file, a CSV file included in an accessed data source, an Excel file included in a database, and / or multiple CSV files. In other words, the user selects a desired data file from the accessed Excel file, CSV file, and multiple Excel files and / or CSV files included in a database to determine the target data file.

[0023] In one specific embodiment, a user clicks a "New Connection" button on the operating system interface to transfer the data source to be connected to the system, or to establish a connection between the operating system and the data source to be connected. For example, the user can enter the IP address, port number, database name, account number, and password on the interface to establish a connection between the corresponding database and the operating system. After the connection between the data source to be connected and the operating system is established, the user can select the desired data file from the data sources displayed on the interface, allowing the operating system to determine the target data file.

[0024] Exemplarily, the accessed data sources include a "driver information table", a "vehicle information table" and a "traffic violation information table". By displaying the "driver information table", "vehicle information table" and "traffic violation information table" on the interface of the operating system, the user selects the two data files of "driver information table" and "vehicle information table" according to actual needs. The operating system responds to the user's operation of selecting the "driver information table" and "vehicle information table" and determines the "driver information table" and "vehicle information table" as target data files.

[0025] Furthermore, after the user uploads the data source to the operating system, or the user establishes a connection between the data source and the operating system, the operating system needs to further process the data source to connect the data source to the operating system, so that the operating system can subsequently extract the corresponding field information in the data source. Specifically, in some embodiments, the database retrieval method based on table semantic annotations may further include the following steps: (1) Determine the type of data source based on its identification characteristics.

[0026] (2) Based on the access engine corresponding to the type, the access descriptor corresponding to the data source is extracted to connect the data source to the operating system.

[0027] In some embodiments, the identification feature may include at least one of an extension, a file signature, and a connection port. The extension may be a file suffix corresponding to an Excel file or a CSV file. For example, the extension may be ".xlsx." For another example, the extension may be ".csx." The file signature may be file header information corresponding to an Excel file or a CSV file. For example, the file header information may be a ZIP structure. For another example, the file header information may be in a comma-delimited format. The connection port may be a connection interface of a database. For example, the connection port may be port 3306. For another example, the connection port may be port 5432.

[0028] In some implementations, the type includes at least one of an Excel file, a CSV file, and a database.

[0029] In some implementations, the access descriptor may be one of a file structure, metadata, and a driver.

[0030] In one specific embodiment, if the operating system determines that the extension of the data source to be accessed is ".xlsx", the operating system determines that the type of the data source to be accessed is an Excel file. In another specific embodiment, if the operating system determines that the file header information of the data source to be accessed is a ZIP structure, the operating system determines that the type of the data source to be accessed is an Excel file.

[0031] In one specific embodiment, if the operating system determines that the extension of the data source to be accessed is ".xlsx", the operating system determines that the type of the data source to be accessed is a CSV file. In another specific embodiment, if the operating system determines that the file header information of the data source to be accessed is in a comma-delimited format, the operating system determines that the type of the data source to be accessed is a CSV file.

[0032] In one specific embodiment, if the operating system detects, through protocol detection, that the access port corresponding to the data source to be accessed is port 3306, the operating system determines that the type of the data source to be accessed is a MySQL database. In another specific embodiment, if the operating system detects, through protocol detection, that the access port corresponding to the data source to be accessed is port 5432, the operating system determines that the type of the data source to be accessed is a PostgreSQL database.

[0033] In some embodiments, the user can enter the IP address, port number, database name, account number and password of the database to be accessed into the operating system, establish a connection between the database and the operating system, and then trigger the operating system to perform protocol detection to detect the access port corresponding to the database to be accessed to determine the type of database to be accessed, that is, determine whether the database to be accessed is a MySQL database or a PostgreSQL database.

[0034] After determining the type of the data source to be accessed, the operating system determines the engine corresponding to the type of the data source to be accessed, performs corresponding analysis on the data source to be accessed, obtains the access descriptor of the data source to be accessed, and thus connects the data source to be accessed to the operating system, so that the operating system can extract field information in the accessed data source according to the actual needs of the user.

[0035] Specifically, the step is based on the access engine corresponding to the type, extracting the access descriptor corresponding to the data source to access the data source, and may also include the following steps: (1) If the file type is Excel, the engine is indeed the Apache POI parser.

[0036] (2) Input the data source into the Apache POI parser to obtain the file structure and metadata corresponding to the Excel file.

[0037] If the operating system detects that the type of the data source to be accessed is an Excel file, it determines that the engine corresponding to the data source to be accessed is the Apache POI parser. Based on the operating system's determination that the engine is the Apache POI parser, the operating system inputs the data source to be accessed into the Apache POI parser, and parses the table name, row data, and column data in the data source to be accessed to obtain the file structure and metadata.

[0038] In some implementations, the step of extracting the access descriptor corresponding to the data source based on the access engine corresponding to the type to access the data source may also include the following steps: (1) If the file type is CSV, the engine is determined to be the OpenCSV parser.

[0039] (2) Input the data source into the OpenCSV parser to obtain the file structure and metadata of the CSV file.

[0040] If the operating system detects that the type of the data source to be connected is a CSV file, it determines that the engine corresponding to the data source to be connected is the OpenCSV parser. Based on the operating system determining that the engine is the OpenCSV parser, the operating system inputs the data source to be connected into the OpenCSV parser, parses the CSV file, and thus obtains the file structure and metadata.

[0041] In some embodiments, the step is based on the access engine corresponding to the type, extracting the access descriptor corresponding to the data source to access the data source, and may also include the step of: if the type is a database, determining that the engine is a driver corresponding to the database.

[0042] If the operating system detects that the type of the data source to be connected is a MySQL database or a PostgreSQL database, it determines that the engine corresponding to the MySQL database or the PostgreSQL database is the driver corresponding to the MySQL database or the PostgreSQL database, and the operating system executes the driver to connect the database to be connected to the system.

[0043] Based on the recognition of the existence of the data source to be accessed, the operating system identifies the type of the data source to be accessed to determine the corresponding engine of the data source to be accessed, and then accurately distinguishes the access connection requests of Excel files, CSV files and databases through file header parsing, metadata analysis or protocol detection. There is no need for manual specification by the user, which reduces the complexity of operation, reduces the possibility of incorrect configuration, and improves the user experience.

[0044] After the operating system accesses the Excel file, and / or CSV file, and / or database, it can verify the data in the Excel file, and / or CSV file, and / or database, and then confirm its type (for example, whether the data type is numeric or date), and then automatically perform format conversion to improve subsequent retrieval efficiency.

[0045] In some embodiments, the operating system scans the data source (i.e., the connected Excel file, and / or CSV file, and / or database), extracts sample data from the data source (e.g., the first N rows in the Excel file or a random sample), and then analyzes the content characteristics of the sample data from the extracted data source, i.e., analyzes the data type of each field in the sample data (e.g., determines whether the data type of the field is an integer, a string, or a date, etc.).

[0046] In some embodiments, when determining the data type of each field in the sample data, the operating system may also handle possible outliers (for example, a column of mixed data types in an Excel file may be marked as a string or a generic type). For example, if it is detected that the sample data in the same column of an Excel file includes fields of multiple data types (for example, integers and strings), and the sample data in that column is marked as a specific type (for example, integers), the type of the field in that column in the Excel file corresponding to the sample data in that column may be modified to a generic type.

[0047] By analyzing the fields contained in the accessed Excel files, and / or CSV files, and / or databases, the cost of manually defining the data structure is reduced, thereby improving the processing efficiency of the operating system and improving the efficiency of subsequent data retrieval. After the operating system accesses the data source to be accessed, the multiple data files included in the target data file determined by the user are independent of each other. When the user wants to use different data files in the multiple data files included in the target data file, it is necessary to consult different data files, which is inefficient and affects the user experience. Therefore, it is necessary to integrate the multiple data files included in the target data file into a report, so that the user can directly consult the field information contained in the target data file through the integrated report, effectively improving work efficiency. Specifically: In step 120 , multiple data files included in the target data file are associated according to a preset metadata access interface and a preset table association method, so as to integrate the multiple data files into an initial report.

[0048] In some embodiments, the preset metadata access interface can be an interface connection. For example, the preset metadata access interface can be an interface (Java Database Connectivity, JDBC). In a specific embodiment, the preset metadata access interface can be the DatabaseMetaData class in the interface. The operating system scans the table structure of the target data file through the preset metadata access interface to obtain the field information contained in the target data file. For example, all table names (for example, "driver_info"), field names (for example, "id_card") and meta information (for example, data types, primary key constraints) contained in the target data file are obtained. The operating system then converts the field information in the multiple data files included in the target data file into field information with business attributes, and the operating system presents the field information with business attributes on the operation interface.

[0049] In some embodiments, the preset table association method may be an equijoin method and / or a non-equijoin method. The operating system, in response to a user's operation on field information having business attributes, associates multiple data files included in the target data file to integrate the multiple data files included in the target data file into an initial report.

[0050] After the user selects the target data file in the connected data source, the operating system extracts the field information included in multiple data files in the target data file through the preset metadata access interface. The operating system then associates different data files through the preset table association method, thereby integrating the multiple data files in the target data file into an initial report, so that subsequent users can directly perform corresponding operations based on the initial report without switching between multiple data reports, effectively improving the efficiency of users' data access.

[0051] As can be seen from the above description, after the user selects the target data file in the connected data source, the operating system extracts the field information contained in multiple data files in the target data file through the preset metadata access interface. At this time, the field information is in the form of characters (for example, the field information of "sn" is presented), rather than field information with business attributes (for example, the field information of "driver's name" is presented, that is, the actual meaning of the field information "sn" is "driver's name"). If the field information extracted by the operating system is directly displayed, the user cannot know the business information corresponding to the field information. Therefore, it is necessary to convert the field information of multiple data files in the target data file into field information with business attributes. Specifically: In some embodiments, the step of associating multiple data files included in the target data file according to a preset metadata access interface and a preset table association method to integrate the multiple data files into an initial report may include the following steps: (1) According to the preset metadata access interface, field information of multiple data files is extracted, and the field information with business attributes is presented.

[0052] (2) In response to the user selecting the field information to be associated from the field information with business attributes, multiple data files are associated through the field information to be associated according to the preset table association method to create an initial report.

[0053] In one embodiment, the preset metadata access interface may be a method for obtaining table information and a method for obtaining column information. Specifically, the system may obtain field information included in multiple data files in a target data file by calling the getTables() and getColumns() methods, and convert the field information into field information with business attributes through the getTables() and getColumns() methods.

[0054] In a specific embodiment, after the operating system extracts the field information included in multiple data files in the target data file through a preset metadata access interface, it can determine the business attribute corresponding to each field information in a comparison table containing field information and field information with business attributes, thereby displaying each field information with business attributes on the operation interface. For example, multiple data files in the target data file include field information "sn". Through the comparison table, it can be queried that the field information "sn" corresponds to each field information with business attributes, which is "Driver Name". Therefore, the field information "Driver Name" is displayed on the operation interface, that is, a column of data will be displayed on the operation interface, with the first row being "Driver Name", the second row being Driver A, the second row being Driver B, and so on.

[0055] The system presents field information with business attributes in the interface, and users can select the field information to be associated in the interface. That is, users can select the field information that needs to be associated from the multiple field information displayed in the operation interface. For example, the user can select the "ID number" in the driver information table and the "ID number" in the vehicle information table as the field information to be associated. Based on the field information to be associated selected by the user, the system integrates multiple data files in the target data file through the preset table association method. For example, the operating system associates the "ID number" in the driver information table with the "ID number" in the vehicle information table through the preset table association method, thereby integrating the driver information table and the vehicle information table into an initial report.

[0056] Furthermore, in some embodiments, the step of extracting field information of multiple data files according to a preset metadata access interface to present field information with business attributes may also include the following steps: (1) According to a preset depth-first search algorithm, traverse the field information included in multiple data files; the field information includes the file names and specific field information corresponding to the multiple data files.

[0057] (2) Create multiple root nodes based on the file name.

[0058] (3) Create multiple child nodes based on the specific field information in the same data file as the target file name; the target file name is any file name in the file name.

[0059] (4) Based on multiple root nodes and multiple child nodes, display information in JSON format is obtained.

[0060] (5) Based on the display information in JSON format, present the field information with business attributes.

[0061] The preset depth-first search algorithm may be a depth-first search (DFS) algorithm, which traverses field information of multiple data files in the target data file and generates a hierarchical representation, namely, a JSON tree.

[0062] The operating system first initializes the depth-first search algorithm, setting each root node of the JSON tree to the file name of the target data file. At this root node, it recursively retrieves the byte list (i.e., specific field information) from the same data file. Based on the byte list, it creates multiple child nodes, each of which includes information such as the byte name, data type, and whether it is a primary key. The depth-first search algorithm traversal results are then serialized into JSON-formatted display information using a JSON library (Java's Jackson). The operating system then sets up an interface based on this JSON-formatted display information, presenting the field information with business attributes corresponding to multiple data files in the target data file on the user interface, allowing the user to select the field information to be associated.

[0063] After presenting the field information with business attributes on the operation interface, the operating system can also select the field information to be displayed in the final report based on actual needs. For example, the field information with business attributes presented on the operation interface are field information A, field information B, field information C, and field information D. The user can click field information A, field information B, and field information C. In response to the user's operation, the operating system will reflect field information A, field information B, and field information C in the final report.

[0064] It can be seen from this that the operating system presents field information with business attributes on the system interface, and the user selects the field information to be associated from the presented field information with business attributes, thereby triggering the operating system to associate multiple data files in the target data file through a preset table association method, so as to integrate multiple data files into an initial report.

[0065] However, in the method of generating an initial report by having the user select the field information to be associated according to actual needs, the user needs to have a certain understanding of the field information of multiple data files before determining the field information to be associated. Based on the above situation, in order to more efficiently integrate multiple data files in the target data file into an initial report, in some embodiments, the database retrieval method based on table semantic annotations may also include the steps of: based on a field semantic vectorization engine, using a word vector and context field method (Word2Vec+Field-Context algorithm) to generate a field feature vector (dimension 256), automatically recommending associated fields through cosine similarity, designing an associated path dynamic programming algorithm, supporting automatic path optimization of multi-table cascade associations, so as to integrate multiple data files into an initial report.

[0066] For example, the operating system may convert field information included in multiple data files included in the target data file into a dimensional feature vector according to a preset double encoding mechanism.

[0067] The pre-set dual encoding mechanism includes a word vector encoding layer and a context encoding layer. The system uses the word vector encoding layer to perform semantic segmentation and standardization on the field information contained in multiple data files within the target data file. For example, the field "customer_id" is processed into "customer" and "id." The system uses the word vector encoding layer to pre-train a word vector model using large-scale domain data, placing words with similar semantics close together in the vector space. For example, the vector distance between "customer" and "customer" is less than 0.3.

[0068] The context encoding layer can include context definitions and dynamic weights. Specifically, the context definition extracts adjacent fields from the table containing the field as a context window. For example, the context of the field "delivery_address" is: the fields "order_id", "recipient_name", "product_list", and "payment_method". Dynamic weight learning automatically learns the importance weights of different context fields through a bidirectional LSTM network. For example, the context influence coefficient of core business field information (e.g., A) is greater than 0.9.

[0069] The system then concatenates the 128-dimensional word vector obtained by the word vector encoding layer with the 128-dimensional context vector obtained by the context encoding layer to form a 256-dimensional comprehensive vector.

[0070] After obtaining the 256-dimensional comprehensive vector, the system uses cosine similarity to compare the similarities between the field information in different data files. For example, the similarity between the "mobile number" field in data file A and the "contact number" field in data file B is 0.92. Another example is the similarity between the "customer ID" field in data file A and the "user_code" field in data file B, which is 0.88.

[0071] The operating system can also verify similarity based on the corresponding data type, value range distribution, and whether the field information is a primary key. For example, the field information "Customer Code" in data file A is (character type, prefix rule), and the field information "cust_no" in data file B is (numeric type, no rule). Although the data types corresponding to the field information "Customer Code" and "cust_no" are different, the similarity in value range distribution is greater than 85%. Therefore, "Customer Code" and "cust_no" can be used as the field information to be associated, so as to associate data files A and B, thereby integrating them into an initial report including data files A and B.

[0072] That is to say, the operating system can use the field information corresponding to the similarity between different vectors in the 256-dimensional comprehensive vector that is greater than the preset threshold as the field information to be associated, and can use the field information corresponding to at least one of the similarity of data type, value range distribution and whether it is a primary key pair that is greater than the preset threshold as the field information to be associated.

[0073] After the operating system integrates multiple data files into an initial report, the user can integrate the field information in the initial report according to their actual needs, thereby determining a new field information, namely indicator information. However, to generate an indicator information, the user needs to enter an SQL statement to generate the indicator information under the execution of the SQL statement. Based on the above situation, the user is required to have certain programming skills, which increases the use threshold of the operating system and makes it impossible for some users to further process the initial report. In order to lower the use threshold of the system and facilitate more users to use it, this application provides an implementation method for automatically generating SQL language, specifically: In step 130, the statistical caliber input by the user is converted into an SQL statement based on a preset dictionary library and dynamic splicing technology to generate indicator information based on the target field information included in the initial report.

[0074] In some embodiments, the preset dictionary library may be a function library including multiple processing functions. For example, the preset dictionary library may include a summation function, an average calculation function, or a total calculation function. For example, the preset dictionary library may also include a conditional filtering function.

[0075] In some embodiments, the statistical caliber may include the field information required to calculate the indicator information, as determined by the user in the initial report, and the function used to calculate the indicator information. For example, if the user wishes to obtain the indicator information "Monthly Accident Handling Rate of ** Branch" in the final report, the user may enter the statistical caliber SUM("Number of Accident Handling Details") WHERE("Handling Unit"), where the statistical caliber includes the SUM() function and the WHERE() conditional function in the preset dictionary library, as well as the two field information "Number of Accident Handling Details" and "Handling Unit".

[0076] In some implementations, the target field information is the field information selected by the user and required for calculating the indicator information to be generated.

[0077] The operating system converts the statistical caliber entered by the user into SQL statements based on the preset dictionary library and dynamic splicing technology to generate indicator information. The user does not need to program in the operating system. The user only needs to enter a relatively simple statistical caliber, and the operating system can generate SQL statements based on the statistical caliber and retrieve the corresponding data through the SQL statements to generate indicator information.

[0078] Furthermore, in some embodiments, the step of converting the statistical caliber input by the user into an SQL statement based on a preset dictionary library and dynamic splicing technology to generate indicator information based on the target field information included in the initial report may include the following steps: (1) In response to the user's selection of field information to be processed in the initial report and the function selected in the preset function library, the statistical scope is determined.

[0079] (2) Extract the field information to be processed in the initial report based on the statistical caliber and the preset metadata access interface.

[0080] (3) According to the statistical caliber, determine the execution procedure of the function in the preset dictionary library.

[0081] (4) Based on the dynamic splicing technology, the field information to be processed will be extracted and the execution program will generate SQL statements.

[0082] (5) Execute SQL statements to generate indicator information.

[0083] The operating system provides a standardized function library (i.e., a preset function library) that users can directly call when defining statistical calibers for indicator information, simplifying the configuration of statistical calibers. A custom syntax parser is used to decompose the statistical calibers entered by users into operators (SUM, WHERE), and dynamic splicing technology is used to map the user-selected field information and operators into SQL statements that the database can recognize. This eliminates the need for user input of SQL statements; the system generates SQL statements based on the simple statistical calibers entered by users, and then executes the SQL statements to generate indicator information.

[0084] In addition, after the system generates indicator information, users can also use the generated indicator information as an element in the statistical caliber, that is, the system also supports generating new indicator information through multiple indicator information. For example, if a user wants to create a new indicator information "** Branch Monthly Accident Handling Rate", the two indicator information "** Branch Monthly Accident Handling Volume" and "** Branch Monthly Accident Total Volume" are used as elements in the statistical caliber, and the division function is selected in the preset dictionary library. In other words, the statistical caliber includes "** Branch Monthly Accident Handling Volume", "** Branch Monthly Accident Total Volume" and the division function. The system then uses dynamic splicing technology to generate SQL statements based on the statistical caliber. After executing the SQL statements, the new indicator information "** Branch Monthly Accident Handling Rate" can be generated.

[0085] In step 140 , a final report is generated based on the field information and indicator information selected by the user from the field information included in the initial report.

[0086] The operating system responds to the user's selection of the field information required for the indicator information in the initial report as the basis to generate indicator information based on this basic field information. In addition, the system also supports the user to select the field information included in the initial report as dimension information and present it in the final report.

[0087] For example, the initial report includes field information A, field information B, field information C, and field information D. In response to the user specifying field information C and field information D as dimension information in the initial report, the operating system responds. The user enters the statistical caliber SUM (field information A + field information B). Dynamic splicing technology is used to convert the statistical caliber into SQL, which is then executed to generate indicator information F. The final report generated by the operating system includes field information C, field information D, and indicator information F.

[0088] The operating system provided in this application only requires the user to select the corresponding object library (i.e., determine the target data file) on the front end. The operating system then processes the target data file accordingly. The user can then set and select different dimensional information and indicator information for business processing. After determining the dimensional information and indicator information used by the user to generate the final report, the operating system uses a declarative configuration engine to generate a visual form for the dimensional information and indicator information used to generate the final report through JSON Schema, thereby visualizing the final report. In addition, the operating system can also use echarts to display a variety of final report types in a categorized manner.

[0089] In order to further improve the security of the accessed data source, in some embodiments, the database retrieval method based on table semantic annotations may further include the following steps: (1) Create a user information table and a permission information table based on the input user information and the permission information corresponding to each user in the user information; (2) Associate the user information table and the permission information table to generate an associated information table; (3) Determine the data files that the current user can view through the associated information table; (4) In response to the current user selecting a data file from the viewable data files, a target data file is determined.

[0090] The operating system stores the basic information of multiple users in the user information table, and then stores the permission information corresponding to each user in the user information table. Through the field information in the user information table and the user information table, the user information table and the user information table are associated to generate an associated information table.

[0091] After logging into the operating system, the user can determine the current user's permissions in the associated information table based on the login information (for example, ID number). Then, based on the current user's permissions, the interface displays the data files that the current user can view in the connected data source, and the user then selects the target data file from the displayed data files.

[0092] Specifically, the operating system uses PostgreSQL JSONB to store user information, permission information, and association information tables. After a user logs in, it checks RBAC to verify whether the user role has the required permissions. If RBAC passes, the ABAC policy is evaluated to enforce attribute-based constraints. Only when both pass are true is an "allow" policy returned. Based on ouath2+springSecurity, user information, role information, permissions, and ABAC policy decision information are cached in Redis using the password mode and authorization code mode. A TTL is set to remind the user to log in again after a login timeout.

[0093] See also Figure 2 , Figure 2 A structural diagram of a database retrieval device based on table semantic annotation provided in an embodiment of the present application is shown. The report generation 200 includes: a determination module 210, an establishment module 220, an execution module 230 and a generation module 240. Specifically: the determination module 210 is used to determine the target data file in response to the user's operation of selecting a data file in the accessed data source.

[0094] The establishing module 220 is used to associate multiple data files included in the target data file according to a preset metadata access interface and a preset table association method, so as to integrate the multiple data files into establishing an initial report.

[0095] The execution module 230 is used to convert the statistical caliber input by the user into an SQL statement based on the preset dictionary library, dynamic splicing technology and preset splicing method, so as to generate indicator information according to the target field information included in the initial report.

[0096] The generating module 240 is used to generate a final report based on the field information and indicator information selected by the user from the field information included in the initial report.

[0097] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described devices and modules can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0098] In several embodiments provided in this application, the coupling or direct coupling or communication connection between the modules shown or discussed can be an indirect coupling or communication connection through some interfaces, devices or modules, which can be electrical, mechanical or other forms.

[0099] In addition, the functional modules in the various embodiments of the present application may be integrated into a processing module, or each module may exist physically separately, or two or more modules may be integrated into a single module. The above-mentioned integrated modules may be implemented in the form of hardware or software functional modules.

[0100] See also Figure 3 , Figure 3A structural diagram of a database retrieval device based on table semantic annotations provided in an embodiment of the present application. The database retrieval device 300 based on table semantic annotations in the present application may include one or more of the following components: a processor 310, a memory 320, and one or more applications, wherein the one or more applications may be stored in the memory 320 and configured to be executed by one or more processors 310, and the one or more programs are configured to execute the database retrieval method based on table semantic annotations as described in the aforementioned method embodiment.

[0101] The processor 310 may include one or more processing cores. The processor 310 utilizes various interfaces and circuits to connect the various components within the database retrieval device 300 based on table semantic annotations. By running or executing instructions, programs, code sets, or instruction sets stored in the memory 320, and accessing data stored in the memory 320, the processor 310 performs various functions and processes data within the database retrieval device 300 based on table semantic annotations. Optionally, the processor 310 may be implemented using at least one of the following hardware forms: a digital signal processing (DSP), a field-programmable gate array (FPGA), or a programmable logic array (PLA). The processor 310 may integrate one or a combination of a central processing unit (CPU), a graphics processing unit (GPU), and a modem. The CPU primarily processes the operating system, user interface, and application programs; the GPU is responsible for rendering and drawing display content; and the modem handles wireless communications. It is understood that the modem may also be implemented independently of the processor 310 via a separate communication chip.

[0102] The memory 320 may include random access memory (RAM) or read-only memory (ROM). The memory 320 may be used to store instructions, programs, code, code sets, or instruction sets. The memory 320 may include a program storage area and a data storage area. The program storage area may store instructions for implementing an operating system, instructions for implementing at least one function, and instructions for implementing the various method embodiments described below. The data storage area may also store data created during use by the database retrieval device 300 based on table semantic annotations.

[0103] See also Figure 4 , Figure 4A structural diagram of a computer-readable storage medium provided in an embodiment of the present application is shown, in which program code is stored. The program code can be called by a processor to execute the database retrieval method based on table semantic annotations described in the above method embodiment.

[0104] Computer-readable storage medium 400 may be an electronic memory such as flash memory, EEPROM (Electrically Erasable Programmable Read-Only Memory), EPROM, a hard disk, or ROM. Alternatively, computer-readable storage medium 400 may include non-transitory computer-readable storage medium. Computer-readable storage medium 400 has storage space for program code 410 for executing any of the method steps described above. This program code can be read from or written to one or more computer program devices. Program code 410 may be compressed, for example, in a suitable format.

[0105] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A database retrieval method based on table semantic annotation, characterized in that: The method comprises: In response to a user selecting a data file from an accessed data source, determining a target data file; Associating the plurality of data files included in the target data file according to a preset metadata access interface and a preset table association method, so as to integrate the plurality of data files into an initial report; Based on a preset dictionary library and dynamic splicing technology, the statistical caliber input by the user is converted into an SQL statement to generate indicator information based on the target field information included in the initial report; A final report is generated based on the field information selected by the user from the field information included in the initial report and the indicator information.

2. The database retrieval method based on table semantic annotation according to claim 1, characterized in that: The method further comprises: Determining the type of the data source according to the identification feature of the data source; Based on the access engine corresponding to the type, the access descriptor corresponding to the data source is extracted to access the data source.

3. The database retrieval method based on table semantic annotation according to claim 2, characterized in that: The type includes at least one of an Excel file, a CSV file, and a database; The extracting, based on the access engine corresponding to the type, the access descriptor corresponding to the data source, includes: If the type is the Excel file, then the engine is indeed the Apache POI parser; Inputting the data source into the Apache POI parser to obtain the file structure and metadata corresponding to the Excel file; Alternatively, if the type is the CSV file, determining that the engine is an OpenCSV parser; Input the data source into the OpenCSV parser to obtain the file structure and metadata of the CSV file; Alternatively, if the type is the database, the engine is determined to be a driver corresponding to the database.

4. The database retrieval method based on table semantic annotation according to claim 1, characterized in that: The step of associating the plurality of data files included in the target data file according to the preset metadata access interface and the preset table association method to integrate the plurality of data files into an initial report includes: Extracting field information of the plurality of data files according to the preset metadata access interface, and presenting the field information having business attributes; In response to the user selecting the field information to be associated from the field information with business attributes, the multiple data files are associated through the field information to be associated according to the preset table association method to create the initial report.

5. The database retrieval method based on table semantic annotation according to claim 4 is characterized in that: The extracting, according to the preset metadata access interface, field information of the plurality of data files and presenting the field information having business attributes includes: According to a preset depth-first search algorithm, traverse the field information included in the multiple data files; the field information includes the file names and specific field information corresponding to the multiple data files respectively; Create multiple root nodes according to the file name; Creating multiple child nodes according to specific field information in the same data file as the target file name; the target file name is any file name in the file name; Obtaining display information in JSON format according to the multiple root nodes and the multiple child nodes; According to the display information in the JSON format, the field information with business attributes is presented.

6. The database retrieval method based on table semantic annotation according to claim 1, characterized in that: The method converts the statistical caliber input by the user into an SQL statement based on the preset dictionary library and dynamic splicing technology to generate indicator information based on the target field information included in the initial report, including: In response to the user selecting field information to be processed in the initial report and a function selected from a preset function library, determining the statistical caliber; Extracting the field information to be processed from the initial report according to the statistical caliber and the preset metadata access interface; Determining an execution program of the function in the preset dictionary library according to the statistical caliber; According to the dynamic splicing technology, the field information to be processed is extracted and the execution program is used to generate the SQL statement; Execute the SQL statement to generate the indicator information.

7. The database retrieval method based on table semantic annotation according to claim 1, characterized in that: The method further comprises: Establishing a user information table and a permission information table based on the input user information and the permission information corresponding to each user in the user information; Associating the user information table with the authority information table to generate an associated information table; Determining the data files that the current user can view through the associated information table; The step of determining the target data file in response to the user selecting a data file from the accessed data source includes: In response to an operation of the current user selecting a data file from the viewable data files, the target data file is determined.

8. The database retrieval method based on table semantic annotation according to claim 2, characterized in that: The identification feature includes at least one of an extension, a file signature, and a connector; The access descriptor includes at least one of a file structure, metadata, and a driver.

9. A database retrieval device based on table semantic annotation, characterized in that: The device comprises: A determination module, configured to determine a target data file in response to a user's operation of selecting a data file from an accessed data source; Establishing a module for associating a plurality of data files included in the target data file according to a preset metadata access interface and a preset table association method, so as to integrate the plurality of data files into an initial report; An execution module, configured to convert the statistical caliber input by the user into an SQL statement based on a preset dictionary library and dynamic splicing technology, so as to generate indicator information according to the target field information included in the initial report; The generating module is used to generate a final report based on the field information selected by the user from the field information included in the initial report and the indicator information.

10. A database retrieval device based on table semantic annotation, characterized in that: include: one or more processors; Memory; One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, and the one or more programs are configured to execute the database retrieval method based on table semantic annotation as described in any one of claims 1-8.

Images