CN112115152A

CN112115152A - Data increment updating and querying method and device, electronic equipment and storage medium

Info

Publication number: CN112115152A
Application number: CN202010970556.9A
Authority: CN
Inventors: 蒋少杰; 戴秀凤; 王德品
Original assignee: China Merchants Finance Technology Co Ltd
Current assignee: China Merchants Finance Technology Co Ltd
Priority date: 2020-09-15
Filing date: 2020-09-15
Publication date: 2020-12-22
Anticipated expiration: 2040-09-15
Also published as: CN112115152B

Abstract

The invention relates to big data technology, and discloses a data increment updating and inquiring method, which comprises the following steps: the method comprises the steps of obtaining an original data set and extracting the original data set to a preset data lake, importing data in the data lake into a preset database, obtaining a preset target table, an increment data table and an update data table, obtaining a source table in the database, extracting data in the source table to the target table, obtaining increment data from the database and extracting the increment data to the increment data table according to a timestamp in the source table and a preset time interval, comparing the increment data table with the target table to obtain update data and extracting the update data to the target table, and associating a view in the database with the target table to obtain an associated view. The invention also provides a data increment updating and inquiring device, electronic equipment and a computer readable storage medium. The invention can solve the problems of slow data query and low accuracy.

Description

Data increment updating and querying method and device, electronic equipment and storage medium

Technical Field

The invention relates to the technical field of big data, in particular to a data increment updating and querying method and device, electronic equipment and a computer readable storage medium.

Background

Under the background of big data, the data scale is sharply enlarged, the data forms are various, and the requirements on data application are increasingly improved, which brings huge challenges to the traditional database technology. The prior art has the following defects: 1. more and more data sources are provided, the integrity of data cannot be guaranteed, the quality of the data is greatly limited, and the accuracy of data query is influenced. 2. Because data update requires time, a simple view query directly associates a view with a database results in slower and slower queries.

Disclosure of Invention

The invention provides a data increment updating and inquiring method, a data increment updating and inquiring device and a computer readable storage medium, and mainly aims to solve the problems of slow data inquiry and low accuracy.

In order to achieve the above object, the present invention provides a data increment updating and querying method, which includes:

acquiring an original data set, and extracting the original data set to a preset data lake;

importing the data in the data lake into a preset database, and acquiring a preset target table, an increment data table and an update data table from the database;

acquiring a source table in the database, extracting data in the source table to the target table, acquiring incremental data from the database according to a timestamp in the source table and a preset time interval, and extracting the incremental data to the incremental data table;

comparing the incremental data table with the target table to obtain updated data, and extracting the updated data to the target table;

and acquiring a view in the database, and associating the view with the target table through a data primary key in the target table to obtain an associated view.

Optionally, the extracting the original data set to a preset data lake includes:

judging the data type in the original data set;

if the data type is an unknown data type, storing the original data set to an unknown data subset in the data lake, and giving an alarm;

and if the type of the data is a known data type, distributing the original data set to a data subset corresponding to the data lake to obtain a known data subset.

Optionally, after the extracting the original data set to a preset data lake, the method further includes:

checking the data in the known data subset by using a preset checking method, and judging whether the data has error data;

and if the error data exists in the data, alarming.

Optionally, the obtaining incremental data from the database according to the timestamp in the source table and a preset time interval, and extracting the incremental data to the incremental data table includes:

updating the timestamp according to the standard time and the preset time interval to obtain an updated timestamp;

and acquiring the incremental data in the update timestamp by using a preset extraction tool, and extracting the incremental data to the incremental target table.

Optionally, the comparing the incremental data table with the target table to obtain updated data includes:

acquiring data in the incremental data table and the target table;

comparing the data, if the data are the same, not updating the data, and if the data are different, updating the data;

and summarizing all updated data to obtain the updated data.

Optionally, the extracting the update data to the target table includes:

acquiring a preset cursor, wherein the cursor comprises a query statement;

executing the query statement in the cursor in the incremental data table and the target table, and extracting the update data to the update table.

Optionally, the obtaining the view in the database, and associating the view with the target table through a data primary key in the target table to obtain an associated view includes:

acquiring a data primary key of the target table;

and associating the data main key with the view by using a pre-constructed query statement to obtain the associated view.

In order to solve the above problem, the present invention further provides a data increment updating and querying device, where the device includes:

the original data extraction module is used for acquiring an original data set and extracting the original data set to a preset data lake;

the original data import module is used for importing the data in the data lake into a preset database, and acquiring a preset target table, an increment data table and an update data table from the database;

the incremental data acquisition module is used for acquiring a source table in the database, extracting data in the source table to the target table, acquiring incremental data from the database according to a timestamp in the source table and a preset time interval, and extracting the incremental data to the incremental data table;

the data updating module is used for comparing the incremental data table with the target table to obtain updated data and extracting the updated data to the target table;

and the view association module is used for acquiring the view in the database, and associating the view with the target table through the data primary key in the target table to obtain an associated view.

In order to solve the above problem, the present invention also provides an electronic device, including:

a memory storing at least one instruction; and

and the processor executes the instructions stored in the memory to realize the data increment updating and querying method.

In order to solve the above problem, the present invention further provides a computer-readable storage medium, where at least one instruction is stored in the computer-readable storage medium, and the at least one instruction is executed by a processor in an electronic device to implement the data increment updating and querying method described above.

The invention classifies the data through the preset data lake, can better identify the data of unknown type and the error data, gives an alarm and improves the accuracy and the quality of data updating. The source table, the incremental data table, the updating data table and the target table are used for updating data, so that the updating of repeated data is reduced, and the occupation of the storage space of a computer is reduced. Meanwhile, the target table is associated with the view by using the data main key to obtain an associated view, so that the data query speed is improved. Therefore, the data increment updating and inquiring method, the data increment updating and inquiring device, the electronic equipment and the computer readable storage medium can solve the problems of slow data inquiry and low accuracy.

Drawings

Fig. 1 is a schematic flow chart illustrating a data increment updating and querying method according to an embodiment of the present invention;

FIG. 2 is a schematic flow chart showing a detailed implementation of one of the steps in FIG. 1;

FIG. 3 is a schematic flow chart showing another step of FIG. 1;

FIG. 4 is a schematic flow chart showing another step of FIG. 1;

FIG. 5 is a schematic flow chart showing another step in FIG. 1;

FIG. 6 is a functional block diagram of an incremental data update and query apparatus according to an embodiment of the present invention;

fig. 7 is a schematic structural diagram of an electronic device for implementing the data increment updating and querying method according to an embodiment of the present invention.

The implementation, functional features and advantages of the objects of the present invention will be further explained with reference to the accompanying drawings.

Detailed Description

It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.

The embodiment of the application provides a data increment updating and querying method. The execution subject of the data increment updating and querying method includes, but is not limited to, at least one of electronic devices such as a server and a terminal, which can be configured to execute the method provided by the embodiment of the present application. In other words, the data increment updating and querying method can be executed by software or hardware installed in the terminal device or the server device, and the software can be a block chain platform. The server includes but is not limited to: a single server, a server cluster, a cloud server or a cloud server cluster, and the like.

Fig. 1 is a schematic flow chart of a data increment updating and querying method according to an embodiment of the present invention. In this embodiment, the data increment updating and querying method includes:

and S1, acquiring an original data set, and extracting the original data set to a preset data lake.

In at least one embodiment of the invention, the data in the raw data set comprises: business data, financial data, product data, or user information data. For example, in the insurance field, the service data may include: number of insuring, amount of insuring, type of insuring, etc., the financial data may include: the fund proportion of the application, the profit of the application, the return rate of the application and the like, and the product data can comprise: the user information data can comprise an identity card number, a user home address and the like.

Preferably, referring to fig. 2, the extracting the original data set to a predetermined data lake includes:

s10, judging the data type in the original data set;

if the data type is an unknown data type, executing S11, storing the original data set to an unknown data subset in the data lake, and performing alarm;

if the type of the data is a known data type, S12 is executed to allocate the original data set to a data subset corresponding to the data lake, so as to obtain a known data subset.

Further, in the embodiment of the present invention, after the extracting the original data set to the preset data lake, the method further includes:

and if the error data exists in the data, alarming.

The data lake is a large warehouse that stores the various raw data of an enterprise, where the data is available for access, processing, analysis, and transmission. The data lake obtains original data sets from a plurality of data sources of an enterprise, and for different purposes, the same original data in the original data sets can also have a plurality of data copies meeting a specific internal model format, so that the processed data in the data lake can be any type of information from structured data to completely unstructured data, the data lake has corresponding partial space for storing data of known data types, and the data lake integrates and stores data of unknown data types in corresponding parts and gives an alarm.

In the embodiment of the present invention, the preset verification method takes the verification of a 17-bit identity card number as an example: 1. multiplying and adding the 17-bit number of the identity card by different coefficients respectively, (for example, the coefficients from the first to the seventeenth bits are 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2 respectively); 2. dividing the result of the addition by 11 to obtain a remainder (the remainder only may be 11 digits of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, and the last digit of the identification card corresponding to the 11 digits is 1, 0, X, 9, 8, 7, 6, 5, 4, 3, 2); 3. and judging whether the ID card number is legal or not by judging whether the remainder corresponds to the ID card number. Wherein illegal data is alerted via a log file. For example, the id number is 34052419800101001X, 3 × 7+4 × 9+0 × 10+ 5+ … +1 × 2 is calculated as 189, 189 is divided by 11 to obtain the result 17 and 2, and the number corresponding to 2 is X according to the remainder correspondence, and it is determined as legal data.

The data lake is used for classifying the original data set, and meanwhile, the preset verification method is used for verifying the known type of data, so that the unknown type of data and the error data can be well identified, and the accuracy of data updating is improved.

And S2, importing the data in the data lake into a preset database, and acquiring a preset target table, an increment data table and an update data table from the database.

Preferably, the database is a data set which is stored together in a certain way, can be shared by a plurality of users, has the smallest redundancy, and is independent from the application program, and can be regarded as an electronic file cabinet, namely a place for storing electronic files, and the users can add, inquire, update, delete and the like to the data in the files. In the embodiment of the present invention, the database includes, but is not limited to, a Sql Server, an oracle, and other databases. Further, in the embodiment of the present invention, when creating the target table, the incremental data table, and the update data table, the table types of the target table, the incremental data table, and the update data table may be set as dynamic tables. The dynamic table is a table type in which data can be dynamically extracted along with time and data change, wherein fields in the dynamic table are of indefinite length. In contrast, the fields in the conventional static table are fixed length, and the fields cannot be added by themselves along with the change of time and data, so that the overflow of data can be caused when the changed data exceeds the fixed length of the static table.

In the embodiment of the invention, the target table is used for storing the finally updated data, the incremental data table is used for storing the incremental data, the update data table is used for storing the update data, and meanwhile, the target table, the incremental data table and the update data table are set to be dynamic tables, so that the update data can be better compatible.

S3, acquiring a source table in the database, extracting data in the source table to the target table, acquiring incremental data from the database according to a timestamp in the source table and a preset time interval, and extracting the incremental data to the incremental data table.

Preferably, referring to fig. 3, the acquiring incremental data from the database according to the timestamp in the source table and the preset time interval, and extracting the incremental data to the incremental data table includes:

s30, updating the timestamp according to the standard time and the preset time interval to obtain an updated timestamp;

and S31, acquiring the incremental data in the update timestamp by using a preset extraction tool, and extracting the incremental data to the incremental target table.

Specifically, the embodiment of the present invention may use a database (e.g., a Sql Server database) whose timestamp supports automatic update, and when data of the source table changes, a value of a timestamp field may be automatically updated to record a time of the change, and by automatically updating the timestamp field, automatic update of the database may be implemented. The source table refers to a preset table for storing the data lake import data. The extraction tool may use an ETL (extract-transform-load) tool, such as a Datastage, Informatica, key, etc. tool.

According to the embodiment of the invention, the incremental data can be acquired and extracted in time through the timestamp and the extraction tool, so that the data updating rate is improved.

And S4, comparing the incremental data table with the target table to obtain updated data, and extracting the updated data to the target table.

Preferably, referring to fig. 4, the comparing the incremental data table with the target table to obtain updated data includes:

s40, acquiring data in the incremental data table and the target table;

s41, comparing the data, if the data are the same, not updating the data, and if the data are different, updating the data;

and S42, summarizing all updated data to obtain the updated data.

Specifically, the extracting the update data to the target table includes:

acquiring a preset cursor, wherein the cursor comprises a query statement;

Wherein the cursor is a method of processing data, the cursor providing the ability to browse data forward or backward one or more rows at a time in the updated original table and the target table. Treating the cursor as a pointer can specify any location in the updated original table and the target table, and then allow the user to process the data at the specified location. In the embodiment of the invention, the query statement can be Structured Query Language (SQL) which is disclosed at present, the SQL is the most extensive language used in data processing, a user is allowed to concisely and briefly declare required business logic, and the SQL belongs to a set language, only needs to clearly express the requirement and does not need to know the specific method; SQL can be optimized, various query optimizers are built in, and the various query optimizers can translate an optimal execution plan for SQL.

According to the embodiment of the invention, the incremental target table is compared with the source table to obtain the updated data, so that the updated data and the data in the source table are ensured not to be repeated, and the occupation of the storage space of a computer is reduced.

S5, obtaining the view in the database, and associating the view with the target table through the data primary key in the target table to obtain an associated view.

Preferably, the view is a view in a database, and is a virtual table, the content of which is defined by a query, and like a real table, the view includes a series of columns and rows with names. However, the views do not exist in the database as sets of stored data values, with row and column data used to reference tables from queries that define the views, and are dynamically generated when the views are referenced. The primary key of data refers to a column or combination of columns in the target table whose value uniquely identifies each row in the target table by which physical integrity of the target table is enforced. The data primary key is mainly used for being associated with other tables.

In detail, as described with reference to fig. 5, the S5 includes:

s50, acquiring a data primary key of the target table;

and S51, associating the data main key with the view by using a pre-constructed query statement to obtain the associated view.

Furthermore, the invention uses the data main key to associate the target table with the view to obtain an associated view, and users can only inquire and modify the data which can be seen by the users through the associated view, thereby ensuring the safety of data inquiry.

The data classification is carried out through the preset data lake, the unknown type data and the error data can be well identified, the alarm is given, and the accuracy and the quality of data updating are improved; the source table, the incremental data table, the updating data table and the target table are used for updating data, so that the updating of repeated data is reduced, and the occupation of the storage space of a computer is reduced; meanwhile, the target table is associated with the view by using the data main key to obtain an associated view, so that the data query speed is improved. Therefore, the embodiment of the invention can solve the problems of slow data query and low accuracy.

Fig. 6 is a functional block diagram of a data increment updating and querying apparatus according to an embodiment of the present invention.

The data increment updating and querying device 100 of the present invention can be installed in an electronic device. According to the implemented functions, the data increment updating and querying device 100 may include an original data extraction module 101, an original data import module 102, an increment data acquisition module 103, a data updating module 104, and a view association module 105. The module of the present invention, which may also be referred to as a unit, refers to a series of computer program segments that can be executed by a processor of an electronic device and that can perform a fixed function, and that are stored in a memory of the electronic device.

In the present embodiment, the functions regarding the respective modules/units are as follows:

the original data extraction module 101 is configured to obtain an original data set, and extract the original data set to a preset data lake.

Preferably, the original data extraction module 101 extracts the original data set to a preset data lake by:

judging the data type in the original data set;

Further, the raw data extraction module 101 further includes:

and if the error data exists in the data, alarming.

The original data import module 102 is configured to import data in the data lake into a preset database, and obtain a preset target table, an incremental data table, and an update data table in the database.

The incremental data obtaining module 103 is configured to obtain a source table in the database, extract data in the source table to the target table, obtain incremental data from the database according to a timestamp in the source table and a preset time interval, and extract the incremental data to the incremental data table.

In detail, the incremental data obtaining module 103 obtains incremental data from the database according to the timestamp and the preset time interval in the source table, and extracts the incremental data to the incremental data table by:

The data updating module 104 is configured to compare the incremental data table with the target table to obtain updated data, and extract the updated data to the target table.

Preferably, the data updating module 104 obtains the updated data by:

acquiring data in the incremental data table and the target table;

and summarizing all updated data to obtain the updated data.

Specifically, the data update module 104 extracts the update data to the target table by:

acquiring a preset cursor, wherein the cursor comprises a query statement;

The view association module 105 is configured to obtain a view in the database, and associate the view with the target table through a data primary key in the target table to obtain an associated view.

In detail, the view association module 105 obtains the associated view by:

acquiring a data primary key of the target table;

Fig. 7 is a schematic structural diagram of an electronic device for implementing a data increment updating and querying method according to an embodiment of the present invention.

The electronic device 1 may include a processor 10, a memory 11 and a bus, and may further include a computer program, such as a data increment update and query program 12, stored in the memory 11 and executable on the processor 10.

The memory 11 includes at least one type of readable storage medium, which includes flash memory, removable hard disk, multimedia card, card-type memory (e.g., SD or DX memory, etc.), magnetic memory, magnetic disk, optical disk, etc. The memory 11 may in some embodiments be an internal storage unit of the electronic device 1, such as a removable hard disk of the electronic device 1. The memory 11 may also be an external storage device of the electronic device 1 in other embodiments, such as a plug-in mobile hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), and the like, which are provided on the electronic device 1. Further, the memory 11 may also include both an internal storage unit and an external storage device of the electronic device 1. The memory 11 may be used to store not only the application software installed in the electronic device 1 and various types of data, such as codes of the data increment updating and querying program 12, but also temporarily store data that has been output or will be output.

The processor 10 may be composed of an integrated circuit in some embodiments, for example, a single packaged integrated circuit, or may be composed of a plurality of integrated circuits packaged with the same or different functions, including one or more Central Processing Units (CPUs), microprocessors, digital Processing chips, graphics processors, and combinations of various control chips. The processor 10 is a Control Unit (Control Unit) of the electronic device, connects various components of the electronic device by using various interfaces and lines, and executes various functions and processes data of the electronic device 1 by running or executing programs or modules (such as data increment updating and querying programs) stored in the memory 11 and calling data stored in the memory 11.

The bus may be a Peripheral Component Interconnect (PCI) bus, an Extended Industry Standard Architecture (EISA) bus, or the like. The bus may be divided into an address bus, a data bus, a control bus, etc. The bus is arranged to enable connection communication between the memory 11 and at least one processor 10 or the like.

Fig. 7 only shows an electronic device with components, and it will be understood by a person skilled in the art that the structure shown in fig. 7 does not constitute a limitation of the electronic device 1, and may comprise fewer or more components than shown, or a combination of certain components, or a different arrangement of components.

For example, although not shown, the electronic device 1 may further include a power supply (such as a battery) for supplying power to each component, and preferably, the power supply may be logically connected to the at least one processor 10 through a power management device, so as to implement functions of charge management, discharge management, power consumption management, and the like through the power management device. The power supply may also include any component of one or more dc or ac power sources, recharging devices, power failure detection circuitry, power converters or inverters, power status indicators, and the like. The electronic device 1 may further include various sensors, a bluetooth module, a Wi-Fi module, and the like, which are not described herein again.

Further, the electronic device 1 may further include a network interface, and optionally, the network interface may include a wired interface and/or a wireless interface (such as a WI-FI interface, a bluetooth interface, etc.), which are generally used for establishing a communication connection between the electronic device 1 and other electronic devices.

Optionally, the electronic device 1 may further comprise a user interface, which may be a Display (Display), an input unit (such as a Keyboard), and optionally a standard wired interface, a wireless interface. Alternatively, in some embodiments, the display may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, an OLED (Organic Light-Emitting Diode) touch device, or the like. The display, which may also be referred to as a display screen or display unit, is suitable for displaying information processed in the electronic device 1 and for displaying a visualized user interface, among other things.

It is to be understood that the described embodiments are for purposes of illustration only and that the scope of the appended claims is not limited to such structures.

The incremental data update and query program 12 stored in the memory 11 of the electronic device 1 is a combination of instructions, and when running in the processor 10, can implement:

Specifically, the specific implementation method of the processor 10 for the instruction may refer to the description of the relevant steps in the embodiments corresponding to fig. 1 to fig. 5, which is not repeated herein.

Further, the integrated modules/units of the electronic device 1, if implemented in the form of software functional units and sold or used as separate products, may be stored in a non-volatile computer-readable storage medium. The computer-readable medium may include: any entity or device capable of carrying said computer program code, recording medium, U-disk, removable hard disk, magnetic disk, optical disk, computer Memory, Read-Only Memory (ROM).

In the embodiments provided in the present invention, it should be understood that the disclosed apparatus, device and method can be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the division of the modules is only one logical functional division, and other divisions may be realized in practice.

The modules described as separate parts may or may not be physically separate, and parts displayed as modules may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the present embodiment.

In addition, functional modules in the embodiments of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, or in a form of hardware plus a software functional module.

It will be evident to those skilled in the art that the invention is not limited to the details of the foregoing illustrative embodiments, and that the present invention may be embodied in other specific forms without departing from the spirit or essential attributes thereof.

The present embodiments are therefore to be considered in all respects as illustrative and not restrictive, the scope of the invention being indicated by the appended claims rather than by the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein. Any reference signs in the claims shall not be construed as limiting the claim concerned.

The block chain is a novel application mode of computer technologies such as distributed data storage, point-to-point transmission, a consensus mechanism, an encryption algorithm and the like. A block chain (Blockchain), which is essentially a decentralized database, is a series of data blocks associated by using a cryptographic method, and each data block contains information of a batch of network transactions, so as to verify the validity (anti-counterfeiting) of the information and generate a next block. The blockchain may include a blockchain underlying platform, a platform product service layer, an application service layer, and the like.

Furthermore, it is obvious that the word "comprising" does not exclude other elements or steps, and the singular does not exclude the plural. A plurality of units or means recited in the system claims may also be implemented by one unit or means in software or hardware. The terms second, etc. are used to denote names, but not any particular order.

Finally, it should be noted that the above embodiments are only for illustrating the technical solutions of the present invention and not for limiting, and although the present invention is described in detail with reference to the preferred embodiments, it should be understood by those skilled in the art that modifications or equivalent substitutions may be made on the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention.

Claims

1. A data increment updating and querying method is characterized by comprising the following steps:

2. The method for incremental data update and query according to claim 1, wherein the extracting the original data set to a preset data lake comprises:

judging the data type in the original data set;

3. The method for incremental data update and query according to claim 2, wherein after extracting the original data set to a preset data lake, the method further comprises:

and if the error data exists in the data, alarming.

4. The method for incrementally updating and querying data as claimed in claim 1, wherein said obtaining incremental data from said database according to a timestamp in said source table and a predetermined time interval, and extracting said incremental data into said incremental data table comprises:

5. The method for data incremental updating and querying of claim 1, wherein the comparing the incremental data table with the target table to obtain updated data comprises:

acquiring data in the incremental data table and the target table;

and summarizing all updated data to obtain the updated data.

6. The method for incremental updating and querying of data as claimed in claim 1, wherein said extracting said updated data to said target table comprises:

acquiring a preset cursor, wherein the cursor comprises a query statement;

7. The method for incremental updating and querying of data according to claim 1, wherein the obtaining a view in the database, associating the view with the target table through a primary key of data in the target table, and obtaining an associated view comprises:

acquiring a data primary key of the target table;

8. An apparatus for incremental updating and querying of data, the apparatus comprising:

9. An electronic device, characterized in that the electronic device comprises:

at least one processor; and the number of the first and second groups,

a memory communicatively coupled to the at least one processor; wherein the content of the first and second substances,

the memory stores instructions executable by the at least one processor to enable the at least one processor to perform a method of incremental data update and query as claimed in any one of claims 1 to 7.

10. A computer-readable storage medium, in which a computer program is stored, which, when being executed by a processor, implements a method for incremental updating and querying of data according to any one of claims 1 to 7.