Data query method, device, apparatus, and storage medium
By merging and synchronizing data tables from a MySQL database within the Clickhouse database, the problem of slow query speeds in traditional relational databases with large data volumes is solved, achieving efficient data querying and synchronization, and improving query efficiency and accuracy.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- 中移信息技术有限公司
- Filing Date
- 2023-02-20
- Publication Date
- 2026-07-03
AI Technical Summary
In existing technologies, as the amount of data increases, traditional relational databases become slow when performing data queries, especially when performing join queries on multiple data tables.
By creating a target data table in the Clickhouse database, data from multiple source data tables in the MySQL database is merged and inserted into the target data table. The system also captures CRUD operations in the source database in real time and synchronizes these operations to the target data table, leveraging the high query performance of the Clickhouse database to accelerate data retrieval.
It improves the accuracy and speed of data querying in the target database, especially significantly improving query efficiency in multi-dimensional queries, avoiding frequent WAL log file writing and processing, and improving processing efficiency.
Smart Images
Figure CN116186060B_ABST
Abstract
Description
Technical Field
[0001] This application belongs to the field of data service technology, and in particular relates to a data query method, apparatus, device and storage medium. Background Technology
[0002] In many data storage fields, data needs to be stored in databases in tabular form. However, with the ever-increasing volume of data—from millions or tens of millions to hundreds of millions or even billions—efficient storage, processing, and analysis of massive amounts of data have become a major challenge.
[0003] In related technologies, data is mainly stored using traditional relational databases (MySQL, Oracle, etc.). When the amount of data is too large, database sharding and table partitioning can be used to store massive amounts of data, and data read and write separation can also be achieved.
[0004] However, due to the sheer volume of data, even with database sharding and partitioning, some individual tables may contain tens of millions, or even hundreds of millions, of records. Furthermore, database sharding and partitioning require querying multiple interconnected tables for a single data query, significantly slowing down data retrieval. Summary of the Invention
[0005] This application provides a data query method, apparatus, device, and storage medium that can solve the problem of slow data query speed in existing systems.
[0006] In a first aspect, embodiments of this application provide a data query method, the method comprising:
[0007] Create a target data table in the target database, the target data table including all field attributes from at least two source data tables;
[0008] Merge the data from the at least two source data tables to obtain merged data, and insert the merged data into the target data table;
[0009] Obtain a first operation on data in the source data table, wherein the first operation includes at least one of an add operation, a modify operation, and a delete operation, wherein the add operation is to add data to the source data table, the modify operation is to modify data in the source data table, and the delete operation is to delete data in the source data table;
[0010] A target operation is generated based on the first operation and the target operation is executed. The target operation is an operation on a target data table and is used to keep the data in the target data table synchronized with the data in the source data table.
[0011] Perform data query based on the target data table.
[0012] In some embodiments, the target operation includes a first insertion operation, and generating the target operation based on the first operation includes:
[0013] When the first operation is a new operation, a first insertion operation is generated based on the new operation. The first insertion operation is an operation that inserts the data added by the new operation into the target data table.
[0014] In some embodiments, the target operation includes a second insertion operation and a batch deletion operation, and generating the target operation based on the first operation includes:
[0015] When the first operation is a modification operation, a second insertion operation and a batch deletion operation are generated based on the modification operation. The second insertion operation is to insert the data modified by the modification operation into the target data table, and the batch deletion operation is to delete the data before the modification operation.
[0016] In some embodiments, the target operation includes a third insertion operation, and generating the target operation based on the first operation includes:
[0017] In the case that the first operation is a deletion operation, a third insertion operation is generated based on the deletion operation. The third insertion operation is an operation to insert a deletion marker into the target data table. The deletion marker is used to mark the data deleted in the source database by the deletion operation in the target database.
[0018] In some embodiments, after generating the third insertion operation based on the deletion operation, the method further includes:
[0019] During the data query process targeting the target data table, data containing the deletion mark is filtered out from the target data table.
[0020] In some embodiments, after generating the third insertion operation based on the deletion operation, the method further includes:
[0021] Batch delete data in the target database that contains the deletion marker.
[0022] In some embodiments, the data query based on the target data table includes:
[0023] The data in the target data table is statistically analyzed according to the pre-set dimension attributes to generate statistical data.
[0024] Perform data queries within the statistical data.
[0025] Secondly, embodiments of this application provide a data query device, the device comprising:
[0026] A module is established to create a target data table in the target database, the target data table including all field attributes from at least two source data tables;
[0027] A merging module is used to merge data from at least two source data tables to obtain merged data, and then insert the merged data into the target data table.
[0028] The acquisition module is used to acquire a first operation on the data in the source data table, wherein the first operation includes at least one of an add operation, a modify operation, and a delete operation, wherein the add operation is to add data to the source data table, the modify operation is to modify data in the source data table, and the delete operation is to delete data in the source data table;
[0029] An execution module is configured to generate a target operation based on the first operation and execute the target operation, wherein the target operation is an operation on a target data table and is used to keep the data in the target data table synchronized with the data in the source data table;
[0030] The query module is used to perform data queries based on the target data table.
[0031] Thirdly, embodiments of this application provide a data query device, the device including: a processor and a memory storing computer program instructions;
[0032] The processor implements the data query method described above when executing computer program instructions.
[0033] Fourthly, embodiments of this application provide a computer storage medium storing computer program instructions, which, when executed by a processor, implement the data query method described above.
[0034] In this application, a target data table is created in the target database. Data from at least two source data tables in the source database is merged and inserted into the target data table in the target database. Furthermore, real-time capture of add, delete, and modify operations on the source database is synchronized to the target data table in the target database, and data queries are performed on the target data table. When the source database is a MySQL database and the target database is a ClickHouse database, real-time synchronization ensures the accuracy of queries in the target database. Leveraging the high query efficiency of ClickHouse database, the data query speed is accelerated, achieving efficient data retrieval. Attached Figure Description
[0035] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the embodiments of this application will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0036] Figure 1 This is a schematic flowchart of a data query method provided in an embodiment of this application;
[0037] Figure 2 This is a schematic diagram of the hardware structure of a data query device provided in an embodiment of this application;
[0038] Figure 3 This is a schematic diagram of the structure of a data query device provided in an embodiment of this application. Detailed Implementation
[0039] The features and exemplary embodiments of various aspects of this application will be described in detail below. To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings and specific embodiments. It should be understood that the specific embodiments described herein are only intended to explain this application and not to limit it. For those skilled in the art, this application can be implemented without some of these specific details. The following description of the embodiments is merely to provide a better understanding of this application by illustrating examples of this application.
[0040] It should be noted that, in this document, relational terms such as "first" and "second" are used merely to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising..." does not exclude the presence of additional identical elements in the process, method, article, or apparatus that includes the element.
[0041] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. The embodiments will now be described in detail with reference to the accompanying drawings.
[0042] Specifically, in order to solve the problems of the prior art, embodiments of this application provide a data query method, apparatus, device, and storage medium. The data query method provided in the embodiments of this application will be described first below.
[0043] Figure 1 A flowchart illustrating a data query method according to an embodiment of this application is shown. The method includes the following steps:
[0044] S110, Create a target data table in the target database, the target data table including all field attributes from at least two source data tables.
[0045] In this embodiment, the source data table is the data table that stores data in the source database. The source database directly responds to user interaction operations. Users can directly update the data in the source database through add, modify, and delete operations. The target data table is the data table that stores data in the target database. The data in the target database is updated as the data in the source database is updated.
[0046] In this embodiment, the source database can be a relational database such as MySQL, and the target database can be a ClickHouse database. MySQL databases can support user operations such as adding, modifying, and deleting data. However, if the amount of data stored in the MySQL database is too large, it is necessary to shard the data in the MySQL database into multiple single tables, each used to store data of the same dimension or the same attribute.
[0047] In the above scenario, if a multidimensional combined statistical query is required to analyze data in a MySQL database based on multiple dimensions, each query often takes a long time. In order to facilitate queries and improve query efficiency, indexes are usually created for each sub-table in the main table of the MySQL database. Although this method can improve query efficiency, it also consumes a large amount of disk space during the query process.
[0048] Compared to MySQL, ClickHouse offers superior storage compression and more efficient query aggregation capabilities. Therefore, a single wide table in ClickHouse can hold a large amount of data without the need for sharding or partitioning like in MySQL. Furthermore, ClickHouse's query performance is over 100 times faster than relational databases like MySQL. This allows data to be synchronized from MySQL to ClickHouse for efficient querying.
[0049] To synchronize data between a MySQL database and a ClickHouse database, a target table can be created in the ClickHouse database. This target table includes all fields and attributes found in the source table. To insert new data into the target table, simply insert the new data into the corresponding positions in the target table according to the field attributes. These field attributes are the header fields in the database table that represent the specific characteristics and types of the data.
[0050] As an optional embodiment, in order to achieve data synchronization, a data synchronizer can be set up, and the data synchronizer can be used to create a target data table in the target database.
[0051] S120, merge the data from the at least two source data tables to obtain merged data, and insert the merged data into the target data table.
[0052] In this embodiment, the source database can be a MySQL database, and the target database can be a ClickHouse database. Because ClickHouse database uses efficient data compression algorithms to meet storage space requirements, a single target table can hold a large amount of data. Furthermore, ClickHouse database is not efficient for join queries involving multiple tables. Therefore, to improve query efficiency in ClickHouse database, data from multiple source tables in the MySQL database can be pre-merged to obtain merged data, which is then inserted into the target table. This way, data from at least two tables in the MySQL database are concentrated in the same target table in the ClickHouse database. This facilitates querying the target data within the target table.
[0053] S130, obtain a first operation on the data in the source data table, wherein the first operation includes at least one of an add operation, a modify operation, and a delete operation, wherein the add operation is to add data to the source data table, the modify operation is to modify the data in the source data table, and the delete operation is to delete the data in the source data table.
[0054] In this embodiment, the user can perform the first operation in the source database, namely, an insert operation, an update operation, and a delete operation, to update the data in the source data table. The insert operation involves inserting new data into the source data table using the INSERT statement; the update operation involves modifying data in the source data table using the UPDATE statement; and the delete operation involves deleting data from the source data table, typically using the DELETE statement.
[0055] S140, generate a target operation based on the first operation and execute the target operation, the target operation being an operation on a target data table, the target operation being used to keep the data in the target data table synchronized with the data in the source data table.
[0056] In this embodiment, since the user's first operation only targets the source database and does not affect the target database, in order to ensure the synchronization of data between the source and target databases, the user's first operation can be captured in real time by a synchronizer, and the first operation can be converted into a target operation targeting the target data table. The target operation is then executed in the target database so that the data in the target data table is synchronized with the data in the source data table.
[0057] S150, perform a data query based on the target data table.
[0058] In this embodiment, the source database can be a MySQL database, and the target database can be a ClickHouse database. If queries are performed on at least two source tables in the MySQL database, complex join operations are required, resulting in slower query speeds. However, in the target database, all data is concentrated in a single target table, making data queries on that table much faster.
[0059] This application involves creating a target data table in the target database, merging data from at least two source data tables in the source database, inserting the data into the target data table in the target database, and capturing CRUD operations on the source database in real time. These operations are then synchronized to the target data table in the target database, and data queries are performed on the target data table. When the source database is a MySQL database and the target database is a ClickHouse database, real-time synchronization ensures the accuracy of queries in the target database, while leveraging the high query efficiency of ClickHouse database enables efficient data retrieval.
[0060] As an optional embodiment, the above-described S130 may include:
[0061] When the first operation is a new operation, a first insertion operation is generated based on the new operation. The first insertion operation is an operation that inserts the data added by the new operation into the target data table.
[0062] In this embodiment, when the source database receives a user's new operation, the new operation can add new data to the source data table in the source database. In order to ensure data synchronization between the target database and the source database, this new data needs to be synchronously added to the target data table in the target database.
[0063] Therefore, a first insert operation can be generated for the target data table based on the new operation. This first insert operation inserts the data added to the source data table into the target data table. In this way, it can be guaranteed that when new data is added to the source database, the target database can also add the same data synchronously, thus ensuring the consistency of data between the source and target databases.
[0064] As an optional embodiment, the above-described S130 may include:
[0065] When the first operation is a modification operation, a second insertion operation and a batch deletion operation are generated based on the modification operation. The second insertion operation is to insert the data modified by the modification operation into the target data table, and the batch deletion operation is to delete the data before the modification operation.
[0066] In this embodiment, the source database can be a MySQL database, and the target database can be a ClickHouse database. Although ClickHouse database has stronger storage capacity and query performance than MySQL database, it also has certain drawbacks. Specifically, data updates in ClickHouse database are subject to some limitations. For example, ClickHouse database has restrictions on high-frequency data submissions, and its support for modification and deletion operations is not user-friendly, resulting in low update efficiency.
[0067] Specifically, ClickHouse databases typically employ copy-on-write (WAL) technology, which writes modification and deletion operations to a WAL log file and then processes them in batches in the background. This ensures data consistency and integrity. However, because each modification or deletion operation requires writing to the log file, the frequent writing and processing of the WAL log file can negatively impact database performance.
[0068] To address the above issue, when a modification operation targeting the source database is captured, it can be converted into an insert operation and a batch delete operation targeting the target database. Since the modification operation actually uses an UPDATE statement to update the data in the database, this update is equivalent to using a DELETE statement to delete the unupdated data from the table and then using an INSERT statement to insert the updated data in the same location.
[0069] In other words, when a modification operation is detected, the modification operation is converted into a second insert operation and a batch delete operation. First, the batch delete operation is applied to delete all the data that needs to be updated in the target data table. Then, multiple single insert operations are applied to insert the updated data in the target data table. In this way, no matter how much data the modification operation needs to modify, only one batch delete operation needs to be performed in the Clickhouse database. This avoids frequently writing and processing the data that needs to be modified to the WAL log file, which greatly improves the processing efficiency of the target database.
[0070] As an optional embodiment, the above-described S130 may include:
[0071] If the first operation is a deletion operation, a third insertion operation is generated based on the deletion operation and executed. The third insertion operation is an operation to insert a deletion marker into the target data table. The deletion marker is used to mark the data deleted in the source database by the deletion operation in the target database.
[0072] In this embodiment, when a deletion operation is captured targeting the source database, the deletion operation can also be converted into a third insertion operation targeting the target database. A deletion marker is inserted into the target database to identify the data deleted in the source database, thereby achieving synchronization between the data in the target database and the source database.
[0073] In this way, delete operations targeting the source database are converted into insert operations targeting the target database, avoiding frequent writing and processing of WAL log files for delete operations and improving the processing efficiency of the target database.
[0074] As an optional embodiment, after generating the third insertion operation based on the deletion operation, the method further includes:
[0075] During the data query process targeting the target data table, data containing the deletion mark is filtered out from the target data table.
[0076] In this embodiment, when a user needs to read or query data in a target data table, data marked with a deletion flag can be ignored during the query or reading process. In this way, although data marked with a deletion flag still exists in the target database, data reading and data querying cannot obtain this data. Therefore, the deletion flag can simulate the deletion operation and achieve the same deletion effect.
[0077] As an optional embodiment, after generating the third insertion operation based on the deletion operation, the method further includes:
[0078] Batch delete data in the target database that contains the deletion marker.
[0079] In this embodiment, the target database can be a Clickhouse database, and data marked for deletion in the target database can be deleted in batches. This avoids frequent single deletion operations in the target database, transforming multiple single deletion operations into a single batch deletion operation, thus improving the processing efficiency of the target database.
[0080] Specifically, when the number of data marked for deletion in the target database reaches a certain amount, or when the target database is idle, a batch deletion operation can be performed on the data marked for deletion. In this way, the batch deletion operation will not affect the normal operation of other functions of the target database.
[0081] As an optional embodiment, the above-described S150 may include:
[0082] The data in the target data table is statistically analyzed according to the pre-set dimension attributes to generate statistical data.
[0083] Perform data queries within the statistical data.
[0084] In this embodiment, users can pre-set dimension attributes according to their query needs, such as time dimensions (year, month, week, day) or regional dimensions (country, province, city, grid), and classify and statistically analyze the data in the target data table according to these dimension attributes to obtain statistical data. Then, users can arbitrarily combine the dimension data they need to query. For example, if a user needs to query data in region B in year A, they can directly query the data in the classified statistical data.
[0085] Since the statistical data has already been compiled for each year and each region, after the user enters the query conditions, they only need to find the data that matches the query conditions. This greatly speeds up the query process and improves the efficiency of data retrieval.
[0086] As an optional embodiment, a synchronizer can be set up between the source database and the target database to synchronize the data between the source database and the target database.
[0087] A data analyzer can also be set up. The data analyzer can perform statistics on the data in the target data table according to the dimension attributes and provide analysis capabilities for the data in the target database. In addition, during the data synchronization process between the target database and the source database, due to the existence of many interfaces in the data interface, data is often missed due to delays. The data analyzer can automatically identify the missing data at regular intervals, perform data back calculation, and make up for the missing data based on the back calculation results.
[0088] First see Figure 2 The data query device 200 provided in this application embodiment includes the following modules:
[0089] Module 201 is used to create a target data table in the target database, the target data table including all field attributes from at least two source data tables;
[0090] Merging module 202 is used to merge data from at least two source data tables to obtain merged data, and insert the merged data into the target data table;
[0091] The acquisition module 203 is used to acquire a first operation on the data in the source data table, wherein the first operation includes at least one of an add operation, a modify operation, and a delete operation, wherein the add operation is to add data to the source data table, the modify operation is to modify data in the source data table, and the delete operation is to delete data in the source data table;
[0092] The execution module 204 is configured to generate a target operation based on the first operation and execute the target operation, wherein the target operation is an operation on a target data table and is used to keep the data in the target data table synchronized with the data in the source data table;
[0093] The query module 205 is used to perform data queries based on the target data table.
[0094] The device can merge data from at least two source tables in the source database and insert it into the target table in the target database. It also captures CRUD operations on the source database in real time, synchronizes these operations to the target table, and performs data queries on the target table. When the source database is a MySQL database and the target database is a ClickHouse database, real-time synchronization ensures the accuracy of queries in the target database, while leveraging ClickHouse's high query efficiency accelerates data retrieval, achieving efficient data retrieval.
[0095] As one implementation of this application, the above-mentioned acquisition module 203 may further include:
[0096] The first insertion unit is configured to generate a first insertion operation based on the first addition operation when the first operation is an addition operation. The first insertion operation is an operation to insert the data added by the addition operation into the target data table.
[0097] As one implementation of this application, the above-mentioned acquisition module 203 may further include:
[0098] The second insertion unit is used to generate a second insertion operation and a batch deletion operation based on the modification operation when the first operation is a modification operation. The second insertion operation is to insert the data modified by the modification operation into the target data table, and the batch deletion operation is to delete the data before the modification operation.
[0099] As one implementation of this application, the above-mentioned acquisition module 203 may further include:
[0100] The third insertion unit is used to generate a third insertion operation based on the deletion operation when the first operation is a deletion operation. The third insertion operation is to insert a deletion mark into the target data table. The deletion mark is used to mark the data deleted in the source database by the deletion operation in the target database.
[0101] As one implementation of this application, the above-mentioned acquisition module 203 may further include:
[0102] The filtering unit is used to filter out data containing the deletion mark in the target data table during the data query process.
[0103] As one implementation of this application, the above-mentioned acquisition module 203 may further include:
[0104] The deletion unit is used to delete data with the deletion mark in batches in the target database.
[0105] As one implementation of this application, the query module 205 described above can be used specifically for:
[0106] The data in the target data table is statistically analyzed according to the pre-set dimension attributes to generate statistical data.
[0107] Perform data queries within the statistical data.
[0108] The data query device provided in this embodiment of the invention can implement the steps in the above method embodiments, and will not be repeated here to avoid repetition.
[0109] Figure 3 A schematic diagram of the hardware structure of the data query device provided in an embodiment of this application is shown.
[0110] The data query device may include a processor 301 and a memory 302 storing computer program instructions.
[0111] Specifically, the processor 301 may include a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits that can be configured to implement the embodiments of this application.
[0112] Memory 302 may include mass storage for data or instructions. For example, and not limitingly, memory 302 may include a hard disk drive (HDD), floppy disk drive, flash memory, optical disk, magneto-optical disk, magnetic tape, or Universal Serial Bus (USB) drive, or a combination of two or more of these. Where appropriate, memory 302 may include removable or non-removable (or fixed) media. Where appropriate, memory 302 may be internal or external to the integrated gateway disaster recovery device. In a particular embodiment, memory 302 is non-volatile solid-state memory.
[0113] Memory may include read-only memory (ROM), random access memory (RAM), disk storage media devices, optical storage media devices, flash memory devices, and electrical, optical, or other physical / tangible memory storage devices. Therefore, typically, memory includes one or more tangible (non-transitory) computer-readable storage media (e.g., memory devices) encoded with software including computer-executable instructions, and when the software is executed (e.g., by one or more processors), it is operable to perform the operations described with reference to the methods according to one aspect of this disclosure.
[0114] The processor 301 reads and executes computer program instructions stored in the memory 302 to implement any of the data query methods in the above embodiments.
[0115] In one example, the data query device may further include a communication interface 303 and a bus 310. For example, Figure 3 As shown, the processor 301, memory 302, and communication interface 303 are connected through bus 310 and complete communication with each other.
[0116] The communication interface 303 is mainly used to realize communication between various modules, devices, units and / or equipment in the embodiments of this application.
[0117] Bus 310 includes hardware, software, or both, that couples components of a data query device together. For example, and not limitingly, the bus may include an Accelerated Graphics Port (AGP) or other graphics bus, an Enhanced Industry Standard Architecture (EISA) bus, a Front Side Bus (FSB), HyperTransport (HT) interconnect, an Industry Standard Architecture (ISA) bus, an Infinite Bandwidth Interconnect, a Low Pin Count (LPC) bus, a memory bus, a Microchannel Architecture (MCA) bus, a Peripheral Component Interconnect (PCI) bus, a PCI-Express (PCI-X) bus, a Serial Advanced Technology Attachment (SATA) bus, a Video Electronics Standards Association Local (VLB) bus, or other suitable buses, or combinations of two or more of these. Where appropriate, bus 310 may include one or more buses. Although specific buses are described and illustrated in embodiments of this application, any suitable bus or interconnect is contemplated herein.
[0118] The data query device can be based on the above embodiments to realize the combination of the above data query method and apparatus.
[0119] Furthermore, in conjunction with the data query methods in the above embodiments, this application embodiment can provide a computer storage medium for implementation. This computer storage medium stores computer program instructions; when these computer program instructions are executed by a processor, they implement any of the data query methods in the above embodiments and achieve the same technical effect. To avoid repetition, further details are omitted here. The aforementioned computer-readable storage medium may include non-transitory computer-readable storage media, such as read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks, etc., and is not limited thereto.
[0120] In addition, this application also provides a computer program product, including computer program instructions, which, when executed by a processor, can implement the steps and corresponding content of the aforementioned method embodiments.
[0121] It should be clarified that this application is not limited to the specific configurations and processes described above and shown in the figures. For the sake of brevity, detailed descriptions of known methods are omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of this application is not limited to the specific steps described and shown. Those skilled in the art can make various changes, modifications, and additions, or change the order of steps, after understanding the spirit of this application.
[0122] The functional blocks shown in the above block diagram can be implemented as hardware, software, firmware, or a combination thereof. When implemented in hardware, they can be, for example, electronic circuits, application-specific integrated circuits (ASICs), appropriate firmware, plug-ins, function cards, etc. When implemented in software, the elements of this application are programs or code segments used to perform the required tasks. Programs or code segments can be stored on a machine-readable medium or transmitted over a transmission medium or communication link via data signals carried on a carrier wave. "Machine-readable medium" can include any medium capable of storing or transmitting information. Examples of machine-readable media include electronic circuits, semiconductor memory devices, ROM, flash memory, erasable ROM (EROM), floppy disks, CD-ROMs, optical disks, hard disks, fiber optic media, radio frequency (RF) links, etc. Code segments can be downloaded via computer networks such as the Internet, intranets, etc.
[0123] It should also be noted that the exemplary embodiments mentioned in this application describe methods or systems based on a series of steps or apparatus. However, this application is not limited to the order of the above steps; that is, the steps can be performed in the order mentioned in the embodiments, or in a different order, or several steps can be performed simultaneously.
[0124] The aspects of this disclosure have been described above with reference to flowchart illustrations and / or block diagrams of methods, apparatus, and computer program products according to embodiments of this disclosure. It should be understood that each block in the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus to produce a machine such that these instructions, executable via the processor of the computer or other programmable data processing apparatus, enable the implementation of the functions / actions specified in one or more blocks of the flowchart illustrations and / or block diagrams. Such a processor can be, but is not limited to, a general-purpose processor, a special-purpose processor, a special application processor, or a field-programmable logic circuit. It is also understood that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can also be implemented by special-purpose hardware performing the specified functions or actions, or can be implemented by a combination of special-purpose hardware and computer instructions.
[0125] The above are merely specific embodiments of this application. Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the systems, modules, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here. It should be understood that the protection scope of this application is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in this application, and these modifications or substitutions should all be covered within the protection scope of this application.
Claims
1. A data query method, characterized by, The method includes: Create a target data table in the target database. The target data table includes a wide table containing all field attributes from at least two source data tables. The target database is the ClickHouse database. Merge the data from the at least two source data tables to obtain merged data, and insert the merged data into the target data table; Obtain a first operation on data in the source data table, wherein the first operation includes at least one of an add operation, a modify operation, and a delete operation, wherein the add operation is to add data to the source data table, the modify operation is to modify data in the source data table, and the delete operation is to delete data in the source data table; A target operation is generated based on the first operation and the target operation is executed. The target operation is an operation on a target data table and is used to keep the data in the target data table synchronized with the data in the source data table. In the case where the first operation is a modification operation, a second insertion operation and a batch deletion operation are generated based on the modification operation. The second insertion operation is an operation to insert the data modified by the modification operation into the target data table, and the batch deletion operation is an operation to delete the data before the modification operation. When the first operation is a deletion operation, a third insertion operation is generated based on the deletion operation to batch delete data with the deletion mark in the target database. The third insertion operation is to insert the deletion mark into the target data table. The deletion mark is used to mark the data deleted by the deletion operation in the source database in the target database. The batch deletion operation is performed when the number of data with the deletion mark in the target database reaches a preset number, or when the target database is idle. According to the pre-set dimension attributes, the data in the target data table is statistically analyzed to generate statistical data. Data is then queried in the statistical data according to the dimensions or the combined dimensions. During the data query process for the target data table, data with the deletion mark is filtered out from the target data table. The data analyzer periodically identifies and retransmits any missing data from the target database and the source database.
2. The data query method of claim 1, wherein, The target operation includes a first insertion operation, and the generation of the target operation based on the first operation includes: When the first operation is a new operation, a first insertion operation is generated based on the new operation. The first insertion operation is an operation that inserts the data added by the new operation into the target data table.
3. A data query apparatus, characterized by, The device includes: A module is used to create a target data table in the target database. The target data table includes a wide table containing all field attributes from at least two source data tables. The target database is a ClickHouse database. A merging module is used to merge data from at least two source data tables to obtain merged data, and then insert the merged data into the target data table. The acquisition module is used to acquire a first operation on the data in the source data table, wherein the first operation includes at least one of an add operation, a modify operation, and a delete operation, wherein the add operation is to add data to the source data table, the modify operation is to modify data in the source data table, and the delete operation is to delete data in the source data table; An execution module is configured to generate a target operation based on the first operation and execute the target operation, wherein the target operation is an operation on a target data table, and the target operation is used to keep the data in the target data table synchronized with the data in the source data table. Specifically, if the first operation is a modification operation, a second insertion operation and a batch deletion operation are generated based on the modification operation. The second insertion operation is an operation to insert the data modified by the modification operation into the target data table, and the batch deletion operation is an operation to delete the data before the modification operation. When the first operation is a deletion operation, a third insertion operation is generated based on the deletion operation to batch delete data with the deletion mark in the target database. The third insertion operation is to insert the deletion mark into the target data table. The deletion mark is used to mark the data deleted by the deletion operation in the source database in the target database. The batch deletion operation is performed when the number of data with the deletion mark in the target database reaches a preset number, or when the target database is idle. The query module is used to statistically analyze the data in the target data table according to the pre-set dimension attributes, generate statistical data, and perform data query in the statistical data according to the dimensions or the combined dimensions. During the data query process for the target data table, data with the deletion mark is filtered out in the target data table. The data analyzer periodically identifies and retransmits any missing data from the target database and the source database.
4. A data query device, characterized by The data query device includes: a processor and a memory storing computer program instructions; When the processor executes the computer program instructions, it implements the data query method as described in any one of claims 1-2.
5. A computer storage medium, characterized in that The computer storage medium stores computer program instructions, which, when executed by a processor, implement the data query method as described in any one of claims 1-2.