Data view generation method and device, computer device, and readable storage medium

Abstract

Embodiments of the present application disclose a data view generation method and device, computer equipment and a readable storage medium. The method comprises: obtaining an index corresponding to a data query request; generating at least one sql statement according to the index; generating at least one sqlNode group according to the sql statement; combining sqlNode groups that meet preset merging conditions to generate a single sqlNode tree; and generating a corresponding sql data view according to the single sqlNode tree. By generating a data view, the association relationship of data query is simplified, the query amount is reduced, data islands are broken, the efficiency of data analysis and loading is improved, and the problems of long data query chain and low data query efficiency caused by low index query granularity are solved. On this basis, the views and index data required by the user to browse can be quickly displayed, so that the user can make decisions faster.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a data view generation method, apparatus, computer device, and readable storage medium. Background Technology

[0002] Current big data indicator platforms suffer from numerous and diverse indicators. Furthermore, the disconnect between data analysis platforms and metadata platforms creates data silos, hindering collaborative data analysis. Limited by the query rate per second of the data analysis platform, users experience long query chains and low query efficiency when querying data on big data indicator platforms if the granularity of the indicator query is too low, resulting in a poor user experience.

[0003] Currently, no effective solution has been proposed to address the problems of excessively long data query chains and low data query efficiency caused by the diverse forms of indicators and the fragmentation between data analysis platforms and metadata platforms in the aforementioned technologies, where the granularity of indicator queries is too low. Summary of the Invention

[0004] In view of this, the purpose of the present invention is to overcome the shortcomings of the prior art and provide a data view generation method, apparatus, computer equipment and readable storage medium, which aims to solve the problem that when users use big data indicator platforms to query data, the data query chain is too long and the data query efficiency is low if the indicator query granularity is too low.

[0005] This invention provides the following technical solution:

[0006] In a first aspect, this disclosure provides a data view generation method, the method comprising:

[0007] Retrieve the metrics corresponding to the data query request;

[0008] Generate at least one SQL statement based on the aforementioned indicators;

[0009] Generate at least one sqlNode group based on the SQL statement;

[0010] Merge sqlNodes that meet the preset merging conditions to generate a single sqlNode tree;

[0011] Generate the corresponding SQL data view based on the single sqlNode tree.

[0012] Furthermore, before merging the sqlNodes that meet the preset merging conditions to generate a single sqlNode tree, the process also includes:

[0013] Retrieve preset indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata;

[0014] The preset merging conditions are generated based on the indicator characteristics, the attribute relationships, the snowflake model relationships, the query conditions, and the user metadata.

[0015] Further, generating the corresponding SQL data view based on the single sqlNode tree includes:

[0016] Using a data analysis platform, the single sqlNode tree is converted to a different dialect to generate a corresponding SQL data view.

[0017] Furthermore, the method also includes:

[0018] Lineage analysis is performed on the aforementioned indicators to generate an sqlNode tree;

[0019] The sqlNode tree is then merged by metrics and cross-referenced by conditions to generate the corresponding SQL query view.

[0020] Furthermore, the metrics include a fact table, dimension tables, and measures. The step of performing lineage analysis on the metrics to generate a sqlNode tree includes:

[0021] Generate personalized configurations for users based on the query conditions of the aforementioned indicators;

[0022] Obtain preset control permissions;

[0023] Based on the preset construction model, the fact table, dimension table, measure, control permissions and user personalization configuration of each indicator are analyzed to generate the corresponding sqlNode tree.

[0024] Furthermore, the preset construction model is a snowflake model.

[0025] Secondly, this disclosure provides a data view generation apparatus, the apparatus comprising:

[0026] The first acquisition module is used to acquire the metrics corresponding to the data query request;

[0027] The first generation module is used to generate at least one SQL statement based on the indicators;

[0028] The second generation module is used to generate at least one sqlNode group based on the SQL statement;

[0029] The third generation module is used to combine sqlNodes that meet the preset merging conditions to generate a single sqlNode tree.

[0030] The fourth generation module is used to generate the corresponding SQL data view based on the single sqlNode tree.

[0031] Furthermore, the device also includes:

[0032] The second acquisition module is used to acquire preset indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata.

[0033] The fifth generation module is used to generate the preset merging conditions based on the indicator characteristics, the attribute relationships, the snowflake model relationships, the query conditions, and the user metadata.

[0034] Thirdly, this disclosure provides a computer device including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the data view generation method described in the first aspect.

[0035] Fourthly, this disclosure provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the data view generation method described in the first aspect.

[0036] The embodiments of this application have the following advantages:

[0037] The data view generation method provided in this application involves: acquiring the metrics corresponding to a data query request; generating at least one SQL statement based on the metrics; generating at least one SQLNode group based on the SQL statement; merging SQLNode groups that meet preset merging conditions to generate a single SQLNode tree; and generating a corresponding SQL data view based on the single SQLNode tree. By generating a data view, the association relationships in data queries are simplified, the query volume is reduced, data silos are broken down, and the efficiency of data analysis and loading is improved. This solves the problems of excessively long data query chains and low data query efficiency caused by excessively low granularity of metric queries. Based on this, the view and metric data that the user needs to browse can be quickly displayed, enabling the user to make decisions more quickly.

[0038] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, preferred embodiments are described below in detail with reference to the accompanying drawings. Attached Figure Description

[0039] To more clearly illustrate the technical solution of the present invention, the accompanying drawings used in the embodiments will be briefly described below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope of protection of the present invention. In the various drawings, similar components are numbered similarly.

[0040] Figure 1 A flowchart of a data view generation method provided in an embodiment of this application is shown;

[0041] Figure 2 This paper shows a schematic diagram of the structure of a data view generation device provided in an embodiment of this application;

[0042] Figure 3 A schematic diagram of the hardware architecture of a computer device provided in an embodiment of this application is shown. Detailed Implementation

[0043] Embodiments of the present invention are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and should not be construed as limiting the present invention.

[0044] It should be noted that when an element is said to be "fixed" to another element, it can be directly on the other element or there may be an intervening element. When an element is said to be "connected" to another element, it can be directly connected to the other element or there may be an intervening element. Conversely, when an element is said to be "directly" on another element, there is no intervening element. The terms "vertical," "horizontal," "left," "right," and similar expressions used in this document are for illustrative purposes only.

[0045] In this invention, unless otherwise explicitly specified and limited, the terms "installation," "connection," "linking," and "fixing," etc., should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral part; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; they can refer to the internal communication of two components or the interaction between two components. Those skilled in the art can understand the specific meaning of the above terms in this invention according to the specific circumstances.

[0046] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of this invention, "a plurality of" means two or more, unless otherwise explicitly specified.

[0047] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein in the template description is for the purpose of describing particular embodiments only and is not intended to limit the invention. The term "and / or" as used herein includes any and all combinations of one or more of the associated listed items.

[0048] Example 1

[0049] like Figure 1 The diagram shown is a flowchart of a data view generation method according to an embodiment of this application. The data view generation method provided in this embodiment includes the following steps:

[0050] Step 110: Obtain the metrics corresponding to the data query request.

[0051] Specifically, when users use computer devices to query data, the computer devices generate indicators corresponding to the data query request. By understanding and analyzing the user's query data, we can identify the content that needs attention in different areas, such as the content that each business line should focus on for each query data. For example, for financial businesses, the content of interest includes, but is not limited to, investment amount and savings amount. These content of interest can be used as indicators. For an indicator, it can be described by indicator name, indicator type, indicator data type, indicator unit, indicator person in charge, indicator security information, etc.

[0052] It is understood that the indicators include atomic indicators, derived indicators, and supplementary indicators. Atomic indicators are the smallest granularity indicators and cannot be further subdivided, such as total order amount. Derived indicators are composed of atomic indicators, time periods, and modifiers, such as the total order amount of a certain wealth management product from a certain branch in the past week. Supplementary indicators are formed by combining at least one derived indicator through various logical operators, such as profit. This application embodiment uses atomic indicators as an example for data processing.

[0053] By obtaining the metrics corresponding to the data query request, it is easier to generate the corresponding SQL statement based on the metrics, and then generate the SQL data view. This simplifies the data query relationship, reduces the query volume, and improves the efficiency of data analysis and loading.

[0054] Step 120: Generate at least one SQL statement based on the indicators.

[0055] Furthermore, each metric corresponds to at least one SQL statement. SQL (Structured Query Language) is a database query and programming language used to access, query, update, and manage relational database systems. The SQL statement is a standard SQL statement conforming to standard SQL syntax, excluding special characters such as comments, spaces, terminators, and newlines.

[0056] Step 130: Generate at least one sqlNode group based on the SQL statement.

[0057] It is understandable that each SQL statement corresponds to a SQLNode. Here, SQLNode refers to the Node used to store SQL statements. Multiple SQL statements and their corresponding SQLNodes can form a SQLNode group, and multiple query metrics will generate multiple SQLNode groups.

[0058] By generating at least one sqlNode group based on the SQL statement, and then generating an SQL data view, the relationship between data queries is simplified, the number of queries is reduced, and the efficiency of data analysis and loading is improved.

[0059] Step 140: Combine the sqlNodes that meet the preset merging conditions to generate a single sqlNode tree.

[0060] Specifically, sqlNode groups are merged according to preset merging conditions to generate a single sqlNode tree. The single sqlNode tree is displayed in a tree-like form, and each node on the single sqlNode tree represents the SQL statement corresponding to the indicator. A clear description of the SQL statement can be established based on the single sqlNode tree, which is very beneficial for subsequent modifications and transformations.

[0061] By merging the sqlNode groups, a single sqlNode tree is generated, which in turn generates an SQL data view. This simplifies the relationships in data queries, reduces the number of queries, breaks down data silos, and improves the efficiency of data analysis and loading.

[0062] Step 150: Generate the corresponding SQL data view based on the single sqlNode tree.

[0063] Furthermore, using a data analysis platform, the merged single sqlNode tree is dialect-converted to generate the corresponding SQL data view. In a specific embodiment, the data analysis platform adopts an OLAP (Online Analytical Processing) system. It is understood that the specific data analysis platform used can be set according to actual needs, and this application embodiment does not limit it in this regard.

[0064] Understandably, the SQL language of different types of databases has different forms. Dialect conversion can transform all SQL statements on a single SQLNode tree into the dialect form of a specific database, achieving a unified SQL statement form. This generates a unified SQL data view, allowing users to query indicators at various granular levels using a complete view mode. This simplifies the data query relationships, reduces the query volume, breaks down data silos, improves the efficiency of data analysis and loading, and solves the problem of excessively long data query chains.

[0065] In one optional implementation, before merging the sqlNodes that meet the preset merging conditions to generate a single sqlNode tree, the method further includes:

[0066] Step 141: Obtain the preset indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata.

[0067] Specifically, indicator characteristics refer to the indicator's business line, the indicator's derivation conditions, the indicator's derivative conditions, the indicator's type, and the indicator's calculation type; attribute relationships refer to the type relationships between the various dimensions associated with the indicator; snowflake model relationships refer to the key relationships established between the various dimensions associated with the indicator through the snowflake model; query conditions refer to the specific requirements of users when querying data; and user metadata refers to the structured data extracted from information resources to describe user characteristics and information.

[0068] Step 142: Generate the preset merging conditions based on the indicator characteristics, the attribute relationships, the snowflake model relationships, the query conditions, and the user metadata.

[0069] Furthermore, based on the obtained indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata, preset merging conditions are generated. Then, based on the preset merging conditions, the sqlNodes that meet the preset merging conditions are combined and merged to generate a single sqlNode tree.

[0070] In one alternative implementation, the method may further include:

[0071] Step 160: Perform lineage analysis on the indicators to generate an sqlNode tree.

[0072] Specifically, the metrics include fact tables, dimension tables, and measures. Lineage analysis is performed on the metrics, i.e., generating personalized user configurations based on the query conditions of the metrics; obtaining preset control permissions; and analyzing the fact tables, dimension tables, measures, control permissions, and personalized user configurations of each metric based on a preset construction model to generate corresponding SQLNode trees. Here, a fact table refers to a fact data table in the database containing a large amount of data that can be statistically analyzed and recorded; a dimension table refers to the quantities used when analyzing the data; a dimension table contains the characteristics of the fact statistics and recorded data in the fact table, such as textual context related to events, used to describe events related to "who, what, where, when, how, and why"; a measure refers to a numerical, continuous field stored in the fact table; and control permissions refer to the different operational permissions that metrics have over the data.

[0073] It is understood that the preset construction model used in this application embodiment is the snowflake model. The snowflake model can minimize the amount of data storage and improve query performance by combining smaller dimension tables, and remove data redundancy. It should be noted that the specific construction model used can be set according to actual needs, and this application embodiment does not limit it in this regard.

[0074] Step 170: Merge the metrics and cross conditions of the sqlNode tree to generate the corresponding SQL query view.

[0075] Furthermore, the sqlNode tree comprises multiple nodes. Metrics merging and condition cross-validation are performed between the nodes of the sqlNode tree to generate corresponding SQL query views. Based on these SQL query views, the relationships in data queries can be simplified, the number of queries can be reduced, and the data loading speed can be significantly improved.

[0076] The embodiments of this application have the following advantages:

[0077] The data view generation method provided in this application involves: acquiring the metrics corresponding to a data query request; generating at least one SQL statement based on the metrics; generating at least one SQLNode group based on the SQL statement; merging SQLNode groups that meet preset merging conditions to generate a single SQLNode tree; and generating a corresponding SQL data view based on the single SQLNode tree. By generating a data view, the association relationships in data queries are simplified, the query volume is reduced, data silos are broken down, and the efficiency of data analysis and loading is improved. This solves the problems of excessively long data query chains and low data query efficiency caused by excessively low granularity of metric queries. Based on this, the view and metric data that the user needs to browse can be quickly displayed, enabling the user to make decisions more quickly.

[0078] Example 2

[0079] like Figure 2 The diagram shown is a structural schematic of a data view generation device 200 according to an embodiment of this application. The device includes:

[0080] The first acquisition module 210 is used to acquire the metrics corresponding to the data query request;

[0081] The first generation module 220 is used to generate at least one SQL statement based on the indicators;

[0082] The second generation module 230 is used to generate at least one sqlNode group based on the sql statement;

[0083] The third generation module 240 is used to combine sqlNodes that meet the preset merging conditions to generate a single sqlNode tree.

[0084] The fourth generation module 250 is used to generate a corresponding SQL data view based on the single sqlNode tree.

[0085] Optionally, the data view generation apparatus may further include:

[0086] The second acquisition module is used to acquire preset indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata.

[0087] The fifth generation module is used to generate the preset merging conditions based on the indicator characteristics, the attribute relationships, the snowflake model relationships, the query conditions, and the user metadata.

[0088] Optionally, the data view generation apparatus may further include:

[0089] The fourth generation submodule is used to utilize the data analysis platform to perform dialect conversion on the single sqlNode tree and generate the corresponding SQL data view.

[0090] Optionally, the data view generation apparatus may further include:

[0091] The sixth generation module is used to perform lineage analysis on the indicators and generate an sqlNode tree;

[0092] The seventh generation module is used to merge metrics and cross conditions in the sqlNode tree to generate the corresponding SQL query view.

[0093] Optionally, the data view generation apparatus may further include:

[0094] The eighth generation module is used to generate personalized user configurations based on the query conditions of the aforementioned indicators.

[0095] The third acquisition module is used to acquire preset control permissions;

[0096] The ninth generation module is used to analyze the fact table, dimension table, measure, control permissions and user personalization configuration of each indicator based on the preset construction model, and generate the corresponding sqlNode tree.

[0097] The embodiments of this application have the following advantages:

[0098] The data view generation apparatus provided in this application simplifies the relationships in data queries, reduces the number of queries, breaks down data silos, and improves the efficiency of data analysis and loading by generating data views. It solves the problems of excessively long data query chains and low data query efficiency caused by excessively low granularity of indicator queries. Based on this, it can quickly display the views and indicator data that users need to browse, enabling users to make decisions more quickly.

[0099] Example 3

[0100] Figure 3 A schematic diagram of the hardware architecture of the computer device provided in this application is shown. The computer device includes a memory and a processor. The memory stores a computer program. When the processor executes the computer program, it implements the steps of the data view generation method described in Embodiment 1.

[0101] In this embodiment, the computer device 300 is a device capable of automatically performing numerical calculations and / or information processing according to pre-set or stored instructions. For example, it can be a rack server, blade server, tower server, or cabinet server (including standalone servers or server clusters composed of multiple servers), etc. Figure 3As shown, the computer device 300 includes, but is not limited to, a memory 310, a processor 320, and a network interface 330 that can communicate and be linked to each other via a system bus. Wherein:

[0102] The memory 310 includes at least one type of computer-readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory 310 may be an internal storage module of the computer device 300, such as the hard disk or memory of the computer device 300. In other embodiments, the memory 310 may also be an external storage device of the computer device 300, such as a plug-in hard disk, smart media card (SMC), secure digital card (SD), flash card, etc. Of course, the memory 310 may include both the internal storage module and the external storage device of the computer device 300. In this embodiment, the memory 310 is typically used to store the operating system and various application software installed on the computer device 300, such as program code for video playback methods. In addition, the memory 310 can also be used to temporarily store various types of data that have been output or will be output.

[0103] In some embodiments, processor 320 may be a central processing unit (CPU), controller, microcontroller, microprocessor, or other data processing chip. This processor 320 is typically used to control the overall operation of computer device 300, such as performing control and processing related to data interaction or communication with computer device 300. In this embodiment, processor 320 is used to run program code stored in memory 310 or process data.

[0104] Network interface 330 may include a wireless network interface or a wired network interface, which is typically used to establish a communication link between computer device 300 and other computer devices. For example, network interface 330 is used to connect computer device 300 to an external terminal via a network, establishing a data transmission channel and communication link between computer device 300 and the external terminal. The network may be an intranet, the Internet, Global System for Mobile Communication (GSM), Wideband Code Division Multiple Access (WCDMA), 4G network, 5G network, Bluetooth, Wi-Fi, or other wireless or wired networks.

[0105] It should be pointed out that, Figure 3 Only a computer device with components 310-330 is shown; however, it should be understood that it is not required to implement all of the components shown, and more or fewer components may be implemented instead.

[0106] In this embodiment, the data view generation method stored in memory 310 can also be divided into one or more program modules and executed by at least one processor (processor 320 in this embodiment) to complete the present invention.

[0107] Example 4

[0108] This embodiment also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the data view generation method in this embodiment.

[0109] In this embodiment, the computer-readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the computer-readable storage medium can be an internal storage unit of a computer device, such as the hard disk or memory of the computer device. In other embodiments, the computer-readable storage medium can also be an external storage device of the computer device, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device. Of course, the computer-readable storage medium can also include both the internal storage unit and the external storage device of the computer device. In this embodiment, the computer-readable storage medium is typically used to store the operating system and various application software installed on the computer device. In addition, the computer-readable storage medium can also be used to temporarily store various types of data that have been output or will be output.

[0110] In all examples shown and described herein, any specific values ​​should be interpreted as merely exemplary and not as limitations; therefore, other examples of exemplary embodiments may have different values.

[0111] It should be noted that similar labels and letters in the following figures indicate similar items. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures.

[0112] The above-described embodiments are merely illustrative of several implementations of the present invention, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of the invention. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these modifications and improvements all fall within the scope of protection of the present invention.

Claims

1. A method for generating a data view, characterized in that, The method includes: Retrieve the metrics corresponding to the data query request; Generate at least one SQL statement based on the aforementioned indicators; Generate at least one sqlNode group based on the SQL statement; Merge sqlNodes that meet the preset merging conditions to generate a single sqlNode tree; Generate the corresponding SQL data view based on the single sqlNode tree; Before merging the sqlNodes that meet the preset merging conditions to generate a single sqlNode tree, the process also includes: Retrieve preset indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata; The preset merging conditions are generated based on the indicator characteristics, the attribute relationships, the snowflake model relationships, the query conditions, and the user metadata.

2. The data view generation method according to claim 1, characterized in that, The step of generating the corresponding SQL data view based on the single sqlNode tree includes: Using a data analysis platform, the single sqlNode tree is converted to a different dialect to generate a corresponding SQL data view.

3. The data view generation method according to claim 1, characterized in that, The method further includes: Lineage analysis is performed on the aforementioned indicators to generate an sqlNode tree; The sqlNode tree is then merged by metrics and cross-referenced by conditions to generate the corresponding SQL query view.

4. The data view generation method according to claim 3, characterized in that, The metrics include a fact table, dimension tables, and measures. The step of performing lineage analysis on the metrics to generate a SQLNode tree includes: Generate personalized configurations for users based on the query conditions of the aforementioned indicators; Obtain preset control permissions; Based on the preset construction model, the fact table, dimension table, measure, control permissions and user personalization configuration of each indicator are analyzed to generate the corresponding sqlNode tree.

5. The data view generation method according to claim 4, characterized in that, The preset construction model is a snowflake model.

6. A data view generation apparatus, characterized in that, The device includes: The first acquisition module is used to acquire the metrics corresponding to the data query request; The first generation module is used to generate at least one SQL statement based on the indicators; The second generation module is used to generate at least one sqlNode group based on the SQL statement; The third generation module is used to combine sqlNodes that meet the preset merging conditions to generate a single sqlNode tree. The fourth generation module is used to generate a corresponding SQL data view based on the single sqlNode tree; The second acquisition module is used to acquire preset indicator characteristics, attribute relationships, snowflake model relationships, query conditions, and user metadata. The fifth generation module is used to generate the preset merging conditions based on the indicator characteristics, the attribute relationships, the snowflake model relationships, the query conditions, and the user metadata.

7. A computer device, characterized in that, It includes a memory and a processor, the memory storing a computer program, and the processor executing the computer program to implement the steps of the data view generation method according to any one of claims 1-5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the steps of the data view generation method according to any one of claims 1-5.

Images