CN112732828A - Cross-platform data sharing method based on data warehouse tool - Google Patents

Cross-platform data sharing method based on data warehouse tool Download PDF

Info

Publication number
CN112732828A
CN112732828A CN202011531600.2A CN202011531600A CN112732828A CN 112732828 A CN112732828 A CN 112732828A CN 202011531600 A CN202011531600 A CN 202011531600A CN 112732828 A CN112732828 A CN 112732828A
Authority
CN
China
Prior art keywords
script
data
database
source
platform
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
Application number
CN202011531600.2A
Other languages
Chinese (zh)
Inventor
赵杰岩
索凤桐
冯畅
张莹
刘燃
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Aisino Corp
Original Assignee
Aisino Corp
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Aisino Corp filed Critical Aisino Corp
Priority to CN202011531600.2A priority Critical patent/CN112732828A/en
Publication of CN112732828A publication Critical patent/CN112732828A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/21Design, administration or maintenance of databases
    • G06F16/215Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/23Updating
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/25Integrating or interfacing systems involving database management systems
    • G06F16/254Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/25Integrating or interfacing systems involving database management systems
    • G06F16/258Data format conversion from or to a database
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/27Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

The invention discloses a cross-platform data sharing method based on a data warehouse tool, which comprises the following steps: configuring a conversion data source, and establishing connection between a source database to be converted and a target database, wherein the source database is a database of a tax management system platform, and the target database is a database of a financial sharing service platform; establishing a table input script for extracting table information from a source library; creating a java script for extracting and processing data in a source database; establishing an insertion updating script, configuring a table field and loading target table information, and loading the extracted and processed data into a table corresponding to a target database; building an SQL script and compiling a deletion statement; sequentially connecting the SQL script, the table input script, the java script and the insertion updating script to complete the establishment of the conversion script file; and establishing a job script file for running conversion, and setting job time. And the real-time sharing of data among different platforms is realized.

Description

Cross-platform data sharing method based on data warehouse tool
Technical Field
The invention relates to the technical field of databases, in particular to a cross-platform data sharing method based on a data warehouse tool.
Background
With the proposal of sharing concept, tax sharing also comes, and in order to realize tax sharing, different subsystems need to be integrated, so as to realize tax sharing. However, the situation that databases among subsystems are not matched can be encountered in the integration process, the codes need to be changed if the databases are changed can be processed in a java code mode, and the flexibility is not high.
At present, some enterprises apply independent tax management system platforms, but docking and fusion are not achieved with a financial sharing service platform, and data among the systems cannot be shared, so that some data are respectively input in two or more systems, repetitive low-efficiency labor is increased, and tax management cost is increased. Meanwhile, due to the fact that the system is large in size and has various functions, the data needing to be synchronized are complex and various. Common interface (such as webservice data synchronization interface) synchronous data occupy too much system resources, and system response time is too long, so that the conventional financial sharing service platform is reasonably utilized and optimized, informatization repeated investment is reduced, multi-platform data sharing is realized, and management of integrating tax and finance is a necessary choice for transformation of the conventional financial sharing service center.
Disclosure of Invention
The invention aims to provide a cross-platform data sharing method based on a data warehouse tool, which realizes data sharing between a tax management system platform and a financial sharing service platform, does not occupy the running resources of each system, and greatly reduces the running pressure of each system.
In order to achieve the above object, the present invention provides a cross-platform data sharing method based on a data warehouse tool, comprising:
configuring a conversion data source, and establishing a connection between a source database and a target database which need to be converted, wherein the source database is a database of a tax management system platform, and the target database is a database of a financial sharing service platform;
establishing a table input script for extracting table information from the source library;
creating a java script for extracting and processing the data in the source database;
establishing an insertion updating script, configuring a table field and loading target table information, and loading the extracted and processed data into a table corresponding to the target database;
establishing an SQL script, and compiling a deletion statement for realizing the synchronous deletion function of the source database and the target database;
sequentially connecting the SQL script, the table input script, the java script and the insertion updating script to complete the establishment of a conversion script file;
and establishing an operation script file for operating the conversion, and setting operation time.
Optionally, the configuration conversion data source includes:
respectively creating a source database data source and a target database data source to establish connection between the source database and the target database.
Optionally, the creating a table input script includes:
and newly building a table input script, and writing an sql statement for extracting table information from the source database in the newly built table input script.
Optionally, the creating an insert update script includes:
newly creating an insertion updating script, configuring a table field and loading target table information so as to load the extracted and processed data into a table corresponding to the target database.
Optionally, the java script is specifically configured to:
and performing non-null processing on partial fields in the extracted data through java codes.
Optionally, the insert update script is specifically configured to:
and corresponding the table fields in the source database and the target database, setting a primary key for comparison, and judging whether each piece of data is newly added or deleted according to the primary key.
Optionally, the creating a job script file and setting a job time includes:
and newly building an operation script file, sequentially placing all the established conversions, and setting the timing scheduling time of the operation script file.
Optionally, after the job script file is created, the method further includes:
and connecting a resource library, and creating role information and role permission information according to requirements, wherein the resource library is used for storing the created conversion and the job script file.
Optionally, the data warehouse tool is a key tool, and the key tool includes a conversion script, a job script file script, and a plurality of resource libraries.
Optionally, before establishing the job script file for running the conversion, the method further includes:
and running the established conversion script file to test whether the conversion script file can be run correctly.
The invention has the beneficial effects that:
a data warehouse-based tool by: configuring a conversion data source to establish connection between a source database and a target database which need to be converted, establishing a table input script to extract table information from the source database, establishing a java script to extract and process data in the source database, establishing an insertion update script configuration table field and loading target table information, loading the extracted and processed data into a table corresponding to the target database, establishing an SQL script and compiling a deletion statement for realizing the synchronous deletion function of the source database and the target database, then sequentially connecting the SQL script, the table input script, the java script and the insertion update script to complete the establishment of a conversion script file, finally establishing an operation script file for operating the conversion, and setting operation time, thereby realizing the cross-platform sharing of data between a tax management system platform and a financial sharing service platform, the data sharing unified management is realized among different platforms of a plurality of systems, only the data source and the target which need to be shared need to be determined, the sharing can be realized quickly, a large number of codes are not needed to be written, the operation resources of each system are not occupied, the operation pressure of each system is greatly reduced, the real-time synchronous sharing is realized, and the risk caused by data change and untimely synchronization is reduced.
Further, based on the resource library, the system operator can create new authority information such as roles and users in real time in the authority centralized management system or enter new data information according to the actual operation situation.
The apparatus of the present invention has other features and advantages which will be apparent from or are set forth in detail in the accompanying drawings and the following detailed description, which are incorporated herein, and which together serve to explain certain principles of the invention.
Drawings
The above and other objects, features and advantages of the present invention will become more apparent by describing in more detail exemplary embodiments thereof with reference to the attached drawings, in which like reference numerals generally represent like parts.
FIG. 1 illustrates a step diagram of a cross-platform data sharing method based on a data warehouse tool according to the present invention.
FIG. 2 is a configuration interface diagram of a configuration data source for inserting an update script in a data warehouse tool based cross-platform data sharing method according to an embodiment of the present invention.
FIG. 3 illustrates a configuration interface diagram for inserting update scripts in a data warehouse tool based cross-platform data sharing method according to an embodiment of the present invention.
FIG. 4 is a diagram illustrating a conversion sequence in a job script file interface in a cross-platform data sharing method based on a data warehouse tool according to an embodiment of the present invention.
Detailed Description
The invention will be described in more detail below with reference to the accompanying drawings. While the preferred embodiments of the present invention are shown in the drawings, it should be understood that the present invention may be embodied in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the invention to those skilled in the art.
FIG. 1 illustrates a step diagram of a cross-platform data sharing method based on a data warehouse tool according to the present invention.
As shown in fig. 1, a cross-platform data sharing method based on a data warehouse tool includes:
step S101: configuring a conversion data source, and establishing connection between a source database to be converted and a target database, wherein the source database is a database of a tax management system platform, and the target database is a database of a financial sharing service platform;
in a specific application scenario, the method of the present invention is applied to a system of a sharing service mode between a tax administration system and a financial service platform, and in this step, referring to fig. 2, a data warehouse tool is used to configure a conversion file data source, and a source database data source and a target database data source are respectively created to establish a connection between a source database and a target database.
The data warehouse tool is a button tool, and the button tool comprises a conversion script, an operation script file script and a plurality of resource libraries.
Specifically, the data warehouse technology (ETL: Extract-Transform-Load, i.e., a process of data extraction, transformation, and loading) is a process of data extraction (Extract), Cleaning (Cleaning), transformation (Transform), and loading (Load). The method is an important ring for constructing a data warehouse, and a user extracts required data from a data source, and finally loads the data into the data warehouse according to a predefined data warehouse model after data cleaning. Data transfer (Data transfer) is the process of changing Data from one representation to another, and when Data is transferred from one system to another, it is necessary to convert the Data to a format that the system can recognize.
While a button is an existing ETL tool, the ETL tool is frequently used in a data warehouse project, the button can also be applied in the following scenarios: integrating data among different applications or databases, exporting data in the databases to text files, loading mass data into the databases, cleaning data, and integrating application-related projects for use. The keytle is very simple to use, realizes what business is done through graphical interface design, and does not need to write codes to realize, so the keytle is designed in a way of orienting metadata. The keylet supports a wide variety of input and output formats, including text files, data tables, and database engines. In addition, the powerful conversion function of the button is very convenient for manipulating data. There are two script files in the button, transformation (transformation) and job (job), the transformation completes the basic transformation for data, and the job completes the control of the whole workflow.
Step S102: establishing a table input script for extracting table information from a source database and establishing a java script for extracting and processing data in the source database;
in the specific application scenario, a new table is created to input a script, an sql statement is written in the script, and a java script is created to process the extracted data.
The background processing comprises the following steps: table information is extracted through sql statements, the extracted data is processed through java codes, partial fields are mainly subjected to non-null processing, and the influence of null fields on the system is eliminated.
Step S103: establishing an insertion updating script, configuring a table field and loading target table information, and loading the extracted and processed data into a table corresponding to a target database;
in the above specific application scenario, referring to fig. 3, an update script is newly inserted, a table field is configured, and target table information is loaded, so that the extracted and processed data is loaded into a table corresponding to a target database.
The method comprises the following steps: and corresponding the table fields of different platforms, setting a main key for comparison, and judging whether the data is newly added and updated or deleted according to the main key.
Step S104: establishing an SQL script, compiling a deletion statement and realizing the synchronous deletion function of a source database and a target database; sequentially connecting the SQL script, the table input script, the java script and the insertion updating script to complete the establishment of the conversion script file;
in the specific application scenario, the SQL script is newly built, and the deletion statement is compiled. And all scripts are connected according to SQL, table input, java and insertion update sequence. And then running the established conversion script file to test whether the conversion script file can be correctly run.
Step S105: and establishing a job script file for running conversion, and setting job time.
In the above specific application scenario, referring to fig. 4, a job is newly created, all the transformations are sequentially placed, for example, multiple transformations, namely transformation 1, transformation 2, transformation 3, and transformation 4, are sequentially placed on a job script file interface, then a job timing scheduling time is set, for example, an interval time is set to 5 minutes, that is, data extraction, transformation, and loading are performed every 5 minutes, and then the configured job is executed.
In this embodiment, after the job script file is created, the method further includes:
step S106: and connecting a resource library, creating role information and role permission information according to requirements, wherein the resource library is used for storing created conversion and job script files.
In the above specific application scenario, the resource library of the button can store the conversion, the job, and the like, to form a collaboration platform. The conversion tasks are grouped and managed in a folder form in the resource library, and a user can define the name of the folder by self. Specifically, a user selects a button to support Database hierarchy, then a Database is configured for connection, after configuration is completed and connection is selected, a user name and a password are input according to requirements, and the user name and the password can be connected to a specified resource library.
In summary, the present invention realizes data cross-platform sharing through the ETL technology, realizes unified management of data sharing among different platforms of a plurality of systems, and can quickly realize sharing without writing a large amount of codes by only determining the source and the target of data to be shared. A system operator can create new authority information such as roles and users in real time in the authority centralized management system or input new data information according to actual operation conditions, and then the data is synchronously shared among different databases under different systems by an ETL (extract transform load) synchronization method.
Having described embodiments of the present invention, the foregoing description is intended to be exemplary, not exhaustive, and not limited to the embodiments disclosed. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments.

Claims (10)

1. A cross-platform data sharing method based on a data warehouse tool is characterized by comprising the following steps:
configuring a conversion data source, and establishing a connection between a source database and a target database which need to be converted, wherein the source database is a database of a tax management system platform, and the target database is a database of a financial sharing service platform;
establishing a table input script for extracting table information from the source library;
creating a java script for extracting and processing the data in the source database;
establishing an insertion updating script, configuring a table field and loading target table information, and loading the extracted and processed data into a table corresponding to the target database;
establishing an SQL script, and compiling a deletion statement for realizing the synchronous deletion function of the source database and the target database;
sequentially connecting the SQL script, the table input script, the java script and the insertion updating script to complete the establishment of a conversion script file;
and establishing an operation script file for operating the conversion, and setting operation time.
2. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein configuring the transformed data source comprises:
respectively creating a source database data source and a target database data source to establish connection between the source database and the target database.
3. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein the building table input script comprises:
and newly building a table input script, and writing an sql statement for extracting table information from the source database in the newly built table input script.
4. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein the establishing an insert update script comprises:
newly creating an insertion updating script, configuring a table field and loading target table information so as to load the extracted and processed data into a table corresponding to the target database.
5. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein the java script is specifically configured to:
and performing non-null processing on partial fields in the extracted data through java codes.
6. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein the insert update script is specifically configured to:
and corresponding the table fields in the source database and the target database, setting a primary key for comparison, and judging whether each piece of data is newly added or deleted according to the primary key.
7. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein the step of creating a job script file and setting a job time comprises:
and newly building an operation script file, sequentially placing all the established conversions, and setting the timing scheduling time of the operation script file.
8. The data warehouse tool-based cross-platform data sharing method of claim 1, further comprising, after the job script file is created:
and connecting a resource library, and creating role information and role permission information according to requirements, wherein the resource library is used for storing the created conversion and the job script file.
9. The data warehouse tool-based cross-platform data sharing method of claim 1, wherein the data warehouse tool is a button tool, and the button tool comprises a conversion script, a job script file script and a plurality of resource libraries.
10. The data warehouse tool-based cross-platform data sharing method of claim 1, further comprising, prior to establishing the job script file that runs the transformation:
and running the established conversion script file to test whether the conversion script file can be run correctly.
CN202011531600.2A 2020-12-22 2020-12-22 Cross-platform data sharing method based on data warehouse tool Pending CN112732828A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202011531600.2A CN112732828A (en) 2020-12-22 2020-12-22 Cross-platform data sharing method based on data warehouse tool

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202011531600.2A CN112732828A (en) 2020-12-22 2020-12-22 Cross-platform data sharing method based on data warehouse tool

Publications (1)

Publication Number Publication Date
CN112732828A true CN112732828A (en) 2021-04-30

Family

ID=75605782

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202011531600.2A Pending CN112732828A (en) 2020-12-22 2020-12-22 Cross-platform data sharing method based on data warehouse tool

Country Status (1)

Country Link
CN (1) CN112732828A (en)

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN113806448A (en) * 2021-09-28 2021-12-17 中电万维信息技术有限责任公司 Configuration method for automatic data synchronization
CN114880394A (en) * 2022-04-25 2022-08-09 浪潮卓数大数据产业发展有限公司 SQL script sharing method, equipment and medium
CN114880394B (en) * 2022-04-25 2024-05-10 浪潮卓数大数据产业发展有限公司 SQL script sharing method, device and medium

Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9734222B1 (en) * 2004-04-06 2017-08-15 Jpmorgan Chase Bank, N.A. Methods and systems for using script files to obtain, format and transport data
WO2018120681A1 (en) * 2016-12-30 2018-07-05 平安科技(深圳)有限公司 Data synchronization method, device and system, data processing server, and storage medium
CN108629002A (en) * 2018-05-03 2018-10-09 山东汇贸电子口岸有限公司 A kind of big data comparison method and device based on kettle
CN108846076A (en) * 2018-06-08 2018-11-20 山大地纬软件股份有限公司 The massive multi-source ETL process method and system of supporting interface adaptation
CN109739851A (en) * 2019-01-21 2019-05-10 广东创能科技股份有限公司 Floating population's big data multi-source acquisition method and system
WO2019223181A1 (en) * 2018-05-21 2019-11-28 平安科技(深圳)有限公司 Etl task data source switching method and system, computer device and storage medium

Patent Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9734222B1 (en) * 2004-04-06 2017-08-15 Jpmorgan Chase Bank, N.A. Methods and systems for using script files to obtain, format and transport data
WO2018120681A1 (en) * 2016-12-30 2018-07-05 平安科技(深圳)有限公司 Data synchronization method, device and system, data processing server, and storage medium
CN108629002A (en) * 2018-05-03 2018-10-09 山东汇贸电子口岸有限公司 A kind of big data comparison method and device based on kettle
WO2019223181A1 (en) * 2018-05-21 2019-11-28 平安科技(深圳)有限公司 Etl task data source switching method and system, computer device and storage medium
CN108846076A (en) * 2018-06-08 2018-11-20 山大地纬软件股份有限公司 The massive multi-source ETL process method and system of supporting interface adaptation
CN109739851A (en) * 2019-01-21 2019-05-10 广东创能科技股份有限公司 Floating population's big data multi-source acquisition method and system

Non-Patent Citations (2)

* Cited by examiner, † Cited by third party
Title
GAVIN: "kettle入门实战", pages 1 - 27, Retrieved from the Internet <URL:https://zhuanlan.zhihu.com/p/137383200> *
叶涵: "kettle入门之五 kettle应用场景(增量插入和更新)", pages 1 - 7, Retrieved from the Internet <URL:https://blog.csdn.net/qq_43015592/article/details/107470146> *

Cited By (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN113806448A (en) * 2021-09-28 2021-12-17 中电万维信息技术有限责任公司 Configuration method for automatic data synchronization
CN113806448B (en) * 2021-09-28 2023-11-21 中电万维信息技术有限责任公司 Configuration method for automatic synchronization of data
CN114880394A (en) * 2022-04-25 2022-08-09 浪潮卓数大数据产业发展有限公司 SQL script sharing method, equipment and medium
CN114880394B (en) * 2022-04-25 2024-05-10 浪潮卓数大数据产业发展有限公司 SQL script sharing method, device and medium

Similar Documents

Publication Publication Date Title
CN109992595A (en) Different database conversion method, apparatus, equipment and storage medium
CN107368503B (en) Data synchronization method and system based on button
US7200668B2 (en) Document conversion with merging
US20100211539A1 (en) System and method for building a data warehouse
CN102087594B (en) Management method and device for extension point and plugins
CN107391635A (en) Data synchronous system and method
CN111324610A (en) Data synchronization method and device
CN107612962B (en) Distributed simulation evaluation test management system
CN111143470A (en) Cross-platform database data synchronization comparison method and device
US20170024447A1 (en) System, method, and apparatus for collaborative editing of common or related computer based software output
CN114741375A (en) Rapid and automatic data migration system and method for multi-source heterogeneous database
CN112732828A (en) Cross-platform data sharing method based on data warehouse tool
CN115292307A (en) Data synchronization system, method and corresponding computer equipment and storage medium
CN110941629A (en) Metadata processing method, device, equipment and computer readable storage medium
CN114048188A (en) Cross-database data migration system and method
US11016756B1 (en) Application repository protocol for disparate entity applications
CN107168822B (en) Oracle streams exception recovery system and method
CN116069859A (en) Incremental data synchronization method of database, storage medium and computer equipment
CN114443294B (en) Big data service component deployment method, system, terminal and storage medium
CN115495465A (en) Data updating method and device and electronic equipment
CN109614442A (en) Data synchronous data listing maintenance, device, storage medium and electronic equipment
CN112612783A (en) Method for realizing cross-platform data sharing
CZ2017124A3 (en) The method of transferring structures and data files between the source and target system and a system for its implementation
US10147061B2 (en) Integrating a construction jobsite system with external tools
JP2006309697A (en) Computer system, program for computer system, and program generating application program based on spreadsheet program among the programs

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