Data visualization and query method for multiple data sources

Abstract

The invention relates to the technical field of data processing, in particular to a multi-data-source data visualization and query method. The invention discloses a data visualization method for multiple data sources. The method comprises the following steps: S1, maintaining the data sources; s2, uploading a file, and creating a file data set; s3, configuring a model, and creating a model data set; s4, creating and configuring an instrument board according to the file data set and the model data set; and S5, releasing the instrument board. Compared with the prior art, the method supports access and rapid expansion of various data sources, realizes 0-code construction of visual reports, realizes low-cost development, supports self-service analysis of users, flexibly expands report analysis requirements, is high in utilization rate, and meets service requirements.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a data visualization and query method for multiple data sources. Background Art

[0002] Many companies are currently facing digital transformation, and data visualization is the stepping stone to digital transformation. Enterprises should start with data visualization when they carry out digital transformation, because data visualization is the most intuitive function that can directly see the effectiveness of digital transformation.

[0003] As companies have many reporting needs, the workload of technical development reports is heavy, and R&D resources cannot be released, etc., data visualization platform software is still in great demand in the market. Secondly, there are a lot of manual reports in companies. Although offline manual reports can basically meet users' data analysis needs, offline manual reports still have limitations. For example, the data is not real-time, not dynamic, there is a lag, the data accuracy is low, and it is difficult to support decision-making analysis; the scope of report sharing is limited; the cost of report adjustment is high, and authority control is difficult to unify and control, etc. Summary of the invention

[0004] In order to overcome the deficiencies of the prior art, the present invention provides a data visualization and query method.

[0005] To achieve the above purpose, a data visualization method for multiple data sources is designed, which includes the following steps: S1, maintain data source; S2, upload files and create file datasets; S3, configure the model and create the model dataset; S4, creating and configuring a dashboard based on the file dataset and the model dataset; S5, dashboard released.

[0006] The specific method of step S1 is as follows: S11, upload the driver plug-in that supports hot swapping to the object storage or manually install the plug-in; S12, maintaining data source information; S13, write the data source information into the redis cache.

[0007] The step S11 comprises the following steps: S111, determine whether the data source cache already exists, if it does, proceed to step S115, if it does not exist, read the data source configuration redis cache; S112, determine whether the local driver plug-in exists, if it does, proceed to step S113, if it does not exist, automatically download the plug-in driver to the local, and proceed to step S113; S113, dynamic class loading, driver registration; S114, initialize the data source connection pool and cache it into the VM memory; S115, obtaining the driver DriverSession.

[0008] The specific method of step S3 is as follows: S31, selecting a data source; S32, determining whether the data source is a Trino or Preosto data source, if yes, selecting catalog and proceeding to the next step, if no, selecting schema and proceeding to the next step; S33, select table, view, and custom SQL; S34, maintain the join relationship; S35, assemble and parse SQL; S36, check the field; S37, configure field attributes; S38, determine whether to configure the physical model. If no physical model needs to be configured, proceed to step S39; if the physical model needs to be configured, select or create a target physical table, configure mapping, generate a scheduling task, and proceed to step S39. S39, save and publish the model.

[0009] In the configuration field attributes of step S37, the dimensions or indicators of the configuration fields are configured.

[0010] The dashboard has the functions of menu mounting, authorized sharing, push, and data export.

[0011] To achieve the above purpose, a data visualization query method for multiple data sources is designed, which includes the following steps: A1: The front-end user sends a data query request and enters query information; A2, input parameters into the Map according to the query information; A3, Dynamic replacement of SQL drill-down conditions; A4, obtain the Driver Session; A5, Driver Session executes SQL; A6, parse SQL through Druid and obtain the syntax tree object; A7, obtain metadata access policy; A8, determining whether the front-end user has metadata access rights, if so, proceeding to step A9, if not, returning a query failure; A9, obtains the row-level data filtering permission value; A10, refactor the permission SQL; A11, obtain cache key; A12, determine whether the redis cache key exists. If it does, get the redis cache and go to step A15. If it does not exist, go to step A13. A13, determine whether the file cache exists. If so, obtain the file cache and proceed to step A15. If not, search in the database and return the result set, then proceed to step A14. A14, determining whether the result set is too large to be written into the file cache, if necessary, writing into the file cache and proceeding to step A15, if not, writing into the redis cache and proceeding to step A15; A15, perform post-processing desensitization on the query results; A16 formats the query result according to the configuration and returns the query result.

[0012] The specific method of step A2 includes the following steps: A21, splicing the pre-filter conditions and appending the corresponding parameters to the Map; A22, splicing the aggregated post-filter conditions and appending the corresponding parameters to the Map; A23, appending custom parameters to the Map; A24, appending the relevant permission context parameters of the front-end user to the Map.

[0013] In step A11, the query parameter is converted into cachekey through the Myabtis interceptor, and the specific method is as follows: A111, determine whether the redis cache corresponding to cachekey exists. If so, obtain the cache data from redis and proceed to step A116. If not, proceed to step A112. A112, determine whether the file cache corresponding to cachekey exists. If so, obtain data from the file cache and proceed to step A116. If not, proceed to step A113. A113, query data from the database through the driver plug-in; A114, determining whether to enable the cache, if the cache needs to be enabled, proceeding to step A115, if the cache does not need to be enabled, proceeding to step A116; A115, determines whether the response size is too large. If so, writes it to the file cache through the Mapped Byte Buffer and sets an expiration time. If not, writes it to the Redis cache and sets an expiration time. A116, return response.

[0014] Compared with the prior art, the present invention supports the access and rapid expansion of multiple data sources, realizes zero-code construction of visual reports, achieves low-cost development, supports user self-service analysis, flexibly expands report analysis needs, has high utilization rate, and meets business needs. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 This is a flow chart of Embodiment 1 of the present invention.

[0016] Figure 2 This is a flow chart of step S1 in the first embodiment of the present invention.

[0017] Figure 3 This is a flowchart of step S11 in the first embodiment of the present invention.

[0018] Figure 4 This is a flowchart of step S3 in the first embodiment of the present invention.

[0019] Figure 5 This is a flow chart of Embodiment 2 of the present invention.

[0020] Figure 6 This is a flowchart of step A11 in the second embodiment of the present invention.

[0021] Figure 7 It is a schematic diagram of the modules of the present invention. DETAILED DESCRIPTION

[0022] The present invention will be further described below with reference to the accompanying drawings. Embodiment 1

[0023] This embodiment is a data visualization method for multiple data sources, including the following steps: S1, maintain data source; S2, upload files and create file datasets; S3, configure the model and create the model dataset; S4, creating and configuring a dashboard based on the file dataset and the model dataset; S5, dashboard released.

[0024] The specific method of step S1 is as follows: S11, uploading a driver plug-in that supports hot plugging to the object storage or manually installing the plug-in; S12, maintaining data source information; S13, writing the data source information into the redis cache.

[0025] Step S11 includes the following steps: S111, determine whether the data source cache already exists, if so, proceed to step S115, if not, read the data source configuration redis cache; S112, determine whether the local driver plug-in exists, if so, proceed to step S113, if not, automatically download the plug-in driver to the local, and enter step S113; S113, dynamic class loading, register the driver; S114, initialize the data source connection pool and cache it to the vm memory; S115, obtain the driver DriverSession.

[0026] The specific method of step S3 is as follows: S31, select the data source; S32, determine whether the data source is a Trino or Preosto data source, if yes, select catalog and proceed to the next step, if not, select schema and proceed to the next step; S33, select table, view, custom SQL; S34, maintain join association; S35, assemble and parse SQL; S36, check fields; S37, configure field properties; S38, determine whether to configure the physical model, if no physical model needs to be configured, proceed to step S39; if the physical model needs to be configured, select or create the target physical table, configure the mapping, generate the scheduling task, and proceed to step S39, S39, save and publish the model.

[0027] In the configuration field attributes of step S37, the dimension or indicator of the configuration field is configured.

[0028] The dashboard has the functions of menu mounting, authorized sharing, push, and data export.

[0029] The data visualization method of this embodiment realizes the docking of multiple data sources, and pluggable management supports the docking of multiple data sources, adapts to common data sources on the market, and uses pluggable data source drive management. The data source is plug-and-play, and can be quickly iterated to meet the user's customized data source needs. It completes the docking of multiple data set types and supports multiple data sets such as data models and file data sets. It completes the development of multiple chart components, supports users to quickly build chart components by dragging and dropping indicator dimensions, and creates data reports with zero code. Embodiment 2

[0030] This embodiment only describes the differences from the first embodiment, and the similarities are not described again.

[0031] This embodiment is a method for further querying based on the data visualization method established in the first embodiment, and includes the following steps: To achieve the above purpose, a data visualization query method for multiple data sources is designed, which includes the following steps: A1, the front-end user sends a data query request and enters the query information; A2, according to the query information, inputs parameters into the Map; A3, SQL drill-down conditions are dynamically replaced; A4, Driver Session is obtained; A5, Driver Session executes SQL; A6, parses SQL through Druid to obtain syntax tree object; A7, obtains metadata access policy; A8, determines whether the front-end user has metadata access permission. If yes, proceed to step A9. If no, return query failure; A9, obtain row-level data filtering permission value; A10, reconstructs permission SQL; A11, obtains cache cachekey; A12, determines whether redis cache key exists. If yes, obtains redis cache and proceeds to step A15. If no, proceeds to step A13. A13, determines whether file cache exists. If yes, obtains file cache and proceeds to step A15. If no, enters database to check and returns result set and proceeds to step A14. A14, determines whether the result set is too large to be written to file cache. If yes, writes to file cache and proceeds to step A15. If no, writes to redis cache and proceeds to step A15. A15, performs post-massaging on query results; A16, formats query results according to configuration and returns query results.

[0032] The specific method of step A2 includes the following steps: A21, splicing the pre-filter conditions and appending the corresponding parameters to the Map; A22, splicing the aggregated post-filter conditions and appending the corresponding parameters to the Map; A23, appending custom parameters to the Map; A24, appending the relevant permission context parameters of the front-end user to the Map.

[0033] In step A11, the query parameter is converted into cachekey through the Myabtis interceptor. The specific method is as follows: A111, determine whether the redis cache corresponding to the cachekey exists. If so, obtain the cache data from redis and enter step A116. If not, proceed to step A112; A112, determine whether the file cache corresponding to the cachekey exists. If so, obtain data from the file cache and enter step A116. If not, proceed to step A113; A113, query data from the database through the driver plug-in; A114, determine whether to enable the cache. If the cache needs to be enabled, proceed to step A115. If the cache does not need to be enabled, proceed to step A116; A115, determine whether the response size is too large. If so, write it to the file cache through Mapped Byte Buffer and set an expiration time. If not, write it to the redis cache and set an expiration time; A116, return the response.

[0034] The present invention can realize online expansion of data sources without downtime, quickly realize the expansion of data source types, and visually configure data source parameters, which is convenient for users to control and tune the connection pool. Visually develop and build dashboards quickly. Model construction is realized by dragging and dropping to lower the user's technical threshold, realize the businessization of model construction, and physical model, that is, physical table construction, and can realize cross-data source federated model construction corresponding to different catalogs through trino data sources, quickly break through data barriers, solve chimney problems, and convert configurations into complex SQL through builder mode. It can realize 0 code to build visual reports or large-screen reports, support a variety of Echart graphic components, and extremely flexible parameter configuration, which can realize the rapid construction of beautiful reports. It has high-performance query capabilities, supports physical models to accelerate complex queries, and realizes data cache acceleration at the physical layer by updating complex query results to physical models at regular intervals. The dashboard has built-in multi-level cache, which can be refreshed asynchronously, and the cache rules can be flexibly configured. It also realizes cache reading and writing after user-level data security isolation, and realizes high-performance data query. For specific processes, please refer to it.

Claims

1. A data visualization method for multiple data sources, characterized by: The steps include: S1, maintain data source; S2, upload files and create file datasets; S3, configure the model and create the model dataset; S4, creating and configuring a dashboard based on the file dataset and the model dataset; S5, dashboard released.

2. The data visualization method for multiple data sources according to claim 1, characterized in that: The specific method of step S1 is as follows: S11, upload the driver plug-in that supports hot swapping to the object storage or manually install the plug-in; S12, maintaining data source information; S13, write the data source information into the redis cache.

3. The data visualization method for multiple data sources according to claim 1, characterized in that: The step S11 comprises the following steps: S111, determine whether the data source cache already exists, if it does, proceed to step S115, if it does not exist, read the data source configuration redis cache; S112, determine whether the local driver plug-in exists, if it does, proceed to step S113, if it does not exist, automatically download the plug-in driver to the local, and proceed to step S113; S113, dynamic class loading, driver registration; S114, initialize the data source connection pool and cache it into the VM memory; S115, obtaining the driver DriverSession.

4. The data visualization method for multiple data sources according to claim 1, characterized in that: The specific method of step S3 is as follows: S31, selecting a data source; S32, determining whether the data source is a Trino or Preosto data source, if yes, selecting catalog and proceeding to the next step, if no, selecting schema and proceeding to the next step; S33, select table, view, and custom SQL; S34, maintain the join relationship; S35, assemble and parse SQL; S36, check the field; S37, configure field attributes; S38, determine whether to configure the physical model. If no physical model needs to be configured, proceed to step S39; if the physical model needs to be configured, select or create a target physical table, configure mapping, generate a scheduling task, and proceed to step S39. S39, save and publish the model.

5. The data visualization method for multiple data sources according to claim 4, characterized in that: In the configuration field attributes of step S37, the dimensions or indicators of the configuration fields are configured.

6. The data visualization method for multiple data sources according to claim 1, characterized in that: The dashboard has the functions of menu mounting, authorized sharing, push, and data export.

7. A method for querying data visualization from multiple data sources according to any one of claims 1 to 6, characterized in that: The steps include: A1: The front-end user sends a data query request and enters the query information; A2, input parameters into the Map according to the query information; A3, Dynamic replacement of SQL drill-down conditions; A4, obtain the Driver Session; A5, Driver Session executes SQL; A6, parse SQL through Druid and obtain the syntax tree object; A7, obtain metadata access policy; A8, determining whether the front-end user has metadata access rights, if so, proceeding to step A9, if not, returning a query failure; A9, obtains the row-level data filtering permission value; A10, refactor the permission SQL; A11, obtain cache key; A12, determine whether the redis cache key exists. If it does, get the redis cache and go to step A15. If it does not exist, go to step A13. A13, determine whether the file cache exists. If so, obtain the file cache and proceed to step A15. If not, search in the database and return the result set, then proceed to step A14. A14, determining whether the result set is too large to be written into the file cache, if necessary, writing into the file cache and proceeding to step A15, if not, writing into the redis cache and proceeding to step A15; A15, perform post-processing desensitization on the query results; A16 formats the query result according to the configuration and returns the query result.

8. The method for querying data visualization from multiple data sources according to claim 7, characterized in that: The specific method of step A2 includes the following steps: A21, splicing the pre-filter conditions and appending the corresponding parameters to the Map; A22, splicing the aggregated post-filter conditions and appending the corresponding parameters to the Map; A23, appending custom parameters to the Map; A24, appending the relevant permission context parameters of the front-end user to the Map.

9. The method for querying data visualization from multiple data sources according to claim 7, characterized in that: In step A11, the query parameter is converted into cachekey through the Myabtis interceptor, and the specific method is as follows: A111, determine whether the redis cache corresponding to cachekey exists. If so, obtain the cache data from redis and proceed to step A116. If not, proceed to step A112. A112, determine whether the file cache corresponding to cachekey exists. If so, obtain data from the file cache and proceed to step A116. If not, proceed to step A113. A113, query data from the database through the driver plug-in; A114, determining whether to enable the cache, if the cache needs to be enabled, proceeding to step A115, if the cache does not need to be enabled, proceeding to step A116; A115, determines whether the response size is too large. If so, writes it to the file cache through the Mapped Byte Buffer and sets an expiration time. If not, writes it to the Redis cache and sets an expiration time. A116, return response.

Images