Data synchronization method and device based on SQOOP, computer device and storage medium

Abstract

The embodiment of the application belongs to the field of big data and financial technology, and relates to a data synchronization method and device based on SQOOP, a computer device and a storage medium. The method comprises the following steps: obtaining to-be-synchronized data from HIVE through SQOOP, and writing the to-be-synchronized data into a temporary table; reading a version state in a state table to determine a current version table and a historical version table, wherein the version state comprises a current version and a historical version, the current version table corresponding to the current version is one of a first storage table and a second storage table, the historical version table corresponding to the historical version is one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table are different; writing the to-be-synchronized data in the temporary table into the historical version table, and exchanging the version states of the historical version table and the current version table after writing is completed; and providing data services through a new current version table. When data is synchronized through SQOOP, the database can provide services all the time.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of big data and financial technology, and particularly relates to a data synchronization method and device based on SQOOP, a computer device and a storage medium. BACKGROUND

[0002] With the advent of the era of big data, the application of big data is also increasing, and the application scene of offline mass processing based on HIVE (a data warehouse tool based on Hadoop, used for processing large-scale distributed stored data) is gradually increasing. SQOOP is a tool for transferring data between Hadoop and a relational database. In actual application, massive data is often processed through HIVE, and then synchronized to the database by SQOOP for application to use data.

[0003] However, this big data system has some deficiencies. First, the database is usually read-write unseparated, when the database performs a query operation, the data table in the database will be locked, if SQOOP wants to write new data into the database at this time, the write operation will be blocked due to the locking of the data table, which may cause delay of data synchronization and data inconsistency. Second, SQOOP performs full update each time data synchronization is performed, that is, SQOOP will first delete the data in the database, and then write the new data in batches. When the data table has an index or the data volume is very large, the synchronization efficiency will be greatly reduced, and a blank period of the data table will occur, during which the data will be missing, and all-weather query support cannot be achieved. For example, in the field of finance and insurance, the insurance company periodically synchronizes the customer data in batches, due to the large number of customers, a large amount of data needs to be synchronized, which takes a long time, if the business needs to query data during the synchronization process, the data table will be blank, causing the query business to be interrupted; if the database is frequently queried, the data table is frequently locked, which will affect the data synchronization. These will cause the database to be unable to normally provide services when SQOOP performs data synchronization. SUMMARY

[0004] The purpose of the embodiments of the present application is to provide a data synchronization method and device based on SQOOP, a computer device and a storage medium, to solve the problem that the database cannot normally provide services when data synchronization is performed by SQOOP.

[0005] In order to solve the above technical problems, the embodiments of the present application provide a data synchronization method based on SQOOP, and a temporary table, a first storage table, a second storage table and a state table are set in the database, and the following technical solution is adopted:

[0006] The SQOOP obtains the to-be-synchronized data from the HIVE, and writes the to-be-synchronized data into the temporary table.

[0007] reading the version state in the state table to determine the current version table and the history version table, the version state including a current version and a history version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the history version table corresponding to the history version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different;

[0008] writing the to-be-synchronized data in the temporary table into the history version table, and swapping the version states of the history version table and the current version table after the writing is completed;

[0009] providing data service through the new current version table.

[0010] To solve the above technical problems, the embodiment of the present application further provides a data synchronization device based on SQOOP, and a temporary table, a first storage table, a second storage table and a state table are arranged in a database, and the following technical scheme is adopted:

[0011] a data acquisition module, configured to acquire to-be-synchronized data from HIVE through SQOOP and write the to-be-synchronized data into the temporary table;

[0012] a state reading module, configured to read the version state in the state table to determine the current version table and the history version table, the version state including a current version and a history version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the history version table corresponding to the history version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different;

[0013] a data writing module, configured to write the to-be-synchronized data in the temporary table into the history version table, and swap the version states of the history version table and the current version table after the writing is completed;

[0014] a service providing module, configured to provide data service through the new current version table.

[0015] To solve the above technical problems, the embodiment of the present application further provides a computer device, and a temporary table, a first storage table, a second storage table and a state table are arranged in a database, and the following technical scheme is adopted:

[0016] acquiring to-be-synchronized data from HIVE through SQOOP and writing the to-be-synchronized data into the temporary table;

[0017] reading the version state in the state table to determine the current version table and the history version table, the version state including a current version and a history version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the history version table corresponding to the history version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different;

[0018] writing the to-be-synchronized data in the temporary table into the history version table, and switching the version states of the history version table and the current version table after the writing is completed;

[0019] providing data service through the new current version table.

[0020] To solve the above technical problems, the embodiment of the present application further provides a computer readable storage medium, and a temporary table, a first storage table, a second storage table and a state table are arranged in the database, and the following technical solution is adopted:

[0021] obtaining to-be-synchronized data from HIVE through SQOOP, and writing the to-be-synchronized data into the temporary table;

[0022] reading the version state in the state table to determine the current version table and the history version table, the version state including a current version and a history version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the history version table corresponding to the history version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different;

[0023] writing the to-be-synchronized data in the temporary table into the history version table, and switching the version states of the history version table and the current version table after the writing is completed;

[0024] providing data service through the new current version table.

[0025] Compared with the prior art, the embodiment of the application has the following beneficial effects: a temporary table, a first storage table, a second storage table and a state table are set in the database; the to-be-synchronized data is obtained from the HIVE through the SQOOP, and the to-be-synchronized data is quickly written into the temporary table, so that the data is quickly imported into the database; the state table can record and determine the version state of the storage table, and the state table is read to determine the current version table and the historical version table in the first storage table and the second storage table, and the data in the historical version table is the previous version of the data in the current version table; the database provides data services by using the current version table, the historical version table is updated during data synchronization, and the current version table can work normally, so that the read-write separation is realized at the database level, the write operation and the read operation of the database do not affect each other, the risk that the data table is locked and cannot work normally is avoided, the AB table mechanism can easily cope with the synchronization of a large amount of data, the database does not display a blank data table to the outside, and the problem that the database cannot provide data services during the synchronization process is avoided; after the to-be-synchronized data is written into the historical version table, the data synchronization is completed, the version states of the historical version table and the current version table are exchanged, and the cross update of the historical version table and the current version table is realized. BRIEF DESCRIPTION OF DRAWINGS

[0026] In order to more clearly illustrate the scheme in the application, the drawings needed in the description of the embodiments of the application will be briefly introduced below. Obviously, the drawings in the following description are some embodiments of the application, and other drawings can be obtained by those skilled in the art without creative labor on the basis of these drawings.

[0027] Figure 1 is an exemplary system architecture diagram to which the application can be applied;

[0028] Figure 2 is a flowchart of one embodiment of the SQOOP-based data synchronization method according to the application;

[0029] Figure 3 is a structural schematic diagram of one embodiment of the SQOOP-based data synchronization device according to the application;

[0030] Figure 4 is a structural schematic diagram of one embodiment of the computer device according to the application. DETAILED DESCRIPTION

[0031] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs; the terminology used in the description herein is for describing particular embodiments only and is not intended to be limiting of the application; the use herein of terms such as "comprise" and "comprising", "have" and "having", "include" and "including" and "contain" and "containing" are to be construed in an open, non-exhaustive way, i.e. in the sense of "including, but not limited to"; the use herein of terms such as "first", "second", and "other" are used to distinguish one object from another, and are not used to describe a particular sequential order.

[0032] Reference herein to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the application. The appearances of the phrase "in an embodiment" in various places in the specification are not necessarily all referring to the same embodiment, nor are they necessarily mutually exclusive of one another.

[0033] In order to make the persons skilled in the art better understand the scheme of the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings.

[0034] As shown in Figure 1 The system architecture 100 can include terminal devices 101, 102, 103, a network 104, and a server 105. The network 104 is a medium for providing a communication link between the terminal devices 101, 102, 103 and the server 105. The network 104 can include various connection types, such as wired, wireless communication links, or optical fiber cables, etc.

[0035] A user can use the terminal devices 101, 102, 103 to interact with the server 105 through the network 104 to receive or send messages, etc. Various communication client applications can be installed on the terminal devices 101, 102, 103, such as web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0036] The terminal devices 101, 102, 103 can be various electronic devices with display screens and supporting web browsing, including but not limited to smartphones, tablet computers, e-book readers, MP3 (Moving Picture Experts Group Audio Layer III) players, MP4 (Moving Picture Experts Group Audio Layer IV) players, laptop portable computers, and desktop computers, etc.

[0037] The server 105 can be a server providing various services, for example, a background server providing support for a page displayed on the terminal device 101, 102, 103.

[0038] It should be noted that the SQOOP-based data synchronization method provided in the embodiments of the present application is generally executed by a server, and accordingly, the SQOOP-based data synchronization apparatus is generally arranged in a server.

[0039] It should be understood that Figure 1 The number of terminal devices, networks and servers in

[0040] With reference to Figure 2 , a flow chart of one embodiment of the SQOOP-based data synchronization method according to the present application is shown. The SQOOP-based data synchronization method, which is provided with a temporary table, a first storage table, a second storage table and a state table in a database, comprises the following steps:

[0041] In step S201, SQOOP is used to obtain data to be synchronized from HIVE, and the data to be synchronized is written into the temporary table.

[0042] In the present embodiment, the electronic device (for example, the server shown in Figure 1 The electronic device on which the SQOOP-based data synchronization method runs can communicate with a terminal device through a wired connection or a wireless connection. It should be noted that the wireless connection can include, but is not limited to, 3G / 4G / 5G connection, WiFi connection, Bluetooth connection, WiMAX connection, Zigbee connection, UWB (ultra wideband) connection, and other now known or future developed wireless connection methods.

[0043] Specifically, HIVE performs a data processing task, and the data after the data processing task is completed is taken as data to be synchronized. SQOOP, according to a set SQOOP task, first writes the data to be processed into the temporary table in the database. The data to be processed can be data in any field, for example, the data to be processed can be customer-related data in a financial institution or an insurance company, and the data amount is large.

[0044] In the present application, a temporary table (Table_tmp), a first storage table (Table_a), a second storage table (Table_b) and a state table (Table_config) are provided in the database. The temporary table is a table used by the database to temporarily store data. The first storage table and the second storage table are used to formally store data and can provide data services (for example, provide application access services), and the first storage table and the second storage table constitute AB tables that back up each other and have consistent table structures. The first storage table and the second storage table have version states, and the state table (Table_config) can record and control the version states of the first storage table and the second storage table. By modifying the state table, the version states of the first storage table and the second storage table can be adjusted. The database can also include a trigger table (Table_config_tmp), and the function of the trigger table will be described in detail below.

[0045] Further, the step of writing the to-be-synchronized data into the temporary table can include directly writing the to-be-synchronized data into the temporary table, wherein the to-be-synchronized data in the temporary table does not have an index.

[0046] Specifically, the to-be-synchronized data is not processed and is directly written into the temporary table. Generally, the data in a data table has an index to facilitate retrieval. However, after the to-be-synchronized data is written into the temporary table, no index of the to-be-synchronized data is generated. Generally, generating an index of data requires a lot of time and computing resources. By directly writing the to-be-synchronized data into the temporary table without generating an index, the speed of writing the to-be-synchronized data into the temporary table is improved.

[0047] In the present embodiment, the to-be-synchronized data is directly written into the temporary table without generating an index, thereby improving the speed of writing the to-be-synchronized data into the temporary table.

[0048] Further, the database also includes a trigger table. After the step of writing the to-be-synchronized data into the temporary table, the method can further include synchronizing preset target type data to the trigger table to execute a synchronization function through a trigger in the trigger table, and reading the version state in the state table according to the synchronization function to determine the current version table and the historical version table.

[0049] Specifically, after the SQOOP writes the to-be-synchronized data into the temporary table through the SQOOP task, the SQOOP synchronizes preset target type data to the trigger table. The target type data can be constant data, such as a predetermined number or a string, etc.

[0050] A trigger ab_trigger is created in the trigger table, which is an AFTER INSERT trigger. When new data to be synchronized is written into the temporary table, SQOOP synchronizes the target type data to the trigger table, so that the trigger is triggered. The triggered trigger calls and executes the synchronization function to determine the current version table and the historical version table according to the version state in the state table read by the synchronization function.

[0051] The synchronization function table_config_tmp_func() is a predefined function that performs synchronization operations between the temporary table, the first storage table, and the second storage table.

[0052] In this embodiment, the preset target type data is synchronized to the trigger table to trigger the preset trigger; the triggered trigger calls and executes the synchronization function to read the version state in the state table to determine the current version table and the historical version table, and sequentially promote the subsequent data synchronization operation.

[0053] In step S202, the version state in the state table is read to determine the current version table and the historical version table, and the version state includes the current version and the historical version. The current version table corresponding to the current version is one of the first storage table and the second storage table, the historical version table corresponding to the historical version is one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table are different.

[0054] Specifically, the first storage table and the second storage table have version states, and the version states include current versions and historical versions. The present application performs full update on the data in HIVE. With the passage of time, the data in HIVE / data to be synchronized / data in the storage table will have version differences; for example, the data in HIVE is updated every day at 23:00, so the version of the data to be synchronized generated on January 1, 2023 is 20230101, and the version of the data to be synchronized generated on January 2, 2023 is 20230102. Version 20230101 is earlier than version 20230102, and on January 2, 2023, for version 20230101 and version 20230102, version 20230101 can be a historical version, and version 20230102 can be a current version.

[0055] If the version state of the storage table is the current version, the storage table can also be recorded as the current version table, and if the version state of the storage table is the historical version, the storage table can also be recorded as the historical version table. It can be understood that the current version table stores the latest data, and the historical version table stores the data of the previous version (here, the latest and the previous version are not considered in the case of newly generated to-be-synchronized data. It can be understood that if there is already to-be-synchronized data, the data in the current version table can also be regarded as historical version data compared with the to-be-synchronized data).

[0056] It should be noted that the version states of the first storage table and the second storage table are different, that is, one of the first storage table and the second storage table is the current version table, and the other is the historical version table. The version states of the first storage table and the second storage table belong to opposite events in mathematics.

[0057] The state table records the version states of the first storage table and the second storage table. By reading the version states in the state table, the current version table and the historical version table can be obtained.

[0058] The table structure of the state table has only two columns, table_name and status. The table_name refers to the name of the storage table, for example, the first storage table can be named Table_a, and the second storage table can be named Table_b. The status indicates the version state of the storage table, which can be represented by Y and N. Y represents the current version (which can be understood as yes, the data in the storage table is the latest state), and N represents the historical version (which can be understood as no, the data in the storage table is not the latest state). In an embodiment, the form of the state table is shown in Table 1:

[0059] Table 1

[0060] table_name status Table_a Y Table_b N

[0061] In step S203, the to-be-synchronized data in the temporary table is written into the historical version table, and the version states of the historical version table and the current version table are exchanged after the writing is completed.

[0062] Specifically, the data in the current version table is already the latest data (except for the to-be-synchronized data), and the data in the historical version table is the data of the previous version (except for the to-be-synchronized data). The first storage table and the second storage table are cross-updated in the present application, and each update eliminates data that is older. Therefore, the to-be-synchronized data in the temporary table is written into the historical version table. After the writing is completed, the previous historical version table has essentially become a new current version table, and the previous current version table has essentially become a new historical version table. Therefore, it is also necessary to exchange the version states of the previous historical version table and the previous current version table.

[0063] Further, the step of writing the to-be-synchronized data in the temporary table into the history version table can include: emptying the history version table; writing the to-be-synchronized data in the temporary table into the emptied history version table and generating an index.

[0064] Specifically, when writing the to-be-synchronized data into the history version table, the history version table is emptied, including the index in the history version table. Then, the to-be-synchronized data in the temporary table is written into the emptied history version table and an index is generated for subsequent data retrieval.

[0065] The database has a current version table and a history version table, and the database provides data services by the current version table, and the history version table exists as a backup. Since the history version table does not provide data services, the speed of writing the to-be-synchronized data table into the history version table can be relatively slow, and an index can be generated in this process to facilitate subsequent data services.

[0066] In this embodiment, the history version table is emptied, the to-be-synchronized data in the temporary table is written into the emptied history version table, and an index is generated to facilitate subsequent data services according to the index.

[0067] Further, the step of exchanging the version states of the history version table and the current version table can include: in the state table, adjusting the version state of the history version table to the current version to obtain a new current version table, and adjusting the version state of the current version table to the history version to obtain a new history version table.

[0068] Specifically, the state table can record and determine the version states of the first storage table and the second storage table. The state table is modified to adjust the version state of the history version table to the current version (continuing the example above, modifying status in the state table from N to Y), to obtain a new current version table, and to adjust the version state of the current version table to the history version (continuing the example above, modifying status in the state table from Y to N), to obtain a new history version table.

[0069] In this embodiment, the state table can record and determine the version states of the storage tables, the state table is modified to adjust the version state of the history version table to the current version, thereby obtaining a new current version table, and to adjust the version state of the current version table to the history version, thereby obtaining a new history version table, and completing the exchange of the version states of the storage tables.

[0070] In step S204, data services are provided by the new current version table.

[0071] Specifically, after the to-be-synchronized data is written into the first storage table / second storage table, the synchronization of the to-be-synchronized data is completed. The database can provide data services, and the latest current version table provides data services, thereby ensuring the timeliness of the provided information.

[0072] Further, the step S204 can include receiving a data acquisition request of an application, querying the new current version table according to the data acquisition request to obtain a data query result, and sending the data query result to the application to provide data service to the application.

[0073] Specifically, the data acquisition request of the application is received, the application can be an application in a terminal device, the latest current version table is queried according to the data acquisition request to obtain a data query result. The data query result is sent to the application to provide data service to the application.

[0074] In the embodiment, the data acquisition request of the application is received, the new current version table is queried according to the data acquisition request to obtain a data query result, and the data query result is sent to the application. Since the new current version table contains the latest data, the data service is provided according to the new current version table, ensuring the accuracy and timeliness of the data service.

[0075] In the application, the SQOOP is used to quickly store the to-be-stored data into a temporary table to realize fast data storage. The first storage table and the second storage table are set in the database, the storage tables are divided into the current version table and the historical version table according to the version state of the storage table, and the current version table usually provides data service to the outside. When the to-be-synchronized data in the temporary table is written into the storage table, the to-be-synchronized data is written into the historical version table to clear the data a long time ago, and the cross update of the first storage table and the second storage table is realized. When the data is synchronized through the temporary table and the historical version table, the current version table can also normally provide data service. From the perspective of the database, the read-write separation is realized, the write operation of the database and the read operation of the database do not affect each other, and the risk that the data table is locked and cannot work normally is eliminated. The AB table mechanism of the historical version table and the current version table can provide data service and data update at any time and anywhere, can easily cope with the synchronization of a large amount of data, does not appear the blank period of the data table to the outside, does not affect the application of the database, and avoids the problem that the database cannot provide service.

[0076] In the embodiment, a temporary table, a first storage table, a second storage table and a state table are set in the database; the to-be-synchronized data is obtained from the HIVE through the SQOOP, and the to-be-synchronized data is quickly written into the temporary table to realize the quick data warehousing; the state table can record and determine the version state of the storage table, the state table is read to determine the current version table and the historical version table in the first storage table and the second storage table, and the data in the historical version table is the previous version of the data in the current version table; the database provides data services by using the current version table, the historical version table is updated during the data synchronization, and the current version table can work normally, so that the read-write separation is realized at the database level, the writing operation and the reading operation of the database do not affect each other, the risk that the data table is locked and cannot work normally is avoided, the AB table mechanism can easily cope with the synchronization of a large amount of data, the database does not display a blank data table to the outside, and the problem that the database cannot provide data services during the synchronization process is avoided; after the to-be-synchronized data is written into the historical version table, the data synchronization is completed, the version states of the historical version table and the current version table are exchanged, and the cross update of the historical version table and the current version table is realized.

[0077] Further, the above-mentioned data synchronization method based on the SQOOP can further include: when it is detected that the current version table has an exception, the version states of the current version table and the historical version table are exchanged.

[0078] Specifically, when the current version table in the database has an exception and cannot provide data services, and the historical version table is normal, the version states of the current version table and the historical version table can be exchanged, that is, the storage table storing the last version data is set as the current version table, and the storage table storing the latest version data is set as the historical version table, so that the data services are continuously provided, and the stability of the database is ensured.

[0079] In the embodiment, when it is detected that the current version table has an exception, the version states of the current version table and the historical version table are exchanged, so that the data services are continuously provided according to the normal storage table, and the stability of the database is improved.

[0080] It should be emphasized that, in order to further ensure the privacy and security of the above-mentioned to-be-synchronized data, the above-mentioned to-be-synchronized data can also be stored in a node of a blockchain.

[0081] The blockchain referred to in the present application is a new application mode of distributed data storage, point-to-point transmission, consensus mechanism, encryption algorithm and other computer technologies. The blockchain is essentially a decentralized database, which is a series of data blocks associated using cryptographic methods, each data block contains the information of a batch of network transactions, and is used to verify the validity (anti-fake) of the information and generate the next block. The blockchain can include a blockchain underlying platform, a platform product service layer and an application service layer.

[0082] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through computer readable instructions, which can be stored in a computer readable storage medium. The program can include the processes of the above-mentioned embodiment methods when executed. The storage medium can be a non-volatile storage medium such as a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0083] It should be understood that although each step in the flowchart of the accompanying drawings is shown in sequence according to the direction of the arrow, these steps are not necessarily executed in sequence according to the direction of the arrow. Unless otherwise specified herein, the execution of these steps is not strictly limited in sequence, and they can be executed in other sequences. Moreover, at least part of the steps in the flowchart of the accompanying drawings can include multiple sub-steps or multiple stages, which are not necessarily executed at the same time, but can be executed at different times, and the execution sequence is not necessarily sequential, but can be executed alternately or alternately with at least part of other steps or sub-steps or stages of other steps.

[0084] Further referring to Figure 3 , as an implementation of the method shown in Figure 2 , the present application provides an embodiment of a data synchronization device based on SQOOP, which corresponds to the method embodiment shown in Figure 2 , and the device can be applied to various electronic devices.

[0085] As shown in Figure 3 , the data synchronization device based on SQOOP 300 comprises a data acquisition module 301, a state reading module 302, a data writing module 303, and a service providing module 304.

[0086] The data acquisition module 301 is configured to acquire the data to be synchronized from the HIVE through the SQOOP and write the data to be synchronized into a temporary table.

[0087] The state reading module 302 is configured to read the version state in the state table to determine the current version table and the historical version table, the version state comprising a current version and a historical version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the historical version table corresponding to the historical version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different.

[0088] The data writing module 303 is configured to write the to-be-synchronized data in the temporary table into the historical version table, and to exchange the version states of the historical version table and the current version table after the writing is completed.

[0089] The service providing module 304 is configured to provide data service through the new current version table.

[0090] In the embodiment, the temporary table, the first storage table, the second storage table and the state table are set in the database; the to-be-synchronized data is obtained from the HIVE through the SQOOP, and the to-be-synchronized data is quickly written into the temporary table to realize the quick data storage; the state table can record and determine the version state of the storage table, and the state table is read to determine the current version table and the historical version table in the first storage table and the second storage table, and the data in the historical version table is the previous version of the data in the current version table; the database provides data service through the current version table, and the historical version table is updated during the data synchronization, while the current version table can work normally, the read-write separation is realized at the database level, the writing operation and the reading operation of the database do not affect each other, the risk that the data table is locked and cannot work normally is avoided, the AB table mechanism can easily cope with the synchronization of a large amount of data, and the database does not display the data table as blank to the outside, thereby avoiding the problem that the database cannot provide data service during the synchronization; after the to-be-synchronized data is written into the historical version table, the data synchronization is completed, the version states of the historical version table and the current version table are exchanged, and the cross update of the historical version table and the current version table is realized.

[0091] In some optional implementation manners of the embodiment, the data obtaining module 301 is further configured to directly write the to-be-synchronized data into the temporary table, and the to-be-synchronized data in the temporary table does not have an index.

[0092] In the embodiment, the to-be-synchronized data is directly written into the temporary table without generating an index, and the speed of writing the to-be-synchronized data into the temporary table is improved.

[0093] In some optional implementation manners of the embodiment, the database further includes a trigger table, and the data synchronization device based on the SQOOP 300 can further include a triggering module, wherein:

[0094] The triggering module is configured to synchronize the preset target type data to the trigger table, so as to execute the synchronization function through the trigger in the trigger table.

[0095] The state reading module 302 is further configured to read the version state in the state table according to the synchronization function, so as to determine the current version table and the historical version table.

[0096] In this embodiment, the preset target type data is synchronized to the trigger table to trigger the preset trigger; the triggered trigger calls and executes the synchronization function to read the version state in the state table to determine the current version table and the history version table, and orderly promote the subsequent data synchronization operation.

[0097] In some optional implementation of this embodiment, the data writing module 303 can include a history emptying submodule and a data writing submodule, wherein:

[0098] The history emptying submodule is configured to empty the history version table.

[0099] The data writing submodule is configured to write the to-be-synchronized data in the temporary table into the emptied history version table and generate an index.

[0100] In this embodiment, the history version table is emptied, the to-be-synchronized data in the temporary table is written into the emptied history version table, and an index is generated, which facilitates subsequent data service provision according to the index.

[0101] In some optional implementation of this embodiment, the data writing module 303 can also be configured to adjust the version state of the history version table to the current version in the state table to obtain a new current version table, and adjust the version state of the current version table to the history version to obtain a new history version table.

[0102] In this embodiment, the state table can record and determine the version state of the storage table, the state table is modified, the version state of the history version table is adjusted to the current version to obtain a new current version table, and the version state of the current version table is adjusted to the history version to obtain a new history version table, thereby completing the exchange of the version state of the storage table.

[0103] In some optional implementation of this embodiment, the service providing module 304 can include a request receiving submodule, a data querying submodule, and a result sending submodule, wherein:

[0104] The request receiving submodule is configured to receive a data acquisition request of an application.

[0105] The data querying submodule is configured to query the new current version table according to the data acquisition request to obtain a data query result.

[0106] The result sending submodule is configured to send the data query result to the application to provide data service to the application.

[0107] In the embodiment, a data acquisition request of an application is received, a new current version table is queried according to the data acquisition request, a data query result is obtained, and the data query result is sent to the application; since the new current version table contains the latest data, data services are provided according to the new current version table, and the accuracy and timeliness of the data services are ensured.

[0108] In some optional implementation of the embodiment, the SQOOP-based data synchronization device 300 can further include an exception switching module, wherein:

[0109] The exception switching module is configured to switch the version states of the current version table and the historical version table when it is detected that the current version table has an exception.

[0110] In the embodiment, when it is detected that the current version table has an exception, the version states of the current version table and the historical version table are switched, so that data services are continuously provided according to the normal storage table, and the stability of the database is improved.

[0111] To solve the above technical problems, the embodiment of the present application further provides a computer device. For details, please refer to Figure 4 , Figure 4 The basic structure block diagram of the computer device of the embodiment is shown in the figure.

[0112] The computer device 4 includes a memory 41, a processor 42, and a network interface 43 which are connected to each other through a system bus. It should be noted that only the computer device 4 with components 41-43 is shown in the figure, but it should be understood that all the shown components are not required to be implemented, and more or fewer components can be alternatively implemented. Among them, those skilled in the art can understand that the computer device herein is a device capable of automatically performing numerical calculation and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs), digital signal processors (DSPs), embedded devices, etc.

[0113] The computer device can be a desktop computer, a notebook computer, a palm computer, a cloud server, and other computing devices. The computer device can interact with a user through a keyboard, a mouse, a remote controller, a touchpad, a voice control device, and other manners.

[0114] The memory 41 includes at least one type of readable storage medium, including a flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory, etc.), a random access memory (RAM), a static random access memory (SRAM), a read-only memory (ROM), an electrically erasable programmable read-only memory (EEPROM), a programmable read-only memory (PROM), a magnetic memory, a magnetic disk, an optical disk, etc. In some embodiments, the memory 41 can be an internal storage unit of the computer device 4, such as a hard disk or a memory of the computer device 4. In other embodiments, the memory 41 can also be an external storage device of the computer device 4, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the computer device 4. Of course, the memory 41 can also include both the internal storage unit and the external storage device of the computer device 4. In this embodiment, the memory 41 is generally used to store an operating system and various application software installed on the computer device 4, such as computer readable instructions of the SQOOP-based data synchronization method, etc. In addition, the memory 41 can also be used to temporarily store various data that have been output or will be output.

[0115] The processor 42 can be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip in some embodiments. The processor 42 is generally used to control the overall operation of the computer device 4. In this embodiment, the processor 42 is used to run computer readable instructions or process data stored in the memory 41, such as computer readable instructions of the SQOOP-based data synchronization method.

[0116] The network interface 43 can include a wireless network interface or a wired network interface, which is generally used to establish a communication connection between the computer device 4 and other electronic devices.

[0117] The computer device provided in this embodiment can execute the SQOOP-based data synchronization method described above. Here, the SQOOP-based data synchronization method can be the SQOOP-based data synchronization method of each of the embodiments described above.

[0118] In the embodiment, a temporary table, a first storage table, a second storage table and a state table are set in the database; the to-be-synchronized data is obtained from the HIVE through the SQOOP and written into the temporary table quickly, so as to realize the quick data warehousing; the state table can record and determine the version state of the storage table, the state table is read to determine the current version table and the historical version table in the first storage table and the second storage table, and the data in the historical version table is the previous version of the data in the current version table; the database provides data services by using the current version table, the historical version table is updated during the data synchronization, and the current version table can work normally, so that the read-write separation is realized at the database level, the write operation and the read operation of the database are not affected, the risk that the data table is locked and cannot work normally is avoided, the AB table mechanism can easily cope with the synchronization of a large amount of data, the database does not display the data table as blank to the outside, and the problem that the database cannot provide data services during the synchronization process is avoided; after the to-be-synchronized data is written into the historical version table, the data synchronization is completed, the version states of the historical version table and the current version table are exchanged, and the cross update of the historical version table and the current version table is realized.

[0119] The application further provides another implementation, namely providing a computer readable storage medium, the computer readable storage medium stores computer readable instructions, the computer readable instructions can be executed by at least one processor to make the at least one processor execute the steps of the SQOOP-based data synchronization method as described above.

[0120] In the embodiment, a temporary table, a first storage table, a second storage table and a state table are set in the database; the to-be-synchronized data is obtained from the HIVE through the SQOOP and written into the temporary table quickly, so as to realize the quick data warehousing; the state table can record and determine the version state of the storage table, the state table is read to determine the current version table and the historical version table in the first storage table and the second storage table, and the data in the historical version table is the previous version of the data in the current version table; the database provides data services by using the current version table, the historical version table is updated during the data synchronization, and the current version table can work normally, so that the read-write separation is realized at the database level, the write operation and the read operation of the database are not affected, the risk that the data table is locked and cannot work normally is avoided, the AB table mechanism can easily cope with the synchronization of a large amount of data, the database does not display the data table as blank to the outside, and the problem that the database cannot provide data services during the synchronization process is avoided; after the to-be-synchronized data is written into the historical version table, the data synchronization is completed, the version states of the historical version table and the current version table are exchanged, and the cross update of the historical version table and the current version table is realized.

[0121] Those skilled in the art can clearly understand the above-mentioned embodiment method can be realized by means of software and the necessary general hardware platform, of course, it can also be realized by hardware, but in many cases, the former is a better embodiment. Based on such understanding, the technical solutions of the present application can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes a plurality of instructions for making a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) execute the method described in each embodiment of the present application.

[0122] Obviously, the above-described embodiments are only some of the embodiments of the present application, not all the embodiments, and the drawings show the preferred embodiments of the present application, but do not limit the patent scope of the present application. The present application can be implemented in many different forms, and conversely, the purpose of providing these embodiments is to make the disclosure of the present application more thorough and comprehensive. Although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions recorded in the foregoing specific embodiments, or make equivalent replacements to some of the technical features. Any equivalent structure made by using the content of the specification and drawings, directly or indirectly applied to other related technical fields, is also within the scope of the patent protection of the present application.

Claims

1. A SQOOP-based data synchronization method, characterized in that, A temporary table, a first storage table, a second storage table and a state table are set in a database, comprising the following steps: The data to be synchronized is obtained from the HIVE through the SQOOP, and the data to be synchronized is written into the temporary table, wherein the data to be synchronized in the temporary table has no index; A version state in the state table is read to determine a current version table and a history version table, the version state comprising a current version and a history version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the history version table corresponding to the history version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different; The data to be synchronized in the temporary table is written into the history version table, and the version states of the history version table and the current version table are exchanged after the writing is completed; Data service is provided through a new current version table; The step of writing the data to be synchronized in the temporary table into the history version table comprises: The history version table is emptied; The data to be synchronized in the temporary table is written into the emptied history version table, and an index is generated.

2. The SQOOP-based data synchronization method of claim 1, wherein, The step of writing the data to be synchronized into the temporary table comprises: The data to be synchronized is directly written into the temporary table, wherein the data to be synchronized in the temporary table has no index.

3. The SQOOP-based data synchronization method of claim 1, wherein, The database further comprises a trigger table, and after the step of writing the data to be synchronized into the temporary table, the method further comprises: Target type data is synchronized to the trigger table to execute a synchronization function through a trigger in the trigger table; According to the synchronization function, the step of reading the version state in the state table to determine the current version table and the history version table is executed.

4. The SQOOP-based data synchronization method of claim 1, wherein, The step of exchanging the version states of the history version table and the current version table comprises: In the state table, the version state of the history version table is adjusted to a current version to obtain a new current version table, and the version state of the current version table is adjusted to a history version to obtain a new history version table.

5. The SQOOP-based data synchronization method of claim 1, wherein, The step of providing data service through the new current version table comprises: A data acquisition request of an application is received; A data query result is obtained by querying the new current version table according to the data acquisition request; The data query result is sent to the application to provide data service to the application.

6. The SQOOP-based data synchronization method of claim 1, wherein, The method further comprises: When it is detected that the current version table has an exception, the version states of the current version table and the history version table are exchanged.

7. A data synchronization apparatus based on SQOOP, characterized by, A temporary table, a first storage table, a second storage table and a state table are set in a database, comprising: A data acquisition module is configured to obtain data to be synchronized from the HIVE through the SQOOP, and write the data to be synchronized into the temporary table, wherein the data to be synchronized in the temporary table has no index; The state reading module is configured to read a version state in the state table to determine a current version table and a history version table, the version state including a current version and a history version, the current version table corresponding to the current version being one of the first storage table and the second storage table, the history version table corresponding to the history version being one of the first storage table and the second storage table, and the version states of the first storage table and the second storage table being different; The data writing module is configured to write the to-be-synchronized data in the temporary table into the history version table, and to exchange the version states of the history version table and the current version table after the writing is completed; The service providing module is configured to provide data service through a new current version table. The data writing module includes a history emptying submodule and a data writing submodule, and wherein: The history emptying submodule is configured to empty the history version table. The data writing submodule is configured to write the to-be-synchronized data in the temporary table into the emptied history version table and to generate an index. 8.A computer device, comprising a memory and a processor, the memory storing computer readable instructions, and the processor implementing steps of the SQOOP-based data synchronization method according to any one of claims 1 to 6 when executing the computer readable instructions.

9. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer readable instructions, and the computer readable instructions implement steps of the SQOOP-based data synchronization method according to any one of claims 1 to 6 when executed by a processor.

Images