Sqoop-based business data cleaning method and related device thereof

Abstract

The embodiment of the application belongs to the technical field of financial technology, is applied to the field of big data security cleaning, relates to a business data cleaning method based on Sqoop and a related device thereof, and comprises the following steps: performing encryption processing on to-be-cleaned plaintext data, synchronizing the encrypted data to a target data warehouse, performing re-encryption processing on the first encrypted format data, exporting the encryption result to a newly created temporary table, merging the newly created temporary table into the target relational business database, and cleaning the to-be-cleaned plaintext data. The encryption operation is directly performed before transmission by using the Sqoop tool, the computing power resources of the data warehouse and the Sqoop tool are reasonably utilized, and the data migration and cleaning efficiency is accelerated. Moreover, through twice encryption, the data is always in an encrypted state in the data cleaning transmission process in the big data scenario, the risk of sensitive data leakage is avoided, finally, only the second encrypted format data is retained in the relational database after cleaning, and the risk of post-leakage of plaintext data is avoided.

Description

Technical Field

[0001] This application relates to the field of financial technology, specifically to the field of big data security cleaning, and particularly to a business data cleaning method based on Sqoop and related equipment. Background Technology

[0002] With the promulgation of the Data Security Law and the Personal Information Protection Law, companies are paying increasing attention to data security and are gradually making it a strategic direction in their data operations. Traditional insurance companies' business involves encrypting personal information such as names, addresses, phone numbers, and identification numbers, or vehicle information such as vehicle identification numbers (VINs) and license plate numbers, and they accumulate large amounts of historical data. According to relevant laws and regulations, all historical sensitive data in insurance companies must be encrypted.

[0003] Especially in the field of fintech, with the development of the financial industry, the data involved includes customer personal privacy data and transaction data, all of which are sensitive information that needs to be encrypted. However, many business lines rely on plaintext information to empower users and external companies, and hastily replacing it with ciphertext can affect business operations and even cause losses. Therefore, to achieve a smooth migration from plaintext to ciphertext, traditional financial business databases are generally relational databases such as Oracle, MySQL, and PostgreSQL. When encrypting large amounts of existing data, directly performing encryption operations within relational databases before migration and cleaning can easily lead to low encryption efficiency, and relational databases have limited computing power. Therefore, existing technologies still suffer from the problem of unreasonable allocation of encryption computing power when performing sensitive data migration, cleaning, and encryption processing on a large scale, resulting in low data cleaning efficiency. Summary of the Invention

[0004] The purpose of this application is to propose a business data cleansing method and related equipment based on Sqoop, so as to solve the problem that the existing technology still has the problem of unreasonable allocation of encryption computing power when performing sensitive data migration, cleansing and encryption processing on a large scale, resulting in low data cleansing efficiency.

[0005] To address the aforementioned technical problems, this application provides a Sqoop-based method for cleaning up business data, employing the following technical solution:

[0006] A Sqoop-based method for cleaning up business data includes the following steps:

[0007] Read the plaintext data to be cleaned from the target relational business database;

[0008] The plaintext data to be cleaned is encrypted to obtain the first encrypted format data of the plaintext data to be cleaned;

[0009] Based on the first target command preset by the Sqoop data synchronization component, the first encrypted format data of the plaintext data to be cleaned is synchronized to the target data warehouse.

[0010] The first encrypted data is encrypted again to obtain the second encrypted data of the plaintext data to be cleaned.

[0011] Based on the second target command preset by the Sqoop data synchronization component, the second encrypted format data of the plaintext data to be cleaned is exported from the target data warehouse to a newly created temporary table;

[0012] The second encrypted format data of the plaintext data to be cleaned in the newly created temporary table is merged into the target relational business database to clean the plaintext data to be cleaned, thus completing the cleaning of the target relational business database.

[0013] Furthermore, the step of reading the plaintext data to be cleaned from the target relational business database specifically includes:

[0014] Obtain all representation fields included in the plaintext data to be cleaned, the number of data entries corresponding to each representation field, and the unique identifier information corresponding to each plaintext data;

[0015] Based on all the representation fields included in the plaintext data to be cleaned, the number of data entries corresponding to each representation field, and the unique identifier information corresponding to each plaintext data, a new temporary table with blank data content is created in the target relational business database.

[0016] Furthermore, the step of encrypting the plaintext data to be cleaned to obtain the first encrypted format data of the plaintext data to be cleaned specifically includes:

[0017] Retrieve the first encryption function pre-created based on the imported encryption / decryption JAR package;

[0018] Call the first encryption function, take the plaintext data to be cleaned as the first input parameter of the first encryption function, encrypt the first input parameter, and obtain the first encrypted format data;

[0019] The step of encrypting the plaintext data to be cleaned to obtain the first encrypted format data of the plaintext data to be cleaned further includes:

[0020] The unique identifier information corresponding to each plaintext data in the plaintext data to be cleaned is used as the second input parameter of the first encryption function, and the second input parameter is associated one by one with each corresponding encrypted format data in the first encrypted format data.

[0021] Furthermore, the first target command is the Sqoop import command. The step of synchronizing the first encrypted format data of the plaintext data to be cleaned to the target data warehouse based on the first target command preset by the Sqoop data synchronization component specifically includes:

[0022] The first encrypted data is synchronized to the target data warehouse according to the Sqoop import command. The Sqoop import command is a command that imports data from a target relational business database into the data warehouse through the Sqoop data synchronization component. The target relational business database includes an Oracle database, and the data warehouse includes a Hive database.

[0023] Furthermore, the step of re-encrypting the first encrypted data to obtain the second encrypted data of the plaintext data to be cleaned specifically includes:

[0024] Obtain the second encryption function pre-created based on the imported encryption / decryption JAR package;

[0025] The second encryption function is called, and the first encrypted format data is used as the first input parameter of the second encryption function. The first input parameter is encrypted to obtain the second encrypted format data.

[0026] The step of re-encrypting the first encrypted data to obtain the second encrypted data of the plaintext data to be cleaned further includes:

[0027] Based on the one-to-one association between the second input parameter of the first encryption function and each corresponding encrypted format data in the first encrypted format data, the unique identification information corresponding to each encrypted format data in the first encrypted format data is determined.

[0028] The unique identifier information is used as the second input parameter of the second encryption function, and the second input parameter is associated one by one with each corresponding encrypted format data in the second encryption format data.

[0029] Furthermore, the second target command is the Sqoop export command. The step of exporting the second encrypted format data of the plaintext data to be cleaned from the target data warehouse to a newly created temporary table based on the second target command preset by the Sqoop data synchronization component specifically includes:

[0030] The second encrypted data is exported to the newly created temporary table according to the Sqoop export command, wherein the Sqoop export command is a command that exports data in the data warehouse to the target relational business database through the Sqoop data synchronization component, and the newly created temporary table is a specific blank table in the target relational business database.

[0031] Furthermore, before performing the step of merging the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database to clean the plaintext data to be cleaned, the method further includes:

[0032] Pre-create corresponding ciphertext columns in each target table within the target relational business database based on all the characterization fields included in the plaintext data to be cleaned;

[0033] Based on a preset function execution monitoring component, determine whether the second encryption function has been executed.

[0034] If the second encryption function has not finished executing, continue to wait for the second encryption function to perform the encryption operation;

[0035] If the second encryption function is completed, then according to the preset execution log, the one-to-one association between the second input parameter of the second encryption function and each corresponding encrypted format data in the second encryption format data is identified, and according to the one-to-one association, the unique identification information corresponding to each encrypted format data in the second encryption format data is determined.

[0036] The step of merging the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, and cleaning the plaintext data to be cleaned, specifically includes:

[0037] Based on the unique identifier information corresponding to each encrypted format data in the second encrypted format data and the unique identifier information of all plaintext data to be cleaned in the target relational business database, each encrypted format data in the second encrypted format data is merged into the corresponding ciphertext column cell;

[0038] Based on the preset database monitoring component, determine whether the encrypted data in the second encrypted format data has been completely merged;

[0039] If the encrypted data in the second encrypted format data has not been completely merged, then continue to wait for the merging operation;

[0040] If all encrypted data in the second encrypted format data are merged, then all plaintext data to be cleaned in the target relational business database will be updated to null values, thus completing the cleaning of the plaintext data to be cleaned.

[0041] To address the aforementioned technical problems, this application also provides a Sqoop-based business data cleansing device, employing the following technical solution:

[0042] A Sqoop-based business data cleansing device includes:

[0043] The plaintext to be cleaned reading module is used to read plaintext data to be cleaned from the target relational business database;

[0044] The first encryption module is used to encrypt the plaintext data to be cleaned and obtain the first encrypted format data of the plaintext data to be cleaned.

[0045] The data warehouse synchronization module is used to synchronize the first encrypted format data of the plaintext data to be cleaned to the target data warehouse based on the first target command preset by the Sqoop data synchronization component.

[0046] The second encryption module is used to encrypt the first encrypted format data again to obtain the second encrypted format data of the plaintext data to be cleaned;

[0047] The data warehouse export module is used to export the second encrypted format data of the plaintext data to be cleaned from the target data warehouse to a newly created temporary table based on the second target command preset by the Sqoop data synchronization component.

[0048] The merged business database module is used to merge the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, clean the plaintext data to be cleaned, and complete the cleaning of the target relational business database.

[0049] To address the aforementioned technical problems, this application also provides a computer device that employs the following technical solution:

[0050] A computer device includes a memory and a processor, wherein the memory stores computer-readable instructions, and the processor executes the computer-readable instructions to implement the steps of the Sqoop-based business data cleansing method described above.

[0051] To address the aforementioned technical problems, this application also provides a computer-readable storage medium, employing the technical solution described below:

[0052] A computer-readable storage medium storing computer-readable instructions, which, when executed by a processor, implement the steps of the Sqoop-based business data cleansing method described above.

[0053] Compared with the prior art, the embodiments of this application have the following main advantages:

[0054] The Sqoop-based data cleansing method described in this application involves: reading plaintext data to be cleaned from a target relational database; encrypting the plaintext data to obtain its first encrypted format; synchronizing the first encrypted format data to a target data warehouse using a preset first target command from the Sqoop data transmission component; re-encrypting the first encrypted format data to obtain its second encrypted format; exporting the second encrypted format data from the target data warehouse to a newly created temporary table using a preset second target command from the Sqoop data transmission component; and merging the second encrypted format data from the newly created temporary table back into the target relational database, thus cleaning the plaintext data and completing the cleansing of the target relational database. This method avoids performing calculations within the relational database, directly performing encryption calculations before transmission using the Sqoop tool, effectively utilizing the computing resources of the data warehouse and the Sqoop tool, and accelerating data migration and cleansing efficiency. Furthermore, by using double encryption, the data remains encrypted throughout the data cleaning and transmission process in big data scenarios, thus avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, avoiding the risk of plaintext data leakage. Attached Figure Description

[0055] To more clearly illustrate the solutions in this application, the accompanying drawings used in the description of the embodiments of this application will be briefly introduced below. Obviously, the accompanying drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0056] Figure 1 This is an exemplary system architecture diagram to which this application can be applied;

[0057] Figure 2 A flowchart of an embodiment of the Sqoop-based business data cleansing method according to this application;

[0058] Figure 3 yes Figure 2 A flowchart of a specific embodiment of step 202 shown;

[0059] Figure 4 yes Figure 2 A flowchart of a specific embodiment of step 204 shown;

[0060] Figure 5 This is a flowchart of a specific embodiment of determining the execution status of the second encryption function before step 206 in the embodiments of this application;

[0061] Figure 6 yes Figure 2 A flowchart of a specific embodiment of step 206 shown;

[0062] Figure 7 A schematic diagram of a structural embodiment of a Sqoop-based business data cleansing device according to this application;

[0063] Figure 8 A schematic diagram of the structure of an embodiment of the computer device according to this application. Detailed Implementation

[0064] 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 pertains; the terminology used herein in the specification of the application is for the purpose of describing particular embodiments only and is not intended to be limiting of the application; the terms "comprising" and "having," and any variations thereof, in the specification, claims, and foregoing drawings of this application, are intended to cover non-exclusive inclusion. The terms "first," "second," etc., in the specification, claims, or foregoing drawings of this application are used to distinguish different objects, not to describe a particular order.

[0065] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0066] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings.

[0067] like Figure 1As shown, system architecture 100 may include terminal devices 101, 102, and 103, a network 104, and a server 105. Network 104 serves as the medium for providing communication links between terminal devices 101, 102, and 103 and server 105. Network 104 may include various connection types, such as wired or wireless communication links, or fiber optic cables, etc.

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

[0069] Terminal devices 101, 102, and 103 can be various electronic devices with displays and support web browsing, including but not limited to smartphones, tablets, e-book readers, MP3 players (Moving Picture Experts Group Audio Layer III), MP4 players (Moving Picture Experts Group Audio Layer IV), laptops, and desktop computers, etc.

[0070] Server 105 can be a server that provides various services, such as a backend server that supports the pages displayed on terminal devices 101, 102, and 103.

[0071] It should be noted that the Sqoop-based business data cleansing method provided in this application is generally executed by a server / terminal device, and correspondingly, the Sqoop-based business data cleansing device is generally installed in the server / terminal device.

[0072] It should be understood that Figure 1 The number of terminal devices, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of terminal devices, networks, and servers can be included.

[0073] First, let me introduce the Sqoop tool. Sqoop is an open-source data transfer and synchronization tool, mainly used for transferring data between Hive and traditional databases. It can import data from a relational database, such as MySQL, Oracle, or Postgres, into the HDFS of a Hive data warehouse, and it can also import data from HDFS into a relational database. It supports massive amounts of data and can be applied to data transfer between data warehouses and relational databases in big data scenarios.

[0074] Continue to refer to Figure 2 The diagram illustrates a flowchart of an embodiment of the Sqoop-based business data cleansing method according to this application. The Sqoop-based business data cleansing method includes the following steps:

[0075] Step 201: Read the plaintext data to be cleaned from the target relational business database.

[0076] In this embodiment, the step of reading the plaintext data to be cleaned from the target relational business database specifically includes: obtaining all the representation fields included in the plaintext data to be cleaned, the number of data entries corresponding to each representation field, and the unique identifier information corresponding to each plaintext data; and creating a new temporary table with blank data content in the target relational business database based on all the representation fields included in the plaintext data to be cleaned, the number of data entries corresponding to each representation field, and the unique identifier information corresponding to each plaintext data.

[0077] In this embodiment, the plaintext data to be cleaned can be sensitive data in big data scenarios, such as user information, insurance amount-related information, platform account password information, and bank transaction information in insurance purchase and insurance claims business processes in big data business scenarios such as property insurance, insurance, and claims.

[0078] Taking user information in the context of big data business in property insurance as an example, the plaintext data to be cleaned here includes user name information, user account information, user password information, and related insurance amount information. At this time, the relational database will set corresponding representation fields based on this user information, such as user ID, name, account, password, and insurance amount. For each user, the user ID will be selected as a unique identifier to distinguish them.

[0079] In this embodiment, the cleaning mainly involves encrypting sensitive data in the original relational business database. When the data volume is small, encryption can be performed directly in the relational database, which consumes less computing power. However, when big data or data warehouse migration is involved, using encryption in the relational database will consume a lot of computing power. Therefore, this application provides a new data cleaning method, namely, a business data cleaning method based on Sqoop, which avoids performing operations in the relational database and performs encryption operations directly before transmission by the Sqoop tool. This makes reasonable use of the computing power resources of the data warehouse and the Sqoop tool, and speeds up data migration and cleaning efficiency.

[0080] Step 202: Encrypt the plaintext data to be cleaned to obtain the first encrypted format data of the plaintext data to be cleaned.

[0081] Continue to refer to Figure 3 , Figure 3 yes Figure 2 A flowchart of a specific embodiment of step 202 shown includes:

[0082] Step 301: Obtain the first encryption function pre-created based on the imported encryption / decryption JAR package, wherein the encryption / decryption JAR package can be a hive-udf-jar package;

[0083] In this embodiment, the encryption / decryption JAR package is imported into the Sqoop tool, and the first encryption process occurs before the Sqoop tool reads the plaintext data to be cleaned from the relational database and transmits it to the data warehouse. Its main purpose is to perform encryption processing before transmission, so as to avoid data leakage due to security vulnerabilities in the encryption process. Even if data leakage occurs, only the first encrypted ciphertext will be leaked, ensuring the secure operation of the data.

[0084] Step 302: Call the first encryption function, take the plaintext data to be cleaned as the first input parameter of the first encryption function, encrypt the first input parameter, and obtain the first encrypted format data;

[0085] Step 303: Use the unique identifier information corresponding to each plaintext data in the plaintext data to be cleaned as the second input parameter of the first encryption function, and associate the second input parameter with each corresponding encrypted format data in the first encrypted format data.

[0086] Continuing with the above property insurance scenario example, the plaintext data to be cleaned includes user name information, user account information, user password information, and related insurance amount information, while the unique identifier is the user ID. By encrypting the plaintext data to be cleaned, i.e., sensitive data, using the Sqoop tool, encryption is performed before the sensitive data is transmitted from the relational business database to the data warehouse. This avoids data leakage due to security vulnerabilities during the cleansing and transmission process. Even if data leakage occurs, only the first encrypted ciphertext will be leaked, ensuring the secure operation of the data.

[0087] Step 203: Based on the first target command preset by the Sqoop data synchronization component, synchronize the first encrypted format data of the plaintext data to be cleaned to the target data warehouse.

[0088] In this embodiment, the first target command is the Sqoop import command. The step of synchronizing the first encrypted format data of the plaintext data to be cleaned to the target data warehouse based on the first target command preset by the Sqoop data synchronization component specifically includes: synchronizing the first encrypted format data to the target data warehouse according to the Sqoop import command. The Sqoop import command is a command that imports data from a target relational business database into the data warehouse through the Sqoop data synchronization component. The target relational business database includes an Oracle database, and the data warehouse includes a Hive database.

[0089] The Sqoop data transfer and synchronization tool has two main command operation characters: import and export. Import imports data from a relational database into the HDFS of a Hive data warehouse, while export imports data from HDFS into a relational database.

[0090] Step 204: Encrypt the first encrypted data again to obtain the second encrypted data of the plaintext data to be cleaned.

[0091] Continue to refer to Figure 4 , Figure 4 yes Figure 2 A flowchart of a specific embodiment of step 204 shown includes:

[0092] Step 401: Obtain the second encryption function pre-created based on the imported encryption / decryption JAR package;

[0093] In this embodiment, the encryption / decryption JAR package is imported into the Sqoop tool. The second encryption process occurs before the Sqoop tool reads the first encrypted data from the data warehouse and transmits it to the relational database. Its main purpose is to perform encryption processing before transmission, so as to avoid data leakage due to security vulnerabilities in the encryption process. Even if data leakage occurs, only the second encrypted ciphertext will be leaked, ensuring the secure operation of the data.

[0094] Step 402: Call the second encryption function, take the first encrypted format data as the first input parameter of the second encryption function, encrypt the first input parameter, and obtain the second encrypted format data;

[0095] Step 403: Based on the one-to-one association between the second input parameter of the first encryption function and each corresponding encryption format data in the first encryption format data, determine the unique identification information corresponding to each encryption format data in the first encryption format data;

[0096] Step 404: Use the unique identifier information as the second input parameter of the second encryption function, and associate the second input parameter with each corresponding encrypted format data in the second encrypted format data.

[0097] The second encryption process occurs before the Sqoop tool reads the first encrypted data from the data warehouse and transmits it to the relational database. Its main purpose is to perform encryption before transmission, avoiding data leakage due to security vulnerabilities during the encryption process. Even if data leakage occurs, only the second encrypted ciphertext will be leaked, ensuring the secure transmission of data.

[0098] Step 205: Based on the second target command preset by the Sqoop data synchronization component, the second encrypted format data of the plaintext data to be cleaned is exported from the target data warehouse to a newly created temporary table.

[0099] In this embodiment, the second target command is the Sqoop export command. The step of exporting the second encrypted format data of the plaintext data to be cleaned from the target data warehouse to a newly created temporary table based on the second target command preset by the Sqoop data synchronization component specifically includes: exporting the second encrypted format data to the newly created temporary table according to the Sqoop export command. The Sqoop export command is a command that exports data from the data warehouse to the target relational business database through the Sqoop data synchronization component. The newly created temporary table is a specific blank table in the target relational business database. The specific blank table is the newly created temporary table with blank data content created in the target relational business database in step 201.

[0100] Step 206: Merge the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, clean the plaintext data to be cleaned, and complete the cleaning of the target relational business database.

[0101] Continue to refer to Figure 5 , Figure 5 This is a flowchart of a specific embodiment of determining the execution status of the second encryption function before step 206 in the embodiments of this application, including:

[0102] Step 501: Create corresponding ciphertext columns in advance in each target table of the target relational business database based on all the representation fields included in the plaintext data to be cleaned;

[0103] Step 502: Based on the preset function execution monitoring component, determine whether the second encryption function has been executed.

[0104] In this embodiment, the function execution monitoring component can be a log recording monitoring component or a process monitoring component built into the program.

[0105] Step 503: If the second encryption function has not finished executing, continue to wait for the second encryption function to perform the encryption operation;

[0106] In this embodiment, the continued waiting can be implemented using a locking or blocking mechanism.

[0107] Step 504: If the second encryption function has been executed, then according to the preset execution log, identify the one-to-one association between the second input parameter of the second encryption function and each corresponding encrypted format data in the second encryption format data, and determine the unique identification information corresponding to each encrypted format data in the second encryption format data according to the one-to-one association.

[0108] By determining whether the second encryption function has finished executing, and then performing the merging process after its completion, the simultaneous processing of transmission and merging is avoided, allowing subsequent execution to focus solely on merging and improving merging efficiency.

[0109] Continue to refer to Figure 6 , Figure 6 yes Figure 2 A flowchart of a specific embodiment of step 206 shown includes:

[0110] Step 601: Based on the unique identifier information corresponding to each encrypted format data in the second encrypted format data and the unique identifier information of all plaintext data to be cleaned in the target relational business database, merge each encrypted format data in the second encrypted format data into the corresponding cell of the ciphertext column;

[0111] Step 602: Based on the preset database monitoring component, determine whether the encrypted data in the second encrypted format data has been completely merged;

[0112] In this embodiment, the database monitoring component can be implemented as a transaction management component built into the database.

[0113] Step 603: If the encrypted data in the second encrypted format data has not been completely merged, continue to wait for the merging operation;

[0114] In this embodiment, the continued waiting can also be implemented using a locking or blocking mechanism.

[0115] Step 604: If all encrypted format data in the second encrypted format data have been merged, then all plaintext data to be cleaned in the target relational business database are updated to null values, thus completing the cleaning of the plaintext data to be cleaned.

[0116] After merging, all plaintext data to be cleaned is updated to null values ​​to complete the cleaning of the plaintext data. Only the second encrypted data is cached in the target relational database. Through double encryption, the data is always in an encrypted state during the data cleaning and transmission process in big data scenarios, avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, avoiding the risk of subsequent leakage of plaintext data.

[0117] This application cleans the plaintext data to be cleaned from the target relational database; encrypts the plaintext data to obtain its first encrypted format; synchronizes this first encrypted format data to the target data warehouse using a first target command preset by the Sqoop data transfer component; re-encrypts the first encrypted format data to obtain its second encrypted format; exports this second encrypted format data from the target data warehouse to a newly created temporary table using a second target command preset by the Sqoop data transfer component; and merges the second encrypted format data from the newly created temporary table back into the target relational database, thus cleaning the plaintext data and completing the cleaning of the target relational database. This avoids performing calculations within the relational database, performing encryption calculations directly before transmission using the Sqoop tool, effectively utilizing the computing resources of the data warehouse and the Sqoop tool, and accelerating data migration and cleaning efficiency. Furthermore, by using double encryption, the data remains encrypted throughout the data cleaning and transmission process in big data scenarios, thus avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, avoiding the risk of plaintext data leakage.

[0118] The embodiments of this application can acquire and process relevant data based on artificial intelligence technology. Artificial intelligence (AI) refers to the theories, methods, technologies, and application systems that use digital computers or machines controlled by digital computers to simulate, extend, and expand human intelligence, perceive the environment, acquire knowledge, and use that knowledge to obtain optimal results.

[0119] Foundational technologies for artificial intelligence generally include sensors, dedicated AI chips, cloud computing, distributed storage, big data processing, operating / interactive systems, and mechatronics. AI software technologies mainly encompass computer vision, robotics, biometrics, speech processing, natural language processing, and machine learning / deep learning.

[0120] In this embodiment, by performing encryption operations directly before transmission using the Sqoop tool, the computing resources of the data warehouse and the Sqoop tool are effectively utilized, accelerating data migration and cleaning efficiency. Furthermore, through double encryption, the data remains encrypted throughout the data cleaning and transmission process in big data scenarios, avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, preventing the risk of subsequent leakage of plaintext data.

[0121] Further reference Figure 7 As a response to the above Figure 2 The implementation of the method shown in this application provides an embodiment of a business data cleansing device based on Sqoop. This device embodiment is similar to... Figure 2 Corresponding to the method embodiments shown, this device can be specifically applied to various electronic devices.

[0122] like Figure 7 As shown, the Sqoop-based business data cleansing device 700 described in this embodiment includes: a plaintext reading module 701, a first encryption module 702, a data warehouse synchronization module 703, a second encryption module 704, a data warehouse export module 705, and a business database merging module 706. Wherein:

[0123] The plaintext reading module 701 is used to read plaintext data to be cleaned from the target relational business database.

[0124] The first encryption module 702 is used to encrypt the plaintext data to be cleaned and obtain the first encrypted format data of the plaintext data to be cleaned.

[0125] The data warehouse synchronization module 703 is used to synchronize the first encrypted format data of the plaintext data to be cleaned to the target data warehouse based on the first target command preset by the Sqoop data synchronization component.

[0126] The second encryption module 704 is used to encrypt the first encryption format data again to obtain the second encryption format data of the plaintext data to be cleaned;

[0127] The data warehouse export module 705 is used to export the second encrypted format data of the plaintext data to be cleaned from the target data warehouse to a newly created temporary table based on the second target command preset by the Sqoop data synchronization component.

[0128] The merged business database module 706 is used to merge the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, clean the plaintext data to be cleaned, and complete the cleaning of the target relational business database.

[0129] In some specific embodiments of this application, the Sqoop-based business data cleansing device 700 further includes a function execution completion judgment module. This module is used to determine whether the second encryption function has been completed based on a preset function execution monitoring component. If the second encryption function has not been completed, it continues to wait for the second encryption function to perform encryption operations. If the second encryption function has been completed, it identifies the one-to-one association between the second input parameter of the second encryption function and each corresponding encrypted format data in the second encrypted format data according to a preset execution log. Based on the one-to-one association, it determines the unique identifier information corresponding to each encrypted format data in the second encrypted format data.

[0130] In some specific embodiments of this application, the Sqoop-based business data cleansing device 700 further includes a merge completion judgment module. The merge completion judgment module is used to determine, according to a preset database monitoring component, whether each encrypted format data in the second encrypted format data has been merged. If each encrypted format data in the second encrypted format data has not been merged, the merge operation continues to wait. If each encrypted format data in the second encrypted format data has been merged, all plaintext data to be cleaned in the target relational business database is updated to null values, thus completing the cleansing of the plaintext data to be cleaned.

[0131] This application cleans the plaintext data to be cleaned from the target relational database; encrypts the plaintext data to obtain its first encrypted format; synchronizes this first encrypted format data to the target data warehouse using a first target command preset by the Sqoop data transfer component; re-encrypts the first encrypted format data to obtain its second encrypted format; exports this second encrypted format data from the target data warehouse to a newly created temporary table using a second target command preset by the Sqoop data transfer component; and merges the second encrypted format data from the newly created temporary table back into the target relational database, thus cleaning the plaintext data and completing the cleaning of the target relational database. This avoids performing calculations within the relational database, performing encryption calculations directly before transmission using the Sqoop tool, effectively utilizing the computing resources of the data warehouse and the Sqoop tool, and accelerating data migration and cleaning efficiency. Furthermore, by using double encryption, the data remains encrypted throughout the data cleaning and transmission process in big data scenarios, thus avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, avoiding the risk of plaintext data leakage.

[0132] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by instructing related hardware through computer-readable instructions. These computer-readable instructions can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the methods described above. The aforementioned storage medium can be a non-volatile storage medium such as a magnetic disk, optical disk, or read-only memory (ROM), or random access memory (RAM).

[0133] It should be understood that although the steps in the flowcharts of the accompanying figures are shown sequentially as indicated by the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the accompanying figures may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the sub-steps or stages of other steps.

[0134] To address the aforementioned technical problems, embodiments of this application also provide a computer device. Please refer to [link / reference needed]. Figure 8 , Figure 8This is a basic structural block diagram of the computer device in this embodiment.

[0135] The computer device 8 includes a memory 8a, a processor 8b, and a network interface 8c that are interconnected via a system bus. It should be noted that only the computer device 8 with components 8a-8c is shown in the figure; however, it should be understood that it is not required to implement all the shown components, and more or fewer components can be implemented alternatively. Those skilled in the art will understand that the computer device described here is a device capable of automatically performing numerical calculations 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.

[0136] The computer device can be a desktop computer, laptop, handheld computer, or cloud server, etc. The computer device can interact with the user via a keyboard, mouse, remote control, touchpad, or voice control.

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

[0138] In some embodiments, the processor 8b may be a central processing unit (CPU), controller, microcontroller, microprocessor, or other data processing chip. The processor 8b is typically used to control the overall operation of the computer device 8. In this embodiment, the processor 8b is used to execute computer-readable instructions stored in the memory 8a or to process data, for example, to execute computer-readable instructions for the Sqoop-based business data cleansing method.

[0139] The network interface 8c may include a wireless network interface or a wired network interface, which is typically used to establish communication connections between the computer device 8 and other electronic devices.

[0140] The computer equipment proposed in this embodiment belongs to the field of financial technology and is applied to the field of big data security cleaning. This application reads the plaintext data to be cleaned from the target relational database; encrypts the plaintext data to obtain the first encrypted format data; synchronizes the first encrypted format data to the target data warehouse based on a first target command preset by the Sqoop data transmission component; encrypts the first encrypted format data again to obtain the second encrypted format data; exports the second encrypted format data from the target data warehouse to a newly created temporary table based on a second target command preset by the Sqoop data transmission component; merges the second encrypted format data from the newly created temporary table into the target relational database, thus cleaning the plaintext data and completing the cleaning of the target relational database. This avoids performing calculations in the relational database, performing encryption calculations directly before transmission by the Sqoop tool, rationally utilizing the computing resources of the data warehouse and the Sqoop tool, and accelerating data migration and cleaning efficiency. Furthermore, by using double encryption, the data remains encrypted throughout the data cleaning and transmission process in big data scenarios, thus avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, avoiding the risk of plaintext data leakage.

[0141] This application also provides another implementation, namely, a computer-readable storage medium storing computer-readable instructions that can be executed by a processor to cause the processor to perform the steps of the Sqoop-based business data cleansing method described above.

[0142] The computer-readable storage medium proposed in this embodiment belongs to the field of financial technology and is applied to the field of big data security cleaning. This application reads plaintext data to be cleaned from a target relational database; encrypts the plaintext data to obtain its first encrypted format; synchronizes the first encrypted format data to a target data warehouse based on a first target command preset by the Sqoop data transmission component; re-encrypts the first encrypted format data to obtain its second encrypted format; exports the second encrypted format data from the target data warehouse to a newly created temporary table based on a second target command preset by the Sqoop data transmission component; and merges the second encrypted format data from the newly created temporary table back into the target relational database, thus cleaning the plaintext data and completing the cleaning of the target relational database. This avoids performing calculations in the relational database, performing encryption calculations directly before transmission using the Sqoop tool, rationally utilizing the computing resources of the data warehouse and the Sqoop tool, and accelerating data migration and cleaning efficiency. Furthermore, by using double encryption, the data remains encrypted throughout the data cleaning and transmission process in big data scenarios, thus avoiding the risk of sensitive data leakage. Finally, after cleaning, only the second encrypted data is retained in the relational database, avoiding the risk of plaintext data leakage.

[0143] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, they can also be...

[0144] While hardware implementation is possible, the former is often a better approach. Based on this understanding, the technical solution of this application, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0145] Obviously, the embodiments described above are only some embodiments of this application, not all embodiments. The accompanying drawings show preferred embodiments of this application, but do not limit the patent scope of this application. This application can be implemented in many different forms; rather, the purpose of providing these embodiments is to provide a more thorough and comprehensive understanding of the disclosure of this application. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing specific embodiments, or make equivalent substitutions for some of the technical features. Any equivalent structures made using the content of this application's specification and drawings, directly or indirectly applied to other related technical fields, are similarly within the scope of patent protection of this application.

Claims

1. A Sqoop-based business data washing method, characterized in that, Includes the following steps: Use the Sqoop data synchronization component to read the plaintext data to be cleaned from the target relational business database; The plaintext data to be cleaned is encrypted using the Sqoop data synchronization component to obtain the first encrypted format data of the plaintext data to be cleaned. Based on the first target command preset by the Sqoop data synchronization component, the first encrypted format data of the plaintext data to be cleaned is synchronized to the target data warehouse. The Sqoop data synchronization component is used to re-encrypt the first encrypted data to obtain the second encrypted data of the plaintext data to be cleaned. Based on the second target command preset by the Sqoop data synchronization component, the second encrypted format data of the plaintext data to be cleaned is exported from the target data warehouse to a newly created temporary table; Merging the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, cleaning the plaintext data to be cleaned, and completing the cleaning of the target relational business database, specifically includes: Based on the unique identifier information corresponding to each encrypted format data in the second encrypted format data and the unique identifier information of all plaintext data to be cleaned in the target relational business database, each encrypted format data in the second encrypted format data is merged into the corresponding ciphertext column cell; Based on the preset database monitoring component, determine whether the encrypted data in the second encrypted format data has been completely merged; If the encrypted data in the second encrypted format data has not been completely merged, then continue to wait for the merging operation; If all encrypted data in the second encrypted format data are merged, then all plaintext data to be cleaned in the target relational business database will be updated to null values, thus completing the cleaning of the plaintext data to be cleaned.

2. The Sqoop-based business data washing method according to claim 1, characterized in that, The step of reading the plaintext data to be cleaned from the target relational business database specifically includes: Obtain all representation fields included in the plaintext data to be cleaned, the number of data entries corresponding to each representation field, and the unique identifier information corresponding to each plaintext data; Based on all the representation fields included in the plaintext data to be cleaned, the number of data entries corresponding to each representation field, and the unique identifier information corresponding to each plaintext data, a new temporary table with blank data content is created in the target relational business database.

3. The Sqoop-based business data washing method according to claim 2, characterized in that, The step of encrypting the plaintext data to be cleaned to obtain the first encrypted format data of the plaintext data to be cleaned specifically includes: Retrieve the first encryption function pre-created based on the imported encryption / decryption JAR package; Call the first encryption function, take the plaintext data to be cleaned as the first input parameter of the first encryption function, encrypt the first input parameter, and obtain the first encrypted format data; The step of encrypting the plaintext data to be cleaned to obtain the first encrypted format data of the plaintext data to be cleaned further includes: The unique identifier information corresponding to each plaintext data in the plaintext data to be cleaned is used as the second input parameter of the first encryption function, and the second input parameter is associated one by one with each corresponding encrypted format data in the first encrypted format data.

4. The Sqoop-based business data washing method according to claim 1 or 3, characterized in that, The first target command is the Sqoop import command. The step of synchronizing the first encrypted format data of the plaintext data to be cleaned to the target data warehouse based on the first target command preset by the Sqoop data synchronization component specifically includes: The first encrypted data is synchronized to the target data warehouse according to the Sqoop import command. The Sqoop import command is a command that imports data from a target relational business database into the data warehouse through the Sqoop data synchronization component. The target relational business database includes an Oracle database, and the data warehouse includes a Hive database.

5. The Sqoop-based business data washing method according to claim 3, characterized in that, The step of re-encrypting the first encrypted data to obtain the second encrypted data of the plaintext data to be cleaned specifically includes: Obtain the second encryption function pre-created based on the imported encryption / decryption JAR package; The second encryption function is called, and the first encrypted format data is used as the first input parameter of the second encryption function. The first input parameter is encrypted to obtain the second encrypted format data. The step of re-encrypting the first encrypted data to obtain the second encrypted data of the plaintext data to be cleaned further includes: Based on the one-to-one association between the second input parameter of the first encryption function and each corresponding encrypted format data in the first encrypted format data, the unique identification information corresponding to each encrypted format data in the first encrypted format data is determined. The unique identifier information is used as the second input parameter of the second encryption function, and the second input parameter is associated one by one with each corresponding encrypted format data in the second encryption format data.

6. The Sqoop-based business data washing method according to claim 1, wherein, The second target command is the Sqoop export command. The step of exporting the second encrypted format data of the plaintext data to be cleaned from the target data warehouse to a newly created temporary table based on the second target command preset by the Sqoop data synchronization component specifically includes: The second encrypted data is exported to the newly created temporary table according to the Sqoop export command, wherein the Sqoop export command is a command that exports data in the data warehouse to the target relational business database through the Sqoop data synchronization component, and the newly created temporary table is a specific blank table in the target relational business database.

7. The Sqoop-based business data washing method according to claim 5, characterized in that, Before performing the step of merging the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, and cleaning the plaintext data to be cleaned, the method further includes: Pre-create corresponding ciphertext columns in each target table within the target relational business database based on all the characterization fields included in the plaintext data to be cleaned; Based on a preset function execution monitoring component, determine whether the second encryption function has been executed. If the second encryption function has not finished executing, continue to wait for the second encryption function to perform the encryption operation; If the second encryption function is completed, the one-to-one association between the second input parameter of the second encryption function and each corresponding encrypted format data in the second encryption format data is identified according to the preset execution log. Based on the one-to-one association, the unique identification information corresponding to each encrypted format data in the second encryption format data is determined.

8. A Sqoop-based business data washing device, characterized in that, include: The plaintext reading module is used to read plaintext data to be cleaned from the target relational business database using the Sqoop data synchronization component. The first encryption module is used to encrypt the plaintext data to be cleaned using the Sqoop data synchronization component, and obtain the first encrypted format data of the plaintext data to be cleaned. The data warehouse synchronization module is used to synchronize the first encrypted format data of the plaintext data to be cleaned to the target data warehouse based on the first target command preset by the Sqoop data synchronization component. The second encryption module is used to re-encrypt the first encrypted data using the Sqoop data synchronization component to obtain the second encrypted data of the plaintext data to be cleaned. The data warehouse export module is used to export the second encrypted format data of the plaintext data to be cleaned from the target data warehouse to a newly created temporary table based on the second target command preset by the Sqoop data synchronization component. The merged-in-business-database module is used to merge the second encrypted format data of the plaintext data to be cleaned in the newly created temporary table into the target relational business database, clean the plaintext data to be cleaned, and complete the cleaning of the target relational business database. Specifically, this includes: Based on the unique identifier information corresponding to each encrypted format data in the second encrypted format data and the unique identifier information of all plaintext data to be cleaned in the target relational business database, each encrypted format data in the second encrypted format data is merged into the corresponding ciphertext column cell; Based on the preset database monitoring component, determine whether the encrypted data in the second encrypted format data has been completely merged; If the encrypted data in the second encrypted format data has not been completely merged, then continue to wait for the merging operation; If all encrypted data in the second encrypted format data are merged, then all plaintext data to be cleaned in the target relational business database will be updated to null values, thus completing the cleaning of the plaintext data to be cleaned.

9. A computer device comprising a memory and a processor, wherein the memory stores computer-readable instructions, and the processor, when executing the computer-readable instructions, implements the steps of the Sqoop-based business data cleansing method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer readable instructions, and the computer readable instructions are executed by the processor to implement the steps of the Sqoop-based business data washing method according to any one of claims 1 to 7.

Images