A method for generating bulk library EODS table structure configuration based on automation script
By automating the EODS table structure configuration process through modular Shell scripts, the problems of low efficiency and high error rate in existing technologies are solved, and efficient and accurate EODS table structure configuration file generation is achieved.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- SHANGHAI TONGLIAN FINANCIAL SERVICES CO LTD
- Filing Date
- 2026-03-24
- Publication Date
- 2026-06-19
AI Technical Summary
In the construction of data platforms for large financial institutions or enterprises, existing technologies rely on manual operation for EODS table structure management, resulting in low efficiency, high error rate, poor repeatability and consistency, and difficulty in achieving intelligent and batch generation of EODS table structure configuration.
Using modular Shell scripts, the functions merge_coredb_lists, handle_merged_list, get_table_list, do_check_table, and generate_list automate the entire process from configuration merging, cleaning, table list extraction, intelligent matching and judgment to the final configuration file generation.
It enables intelligent and batch generation of EODS table structure configuration files, greatly improving work efficiency and accuracy, replacing traditional manual processes, and ensuring the standardization and accuracy of results.
Smart Images
Figure CN122240203A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database operation and maintenance and development operation and maintenance automation technology, and in particular to a method for generating batch database EODS table structure configurations based on automated scripts. Background Technology
[0002] In the construction of data platforms for large financial institutions or enterprises, managing the table structure of downstream EODS (End of Day Store) schemas monthly based on upstream table structure change documents is a crucial task. Business changes often necessitate adding audit fields (such as ETL_DATE, CREATED_TIME, etc.) to numerous existing tables. Traditionally, this task has heavily relied on manual operation, with the following workflow: Developers or version managers need to manually search and filter table structure changes related to a specific project (such as CPS or MPS systems) from multiple scattered .list configuration files in the coredb directory.
[0003] For the selected changes, it is necessary to manually determine whether the target table exists in the current EODS environment's table list (usually stored in multiple files under the eodsFiles directory).
[0004] Based on the judgment results (such as whether the table exists in all environments, some environments, or does not exist), different formats of final execution lists (such as eods_addcol.list and eodss_addcol.list) are manually written to guide the database to execute the ALTERTABLE ... ADD COLUMN ... operation.
[0005] This manual process has significant flaws: Inefficiency: When faced with hundreds or thousands of tables, manual searching, filtering, judging, and script writing are extremely time-consuming and can easily become a bottleneck in project delivery.
[0006] High error rate: Manual operation is prone to problems such as missing tables, incorrect tables, inconsistent field definitions or script format errors, which may lead to deployment failure or even data inconsistency or service interruption.
[0007] Poor repeatability and consistency: Each change relies on the experience of specific personnel, making it difficult to standardize and solidify the process, which is not conducive to knowledge transfer and compliance auditing.
[0008] Therefore, it is necessary to provide a method for generating batch EODS table structure configurations based on automated scripts to replace manual processes and achieve intelligent, batch generation of EODS table structure configuration files. Summary of the Invention
[0009] The purpose of this invention is to provide a method for generating batch EODS table structure configurations based on automated scripts, so as to replace manual processes and realize intelligent and batch generation of EODS table structure configuration files.
[0010] To address the problems existing in the prior art, this invention provides a method for generating batch EODS table structure configurations based on automated scripts, comprising the following steps: S1: Configure the merge module to use the merge_coredb_lists function to merge and generate a unified intermediate file merged_list.list; S2: Set up a data cleaning module and use the handle_merged_list function to clean the merged_list.list file generated by the merging module; S3: Set up the table list extraction module, and use the get_table_list function to process the cleaned merged_list.list file; S4: Set up the intelligent matching and judgment module, and use the do_check_table function to perform pre-checking and list classification. The lists are table_eods_list and table_eodss_list respectively. S5: Set up the configuration file generation module, and use the generate_list function to perform format conversion on the corresponding rows in the original merged_list.list based on table_eods_list and table_eodss_list to generate the final deliverable configuration files, namely eodss_addcol.list and eods_addcol.list files.
[0011] Optionally, in the method for generating batch library EODS table structure configuration based on automated scripts, the merging module is configured in S1 as follows: S11: The merge module traverses the input coredb directory and its two-level subdirectories, searching for all configuration files with the .list suffix; S12: The merging module concatenates the contents of the found scattered files in order, uses the function merge_coredb_lists to merge them into a unified intermediate file merged_list.list, and forces the addition of newline characters to the contents of each intermediate file during the merging process to provide clean and well-organized source data for subsequent processing.
[0012] Optionally, in the method for generating batch library EODS table structure configurations based on automated scripts, the function handle_merged_list in step S2 is used to clean the merged_list.list file generated by the merging module, as follows: Read the file line by line and use the fixed string matching function of the grep command to keep only the data lines that start with "CPS|" or "MPS|", and filter out and discard all the other lines.
[0013] Optionally, in the method for generating batch library EODS table structure configurations based on automated scripts, the get_table_list function is used in S3 to process the cleaned merged_list.list file, as follows: S31: Use the awk command with vertical bars as delimiters to extract the second column of each row. The second column of each row is the table name. S32: Sort and remove duplicates from the list of table names using the sort and uniq commands; S33: All unique target table names are assigned to a global shell variable TABLE_LIST.
[0014] Optionally, in the method for generating batch library EODS table structure configurations based on automated scripts, The pre-check method in S4 is as follows: iterate through each table name in the TABLE_LIST variable, and use grep to perform an exact match search in all relevant files under the eodsFiles directory. If no match is found for any table in all files, it is determined that no modification is needed and the process terminates; otherwise, proceed to the next step. In S4, the list is categorized as follows: After confirming the need for modification, the TABLE_LIST is traversed again to accurately count the total number of rows matched by each table in all EODS environment files; intelligent categorization is then performed based on the statistical results. Store the table names that match exactly 12 rows into the variable table_eodss_list; Store the table names that match 11 or 12 rows into the variable table_eods_list.
[0015] Optionally, in the method for generating batch library EODS table structure configurations based on automated scripts, the final deliverable configuration file is generated in S5 as follows: Iterate through each table name in table_eodss_list, find the original row containing that table name in merged_list.list, connect the first column of each row with the second column of each row with an underscore to form a new table name, and replace the row header identifier with EODSS. The converted row is written to the eodss_addcol.list file. Iterate through each table name in table_eods_list, perform a lookup and transformation operation, but replace the row header identifier with EODS, and write the transformed rows to the eods_addcol.list file.
[0016] Optionally, the method for generating batch library EODS table structure configuration based on automated scripts further includes the following step: S6: In the EODS SCHEMA table CPS_TM_CARD_BATCH_CANCEL_HST, add a field named ETL_DATE with data type DATE.
[0017] This invention also provides a system for generating batch library EODS table structure configurations based on automated scripts. The system for establishing batch library EODS table structure configurations using the aforementioned method includes: The merging module uses the merge_coredb_lists function to merge and generate a unified intermediate file merged_list.list; The data cleaning module uses the handle_merged_list function to clean the merged_list.list file generated by the merging module; The table list extraction module uses the get_table_list function to process the cleaned merged_list.list file. The intelligent matching and judgment module uses the do_check_table function to perform pre-checking and list classification, with the lists being table_eods_list and table_eodss_list respectively. The configuration file generation module uses the generate_list function to perform format conversion on the corresponding rows in the original merged_list.list based on table_eods_list and table_eodss_list, generating the final deliverable configuration files, namely eodss_addcol.list and eods_addcol.list.
[0018] In the method for generating batch EODS table structure configurations based on automated scripts provided by this invention, the entire process from merging and cleaning the original configurations, extracting the table list, intelligent matching and judgment to generating the final configuration file is automatically completed through modular Shell scripts. This greatly improves work efficiency and accuracy, thereby replacing manual processes and realizing the intelligent and batch generation of EODS table structure configuration files. Attached Figure Description
[0019] Figure 1 A flowchart illustrating a method for generating batch library EODS table structure configurations provided in an embodiment of the present invention. Detailed Implementation
[0020] The specific embodiments of the present invention will now be described in more detail with reference to the accompanying drawings. The advantages and features of the present invention will become clearer from the following description. It should be noted that the drawings are all in a very simplified form and use non-precise proportions, and are only used to facilitate and clarify the illustration of the embodiments of the present invention.
[0021] In the following, if the methods described herein include a series of steps, the order of these steps presented herein is not necessarily the only order in which these steps can be performed, and some of the steps described may be omitted and / or some other steps not described herein may be added to the method.
[0022] The purpose of this invention is to overcome the shortcomings of existing technologies and provide a method and system for generating batch EODS table structure configurations based on automated scripts. This method uses modular Shell scripts to automatically complete the entire process from merging and cleaning the original configurations, extracting the table list, intelligent matching and judgment, to generating the final configuration file, greatly improving work efficiency and accuracy.
[0023] To achieve the above objectives, this invention provides a method for generating batch database EODS table structure configurations based on an automated script. Its core is implemented by a master script (e.g., 2Eods.sh), which receives a coredb directory path as an input parameter and sequentially establishes and executes the following five modules.
[0024] like Figure 1 As shown, the overall execution flow is as follows: S1: Configure the merge module to use the merge_coredb_lists function to merge and generate a unified intermediate file merged_list.list; S11: The merge module traverses the input coredb directory and its two-level subdirectories, searching for all configuration files with the .list suffix; S12: The merging module concatenates the contents of the found scattered files in order and uses the function merge_coredb_lists to generate a unified intermediate file merged_list.list. This process ensures the integrity of the data and forces the addition of newline characters to the contents of each intermediate file during merging, so as to provide clean and well-organized source data for subsequent processing.
[0025] S2: Configure the data cleaning module, using the handle_merged_list function to clean the merged_list.list file generated by the merging module, as follows: The file is read line by line, and the fixed string matching function of the grep command is used to keep only the data lines that start with "CPS|" or "MPS|", and all other lines are filtered and discarded. This step accurately filters out the valid table structure changes related to the target system (CPS, MPS) and forms a standardized list to be processed.
[0026] S3: Set up the table list extraction module, and use the get_table_list function to process the cleaned merged_list.list file as follows: S31: Use the awk command with the vertical bar "|" as the separator to extract the second column of each row. The second column of each row is the table name. S32: Sort and remove duplicates from the list of table names using the sort and uniq commands; S33: All unique target table names are assigned to a global Shell variable TABLE_LIST for use by subsequent modules.
[0027] S4: Set up the intelligent matching and judgment module, and use the do_check_table function to perform pre-checking and list classification. The lists are table_eods_list and table_eodss_list respectively. Specifically, the pre-check method is as follows: iterate through each table name in the TABLE_LIST variable and check all related files (e.g., multiple) in the eodsFiles directory. YH_EODS The .txt file is used to perform an exact match search using grep. If no match is found in any of the tables across all files, the process is considered to be without modification and terminates; otherwise, proceed to the next step. The list is categorized as follows: After confirming the need for modification, the TABLE_LIST is iterated again to accurately count the total number of rows matched by each table in all EODS environment files; intelligent categorization is then performed based on the statistical results. Store the table names that match exactly 12 rows into the variable table_eodss_list; Store the table names that match 11 or 12 rows into the variable table_eods_list.
[0028] This classification logic lays the foundation for generating different types of final configurations for tables with different matching degrees.
[0029] S5: Set up the configuration file generation module, and use the generate_list function to perform format conversion on the corresponding rows in the original merged_list.list based on table_eods_list and table_eodss_list to generate the final deliverable configuration files, namely eodss_addcol.list and eods_addcol.list files.
[0030] Specifically, the method for generating the final deliverable configuration file is as follows: Iterate through each table name in table_eodss_list, find the original row containing that table name in merged_list.list, connect the first column of each row (e.g., "CPS") with the second column (table name) of each row using an underscore to form a new table name (e.g., "CPS_TM_CARD"), and replace the row header identifier with EODSS. The converted row (e.g., EODSS|CPS_TM_CARD|add|ETL_DATE DATE) is written to the eodss_addcol.list file. Iterate through each table name in table_eods_list, perform a lookup and transformation operation, but replace the row header identifier with EODS, and write the transformed rows to the eods_addcol.list file.
[0031] S6: This invention ultimately generates two configuration files: eods_addcol.list and eodss_addcol.list. Taking the line "EODS|CPS_TM_CARD_BATCH_CANCEL_HST|add|ETL_DATE DATE" in eods_addcol.list as an example, it explicitly instructs the database operation and maintenance system to add a field named ETL_DATE with the data type DATE to the table CPS_TM_CARD_BATCH_CANCEL_HST in the EODS SCHEMA. By running the script of this invention, the analysis, filtering, and writing work that originally required several hours of manual work can be completed automatically in seconds, with standardized and accurate output results, achieving a revolutionary improvement in efficiency and quality assurance in EODS table structure configuration.
[0032] This invention also provides a system for generating batch library EODS table structure configurations based on automated scripts. The system for establishing batch library EODS table structure configurations using the aforementioned method includes: (1) The merging module uses the merge_coredb_lists function to merge and generate a unified intermediate file merged_list.list; The merge module is configured to traverse the input coredb directory and its two-level subdirectories, searching for all configuration files with the .list suffix; The merge module is configured to concatenate the contents of the found scattered files in order, and use the function merge_coredb_lists to merge them into a unified intermediate file merged_list.list. This process ensures the integrity of the data and forces the addition of newline characters to the contents of each intermediate file during the merge process, so as to provide clean and well-organized source data for subsequent processing.
[0033] (2) The data cleaning module uses the handle_merged_list function to clean the merged_list.list file generated by the merging module, as follows: The file is read line by line, and the fixed string matching function of the grep command is used to keep only the data lines that start with "CPS|" or "MPS|", and all other lines are filtered and discarded. This step accurately filters out the valid table structure changes related to the target system (CPS, MPS) and forms a standardized list to be processed.
[0034] (3) The table list extraction module uses the get_table_list function to process the cleaned merged_list.list file. The processing method is as follows: Use the awk command with the vertical bar "|" as the separator to extract the second column of each row. The second column of each row is the table name. The list of table names can be sorted and deduplicated using the sort and uniq commands. All unique target table names are assigned to a global shell variable TABLE_LIST for use by subsequent modules.
[0035] (4) Intelligent matching and judgment module, which uses the do_check_table function for pre-checking and list classification, with the lists being table_eods_list and table_eodss_list respectively; Specifically, the pre-check method is as follows: iterate through each table name in the TABLE_LIST variable and check all related files (e.g., multiple) in the eodsFiles directory. YH_EODS The .txt file is used to perform an exact match search using grep. If no match is found in any of the tables across all files, the process is considered to be without modification and terminates; otherwise, proceed to the next step. The list is categorized as follows: After confirming the need for modification, the TABLE_LIST is iterated again to accurately count the total number of rows matched by each table in all EODS environment files; intelligent categorization is then performed based on the statistical results. Store the table names that match exactly 12 rows into the variable table_eodss_list; Store the table names that match 11 or 12 rows into the variable table_eods_list.
[0036] This classification logic lays the foundation for generating different types of final configurations for tables with different matching degrees.
[0037] (5) The configuration file generation module uses the generate_list function to perform format conversion on the corresponding rows in the original merged_list.list based on table_eods_list and table_eodss_list, and generates the final deliverable configuration files, namely eodss_addcol.list and eods_addcol.list.
[0038] Specifically, the method for generating the final deliverable configuration file is as follows: Iterate through each table name in table_eodss_list, find the original row containing that table name in merged_list.list, connect the first column of each row (e.g., "CPS") with the second column (table name) of each row using an underscore to form a new table name (e.g., "CPS_TM_CARD"), and replace the row header identifier with EODSS. The converted row (e.g., EODSS|CPS_TM_CARD|add|ETL_DATE DATE) is written to the eodss_addcol.list file. Iterate through each table name in table_eods_list, perform a lookup and transformation operation, but replace the row header identifier with EODS, and write the transformed rows to the eods_addcol.list file.
[0039] This invention ultimately generates two configuration files: eods_addcol.list and eodss_addcol.list. Taking the line "EODS|CPS_TM_CARD_BATCH_CANCEL_HST|add|ETL_DATE DATE" in eods_addcol.list as an example, it explicitly instructs the database operation and maintenance system to add a field named ETL_DATE with the data type DATE to the CPS_TM_CARD_BATCH_CANCEL_HST table in the EODS SCHEMA. By running the script of this invention, the analysis, filtering, and writing work that originally required several hours of manual work can be completed automatically in seconds, with standardized and accurate output results, achieving a revolutionary improvement in efficiency and quality assurance in EODS table structure configuration.
[0040] In summary, the method for generating batch EODS table structure configurations based on automated scripts provided by this invention automatically completes the entire process from merging and cleaning the original configurations, extracting the table list, intelligent matching and judgment to generating the final configuration file through modular Shell scripts. This greatly improves work efficiency and accuracy, thereby replacing manual processes and realizing the intelligent and batch generation of EODS table structure configuration files.
[0041] The above are merely preferred embodiments of the present invention and do not constitute any limitation on the present invention. Any equivalent substitutions or modifications made by those skilled in the art to the technical solutions and content disclosed in the present invention without departing from the scope of the present invention shall be deemed to have remained within the protection scope of the present invention.
Claims
1. A method for generating batch EODS table structure configurations based on automated scripts, characterized in that, Includes the following steps: S1: Configure the merge module to use the merge_coredb_lists function to merge and generate a unified intermediate file merged_list.list; S2: Set up a data cleaning module and use the handle_merged_list function to clean the merged_list.list file generated by the merging module; S3: Set up the table list extraction module, and use the get_table_list function to process the cleaned merged_list.list file; S4: Set up the intelligent matching and judgment module, and use the do_check_table function to perform pre-checking and list classification. The lists are table_eods_list and table_eodss_list respectively. S5: Set up the configuration file generation module, and use the generate_list function to perform format conversion on the corresponding rows in the original merged_list.list based on table_eods_list and table_eodss_list to generate the final deliverable configuration files, namely eodss_addcol.list and eods_addcol.list files.
2. The method for generating batch library EODS table structure configurations based on automated scripts as described in claim 1, characterized in that, Configure the merging module in S1 as follows: S11: The merge module traverses the input coredb directory and its two-level subdirectories, searching for all configuration files with the .list suffix; S12: The merging module concatenates the contents of the found scattered files in order, uses the function merge_coredb_lists to merge them into a unified intermediate file merged_list.list, and forces the addition of newline characters to the contents of each intermediate file during the merging process to provide clean and well-organized source data for subsequent processing.
3. The method for generating batch library EODS table structure configurations based on automated scripts as described in claim 2, characterized in that, In S2, the function handle_merged_list is used to clean the merged_list.list file generated by the merging module, as follows: Read the file line by line and use the fixed string matching function of the grep command to keep only the data lines that start with "CPS|" or "MPS|", and filter out and discard all the other lines.
4. The method for generating batch library EODS table structure configurations based on automated scripts as described in claim 3, characterized in that, In S3, the `get_table_list` function is used to process the cleaned `merged_list.list` file, as follows: S31: Use the awk command with vertical bars as delimiters to extract the second column of each row. The second column of each row is the table name. S32: Sort and remove duplicates from the list of table names using the sort and uniq commands; S33: All unique target table names are assigned to a global shell variable TABLE_LIST.
5. The method for generating batch library EODS table structure configurations based on automated scripts as described in claim 4, characterized in that, The pre-check method in S4 is as follows: iterate through each table name in the TABLE_LIST variable, and use grep to perform an exact match search in all relevant files under the eodsFiles directory. If no match is found for any table in all files, it is determined that no modification is needed and the process terminates. Otherwise, proceed to the next step; In S4, the list is categorized as follows: After confirming the need for modification, the TABLE_LIST is traversed again to accurately count the total number of rows matched by each table in all EODS environment files; intelligent categorization is then performed based on the statistical results. Store the table names that match exactly 12 rows into the variable table_eodss_list; Store the table names that match 11 or 12 rows into the variable table_eods_list.
6. The method for generating batch library EODS table structure configurations based on automated scripts as described in claim 5, characterized in that, The method for generating a final, deliverable configuration file in S5 is as follows: Iterate through each table name in table_eodss_list, find the original row containing that table name in merged_list.list, connect the first column of each row with the second column of each row with an underscore to form a new table name, and replace the row header identifier with EODSS. The converted row is written to the eodss_addcol.list file. Iterate through each table name in table_eods_list, perform a lookup and transformation operation, but replace the row header identifier with EODS, and write the transformed rows to the eods_addcol.list file.
7. The method for generating batch library EODS table structure configurations based on automated scripts as described in claim 6, characterized in that, It also includes the following steps: S6: In the EODS SCHEMA table CPS_TM_CARD_BATCH_CANCEL_HST, add a new field named ETL_DATE with the data type DATE.
8. A system for generating batch library EODS table structure configurations based on automated scripts, characterized in that, A system for configuring the structure of a batch library EODS table using the method described in any one of claims 1-7 includes: The merging module uses the merge_coredb_lists function to merge and generate a unified intermediate file merged_list.list; The data cleaning module uses the handle_merged_list function to clean the merged_list.list file generated by the merging module; The table list extraction module uses the get_table_list function to process the cleaned merged_list.list file. The intelligent matching and judgment module uses the do_check_table function to perform pre-checking and list classification, with the lists being table_eods_list and table_eodss_list respectively. The configuration file generation module uses the generate_list function to perform format conversion on the corresponding rows in the original merged_list.list based on table_eods_list and table_eodss_list, generating the final deliverable configuration files, namely eodss_addcol.list and eods_addcol.list.