Cold and hot data identification method and device, electronic equipment and storage medium

Abstract

The invention discloses a cold and hot data recognition method and device, electronic equipment and a storage medium, and is used for solving the problem that an existing cold and hot data recognition method is low in recognition accuracy, and the method comprises the steps that SQL query request information of an online analysis data warehouse and query related index information of the SQL query request information in each preset time period of the last time period are obtained; for each piece of SQL query request information, determining the weight of each piece of query related index information of the SQL query request information according to the query related index information of the SQL query request information in each preset time period; according to the query related index information of the SQL query request information in each preset time period and the weight of each piece of query related index information, constructing a target feature matrix of the SQL query request information; and inputting the target feature matrix into a cold and hot data prediction model to obtain a popularity prediction result of the business data corresponding to the SQL query request information in the offline data warehouse.

Description

Technical Field

[0001] This application relates to the field of computer technologies, and in particular, to a method and device for identifying hot and cold data, an electronic device, and a storage medium. Background Art

[0002] In a big data analysis system, with the exponential growth of the data scale, the amount of business data that enterprises or organizations need to process is constantly increasing. Among them, data with a low access frequency is cold data, and data that is frequently accessed (such as querying and updating) is hot data. For example, in the Internet of Things (IoT) business, the business data that needs to be processed in real time generated by IoT devices is hot data, and the monitoring data for IoT devices, such as monitoring log files, is cold data. In video business processing, hot data includes video files that are being accessed or edited, and cold data includes backup materials, etc.

[0003] Data warehouse systems are widely used in data offline analysis (such as Hive data warehouse) and real-time query (such as StarRocks data warehouse). Importing data from the Hive data warehouse into the StarRocks data warehouse is often applied to scenarios that require high-performance real-time analysis. The StarRocks data warehouse is famous for its efficient massively parallel processing (MPP) architecture and sub-second query performance, while the Hive data warehouse is more suitable for batch storage and offline analysis. In related technologies, an import strategy based on partitioning or primary keys is usually adopted to import data from the Hive data warehouse into the StarRocks data warehouse. The imported cold data and hot data are stored mixed in the same partition or table. Since the storage of cold data incurs a large amount of additional storage costs and hinders the access performance of hot data during the query process, it causes unbalanced resource utilization, thereby affecting the overall performance and query efficiency of the data warehouse system.

[0004] To address the above problems, the hot and cold data identification strategies commonly used in related technologies, such as the LRU (Least Recently Used) strategy and the LFU (Least Frequently Used) strategy, are used to identify hot and cold data and then store them separately in different partitions or tables. However, the LRU and LFU strategies have deficiencies in predicting hot and cold data. Since these strategies distinguish hot and cold data based on static and simple features such as access frequency, however, the hot and cold characteristics of data usually change dynamically over time. For example, some data may be frequently accessed during a specific period, and then the access demand decreases significantly, changing from hot data to cold data. Therefore, the existing strategies result in low accuracy in identifying hot and cold data, thereby affecting the query efficiency of the data. Summary of the Invention

[0005] To solve the problem of low recognition accuracy in existing hot and cold data recognition methods, embodiments of the present application provide a hot and cold data recognition method, apparatus, electronic device, and storage medium.

[0006] In a first aspect, embodiments of the present application provide a hot and cold data recognition method, including:

[0007] Obtain SQL query request information and its query-related metric information in the online analytical data warehouse during each preset time period in the previous time period. The query-related metrics of the SQL query request information include static query metrics and time metrics;

[0008] For each piece of SQL query request information, determine the weight of each query-related metric information of the SQL query request information according to the query-related metric information of the SQL query request information during each preset time period;

[0009] Construct a target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information during each preset time period and the weight of each query-related metric information;

[0010] Input the target feature matrix into a hot and cold data prediction model to obtain a heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is trained based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse. The heat of the business data represents the frequency of the business data being queried;

[0011] Migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse.

[0012] In an implementation manner, the static query metrics at least include: SQL query request information, query count information, scanned row count information of the business data corresponding to the SQL query request, query failure count information, and memory usage information of the data queried according to the SQL query request; the time metrics at least include: query time information and response duration information of the SQL query request;

[0013] For each piece of SQL query request information, determining the weight of each query-related metric information of the SQL query request information according to the query-related metric information of the SQL query request information during each preset time period specifically includes:

[0014] For each piece of SQL query request information, normalize each query-related metric information of the SQL query request information within each preset time period separately;

[0015] Determine the weight of each query-related metric information of the SQL query request information based on the normalized query-related metric information and the random forest model.

[0016] In one implementation, determining the weight of each query-related metric information of the SQL query request information based on the normalized query-related metric information and the random forest model specifically includes:

[0017] Input the normalized query-related metric information of the SQL query request information within each preset time period into the random forest model respectively to obtain the heat prediction value of the SQL query request information within each preset time period. The random forest model is used to predict the heat of the SQL query request information based on the query-related metric information of the SQL query request information, and the heat of the SQL query request information represents the frequency of the SQL query request information being used to query business data;

[0018] Based on the heat prediction value and the true heat value of the SQL query request information within each preset time period, determine the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting;

[0019] Determine the weight of the corresponding query-related metric information of the SQL query request information based on the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting.

[0020] In one implementation, determining the weight of the corresponding query-related metric information of the SQL query request information based on the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting specifically includes:

[0021] Calculate the weight of each query-related metric information of the SQL query request information through the following formula:

[0022]

[0023] where I(x i ) represents the weight of the i-th query-related metric information of the SQL query request information;

[0024] Denote the loss change amount predicted by the random forest model when the i-th query-related metric of the SQL query request information in the t-th preset time period is used for splitting, where t = 1, 2, ……, T, and T represents the number of the preset time periods, y t Denote the true value of the popularity of the SQL query request information in the t-th preset time period, Denote the predicted value of the popularity of the SQL query request information in the t-th preset time period, X t =[x′ 1,t , x′ 2,t , ……, x′ n,t , and f(X t ) is the objective function of the random forest model, where X t Denote the combined information of the query-related metrics of the SQL query request information after normalization in the t-th preset time period, and x′ 1,t ~x′ n,t Denote the 1st to n-th query-related metric information of the SQL query request information after normalization in the t-th preset time period, where n represents the number of the query-related metric information.

[0025] In one implementation manner, construct the target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period and the weights of each query-related metric information, specifically including:

[0026] Construct the feature matrix of the SQL query request information in each preset time period according to each query-related metric information of the SQL query request information after normalization in each preset time period and the weights of the corresponding query-related metric information;

[0027] Construct the target feature matrix of the SQL query request information according to the feature matrices corresponding to the SQL query request information in each preset time period.

[0028] In one implementation manner, construct the feature matrix of the SQL query request information in each preset time period according to each query-related metric information of the SQL query request information after normalization in each preset time period and the weights of the corresponding query-related metric information, specifically including:

[0029] Construct the feature matrix of the SQL query request information in each preset time period through the following formula:

[0030]

[0031] where, z tIt represents the feature matrix of the SQL query request information within the t-th preset time period, where t = 1, 2, ……, T, and T represents the number of the preset time periods;

[0032] I(x1)~I(x n ) represents the weights of the 1st to n-th query-related index information of the SQL query request information, and n represents the number of query-related index information;

[0033] x′ 1,t ~x′ n,t represents the 1st to n-th query-related index information after normalization of the SQL query request information within the t-th preset time period.

[0034] In one implementation, the target feature matrix of the SQL query request information is constructed according to the feature matrix corresponding to the SQL query request information in each preset time period, specifically including:

[0035] The target feature matrix of the SQL query request information is constructed by the following formula:

[0036]

[0037] where Z represents the target feature matrix of the SQL query request information;

[0038] z1~z T represents the feature matrix of the SQL query request information within the 1st to T-th preset time periods.

[0039] In a second aspect, an embodiment of the present application provides a cold and hot data identification device, including:

[0040] An acquisition module, configured to acquire the SQL query request information and its query-related index information of the online analytical data warehouse in each preset time period of the previous time period, where the query-related indexes of the SQL query request information include static query indexes and time indexes;

[0041] A determination module, configured to determine, for each SQL query request information, the weight of each query-related index information of the SQL query request information according to the query-related index information of the SQL query request information in each preset time period;

[0042] A feature matrix construction module, configured to construct the target feature matrix of the SQL query request information according to the query-related index information of the SQL query request information in each preset time period and the weight of each query-related index information;

[0043] An identification module, configured to input the target feature matrix into a hot and cold data prediction model to obtain a heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is obtained by training based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse, and the heat of the business data represents the frequency of the business data being queried.

[0044] A processing module, configured to migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse.

[0045] In one implementation, the static query metrics at least include: SQL query request information, query count information, scanned row count information of the business data corresponding to the SQL query request, query failure count information, and memory usage information corresponding to querying data according to the SQL query request; the time metrics at least include: query time information and response duration information of the SQL query request.

[0046] The determination module is specifically configured to, for each piece of SQL query request information, normalize each query-related metric information of the SQL query request information within each preset time period; determine the weight of each query-related metric information of the SQL query request information according to the normalized query-related metric information and a random forest model.

[0047] In one implementation, the determination module is specifically configured to input the normalized query-related metric information of the SQL query request information within each preset time period into the random forest model respectively to obtain a heat prediction value of the SQL query request information within each preset time period. The random forest model is used to predict the heat of the SQL query request information based on the query-related metric information of the SQL query request information, and the heat of the SQL query request information represents the frequency of the SQL query request information being used to query business data; determine the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting according to the heat prediction value and the true heat value of the SQL query request information within each preset time period; determine the weight of the corresponding query-related metric information of the SQL query request information according to the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting.

[0048] In one embodiment, the determining module is specifically configured to calculate the weight of each query-related metric information of the SQL query request information through the following formula:

[0049]

[0050] where I(x i ) represents the weight of the i-th query-related metric information of the SQL query request information;

[0051] represents the change in loss predicted by the random forest model when the i-th query-related metric of the SQL query request information is used for splitting within the t-th preset time period, t = 1, 2, ……, T, T represents the number of preset time periods, y t represents the true value of the popularity of the SQL query request information within the t-th preset time period, represents the predicted value of the popularity of the SQL query request information within the t-th preset time period, X t =[x′ 1,t ,x′ 2,t ,……,x′ n,t , f(X t ) is the objective function of the random forest model, X t represents the combination of normalized query-related metric information of the SQL query request information within the t-th preset time period, x′ 1,t ~x′ n,t represent the 1st to nth normalized query-related metric information of the SQL query request information within the t-th preset time period, n represents the number of query-related metric information.

[0052] In one embodiment, the feature matrix construction module is specifically configured to construct the feature matrix of the SQL query request information for each preset time period according to each normalized query-related metric information of the SQL query request information within each preset time period and the weight of each corresponding query-related metric information; construct the target feature matrix of the SQL query request information according to the feature matrices corresponding to the SQL query request information for each preset time period.

[0053] In one embodiment, the feature matrix construction module is specifically configured to construct the feature matrix of the SQL query request information for each preset time period through the following formula:

[0054]

[0055] where z tIt represents the feature matrix of the SQL query request information within the t-th preset time period, where t = 1, 2, ……, T, and T represents the number of the preset time periods;

[0056] I(x1)~I(x n ) represents the weights of the 1st to n-th query-related index information of the SQL query request information, and n represents the number of the query-related index information;

[0057] x′ 1,t ~x′ n,t represents the 1st to n-th query-related index information after normalization of the SQL query request information within the t-th preset time period.

[0058] In one implementation manner, the feature matrix construction module is specifically configured to construct the target feature matrix of the SQL query request information through the following formula:

[0059]

[0060] where Z represents the target feature matrix of the SQL query request information;

[0061] z1~z T represents the feature matrix of the SQL query request information within the 1st to T-th preset time periods.

[0062] In a third aspect, an embodiment of the present application provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, the cold and hot data recognition method described in the present application is implemented.

[0063] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, on which a computer program is stored. When the program is executed by a processor, the steps in the cold and hot data recognition method described in the present application are implemented.

[0064] The beneficial effects of the present application are as follows:

[0065] The hot and cold data recognition method, device, electronic device, and storage medium provided by the embodiments of this application obtain the SQL (Structured Query Language) query request information and its query-related metric information in the online analytical data warehouse during each preset time period in the previous time period. The query-related metrics of the SQL query request information include static query metrics and time metrics. For each SQL query request information, according to the query-related metric information of the SQL query request information in each preset time period, determine the weight of each query-related metric information of the SQL query request information. According to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information, construct the target feature matrix of the SQL query request information. Input the target feature matrix into the hot and cold data prediction model to obtain the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is trained based on a three-dimensional convolutional neural network (3D Convolutional Neural Network, 3D CNN). The hot and cold data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse. The heat of the business data represents the frequency of the business data being queried. Migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse. In the embodiments of this application, multi-dimensional features are extracted from the historical SQL query information including static query metrics and time metrics in the online analytical data warehouse according to a preset time period, and the target feature matrix of the SQL query request information is constructed according to the importance of different dimensional features (i.e., the weight of each query-related metric information). Based on the trained hot and cold data prediction model, predict the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse in the next time period. Furthermore, migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the cold data. In the prior art, the strategy of identifying hot and cold data based on simple static metrics such as access frequency does not consider the time characteristics of data access, resulting in low identification accuracy. Compared with the prior art, the hot and cold data prediction model in this application is trained using a three-dimensional convolutional neural network, and a three-dimensional convolutional kernel is used to capture the dynamic trend of the SQL query access pattern in the time dimension, improving the accuracy of hot and cold data recognition. Moreover, different from the traditional partition storage method, this application realizes the dynamic separation storage of hot data and cold data, stores the identified hot data in the online analytical data warehouse, and stores the cold data in the offline data warehouse, so that the client can accurately access hot and cold data, improving the query efficiency of the data.

[0066] Other features and advantages of the present application will be set forth in the following description, and in part will be obvious from the description, or may be learned by practice of the present application. The objectives and other advantages of the present application may be realized and attained by the structure particularly pointed out in the written description, claims, as well as the drawings. Description of the Drawings

[0067] The drawings described herein are for further understanding of the present application, and constitute a part of the present application. The schematic embodiments and descriptions thereof of the present application are used to explain the present application, and do not constitute an improper limitation to the present application. In the drawings:

[0068] Figure 1 It is a schematic diagram of the application scenario of the hot and cold data identification method provided by the embodiment of the present application;

[0069] Figure 2 It is a schematic flowchart of the hot and cold data identification method provided by the embodiment of the present application;

[0070] Figure 3 It is a schematic flowchart of determining the weight of each query-related index information of the SQL query request information provided by the embodiment of the present application;

[0071] Figure 4 It is a schematic flowchart of determining the weight of each query-related index information of the SQL query request information provided by the embodiment of the present application;

[0072] Figure 5 It is a schematic flowchart of constructing the target feature matrix of the SQL query request information provided by the embodiment of the present application;

[0073] Figure 6 It is a schematic structural diagram of the hot and cold data identification device provided by the embodiment of the present application;

[0074] Figure 7 It is a schematic structural diagram of the electronic device provided by the embodiment of the present application. Detailed Embodiments

[0075] To solve the problem of low recognition accuracy of the existing hot and cold data identification methods, the embodiments of the present application provide a hot and cold data identification method, device, electronic device and storage medium.

[0076] The following describes the preferred embodiments of the present application with reference to the drawings of the specification. It should be understood that the preferred embodiments described herein are only for explaining and understanding the present application, and are not used to limit the present application. And without conflict, the embodiments in the present application and the features in the embodiments may be combined with each other.

[0077] In this document, it should be understood that among the technical terms involved in the present application:

[0078] 1. Random Forest algorithm: An ensemble learning algorithm composed of multiple decision trees, used for tasks such as classification and regression. It improves the accuracy and stability of the model by training multiple decision trees and integrating their prediction results. In classification tasks, Random Forest determines the final predicted class through majority voting; in regression tasks, the average predicted value of all trees is used as the final output. Random Forest has strong noise resistance, is suitable for high-dimensional data scenarios, and can screen out the features that have the greatest impact on the model through feature importance evaluation.

[0079] 2. 3D Convolutional Neural Network: A deep learning model designed specifically for tasks with three-dimensional data structures. The convolutional kernels of 3D CNN slide not only in width and height but also in the depth or time dimension, allowing the model to more comprehensively extract features in sequential data or three-dimensional data. 3D CNN is widely used in tasks such as video action recognition, medical image analysis, and video classification, due to its excellent performance in jointly modeling spatial and temporal features.

[0080] 3. Normalization: A data preprocessing technique used to transform data of different scales into a unified range, usually [0,1] or [-1,1]. Common normalization methods include Min-Max Normalization and Z-score Normalization. Normalization is applicable to processing features of different magnitudes, enabling the model to effectively learn even when the feature values vary greatly, and improving the performance of the model during training and prediction.

[0081] First refer to Figure 1, which is a schematic diagram of an application scenario of the hot and cold data identification method provided by the embodiments of the present application. It may include a data analysis platform 101, an offline data warehouse 102, and an online analytical data warehouse 103. The data analysis platform 101 is respectively connected to the offline data warehouse 102 and the online analytical data warehouse 103 through a network, and the offline data warehouse 102 is connected to the online analytical data warehouse 103 through a network. The offline data warehouse 102 refers to a data management system that is mainly used to store and process a large amount of historical data, supports complex query and analysis operations, but does not directly support real-time or near-real-time data processing. Such systems are usually used for batch processing and periodic data updates to provide functions such as business intelligence, report generation, and trend analysis. The offline data warehouse 102 can be a Hive data warehouse, and the embodiments of the present application do not limit this. The online analytical data warehouse 103 refers to a data warehouse that supports real-time or near-real-time data analysis and is a system designed for quickly processing and analyzing a large amount of data. These systems can provide instant query responses, enabling users to quickly obtain the required information for decision support, report generation, etc. The online analytical data warehouse 103 can be a StarRocks data warehouse, an Amazon Redshift data warehouse, a Snowflake data warehouse, etc., and the embodiments of the present application do not limit this. Both the offline data warehouse 102 and the online analytical data warehouse 103 allow the use of SQL interfaces to query the stored large-scale data sets. The offline data warehouse 102 is used to store business data, and imports the hot data in the updated business data into the online analytical data warehouse 103 for storage according to a preset time period, and retains the cold data in the offline data warehouse 102. The client queries the corresponding business data by sending SQL query request information to the online analytical data warehouse 103.The cold and hot data identification method provided by the embodiments of this application is applied to the application scenario of importing business data in the offline data warehouse 102 into the online analytical data warehouse 103. The business data in the offline data warehouse 102 is identified as cold or hot data in the following manner: The data analysis platform 101 obtains the SQL query request information and its query-related metric information in each preset time period of the online analytical data warehouse in the previous time period. The query-related metrics of the SQL query request information include static query metrics and time metrics. For each piece of SQL query request information, according to the query-related metric information of the SQL query request information in each preset time period, the weight of each query-related metric information of the SQL query request information is determined. According to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information, a target feature matrix of the SQL query request information is constructed. The target feature matrix is input into the cold and hot data prediction model to obtain the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The cold and hot data prediction model is obtained by training based on a three-dimensional convolutional neural network. The cold and hot data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse. The heat of the business data represents the frequency of the business data being queried. The business data with a heat prediction result of hot data is migrated from the offline data warehouse to the online analytical data warehouse for storage, and the business data with a heat prediction result of cold data is retained in the offline data warehouse. Among them, the business data can be data in any business field, such as Internet of Things business data, including: real-time business data generated by Internet of Things devices, monitoring data of Internet of Things devices such as monitoring log files, etc.; it can also be video business data, including video files, backup materials, etc.; it can also be business data in the network security operation scenario, security device (such as firewall) alarm data, etc. The embodiments of this application do not limit this.

[0082] The data analysis platform 101 can be a device with computing capabilities, such as a server, a terminal device, etc. The server can be an independent physical server or a cloud server that provides basic cloud computing services such as cloud servers, cloud databases, and cloud storage. The terminal device can be, but is not limited to: smart phones, tablet computers, laptop computers, desktop computers, etc. The embodiments of this application do not limit this.

[0083] Based on the above application scenario, the following will refer to the attached Figures 2 - 5 Describe the exemplary embodiments of this application in more detail. It should be noted that the above application scenario is only shown for the convenience of understanding the spirit and principle of this application, and the embodiments of this application are not limited by this. On the contrary, the embodiments of this application can be applied to any applicable scenario.

[0084] Such as Figure 2As shown in the figure, it is a schematic flowchart of the implementation process of the hot and cold data identification method provided by the embodiment of the present application. This hot and cold data identification method can be applied to the above-mentioned data analysis platform 101, and specifically may include the following steps:

[0085] S21. Obtain the SQL query request information and its query-related index information in the online analytical data warehouse within each preset time period of the previous time period.

[0086] Specifically, during implementation, the data analysis platform can identify hot and cold data for the business data in the offline data warehouse according to a preset time period, and divide a time period into multiple preset time periods. The preset time period and the preset time period can be set according to requirements. For example, the preset time period can be set to 1 day, and each hour is a preset time period. In this way, a time period can be divided into 24 preset time periods. It can also be set to other arbitrary durations for the preset time period and the preset time period. The embodiments of the present application do not limit this.

[0087] During implementation, the historical SQL query information is recorded in the database audit log of the online analytical data warehouse. The data analysis platform obtains each SQL query request information and its query-related index information in the online analytical data warehouse within each preset time period of the previous time period statistically by the online analytical data warehouse from the database audit log of the online analytical data warehouse. The online analytical data warehouse can also actively report the SQL query request information and its query-related index information in the online analytical data warehouse within each preset time period of the previous time period to the data analysis platform. The embodiments of the present application do not limit this.

[0088] Among them, the query-related indexes of the SQL query request information include static query indexes and time indexes. The static query indexes at least include: SQL query request information, query times information, the number of scanned rows information of the business data corresponding to the SQL query request, query failure times information, and the memory usage rate information of the data queried according to the SQL query request; the time indexes at least include: query time information and the response duration information of the SQL query request. The response duration information of a certain SQL query request within each preset time period can be the average response duration information of this SQL query request within this preset time period.

[0089] S22. For each SQL query request information, determine the weight of each query-related index information of the SQL query request information according to the query-related index information of the SQL query request information within each preset time period.

[0090] Specifically, during implementation, for each SQL query request information, the weight of each query-related index information of the SQL query request information can be determined according to the process shown in Figure 3 the figure, including the following steps:

[0091] S31. For each piece of SQL query request information, normalize each query-related metric information of the SQL query request information within each preset time period separately.

[0092] Specifically, when implementing, the following formula can be used to normalize each query-related metric information of the SQL query request information within each preset time period separately:

[0093]

[0094] Among them, x′ i,t represents the normalized i-th query-related metric information of the SQL query request information in the t-th preset time period of the previous time cycle, i = 1, 2, ……, n, where n represents the number of query-related metric information of the SQL query request information, and t = 1, 2, ……, T, where T represents the number of preset time periods;

[0095] x i,t represents the i-th query-related metric information of the SQL query request information in the t-th preset time period;

[0096] x min,i represents the minimum value of the i-th query-related metric information of the SQL query request information in the T preset time periods of the previous time cycle, and x max,i represents the maximum value of the i-th query-related metric information of the SQL query request information in the T preset time periods of the previous time cycle.

[0097] S32. Determine the weight of each query-related metric information of the SQL query request information according to the normalized query-related metric information and the random forest model.

[0098] Specifically, when implementing, the weight of each query-related metric information of the SQL query request information represents the importance of each query-related metric information, that is, the contribution degree of each query-related metric information.

[0099] When implementing, the weight of each query-related metric information of the SQL query request information can be determined according to the process as Figure 4 shown, including the following steps:

[0100] S41. Input the normalized query-related metric information of the SQL query request information in each preset time period into the random forest model respectively to obtain the heat prediction value of the SQL query request information in each preset time period.

[0101] Specifically, the random forest model is used to predict the heat of the SQL query request information based on the query-related metric information of the SQL query request information. The heat of the SQL query request information represents the frequency of the SQL query request information being used to query business data.

[0102] Specifically, for each SQL query request, the query-related metric information after normalizing the SQL query request information within each preset time period is combined into a feature vector. For an SQL query request, the feature vector formed by combining the query-related metric information after normalization can be expressed as:

[0103] X t =[x′ 1,t ,x′ 2,t ,……,x′ n,t

[0104] X t represents the combination of query-related metric information after normalizing the SQL query request information within the t-th preset time period, and can be represented in vector form;

[0105] x′ 1,t ~x′ n,t represent the 1st to n-th query-related metric information after normalizing the SQL query request information within the t-th preset time period, i = 1, 2, ……, n, where n represents the number of query-related metric information of the SQL query request information, and t = 1, 2, ……, T, where T represents the number of preset time periods.

[0106] For each preset time period t, there is a corresponding target variable y t , y t is the true value of the popularity of the SQL query request information within the t-th preset time period. Then, the target variable matrix corresponding to the T preset time periods is: y = [y1, y2,......, y T . Based on the random forest model, the predicted popularity value of the SQL query request information within each preset time period can be obtained:

[0107]

[0108] represents the predicted popularity value of the SQL query request information within the t-th preset time period;

[0109] f(X t ) is the objective function of the random forest model.

[0110] ​During implementation, each query-related metric information after normalizing the SQL query request information can be separately input into a text embedding model (Embedding) for embedding to obtain the feature vector of each query-related metric information after normalization. The text embedding model is used to generate the vector representation of the text. The text embedding model can, but is not limited to, adopt the following models: the text-embedding-large-3 model of OpenAI, the BCEmbedding model of Youdao, etc. Any other model that can generate text vectors can also be used. The embodiments of the present application do not make any limitations in this regard.

[0111] S42. According to the predicted heat value and the actual heat value of the SQL query request information in each preset time period, determine the loss change amount of each query-related metric of the SQL query request being used for prediction by the random forest model during splitting in each preset time period.

[0112] During specific implementation, it is possible to indirectly understand which features contribute the most to reducing the overall loss through feature importance. Feature importance can be measured by the average decrease in impurity or permutation importance. The higher the importance of a feature, the greater its role in reducing the overall loss of the model. The loss (such as mean squared error) change amount of the i-th query-related metric of the SQL query request information in the t-th preset time period being used for splitting in the random forest model prediction is:

[0113] S43. Determine the weight of the corresponding query-related metric information of the SQL query request information according to the loss change amount of each query-related metric of the SQL query request being used for splitting in the random forest model prediction in each preset time period.

[0114] During specific implementation, the weight of each query-related metric information of the SQL query request information can be calculated through the following formula:

[0115]

[0116] where, I(x i ) represents the weight of the i-th query-related metric information of the SQL query request information;

[0117] represents the loss change amount of the i-th query-related metric of the SQL query request information being used for splitting in the random forest model prediction in the t-th preset time period, t = 1, 2,..., T, T represents the number of preset time periods, y t represents the actual heat value of the SQL query request information in the t-th preset time period, represents the predicted heat value of the SQL query request information in the t-th preset time period, X t = [x′ 1,t , x′ 2,t , ……, x′ n,t , f(X t ) is the objective function of the random forest model, and X t represents the combined query-related metric information after normalizing the SQL query request information within the t-th preset time period. x′ 1,t ~x′ n,t represents the 1st to n-th query-related metric information after normalizing the SQL query request information within the t-th preset time period, and n represents the number of query-related metric information.

[0118] S23. Construct the target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information within each preset time period and the weight of each query-related metric information.

[0119] During implementation, the target feature matrix of the SQL query request information can be constructed according to the process shown in Figure 5 as follows:

[0120] S51. Construct the feature matrix of the SQL query request information within each preset time period according to each query-related metric information after normalizing the SQL query request information within each preset time period and the weight of each corresponding query-related metric information.

[0121] Specifically during implementation, the feature matrix of the SQL query request information within each preset time period can be constructed through the following formula:

[0122]

[0123] where z t represents the feature matrix of the SQL query request information within the t-th preset time period, t = 1, 2, ……, T, and T represents the number of the preset time periods;

[0124] I(x1)~I(x n ) represents the weights of the 1st to n-th query-related metric information of the SQL query request information, and n represents the number of query-related metric information;

[0125] x′ 1,t ~x′ n,t represents the 1st to n-th query-related metric information after normalizing the SQL query request information within the t-th preset time period.

[0126] In a possible implementation manner, preset weights can also be set in advance for each query-related metric information of different SQL query request information, then I(x1)~I(x n)The preset weights of the 1st to nth query-related metric information for querying information using SQL can be adopted, and the embodiments of the present application do not limit this.

[0127] S52. Construct a target feature matrix of the SQL query request information according to the feature matrix corresponding to the SQL query request information in each preset time period.

[0128] Specifically in implementation, the target feature matrix of the SQL query request information is constructed by the following formula:

[0129]

[0130] Where Z represents the target feature matrix of the SQL query request information;

[0131] z1 to z T represent the feature matrix of the SQL query request information in the 1st to Tth preset time periods.

[0132] The target feature matrix Z of the SQL query request information is a three-dimensional time series matrix.

[0133] In the embodiments of the present application, based on the feature importance calculation method of the random forest, the error changes brought by the feature splitting of multiple decision trees in the random forest algorithm are analyzed, the influence of each feature on the performance of the SQL query request information is quantified, and a weighted feature matrix is constructed, effectively improving the accuracy of cold and hot data recognition of the cold and hot data prediction model.

[0134] S24. Input the target feature matrix into the cold and hot data prediction model to obtain the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse.

[0135] In specific implementation, the hot and cold data prediction model is obtained by training based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of business data corresponding to SQL query request information in an offline data warehouse. The heat of business data represents the frequency of the business data being queried. The three-dimensional convolutional neural network may include 3 three-dimensional convolutional layers, 2 pooling layers, and 1 fully connected layer, and may also be other structures. The embodiments of the present application do not limit this. When training the hot and cold data prediction model, the query-related index information of SQL query request information in each preset time period within several historical time periods can be used as training samples. The weights of the query-related index information of the sample SQL query request information and the construction of the target feature matrix of the sample SQL query request information can refer to the corresponding processes described above, which will not be elaborated here. During training, cross-entropy can be used as the loss function, and the Adam optimization algorithm can be used to train the three-dimensional convolutional neural network, adjust the parameters of the three-dimensional convolutional neural network, and evaluate and verify the model through indicators such as accuracy and recall until the model converges to obtain the trained hot and cold data prediction model.

[0136] In this step, for each SQL query request information, the constructed target feature matrix of the SQL query request information is input into the hot and cold data prediction model to obtain the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse, and corresponding heat labels are marked for each SQL query request information in the online analytical data warehouse according to the heat prediction result. If the heat prediction value of the business data corresponding to the SQL query request information is greater than or equal to the preset threshold Q, it is determined that the business data corresponding to the SQL query request information is hot data, and this SQL query request information is marked as "SQL query request corresponding to hot data". If the heat prediction value of the business data corresponding to the SQL query request information is less than the preset threshold Q, it is determined that the business data corresponding to the SQL query request information is cold data, and this SQL query request information is marked as "SQL query request corresponding to cold data". Among them, the preset threshold Q can be set by itself according to empirical values, such as it can be set to 0.6. The embodiments of the present application do not limit this.

[0137] In one implementation, the heat labels of each SQL query request information in the previous time period can be compared with the heat labels of the corresponding SQL query request information in the next time period predicted currently. If they are the same, there is no need to update the heat labels. If they are different, the heat labels corresponding to the previous time period are updated to the heat labels corresponding to the next time period predicted currently, thereby saving computing resources. Among them, the heat labels of each SQL query request information in the previous time period are predicted based on the query-related index information of each SQL query request information in the time period before the previous time period.

[0138] S25. Migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse.

[0139] In specific implementation, for each SQL query request information, if the business data corresponding to the SQL query request is updated in the next time period (such as new business data is added), then in the next time period, the business data with a heat prediction result of hot data is deduplicated and migrated from the offline data warehouse to the online analytical data warehouse for storage, and the business data with a heat prediction result of cold data is retained in the offline data warehouse.

[0140] In the next time period, when the user sends SQL query request information to the online analytical data warehouse through the client, after the pre-analysis database receives the SQL query request information sent by the client, it first queries the heat label of the SQL query request information. If it is determined that the heat label of the SQL query request information is "SQL query request corresponding to hot data", the business data corresponding to the SQL query request information is extracted from the local and returned to the client. If it is determined that the heat label of the SQL query request information is "SQL query request corresponding to cold data", the SQL query request information is forwarded to the offline data warehouse, and the offline data warehouse queries the corresponding business data according to the received SQL query request information and returns it to the client. The hot and cold data in the next time period are stored in different types of data warehouses respectively, so that the client can accurately access the hot and cold data and improve the query efficiency of the hot and cold data.

[0141] The hot and cold data recognition method, device, electronic device, and storage medium provided by the embodiments of the present application obtain the SQL query request information and its query-related metric information in the online analytical data warehouse during each preset time period in the previous time period. The query-related metrics of the SQL query request information include static query metrics and time metrics. For each SQL query request information, according to the query-related metric information of the SQL query request information in each preset time period, determine the weight of each query-related metric information of the SQL query request information. According to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information, construct the target feature matrix of the SQL query request information. Input the target feature matrix into the hot and cold data prediction model to obtain the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is trained based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse. The heat of the business data represents the frequency of the business data being queried. Migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse. In the embodiments of the present application, multi-dimensional features are extracted from the historical SQL query information including static query metrics and time metrics in the online analytical data warehouse according to a preset time period, and the target feature matrix of the SQL query request information is constructed according to the importance of different dimensional features (i.e., the weight of each query-related metric information). Based on the trained hot and cold data prediction model, predict the heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse in the next time period. Furthermore, migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the cold data. In the prior art, the strategy of identifying hot and cold data based on simple static metrics such as access frequency does not consider the time characteristics of data access, resulting in low identification accuracy. Compared with the prior art, the hot and cold data prediction model in the present application is trained using a three-dimensional convolutional neural network, and a three-dimensional convolutional kernel is used to capture the dynamic trend of the SQL query access pattern in the time dimension, improving the accuracy of hot and cold data identification. Moreover, different from the traditional partition storage method, the present application realizes the dynamic separation storage of hot data and cold data, stores the identified hot data in the online analytical data warehouse, and stores the cold data in the offline data warehouse, so that the client can accurately access hot and cold data, improving the query efficiency of the data.

[0142] Based on the same inventive concept, the embodiments of the present application also provide a hot and cold data recognition device. Since the principle of solving problems by the above hot and cold data recognition device is similar to that of the above hot and cold data recognition method, the implementation of the above device can refer to the implementation of the method, and the repeated parts will not be described again.

[0143] As shown in Figure 6 , it is a schematic structural diagram of the hot and cold data recognition device provided by an embodiment of the present application. The hot and cold data recognition device can be applied to a data analysis platform 101 as shown in Figure 1 . The device may include:

[0144] An acquisition module 61, configured to acquire SQL query request information and its query-related metric information in the online analysis data warehouse for each preset time period in the previous time period. The query-related metrics of the SQL query request information include static query metrics and time metrics;

[0145] A determination module 62, configured to, for each SQL query request information, determine the weight of each query-related metric information of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period;

[0146] A feature matrix construction module 63, configured to construct a target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information;

[0147] An identification module 64, configured to input the target feature matrix into a hot and cold data prediction model to obtain a heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is trained based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse. The heat of the business data represents the frequency of the business data being queried;

[0148] A processing module 65, configured to migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analysis data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse.

[0149] In an implementation manner, the static query metrics at least include: SQL query request information, query count information, scanned row count information of the business data corresponding to the SQL query request, query failure count information, and memory usage information of the data queried according to the SQL query request; the time metrics at least include: query time information and response duration information of the SQL query request;

[0150] The determining module 62 is specifically configured to normalize each query-related metric information of the SQL query request information within each preset time period for each SQL query request information; and determine the weight of each query-related metric information of the SQL query request information according to the normalized query-related metric information and the random forest model.

[0151] In one implementation, the determining module 62 is specifically configured to input each normalized query-related metric information of the SQL query request information within each preset time period into the random forest model respectively, to obtain a heat prediction value of the SQL query request information within each preset time period. The random forest model is used to predict the heat of the SQL query request information based on the query-related metric information of the SQL query request information. The heat of the SQL query request information represents the frequency of the SQL query request information being used to query business data; determine the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting according to the heat prediction value and the true heat value of the SQL query request information within each preset time period; and determine the weight of the corresponding query-related metric information of the SQL query request information according to the loss change amount predicted by the random forest model when each query-related metric of the SQL query request within each preset time period is used for splitting.

[0152] In one implementation, the determining module 62 is specifically configured to calculate the weight of each query-related metric information of the SQL query request information through the following formula:

[0153]

[0154] where, I(x i ) represents the weight of the i-th query-related metric information of the SQL query request information;

[0155] represents the loss change amount predicted by the random forest model when the i-th query-related metric of the SQL query request information within the t-th preset time period is used for splitting, t = 1, 2, ……, T, T represents the number of the preset time periods, y t represents the true heat value of the SQL query request information within the t-th preset time period, represents the heat prediction value of the SQL query request information within the t-th preset time period, X t =[x′ 1,t ,x′ 2,t ,……,x′ n,t , f(X t) is the objective function of the random forest model, X t represents the combined query-related metric information after normalization of the SQL query request information within the t-th preset time period, x′ 1,t ~x′ n,t represents the 1st to n-th query-related metric information after normalization of the SQL query request information within the t-th preset time period, where n represents the number of query-related metric information.

[0156] In one implementation manner, the feature matrix construction module 63 is specifically configured to construct a feature matrix of the SQL query request information within each preset time period according to each query-related metric information after normalization of the SQL query request information within each preset time period and the weights of the corresponding query-related metric information; and construct a target feature matrix of the SQL query request information according to the feature matrices corresponding to the SQL query request information within each preset time period.

[0157] In one implementation manner, the feature matrix construction module 63 is specifically configured to construct a feature matrix of the SQL query request information within each preset time period through the following formula:

[0158]

[0159] where z t represents the feature matrix of the SQL query request information within the t-th preset time period, t = 1, 2, ……, T, and T represents the number of preset time periods;

[0160] I(x1)~I(x n ) represents the weights of the 1st to n-th query-related metric information of the SQL query request information, where n represents the number of query-related metric information;

[0161] x′ 1,t ~x′ n,t represents the 1st to n-th query-related metric information after normalization of the SQL query request information within the t-th preset time period.

[0162] In one implementation manner, the feature matrix construction module 63 is specifically configured to construct a target feature matrix of the SQL query request information through the following formula:

[0163]

[0164] where Z represents the target feature matrix of the SQL query request information;

[0165] z1~z T represents the feature matrices of the SQL query request information within the 1st to T-th preset time periods.

[0166] Based on the same inventive concept, an embodiment of the present application further provides an electronic device 700. Referring to Figure 7 as shown, the electronic device 700 is used to implement the hot and cold data recognition method described in the above method embodiment. The electronic device 700 in this embodiment may include: a memory 701, a processor 702, and a computer program stored in the memory and executable on the processor, such as a hot and cold data recognition program. When the processor executes the computer program, the steps in the above various hot and cold data recognition method embodiments are implemented.

[0167] In the embodiment of the present application, the specific connection medium between the above-mentioned memory 701 and processor 702 is not limited. In the embodiment of the present application Figure 7 it is shown that the memory 701 and the processor 702 are connected by a bus 703. The bus 703 is represented by a thick line in Figure 7 The connection manners of other components are only for illustrative purposes and are not to be construed as limiting. The bus 703 may be divided into an address bus, a data bus, a control bus, etc. For the sake of convenience of representation, Figure 7 only a thick line is used to represent it in

[0168] The memory 701 may be a volatile memory, such as a random-access memory (RAM); the memory 701 may also be a non-volatile memory, such as a read-only memory, a flash memory, a hard disk drive (HDD), or a solid-state drive (SSD), or the memory 701 is any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory 701 may be a combination of the above memories.

[0169] The processor 702 is used to implement the hot and cold data recognition method provided by the embodiment of the present application.

[0170] An embodiment of the present application further provides a computer-readable storage medium, storing computer-executable instructions required to be executed by the above-mentioned processor, which includes a program required to be executed by the above-mentioned processor.

[0171] In some possible embodiments, various aspects of the hot and cold data identification method provided in this application can also be implemented in the form of a program product, which includes program code. When the program product runs on an electronic device, the program code is used to cause the electronic device to execute the steps in the hot and cold data identification method according to various exemplary embodiments of this application described above in this specification.

[0172] Those skilled in the art should understand that the embodiments of this application can be provided as a method, a device, or a computer program product. Therefore, this application can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, this application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0173] This application is described with reference to the flowcharts and / or block diagrams of methods, devices (apparatus), and computer program products according to the embodiments of this application. It should be understood that each flow and / or block in the flowchart and / or block diagram, and the combination of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing devices generate a device for implementing the specified functions in one Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0174] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory generate a manufactured article including an instruction device, and the instruction device implements the specified functions in one Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0175] These computer program instructions can also be loaded onto a computer or other programmable data processing device, so that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process. Thus, the instructions executed on the computer or other programmable device provide steps for implementing the specified functions in one Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0176] Although the preferred embodiments of the present application have been described, additional changes and modifications can be made to these embodiments by those skilled in the art once they learn the basic creative concept. Therefore, the appended claims are intended to be interpreted to include the preferred embodiments as well as all changes and modifications falling within the scope of the present application.

[0177] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalent technologies, the present application is also intended to include these modifications and variations.

Claims

1. A method for identifying hot and cold data, characterized in that, Including: Obtain the SQL query request information and its query-related metric information in the online analytical data warehouse during each preset time period in the previous time period. The query-related metrics of the SQL query request information include static query metrics and time metrics; For each piece of SQL query request information, determine the weight of each query-related metric information of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period; Construct a target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information; Input the target feature matrix into a hot and cold data prediction model to obtain a heat prediction result of the business data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is trained based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of the business data corresponding to the SQL query request information in the offline data warehouse, and the heat of the business data represents the frequency of the business data being queried; Migrate the business data with a heat prediction result of hot data from the offline data warehouse to the online analytical data warehouse for storage, and retain the business data with a heat prediction result of cold data in the offline data warehouse.

2. The method according to claim 1, characterized in that, The static query metrics at least include: SQL query request information, query count information, scanned row count information of the business data corresponding to the SQL query request, query failure count information, and memory usage information corresponding to querying data according to the SQL query request; the time metrics at least include: query time information and response duration information of the SQL query request; For each piece of SQL query request information, determining the weight of each query-related metric information of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period specifically includes: For each piece of SQL query request information, normalize each query-related metric information of the SQL query request information in each preset time period; Determine the weight of each query-related metric information of the SQL query request information according to the normalized query-related metric information and a random forest model.

3. The method according to claim 2, wherein Determining the weight of each query-related metric information of the SQL query request information according to the normalized query-related metric information and a random forest model specifically includes: Input the normalized query-related metric information of the SQL query request information in each preset time period into the random forest model respectively to obtain a heat prediction value of the SQL query request information in each preset time period. The random forest model is used to predict the heat of the SQL query request information based on the query-related metric information of the SQL query request information, and the heat of the SQL query request information represents the frequency of the SQL query request information being used to query business data; Determine the loss change amount predicted by the random forest model when each query-related metric of the SQL query request is used for splitting according to the predicted heat value and the actual heat value of the SQL query request information in each preset time period; Determine the weight of the corresponding query-related metric information of the SQL query request information according to the loss change amount predicted by the random forest model when each query-related metric of the SQL query request is used for splitting in each preset time period.

4. The method according to claim 3, characterized in that Determine the weight of the corresponding query-related metric information of the SQL query request information according to the loss change amount predicted by the random forest model when each query-related metric of the SQL query request is used for splitting in each preset time period, specifically including: Calculate the weight of each query-related metric information of the SQL query request information through the following formula: Among them, I(x i ) represents the weight of the i-th query-related metric information of the SQL query request information; Denote the loss change amount predicted by the random forest model when the $i$-th query-related metric of the SQL query request information in the $t$-th preset time period is used for splitting, where $t = 1, 2, \ldots, T$, and $T$ represents the number of the preset time periods, $y$ t Denote the true value of the popularity of the SQL query request information in the $t$-th preset time period, Denote the predicted value of the popularity of the SQL query request information in the $t$-th preset time period, X t $=$ [x′ 1,t , x′ 2,t , \ldots, x′ n,t , and $f(X$ t ) is the objective function of the random forest model, $X$ t Denote the combined query-related metric information after normalization of the SQL query request information in the $t$-th preset time period, and $x′$ 1,t $\sim x′$ n,t Denote the 1st to $n$-th query-related metric information after normalization of the SQL query request information in the $t$-th preset time period, where $n$ represents the number of the query-related metric information.

5. The method according to any one of claims 2 to 4, characterized in that Construct the target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information, specifically including: Construct the feature matrix of the SQL query request information in each preset time period according to the normalized query-related metric information of the SQL query request information in each preset time period and the weight of each corresponding query-related metric information; Construct the target feature matrix of the SQL query request information according to the feature matrices corresponding to the SQL query request information in each preset time period.

6. The method according to claim 5, characterized in that, Construct the feature matrix of the SQL query request information in each preset time period according to the normalized query-related metric information of the SQL query request information in each preset time period and the weight of each corresponding query-related metric information, specifically including: Construct the feature matrix of the SQL query request information in each preset time period through the following formula: Among them, z t represents the feature matrix of the SQL query request information in the t-th preset time period, where t = 1, 2, ……, T, and T represents the number of the preset time periods; I(x1)~I(x n ) represents the weights of the 1st to nth query-related metric information of the SQL query request information, where n represents the number of query-related metric information; x' 1,t ~x' n,t represent the 1st to nth query-related metric information after normalization of the SQL query request information in the t-th preset time period.

7. The method according to claim 6, characterized in that Construct the target feature matrix of the SQL query request information according to the feature matrices corresponding to the SQL query request information in each preset time period, specifically including: Construct the target feature matrix of the SQL query request information through the following formula: where Z represents the target feature matrix of the SQL query request information; z1 to z T It represents the feature matrix of the SQL query request information within the 1st to Tth preset time periods.

8. A cold and hot data recognition device, characterized in that, including: An acquisition module, configured to acquire the SQL query request information and its query-related metric information of the online analytical data warehouse in each preset time period of the previous time cycle, and the query-related metrics of the SQL query request information include static query metrics and time metrics; A determination module, configured to, for each piece of SQL query request information, determine the weight of each query-related metric information of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period; A feature matrix construction module, configured to construct the target feature matrix of the SQL query request information according to the query-related metric information of the SQL query request information in each preset time period and the weight of each query-related metric information; An identification module, configured to input the target feature matrix into a hot and cold data prediction model to obtain a heat prediction result of service data corresponding to the SQL query request information in the offline data warehouse. The hot and cold data prediction model is obtained by training based on a three-dimensional convolutional neural network. The hot and cold data prediction model is used to predict the heat of service data corresponding to SQL query request information in the offline data warehouse, and the heat of the service data represents the frequency of the service data being queried; A processing module, configured to migrate service data with a heat prediction result of hot data from the offline data warehouse to the online analysis data warehouse for storage, and retain service data with a heat prediction result of cold data in the offline data warehouse.

9. An electronic device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the hot and cold data identification method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the steps in the hot and cold data identification method according to any one of claims 1 to 7.

Images