Flatness detection method and device

Abstract

The application discloses a kind of flat detection method, device, method includes: timing trigger flat detection, for each processing instance, the actual table structure of each processing instance corresponding expected field information and target Hive table is acquired, wherein expected field information defines the field that target Hive table should contain and processing logic;The actual table structure of each processing instance corresponding expected field information and target Hive table is compared, and comparison result is obtained;According to comparison result, corresponding table structure change operation is executed;After table structure change operation succeeds, based on the SQL template of preestablished, expected field information and the calculation engine type specified by processing instance, processing SQL sentence is generated by replacing placeholder in SQL template;Processing SQL sentence is deployed to scheduling platform, to create or update corresponding data processing task.The application improves flat detection efficiency.

Description

Technical Field

[0001] This application relates to the field of computer technology, and more specifically, to a flattening detection method and apparatus. Background Technology

[0002] In the field of big data analytics, Hive data warehouses are widely used to store and analyze massive amounts of business data. Due to the complexity and variability of business data, many Hive tables contain JSON-formatted fields to store semi-structured extended information, such as user behavior logs, business event details, and configuration parameters. While these JSON fields provide flexible data storage capabilities, in real-world data analysis scenarios, it is often necessary to access specific key-value pairs within the JSON for filtering, aggregation, and calculation.

[0003] Currently, the main method for flattening detection is manual flattening, which involves data developers manually writing ETL tasks to extract the key-value pairs that need to be analyzed from the JSON fields in advance and write them into newly created independent fields to form the flattened target table. When the business needs to analyze new JSON key-value pairs, the developers need to manually modify the ETL code, adjust the table structure, and re-execute the data processing task.

[0004] However, the above methods suffer from high labor costs. Summary of the Invention

[0005] The main purpose of this application is to provide a flatness detection method and apparatus that can improve the efficiency of flatness detection.

[0006] To achieve the above objectives, firstly, this application provides a method for flattening detection, comprising: The flattening detection is triggered periodically. For each processing instance, the expected field information and the actual table structure of the target Hive table are obtained. The expected field information defines the fields that the target Hive table should contain and the processing logic. The expected field information corresponding to each processing instance is compared with the actual table structure of the target Hive table to obtain the comparison results; Based on the comparison results, perform the corresponding table structure change operations; After the table structure change operation is successful, based on the preset SQL template, expected field information and the calculation engine type specified by the processing instance, the processing SQL statement is generated by replacing the placeholders in the SQL template. Deploy the SQL statements to the scheduling platform to create or update the corresponding data processing tasks.

[0007] In one embodiment, a leveling-out detection is triggered periodically. For each processing instance, the expected field information corresponding to each processing instance and the actual table structure of the target Hive table are obtained, including: Query all processing instances whose status is enabled; For each processing instance across all processing instances, read the desired field information associated with each processing instance from the field configuration table. Verify whether the expected field information is empty; If not empty, call the Hive metadata service interface to obtain the actual table structure of the target Hive table.

[0008] In one embodiment, the expected field information corresponding to each processing instance is compared with the actual table structure of the target Hive table to obtain the comparison result, including: The field definitions in the expected field information are compared with the field definitions in the actual table structure to obtain the comparison results. The field definitions include at least the field name and the field data type.

[0009] In one embodiment, the comparison result includes at least one of the following situations: The target Hive table does not exist; The expected field information should match the actual table structure; The expected field information includes new fields that are not present in the actual table structure; At least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

[0010] In one embodiment, based on the comparison results, a corresponding table structure change operation is performed, including: When the comparison result indicates that the target Hive table does not exist, generate and execute the table creation DDL statement based on the expected field information. When the comparison result indicates that there are new fields in the expected field information that are not present in the actual table structure, a modification table is generated to add the new fields and DDL statements are executed.

[0011] In one embodiment, the method further includes: An alarm is triggered when the comparison result shows that at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

[0012] In one embodiment, based on a preset SQL template, desired field information, and the computing engine type specified by the processing instance, a processing SQL statement is generated by replacing placeholders in the SQL template, including: Retrieve a preset SQL template associated with each processing instance, wherein the preset SQL template contains at least one placeholder; Based on the expected field information, the calculation engine type, and the custom configuration of the processing instance, construct the mapping relationship between placeholders and the SQL fragments to be replaced; Iterate through the placeholders in the preset SQL template, perform string replacement according to the mapping relationship, and generate the processed SQL statement.

[0013] In one embodiment, a mapping relationship between placeholders and SQL fragments to be replaced is constructed based on desired field information, computing engine type, and custom configuration of the processing instance, including: Determine the type of each placeholder in the preset SQL template; Based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, calculation engine type, or custom configuration of the processing instance, and a mapping relationship between the placeholder and the SQL fragment to be replaced is established.

[0014] In one embodiment, the placeholder type includes at least one of the following: System-wide placeholders used to generate a list of query fields when generating processing SQL statements; JSON parsing placeholders are used to generate appropriate JSON parsing statements based on the computing engine type when generating processing SQL statements; Custom placeholders are used to replace custom values ​​that the user has pre-configured in the processing instance when generating processing SQL statements.

[0015] In one embodiment, based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the calculation engine type, or the custom configuration of the processing instance, including: When the placeholder is a system-wide placeholder, read the field definitions and processing logic of all fields to be leveled from the expected field information; Based on the field definitions and processing logic of all fields to be flattened, a field selection list is generated, and the field selection list is used as the SQL fragment to be replaced corresponding to the system's general placeholder. The field selection list is a list of SELECT clause fields formed by concatenating the processing logic of each field according to the preset field sorting value.

[0016] In one embodiment, based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the calculation engine type, or the custom configuration of the processing instance, including: When the placeholder is a JSON parsing placeholder, obtain the configuration parameters of the JSON parsing placeholder. The configuration parameters include at least the source field name where the JSON data is located, the JSON path, and the list of key-value pairs to be extracted. Based on the computing engine type specified in the processing instance, the corresponding JSON parsing statement generation strategy is invoked to generate a JSON parsing statement adapted to the computing engine type, which is then used as the SQL fragment to be replaced corresponding to the JSON parsing placeholder.

[0017] In one embodiment, based on the computing engine type specified by the processing instance, the corresponding JSON parsing statement generation strategy is invoked to generate a JSON parsing statement adapted to the computing engine type, including: If the computation engine type is the first engine type, then the first parsing statement based on the json_parse function and the row type is generated; If the calculation engine type is the second engine type, then a second parsing statement based on the json_tuple function and the lateral view is generated.

[0018] In one embodiment, based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the calculation engine type, or the custom configuration of the processing instance, including: When the placeholder is a custom placeholder, the user-preset value corresponding to the custom placeholder is read from the custom configuration of the processing instance and used as the SQL fragment to be replaced.

[0019] In one embodiment, string replacement is performed based on the mapping relationship to generate a processing SQL statement, including: Find the corresponding SQL fragment to be replaced from the mapping relationship; Replace the placeholders with the SQL fragments to be replaced to generate the processed SQL statements.

[0020] Secondly, embodiments of this application provide a flatness detection device, including: The data acquisition module is used to trigger leveling detection on a regular basis. For each processing instance, it acquires the expected field information and the actual table structure of the target Hive table. The expected field information defines the fields that the target Hive table should contain and the processing logic. The data comparison module is used to compare the expected field information corresponding to each processing instance with the actual table structure of the target Hive table and obtain the comparison results. The operation module is used to perform corresponding table structure change operations based on the comparison results; The SQL statement generation module is used to generate processing SQL statements after a table structure change operation is successful, based on a preset SQL template, expected field information, and the calculation engine type specified by the processing instance, by replacing placeholders in the SQL template. The deployment module is used to deploy the processing SQL statements to the scheduling platform to create or update the corresponding data processing tasks.

[0021] Thirdly, embodiments of this application provide a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of any of the methods described above.

[0022] Fourthly, embodiments of this application provide a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of any of the methods described above.

[0023] Fifthly, embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, implements the steps of any of the methods described above.

[0024] This application provides a method and apparatus for leveling out, comprising: periodically triggering leveling out; for each processing instance, obtaining the expected field information corresponding to each processing instance and the actual table structure of the target Hive table, wherein the expected field information defines the fields and processing logic that the target Hive table should contain; then comparing the expected field information corresponding to each processing instance with the actual table structure of the target Hive table to obtain the comparison result; and then performing a corresponding table structure change operation based on the comparison result. After the table structure change operation is successful, based on a preset SQL template, the expected field information, and the computing engine type specified by the processing instance, generating a processing SQL statement by replacing the placeholders in the SQL template, thereby deploying the processing SQL statement to the scheduling platform to create or update the corresponding data processing task. This application automatically detects changes in the Hive table structure through a periodically triggered mechanism, accurately compares the expected field information with the actual table structure, and automatically executes the corresponding table structure change operation based on the comparison result. This achieves complete automation from table structure detection to change, completely eliminating the cumbersome process of relying on manual monitoring and manual modification of the table structure in traditional solutions, significantly reducing the operational burden and error probability of data development personnel, and improving the efficiency of leveling out. Attached Figure Description

[0025] The accompanying drawings, which form part of this application, are used to provide a further understanding of the application and to make other features, objects, and advantages of the application more apparent. The illustrative embodiments and descriptions of this application are used to explain the application and do not constitute an undue limitation of the application. In the drawings: Figure 1 This is an overall architecture diagram of a JSON automatic flattening system provided in an embodiment of this application; Figure 2 This is a schematic diagram of the structure of an application service layer provided in an embodiment of this application; Figure 3This is a schematic flowchart of a flattening detection method provided in an embodiment of this application; Figure 4 This is a flowchart illustrating a method for determining a structure table according to an embodiment of this application; Figure 5 This is a schematic diagram of a process for determining the execution result of a DDL operation, provided in an embodiment of this application. Figure 6 This is a schematic diagram of the structure of a flattening detection device provided in an embodiment of this application; Figure 7 This is a schematic diagram of the computer device provided in the embodiments of this application. Detailed Implementation

[0026] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0027] The terms "first," "second," "third," "fourth," etc. (if present) in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein.

[0028] In the embodiments of this application, the terms "module" or "unit" refer to a computer program or part of a computer program that has a predetermined function and works with other related parts to achieve a predetermined goal, and can be implemented wholly or partially using software, hardware (such as processing circuitry or memory), or a combination thereof. Similarly, a processor (or multiple processors or memory) can be used to implement one or more modules or units. Furthermore, each module or unit can be part of an overall module or unit that includes the functionality of that module or unit.

[0029] It should be understood that in the various embodiments of this application, the sequence number of each process does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0030] It should be understood that in this application, "comprising" and "having" and any variations thereof are intended to cover non-exclusive inclusion, 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 that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such process, method, product or device.

[0031] It should be understood that in this application, "multiple" refers to two or more. "And / or" is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, "and / or B" can represent: A alone, A and B simultaneously, or B alone. The character " / " generally indicates that the preceding and following related objects are in an "or" relationship. "Contains A, B, and C", "Contains A, B, and C" means that all three A, B, and C are contained; "Contains A, B, or C" means that one of A, B, and C is contained; "Contains A, B, and / or C" means that any one, two, or three of A, B, and C are contained.

[0032] It should be understood that in this application, "B corresponding to A", "B corresponding to A", "A corresponds to B", or "B corresponds to A" means that B is associated with A, and B can be determined based on A. Determining B based on A does not mean determining B solely based on A; B can also be determined based on A and / or other information. Matching A and B is defined as a similarity between A and B that is greater than or equal to a preset threshold.

[0033] Depending on the context, "if" as used here can be interpreted as "when," "when," "in response to determination," or "in response to detection."

[0034] The data involved in this application may be data authorized by the tester or fully authorized by all parties. The collection, dissemination, and use of the data shall comply with the relevant laws, regulations and standards of the relevant countries and regions. The implementation methods / executives of this application may be combined with each other.

[0035] The technical solutions of this application will be described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments.

[0036] To facilitate understanding of this application, the following technical terms are explained: Hive: A data warehouse tool based on Hadoop, used for storing and querying large-scale structured data.

[0037] JSON (JavaScript Object Notation): A lightweight data interchange format often used to store semi-structured data.

[0038] JSON flattening: The process of expanding a nested JSON data structure into flattened, independent fields.

[0039] SQL templates: Predefined SQL statement frames that include replaceable placeholders.

[0040] Placeholders: Special markers in SQL templates that will be replaced with specific values ​​or logic during actual execution.

[0041] Presto: A distributed SQL query engine that supports interactive queries on large-scale data.

[0042] Kyuubi: A unified multi-tenant SQL gateway service based on Spark.

[0043] DDL (Data Definition Language): SQL statements used to define the structure of a database, such as creating tables and modifying table structures.

[0044] Derived table: The target data table generated after data processing.

[0045] Honghu: A big data task scheduling platform used to manage and execute data processing tasks.

[0046] The json_tuple function: A function in Hive / Spark used to parse JSON strings and extract multiple key-value pairs.

[0047] The json_parse function: A function in Presto used to parse a JSON string into a JSON object.

[0048] Lateral view: A side view in Hive / Spark used to expand a row of data into multiple rows or columns.

[0049] The present application will now be described in conjunction with the accompanying drawings and specific embodiments.

[0050] In the field of big data analytics, Hive data warehouses are widely used to store and analyze massive amounts of business data. Due to the complexity and variability of business data, many Hive tables contain JSON-formatted fields to store semi-structured extended information, such as user behavior logs, business event details, and configuration parameters. While these JSON fields provide flexible data storage capabilities, in practical data analysis scenarios, it is often necessary to access specific key-value pairs within the JSON for filtering, aggregation, and calculation. With the explosive growth of data volume and the increasingly refined needs of business analysis, how to efficiently and cost-effectively extract structured data from JSON fields has become a key issue affecting data query performance and development efficiency.

[0051] Currently, two main technical solutions are used for data processing of JSON fields in Hive tables. Solution 1 is the real-time parsing solution, which parses specific key-value pairs from the JSON string in real time using SQL functions during each query execution. For example, it uses Hive's `get_json_object` function or Presto's `json_extract_scalar` function to directly extract the required data from the JSON field. Solution 2 is the manual scaling solution, where data developers manually write ETL tasks to pre-extract the key-value pairs to be analyzed from the JSON field and write them into a newly created independent field, forming the scaled target table. When the business needs to analyze new JSON key-value pairs, the developers need to manually modify the ETL code, adjust the table structure, and re-execute the data processing task.

[0052] Existing technologies have significant drawbacks in practical applications. For real-time parsing solutions, each query requires real-time parsing of the JSON string. When the data volume reaches hundreds of millions, the JSON parsing function consumes a large amount of computing resources, causing query response time to deteriorate from seconds to minutes or even longer. At the same time, the key-value pairs within JSON fields cannot be indexed, making filtering, sorting, and aggregation operations based on JSON internal key-value pairs extremely inefficient. Furthermore, different computing engines (such as Presto, Hive, and Spark) use different JSON parsing functions and syntaxes, requiring developers to memorize multiple sets of function usages, increasing development complexity and the probability of errors. For manual data leveling solutions, whenever the business needs to analyze new JSON key-value pairs, data developers need to manually complete a series of tasks, including modifying the Hive table structure, modifying the ETL processing SQL code, testing and deploying new tasks, and backtracking historical data. The entire process usually takes 1-3 working days, which seriously affects the timeliness of business data analysis. At the same time, different data tables and different developers use their own leveling methods, lacking unified management and standards, making the code difficult to maintain. More importantly, when the upstream data source adds new JSON key-value pairs, the system cannot automatically detect and trigger the leveling process, relying entirely on manual monitoring and intervention, which can easily lead to data omissions and analysis delays.

[0053] To address the aforementioned issues, this application provides a method for flattening detection.

[0054] Please see Figure 1 , Figure 1 An overall architecture diagram of a JSON automatic flattening system provided for embodiments of this application includes: like Figure 1As shown, this invention adopts a three-tier architecture design that separates the front-end and back-end. It is divided into four layers: a visual management platform (front-end layer), a back-end core service layer, a data storage layer, and an external system interaction layer. Each layer communicates with the other through HTTPS and REST API, forming a complete closed-loop processing system.

[0055] The visual management platform, developed based on the Vue.js framework, serves as the user interaction entry point, providing functional interfaces for template management, processing instance management, Hive table field configuration, task monitoring, and log querying. The template management page allows users to edit SQL templates online and configure placeholders using the Monaco Editor component. The processing instance page manages specific leveling task instances and their status changes. The field configuration page provides visual configuration capabilities for Hive table fields, including field names, data types, field processing logic, and JSON extraction keys. Each field's processing logic can be configured individually: if the processing logic is empty, the field name is used directly as the SELECT expression; if extraction from JSON is required, the configuration format is "JSON alias.key name", such as "jtr_variables.userName"; if type conversion is needed, a complete conversion expression can be configured, such as "CAST(jtr_variables.age AS BIGINT)". The task monitoring and log query pages are used to view the execution status and historical records of leveling tasks.

[0056] The backend core service layer, developed based on the Java Spring Boot framework, is the central hub of the system's business logic. This layer is further divided into a controller layer and a core business layer. The controller layer includes TemplateController (responsible for template maintenance and placeholder configuration), ProcessController (responsible for instance management and state changes), and HiveFieldController (responsible for field configuration and JSON extraction key management). These controllers receive REST API requests from the frontend and perform initial processing. The core business layer uses JsonFlatCoreService as its core engine, undertaking key responsibilities such as periodic monitoring scans, SQL template generation and parsing, table structure comparison and DDL generation, and task scheduling and interactive state synchronization. This layer also includes several specialized sub-services: TemplateSvc handles template-related logic, ProcessSvc manages processing instances, HiveFieldSvc maintains field configurations, NotificationSvc handles notification sending, RecallSvc handles data callbacks, and TaskRecordSvc records task execution status. These sub-services work together to support the operation of the core engine.

[0057] The data storage layer uses a relational database (such as MySQL) to store various configuration data and operation records of the system, including template configuration tables, placeholder configuration tables, processing instance tables, Hive field configuration tables, task record tables, and backtracking record tables. These six core data tables correspond to various persistent information required for system operation, providing data support for upper-layer business logic.

[0058] The external system interaction layer is responsible for interfacing with surrounding systems within the big data ecosystem. On one hand, the system interacts with scheduling platforms (such as Honghu) to achieve functions such as creating, executing, and synchronizing processing tasks. On the other hand, the system interacts with Hive metadata services to obtain the table structure information of the target Hive table in real time, providing a data foundation for table structure comparison. In addition, the system also integrates notification channels such as SMTP email service, DingTalk, and HiChat to send instant messages or email notifications to relevant personnel when a task is completed or encounters an anomaly.

[0059] like Figure 2 As shown, the application service layer is further subdivided into the controller layer and the core business layer. The two have a clear division of labor and work together to complete the system's business processing logic.

[0060] The controller layer sits at the outermost layer of the application service layer, responsible for exposing REST API interfaces for frontend calls. Specifically, TemplateController provides interfaces related to template maintenance and placeholder configuration, supporting CRUD operations on templates and placeholder binding management; ProcessController manages the lifecycle of processing instances, including instance creation, startup, shutdown, status queries, and manual triggering of leveling operations; and HiveFieldController focuses on the configuration management of Hive table fields, providing interfaces for maintaining field information and supporting the configuration of JSON extraction keys. After receiving frontend requests, these controllers perform preliminary validation and parsing of request parameters, and then call the corresponding service interfaces of the core business layer for specific processing.

[0061] The core business layer uses JsonFlatCoreService as its core engine, encapsulating the key business processes of this invention. The scheduled monitoring cycle scanning function automatically triggers the leveling detection process at preset time intervals (e.g., every 5 minutes); the SQL template generation and parsing function dynamically generates suitable processing SQL statements based on placeholder types and calculation engine configurations; the table structure comparison DDL generation function compares expected field information with the actual table structure and generates corresponding table creation or modification DDL statements based on the comparison results; and the task scheduling interaction status synchronization function is responsible for interfacing with external scheduling platforms, submitting DDL tasks and processing tasks, and synchronizing the execution status of tasks.

[0062] Around the core engine, the core business layer also deploys several specialized sub-services: TemplateSvc is responsible for reading and parsing SQL templates; ProcessSvc manages the configuration information and running status of processing instances; HiveFieldSvc maintains the read and write operations of the field configuration table; NotificationSvc triggers notifications at key nodes in the process (such as task completion and exception alarms); RecallSvc handles historical data backtracking tasks required due to table structure changes; and TaskRecordSvc records task information for each interaction with the scheduling platform, including task type, scheduling platform task ID, execution status, and executed SQL content, providing data support for task tracing and problem troubleshooting.

[0063] The controller layer and the core business layer are decoupled using dependency injection. The controller completes business processing by calling service interfaces without needing to concern itself with the specific implementation details. This layered design gives the system good scalability and maintainability, with clear responsibilities for each module, facilitating subsequent feature enhancements and iterative optimizations.

[0064] Please see Figure 3 , Figure 3 This is a schematic flowchart illustrating a flattening detection method provided in an embodiment of this application. Figure 3 As shown, it includes the following steps: Step S301: Trigger the leveling detection periodically. For each processing instance, obtain the expected field information and the actual table structure of the target Hive table for each processing instance.

[0065] The expected field information defines the fields and processing logic that the target Hive table should contain, and is stored in the system's field configuration table.

[0066] In one embodiment, a leveling-out detection is triggered periodically. For each processing instance, the expected field information corresponding to each processing instance and the actual table structure of the target Hive table are obtained, including: querying all processing instances that are enabled; for each processing instance among all processing instances, reading the expected field information associated with each processing instance from the field configuration table and verifying whether the expected field information is empty; if it is not empty, calling the Hive metadata service interface to obtain the actual table structure of the target Hive table.

[0067] Specifically, a pre-configured scheduled task is used to periodically trigger the leveling detection process at fixed time intervals (e.g., every 5 minutes). When the scheduled task is triggered, the process first queries the processing instance table for a list of all processing instances that are in an enabled state, which serves as the candidate set of instances to be detected this time.

[0068] For each processing instance in the candidate instance set, perform the following processing: First, all expected field information associated with this processing instance is read from the field configuration table (json_flat_hive_fields). The field configuration table records the complete field definitions that the target Hive table should contain. Each record includes: the name of the Hive table (in the format of database name.table name), the field name, the Chinese description of the field, the field type (0 indicates a regular field, 1 indicates a partition field), the field data type (such as string, bigint, etc.), the field sorting value (used to control the order of the field in the table), and the field processing logic (specifying how to generate the value of the field from the source data, such as directly referencing the source field or extracting a specific key value from JSON).

[0069] After obtaining the expected field information corresponding to the processing instance, the system first checks whether the expected field information is empty. If it is empty, it means that the processing instance has not been configured with any leveling field, and the system skips the current instance and continues to process the next processing instance.

[0070] If the desired field information is not empty, the Hive metadata service interface is called to query the actual table structure of the target Hive table. The target Hive table is uniquely identified by the database name and table name specified in the processing instance configuration. The information returned by the Hive metadata service includes: whether the table exists, the actual list of fields contained in the table, the name and data type of each field, etc.

[0071] Through the above steps, two types of information are obtained for each enabled processing instance: one is the expected table structure defined by the field configuration table (including field definitions and processing logic), and the other is the actual table structure obtained from the Hive metadata service.

[0072] Step S302: Compare the expected field information corresponding to each processing instance with the actual table structure of the target Hive table to obtain the comparison results.

[0073] In one embodiment, the expected field information corresponding to each processing instance is compared with the actual table structure of the target Hive table to obtain the comparison result, including: comparing the field definition in the expected field information with the field definition in the actual table structure to obtain the comparison result, wherein the field definition includes at least the field name and the field data type.

[0074] The comparison results include at least one of the following situations: the target Hive table does not exist; the expected field information is consistent with the actual table structure; the expected field information contains newly added fields that are not present in the actual table structure; at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

[0075] Specifically, after obtaining the actual table structure of the target Hive table, the first step is to determine whether the table exists. If the Hive metadata service returns a result indicating that the table does not exist, the comparison result is directly determined to be "the target Hive table does not exist," and the table creation process will be triggered subsequently.

[0076] If the target Hive table already exists, the system extracts the set of field definitions from the actual table structure. Each field definition must include at least the field name and data type. Simultaneously, the system extracts the field definitions (also including field name and data type) of all fields to be balanced from the expected field information. The system then compares the expected field set with the actual field set item by item.

[0077] During the comparison process, each field in the expected field list is iterated over to check if a field with the same name and data type exists in the actual table. Based on the comparison results, one or a combination of the following scenarios are identified: If every field in the expected field information can be found in the actual table, and the field names and data types are completely consistent, then the comparison result is that the expected field information is consistent with the actual table structure, indicating that the current table structure meets expectations and no leveling operation is required.

[0078] If some fields in the expected field information cannot be found in the actual table (i.e., the field names do not appear in the actual field list), the comparison result will show that there are new fields in the expected field information that are not present in the actual table structure. The details of these new fields will be recorded, and the process of adding fields will be prepared.

[0079] If a field in the expected field information has the same name as a field in the actual table, but the data types do not match (e.g., expected to be BIGINT, but actually STRING), the comparison result will show that at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure. This situation will be marked as a field conflict, and an alarm handling process will be triggered to notify relevant personnel to intervene and resolve the issue.

[0080] The above three scenarios may occur simultaneously, such as when there is both a newly added field and a field with a conflicting data type. The system will record all differences and drive the subsequent leveling-out actions according to the preset priority (such as handling data type conflict alarms first, and then handling the addition of new fields) or the parallel processing mechanism.

[0081] Step S303: Based on the comparison results, perform the corresponding table structure change operation.

[0082] In one embodiment, based on the comparison result, the corresponding table structure change operation is performed, including: when the comparison result indicates that the target Hive table does not exist, generating and executing table creation DDL statements based on the expected field information; when the comparison result indicates that the expected field information contains new fields that are not present in the actual table structure, generating a modification table for adding the new fields and executing DDL statements.

[0083] In one embodiment, the method further includes triggering an alarm process when the comparison result shows that at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

[0084] Specifically, based on the different scenarios identified by the comparison results, differentiated table structure change operations are triggered to ensure that the actual structure of the target Hive table is ultimately completely consistent with the expected field information.

[0085] If the comparison result indicates that the target Hive table does not exist, the table creation process begins. First, a complete set of field definitions is extracted from the expected field information, including the field names, data types, and sorting values ​​of all ordinary and partition fields. Based on this information, a `CREATE TABLE` table creation DDL statement is generated in sorted order, fully defining the table structure of the target Hive table. Subsequently, the generated table creation DDL statement is submitted to the big data task scheduling platform for execution, and the system polls for completion. If the DDL execution is successful, the operation is recorded, and the process proceeds to the subsequent SQL generation and task creation stages; if execution fails, the reason for the failure is recorded, and an alarm notification is triggered.

[0086] If the comparison results indicate the existence of new fields in the expected field information that are not present in the actual table structure, the field addition process begins. First, a list of all new fields is identified; these fields exist in the expected field information but not in the current Hive table. To ensure data consistency, it is checked whether any data processing tasks associated with this processing instance are currently running or waiting. If so, these running tasks are stopped, and the data time reference point for backtracking is recorded. Then, an ALTER TABLE ADD COLUMN statement is generated based on the definition of the new field. This statement only contains the definition of the new field. The DDL statement is submitted to the scheduling platform for execution, and execution is awaited. After the DDL field addition is successful, the processing SQL statement is regenerated, and the corresponding processing task configuration on the scheduling platform is updated. Simultaneously, based on the previously recorded task stop time point, a historical data backtracking task is triggered to reprocess the data during the stop period, ensuring that the data for the new field is completely filled.

[0087] If the comparison result shows that at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure, the alarm handling process is initiated. All fields with data type conflicts are identified, and the expected and actual data types of each conflicting field are recorded. Subsequently, an alarm notification is sent to the task manager via instant message or email, informing them of the detailed information of the conflicting field and indicating that manual intervention is required. Table structure change operations will not be automatically executed to avoid data corruption or loss of precision due to forced data type conversion. After manual processing is completed, operations personnel can manually trigger a re-comparison or directly repair the table structure.

[0088] Step S304: After the table structure change operation is successful, based on the preset SQL template, expected field information and the calculation engine type specified by the processing instance, the processing SQL statement is generated by replacing the placeholders in the SQL template.

[0089] In one embodiment, based on a preset SQL template, expected field information, and the computing engine type specified by the processing instance, a processing SQL statement is generated by replacing placeholders in the SQL template. This includes: obtaining a preset SQL template associated with each processing instance, wherein the preset SQL template contains at least one placeholder; constructing a mapping relationship between placeholders and SQL fragments to be replaced according to the expected field information, computing engine type, and custom configuration of the processing instance; traversing the placeholders in the preset SQL template, performing string replacement according to the mapping relationship, and generating the processing SQL statement.

[0090] The process involves constructing a mapping relationship between placeholders and SQL fragments to be replaced based on the expected field information, the calculation engine type, and the custom configuration of the processing instance. This includes: determining the type of each placeholder in the preset SQL template; obtaining the corresponding SQL fragment to be replaced from the expected field information, the calculation engine type, or the custom configuration of the processing instance based on the type of the placeholder, and establishing a mapping relationship between the placeholder and the SQL fragment to be replaced.

[0091] The placeholders include at least one of the following types: system-wide placeholders, used to generate a list of query fields when generating processing SQL statements; JSON parsing placeholders, used to generate an adapted JSON parsing statement based on the computing engine type when generating processing SQL statements; and custom placeholders, used to replace custom values ​​pre-configured by the user in the processing instance when generating processing SQL statements.

[0092] In one embodiment, based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the calculation engine type, or the custom configuration of the processing instance. This includes: when the placeholder is a system general placeholder, reading the field definitions and processing logic of all fields to be flattened from the expected field information; generating a field selection list based on the field definitions and processing logic of all fields to be flattened, and using the field selection list as the SQL fragment to be replaced corresponding to the system general placeholder. The field selection list is a list of SELECT clause fields formed by sequentially concatenating the processing logic of each field according to a preset field sorting value.

[0093] In another embodiment, based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the computing engine type, or the custom configuration of the processing instance. This includes: when the placeholder is a JSON parsing placeholder, obtaining the configuration parameters of the JSON parsing placeholder, wherein the configuration parameters include at least the source field name where the JSON data is located, the JSON path, and the list of key-value pairs to be extracted; and based on the computing engine type specified by the processing instance, calling the corresponding JSON parsing statement generation strategy to generate a JSON parsing statement adapted to the computing engine type, and using it as the SQL fragment to be replaced corresponding to the JSON parsing placeholder.

[0094] Specifically, based on the computing engine type specified in the processing instance, the corresponding JSON parsing statement generation strategy is invoked to generate a JSON parsing statement adapted to the computing engine type. This includes: if the computing engine type is the first engine type, then a first parsing statement based on the json_parse function and the row type is generated; if the computing engine type is the second engine type, then a second parsing statement based on the json_tuple function and the lateral view is generated.

[0095] In another embodiment, depending on the type of the placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the calculation engine type, or the custom configuration of the processing instance, including: when the placeholder is a custom placeholder, reading the user preset value corresponding to the custom placeholder from the custom configuration of the processing instance as the SQL fragment to be replaced.

[0096] The process of performing string replacement based on the mapping relationship to generate a processing SQL statement includes: finding the corresponding SQL fragment to be replaced from the mapping relationship; replacing the placeholder with the SQL fragment to be replaced; and generating the processing SQL statement.

[0097] Specifically, first, according to the configuration information of the processing instance, obtain the preset SQL template associated with this instance. This SQL template is pre-stored in the template configuration table and contains at least one placeholder, which is marked in a specific format (such as <system_insert_logic / >, <system_json_logic json_as="alias" / >, etc.), and is used to dynamically replace with specific SQL fragments when generating the final SQL. The applicable calculation engine type, such as Presto engine or Kyuubi engine, is also recorded in the template to ensure that the subsequent generated parsing statements are compatible with the engine syntax.

[0098] For the two mainstream calculation engines, Presto and Kyuubi, a unified adaptation mechanism has been implemented for the JSON parsing syntax differences of different engines: (1) Presto engine adaptation The Presto engine uses the `json_parse` function to parse JSON strings and uses the `row` type for structured access. The generated SQL statement format is: ```sql SELECT alias.key1 as field_1, alias.key2 as field_2 FROM source_table CROSS JOIN UNNEST(CAST(json_parse(JSON_field) AS ARRAY(ROW(key1 VARCHAR, key2 VARCHAR)))) AS t(alias) ``` Or for a single JSON record: ```sql SELECT alias.key1 as field_1, alias.key2 as field_2 FROM source_table CROSS JOIN (SELECT CAST(json_parse(JSON_field) AS ROW(key1 VARCHAR, key2 VARCHAR)) AS alias) ``` (2) Kyuubi / Spark engine adaptation The Kyuubi engine uses the `json_tuple` function in combination with the `lateral view` side view to implement JSON parsing. The generated SQL statement format is: SQL SELECT Alias.key1 as field1, Alias.key2 as field2 FROM source table LATERAL VIEW json_tuple(JSON field, 'key1', 'key2') alias AS key1, key2 ``` For nested JSON, first use the `get_json_object` function to retrieve the sub-JSON at the specified path, and then call `json_tuple` to parse it: SQL LATERAL VIEW json_tuple(get_json_object(JSON field, '$.nested path'), 'key1', 'key2') alias AS key1, key2.

[0099] After obtaining the SQL template, the system proceeds to the placeholder parsing and mapping relationship construction phase. First, all placeholders contained in the template are identified, and their types are determined based on the placeholder naming rules and configuration attributes. Placeholder types include three categories: system-wide placeholders, JSON parsing placeholders, and custom placeholders. For each type of placeholder, the system employs corresponding processing strategies to retrieve the SQL fragments to be replaced from different data sources. For system-wide placeholders (such as...)<system_insert_logic / > The system reads the field definitions and processing logic of all fields to be leveled associated with the processing instance from the expected field information. Field definitions include field name, data type, and field sorting value. The processing logic specifies how the field value is generated from the source data (e.g., directly referencing the source field, extracting a specific key-value pair from JSON, or undergoing type conversion). Each field is iterated through sequentially according to its sorting value, and concatenated into a complete field selection list based on its processing logic. If the field processing logic is empty, the field name is used directly as the SELECT expression; if the processing logic is configured in the format "JSON alias.key name", it is concatenated into a reference of that format; if it is a complete type conversion expression, it is used directly. After all fields are concatenated, a complete list of SELECT clause fields is obtained, serving as a system-wide placeholder for the SQL fragment to be replaced.

[0100] For JSON parsing placeholders (such as <system_json_logic json_as="alias" / >), first parse the configuration parameters in the placeholder, including the source field name where the JSON data is located, the JSON path (used to access nested JSON), and the list of key-value pairs to be extracted. Subsequently, according to the type of calculation engine specified by the processing instance, call the corresponding JSON parsing statement generation strategy. If the calculation engine is Presto (the first engine type), generate the first parsing statement based on the json_parse function and the row type, such as "CROSS JOIN UNNEST(CAST(json_parse(source field) AS ARRAY(ROW(key1 VARCHAR, key2 VARCHAR)))) AS t(alias)" or in the case of a single-record scenario, "CROSS JOIN (SELECT CAST(json_parse(source field) AS ROW(key1 VARCHAR, key2 VARCHAR)) AS alias)". If the calculation engine is Kyuubi (the second engine type), generate the second parsing statement based on the json_tuple function and the lateral view, such as "LATERAL VIEW json_tuple(source field, 'key1', 'key2') alias AS key1, key2". For nested JSON scenarios, first use the get_json_object function to obtain the sub-JSON and then call json_tuple. The generated complete JSON parsing statement serves as the SQL fragment to be replaced for this placeholder.

[0101] For custom placeholders (such as ), read the values preset by the user from the custom configuration of the processing instance. When creating the processing instance, the user can configure specific replacement content for the custom placeholder, such as the partition date value "20240101". The system directly reads this user-preset value as the SQL fragment to be replaced.

[0102] After constructing the mapping relationship between all placeholders and the SQL fragments to be replaced, enter the string replacement stage. Traverse each placeholder in the SQL template, search for the corresponding SQL fragment to be replaced in the mapping relationship table, and precisely replace the placeholder with the corresponding fragment content. During the replacement process, keep other SQL structures in the original template unchanged, and only fill in the content at the placeholder position. After all placeholders are replaced, the system obtains a complete and executable processing SQL statement, which has been dynamically generated according to the expected field information, the type of calculation engine, and the user's custom configuration, and can be directly submitted to the scheduling platform to create or update data processing tasks.

[0103] Step S305: Deploy the processing SQL statements to the scheduling platform to create or update the corresponding data processing tasks.

[0104] Specifically, after successfully generating the processing SQL statement, the first step is to determine whether a corresponding data processing task already exists on the scheduling platform for the current processing instance. Based on the unique identifier of the processing instance, the local task record table is queried to check if there is a scheduling platform task record associated with that instance and in a status of "running" or "created". The task record table stores task information for each interaction with the scheduling platform, including task type (create table, add field, create processing, modify processing, etc.), scheduling platform task ID, task status (initialization, running, success, failure), and the executed SQL content.

[0105] If the query results show that there is no corresponding data processing task for the processing instance, the task creation process begins. The task creation interface provided by the scheduling platform is called, and the generated processing SQL statement is submitted as the task's execution code. Simultaneously, based on the processing instance's configuration information, the task's scheduling parameters are set, including the execution cycle (e.g., daily execution at midnight), execution engine type (Presto or Kyubi), resource queue, and timeout. Upon receiving the request, the scheduling platform creates a new data processing task on the platform and returns a globally unique task ID. After receiving the task ID, its association with the processing instance is written to the task record table, and the task status is initialized to "running" or "created" for subsequent status synchronization and traceability.

[0106] If the query results show that a corresponding data processing task already exists for the processing instance (e.g., it has been created previously), the system enters the task update process. It calls the task update interface provided by the scheduling platform to replace the execution code in the original task with the newly generated processing SQL statement. Simultaneously, it checks if the scheduling parameters have changed; if so, it submits the update accordingly. After receiving the update request, the scheduling platform modifies the configuration of the corresponding task and returns the operation result. Upon receiving a successful response, it updates the status of the corresponding record in the task record table and records the timestamp and update content of this update.

[0107] After a task is created or updated and submitted, it enters the status synchronization phase. A scheduled task is set up to periodically query the scheduling platform for the task's execution status based on the task ID in the task log table. The scheduling platform may return statuses such as: waiting, running, success, and failure. The status field in the local task log table is updated based on the returned status. For tasks with a status of "success," the time of successful completion is recorded, and subsequent notification processes are triggered; for tasks with a status of "failure," the reason for failure is recorded, and an alert notification is sent to the task manager via instant message or email, indicating that manual intervention is required to investigate the problem.

[0108] After a task is successfully deployed and run, relevant metadata information about the task execution will be recorded, including the content of the executed SQL statements, the names of the data tables involved, and the range of data affected. This information will be stored in the extended fields of the task record table for subsequent auditing and troubleshooting.

[0109] Through the above process, the automated deployment of SQL statements is achieved, ensuring that data processing tasks can run according to the expected cycle, continuously flattening the JSON fields in the Hive table, and writing the flattened data into the target table, thereby completing the fully automated closed loop from table structure change to data processing.

[0110] Please see Figure 4 and Figure 5 , Figure 4 and Figure 5 A flowchart illustrating a flattening detection method provided in this application embodiment includes the following steps: like Figure 4 and Figure 5 As shown, this invention is driven by a timed monitoring mechanism, which achieves intelligent leveling of JSON fields in Hive tables through periodic table structure comparison and automated processing. The entire process can be divided into several stages, including timed triggering, instance traversal, table structure comparison, branch processing (table creation, field addition, conflict alerts), and subsequent task scheduling and notification. Each stage is closely connected to form a complete automated closed loop.

[0111] A pre-configured scheduled task automatically triggers the leveling detection process at fixed time intervals (e.g., every 5 minutes). After the scheduled task starts, it first queries the processing instance table for a list of all processing instances with an "enabled" status, serving as the candidate set for this current processing. Then, it enters an instance traversal loop, sequentially performing subsequent detection and processing on each enabled processing instance.

[0112] For the currently iterated processing instance, the system first reads all field configuration information associated with that instance from the Hive field configuration table, i.e., the expected target table structure, including field names, data types, processing logic, and other definitions. If the read field configuration is empty, it means that the instance has not yet been configured with any leveling fields, and the system skips the current instance and directly processes the next one. If the configuration is not empty, the system calls the Hive metadata service interface to query the actual table structure of the target Hive table pointed to by the processing instance. The query result may return either the table exists (containing the actual field list) or the table does not exist.

[0113] After obtaining the actual table structure, it is compared with the expected field information, and different processing branches are initiated based on the comparison results: If the target Hive table does not exist: it is determined that a new table needs to be created, and the "Table Creation Process" is initiated.

[0114] If the table already exists, then further compare the differences between the expected fields and the actual fields: Completely consistent: This means that the current table structure meets expectations and no leveling operation is needed. End the processing of this instance and move on to the next instance.

[0115] There are new fields: This means that the expected field information includes fields that do not actually exist in the table. Proceed to the "Add Fields Process".

[0116] Field data type conflict: This means that the data type of the expected field is inconsistent with the data type of the field with the same name in the actual table. The system will enter "conflict alarm handling" and notify relevant personnel to intervene and resolve the issue via instant message or email.

[0117] When the table does not exist, the table creation process is executed. First, based on the field definitions (including regular fields and partitioning fields) in the expected field information, a complete CREATE TABLE statement is generated according to the field sorting values. Then, this DDL statement is submitted to the big data task scheduling platform for execution, and the platform polls for the DDL task to complete. After successful DDL execution, the SQL generation engine is invoked. Based on the preset SQL template, the expected field information, and the computing engine type specified by the processing instance, a processing SQL statement is generated using a placeholder replacement mechanism. Finally, a new data processing task is created on the scheduling platform, the processing SQL and scheduling parameters (such as the scheduled execution period) are set, detailed information about this leveling operation is recorded, and a leveling success notification is sent.

[0118] When a new field is detected in the configuration, the field addition process begins. First, a complete list of all new fields is identified. To ensure data consistency, it is checked whether any processing tasks associated with this instance are currently running or waiting. If so, these tasks are stopped, and the data time reference point for rollback is recorded. Then, an `ALTER TABLE ADD COLUMN` statement is generated based on the new field's definition, containing only the definition of the new field, and submitted to the scheduling platform for execution. The system polls for the DDL execution to complete. After successful DDL field addition, the processing SQL statement is regenerated, and the corresponding processing task configuration on the scheduling platform is updated. Simultaneously, based on the previously recorded task stop time, a data rollback task is created to reprocess the data from the stop period, ensuring the new field's data is fully populated. Finally, a successful rollback notification is sent to inform relevant personnel of the newly added field information.

[0119] When a data type conflict is detected, all conflicting fields are identified, and the expected and actual data types for each conflicting field are recorded. Subsequently, an alert notification is sent to the task manager via instant messaging or email, indicating the need for manual intervention. Table structure changes are not automatically executed to avoid data corruption due to forced data type conversions. After manual processing is completed, operations personnel can manually trigger a re-comparison or directly repair the table structure.

[0120] Whether it's creating a table, adding fields, or handling conflict alerts, the current instance's loop ends after processing is complete, and the process moves to the next active processing instance until all instances are processed, at which point the scheduled data balancing detection task concludes. This entire process automates the entire lifecycle management from table structure changes to data processing task updates, significantly reducing manual intervention costs and ensuring the timeliness and accuracy of JSON field balancing.

[0121] This application provides a method for leveling out, comprising: periodically triggering leveling out; for each processing instance, obtaining the expected field information corresponding to each processing instance and the actual table structure of the target Hive table, wherein the expected field information defines the fields and processing logic that the target Hive table should contain; then comparing the expected field information corresponding to each processing instance with the actual table structure of the target Hive table to obtain the comparison result; and then performing the corresponding table structure change operation based on the comparison result. After the table structure change operation is successful, based on a preset SQL template, the expected field information, and the computing engine type specified by the processing instance, processing SQL statements are generated by replacing placeholders in the SQL template, thereby deploying the processing SQL statements to the scheduling platform to create or update the corresponding data processing task. This application automatically detects changes in the Hive table structure through a periodically triggered mechanism, accurately compares the expected field information with the actual table structure, and automatically executes the corresponding table structure change operation based on the comparison result. This achieves complete automation from table structure detection to change, completely eliminating the cumbersome process of relying on manual monitoring and manual modification of the table structure in traditional solutions, significantly reducing the operational burden and error probability of data development personnel, and improving the efficiency of leveling out.

[0122] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0123] The following are device embodiments of this application. For details not described in detail, please refer to the corresponding method embodiments described above.

[0124] Figure 6This diagram illustrates a flatness detection device according to an embodiment of this application. For ease of explanation, only the parts relevant to the embodiment of this application are shown. The flatness detection device includes: The data acquisition module 601 is used to trigger the leveling detection at regular intervals. For each processing instance, it acquires the expected field information and the actual table structure of the target Hive table for each processing instance. The expected field information defines the fields that the target Hive table should contain and the processing logic. The data comparison module 602 is used to compare the expected field information corresponding to each processing instance with the actual table structure of the target Hive table and obtain the comparison results. Operation module 603 is used to perform corresponding table structure change operations based on the comparison results; The SQL statement generation module 604 is used to generate processing SQL statements after a table structure change operation is successful, based on a preset SQL template, expected field information, and the calculation engine type specified by the processing instance, by replacing placeholders in the SQL template. Deployment module 605 is used to deploy processing SQL statements to the scheduling platform to create or update corresponding data processing tasks.

[0125] In one embodiment, the data acquisition module 601 is further configured to query all processing instances whose status is enabled; For each processing instance across all processing instances, read the desired field information associated with each processing instance from the field configuration table. Verify whether the expected field information is empty; If not empty, call the Hive metadata service interface to obtain the actual table structure of the target Hive table.

[0126] In one embodiment, the data comparison module 602 is further configured to compare the field definitions in the expected field information with the field definitions in the actual table structure to obtain the comparison results, wherein the field definition includes at least the field name and the field data type.

[0127] In one embodiment, the comparison result includes at least one of the following situations: The target Hive table does not exist; The expected field information should match the actual table structure; The expected field information includes new fields that are not present in the actual table structure; At least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

[0128] In one embodiment, the operation module 603 is further configured to generate and execute a table creation DDL statement based on the expected field information when the comparison result indicates that the target Hive table does not exist; When the comparison result indicates that there are new fields in the expected field information that are not present in the actual table structure, a modification table is generated to add the new fields and DDL statements are executed.

[0129] In one embodiment, the apparatus further includes an alarm processing module, which triggers alarm processing when the comparison result indicates that at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

[0130] In one embodiment, the SQL statement generation module 604 is further configured to obtain a preset SQL template associated with each processing instance, wherein the preset SQL template contains at least one placeholder. Based on the expected field information, the calculation engine type, and the custom configuration of the processing instance, construct the mapping relationship between placeholders and the SQL fragments to be replaced; Iterate through the placeholders in the preset SQL template, perform string replacement according to the mapping relationship, and generate the processed SQL statement.

[0131] In one embodiment, the SQL statement generation module 604 is further configured to determine the type of each placeholder in a preset SQL template; Based on the type of placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, calculation engine type, or custom configuration of the processing instance, and a mapping relationship between the placeholder and the SQL fragment to be replaced is established.

[0132] In one embodiment, the placeholder type includes at least one of the following: System-wide placeholders used to generate a list of query fields when generating processing SQL statements; JSON parsing placeholders are used to generate appropriate JSON parsing statements based on the computing engine type when generating processing SQL statements; Custom placeholders are used to replace custom values ​​that the user has pre-configured in the processing instance when generating processing SQL statements.

[0133] In one embodiment, the SQL statement generation module 604 is further configured to read the field definitions and processing logic of all fields to be flattened from the expected field information when the placeholder is a system general placeholder; Based on the field definitions and processing logic of all fields to be flattened, a field selection list is generated, and the field selection list is used as the SQL fragment to be replaced corresponding to the system's general placeholder. The field selection list is a list of SELECT clause fields formed by concatenating the processing logic of each field according to the preset field sorting value.

[0134] In one embodiment, the SQL statement generation module 604 is further configured to obtain the configuration parameters of the JSON parsing placeholder when the placeholder is a JSON parsing placeholder, wherein the configuration parameters include at least the source field name where the JSON data is located, the JSON path, and the list of key-value pairs to be extracted; Based on the computing engine type specified in the processing instance, the corresponding JSON parsing statement generation strategy is invoked to generate a JSON parsing statement adapted to the computing engine type, which is then used as the SQL fragment to be replaced corresponding to the JSON parsing placeholder.

[0135] In one embodiment, the SQL statement generation module 604 is further configured to generate a first parsing statement based on the json_parse function and the row type if the calculation engine type is a first engine type; If the calculation engine type is the second engine type, then a second parsing statement based on the json_tuple function and the lateral view is generated.

[0136] In one embodiment, the SQL statement generation module 604 is further configured to read the user-preset value corresponding to the custom placeholder from the custom configuration of the processing instance when the placeholder is a custom placeholder, and use it as the SQL fragment to be replaced.

[0137] In one embodiment, the SQL statement generation module 604 is further configured to find the corresponding SQL fragment to be replaced from the mapping relationship; Replace the placeholders with the SQL fragments to be replaced to generate the processed SQL statements.

[0138] This application provides a data leveling detection device, specifically used for: periodically triggering data leveling detection; for each processing instance, obtaining the expected field information corresponding to each processing instance and the actual table structure of the target Hive table; wherein the expected field information defines the fields and processing logic that the target Hive table should contain; then comparing the expected field information corresponding to each processing instance with the actual table structure of the target Hive table to obtain the comparison result; and then performing the corresponding table structure change operation based on the comparison result. After the table structure change operation is successful, based on a preset SQL template, the expected field information, and the computing engine type specified by the processing instance, generating a processing SQL statement by replacing the placeholders in the SQL template, thereby deploying the processing SQL statement to the scheduling platform to create or update the corresponding data processing task. This application automatically detects changes in the Hive table structure through a periodically triggered mechanism, accurately compares the expected field information with the actual table structure, and automatically executes the corresponding table structure change operation based on the comparison result. This achieves complete automation from table structure detection to change, completely eliminating the cumbersome process of relying on manual monitoring and manual modification of the table structure in traditional solutions, significantly reducing the operational burden and error probability of data development personnel, and improving the efficiency of data leveling detection.

[0139] This application Figure 7 A schematic diagram of a computer device is provided. (Example) Figure 7 As shown, the computer device 7 in this embodiment includes a processor 701, a memory 702, and a computer program 703 stored in the memory 702 and executable on the processor 701. When the processor 701 executes the computer program 703, it implements the steps in the various flattening detection method embodiments described above, for example... Figure 3 Steps 301 to 305 are shown. Alternatively, when processor 701 executes computer program 703, it implements the functions of each module / unit in the above-described embodiments of the flattening detection device, for example... Figure 6 The functions of modules / units 601 to 605 shown.

[0140] This application also provides a readable storage medium storing a computer program, which, when executed by a processor, is used to implement the flattening detection method provided in the various embodiments described above.

[0141] The readable storage medium can be a computer storage medium or a communication medium. A communication medium includes any medium that facilitates the transfer of computer programs from one location to another. A computer storage medium can be any available medium accessible to a general-purpose or special-purpose computer. For example, a readable storage medium is coupled to a processor, enabling the processor to read information from and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can reside in an Application-Specific Integrated Circuit (ASIC). Alternatively, the ASIC can be located in a user equipment. Of course, the processor and the readable storage medium can also exist as discrete components in a communication device. The readable storage medium can be a read-only memory (ROM), random access memory (RAM), CD-ROM, magnetic tape, floppy disk, and optical data storage device, etc.

[0142] This application also provides a program product including executable instructions stored in a readable storage medium. At least one processor of the device can read the executable instructions from the readable storage medium, and the execution of the executable instructions by the at least one processor causes the device to implement the flattening detection method provided in the various embodiments described above.

[0143] In the embodiments of the above-described device, it should be understood that the processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), etc. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in this application can be directly manifested as being executed by a hardware processor, or executed by a combination of hardware and software modules within the processor.

[0144] The above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for detecting flatness, characterized in that, include: The flattening detection is triggered periodically. For each processing instance, the expected field information and the actual table structure of the target Hive table are obtained. The expected field information defines the fields and processing logic that the target Hive table should contain. The expected field information corresponding to each processing instance is compared with the actual table structure of the target Hive table to obtain the comparison result; Based on the comparison results, perform the corresponding table structure change operation; After the table structure change operation is successful, based on the preset SQL template, the expected field information, and the calculation engine type specified by the processing instance, the processing SQL statement is generated by replacing the placeholders in the SQL template. The processing SQL statements are deployed to the scheduling platform to create or update the corresponding data processing tasks.

2. The flatness detection method as described in claim 1, characterized in that, The timed triggering of the leveling-off detection involves, for each processing instance, obtaining the expected field information corresponding to each processing instance and the actual table structure of the target Hive table, including: Query all processing instances whose status is enabled; For each of the aforementioned processing instances, read the desired field information associated with each processing instance from the field configuration table. Verify whether the expected field information is empty; If not empty, call the Hive metadata service interface to obtain the actual table structure of the target Hive table.

3. The flatness detection method as described in claim 1, characterized in that, The step of comparing the expected field information corresponding to each processing instance with the actual table structure of the target Hive table to obtain the comparison result includes: The field definitions in the expected field information are compared with the field definitions in the actual table structure to obtain the comparison results. The field definitions include at least the field name and the field data type.

4. The flatness detection method as described in claim 3, characterized in that, The comparison results include at least one of the following situations: The target Hive table does not exist; The expected field information is consistent with the actual table structure; The expected field information contains new fields that are not present in the actual table structure; At least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

5. The flatness detection method as described in claim 4, characterized in that, The step of performing corresponding table structure change operations based on the comparison results includes: When the comparison result indicates that the target Hive table does not exist, a table creation DDL statement is generated and executed based on the expected field information. When the comparison result indicates that there is a new field in the expected field information that is not present in the actual table structure, a modification table for adding the new field is generated and DDL statements are executed.

6. The flatness detection method as described in claim 4, characterized in that, The method further includes: An alarm is triggered when the comparison result shows that at least one field in the expected field information does not match the data type of the corresponding field in the actual table structure.

7. The flatness detection method as described in claim 1, characterized in that, The process generates processing SQL statements by replacing placeholders in the SQL template, based on a preset SQL template, the desired field information, and the computing engine type specified by the processing instance. This includes: Obtain a preset SQL template associated with each of the processing instances, wherein the preset SQL template contains at least one placeholder; Based on the expected field information, the computing engine type, and the custom configuration of the processing instance, a mapping relationship between placeholders and SQL fragments to be replaced is constructed; The placeholders in the preset SQL template are traversed, and string replacement is performed according to the mapping relationship to generate the processing SQL statement.

8. The flatness detection method as described in claim 7, characterized in that, The step of constructing a mapping relationship between placeholders and SQL fragments to be replaced based on the expected field information, the computing engine type, and the custom configuration of the processing instance includes: Determine the type of each placeholder in the preset SQL template; Based on the type of the placeholder, the corresponding SQL fragment to be replaced is obtained from the expected field information, the computing engine type, or the custom configuration of the processing instance, and a mapping relationship between the placeholder and the SQL fragment to be replaced is established.

9. The flatness detection method as described in claim 8, characterized in that, The placeholder type includes at least one of the following: System-wide placeholders are used to generate a list of query fields when generating the processing SQL statement; JSON parsing placeholders are used to generate an adapted JSON parsing statement based on the computing engine type when generating the processing SQL statement; A custom placeholder is used to replace the user-configured custom value in the processing instance when generating the processing SQL statement.

10. The flatness detection method as described in claim 9, characterized in that, The step of obtaining the corresponding SQL fragment to be replaced from the expected field information, the computing engine type, or the custom configuration of the processing instance based on the type of the placeholder includes: When the placeholder is a system-wide placeholder, the field definitions and processing logic of all fields to be leveled are read from the expected field information; Based on the field definitions and processing logic of all fields to be flattened, a field selection list is generated, and the field selection list is used as the SQL fragment to be replaced corresponding to the system's general placeholder. The field selection list is a list of SELECT clause fields formed by concatenating the processing logic of each field according to a preset field sorting value.

11. The flatness detection method as described in claim 9, characterized in that, The step of obtaining the corresponding SQL fragment to be replaced from the expected field information, the computing engine type, or the custom configuration of the processing instance based on the type of the placeholder includes: When the placeholder is a JSON parsing placeholder, obtain the configuration parameters of the JSON parsing placeholder, wherein the configuration parameters include at least the source field name where the JSON data is located, the JSON path, and the list of key-value pairs to be extracted; Based on the computing engine type specified in the processing instance, the corresponding JSON parsing statement generation strategy is invoked to generate a JSON parsing statement adapted to the computing engine type, which is then used as the SQL fragment to be replaced corresponding to the JSON parsing placeholder.

12. The flatness detection method as described in claim 11, characterized in that, The step of invoking the corresponding JSON parsing statement generation strategy based on the computing engine type specified by the processing instance to generate a JSON parsing statement adapted to the computing engine type includes: If the computing engine type is the first engine type, then a first parsing statement based on the json_parse function and the row type is generated; If the computing engine type is the second engine type, then a second parsing statement based on the json_tuple function and the lateral view is generated.

13. The flatness detection method as described in claim 9, characterized in that, The step of obtaining the corresponding SQL fragment to be replaced from the expected field information, the computing engine type, or the custom configuration of the processing instance based on the type of the placeholder includes: When the placeholder is a custom placeholder, the user preset value corresponding to the custom placeholder is read from the custom configuration of the processing instance and used as the SQL fragment to be replaced.

14. The flatness detection method as described in claim 7, characterized in that, The step of performing string replacement based on the mapping relationship to generate the processing SQL statement includes: Find the corresponding SQL fragment to be replaced from the mapping relationship; The placeholder is replaced with the SQL fragment to be replaced, and the processing SQL statement is generated.

15. A flatness detection device, characterized in that, include: The data acquisition module is used to trigger the leveling detection at regular intervals. For each processing instance, it acquires the expected field information and the actual table structure of the target Hive table corresponding to each processing instance. The expected field information defines the fields and processing logic that the target Hive table should contain. The data comparison module is used to compare the expected field information corresponding to each processing instance with the actual table structure of the target Hive table and obtain the comparison results. The operation module is used to perform corresponding table structure change operations based on the comparison results; The SQL statement generation module is used to generate processing SQL statements by replacing placeholders in the SQL template after the table structure change operation is successful, based on a preset SQL template, the expected field information, and the calculation engine type specified by the processing instance. The deployment module is used to deploy the processing SQL statements to the scheduling platform to create or update the corresponding data processing tasks.

Images