Data updating method and system, computer equipment and medium

Abstract

The invention discloses a data updating method and system, computer equipment and a medium, and relates to the technical field of data analysis, and the disclosed data updating method comprises the following steps: receiving offline data, and storing the offline data in a first storage engine of a click home; receiving the real-time data, and storing the real-time data into a second storage engine of the click home; and when a query request is received, extracting the offline data in the first storage engine and the real-time data in the second storage engine, and performing data updating based on the offline data and the real-time data to obtain updated data. The technical scheme of the invention aims to solve the technical problem of how to improve the timeliness of data updating when a click home model is used.

Description

Technical Field

[0001] The present application relates to the field of data analysis technology, and in particular to a data updating method, system, computer device and medium. Background Art

[0002] Nowadays, the commonly used ClickHouse application model adopts the following two architectures when updating data: one is pure offline batch data import, that is, the data of the previous day is imported into ClickHouse through batch jobs every day and provided to the business for analysis; the other is to use streaming big data models such as Flink, accumulate statistics through windows in computing models such as Flink, and then refresh the results to ClickHouse regularly to reduce the frequency of updates.

[0003] However, these two commonly used clickhouse application architectures have the following defects that are difficult to overcome in actual applications: The above-mentioned pure offline computing model lacks real-time data import, and the timeliness of data is mostly T-1, resulting in poor timeliness of data. For businesses that are sensitive to data, pure offline computing models are difficult to meet their actual data analysis needs. The above-mentioned method of using streaming big data models such as flink requires maintaining large wide tables in clickhouse. However, generally speaking, the update frequency of different fields is different. In real-time models such as flink, some relatively large dimension tables need to be maintained. If the dimension table is updated, the dimension table also needs to be updated at the same time. Finally, the real-time model associates the real-time updated field with the dimension table to obtain the final result and then refreshes it to clickhouse. As a result, the data received by clickhouse also has the problem of poor timeliness.

[0004] Therefore, how to improve the timeliness of data updates when using the clickhouse model is a technical problem that needs to be solved by technicians in this field. Summary of the invention

[0005] The main purpose of this application is to provide a data update method, system, computer device and medium, aiming to solve the technical problem of how to improve the timeliness of data update when using the clickhouse model.

[0006] To achieve the above purpose, the present application proposes a data updating method, which comprises:

[0007] Receive offline data, and store the offline data in a first storage engine of clickhouse;

[0008] Receive real-time data and store the real-time data in the second storage engine of clickhouse;

[0009] When a query request is received, offline data in the first storage engine and real-time data in the second storage engine are extracted, and data is updated based on the offline data and the real-time data to obtain updated data.

[0010] In one embodiment, the step of updating data based on the offline data and the real-time data to obtain updated data includes:

[0011] Extracting field names from the offline data and the real-time data, and determining respective update times corresponding to respective field names in the offline data and the real-time data;

[0012] Determine the latest update time corresponding to each of the field names among the update times corresponding to each of the field names;

[0013] Determining, according to each of the latest update times, the latest value corresponding to each of the field names in the offline data or the real-time data;

[0014] Update the values ​​corresponding to the field names in ClickHouse to the latest values ​​to obtain updated data.

[0015] In one embodiment, the step of storing the offline data in the first storage engine of clickhouse includes:

[0016] Determine a snapshot date of the offline data, and determine a target storage area corresponding to the snapshot date in the first storage engine of clickhouse;

[0017] The offline data is stored in the target storage area.

[0018] In one embodiment, after the step of storing the offline data in the first storage engine of clickhouse, the method further comprises:

[0019] Obtaining an expiration date of the offline data according to the snapshot date of the offline data and a preset validity period;

[0020] When it is detected that the current date is the same as the expiration date, the offline data in the target storage area is cleared, and the corresponding relationship between the target storage area and the snapshot date is cleared.

[0021] In one embodiment, the step of storing the real-time data in the second storage engine of ClickHouse includes:

[0022] Extracting real-time field names from the real-time data, and extracting data values ​​corresponding to each of the real-time field names;

[0023] Among the data values, the data values ​​with empty content are marked, and after the marking is completed, the data values ​​are stored in the second storage engine of clickhouse.

[0024] In one embodiment, before the step of extracting offline data in the first storage engine and real-time data in the second storage engine, the method further includes:

[0025] Extracting the real-time data in the second storage engine, and extracting the real-time field name in the real-time data;

[0026] In the real-time data, determining the real-time update time corresponding to each of the real-time field names, wherein the real-time update time is the data receiving time of the data value corresponding to each of the real-time field names without a mark;

[0027] In each of the real-time update times, determine the latest real-time update time corresponding to each of the real-time field names;

[0028] According to each of the latest real-time update times, expired real-time data is cleared in the second storage engine.

[0029] In one embodiment, the first storage engine is a replicateMergeTree engine, and the second storage engine is an AggragationMergeTree engine.

[0030] In addition, to achieve the above purpose, the present application also proposes a data updating system, the data updating system comprising:

[0031] A first storage module is used to receive offline data and store the offline data in a first storage engine of clickhouse;

[0032] The second storage module is used to receive real-time data and store the real-time data in the second storage engine of clickhouse;

[0033] The data update module is used to extract the offline data in the first storage engine and the real-time data in the second storage engine when receiving a query request, and update the data based on the offline data and the real-time data to obtain updated data.

[0034] In addition, to achieve the above objectives, the present application also proposes a computer device, which includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the data update method described above.

[0035] In addition, to achieve the above-mentioned purpose, the present application also proposes a storage medium, which is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, the steps of the data updating method described above are implemented.

[0036] In an embodiment of the present application, the present application receives offline data and stores the offline data in a first storage engine of clickhouse; and receives real-time data and stores the real-time data in a second storage engine of clickhouse, so that offline data and real-time data can be stored separately, and data is not updated when storing data; then, when a query request is received, the offline data in the first storage engine and the real-time data in the second storage engine are extracted, and the data is updated based on the offline data and the real-time data to obtain updated data, thereby ensuring that the latest and most accurate data can be obtained at the time of query based on the method of updating data at the time of query, thereby improving the timeliness of data update. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0038] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0039] Figure 1 A flowchart of the first embodiment of the data updating method of this application is provided;

[0040] Figure 2 This is a schematic diagram of offline data partition storage according to an embodiment of the data updating method of the present application;

[0041] Figure 3 A schematic diagram of the corresponding relationship between the data storage engine and the data updating method according to an embodiment of the present application;

[0042] Figure 4 This is a schematic diagram of fact data fusion in accordance with an embodiment of the data updating method of the present application;

[0043] Figure 5 This is a schematic diagram of real-time data fusion of offline data in a specific embodiment of the data updating method of this application;

[0044] Figure 6 This is a schematic diagram of the module structure of the data updating system according to an embodiment of the present application;

[0045] Figure 7 Schematic diagram of the device structure of the hardware operating environment involved in the data updating method in the embodiment of the present application.

[0046] The purpose, features and advantages of this application will be further described in conjunction with the embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0047] It should be understood that the specific embodiments described herein are only used to explain the technical solutions of the present application and are not used to limit the present application.

[0048] In order to better understand the technical solution of the present application, a detailed description will be given below in conjunction with the accompanying drawings and specific implementation methods.

[0049] Nowadays, the commonly used clickhouse application model adopts the following two architectures when updating data: one is pure offline batch data import, that is, the data of the previous day is imported into clickhouse through batch jobs every day and provided to the business for analysis; the other is to use streaming big data models such as flink, accumulate statistics through windows in computing models such as flink, and then refresh the results to clickhouse regularly to reduce the frequency of updates.

[0050] However, these two commonly used clickhouse application architectures have the following defects that are difficult to overcome in actual applications: The above-mentioned pure offline computing model lacks real-time data import, and the timeliness of data is mostly T-1, resulting in poor timeliness of data. For businesses that are sensitive to data, pure offline computing models are difficult to meet their actual data analysis needs. The above-mentioned method of using streaming big data models such as flink requires maintaining large wide tables in clickhouse. However, generally speaking, the update frequency of different fields is different. In real-time models such as flink, some relatively large dimension tables need to be maintained. If the dimension table is updated, the dimension table also needs to be updated at the same time. Finally, the real-time model associates the real-time updated field with the dimension table to obtain the final result and then refreshes it to clickhouse. As a result, the data received by clickhouse also has the problem of poor timeliness.

[0051] Therefore, how to improve the timeliness of data updates when using the clickhouse model is a technical problem that needs to be solved by technicians in this field.

[0052] To solve the above problems, in an embodiment of the present application, the present application receives offline data and stores the offline data in the first storage engine of clickhouse; and receives real-time data and stores the real-time data in the second storage engine of clickhouse, so that offline data and real-time data can be stored separately, and data is not updated when storing data; then, when a query request is received, the offline data in the first storage engine and the real-time data in the second storage engine are extracted, and the data is updated based on the offline data and the real-time data to obtain updated data, so that based on the method of updating data at the time of query, it can be ensured that the latest and most accurate data can be obtained at the time of query, thereby improving the timeliness of data update.

[0053] It should be noted that the execution subject of the data update method of the present application may be a computer device with data processing, network communication and program running functions, such as a server, a computer, etc. The following takes a server as an example to illustrate various embodiments of the data update method of the present application.

[0054] Based on this, the present application embodiment provides a data updating method, referring to Figure 1 , Figure 1 This is a flow chart of the first embodiment of the data updating method of the present application.

[0055] In this embodiment, the data updating method includes steps S10 to S30:

[0056] Step S10, receiving offline data, and storing the offline data in the first storage engine of clickhouse;

[0057] Step S20, receiving real-time data, and storing the real-time data in the second storage engine of clickhouse;

[0058] It should be noted that the above offline data and real-time data are data that can be obtained by ClickHouse. In this embodiment, offline data and real-time data are stored using different storage engines. In addition, in this embodiment, after receiving offline data or real-time data, the offline data or real-time data is only written to the storage engine, and the data update step is not performed.

[0059] Step S30, when a query request is received, extracting offline data in the first storage engine and real-time data in the second storage engine, and updating data based on the offline data and the real-time data to obtain updated data.

[0060] It should be noted that the query request refers to a network request including the content to be queried. In a feasible implementation, the query request may be to query the current value of a certain field.

[0061] In this embodiment, after receiving the query request, the received data will be extracted from the first storage engine and the second storage engine, and then the data in ClickHouse will be updated based on the extracted data, thereby ensuring that the information obtained by the query is accurate.

[0062] In a feasible implementation manner, the above step S30 includes:

[0063] Step S301, extracting field names from the offline data and the real-time data, and determining update times corresponding to the field names in the offline data and the real-time data;

[0064] It should be noted that offline data and real-time data include multiple field names and updated values ​​corresponding to each field name. For example, there is a data stream in real-time data, the field names in the data stream include aa, bb, cc, and the data stream also includes updated values ​​corresponding to aa, bb, cc.

[0065] It is understandable that after extracting the field names in the offline data and the real-time data, it is also possible to query the offline data and the real-time data based on the field names to obtain the update times corresponding to the respective field names.

[0066] For example, when there are multiple data streams in real-time data and offline data that update the value corresponding to the field name aa, a query can be performed based on the keyword "aa" to find multiple update times corresponding to "aa".

[0067] Step S302, determining the latest update time corresponding to each of the field names among the update times corresponding to each of the field names;

[0068] It is understandable that after obtaining the update times corresponding to the respective field names, the update times corresponding to the same field name can be compared to determine the latest update time corresponding to the respective field names.

[0069] Step S303, determining the latest value corresponding to each of the field names in the offline data or the real-time data according to the latest update time;

[0070] It is understandable that since the latest update time may come from offline data or real-time data, after obtaining the latest update time corresponding to each field name, the latest value corresponding to each field name can be found in the offline data or real-time data based on the latest update time.

[0071] Step S304, update the values ​​corresponding to the field names in clickhouse to the latest values ​​to obtain updated data.

[0072] It is understandable that after the latest values ​​corresponding to each field name are obtained after the real-time data and offline data are integrated, the values ​​corresponding to each field name in ClickHouse can be updated according to the latest values, so as to obtain updated data. Then, based on the query request, the query result corresponding to the query request can be queried in the updated data.

[0073] Specifically, in a feasible implementation, the code for integrating real-time data and offline data is as follows:

[0074]

[0075]

[0076]

[0077] The logic of the above code is as follows: First, create a view called CASE_INFO_MV_NEWST, the purpose of which is to merge real-time data (CASE_INFO_REALTIME table) and offline data (CASE_INFO_OFFLINE_MV table) and provide the latest information. Then, use the UNIONALL operation to merge the data of the two tables. Specifically: the CASE_INFO_REALTIME table contains real-time data, and the CASE_INFO_OFFLINE_MV table contains offline T-1 information, both of which are located in the plalysis database. Then, for the merged data, use the argMax function to find the latest value of each field (CUST_NUM, CUST_NAME, ALCT_DATE, FOLLOW_TIMES, SPECIAL_CASE_FLAG) for each CASE_ID and DW_SNSH_DT combination. The argMax function is used with a conditional judgment to ensure that only non-empty or non-zero LAST_MODIFY_TIME is selected. Finally, the results are grouped according to DW_SNSH_DT and CASE_ID so that each group will contain the latest information for a specific date and case ID. It should be noted that the above view is used for data aggregation and analysis, providing the latest status of each case at a specific point in time, including customer number, customer name, assignment date, number of follow-ups, and special case flags. Therefore, the above code can achieve the fusion of real-time data and offline data and the effect of displaying the latest data in the view.

[0078] In an embodiment of the present application, the present application receives offline data and stores the offline data in a first storage engine of clickhouse; and receives real-time data and stores the real-time data in a second storage engine of clickhouse, so that offline data and real-time data can be stored separately, and data is not updated when storing data; then, when a query request is received, the offline data in the first storage engine and the real-time data in the second storage engine are extracted, and the data is updated based on the offline data and the real-time data to obtain updated data, thereby ensuring that the latest and most accurate data can be obtained at the time of query based on the method of updating data at the time of query, thereby improving the timeliness of data update.

[0079] Furthermore, based on the first embodiment of the above-mentioned data updating method, a second embodiment of the data updating method of the present application is proposed.

[0080] In this embodiment, the above step S10 includes:

[0081] Step S101, determining a snapshot date of the offline data, and determining a target storage area corresponding to the snapshot date in the first storage engine of clickhouse;

[0082] It should be noted that the snapshot date refers to the specific date when the data is captured.

[0083] In this embodiment, a plurality of storage areas are provided in the first storage engine, and the target storage area refers to a storage area preset corresponding to the snapshot date.

[0084] Step S102: storing the offline data in the target storage area.

[0085] For example, please refer to Figure 2 , Figure 2 This is a schematic diagram of offline data partition storage in an embodiment of the data update method of this application. Figure 2 In the example, if the snapshot date of the offline data is 2024 / 9 / 18, the offline data is stored in the storage area corresponding to 2024 / 9 / 18 in the first storage engine; if the snapshot date of the offline data is 2024 / 9 / 17, the offline data is stored in the storage area corresponding to 2024 / 9 / 17 in the first storage engine. In addition, in order to facilitate the output of the latest offline data received, the latest offline data can also be displayed on the view. When there is a need to access data, each physical storage node Node can be accessed through the scatter function and gather function.

[0086] In this embodiment, the present application can avoid the problem of data being difficult to clean up due to the mixing of data with different snapshot dates in the same storage partition by partitioning the offline data.

[0087] Furthermore, after the above step S10, the method further includes:

[0088] Step S100, obtaining an expiration date of the offline data according to the snapshot date of the offline data and a preset validity period;

[0089] It is understandable that the storage space in the storage engine is limited. Therefore, the validity period of offline data in the storage engine can be preset, and then the snapshot date of the offline data is added to the validity period to obtain the expiration date of the offline data.

[0090] Step S200: when it is detected that the current date is the same as the expiration date, the offline data in the target storage area is cleared, and the corresponding relationship between the target storage area and the snapshot date is cleared.

[0091] It is understandable that after obtaining the expiration date corresponding to each offline data, the steps of comparing the current date with each expiration date and clearing the expired offline data can be continuously performed. In addition, to facilitate the recycling of the storage area, after clearing the expired offline data, the corresponding relationship between the storage area corresponding to the expired offline data and the snapshot date corresponding to the expired offline data can also be cleared.

[0092] Therefore, this embodiment can reasonably clear out expired offline data by setting the effective duration of offline data, thereby improving the availability of the storage area. In addition, in a feasible implementation, in order to facilitate the output of offline data, an offline view can also be established based on the first storage engine, wherein the offline view is used to display the latest received offline data.

[0093] In a feasible implementation manner, the above step S20 includes:

[0094] Step S201, extracting the real-time field names in the real-time data, and extracting the data values ​​corresponding to each of the real-time field names;

[0095] It should be noted that the real-time field name refers to the field name in the real-time data. In this embodiment, before storing the implementation data, it is also necessary to extract the real-time field name and the data value corresponding to each real-time field name, and process the data value so as to perform data cleaning after storing the real-time data.

[0096] Step S202: Among the data values, mark the data values ​​with empty content, and after marking, store the data values ​​in the second storage engine of clickhouse.

[0097] It is understandable that in real-time data, there may be some real-time field names whose corresponding values ​​are empty. For example, in a certain data stream of real-time data, the updated value (data value) corresponding to the real-time field name "aa" is empty (for example: {aa: ""}), which indicates that the data stream has not updated the value corresponding to "aa". Therefore, data values ​​with empty content need to be marked to indicate that the data value has not updated the value of the real-time field name. Then, after the marking is completed, each data value can be stored in the second storage engine.

[0098] For example, for data values ​​that do not have updates, the identifier null can be used to indicate that there is no update. Thus, {aa: ""} in the above data stream will be marked as {aa: null}, and then the marked data will be stored in the second storage engine.

[0099] In this embodiment, the present application can distinguish the part used for updating data and the part not used for updating data by marking the data values ​​not used for updating data, thereby facilitating identification of the latest value corresponding to the real-time field name.

[0100] In a feasible implementation manner, before the above step S30, the method further includes:

[0101] Step S300, extracting the real-time data in the second storage engine, and extracting the real-time field name in the real-time data;

[0102] It should be noted that, since the timing of fusing offline data and real-time data in this application is "when receiving a query request", before receiving a query request, real-time data and offline data are both stored separately. However, it is understandable that the volume of real-time data is huge. If the time interval between two adjacent query requests is long, a large amount of real-time data will exist in the second storage engine. Therefore, the data in the second storage engine also needs to be cleaned up regularly.

[0103] In this embodiment, the first step of cleaning the real-time data is to extract the real-time data and extract the real-time field names in the real-time data.

[0104] Step S400, in the real-time data, determining the real-time update time corresponding to each of the real-time field names, wherein the real-time update time is the data receiving time of the data value without a mark corresponding to each of the real-time field names;

[0105] It is understandable that after obtaining the real-time field names in the real-time data, the real-time update time corresponding to each real-time field name can be queried based on the real-time field name. Since the data values ​​with marks in the real-time data are actually not used for data update, the data values ​​with marks will be excluded when querying the real-time update time.

[0106] Step S500, determining the latest real-time update time corresponding to each of the real-time field names in each of the real-time update times;

[0107] It is understandable that a real-time field name may correspond to multiple real-time update times. In order to determine the latest value corresponding to the real-time field name, it is also necessary to compare the real-time update times corresponding to the same real-time field name to determine the latest real-time update time.

[0108] Step S600: Clear outdated real-time data in the second storage engine according to the latest real-time update time.

[0109] In this embodiment, after determining the latest real-time update time corresponding to each real-time field name, the latest value corresponding to each real-time field name can be further determined. That is, before being merged with offline data, the real-time data will also be merged by itself. Thus, the real-time data can be divided into the latest data and expired data, and then the expired data in the real-time data can be cleaned up to improve the availability of the second storage engine.

[0110] In addition, please refer to Figure 3 , Figure 3 This is a schematic diagram of the data storage engine corresponding relationship of an embodiment of the data updating method of this application. Figure 3 In the example, the first storage engine is the replicateMergeTree engine, and the second storage engine is the AggragationMergeTree engine.

[0111] Specifically, offline data is fully output every day through offline models such as spark+hive. Offline data overwrites the data of the previous day every day and is loaded into the offline replicateMergeTree table in clickhosue in small batches. Real-time data is entered into a separate table. When writing data, the updated fields store values, and the fields that are not updated are set to null. They are directly stored in the real-time table of the AggragationMergeTree engine without association and caching dimension tables. In addition, real-time data uses AggragationMergeTree as the table engine to ensure the eventual consistency of aggregation. At the same time, because real-time data may be continuously updated, when using AggragationMergeTree, when the merge starts inside clickhosue, the real-time data with an older last update time will be deleted, leaving only the real-time data with the latest last update time, reducing duplicate data in real-time updates.

[0112] For example, please refer to Figure 4 , Figure 4 This is a schematic diagram of fact data fusion in one embodiment of the data updating method of this application. Figure 4 In the example, there are 6 streams of streaming data that update different fields, with event_time as the update timestamp. When the first stream data is written, only field 1 has a value, and other fields are set to NULL. The second stream data only updates field 2, so only field 2 has a value, and other fields are set to NULL. When the real-time model is finally merged, the data of the 6 streams will be merged into the latest data.

[0113] Furthermore, based on the above-mentioned various embodiments of the data updating method of the present application, a specific embodiment of the data updating method of the present application is proposed.

[0114] Please refer to Figure 5 , Figure 5 This is a schematic diagram of real-time data fusion of offline data in a specific embodiment of the data updating method of this application. Figure 5 In , there are 6 real-time data streams and 1 offline data stream. Among them, real-time data stream 1 updates field 1 and field 3, real-time data stream 2 updates field 1 and field 2, real-time data stream 3 updates field 3 and field 5, real-time data stream 4 updates field 6, real-time data stream 5 updates field 3 and field 5, and real-time data stream 6 updates field 6. In the offline data stream, fields 1 to 6 are all updated. For field 1, if the last modification time of the offline data stream is less than the last modification time of the real-time data stream 1, and the last modification time of the real-time data stream 1 is less than the last modification time of the real-time data stream 2, then the latest value of field 1 is stream 2.value. If the time is offline < stream 1 < stream 2 < stream 3 < stream 4 < stream 5 < stream 6, the final result is: the latest value of field 1 is stream 2.value, the latest value of field 2 is stream 2.value, the latest value of field 3 is stream 5.value, the latest value of field 4 is offline.value, the latest value of field 5 is stream 5.value, and the latest value of field 6 is stream 6.value. Since the last update time (event_time) of data stream 6 is the largest, the last update time of the integrated data is the same as the last update time of data stream 6. Therefore, when querying through ClickHouse, the offline data and real-time data can be merged to ensure that the queried data has good timeliness.

[0115] The multi-stream fusion data update technology based on clickhouse proposed in this application can set different clickhouse table engines for real-time data and offline tables, and then, based on the design of expiration time + aggragationMergeTree, it can ensure that offline and real-time data will be gradually cleaned up. In addition, when processing real-time data, the real-time stream view can use the argMax function. When there is no merge, argMax can be sorted according to the last modification time field, and the NULL value is not included in the sorting, and finally the latest data with the last modification time is left. In this application, by combining the argMax syntax, it is avoided that when clickhouse does not trigger the merge operation, when there are duplicate data inside, the problem of divergence in the external display of data occurs. In addition, for different update streams, there are updated fields with values ​​set, and the design of setting the fields without updates to NULL avoids the requirement of removing the associated dimension table in the previous typical real-time model, greatly improves the efficiency of writing, delays the merge operation to read data, and maximizes the performance of clickhouse's own query.

[0116] Therefore, compared with traditional technologies, this application does not need to maintain dimension tables, and does not perform association or updates when writing, thus avoiding the impact of the poor performance of clickhouse's own association and update on the performance of data writing, and has high sequential writing performance; at the same time, since the dimension table is removed, there is no need to maintain the data in the dimension table, which improves the real-time performance of data updates. In addition, this application designs different mergeTree engines for offline data and real-time data, and sets different data cleaning strategies for offline and real-time data, so that offline and real-time data are cleaned in a timely manner, and a good balance is achieved in time and space within the limited data redundancy range.

[0117] It should be noted that the above examples are only used to understand the present application and do not constitute a limitation on the data updating method of the present application. More simple transformations based on this technical concept are all within the scope of protection of the present application.

[0118] This application also provides a data update system, please refer to Figure 6 , the data updating system comprises:

[0119] The first storage module 10 is used to receive offline data and store the offline data in the first storage engine of clickhouse;

[0120] The second storage module 20 is used to receive real-time data and store the real-time data in the second storage engine of clickhouse;

[0121] The data update module 30 is used to extract the offline data in the first storage engine and the real-time data in the second storage engine when receiving a query request, and update the data based on the offline data and the real-time data to obtain updated data.

[0122] In one embodiment, the data updating module 30 is further used for:

[0123] Extracting field names from the offline data and the real-time data, and determining respective update times corresponding to respective field names in the offline data and the real-time data;

[0124] Determine the latest update time corresponding to each of the field names among the update times corresponding to each of the field names;

[0125] Determining, according to each of the latest update times, the latest value corresponding to each of the field names in the offline data or the real-time data;

[0126] Update the values ​​corresponding to the field names in ClickHouse to the latest values ​​to obtain updated data.

[0127] In one embodiment, the first storage module 10 is further used for:

[0128] Determine a snapshot date of the offline data, and determine a target storage area corresponding to the snapshot date in the first storage engine of clickhouse;

[0129] The offline data is stored in the target storage area.

[0130] In one embodiment, the data updating system further includes:

[0131] An expiration time calculation module, used to obtain the expiration date of the offline data according to the snapshot date of the offline data and a preset validity period;

[0132] The offline data cleaning module is used to clear the offline data in the target storage area and clear the corresponding relationship between the target storage area and the snapshot date when detecting that the current date is the same as the expiration date.

[0133] In one embodiment, the second storage module 20 is further used for:

[0134] Extracting real-time field names from the real-time data, and extracting data values ​​corresponding to each of the real-time field names;

[0135] Among the data values, the data values ​​with empty content are marked, and after the marking is completed, the data values ​​are stored in the second storage engine of clickhouse.

[0136] In one embodiment, the data updating system further includes:

[0137] A real-time data extraction module, used to extract the real-time data in the second storage engine, and extract the real-time field name in the real-time data;

[0138] An update time determination module, used to determine, in the real-time data, the respective real-time update times corresponding to the respective real-time field names, wherein the real-time update time is the data receiving time of the data value corresponding to the respective real-time field names without a mark;

[0139] A time comparison module, used to determine the latest real-time update time corresponding to each of the real-time field names in each of the real-time update times;

[0140] The real-time data clearing module is used to clear outdated real-time data in the second storage engine according to the latest real-time update time.

[0141] The data update system provided by this application adopts the data update method in the above embodiment, which can solve the technical problem of how to improve the timeliness of data update when using the clickhouse model. Compared with the prior art, the beneficial effects of the data update system provided by this application are the same as the beneficial effects of the data update method provided by the above embodiment, and the other technical features of the data update system are the same as the features disclosed in the above embodiment method, which will not be repeated here.

[0142] The present application provides a computer device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the data updating method in the above-mentioned embodiment 1.

[0143] Reference below Figure 7 , which shows a schematic diagram of the structure of a computer device suitable for implementing an embodiment of the present application. Figure 7 The computer device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0144] like Figure 7As shown, the computer device may include a processing device 1001 (e.g., a central processing unit, a graphics processor, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM: Read Only Memory) 1002 or a program loaded from a storage device 1003 to a random access memory (RAM: Random Access Memory) 1004. In RAM1004, various programs and data required for the operation of the computer device are also stored. The processing device 1001, ROM1002, and RAM1004 are connected to each other through a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Generally, the following systems can be connected to the I / O interface 1006: an input device 1007 including, for example, a touch screen, a touch pad, a keyboard, a mouse, an image sensor, a microphone, an accelerometer, a gyroscope, etc.; an output device 1008 including, for example, a liquid crystal display (LCD: Liquid Crystal Display), a speaker, a vibrator, etc.; a storage device 1003 including, for example, a magnetic tape, a hard disk, etc.; and a communication device 1009. The communication device 1009 can allow the computer device to communicate with other devices wirelessly or by wire to exchange data. Although the figure shows a computer device with various systems, it should be understood that it is not required to implement or have all the systems shown. More or fewer systems can be implemented or have alternatively.

[0145] In particular, according to the embodiments disclosed in the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication device, or installed from a storage device 1003, or installed from a ROM 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the method of the embodiment disclosed in the present application are executed.

[0146] The computer device provided by this application adopts the data update method in the above embodiment, which can solve the technical problem of how to improve the timeliness of data update when using the clickhouse model. Compared with the prior art, the beneficial effects of the computer device provided by this application are the same as the beneficial effects of the data update method provided by the above embodiment, and the other technical features in the computer device are the same as the features disclosed in the previous embodiment method, which will not be repeated here.

[0147] It should be understood that the various parts disclosed in this application can be implemented by hardware, software, firmware or a combination thereof. In the description of the above embodiments, specific features, structures, materials or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0148] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

[0149] The present application provides a computer-readable storage medium having computer-readable program instructions (ie, computer programs) stored thereon, wherein the computer-readable program instructions are used to execute the data updating method in the above-mentioned embodiment.

[0150] The computer-readable storage medium provided in the present application may be, for example, a USB flash drive, but is not limited to electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, systems or devices, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, system or device. The program code contained on the computer-readable storage medium may be transmitted using any appropriate medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination of the above.

[0151] The computer-readable storage medium may be included in the computer device; or may exist independently without being installed in the computer device.

[0152] The above-mentioned computer-readable storage medium carries one or more programs. When the above-mentioned one or more programs are executed by a computer device, the computer device: receives offline data and stores the offline data in the first storage engine of clickhouse; receives real-time data and stores the real-time data in the second storage engine of clickhouse; when receiving a query request, extracts the offline data in the first storage engine and the real-time data in the second storage engine, and updates the data based on the offline data and the real-time data to obtain updated data.

[0153] Computer program code for performing the operations of the present application may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0154] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0155] The modules involved in the embodiments described in this application may be implemented by software or hardware, wherein the name of the module does not constitute a limitation on the unit itself in some cases.

[0156] The readable storage medium provided in this application is a computer-readable storage medium, which stores computer-readable program instructions (i.e., computer programs) for executing the above-mentioned data update method, and can solve the technical problem of how to improve the timeliness of data updates when using the clickhouse model. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as the beneficial effects of the data update method provided in the above-mentioned embodiment, and will not be repeated here.

[0157] The present application also provides a computer program product, including a computer program, which implements the steps of the above-mentioned data updating method when executed by a processor.

[0158] The computer program product provided in this application can solve the technical problem of how to improve the timeliness of data updates when using the clickhouse model. Compared with the prior art, the beneficial effects of the computer program product provided in this application are the same as the beneficial effects of the data update method provided in the above embodiment, which will not be repeated here.

[0159] The above descriptions are only some embodiments of the present application, and are not intended to limit the patent scope of the present application. All equivalent structural changes made using the contents of the present application specification and drawings under the technical concept of the present application, or direct / indirect applications in other related technical fields are included in the patent protection scope of the present application.

Claims

1. A data updating method, characterized in that: The data updating method comprises: Receive offline data, and store the offline data in a first storage engine of clickhouse; Receive real-time data and store the real-time data in the second storage engine of clickhouse; When a query request is received, offline data in the first storage engine and real-time data in the second storage engine are extracted, and data is updated based on the offline data and the real-time data to obtain updated data.

2. The data updating method according to claim 1, characterized in that: The step of updating data based on the offline data and the real-time data to obtain updated data includes: Extracting field names from the offline data and the real-time data, and determining respective update times corresponding to respective field names in the offline data and the real-time data; Determine the latest update time corresponding to each of the field names among the update times corresponding to each of the field names; Determining, according to each of the latest update times, the latest value corresponding to each of the field names in the offline data or the real-time data; Update the values ​​corresponding to the field names in ClickHouse to the latest values ​​to obtain updated data.

3. The data updating method according to claim 1, characterized in that: The step of storing the offline data in the first storage engine of clickhouse includes: Determine a snapshot date of the offline data, and determine a target storage area corresponding to the snapshot date in the first storage engine of clickhouse; The offline data is stored in the target storage area.

4. The data updating method according to claim 3, characterized in that: After the step of storing the offline data in the first storage engine of clickhouse, the method further includes: Obtaining an expiration date of the offline data according to the snapshot date of the offline data and a preset validity period; When it is detected that the current date is the same as the expiration date, the offline data in the target storage area is cleared, and the corresponding relationship between the target storage area and the snapshot date is cleared.

5. The data updating method according to claim 1, characterized in that: The step of storing the real-time data in the second storage engine of clickhouse includes: Extracting real-time field names from the real-time data, and extracting data values ​​corresponding to each of the real-time field names; Among the data values, the data values ​​with empty content are marked, and after the marking is completed, the data values ​​are stored in the second storage engine of clickhouse.

6. The data updating method according to claim 5, characterized in that: Before the step of extracting offline data in the first storage engine and real-time data in the second storage engine, the method further includes: Extracting the real-time data in the second storage engine, and extracting the real-time field name in the real-time data; In the real-time data, determining the real-time update time corresponding to each of the real-time field names, wherein the real-time update time is the data receiving time of the data value corresponding to each of the real-time field names without a mark; In each of the real-time update times, determine the latest real-time update time corresponding to each of the real-time field names; According to each of the latest real-time update times, expired real-time data is cleared in the second storage engine.

7. The data updating method according to any one of claims 1 to 6, characterized in that: The first storage engine is a replicateMergeTree engine, and the second storage engine is an AggragationMergeTree engine.

8. A data updating system, characterized in that: The data updating system comprises: A first storage module is used to receive offline data and store the offline data in a first storage engine of clickhouse; The second storage module is used to receive real-time data and store the real-time data in the second storage engine of clickhouse; The data update module is used to extract the offline data in the first storage engine and the real-time data in the second storage engine when receiving a query request, and update the data based on the offline data and the real-time data to obtain updated data.

9. A computer device, characterized in that: The computer device comprises: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the data updating method according to any one of claims 1 to 7.

10. A storage medium, characterized in that: The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, the steps of the data updating method according to any one of claims 1 to 7 are implemented.

Images