CN112115208A - Database data importing method - Google Patents
Database data importing method Download PDFInfo
- Publication number
- CN112115208A CN112115208A CN202010921628.0A CN202010921628A CN112115208A CN 112115208 A CN112115208 A CN 112115208A CN 202010921628 A CN202010921628 A CN 202010921628A CN 112115208 A CN112115208 A CN 112115208A
- Authority
- CN
- China
- Prior art keywords
- data
- import
- database
- batch
- file
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 55
- 230000008676 import Effects 0.000 claims abstract description 90
- 238000013500 data storage Methods 0.000 claims abstract description 8
- 101100340248 Arabidopsis thaliana SCRM gene Proteins 0.000 claims description 6
- 238000007726 management method Methods 0.000 description 8
- 238000005516 engineering process Methods 0.000 description 6
- 238000013439 planning Methods 0.000 description 3
- 238000004458 analytical method Methods 0.000 description 2
- 230000000052 comparative effect Effects 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 230000003247 decreasing effect Effects 0.000 description 1
- 230000003111 delayed effect Effects 0.000 description 1
- 230000010365 information processing Effects 0.000 description 1
- 230000010354 integration Effects 0.000 description 1
- 238000013068 supply chain management Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/221—Column-oriented storage; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/252—Integrating or interfacing systems involving database management systems between a Database Management System and a front-end application
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a database data importing method, which comprises the following steps that step S1, a data file comprising mobile phone number data is uploaded, the first row of a data file row is read as header information, and the header information and the data storage row are matched with each other according to the data storage row provided in a database; step S2, a batch import data request is provided for a plurality of data obtained after matching fields, the batch import data request is assembled into an SQL statement for merging, and the data corresponding to the batch import data request for merging is imported; step S3, executing the import process, and determining whether the database data already has the mobile phone number included in the batch import data request, if so, updating the data corresponding to the database, and if not, generating and adding the corresponding data included in the batch import data request by the database.
Description
Technical Field
The invention relates to the technical field of data processing, in particular to a database data importing method.
Background
With the increasing market competition, the client information of each enterprise becomes more important. With the wide application of information technology in enterprises, enterprises generally accumulate a large amount of customer information data, and how to update the data in time so as to make correct decisions is of great significance to the enterprises.
Data warehousing is a technology developed to support enterprise decision-making, and aims to integrate all data in an enterprise into one large warehouse, so as to facilitate query and analysis of users, generate reports and the like. The data in the data warehouse come from different business databases and are organized according to the subjects. Because the data warehouse can reasonably organize a large amount of historical data, the database system can more conveniently utilize the data and analyze the data, a model is established on the basis of analysis, and results are given to structured and semi-structured decision problems, so that the data warehouse technology is becoming one of key technologies for enterprise information integration and decision-making assistance application.
And data of the large-scale data warehouse is imported into a database system, a large number of users upload data concurrently, and the data is persisted to a storage database system and a metadata database system of the data warehouse in a batch processing mode. Generally, from generation to import of customer information data to a data warehouse, the characteristics of concentration, real-time performance and the like exist, and a large amount of data can be imported into the same metadata object in a short time. In the data submission stage, the metadata update is delayed and limited, so that the data import concurrency and success rate are affected. It can be seen that the batch processing operation of the data import of the existing data warehouse into the database system has the problems of large time-delay, high concurrency support depending on the metadata database system, and the like.
Moreover, at present, the speed of the client import process is slow in the process of importing a large amount of client information into the database in the market; the import file format is single, and only CSV format import is generally supported.
Disclosure of Invention
The invention aims to provide a database data importing method to solve the problems in the prior art.
In order to achieve the purpose, the invention provides the following technical scheme:
a method for importing data into a database comprises the following steps,
the method comprises the following steps:
step S1, uploading a data file including mobile phone number data, reading the first row of the data file row as header information, and matching the header information with the data storage row according to the data storage row provided in the database;
step S2, a batch import data request is provided for a plurality of data obtained after matching fields, the batch import data request is assembled into an SQL statement for merging, and the data corresponding to the batch import data request for merging is imported;
step S3, executing the import process, and determining whether the database data already has the mobile phone number included in the import data request, if so, updating the data corresponding to the database, and if not, generating and adding the corresponding data included in the batch import data request by the database.
Further, when the batch import data request is subjected to the import processing, whether the mobile phone number included in the batch import data exists in the database or not is judged, if yes, batch updating operation is performed on data corresponding to the database, and if not, batch generation data and adding operation are performed on the database.
Preferably, for the data of the read data file, the line number of the batch reading of the data file at each time is set, the total page number is calculated,
and circularly reading the data of the corresponding page number from the first page of the data file, wherein the number of the cycles is increased once, and the corresponding page number is increased by one page until the number of the cycles is equal to the total page number.
Further, the data file is read circularly according to the number of lines of the data file read in batch each time, and the position of the number of lines of finishing reading circularly of the batch processing file is recorded and used as the position of the number of lines of starting reading the data file in batch processing of the next circularly reading.
Preferably, the data of the data import request can be imported in a foreground or a background,
if the foreground is selected for import, the imported progress bar is displayed on the page,
if background import is selected, the import process is performed in a silent mode by the background server, and the import result can be checked in the import history.
Preferably, the format of the profile file includes at least CSV, XLSX, and CSV.
Preferably, the databases include, but are not limited to, a CRM database, a SCRM database, and an ERP database.
The invention has the following beneficial effects:
according to the database data importing method, the data files are read in batch processing, the data information is processed in batch, the adding and updating operations between the program and the database are reduced by 99% compared with the prior art, the influence on the use performance of the database caused by frequent database adding and updating operations is avoided, and the importing speed of the importing method is improved by 80 times compared with that of the existing importing method.
According to the database data importing method, the data files are read in batch, the data information is processed in batch, and the adding and updating operations between the program and the database are reduced by 99% compared with the previous adding and updating operations; frequent database addition and update operations of the existing import method can affect the service performance of the database and the import speed of an import function of the existing import method: import speed = computer hardware performance/(number of file rows x number of columns x number of existing data volumes of client tables in the database). The import speed of the import method of the invention is improved by 80 times (under the same condition) compared with the import speed of the existing import method, the import efficiency of the client is obviously improved, the format of the import file can be diversified, and the format does not need to be converted. Therefore, the limitation of the prior art on the import file format is solved, and the current situation that the prior art cannot import the file rapidly is overcome. The database data importing method of the invention can obviously improve the importing speed and enrich the importing file format, and the importing file recorded with the client information data can be rapidly imported into the database by applying the database data importing method of the invention.
Drawings
FIG. 1 is a schematic diagram of a database data importing method according to the present invention;
fig. 2 is a schematic diagram of a prior art structure and a flow chart.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
The special words of the invention include:
a database: the database is a data set which is stored together in a certain mode, can be shared by a plurality of users, has the redundancy as small as possible, is independent from an application program, can be regarded as an electronic file cabinet, namely a place for storing electronic files, and can be used for adding, inquiring, updating, deleting and the like of data in the files.
CRM database: CRM is an abbreviation of Customer Relationship Management, and is an advanced tool for managing Customer profiles, sales leads, sales activities, business reports, and statistics of sales performance.
SCRM database: the SCRM is an abbreviation of Social Customer Relationship Management, the SCRM database builds a complete Social marketing closed loop, and draws in transaction data to a Social platform to realize an online and offline community marketing integrated Management system, and provides a one-stop community marketing solution scheme of' vermicelli connection, marketing Management and repurchase conversion
An ERP database: the ERP system is a short term for Enterprise Resource Planning (Enterprise Resource Planning), and is a management platform which is established on the basis of information technology, integrates the information technology and advanced management ideas, and provides decision means for Enterprise employees and decision layers by using a systematized management idea. The system is a new generation of integrated management information system developed from MRP (material demand planning), expands the functions of MRP and has the core idea of supply chain management. The method breaks out of the traditional enterprise boundary, optimizes the resources of the enterprise from the supply chain range, optimizes the operation mode of the modern enterprise, and reflects the requirement of the market on the reasonable allocation of the resources of the enterprise. The method has obvious effects on improving the business process of enterprises and improving the core competitiveness of the enterprises.
Customer import: and storing the contents of the files of the client information, such as csv, txt and the like, in a database.
Matching field: refers to columns in the import file that correspond to columns stored in the database.
Referring to fig. 1, in an embodiment of the present invention, a database data importing method,
the method comprises the following steps:
step S1, uploading a data file including mobile phone number data, reading the first row of the data file row as header information, and matching the header information with the data storage row according to the data storage row provided in the database;
step S2, a batch import data request is provided for a plurality of data obtained after matching fields, the batch import data request is assembled into an SQL statement for merging, and the data corresponding to the batch import data request for merging is imported;
step S3, executing the import process, and determining whether the database data already has the mobile phone number included in the import data request, if so, updating the data corresponding to the database, and if not, generating and adding the corresponding data included in the batch import data request by the database.
The invention discloses a database data importing method, which comprises the following steps:
the reading method comprises the steps of setting the number of rows of the data file to be read in batch every time for the read data of the data file, calculating the total number of pages, circularly reading the data of the corresponding number of pages from the first page of the data file, increasing the number of cycles once, and increasing the number of pages by one page until the number of cycles is equal to the total number of pages. And reading the data file from the first page according to the number of pages for circulation, and adding one page for each circulation until the number of times of the circulation is equal to the total number of pages.
In each specific circulation process, circularly reading the data file according to the number of lines of batch reading of the data file, recording the position of the number of circularly reading ending lines of the batch processing file as the position of the number of starting lines of reading the data file of the next batch processing, aiming at reading the data file according to the number of lines set by batch reading of the data file to obtain corresponding client information, and recording the position of the number of lines of the batch processed file at each time as the position of the number of starting lines of reading the data file of the next batch processing.
Taking CRM data as an example, when the batch import data request is subjected to import processing, judging whether a mobile phone number included in the batch import data exists in the database or not, if so, executing batch updating operation on the CRM data corresponding to the database, if not, executing batch generation of the CRM data and adding operation on the CRM data by the database, and aiming at judging whether customer information obtained by batch processing exists in the database or not according to mobile phone number query, wherein the customer information is queried from the database only once, the customers with the database are subjected to batch updating to the database operation, if not, the batch adding operation is carried out, and the database updating operation and the database adding operation are carried out at most once in each batch processing. If there are 1000 valid clients in the file and the number of batch processing is set to 1000, then the total number of pages is one page, i.e. the processing of data can be completed by performing one for-loop, and then the adding and updating operations to the database are only twice at most.
Importing data of the data importing request, wherein foreground import or background import can be selected, if foreground import is selected, the imported progress bar is displayed on a page, if background import is selected, the importing process is performed in a silent mode by a background server, and an importing result can be checked in an importing history; the method is characterized in that two importing modes, namely foreground importing and background importing, can be selected after matching fields, and the imported progress bar can be displayed on a page after foreground importing is selected; when the background import is selected, the import process is performed in a silent mode by the background server, and the import result can be checked in the import history. When processing the client information processing data of the imported file, judging whether the mobile phone number of the client exists in the current enterprise according to the mobile phone number of the client, if so, updating other fields of the client, and if not, adding the information of the client into the database system.
The format supported by the uploaded file of the CRM database data import method of the invention is as follows: three formats of CSV, XLSX and CSV.
The databases of the present invention include, but are not limited to, CRM databases, SCRM databases, and ERP databases.
Referring to fig. 2, in a comparative example of the present invention,
the existing import method comprises the following implementation processes: reading the first line of the data file as a matching line, taking other lines as data lines, setting a batch processing size value, and through while circulation, decreasing the batch processing size value according to 1 until the batch processing size value is 0 or the batch processing size value is not exited from the while circulation until the batch processing size value is 0 or the end of the file; wherein one batch size processes one line of data, each line of data representing one customer; when processing data, whether the mobile phone number of the client in the file is already stored in the database system is verified, if not, the client information is added into the database system, and if so, the client is updated.
The format supported by the uploaded file of the existing import method is as follows: the CSV format.
Import speed of import function affecting existing import method:
import speed = computer hardware performance/(number of file rows x number of columns x number of existing data volumes of client tables in the database).
Each loop of the comparative example only processes information for one customer, and if there are 1000 customers with the correct phone number in the file, then,
add database operation + update database operation =1000,
i.e. 1000 database addition or update operations are required.
The import method mainly adopts batch processing to read the data files, batch processing of data information is carried out, and the adding and updating operations between the program and the database are reduced by 99 percent compared with the prior art; frequent database addition and update operations can affect database performance. The introduction speed of the CRM database data introduction method is improved by 80 times compared with that of the conventional introduction method under the same condition.
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 sign in a claim should not be construed as limiting the claim concerned.
Claims (7)
1. A database data importing method is characterized in that,
the method comprises the following steps:
step S1, uploading a data file including mobile phone number data, reading the first row of the data file row as header information, and matching the header information with the data storage row according to the data storage row provided in the database;
step S2, a batch import data request is provided for a plurality of data obtained after matching fields, the batch import data request is assembled into an SQL statement for merging, and the data corresponding to the batch import data request for merging is imported;
step S3, executing the import process, and determining whether the database data already has the mobile phone number included in the batch import data request, if so, updating the data corresponding to the database, and if not, generating and adding the corresponding data included in the batch import data request by the database.
2. The database data import method according to claim 1,
and when the batch import data request is subjected to import processing, judging whether the mobile phone numbers included in the batch import data exist in the database, if so, executing batch updating operation on the corresponding data of the database, and if not, executing batch generation data and adding operation on the database.
3. The database data import method according to claim 1,
for the data of the read data file, setting the line number of the batch reading of the data file at each time, calculating the total page number,
and circularly reading the data of the corresponding page number from the first page of the data file, wherein the number of the cycles is increased once, and the corresponding page number is increased by one page until the number of the cycles is equal to the total page number.
4. The database data import method according to claim 3,
and circularly reading the data file according to the line number of the data file read in batch each time, and recording the position of the circularly reading ending line number of the batch processing file as the position of the starting line number of the data file read in batch in the next circular reading.
5. The database data import method according to claim 1,
importing the data of the import data request, selecting foreground import or background import,
if the foreground is selected for import, the imported progress bar is displayed on the page,
if background import is selected, the import process is performed in a silent mode by the background server, and the import result can be checked in the import history.
6. The database data import method according to claim 1, wherein the format of the profile file includes at least CSV, XLSX, and CSV.
7. The database data import method according to claim 1, wherein the database includes but is not limited to CRM database, SCRM database, and ERP database.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010921628.0A CN112115208A (en) | 2020-09-04 | 2020-09-04 | Database data importing method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010921628.0A CN112115208A (en) | 2020-09-04 | 2020-09-04 | Database data importing method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112115208A true CN112115208A (en) | 2020-12-22 |
Family
ID=73803250
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010921628.0A Pending CN112115208A (en) | 2020-09-04 | 2020-09-04 | Database data importing method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112115208A (en) |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104317974A (en) * | 2014-11-21 | 2015-01-28 | 武汉理工大学 | Reconfigurable multi-source data importing method in ERP system |
| CN105808375A (en) * | 2014-12-31 | 2016-07-27 | 航天信息软件技术有限公司 | Data import method and device |
-
2020
- 2020-09-04 CN CN202010921628.0A patent/CN112115208A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104317974A (en) * | 2014-11-21 | 2015-01-28 | 武汉理工大学 | Reconfigurable multi-source data importing method in ERP system |
| CN105808375A (en) * | 2014-12-31 | 2016-07-27 | 航天信息软件技术有限公司 | Data import method and device |
Non-Patent Citations (4)
| Title |
|---|
| 伍华聪: "客户关系管理***中对客户及相关数据的导入导出分析处理", HTTPS://WWW.CNBLOGS.COM/WUHUACONG/P/3936963.HTML, pages 1 - 9 * |
| 张文凤: "中小微型企业销售自动化***", 中国优秀硕士学位论文全文数据库信息科技辑, pages 14 - 24 * |
| 张文凤: "中小微型企业销售自动化***", 信息科技辑, pages 14 - 24 * |
| 李华荣等: "MySQL程序员面试笔试宝典", 机械工业出版社, pages: 188 * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107908672B (en) | Application report realization method, device and storage medium based on Hadoop platform | |
| US10235430B2 (en) | Systems, methods, and apparatuses for detecting activity patterns | |
| US10216782B2 (en) | Processing of updates in a database system using different scenarios | |
| US20120005153A1 (en) | Creation of a data store | |
| CN105630934A (en) | Data statistic method and system | |
| CN114461644A (en) | Data acquisition method and device, electronic equipment and storage medium | |
| CN114416703A (en) | Method, device, equipment and medium for automatically monitoring data integrity | |
| US11726975B2 (en) | Auto unload | |
| CN111310032A (en) | Resource recommendation method and device, computer equipment and readable storage medium | |
| CN111046081B (en) | Industrial time sequence data access method and system | |
| US10055469B2 (en) | Method and software for retrieving information from big data systems and analyzing the retrieved data | |
| US10922328B2 (en) | Method and system for implementing an on-demand data warehouse | |
| WO2023231716A1 (en) | Data processing methods and apparatus, electronic device and storage medium | |
| CN114936242B (en) | Data report customization application system | |
| CN111415138A (en) | Creative processing method and system, client and server | |
| US20200272998A1 (en) | User Interfaces for Filtering Electronic Calendar Data Sets | |
| CN112115208A (en) | Database data importing method | |
| CN116010380A (en) | Data warehouse automatic management method based on visual modeling | |
| CN114090686A (en) | Account-out acceleration method and device | |
| CN114722045A (en) | Time series data storage method and device | |
| JP2017010376A (en) | Mart-less verification support system and mart-less verification support method | |
| CN116501375B (en) | Data dictionary version management method, device, computer equipment and storage medium | |
| US11934396B2 (en) | Data reconciliation for big data environments | |
| CN113987372B (en) | Hot data acquisition method, device and equipment of domain business object model | |
| Wang et al. | Research and implementation of an analytical database testing platform in telecommunication industry |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |