An ETL templating warehousing method, system, device and storage medium

Abstract

The application provides an ETL templating warehousing method, system, device and storage medium, resource directory and resource directory data items are configured; different theme sub-jobs are established based on the resource directory and resource directory data items; different theme total job templates are configured based on the different theme sub-jobs; the to-be-warehoused data is acquired, and filtering processing is performed on the to-be-warehoused data to obtain filtered to-be-warehoused data; theme classification is performed based on the filtered to-be-warehoused data, and the corresponding theme total job template is selected to clean and warehouse the filtered to-be-warehoused data. By establishing different theme total job templates, the data analysis and warehousing coupling degree is reduced, the problems that the basic data items will have increments over time, manual configuration is prone to problems, and the efficiency is low, and all tables are in a total job, and only the specified theme table is actually warehoused, and there are too many redundant judgment processes are solved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer application, and in particular to an ETL templating warehousing method, system, device and storage medium. BACKGROUND

[0002] Since the official launch of the reform of the public supervision industry, due to the wide data sources, it is difficult to form an evidence chain by offline manual sorting of structured, unstructured and special query data. According to the public service industry, the mobile communication equipment, storage medium, automobile equipment, electronic industry, communication industry and other themes are adjusted to be the standard data items of data warehousing. According to the case handling process, the data in the warehouse is encrypted, packaged according to different data themes, and the data extracted by the coordination investigation is extracted to the logically isolated case database to avoid irrelevant personnel from accessing the core data. The data warehousing business system obtains the encrypted theme data packet, decrypts the data packet, obtains the table and data volume that need to be warehoused according to the xml index file in the data packet, and uses ETL to extract the original data to the specified database (multi-data source) after cleaning. Usually, by maintaining the theme X of the resource directory item, maintaining the mapping relationship of the ELASTICSEARCH index key field of the basic data item field, configuring the cleaning rules, based on the rules, using the code to generate the file set -> preposition X, preposition X -> buffer X, buffer X -> application library X (MPP), buffer -> case X (MPP), buffer X -> ELASTICSEARCH index table, buffer X -> HBASE cleaning conversion process, and different theme X warehousing sub-job, based on the sub-job of X theme, a set of X theme total job template is configured, and the data warehousing only needs to parse the warehousing packet to the specified directory, parse the theme of the data packet, call ETLAPI to set the variable to be passed, and finally call the corresponding theme total job to perform warehousing processing.

[0003] However, the basic data items of the above method will increase over time, and manual configuration is prone to problems and low efficiency. At the same time, all tables are in a total job, and only the specified theme table is actually warehoused, which exists too many redundant judgment processes.

[0004] Therefore, the present application provides an ETL templating warehousing method, system, device and storage medium for solving the above problems. SUMMARY

[0005] Therefore, it is necessary to provide an ETL templating warehousing method, system, device and storage medium for solving the above problems.

[0006] The present application provides an ETL templating warehousing method, comprising:

[0007] configuring a resource directory and a resource directory data item;

[0008] establishing different subject sub-jobs based on the resource directory and the resource directory data item;

[0009] configuring different subject total job templates based on the different subject sub-jobs;

[0010] obtaining to-be-warehoused data, and performing filtering processing on the to-be-warehoused data to obtain filtered to-be-warehoused data;

[0011] performing subject classification based on the filtered to-be-warehoused data, and selecting a corresponding subject total job template to perform cleaning and warehousing on the filtered to-be-warehoused data.

[0012] In some possible implementation manners, the establishing of the different subject sub-jobs based on the resource directory and the resource directory data item comprises:

[0013] dividing subjects based on the resource directory and the resource directory data item;

[0014] merging cleaning process sub-jobs of resource directory data items of different subjects into different subject sub-jobs.

[0015] In some possible implementation manners, the cleaning process sub-job of the resource directory data item comprises:

[0016] generating a table creation script template of the resource directory data item based on the resource directory and the resource directory data item;

[0017] generating a data source and a target table based on the resource directory data item;

[0018] adding a data cleaning script based on the resource directory data item;

[0019] adding a data cleaning script to a corresponding preposition and buffer of the resource directory data item based on the data cleaning script added based on the resource directory data item;

[0020] generating a sql query source based on an es mapping relationship of the resource directory data item.

[0021] In some possible implementation manners, the selecting of the corresponding subject total job template to perform cleaning and warehousing on the filtered to-be-warehoused data comprises:

[0022] according to a subject classified by the to-be-warehoused data, calling a corresponding subject total job template through an ETL API, and performing parameter configuration on the called subject total job template based on to-be-warehoused data belonging to different subjects, to obtain a target subject total job template;

[0023] The filtered data to be stored is cleaned and stored based on the target topic master task template.

[0024] In some possible implementations, the filtering of the filtered data to be imported into the database to obtain the processed data to be imported into the database includes:

[0025] Based on preset parameters, it is determined whether the data to be entered into the warehouse meets the entry conditions. Data that does not meet the entry conditions is removed to obtain the filtered data to be entered into the warehouse.

[0026] In some possible implementations, configuring parameters on the invoked topic master assignment template to obtain the target topic master assignment template includes:

[0027] The parameters in the main task template for the called topic are configured based on the data to be imported into the database;

[0028] Add reconciliation sub-tasks, inventory error record sub-tasks, and reconciliation push sub-tasks to the main task template.

[0029] In some possible implementations, the step of selecting the corresponding topic master job template to clean and store the filtered data to be stored includes:

[0030] When cleaning and storing the filtered data based on the selected topic master task template, multiple selected topic master task templates are used simultaneously to clean and store the data.

[0031] On the other hand, the present invention also provides an ETL templated import system, comprising:

[0032] The resource directory and data item configuration module configures the resource directory and resource directory data items.

[0033] The topic sub-job creation module creates sub-jobs for different topics based on the resource directory and resource directory data items;

[0034] The module for creating master assignments for a specific theme configures master assignment templates for different themes based on the sub-assignments of those themes.

[0035] The data to be entered into the warehouse module acquires the data to be entered into the warehouse and filters the data to be entered into the warehouse to obtain filtered data to be entered into the warehouse.

[0036] The data entry module performs topic classification based on the filtered data to be entered into the database, and selects the corresponding topic master job template to clean and enter the filtered data into the database. On the other hand, the present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the alarm traffic classification and management method described above.

[0037] On the other hand, the present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the above-described ETL templated data import method.

[0038] The beneficial effects of the above embodiments are as follows: The ETL templated data import method, system, device, and storage medium provided by the present invention configure resource catalogs and resource catalog data items; establish sub-jobs for different themes based on the resource catalogs and resource catalog data items; configure general job templates for different themes based on the sub-jobs for different themes; obtain data to be imported and filter the data to be imported to obtain filtered data to be imported; classify the data to be imported by theme based on the filtered data to be imported, and select the corresponding general job template for the theme to clean and import the filtered data to be imported. By establishing general job templates for different themes, the coupling between data parsing and import is reduced, solving the problems that basic data items increase over time, manual configuration is prone to problems and is inefficient, and all tables are in one general job, but only tables of the specified theme are actually imported, resulting in too many redundant judgment processes. Attached Figure Description

[0039] Figure 1 A flowchart illustrating an embodiment of an ETL templated database import method provided by the present invention;

[0040] Figure 2 This is a structural diagram of an embodiment of an ETL templated database import system provided by the present invention;

[0041] Figure 3 A schematic diagram of the structure of an embodiment of the electronic device provided by the present invention. Detailed Implementation

[0042] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0043] It should be understood that the illustrative drawings are not drawn to scale. The flowcharts used in this invention illustrate operations implemented according to some embodiments of the invention. It should be understood that the operations in the flowcharts may not be implemented in sequence, and steps without logical contextual relationships may be reversed or performed simultaneously. Furthermore, those skilled in the art, guided by the content of this invention, may add one or more other operations to the flowcharts, or remove one or more operations from the flowcharts.

[0044] Some of the block diagrams shown in the accompanying drawings are functional entities and do not necessarily correspond to physically or logically independent entities. These functional entities can be implemented in software, in one or more hardware modules or integrated circuits, or in different network and / or processor systems and / or micro-load systems.

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

[0046] This invention provides an ETL template-based database import method. Please refer to [link / reference]. Figure 1 , Figure 1 This invention provides an ETL templated import method, comprising:

[0047] S101. Configure the resource directory and the data items in the resource directory;

[0048] S102. Based on the resource catalog and resource catalog data items, create sub-jobs with different themes;

[0049] S103. Configure different master assignment templates for different themes based on the sub-assignments of the different themes;

[0050] S104. Obtain the data to be entered into the database, and filter the data to be entered into the database to obtain the filtered data to be entered into the database.

[0051] S105. Based on the filtered data to be entered into the database, perform topic classification, select the corresponding topic master job template, and clean and enter the filtered data into the database.

[0052] Compared with existing technologies, this invention provides an ETL templated data import method, system, device, and storage medium. This involves configuring a resource catalog and resource catalog data items; establishing sub-jobs for different themes based on the resource catalog and resource catalog data items; configuring different overall job templates for different themes based on the sub-jobs; acquiring data to be imported and filtering it to obtain filtered data; classifying the filtered data by theme; and selecting the corresponding overall job template to clean and import the filtered data. By establishing overall job templates for different themes, the coupling between data parsing and import is reduced. This solves the problems of incremental basic data items over time, which are prone to errors and inefficiency when manually configuring them, and the excessive redundant judgment processes inherent in having all tables in a single overall job when only tables for the specified theme are actually imported.

[0053] In a preferred embodiment of the present invention, the step of establishing sub-jobs with different themes based on the resource catalog and resource catalog data items includes:

[0054] Themes are divided based on the resource catalog and resource catalog data;

[0055] The cleansing processes for resource catalog data items on different themes are merged into sub-jobs on different themes.

[0056] In a preferred embodiment of the present invention, the sub-job of the cleansing process for the resource catalog data items includes:

[0057] A table creation script template for the resource directory data items is generated based on the resource directory and the resource directory data items;

[0058] Generate a data source and a destination table based on the resource catalog data items;

[0059] Data cleaning script added based on the resource catalog data items;

[0060] Based on the data cleaning script added to the resource directory data item, add data cleaning scripts to the front and buffer corresponding to the resource directory data item;

[0061] SQL query source is generated based on the mapping relationship between the resource directory data items and Elasticsearch.

[0062] In a specific embodiment, the cleansing process sub-job of the resource catalog data items is specifically to further maintain the basic data items of the resource catalog based on the resource catalog, namely the table fields, types, precision, scale, Elasticsearch mapping, cleansing rules to be added to the fields, etc. After maintaining the above information, the template generation component in this ETL tool can be used to automatically generate table creation statements and MPP import YML templates for different data sources (front-end, buffer, MPP, HBase) for each table. Based on the table creation statements in production, the operation and maintenance personnel manually complete the table space planning, execute the table creation statements, and put the import YML template into the specified position. Using the ETL process generation component, the data sources used by different ETL databases, cleansing rules, field mapping relationships, destination tables, reconciliation SQL statistics, OS command calls, etc., and the cleansing process sub-jobs such as process variable judgment, quality threshold variable judgment, and cleansing process variable judgment can be automatically added to each table according to the theme.

[0063] In a preferred embodiment of the present invention, the step of selecting the corresponding topic master task template to clean and store the filtered data to be stored includes:

[0064] Based on the subject categories of the data to be imported, the corresponding subject master job template is called through ETLAPAI, and the parameters of the called subject master job template are configured based on the data to be imported to different subjects to obtain the target subject master job template.

[0065] The filtered data to be stored is cleaned and stored based on the target topic master task template.

[0066] In a preferred embodiment of the present invention, the step of filtering the filtered data to be entered into the database to obtain the processed data to be entered into the database includes:

[0067] Based on preset parameters, it is determined whether the data to be entered into the warehouse meets the entry conditions. Data that does not meet the entry conditions is removed to obtain the filtered data to be entered into the warehouse.

[0068] In a preferred embodiment of the present invention, configuring parameters of the invoked topic master assignment template to obtain the target topic master assignment template includes:

[0069] The parameters in the main task template for the called topic are configured based on the data to be imported into the database;

[0070] Add reconciliation sub-tasks, inventory error record sub-tasks, and reconciliation push sub-tasks to the main task template.

[0071] In a preferred embodiment of the present invention, the step of selecting the corresponding topic master task template to clean and store the filtered data to be stored includes:

[0072] When cleaning and storing the filtered data based on the selected topic master task template, multiple selected topic master task templates are used simultaneously to clean and store the data.

[0073] In a specific implementation, by adding the maximum number of simultaneous tasks for the total topic assignment template and the maximum number of simultaneous conversion runs, it is ensured that the database (DM.MPP) for the user's cleaning and conversion process does not occupy too many connections, and that the same data can be traced back through a unique identifier throughout the entire database. This can greatly improve the efficiency of data sorting during the case handling process and ensure that the entire lifecycle of the data is traceable.

[0074] To better implement the ETL templated database import method in this embodiment of the invention, based on the ETL templated database import method, this embodiment of the invention also provides an ETL templated database import system, such as... Figure 2 A structural diagram of an embodiment of an ETL templated import system 200 provided by the present invention includes:

[0075] Resource catalog and data item configuration module 201, configures resource catalog and resource catalog data items;

[0076] The topic sub-job creation module 202 creates sub-jobs for different topics based on the resource directory and resource directory data items;

[0077] The topic-based overall task creation module 203 configures different topic-based overall task templates based on the sub-tasks of the different topics;

[0078] The data to be entered into the warehouse module 204 acquires the data to be entered into the warehouse and filters the data to be entered into the warehouse to obtain filtered data to be entered into the warehouse.

[0079] The data entry module 205 performs subject classification based on the filtered data to be entered into the database, and selects the corresponding subject general task template to clean and enter the filtered data into the database.

[0080] This invention also provides an electronic device, combined with Figure 3 Let's take a look. Figure 3 This is a schematic diagram of an embodiment of the electronic device provided by the present invention. The electronic device 300 includes a processor 301, a memory 302, and a computer program stored in the memory 302 and executable on the processor 301. When the processor 301 executes the program, it implements an ETL templated library insertion method as described above.

[0081] In a preferred embodiment, the electronic device 300 further includes a display 303 for displaying the processor 301 executing an ETL templated data import method as described above.

[0082] For example, a computer program may be divided into one or more modules / units, one or more of which are stored in memory 302 and executed by processor 301 to complete the present invention. One or more modules / units may be a series of computer program instruction segments capable of performing a specific function, which describe the execution process of the computer program in electronic device 300.

[0083] Electronic device 300 can be a desktop computer, laptop, PDA, or smartphone with an adjustable camera module.

[0084] The processor 301 may be an integrated circuit chip with signal processing capabilities. The processor 301 can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this invention. The general-purpose processor can be a microprocessor or any conventional processor.

[0085] The memory 302 may be, but is not limited to, Random Access Memory (RAM), Read Only Memory (ROM), Programmable Read-Only Memory (PROM), Erasable Programmable Read-Only Memory (EPROM), Electrically Erasable Programmable Read-Only Memory (EEPROM), etc. The memory 302 stores programs, and the processor 301 executes these programs upon receiving execution instructions. The process definition method disclosed in any of the foregoing embodiments of this invention can be applied to the processor 301, or implemented by the processor 301.

[0086] The display 303 can be an LCD screen or an LED screen. For example, the display screen on a mobile phone.

[0087] Understandable Figure 3 The structure shown is only a schematic diagram of one possible structure of electronic device 300. Electronic device 300 may also include more than one of the following: Figure 3 Show more or fewer components. Figure 3 The components shown can be implemented using hardware, software, or a combination thereof.

[0088] On the other hand, the present invention also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the above-described method for classifying and managing alarm traffic. Generally, the computer instructions for implementing the method of the present invention can be carried by any combination of one or more computer-readable storage media. Non-transitory computer-readable storage media can include any computer-readable medium except for transient, propagating signals themselves.

[0089] Computer-readable storage media can be, for example—but not limited to—electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatuses, or any combination thereof. More specific examples (a non-exhaustive list) of computer-readable storage media include: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this document, a computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in connection with an instruction execution system, apparatus, or device.

[0090] Computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof. Programming languages ​​include object-oriented programming languages—such as Java, Smalltalk, and C++—as well as conventional procedural programming languages—such as the "C" language or similar programming languages. In particular, Python, suitable for neural network computation, and platform frameworks based on TensorFlow, PyTorch, etc., can be used. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0091] Those skilled in the art will understand that all or part of the processes of the methods described in the above embodiments can be implemented by a computer program instructing related hardware (such as a processor, controller, etc.), and the computer program can be stored in a computer-readable storage medium. The computer-readable storage medium may be a disk, optical disk, read-only memory, or random access memory, etc.

[0092] Compared with existing technologies, this invention provides an ETL templated data import method, system, device, and storage medium. This involves configuring a resource catalog and resource catalog data items; establishing sub-jobs for different themes based on the resource catalog and resource catalog data items; configuring different overall job templates for different themes based on the sub-jobs; acquiring data to be imported and filtering it to obtain filtered data; classifying the filtered data by theme; and selecting the corresponding overall job template to clean and import the filtered data. By establishing overall job templates for different themes, the coupling between data parsing and import is reduced. This solves the problems of incremental basic data items over time, which are prone to errors and inefficiency when manually configuring them, and the excessive redundant judgment processes inherent in having all tables in a single overall job when only tables for the specified theme are actually imported.

[0093] The above provides a detailed description of the ETL templated database insertion method, system electronic equipment, and storage medium proposed in this invention. Specific examples have been used to illustrate the principles and implementation methods of this invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, those skilled in the art will recognize that there will be changes in the specific implementation methods and application scope based on the ideas of this invention. The above descriptions are only preferred embodiments of this invention, but the protection scope of this invention is not limited thereto. Any changes or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in this invention should be included within the protection scope of this invention.

Claims

1. An ETL template-based data import method, characterized in that, include: Configure resource directories and resource directory data items; Based on the resource catalog and the resource catalog data items, sub-jobs with different themes are established, including: dividing themes based on the resource catalog and resource catalog data; merging the cleaning process sub-jobs of resource catalog data items of different themes into sub-jobs of different themes; Configure different theme-based assignment templates for sub-assignments based on the different themes; Obtain the data to be entered into the database, and filter the data to be entered into the database to obtain the filtered data to be entered into the database. Based on the filtered data to be entered into the database, the data is classified by topic, and the corresponding topic master task template is selected to clean and enter the filtered data into the database. The cleaning process sub-jobs for the resource catalog data items include: A table creation script template for the resource directory data items is generated based on the resource directory and the resource directory data items; Generate a data source and a destination table based on the resource catalog data items; Add a data cleaning script based on the data items in the resource directory; Based on the data cleaning script added to the resource directory data item, add data cleaning scripts to the front and buffer corresponding to the resource directory data item; SQL query source is generated based on the mapping relationship between the resource directory data items and Elasticsearch.

2. The ETL template-based data import method according to claim 1, characterized in that, The step of selecting the corresponding topic master task template to clean and input the filtered data into the database includes: Based on the subject categories of the data to be imported, the corresponding subject master job template is called through ETLAPAI, and the parameters of the called subject master job template are configured based on the data to be imported to different subjects to obtain the target subject master job template. The filtered data to be stored is cleaned and stored based on the target topic master task template.

3. The ETL template-based data import method according to claim 1, characterized in that, The process of filtering the filtered data to obtain the processed data to be entered into the database includes: Based on preset parameters, it is determined whether the data to be entered into the warehouse meets the entry conditions. Data that does not meet the entry conditions is removed to obtain the filtered data to be entered into the warehouse.

4. The ETL template-based data import method according to claim 2, characterized in that, The step of configuring parameters for the called topic master assignment template to obtain the target topic master assignment template includes: The parameters in the main task template for the called topic are configured based on the data to be imported into the database; Add reconciliation sub-tasks, inventory error record sub-tasks, and reconciliation push sub-tasks to the main task template.

5. The ETL template-based data import method according to claim 1, characterized in that, The step of selecting the corresponding topic master task template to clean and input the filtered data into the database includes: When cleaning and storing the filtered data based on the selected topic master task template, multiple selected topic master task templates are used simultaneously to clean and store the data.

6. An ETL template-based data import system, characterized in that, include: The resource directory and data item configuration module configures the resource directory and resource directory data items. The topic sub-job creation module creates sub-jobs for different topics based on the resource catalog and resource catalog data items, including: dividing topics based on the resource catalog and resource catalog data; and merging the cleaning process sub-jobs of resource catalog data items for different topics into different topic sub-jobs. The module for creating master assignments for a specific theme allows you to configure master assignment templates for different themes based on the sub-assignments of those themes. The data to be entered into the warehouse module acquires the data to be entered into the warehouse and filters the data to be entered into the warehouse to obtain filtered data to be entered into the warehouse. The data entry module performs subject classification based on the filtered data to be entered into the database, and selects the corresponding subject master job template to clean and enter the filtered data into the database. The cleaning process sub-jobs for the resource catalog data items include: A table creation script template for the resource directory data items is generated based on the resource directory and the resource directory data items; Generate a data source and a destination table based on the resource catalog data items; Add a data cleaning script based on the data items in the resource directory; Based on the data cleaning script added to the resource directory data item, add data cleaning scripts to the front and buffer corresponding to the resource directory data item; SQL query source is generated based on the mapping relationship between the resource directory data items and Elasticsearch.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements an ETL templated library loading method according to any one of claims 1 to 5.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements an ETL templated data import method according to any one of claims 1 to 5.

Images