Data table determination method and device, storage medium and electronic device

Abstract

The present invention discloses a method and device for determining a data table, a storage medium and an electronic device, wherein the method includes: obtaining a development file in a target application, wherein the development file is used to indicate the construction rules of the data table, and the development file includes: a template file, a structured query language of a heterogeneous data dictionary; receiving a setting instruction of a target object, performing query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: database information of a source system; synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement. The above technical solution solves the problems of being unable to quickly and accurately confirm the data table for establishing a data warehouse, and determining the corresponding table creation statement through the data table.

Description

Technical Field

[0001] The present invention relates to the field of communications, and in particular to a method and device for determining a data table, a storage medium, and an electronic device. Background Art

[0002] Data warehouses are playing an increasingly important role in enterprise decision support and OLAP (Online Analytical Processing) applications, leading to an increasing number of developers participating in data warehouse construction and data modeling. Data access and model building require extensive Hive data warehouse table creation and field type mapping. Manually coding Hive table creation statements is inefficient and error-prone. Manually mapping field types across heterogeneous databases can lead to poor standardization, frequent type incompatibilities, and precision loss. Naming standards are highly dependent on the developer's skill set, creating a high learning curve for new developers and making naming errors common during the development phase.

[0003] In related technologies, there is no effective solution to the problems of being unable to quickly and accurately identify the data tables for establishing data warehouses, and to determine the corresponding table creation statements through the data tables. Summary of the Invention

[0004] Embodiments of the present invention provide a method and device for determining a data table, a storage medium, and an electronic device to at least solve the problems in the related art of being unable to quickly and accurately confirm the data table for establishing a data warehouse, and to determine the corresponding table creation statement through the data table.

[0005] According to an embodiment of the present invention, a method for determining a data table is provided, comprising: obtaining a development file in a target application, wherein the development file is used to indicate a construction rule for a data table, and the development file includes: a template file, and a structured query language for a heterogeneous data dictionary; receiving a setting instruction for a target object, performing query processing on the structured query language for the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language for the heterogeneous data dictionary, wherein the setting instruction includes: database information to which a source system belongs; synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement.

[0006] In an exemplary embodiment, a setting instruction for a target object is received, and query processing is performed on the structured query language of a heterogeneous data dictionary according to the setting instruction to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, including: uploading the structured query language of the heterogeneous data dictionary to the source system of the corresponding database for data type mapping according to the database information of the source system carried by the setting instruction; and summarizing the data type information in the database, the data type information of the data warehouse to be established, and the results of the data type mapping to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

[0007] In an exemplary embodiment, based on the database information of the source system carried by the setting instruction, the structured query language of the heterogeneous data dictionary is uploaded to the source system of the corresponding database for data type mapping, including: obtaining the first data type of the structured query language of the heterogeneous data dictionary when stored in the database, and determining the first byte length of the first data type; determining the second byte length obtained by performing a logarithmic operation on the first byte length in the source system of the database, wherein the second byte length is used to indicate the length of the first byte length when stored in the data warehouse as the second data type; and determining the result of data type mapping of the structured query language of the heterogeneous data dictionary in the source system of the database based on the second byte length.

[0008] In an exemplary embodiment, synchronizing a standard data dictionary into a template file to generate a data table includes: parsing the standard data dictionary, determining the location coordinate information of a target field in the standard data dictionary, wherein the target field includes at least one of the following: a field name in the standard data dictionary, a data type in the standard data dictionary, and a field annotation in the standard data dictionary; and matching the target field into the template file according to the location coordinate information to generate a data table.

[0009] In an exemplary embodiment, a data table is uploaded to a target application to obtain a corresponding standardized table creation statement, including: uploading the data table to the target application, and determining the target application's detection result for the data table, wherein the detection result is used to indicate the recognition result of the verification parameters configured in the target application on the content in the data table, and the verification parameters include at least one of the following: a reserved word list of the data table, a naming rule and / or additional field information of the data table, a timestamp of the data table, a data type of the data table, a storage format of the data table, and a reference field of the data table; when the detection result indicates that the data table passes the target application detection, receiving the standardized table creation statement for data table conversion issued by the target application.

[0010] In an exemplary embodiment, after receiving the standardized table creation statement for data table conversion issued by the target application, the above method also includes: submitting the standardized table creation statement to the data warehouse for execution, and obtaining the execution result of the standardized table creation statement executed by the data warehouse; when the execution result indicates that the target data warehouse corresponding to the standardized table creation statement has been successfully established, generating a prompt message, wherein the prompt message is used to prompt that the target data warehouse corresponding to the target object data table has been successfully established.

[0011] In an exemplary embodiment, after obtaining the development file in the target application, the above method also includes: when the setting instruction also includes table name information, changing the name of the template file in the development file according to the table name information; when it is determined that the template file has completed the name change, selecting attribute information of the template file according to preset specifications, wherein the attribute information includes at least one of the following: the database library name corresponding to the template file, the partition type of the template file in the database, the information of the current creator of the template file, and the table type corresponding to the template file.

[0012] According to another embodiment of the present invention, a device for determining a data table is also provided, including: an acquisition module for acquiring a development file in a target application, wherein the development file is used to indicate a construction rule of a data table, and the development file includes: a template file, a structured query language of a heterogeneous data dictionary; a query module for receiving a setting instruction of a target object, performing query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: database information of a source system; a generation module for synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement.

[0013] In an exemplary embodiment, the above-mentioned query module is also used to upload the structured query language of the heterogeneous data dictionary to the source system of the corresponding database for data type mapping according to the database information of the source system carried by the setting instruction; summarize the data type information in the database, the data type information of the data warehouse to be established, and the results of the data type mapping to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

[0014] In an exemplary embodiment, the query module is further used to obtain a first data type of a structured query language of a heterogeneous data dictionary when stored in a database, and determine a first byte length of the first data type; determine a second byte length obtained by performing a logarithmic operation on the first byte length in a source system of the database, wherein the second byte length is used to indicate the length of the first byte length when stored in a data warehouse as a second data type; and determine a result of data type mapping of the structured query language of the heterogeneous data dictionary in the source system of the database based on the second byte length.

[0015] In an exemplary embodiment, the above-mentioned generation module is also used to parse the standard data dictionary and determine the location coordinate information of the target field in the standard data dictionary, wherein the target field includes at least one of the following: the field name in the standard data dictionary, the data type in the standard data dictionary, and the field annotation in the standard data dictionary; and match the target field to the template file according to the location coordinate information to generate a data table.

[0016] In an exemplary embodiment, the above-mentioned generation module is also used to upload the data table to the target application and determine the detection result of the target application on the data table, wherein the detection result package is used to indicate the recognition result of the verification parameters configured in the target application on the content of the data table, and the verification parameters include at least one of the following: a reserved word list of the data table, a naming rule and / or additional field information of the data table, a timestamp of the data table, a data type of the data table, a storage format of the data table, and a reference field of the data table; when the detection result indicates that the data table passes the target application detection, a standardized table creation statement for data table conversion issued by the target application is received.

[0017] In an exemplary embodiment, the above-mentioned device also includes: an execution module, which is used to submit the standardized table creation statement to the data warehouse for execution, and obtain the execution result of the standardized table creation statement executed by the data warehouse; when the execution result indicates that the target data warehouse corresponding to the standardized table creation statement has been successfully established, a prompt message is generated, wherein the prompt message is used to prompt that the target data warehouse corresponding to the target object data table has been successfully established.

[0018] In an exemplary embodiment, the above-mentioned device also includes: a change module, which is used to change the name of the template file in the development file according to the table name information when the setting instruction also includes table name information; when it is determined that the template file has completed the name change, the attribute information of the template file is selected according to the preset specification, wherein the attribute information includes at least one of the following: the database library name corresponding to the template file, the partition type of the template file in the database, the information of the current creator of the template file, and the table type corresponding to the template file.

[0019] According to another aspect of the embodiments of the present invention, a computer-readable storage medium is provided, in which a computer program is stored. The computer program is configured to execute the above-mentioned method for determining the data table when running.

[0020] According to another aspect of an embodiment of the present invention, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the method for determining the data table through the computer program.

[0021] In an embodiment of the present invention, a development file in a target application is obtained, wherein the development file is used to indicate the construction rules of a data table, and the development file includes: a template file and a structured query language of a heterogeneous data dictionary; a setting instruction of a target object is received, and the structured query language of the heterogeneous data dictionary is queried and processed according to the setting instruction to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: the database information of the source system; the standard data dictionary is synchronized to the template file to generate a data table, and the data table is uploaded to the target application to obtain a corresponding standardized table creation statement; that is, the target application parses the standard specification for establishing a data warehouse to obtain a template file corresponding to the standard specification and the structured query language of the heterogeneous data dictionary, and then the target application processes the data table corresponding to the template file after the target object is processed, and determines the standardized table creation statement corresponding to the data table to establish the data warehouse. The above technical solution solves the problems of being unable to quickly and accurately confirm the data table for establishing the data warehouse and determining the corresponding table creation statement through the data table, thereby realizing the rapid establishment of the data warehouse corresponding to the database of the source system, improving the efficient processing of data dictionary information, and making the data warehouse establishment specification realize the unification of the standardized process. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of this application. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the drawings:

[0023] Figure 1 This is a hardware structure block diagram of a computer terminal for a method for determining a data table according to an embodiment of the present invention;

[0024] Figure 2 is a flowchart of a method for determining a data table according to an embodiment of the present invention;

[0025] Figure 3 is a timing diagram of a method for determining a data table according to an optional embodiment of the present invention;

[0026] Figure 4is a flowchart of WEB application data processing according to an optional embodiment of the present invention;

[0027] Figure 5 is a system architecture diagram of a WEB application according to an optional embodiment of the present invention;

[0028] Figure 6 It is a structural block diagram of a device for determining a data table according to an embodiment of the present invention. DETAILED DESCRIPTION

[0029] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0030] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0031] The method embodiments provided in the embodiments of the present application can be executed in a mobile terminal, a computer terminal or a similar computing device. Taking running on a computer terminal as an example, Figure 1 FIG. 1 is a hardware structure diagram of a computer terminal for a method for determining a data table according to an embodiment of the present invention. Figure 1 As shown, the computer terminal may include one or more ( Figure 1 Only one is shown) a processor 102 (the processor 102 may include but is not limited to a microprocessor MCU or a programmable logic device FPGA and other processing devices) and a memory 104 for storing data. In an exemplary embodiment, the computer terminal may also include a transmission device 106 and an input / output device 108 for communication functions. It will be understood by those skilled in the art that Figure 1 The structure shown is only for illustration and does not limit the structure of the above-mentioned computer terminal. For example, the computer terminal may also include Figure 1More or fewer components than shown, or with Figure 1 Equivalent functions or comparisons shown Figure 1 Shown are different configurations with more functionality.

[0032] The memory 104 can be used to store computer programs, for example, software programs and modules of application software, such as the computer program corresponding to the method for determining a data table in the embodiment of the present invention. The processor 102 executes the computer program stored in the memory 104 to perform various functional applications and data processing, thereby implementing the above-mentioned method. The memory 104 may include a high-speed random access memory and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include a memory remotely located relative to the processor 102, and these remote memories may be connected to the computer terminal via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0033] The transmission device 106 is used to receive or send data via a network. A specific example of the aforementioned network may include a wireless network provided by a computer terminal's communications provider. In one embodiment, the transmission device 106 includes a network interface controller (NIC), which can be connected to other network devices via a base station to enable communication with the Internet. In another embodiment, the transmission device 106 may be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0034] In this embodiment, a method for determining a data table is provided, which is applied to the above-mentioned computer terminal. Figure 2 4 is a flow chart of a method for determining a data table according to an embodiment of the present invention, the flow chart comprising the following steps:

[0035] Step S202: Acquire a development file in the target application, wherein the development file is used to indicate a construction rule for a data table, and the development file includes: a template file, and a structured query language for a heterogeneous data dictionary;

[0036] It should be noted that the above-mentioned development files can be flexibly constructed in the target application according to the data warehouse standard specifications. During the construction, the target application identifies the non-hidden worksheets in the preset template file, determines that the content structure in the preset template file meets the template specifications, and then processes the table information related content. The configured data warehouse hive reserved word list, data warehouse development specification naming rules and additional field information, timestamp data type and parquet storage format conflict identification function module, reserved word automatic quote module in the target application are then configured to correspond to the template file that meets the data warehouse standard specifications.

[0037] Step S204: receiving a setting instruction for the target object, performing query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: information about the database to which the source system belongs;

[0038] Optionally, after determining the database and table name information of the source table of the source system corresponding to the structured query language of the heterogeneous data dictionary, the standard data types are automatically mapped using the structured query language.

[0039] Step S206 , synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement.

[0040] Through the above steps, the development file in the target application is obtained, wherein the development file is used to indicate the construction rules of the data table, and the development file includes: a template file, a structured query language of a heterogeneous data dictionary; receiving the setting instruction of the target object, querying the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining the standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: the database information of the source system; synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain the corresponding standardized table creation statement; that is, the target application parses the standard specification for data warehouse establishment to obtain the template file corresponding to the standard specification and the structured query language of the heterogeneous data dictionary, and then processes the data table corresponding to the template file after the target object is processed by the target application, determines the standardized table creation statement corresponding to the data table, and establishes the data warehouse. The above technical solution solves the problems of being unable to quickly and accurately confirm the data table for establishing the data warehouse and determining the corresponding table creation statement through the data table, thereby realizing the rapid establishment of the data warehouse corresponding to the database of the source system, and improving the efficient processing of data dictionary information, so that the data warehouse establishment specification realizes the unification of the standardized process.

[0041] Optionally, the above template file can be a spreadsheet template or other types of templates. This is flexibly determined according to actual conditions, and this application does not impose too many restrictions on this.

[0042] In an exemplary embodiment, a setting instruction for a target object is received, and query processing is performed on the structured query language of a heterogeneous data dictionary according to the setting instruction to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, including: uploading the structured query language of the heterogeneous data dictionary to the source system of the corresponding database for data type mapping according to the database information of the source system carried by the setting instruction; and summarizing the data type information in the database, the data type information of the data warehouse to be established, and the results of the data type mapping to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

[0043] That is to say, when determining the standard data dictionary for establishing a data warehouse corresponding to the structured query language of the heterogeneous data dictionary, it is necessary to determine the database information of the source system for the data warehouse to be established, and then determine the corresponding data type in the data warehouse based on the data type corresponding to the database information to complete the data type mapping. Further, the data type information in the database, the data type information of the data warehouse to be established, and the results of the data type mapping are summarized to determine the standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

[0044] In an exemplary embodiment, based on the database information of the source system carried by the setting instruction, the structured query language of the heterogeneous data dictionary is uploaded to the source system of the corresponding database for data type mapping, including: obtaining the first data type of the structured query language of the heterogeneous data dictionary when stored in the database, and determining the first byte length of the first data type; determining the second byte length obtained by performing a logarithmic operation on the first byte length in the source system of the database, wherein the second byte length is used to indicate the length of the first byte length when stored in the data warehouse as the second data type; and determining the result of data type mapping of the structured query language of the heterogeneous data dictionary in the source system of the database based on the second byte length.

[0045] For example, after the data dictionary SQL (equivalent to the structured query language of the heterogeneous data dictionary in the embodiment of the present invention) exists on the development client, the source table's library and table name information are filled in according to the setting instructions issued by the target object, and the data dictionary SQL (Structured Query language, structured query language, referred to as SQL) is submitted to the source system for query. The data dictionary SQL will complete the standard data type mapping. For example, for the bit data type in the MySQL database, the data dictionary SQL will automatically perform logarithmic operations to calculate the data type length in the hive data warehouse, and the field order will be automatically sorted according to the field order of the source system.

[0046] In an exemplary embodiment, synchronizing a standard data dictionary into a template file to generate a data table includes: parsing the standard data dictionary, determining the location coordinate information of a target field in the standard data dictionary, wherein the target field includes at least one of the following: a field name in the standard data dictionary, a data type in the standard data dictionary, and a field annotation in the standard data dictionary; and matching the target field into the template file according to the location coordinate information to generate a data table.

[0047] It is understood that, in order to facilitate the filling of the standard data dictionary in the corresponding template file, the target fields in the standard data dictionary generated by data type mapping in the source system have specific location coordinates. These fields can then be matched with the default coordinate location information in the template file, allowing the standard data dictionary to be quickly filled into the template file to generate a data table. For example, the fields at the location coordinates in the data dictionary are copied to the corresponding coordinates in the template to complete the matching, thereby converting the standard data dictionary into a data table.

[0048] In an exemplary embodiment, a data table is uploaded to a target application to obtain a corresponding standardized table creation statement, including: uploading the data table to the target application, and determining the target application's detection result for the data table, wherein the detection result is used to indicate the recognition result of the verification parameters configured in the target application on the content in the data table, and the verification parameters include at least one of the following: a reserved word list of the data table, a naming rule and / or additional field information of the data table, a timestamp of the data table, a data type of the data table, a storage format of the data table, and a reference field of the data table; when the detection result indicates that the data table passes the target application detection, receiving the standardized table creation statement for data table conversion issued by the target application.

[0049] That is, when the target application converts the data table into the corresponding standardized table creation statement for building the data warehouse, in order to make the converted standardized table creation statement better fit the current data warehouse, the data table is tested according to the verification parameters of the preset conditions of the data warehouse to be established.

[0050] Optionally, the validation parameters for the preset conditions include: a list of Hive reserved words, naming rules and additional field information in the data warehouse development specification, a conflict identification module for the timestamp data type and the parquet storage format, and an automatic quotation module for reserved words. The validation parameters are then used to determine whether the corresponding content structure in the data table complies with the template specification, convert the read content in the data table into standardized table creation statements, and use the standardized table creation statements to establish the data warehouse.

[0051] Optionally, when creating a data warehouse using a data table, if multiple data tables differ only in their database names, simply separate the database names with slashes. To create multiple data tables with different structures at once, simply expand the worksheet in the same Excel file.

[0052] In an exemplary embodiment, after receiving the standardized table creation statement for data table conversion issued by the target application, the above method also includes: submitting the standardized table creation statement to the data warehouse for execution, and obtaining the execution result of the standardized table creation statement executed by the data warehouse; when the execution result indicates that the target data warehouse corresponding to the standardized table creation statement has been successfully established, generating a prompt message, wherein the prompt message is used to prompt that the target data warehouse corresponding to the target object data table has been successfully established.

[0053] In an exemplary embodiment, after obtaining the development file in the target application, the above method also includes: when the setting instruction also includes table name information, changing the name of the template file in the development file according to the table name information; when it is determined that the template file has completed the name change, selecting attribute information of the template file according to preset specifications, wherein the attribute information includes at least one of the following: the database library name corresponding to the template file, the partition type of the template file in the database, the information of the current creator of the template file, and the table type corresponding to the template file.

[0054] In order to better understand the process of the method for determining the above data table, the implementation method flow of determining the above data table is described below in combination with an optional embodiment, but it is not intended to limit the technical solution of the embodiment of the present invention.

[0055] In this embodiment, a method for determining a data table is provided. Figure 3 is a timing diagram of a method for determining a data table according to an optional embodiment of the present invention, such as Figure 3 As shown, the specific steps are as follows:

[0056] Step S301: The developer client is a data warehouse developer (equivalent to the target object in the embodiment of the present invention). The developer client uses a browser to access a web page and enters a web application (equivalent to the target application in the embodiment of the present invention). The web application provides template file download and generation of a heterogeneous data dictionary SQL (equivalent to the structured query language of the heterogeneous data dictionary in the embodiment of the present invention).

[0057] Step S302: Displaying the template file and the data dictionary SQL on the corresponding browser client;

[0058] Step S303: Send the data dictionary SQL on the client to the source system for query. After obtaining the data dictionary SQL, fill in the source table's library and table name information, and submit the SQL to the source system for query. The data dictionary SQL will complete the standard data type mapping. For example, for a bit data type like in a MySQL database, the SQL will automatically perform logarithmic operations to calculate the length of the data type in Hive, and the field order will be automatically sorted according to the field order of the source system.

[0059] Step S304: The source system converts the data dictionary SQL into a corresponding standard data dictionary. The source system returns the data dictionary information to the client. The field names, data types, and location coordinates of field comments in the data dictionary are automatically matched with the Excel template file.

[0060] Step S305: The source system returns the data dictionary information to the developer client. The field names, data types, and location coordinates of field comments in the data dictionary are automatically matched with the Excel template file.

[0061] Fill out the Excel template file on the client side, completing the necessary library name, partition, creator, and table type selection. The Excel template file uses built-in functions to determine naming conventions and predict table names. If you need to create multiple tables that differ only in library names, simply separate the library names with a slash. To create multiple tables with different structures at once, simply expand the worksheet in the same Excel template file.

[0062] Step S306: Upload the Excel template file to the WEB application;

[0063] Step S307: The WEB application performs a reserved word check and a compatibility check on the Excel template file;

[0064] Step S308: If the test passes, the Excel template file is converted into a standardized table creation statement for establishing a data warehouse and sent to the corresponding client;

[0065] Optionally, the web application traverses all non-hidden Excel sheets (worksheets), identifies their contents, and processes the table information after determining that the structure conforms to the template specifications. The web application is configured with a list of Hive reserved words, naming rules and additional field information consistent with data warehouse development specifications, a module for identifying conflicts between the timestamp data type and the Parquet storage format, and a module for automatically quoting reserved words. After completing the standard verification, multiple standardized table creation statements are generated and returned to the developer client.

[0066] Step S309: Send the standardized table creation statement to the Hive data warehouse to create the corresponding data warehouse; submit the table creation statement to the Hive data warehouse for execution, and the data warehouse returns the execution result, completing the standardized Hive table landing;

[0067] Step S310: Feedback the establishment result to the client for display.

[0068] This embodiment also provides a WEB application data processing method. Figure 4 : is a flow chart of WEB application data processing according to an optional embodiment of the present invention, such as Figure 4 As shown, the specific steps are as follows:

[0069] Step S401: The WEB application parses the Excel file uploaded by the user;

[0070] Step S402: Read non-hidden worksheets;

[0071] Step S403: Determine whether the Excel file conforms to the template format;

[0072] Step S404: if the template format is met, parse the worksheet content;

[0073] Step S405: Determine whether it complies with the data warehouse development specifications;

[0074] Step S406: If the data warehouse development specifications are met, perform field information traversal;

[0075] Step S407: Check whether the field name is a Hive reserved word;

[0076] Step S408: If the field name is a Hive reserved word, quote the field;

[0077] Step S409: Prompt that the field contains reserved words;

[0078] Step S410: Supplement additional fields;

[0079] Step S411: Generate and temporarily store the table creation statement for this worksheet;

[0080] Step S412: determining whether there is an unprocessed worksheet;

[0081] Step S413: When all worksheets are processed, the standard table creation statement is returned to the developer client.

[0082] It should be noted that the specific features of the above-mentioned WEB application's processing method for uploaded Excel files include: 1) The Excel file is processed by traversing the sheet, and hidden sheets are automatically identified and skipped during the process. It helps the developer client to realize the batch generation of table creation statements while allowing the developer client to accurately control the specific sheet to be processed. 2) When traversing the sheet, the library name, table name, table type, and partition configuration information will be taken out from the specific coordinates and verified using the data warehouse development specifications. 3) After passing the development specification verification, the fields will be traversed. The fields will be checked for reserved words, and after the additional fields are supplemented, the table creation statement for the current sheet will be generated and temporarily stored. 4) If there is an unprocessed sheet, jump to step 2) to continue processing. After all sheet data is processed, the generated table creation statement information will be returned to the developer client in batches.

[0083] As an optional implementation, Figure 5 The figure shows the system architecture of a web application in an optional embodiment of the present invention. It includes the following components: a front-end UI 52, a deployment environment 54, and infrastructure 56. It should be noted that the web application logic is implemented in Python, with Excel file processing enabled by the open-pyxl module, data validation enabled by RE, and traversal operations enabled by itertools. Flask is used to build the web application. The front-end UI utilizes the traditional HTML, CSS, and JS technology stack.

[0084] Through the above optional embodiments, using data tables and corresponding WEB applications, in the process of building a data warehouse, efficient processing of data dictionary information is achieved with the help of data dictionary SQL and data tables that automatically map data types. The processing flow of template files in WEB applications and data warehouse construction specifications are built into data tables and WEB applications to achieve efficient processing. Field types are automatically mapped, and developers do not need to refer to the specifications to process the field types of the source system one by one. The improvement in efficiency is particularly obvious when processing tables with more fields. And accurate processing results are achieved. It saves developers a lot of programmed coding work, significantly reduces the probability of spelling errors, and allows developers to complete development work that complies with specifications even if they do not fully understand the development specifications and the technical details of the data warehouse.

[0085] Through the description of the above embodiments, those skilled in the art can clearly understand that the method according to the above embodiment can be implemented by means of software plus the necessary general hardware platform, and of course it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal device (which can be a mobile phone, computer, server, or network device, etc.) to execute the methods of various embodiments of the present invention.

[0086] Figure 6 is a structural block diagram of a device for determining a data table according to an embodiment of the present invention; Figure 6 Shown, including:

[0087] An acquisition module 62 is configured to acquire a development file in a target application, wherein the development file is used to indicate a construction rule for a data table, and the development file includes: a template file and a structured query language for a heterogeneous data dictionary;

[0088] The query module 64 is configured to receive a setting instruction for a target object, perform query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: information about the database to which the source system belongs;

[0089] The generating module 66 is configured to synchronize the standard data dictionary to the template file to generate a data table, and upload the data table to the target application to obtain a corresponding standardized table creation statement.

[0090] The device is used to obtain a development file in a target application, wherein the development file is used to indicate the construction rules of a data table, and the development file includes: a template file and a structured query language of a heterogeneous data dictionary; receive a setting instruction of a target object, perform query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: the database information of the source system; synchronize the standard data dictionary to the template file to generate a data table, and upload the data table to the target application to obtain a corresponding standardized table creation statement; that is, the target application parses the standard specification for establishing a data warehouse to obtain a template file corresponding to the standard specification and the structured query language of the heterogeneous data dictionary, and then processes the data table corresponding to the template file after the target object is processed by the target application, determines the standardized table creation statement corresponding to the data table, and establishes the data warehouse. The above technical solution solves the problems of being unable to quickly and accurately confirm the data table for establishing a data warehouse and determining the corresponding table creation statement through the data table, thereby realizing the rapid establishment of the data warehouse corresponding to the database of the source system, improving the efficient processing of data dictionary information, and making the data warehouse establishment specification realize the unification of standardized processes.

[0091] Optionally, the above template file can be a spreadsheet template or other types of templates. This is flexibly determined according to actual conditions, and this application does not impose too many restrictions on this.

[0092] In an exemplary embodiment, the above-mentioned query module is also used to upload the structured query language of the heterogeneous data dictionary to the source system of the corresponding database for data type mapping according to the database information of the source system carried by the setting instruction; summarize the data type information in the database, the data type information of the data warehouse to be established, and the results of the data type mapping to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

[0093] That is to say, when determining the standard data dictionary for establishing a data warehouse corresponding to the structured query language of the heterogeneous data dictionary, it is necessary to determine the database information of the source system for the data warehouse to be established, and then determine the corresponding data type in the data warehouse based on the data type corresponding to the database information to complete the data type mapping. Further, the data type information in the database, the data type information of the data warehouse to be established, and the results of the data type mapping are summarized to determine the standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

[0094] In an exemplary embodiment, the query module is further used to obtain a first data type of a structured query language of a heterogeneous data dictionary when stored in a database, and determine a first byte length of the first data type; determine a second byte length obtained by performing a logarithmic operation on the first byte length in a source system of the database, wherein the second byte length is used to indicate the length of the first byte length when stored in a data warehouse as a second data type; and determine a result of data type mapping of the structured query language of the heterogeneous data dictionary in the source system of the database based on the second byte length.

[0095] For example, after the data dictionary SQL (equivalent to the structured query language of the heterogeneous data dictionary in the embodiment of the present invention) exists on the development client, the source table's library and table name information are filled in according to the setting instructions issued by the target object, and the data dictionary SQL (Structured Query language, structured query language, referred to as SQL) is submitted to the source system for query. The data dictionary SQL will complete the standard data type mapping. For example, for the bit data type in the MySQL database, the data dictionary SQL will automatically perform logarithmic operations to calculate the data type length in the hive data warehouse, and the field order will be automatically sorted according to the field order of the source system.

[0096] In an exemplary embodiment, the above-mentioned generation module is also used to parse the standard data dictionary and determine the location coordinate information of the target field in the standard data dictionary, wherein the target field includes at least one of the following: the field name in the standard data dictionary, the data type in the standard data dictionary, and the field annotation in the standard data dictionary; and match the target field to the template file according to the location coordinate information to generate a data table.

[0097] It is understood that, in order to facilitate the filling of the standard data dictionary in the corresponding template file, the target fields in the standard data dictionary generated by data type mapping in the source system have specific location coordinates. These fields can then be matched with the default coordinate location information in the template file, allowing the standard data dictionary to be quickly filled into the template file to generate a data table. For example, the fields at the location coordinates in the data dictionary are copied to the corresponding coordinates in the template to complete the matching, thereby converting the standard data dictionary into a data table.

[0098] In an exemplary embodiment, the above-mentioned generation module is also used to upload the data table to the target application and determine the detection result of the target application on the data table, wherein the detection result is used to indicate the recognition result of the verification parameters configured in the target application on the content of the data table, and the verification parameters include at least one of the following: a list of reserved words of the data table, a naming rule and / or additional field information of the data table, a timestamp of the data table, a data type of the data table, a storage format of the data table, and a reference field of the data table; when the detection result indicates that the data table passes the target application detection, a standardized table creation statement for data table conversion issued by the target application is received.

[0099] That is, when the target application converts the data table into the corresponding standardized table creation statement for building the data warehouse, in order to make the converted standardized table creation statement better fit the current data warehouse, the data table is tested according to the verification parameters of the preset conditions of the data warehouse to be established.

[0100] Optionally, the validation parameters for the preset conditions include: a list of Hive reserved words, naming rules and additional field information in the data warehouse development specification, a conflict identification module for the timestamp data type and the parquet storage format, and an automatic quotation module for reserved words. The validation parameters are then used to determine whether the corresponding content structure in the data table complies with the template specification, convert the read content in the data table into standardized table creation statements, and use the standardized table creation statements to establish the data warehouse.

[0101] Optionally, when creating a data warehouse using a data table, if multiple data tables differ only in their database names, simply separate the database names with slashes. To create multiple data tables with different structures at once, simply expand the worksheet in the same Excel file.

[0102] In an exemplary embodiment, the above-mentioned device also includes: an execution module, which is used to submit the standardized table creation statement to the data warehouse for execution, and obtain the execution result of the standardized table creation statement executed by the data warehouse; when the execution result indicates that the target data warehouse corresponding to the standardized table creation statement has been successfully established, a prompt message is generated, wherein the prompt message is used to prompt that the target data warehouse corresponding to the target object data table has been successfully established.

[0103] In an exemplary embodiment, the above-mentioned device also includes: a change module, which is used to change the name of the template file in the development file according to the table name information when the setting instruction also includes table name information; when it is determined that the template file has completed the name change, the attribute information of the template file is selected according to the preset specification, wherein the attribute information includes at least one of the following: the database library name corresponding to the template file, the partition type of the template file in the database, the information of the current creator of the template file, and the table type corresponding to the template file.

[0104] An embodiment of the present invention further provides a storage medium, which includes a stored program, wherein the program executes any of the above methods when it is run.

[0105] Optionally, in this embodiment, the storage medium may be configured to store program codes for executing the following steps:

[0106] S1, obtaining a development file in a target application, wherein the development file is used to indicate a construction rule of a data table, and the development file includes: a template file, and a structured query language of a heterogeneous data dictionary;

[0107] S2, receiving a setting instruction of a target object, performing query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: information about the database to which the source system belongs;

[0108] S3, synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement.

[0109] An embodiment of the present invention further provides an electronic device, comprising a memory and a processor, wherein the memory stores a computer program, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.

[0110] Optionally, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor, and the input / output device is connected to the processor.

[0111] Optionally, in this embodiment, the processor may be configured to execute the following steps through a computer program:

[0112] S1, obtaining a development file in a target application, wherein the development file is used to indicate a construction rule of a data table, and the development file includes: a template file, and a structured query language of a heterogeneous data dictionary;

[0113] S2, receiving a setting instruction of a target object, performing query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: information about the database to which the source system belongs;

[0114] S3, synchronizing the standard data dictionary to the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement.

[0115] Optionally, in this embodiment, the above-mentioned storage medium may include but is not limited to: a USB flash drive, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk or an optical disk, and other media that can store program codes.

[0116] Optionally, specific examples in this embodiment may refer to the examples described in the above embodiments and optional implementation modes, and this embodiment will not be described in detail here.

[0117] Obviously, those skilled in the art will appreciate that the various modules or steps of the present invention described above can be implemented using a general-purpose computing device, centralized on a single computing device, or distributed across a network of multiple computing devices. Alternatively, they can be implemented using program code executable by a computing device, which can then be stored in a storage device and executed by the computing device. In some cases, the steps shown or described can be performed in a different order than that shown, or can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.

[0118] The foregoing description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Those skilled in the art will readily appreciate that various modifications and variations of the present invention are possible. Any modifications, equivalent substitutions, or improvements made within the principles of the present invention are intended to be within the scope of protection of the present invention.

Claims

1. A method for determining a data table, characterized in that: include: Acquire a development file in a target application, wherein the development file is used to indicate a construction rule for a data table, and the development file includes: a template file and a structured query language for a heterogeneous data dictionary; Receive a setting instruction for a target object, perform query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: information about the database to which the source system belongs; Synchronizing the standard data dictionary into the template file to generate a data table, and uploading the data table to the target application to obtain a corresponding standardized table creation statement; The receiving of the target object setting instruction, performing query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtaining a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, includes: Obtaining a first data type of the structured query language of the heterogeneous data dictionary when stored in a database, and determining a first byte length of the first data type; determining a second byte length obtained by performing a logarithmic operation on the first byte length in a source system of the database, wherein the second byte length is used to indicate the length of the first byte length when stored in a data warehouse as a second data type; and determining a result of data type mapping of the structured query language of the heterogeneous data dictionary in the source system of the database based on the second byte length; The data type information in the database, the data type information of the data warehouse to be established, and the result of the data type mapping are summarized to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

2. The method for determining a data table according to claim 1, wherein: Synchronizing the standard data dictionary into the template file to generate a data table includes: Parsing the standard data dictionary to determine location coordinate information of a target field in the standard data dictionary, wherein the target field includes at least one of the following: a field name in the standard data dictionary, a data type in the standard data dictionary, and a field annotation in the standard data dictionary; The target field is matched to the template file according to the position coordinate information to generate a data table.

3. The method for determining a data table according to claim 1, wherein: Upload the data table to the target application to obtain the corresponding standardized table creation statement, including: Uploading the data table to the target application, and determining a detection result of the target application on the data table, wherein the detection result is used to indicate a recognition result of the content in the data table by verification parameters configured in the target application, the verification parameters including at least one of the following: a reserved word list of the data table, a naming rule and / or additional field information of the data table, a timestamp of the data table, a data type of the data table, a storage format of the data table, and a reference field of the data table; When the detection result indicates that the data table passes the detection of the target application, a standardized table creation statement for the data table conversion issued by the target application is received.

4. The method for determining a data table according to claim 3, wherein: After receiving the standardized table creation statement for the data table conversion issued by the target application, the method further includes: Submitting the standardized table creation statement to the data warehouse for execution, and obtaining the execution result of the standardized table creation statement executed by the data warehouse; When the execution result indicates that the target data warehouse corresponding to the standardized table creation statement has been successfully created, a prompt message is generated, wherein the prompt message is used to prompt that the target data warehouse corresponding to the data table of the target object has been successfully created.

5. The method for determining a data table according to claim 1, wherein: After obtaining the development files in the target application, the method further includes: In a case where the setting instruction further includes table name information, changing the name of the template file in the development file according to the table name information; When it is determined that the template file has completed the name change, attribute information of the template file is selected according to preset specifications, wherein the attribute information includes at least one of the following: the database library name corresponding to the template file, the partition type of the template file in the database, the information of the current creator of the template file, and the table type corresponding to the template file.

6. A device for determining a data table, characterized in that: include: An acquisition module is used to acquire a development file in a target application, wherein the development file is used to indicate a construction rule of a data table, and the development file includes: a template file and a structured query language of a heterogeneous data dictionary; a query module, configured to receive a setting instruction for a target object, perform query processing on the structured query language of the heterogeneous data dictionary according to the setting instruction, and obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary, wherein the setting instruction includes: information about the database to which the source system belongs; A generation module, configured to synchronize the standard data dictionary to the template file to generate a data table, and upload the data table to a target application to obtain a corresponding standardized table creation statement; The query module is further used to obtain a first data type of the structured query language of the heterogeneous data dictionary when stored in a database, and determine a first byte length of the first data type; determine a second byte length obtained by performing a logarithmic operation on the first byte length in the source system of the database, wherein the second byte length is used to indicate the length of the first byte length when stored in the data warehouse as a second data type; determine a result of data type mapping of the structured query language of the heterogeneous data dictionary in the source system of the database based on the second byte length; and summarize the data type information in the database, the data type information of the data warehouse to be established, and the result of the data type mapping to obtain a standard data dictionary corresponding to the structured query language of the heterogeneous data dictionary.

7. A computer-readable storage medium, characterized in that: The computer-readable storage medium includes a stored program, wherein the program executes the method described in any one of claims 1 to 5 when executed.

8. An electronic device comprising a memory and a processor, characterized in that: A computer program is stored in the memory, and the processor is configured to execute the method according to any one of claims 1 to 5 through the computer program.

Images