A system, method, apparatus, and medium for vehicle table association optimization

Abstract

The application provides a dimension table association optimization system, method, device and medium, the system comprises: a cache; a configurator configured to provide configuration information, the configuration information comprising external storage link information and cache information; an external data storage configured to store dimension table data; a cache initializer configured to pass data extraction logic to the external data storage for execution according to the external storage link information to obtain the dimension table data; a data analyzer configured to generate corresponding key-value pairs according to the obtained dimension table data; a cache updater configured to update or initialize the cache according to the key-value pairs; and a primary cache query configured to generate a to-be-queried key-value according to to-be-queried data, to obtain a matching key-value pair in the cache based on the to-be-queried key-value, and to complete the association of the dimension table data and the to-be-queried data. The application can ensure high throughput, low delay and fast completion of dimension table association, and can effectively improve the processing efficiency of dimension table association tasks.

Description

Technical Field

[0001] This invention relates to the field of big data processing, and specifically to a dimension table association optimization system, method, device, and medium. Background Technology

[0002] With the rapid development of information technology, the speed and volume of data generation are constantly increasing. High throughput, low latency, and fast data processing have become crucial. FLink, as the most popular real-time computing engine, is highly favored for its high throughput and low latency capabilities. The efficiency of dimension table joins during data processing directly affects the timeliness and accuracy of computation.

[0003] In the case of massive amounts of data, high-throughput and low-latency dimension table joins are the key to solving the problem. If the data volume of the dimension table is too large, it will cause memory overflow. Frequent interaction with external databases will lead to processing timeouts and join failures, which will greatly affect the speed and accuracy of data processing. Summary of the Invention

[0004] One objective of this invention is to provide a dimension table association optimization system to solve the problems of low efficiency and poor accuracy in the prior art. A second objective is to provide a dimension table association optimization method. A third objective is to provide a device for executing the dimension table association optimization method. A fourth objective is to provide a computer-readable storage medium for storing the dimension table association optimization method.

[0005] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0006] This application provides a dimension table association optimization system, comprising: a cache; a configurator for providing configuration information, the configuration information including external storage link information and cache information; an external data storage unit for storing dimension table data; a cache initializer for passing data extraction logic to the external data storage unit for execution according to the external storage link information to obtain the dimension table data; a data analyzer for generating corresponding key-value pairs based on the obtained dimension table data; a cache updater for updating or initializing the cache according to the key-value pairs; and a first-level cache queryer for generating a query key-value pair based on the query key-value pair, so as to obtain the matching key-value pair in the cache based on the query key-value pair, thereby completing the association between the dimension table data and the query data.

[0007] In one embodiment of this application, the system further includes a data update timer, used to initialize a timer according to the timed update configuration information provided by the configurator; when the execution time of the timer arrives, the cache initializer is called to retrieve dimension table data from the external data storage, so as to initialize or update the cache based on the dimension table data.

[0008] In one embodiment of this application, the external storage link information includes a database type, and the cache initializer determines the corresponding database configuration information based on the database type to complete the initialization of the external data storage based on the database configuration information.

[0009] In one embodiment of this application, the system further includes a second-level cache queryer, configured to determine the logic implementation of the key value to be queried from the external data storage based on the external storage link information when the first-level cache queryer does not find a matching key-value pair, and return the query result to the first-level cache queryer. When dimension table data corresponding to the key value to be queried exists in the external data storage, the cache is updated based on the dimension table data.

[0010] In one embodiment of this application, the system further includes: a data sidestreamer, configured to send key-value pairs generated by the data analyzer to the data sidestreamer to generate a side-output data stream when the secondary cache queryer fails to find matching dimension table data from the external data storage; and a data writer, configured to initialize the receiver interface according to the database type to persist the side-output data stream to the external data storage according to the receiver interface.

[0011] In one embodiment of this application, the cache information includes one or more of the following: initial capacity, maximum capacity, expiration policy, and expiration time.

[0012] In one embodiment of this application, the cache initializer includes a partitioning component, and the configurator provides a partitioning strategy for the partitioning component so that after the cache initializer obtains the dimension table data, it caches the dimension table data to the corresponding partition according to the partitioning strategy.

[0013] In one embodiment of this application, a dimension table association optimization method is characterized by comprising: providing configuration information, the configuration information including external storage link information and cache information; passing data extraction logic to an external data storage device for execution according to the external storage link information to obtain dimension table data; generating corresponding key-value pairs according to the obtained dimension table data; updating or initializing the cache according to the key-value pairs; generating a query key-value pair according to the query data, so as to obtain the matching key-value pair in the cache based on the query key-value pair, thereby completing the association between the dimension table data and the query data.

[0014] In one embodiment of this application, obtaining a matching key-value pair in the cache based on the key-value pair to be queried includes: if no matching key-value pair exists in the cache, then obtaining dimension table data matching the key-value pair from the external data storage and updating the cache with the obtained dimension table data; if no dimension table data matching the key-value pair exists in the external storage, then returning the query result to the query application.

[0015] In one embodiment of this application, the step of passing the data extraction logic to an external data storage device for execution based on the external storage link information to obtain dimension table data includes: when the amount of dimension table data is less than or equal to a preset threshold, performing a full load of the dimension table data in the external storage device; when the amount of dimension table data is greater than the preset threshold, configuring an expiration policy for key-value pairs in the cache, so as to load the dimension table data from the external storage device according to the expiration policy.

[0016] This application also provides a computer device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the dimension table association optimization method.

[0017] This application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the dimension table association optimization method described above.

[0018] The beneficial effects of this invention are:

[0019] This application provides an external data storage, cache initializer, data analyzer, cache updater, and configuration information required for cache initialization through a configurator. This allows the cache to be initialized according to different scenarios and dimension table association requirements, meeting the application needs of dimension table cache association tasks in different data backends. It accurately and efficiently implements dimension table association tasks under different association conditions. By initializing and updating the cache, it ensures high throughput, low latency, and fast completion of dimension table associations, thereby improving the processing efficiency of dimension table association tasks. Attached Figure Description

[0020] Figure 1 This is a schematic diagram of the framework structure of a dimension table association optimization system in one embodiment of this application.

[0021] Figure 2 This is a schematic diagram of the structural framework of a dimension table association optimization system with a data update timer in one embodiment of this application.

[0022] Figure 3 This is a schematic diagram of the structural framework of a dimension table association optimization system with a second-level cache queryer in one embodiment of this application.

[0023] Figure 4 This is a schematic diagram of the structural framework of a dimension table association optimization system with a data writer and a data sidestream in one embodiment of this application.

[0024] Figure 5 This is a schematic diagram of the implementation framework for dimension table joins based on Flink.

[0025] Figure 6 This is a flowchart illustrating a dimension table association optimization method in one embodiment of this application.

[0026] Figure 7 This is a schematic diagram of the internal structure of a computer device according to an embodiment of this application. Detailed Implementation

[0027] The embodiments of the present invention will be described below with reference to the accompanying drawings and preferred embodiments. Those skilled in the art can easily understand other advantages and effects of the present invention from the content disclosed in this specification. The present invention can also be implemented or applied through other different specific embodiments, and various details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of the present invention. It should be understood that the preferred embodiments are only for illustrating the present invention and not for limiting the scope of protection of the present invention.

[0028] It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of the present invention. Therefore, the drawings only show the components related to the present invention and are not drawn according to the actual number, shape and size of the components in the actual implementation. In the actual implementation, the form, quantity and proportion of each component can be arbitrarily changed, and the layout of the components may also be more complex.

[0029] Terminology Explanation:

[0030] A dimension table, also known as a dimension analysis table, is a quantity used when analyzing data.

[0031] A fact table is a result table generated from a specific dimension after data aggregation; it is a specific statistical table.

[0032] The concepts of dimension tables and fact tables are often used in data warehouses. The two are mutually corresponding. Dimension table association is the association between fact tables and dimension tables. For example, a sales statistics table is a fact table, and the data source for its statistics is inseparable from the "product price table". The product price table is a dimension table of the sales statistics table. Fact data and dimension data usually need to be determined according to the specific problem. The fact table is used to store the measurement of facts and the foreign key values ​​pointing to each dimension, while the dimension table is used to store the metadata of the dimension.

[0033] Apache Flink (hereinafter referred to as Flink) is an open-source stream processing framework developed by the Apache Software Foundation. Its core is a distributed streaming data stream engine written in Java and Scala. Flink executes arbitrary streaming data programs in a data-parallel and pipelined manner. Flink's pipelined runtime system can execute both batch and stream processing programs. Furthermore, Flink's runtime itself also supports the execution of iterative algorithms.

[0034] The inventors discovered the following problems with existing cached big data dimension table joins:

[0035] 1. The memory cache lacks initialization capabilities. At the beginning of a task, it needs to interact extensively with Redis. When the data volume is particularly large, it will put a lot of pressure on Redis and cause data processing delays, leading to back pressure on Flink tasks or even task failure.

[0036] 2. The backend database heavily relies on Redis and supports only a limited range of database types. Switching to other databases with lower read / write frequency, such as MySQL, would severely impact performance. The data expiration policy is also simplistic, requiring data to be retrieved again once it expires, which puts a lot of pressure on the backend data service. In addition, the cache update efficiency is too low, which seriously affects the efficiency of joins.

[0037] 3. The dimension table association is limited to a single scenario. It does not support situations where both association with a dimension table and the generation of new data in the dimension table are required simultaneously. If one task generates data and another task associates the data, due to the characteristics of streaming tasks, a strong dependency cannot be formed between the two tasks, resulting in a period of time when the association cannot be established.

[0038] Based on the problems existing in the prior art, this application proposes a dimension table association optimization system, method, device and medium. The solution of this application will be described in detail below with reference to specific embodiments.

[0039] Please see Figure 1 , Figure 1 This is a schematic diagram of the framework structure of a dimension table association optimization system in one embodiment of this application. The dimension table association optimization system includes: a cache; a configurator for providing configuration information, including external storage link information and cache information; an external data storage unit for storing dimension table data; a cache initializer for passing data extraction logic to the external data storage unit for execution based on the external storage link information to obtain dimension table data; a data analyzer for generating corresponding key-value pairs based on the obtained dimension table data; a cache updater for updating or initializing the cache based on the key-value pairs; and a first-level cache queryer for generating a query key-value pair based on the query key-value pair, thereby obtaining matching key-value pairs in the cache and completing the association between the dimension table data and the query data.

[0040] Specifically, the configuration information for the cache initializer, external data storage, first-level cache queryer, data analyzer, and cache can be configured through the configurator. The cache initializer, based on the specific cache data retrieval strategy configured by the configurator, calls the external data storage to retrieve appropriate data from external storage for cache initialization and updates. The configurator completes the initialization of the external data storage. The external data storage is mainly used to establish connections and perform read / write operations with external storage, i.e., querying or saving data to external storage. Different external storage systems have different configurations and initialization methods, and the data serialization and deserialization also differ. Configuration and adjustments can be made according to actual application needs; no restrictions are imposed here. The data analyzer, based on the specific data transformation operation logic configured by the configurator, transforms the dimension table data retrieved from the external data storage to obtain the corresponding keys and values ​​needed for caching, thus obtaining key-value pairs for the dimension table data. The data analyzer can use these key-value pairs to initialize and update cached data. The cache updater obtains the transformed key-value pairs from the data analyzer and populates or updates the Caffeine cache with these pairs. The configurator allows you to configure cache information, including the initial cache size, maximum cache size, data expiration policy, and data expiration time. The specific types of cache information configured can be selected and adjusted according to actual application needs; there are no restrictions here. Data expiration policies can include no expiration, expiration based on write time, and expiration based on read time. For cases where the dimension table data volume is small, the data expiration policy can be configured to be no expiration, loading the entire dimension table data into the cache for initialization or updating. Applications or other external devices can query the data in the cache through the first-level cache queryer to obtain query results, facilitating the association between the application's fact table and the dimension table data in the cache. For example, the application can input the fact table as the data to be queried into the first-level cache queryer, generating a query key-value pair. This key-value pair is then compared with the corresponding key-value pair in the dimension table data in the cache. If they match, it is assumed that the corresponding dimension table data exists in the cache, and the dimension table data value is returned to the first-level cache queryer, allowing the application to perform dimension table associations based on the data obtained from the first-level cache queryer.

[0041] In one embodiment, the cache may include Redis, Guava Cache, Caffeine, Memcached, Ehcache, etc. The following uses Caffeine as an example to describe the scheme of this application embodiment. The specific cache type can be configured and adjusted according to the actual application requirements, and is not limited here.

[0042] Please see Figure 2 , Figure 2This is a schematic diagram of the structural framework of a dimension table association optimization system with a data update timer in one embodiment of this application. In one embodiment, the dimension table association optimization system may further set a data update timer to initialize a timer according to the timed update configuration information provided by the configurator; when the timer's execution time arrives, a cache initializer is called to retrieve dimension table data from an external data storage, so as to initialize or update the cache based on the dimension table data. Specifically, the data update timer can initialize a timer according to the dimension table data update frequency configured by the configurator, and the timer calls the cache initializer at a fixed frequency to complete the initialization and update operations of external data to the cache, ensuring the availability of cached data.

[0043] In one embodiment, the cache initializer includes a partitioning component, and the configurator provides a partitioning strategy for the partitioning component so that, after the cache initializer obtains the dimension table data, it caches the corresponding partitions of the dimension table data according to the partitioning strategy.

[0044] In one embodiment, the steps for the entire system to complete the dimension table join can be represented as follows:

[0045] In step S200, configuration information is provided through the configurator. The configuration information includes information about the data update timer, external storage connection information, and Caffeine cache information, which is mainly used for the initialization of each component.

[0046] Step S201: Obtain the external storage connection information from the configurator, such as the driver, URL, username, password, dbtype, etc. used to connect to the database. An example of the connection information can be represented as follows:

[0047] dbtype=mysql

[0048] mysql.driver=com.jdbc.mysql.driver

[0049] mysql.username = xxx

[0050] mysql.password=xxx

[0051] mysql.xxx.xxx=xxx

[0052] The specific database configuration information is obtained by using dbtype as a prefix, and the data connection pool is initialized, thereby initializing the external data storage.

[0053] Step S202: Obtain the configuration information of the Caffeine cache from the configurator: initial size, maximum size, expiration policy (write expiration, read expiration), expiration time, etc. Since dimension table associations with small data volumes are usually full caches, the cache does not need to expire. Based on these, initialize a Caffeine cache with an initial capacity of n (e.g., 10000, depending on the size of the dimension table) and a maximum size of m (must be greater than the number of data rows in the dimension table) that will not expire.

[0054] Step S203: Obtain data update timer information from the configurator. If the information indicates that timed updates need to be enabled, further read the timer's interval. Initialize a timer using the interval.

[0055] In step S204, the Caffeine cache initializer needs to determine the specific implementation based on the external storage database type (dbtype) in the configurator. For example, if dbtype = mysql, the initializer's SQL implementation would be similar to (select * from dim.table1 where a = b limit m). Since it's a full load, the limit statement is not needed. a = b is the condition for querying data. Based on Flink's partitioning strategy, the dimension table data can be allocated to the corresponding partitions in Flink, so that each partition only processes and caches the data it needs, without caching data that is not needed. Of course, if the data volume is small and memory is sufficient, the dimension table data can be fully cached in each partition, and the a = b condition in the WHERE clause can be removed.

[0056] In step S205, the data update timer will begin its first execution after initialization by calling the Caffeine cache initializer.

[0057] In step S206, the cache initializer calls the external data storage and passes the logic for retrieving data to the external data storage for execution, thereby obtaining the dimension table data.

[0058] In step S207, the data analyzer performs data transformation on the obtained dimension table data to generate the required key values ​​and corresponding cached value values, thereby obtaining key-value pairs of dimension table data.

[0059] In step S208, the cache updater initializes or updates the cache based on the transformed data. At this point, the cache data associated with the dimension table has completed the initialization operation and can provide association services.

[0060] In step S209, the timer for the data update timer is set, and the timer begins executing the logic from S206 to S208 to complete the overall update of the cache.

[0061] In step S210, when data arrives and needs to be queried from the cache, the first-level cache queryer first obtains the key to be queried from the data, and then searches for the data in the cache using the key. If the data exists, it is returned to the application; otherwise, the application is returned a status indicating that the data does not exist.

[0062] This completes the logic for joining dimension tables with relatively small datasets. Throughout the entire process, the data joining occurs entirely in memory, without any direct interaction with external services or tasks. Therefore, it achieves high throughput, low latency, and high efficiency.

[0063] Please see Figure 3 , Figure 3 This is a schematic diagram of the structural framework of a dimension table association optimization system with a second-level cache query in one embodiment of this application. The second-level cache query is used to determine the key value to be queried from the external data storage based on the external storage link information when the first-level cache query does not find a matching key-value pair, and return the query result to the first-level cache query. When dimension table data corresponding to the key value to be queried exists in the external data storage, the cache is updated based on the dimension table data.

[0064] Specifically, for dimension table join scenarios with large datasets, it's not suitable to store all the data in the dimension table within the Flink task's memory. In this case, the dimension table join steps can be represented as:

[0065] In step S300, the configurator provides configuration information, which includes external storage link information, Caffeine cache information, secondary queryer information, and information for initializing each component.

[0066] Step S301: Obtain the external storage connection information from the configurator, such as the driver, URL, username, password, dbtype, etc. used to connect to the database. For example, the connection information can be represented as follows:

[0067] dbtype=clickhouse

[0068] clickhouse.driver=com.jdbc.clickhouse.driver;

[0069] clickhouse.username = xxx;

[0070] clickhouse.password=xxx

[0071] clickhouse.xxx.xxx = xxx

[0072] The specific database configuration information is obtained by using dbtype as a prefix, and the data connection pool is initialized, thereby initializing the external data storage.

[0073] Step S302: Obtain the configuration information of the Caffeine cache from the configurator: the initial size, maximum size, expiration policy (write expiration, read expiration), and expiration time of the cache. Based on these, initialize a Caffeine cache with an initial capacity of n (e.g., 10000, depending on the size of the dimension table), a maximum size of m (the amount of data to be loaded), and a write-expired cache.

[0074] Step S303, the Caffeine cache initializer, needs to determine the specific implementation based on the dbtype of the external storage in the configurator. For example, if dbtype = clickhouse, the initializer's SQL implementation is similar to (select * from dim.table1 where a = b limit m order by update_time desc). Because the dimension table data is relatively large, the limit statement limits the amount of data loaded. a = b is the condition for querying data. According to Flink's partitioning strategy, the data of the dimension table can be allocated to the corresponding partitions in Flink, so that the partitions only process and cache the data they need, and do not need to cache data that is not used. order by time desc prioritizes loading the most recently used data (loading hot data).

[0075] In step S304, the cache initializer calls the external data storage and passes the logic for retrieving data to the external data storage for execution, thereby obtaining the dimension table data.

[0076] In step S305, the data analyzer performs data transformation on the obtained dimension table data to generate the required key values ​​and corresponding cached value values.

[0077] In step S306, the cache updater initializes or updates the cache based on the transformed data. At this point, the cache data associated with the dimension table has completed the initialization operation and can provide association services.

[0078] In step S307, when the application inputs the data to be queried into the first-level cache queryer, the first-level cache queryer first obtains the key to be queried through the data to be queried, and then searches for the data in the cache through the key. If it exists, it is returned to the application; if it does not exist, the second-level cache queryer is called.

[0079] In step S308, the second-level cache queryer determines the database configuration information based on the dbtype of the external storage in the configurator to link to the corresponding database, and implements the logic of querying external data based on the key value to be queried. For example, if dbtype = clickhouse, the SQL implementation of the queryer is similar to (select * from dim.table1 where key_name = key limit 1 order by update_time desc), which retrieves the latest value data based on the specified key value.

[0080] In step S309, the secondary cache queryer calls the external data storage to execute the logic of obtaining the latest data based on the key value. If the data exists, the logic of S304 and S3056 is executed, and the data and status (data exists) are returned to the primary cache queryer. If the data does not exist, the status is returned to the primary cache queryer, and the primary queryer returns the status to the application.

[0081] At this point, the dimension table join logic for large datasets is complete. Most of the data joins are performed in memory. Only a very small amount of cold data triggers an interaction between the second-level cache lookup server and the external service. Additionally, when expired data arrives and query requests are received, the second-level cache lookup server is invoked. However, in such cases, the data queries follow a normal distribution; not all expired data will trigger query requests at once, and there won't be a large number of queries requiring the second-level cache lookup server. Therefore, high throughput, low latency, and high efficiency dimension table joins can be achieved.

[0082] Please see Figure 4 , Figure 4 This is a schematic diagram of the structural framework of a dimension table association optimization system, which includes a data writer and a data sidestreamer, according to one embodiment of this application. The data sidestreamer is used to send key-value pairs generated by the data analyzer to the data sidestreamer to generate a side-output data stream when the secondary cache queryer fails to find matching dimension table data from the external data storage. The data writer is used to initialize the receiver interface according to the database type, so as to persist the side-output data stream to the external data storage according to the receiver interface.

[0083] In one embodiment, there are situations where dimension tables need to be generated based on data streams during use. For example, in a vehicle tracking information stream, some data includes vehicle channel information, while some does not. A slowly changing dimension table—the Vehicle Channel Table—is generated from the tracking stream containing vehicle channel information. However, some information lacks channel information and needs to be linked through the Vehicle Channel Table to complete the vehicle channel information. In such cases where both consuming and generating dimension tables are required for the same data stream, the dimension table association and new dimension table data generation can be completed through the following steps:

[0084] In step S400, the configurator provides configuration information, which includes external storage link information, Caffeine cache information, secondary queryer information, data sidestreamer information, and data writer information, for initializing each component.

[0085] Step S401: Obtain the external storage connection information from the configurator, such as the driver, URL, username, password, dbtype, etc., used to connect to the database. For example, the connection information can be represented as:

[0086] dbtype=clickhouse

[0087] clickhouse.driver=com.jdbc.clickhouse.driver;

[0088] clickhouse.username = xxx;

[0089] clickhouse.password=xxx

[0090] clickhouse.xxx.xxx = xxx

[0091] The database configuration information is obtained by using dbtype as a prefix, which completes the initialization of the data connection pool and thus initializes the external storage for the data.

[0092] Step S402: Obtain the configuration information of the Caffeine cache from the configurator: the initial size, maximum size, expiration policy (write expiration, read expiration), and expiration time of the cache. Based on these, initialize a Caffeine cache with an initial capacity of n (e.g., 10000, depending on the size of the dimension table), a maximum size of m (the amount of data to be loaded), and a write-expired cache.

[0093] Step S403, the Caffeine cache initializer, needs to determine the specific implementation based on the dbtype of the external storage in the configurator. For example, if dbtype = clickhouse, the initializer's SQL implementation is similar to (select * from dim.table1 where a = b limit m order by update_time desc). Because the dimension table data is relatively large, the limit statement limits the amount of data loaded. a = b is the condition for querying data. According to Flink's partitioning strategy, the data of the dimension table can be allocated to the corresponding partitions in Flink, so that the partitions only process and cache the data they need, and do not need to cache data that is not used. order by time desc prioritizes loading the most recently used data (loading hot data).

[0094] In step S404, the cache initializer calls the external data storage and passes the logic for retrieving data to the external data storage for execution, thereby obtaining the dimension table data.

[0095] In step S405, the data analyzer performs data transformation on the obtained dimension table data to generate the required key values ​​and corresponding cached value values, thereby obtaining key-value pairs composed of key and value.

[0096] In step S406, the cache updater initializes or updates the cache based on the transformed data. At this point, the cache data associated with the dimension table has completed the initialization operation and can provide association services.

[0097] In step S407, when data arrives, the first-level cache queryer first obtains the key to be queried from the data, and then searches for the data in the cache using the key. If the data exists, it is returned to the application; otherwise, the second-level cache queryer is called.

[0098] In step S408, the second-level cache queryer determines the logic implementation for querying external data based on the key value according to the dbtype of the external storage in the configurator. For example, if dbtype = clickhouse, the SQL implementation of the queryer is similar to (select * from dim.table1 where key_name = key limit 1 order by update_time desc), which retrieves the latest value data based on the specified key value.

[0099] In step S409, the secondary cache queryer calls the external data storage to execute the logic of obtaining the latest data based on the key value. If the data exists, it executes the logic of S404 and S405 and returns the data and status (data exists) to the primary queryer. If the data does not exist, it returns the status to the primary cache queryer, which then returns the status to the application.

[0100] In step S410, if the secondary cache queryer finds that the data does not exist in external storage, it will perform the following two operations:

[0101] Step S4101: Call the data analyzer to generate a key and its corresponding value, then continue with steps S404 and S405.

[0102] Step S4102: Send the data to the data sidestream.

[0103] The data sidestream wraps the data into side-output data and sends it to Flink's downstream stream.

[0104] In step S411, the data writer determines the specific implementation of the receiver interface SinkFunction based on the type of dbtype in the external storage in the configurator, and completes the initialization of SinkFunction by configuring data with dbtype as a prefix (as described in step 400).

[0105] In step S412, the data writer obtains the side output data stream Stream generated above from the downstream stream and adds a Sink to it, completing the external persistence of the dimension data. It should be noted that the table persisted by SinkFunction and the initialization cache are the same table, that is, the dimension table used.

[0106] In this scenario, the dimension table joins and dimension data production logic are already complete. This implementation not only provides fast dimension table joins, but also allows newly generated dimension table data to be immediately used in the joins, without waiting for external data to be persisted. During this period, there are instances where data joins cannot be established, leading to reduced accuracy. This solution precisely addresses this issue.

[0107] Please see Figure 5 , Figure 5This diagram illustrates the framework for dimension table joins based on Flink. In one embodiment, the aforementioned solution of this application can be implemented using Flink's asynchronous I / O, further improving the efficiency of dimension table joins based on Flink. Specifically, an internal Flink thread can connect to both a synchronous data processing interface and an asynchronous data processing interface, where the synchronous interface is bound to its corresponding synchronous interface implementation, and the asynchronous interface is bound to its corresponding asynchronous interface implementation. Integrating the interface that combines the synchronous and asynchronous interface implementations into a data output processor, and performing dimension table joins based on this processor, can improve the efficiency of dimension table joins.

[0108] Please see Figure 6 , Figure 6 This is a flowchart illustrating a dimension table join optimization method in one embodiment of this application. This method is used to implement the architecture of the aforementioned dimension table join optimization system. The specific system architecture will not be described in detail here. The method includes the above steps:

[0109] Step S600: Provide configuration information, including external storage link information and cache information;

[0110] Step S610: The data extraction logic is passed to the external data storage for execution based on the external storage link information to obtain the dimension table data;

[0111] Step S620: Generate corresponding key-value pairs based on the obtained dimension table data;

[0112] Step S630: Update or initialize the cache based on the key-value pair;

[0113] Step S640: Generate a query key value based on the query data, and obtain the matching key-value pair in the cache based on the query key value to complete the association between the dimension table data and the query data.

[0114] In one embodiment, retrieving a matching key-value pair from the cache based on the key-value pair to be queried includes: if no matching key-value pair exists in the cache, retrieving dimension table data matching the key-value pair from an external data storage device and updating the cache with the retrieved dimension table data; if no dimension table data matching the key-value pair exists in the external storage device, returning the query result to the query application.

[0115] In one embodiment, the step of passing the data extraction logic to an external data storage device for execution based on external storage link information to obtain dimension table data includes: when the amount of dimension table data is less than or equal to a preset threshold, performing a full load of the dimension table data in the external storage device; when the amount of dimension table data is greater than the preset threshold, configuring an expiration policy for key-value pairs in the cache, so as to load the dimension table data from the external storage device according to the expiration policy.

[0116] like Figure 7 The diagram shown is a schematic representation of the internal structure of a computer device in one embodiment. A computer device is provided, including: a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it performs the following steps: providing configuration information, including external storage link information and cache information; passing data extraction logic to an external data storage device for execution based on the external storage link information to obtain dimension table data; generating corresponding key-value pairs based on the obtained dimension table data; updating or initializing the cache based on the key-value pairs; generating a query key-value pair based on the query data, and retrieving matching key-value pairs from the cache based on the query key-value pair, thereby completing the association between the dimension table data and the query data.

[0117] In one embodiment, when the processor executes the above-mentioned method, the implementation of retrieving the matching key-value pair in the cache based on the key-value to be queried includes: if there is no matching key-value pair in the cache, retrieving dimension table data that matches the key-value to be queried from the external data storage and updating the cache with the retrieved dimension table data; if there is no dimension table data that matches the key-value to be queried in the external storage, returning the query result to the query application.

[0118] In one embodiment, when the processor executes the above-mentioned step of passing the data extraction logic to the external data storage memory for execution based on the external storage link information to obtain dimension table data includes: when the amount of dimension table data is less than or equal to a preset threshold, performing a full load of the dimension table data in the external storage memory; when the amount of dimension table data is greater than the preset threshold, configuring an expiration policy for key-value pairs in the cache, so as to load the dimension table data from the external storage memory according to the expiration policy.

[0119] In one embodiment, the aforementioned computer equipment can be used as a server, including but not limited to a standalone physical server or a server cluster consisting of multiple physical servers. The computer equipment can also be used as a terminal, including but not limited to mobile phones, tablets, personal digital assistants, or smart devices. Figure 7 As shown, the computer device includes a processor, non-volatile storage medium, internal memory, display screen, and network interface connected via a system bus.

[0120] The processor of this computer device provides computing and control capabilities to support the operation of the entire device. The non-volatile storage medium of the computer device stores the operating system and computer programs. These programs can be executed by the processor to implement the dimension table association optimization method provided in the above embodiments. The internal memory of the computer device provides a cached runtime environment for the operating system and computer programs stored in the non-volatile storage medium. The display interface can display data via a screen. The screen can be a touchscreen, such as a capacitive or electronic screen, and can generate corresponding instructions by receiving click operations on the controls displayed on the touchscreen.

[0121] Those skilled in the art will understand that Figure 7 The structure of the computer device shown in the figure is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. A specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0122] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, it performs the following steps: providing configuration information, including external storage link information and cache information; passing data extraction logic to an external data storage device for execution based on the external storage link information to obtain dimension table data; generating corresponding key-value pairs based on the obtained dimension table data; updating or initializing the cache based on the key-value pairs; generating a query key-value pair based on the query data, so as to obtain the matching key-value pair in the cache based on the query key-value pair, thereby completing the association between the dimension table data and the query data.

[0123] In one embodiment, when the computer program is executed by the processor, the implementation of retrieving matching key-value pairs in the cache based on the key-value to be queried includes: if no matching key-value pair exists in the cache, retrieving dimension table data matching the key-value to be queried from the external data storage and updating the cache with the retrieved dimension table data; if no dimension table data matching the key-value to be queried exists in the external storage, returning the query result to the query application.

[0124] In one embodiment, when the computer program is executed by the processor, the step of passing the data extraction logic to the external data storage memory for execution based on the external storage link information to obtain dimension table data includes: when the amount of dimension table data is less than or equal to a preset threshold, performing a full load of the dimension table data in the external storage memory; when the amount of dimension table data is greater than the preset threshold, configuring an expiration policy for key-value pairs in the cache, so as to load the dimension table data from the external storage memory according to the expiration policy.

[0125] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a non-volatile computer-readable storage medium. When executed, the program can include the processes of the embodiments of the methods described above. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), etc.

[0126] The above embodiments are merely preferred embodiments provided to fully illustrate the present invention, and the scope of protection of the present invention is not limited thereto. Equivalent substitutions or modifications made by those skilled in the art based on the present invention are all within the scope of protection of the present invention.

Claims

1. A dimension table association optimization system, characterized in that, include: cache; A configurator is used to provide configuration information, including external storage link information and cache information; External data storage device for storing dimension table data; A cache initializer is used to pass the data extraction logic to the external data storage device for execution based on the external storage link information, so as to actively extract the dimension table data from the external data storage device; The cache initializer includes a partitioning component, and the configurator provides a partitioning strategy for the partitioning component so that after the cache initializer obtains the dimension table data, the dimension table data is cached to the corresponding partition according to the partitioning strategy. A data update timer is used to initialize a timer according to the timed update configuration information provided by the configurator; when the execution time of the timer arrives, the cache initializer is called to retrieve dimension table data from the external data storage, so as to initialize or update the cache based on the dimension table data; A data analyzer is used to generate corresponding key-value pairs based on the obtained dimension table data; A cache updater is used to update or initialize the cache based on the key-value pairs; A first-level cache queryer is used to generate a query key value based on the data to be queried, so as to obtain the matching key-value pair in the cache based on the query key value, and complete the association between the dimension table data and the data to be queried.

2. The dimension table association optimization system according to claim 1, characterized in that, The external storage link information includes the database type. The cache initializer determines the corresponding database configuration information based on the database type, and completes the initialization of the external data storage based on the database configuration information.

3. The dimension table association optimization system according to claim 2, characterized in that, The system also includes a second-level cache queryer, which is used to determine the logic implementation of the key value to be queried from the external data storage based on the external storage link information when the first-level cache queryer does not find a matching key-value pair, and return the query result to the first-level cache queryer. When dimension table data corresponding to the key value to be queried exists in the external data storage, the cache is updated based on the dimension table data.

4. The dimension table association optimization system according to claim 3, characterized in that, The system also includes: A data sidestreamer is used to send the key-value pairs generated by the data analyzer to the data sidestreamer when the second-level cache queryer fails to find matching dimension table data from the external data storage, so as to generate a side-output data stream. A data writer is used to initialize the receiver interface according to the database type, so as to persist the side output data stream to the external data storage according to the receiver interface.

5. The dimension table association optimization system according to any one of claims 1-4, characterized in that, The cache information includes one or more of the following: initial capacity, maximum capacity, expiration policy, and expiration time.

6. A method for optimizing dimension table associations, characterized in that, include: Provide configuration information, which includes external storage link information and cache information; The data extraction logic is passed to the external data storage for execution based on the external storage link information, so as to actively extract dimension table data from the external storage. The cache initializer includes a partitioning component, and the configurator provides a partitioning strategy for the partitioning component. After the cache initializer obtains the dimension table data, it caches the dimension table data to the corresponding partition according to the partitioning strategy. A timer is initialized according to the timed update configuration information provided by the configurator. When the timer's execution time arrives, the cache initializer is invoked to retrieve the dimension table data from the external data storage. Corresponding key-value pairs are generated based on the obtained dimension table data. Update or initialize the cache based on the key-value pairs; Generate a query key value based on the query data, and obtain the matching key-value pair in the cache based on the query key value to complete the association between the dimension table data and the query data.

7. The dimension table association optimization method according to claim 6, characterized in that, Retrieving the matching key-value pair from the cache based on the key-value pair to be queried includes: If no matching key-value pair is found in the cache, the dimension table data matching the key-value pair to be queried is retrieved from the external data storage, and the retrieved dimension table data is updated in the cache. If no dimension table data matching the key value to be queried exists in the external storage, the query result will be returned to the query application.

8. The dimension table association optimization method according to claim 6 or 7, characterized in that, The steps of passing the data extraction logic to the external data storage for execution based on the external storage link information to obtain the dimension table data include: When the amount of dimension table data is less than or equal to a preset threshold, the dimension table data in the external storage is fully loaded. When the amount of data in the dimension table exceeds the preset threshold, an expiration policy for key-value pairs in the cache is configured to load the dimension table data from the external storage according to the expiration policy.

9. A computer device, comprising: A memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, when the processor executes the computer program, it implements the steps of the dimension table association optimization method according to any one of claims 6 to 8.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the dimension table association optimization method as described in any one of claims 6 to 8.

Images