Automatic testing method for database external table function

Abstract

The invention provides an automatic test method for external table functions of a database, which comprises the following steps of: constructing a test case: customizing a test interface file according to an actual test scene based on a unified test interface template; a python scheduling tool is operated; starting a container environment and configuring a plurality of external data source environments and a distributed database cluster environment according to parameters in the test interface template; running an automatic test engine: running the automatic test engine corresponding to various external data source environments, wherein the automatic test engine automatically reads and writes data according to the configuration in the test interface file; generating a test report; and destroying the container environment. The method has the beneficial effects that the test components are uniformly and automatically deployed; uniformly abstracting test access interfaces of different data sources; the automatic testing method independent of manual interaction operation is realized.

Description

Technical Field

[0001] The present invention belongs to the technical field of distributed databases, and in particular relates to an automated testing method for database external table functions. Background Art

[0002] An external table is a special type of database table that doesn't store data itself but instead references data stored in external storage (such as HDFS, S3, OSS, and file systems). External tables are commonly used in big data processing tools like Hudi, Hadoop, Hive, Spark, and HDFS. They are primarily used to query externally stored data without modifying the data itself.

[0003] Traditional testing of database external tables typically requires complex manual environment configuration and numerous test steps. First, you need to manually configure a diverse external data source environment with numerous components, such as Hudi, Hadoop, Hive, Spark, and HDFS. You also need to manually install the distributed database cluster under test. Furthermore, during the test execution process, you must frequently manually interact with the external data source and the database cluster under test. This results in low test execution efficiency, a multitude of steps, and is prone to errors. Manually writing test cases for external tables also has numerous drawbacks, such as poor readability, difficulty in maintenance, and difficulty in scalability.

[0004] Therefore, to address the testing pain points of the above-mentioned database external table functions, a more unified and efficient automated testing method is needed. Summary of the Invention

[0005] In view of this, the present invention aims to propose an automated testing method for database external table functions to solve the problems in the prior art of complex manual environment configuration and numerous test execution steps.

[0006] To achieve the above object, the technical solution of the present invention is achieved as follows: An automated testing method for database external table functions includes the following steps: S1. Build test cases: Based on a unified test interface template, customize the test interface file according to the actual test scenario; S2. Run the Python scheduling tool; S3. Start the container environment and configure multiple external data source environments and distributed database cluster environments according to the parameters in the test interface template; S4. Run the automated test engine: Run the automated test engine corresponding to various external data source environments. The automated test engine reads and writes data according to the configuration in the test interface file. S5. Generate a test report; S6. Destroy the container environment; In step S1, based on the unified test interface template, the test interface file is customized according to the actual test scenario, including: S11. Analyze the test data parameters, divide the data into four parts: type, setup, operate, and teardown according to the data parameters, and fill in the data parameters in each part; S12. In the test interface template, design read and write interfaces for various external data source environments and standardize the interactive operations of various external data source environments.

[0007] Furthermore, in step S3, the container environment is started and various external data source environments and distributed database cluster environments are configured according to the parameters in the test interface template, including: S31. Build an external data source environment; S32. Build a distributed database cluster environment.

[0008] Furthermore, in step S4, the automated test engines corresponding to the various external data source environments are run. The automated test engines read and write data according to the configuration in the test interface file, including: S41. If the operation is performed on hudi, the hudi-operate-tool engine is used to read and write data in the hudi environment; S42. Use the catalog-tool engine to operate the distributed database cluster, automatically operate the catalog in the distributed database cluster, and automatically configure the Kerberos authentication and distributed cluster parameters in the distributed database cluster nodes.

[0009] Furthermore, in step S5, a test report is generated, including: The running results are stored in the server, and a result directory is generated in the server to record the execution log of the test tool and the execution results of the SQL case.

[0010] Furthermore, in step S31, an external data source environment is built, including: S311. Write a Docker Compose file according to the Docker Compose syntax and define the external data source parameters in the file. S312. Use the command docker compose up -d to start the containers in the file. If all containers in the terminal display the word "started", the external data source environment is set up.

[0011] Furthermore, in step S32, a distributed database cluster environment is built, including: S321. Write a Docker Compose file according to the syntax of Docker Compose and define the parameters of the distributed database in the file. S322. Use the command docker compose up -d to start the containers in the file. If all containers in the terminal display the word "started", the container startup is complete. S323. Install the distributed database. If the database installation is complete, the distributed database cluster environment is set up.

[0012] Furthermore, in step S41, the hudi-operate-tool engine is used to perform read and write operations on the data in the hudi environment, including: Use Java to operate Spark with Kerberos authentication, and use the interface in Java to read and write data in the Hudi environment.

[0013] Furthermore, in step S42, the catalog in the distributed database cluster is automatically operated, and the Kerberos authentication and distributed cluster parameters in the distributed database cluster nodes are automatically configured, including: Use Python to operate the distributed database cluster, package the program into a binary file, and use the Java interface to call the binary file to perform catalog read and write operations in the distributed database cluster.

[0014] Furthermore, in step S12, read and write interfaces are designed for various external data source environments, including: S121. If it is the interface design of the hudi data source environment, first determine the database name and table name fields in hudi; S122, determining a field for storing a data file path; S123. Determine the hudi write type, hudi storage type, pre-merge column, partition column, and data insertion method fields; S124. Use the Java interface provided by Hudi to operate the Hudi table, define the column in the form of a YAML file, and determine the schema column definition field; S125. Use the row field to determine the data in each row, where each item is separated from each column of data by a set symbol.

[0015] Furthermore, multiple external data source environments include Hudi, Hadoop, Hive, Spark, and HDFS.

[0016] Compared with the prior art, the automated testing method for database external table functions described in the present invention has the following beneficial effects: (1) Unified automated deployment of test components: Through containerization technology, Docker Compose is used to quickly and automatically build a unified test environment (with Kerberos encryption authentication) that integrates multiple different external data sources (such as Hudi, Hadoop, Hive, Spark, and HDFS) and the tested distributed database cluster. This eliminates the need for testers to manually perform complex installation and configuration on physical or virtual machines.

[0017] (2) Unified abstraction of test access interfaces for different data sources: For different external data source environments in the database (such as Hudi, Hadoop, Hive, Spark, and HDFS, etc.), a unified test access interface is provided in the form of a YAML configuration file. In addition, the interfaces of different data sources have their own environment configuration and operation interfaces. The overall test case is very easy to maintain and expand.

[0018] (3) Implementing an automated testing method that does not rely on manual interaction: When operating data in different external data source environments, frequent manual interaction with the data source and the database cluster under test is required. This process is time-consuming, labor-intensive, and error-prone. The present invention implements automated access to different external data sources and the database under test, automating the testing process, greatly improving overall testing efficiency and reducing maintenance costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] The accompanying drawings, which constitute part of the present invention, are provided to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are provided to explain the present invention and do not constitute an undue limitation of the present invention. In the accompanying drawings: Figure 1 This is a schematic diagram of the overall process described in an embodiment of the present invention. DETAILED DESCRIPTION

[0020] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0021] In the description of the present invention, it should be understood that the terms "center", "longitudinal", "lateral", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inside", "outside" and the like indicate orientations or positional relationships based on the orientations or positional relationships shown in the accompanying drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as limiting the present invention. In addition, the terms "first", "second", etc. are only used for descriptive purposes and cannot be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, features defined as "first", "second", etc. may explicitly or implicitly include one or more of the features. In the description of the present invention, unless otherwise specified, "multiple" means two or more.

[0022] In the description of the present invention, it should be noted that, unless otherwise expressly specified or limited, the terms "mounted," "connected," and "connected" should be understood in a broad sense. For example, they may refer to fixed connections, detachable connections, or integral connections; mechanical connections or electrical connections; direct connections or indirect connections through an intermediate medium; and internal communication between two components. Those skilled in the art will understand the specific meanings of the above terms in the present invention based on specific circumstances.

[0023] The present invention will be described in detail below with reference to the accompanying drawings and in conjunction with embodiments.

[0024] Glossary: Python: A concise, easy-to-read, and powerful high-level programming language widely used in data analysis, artificial intelligence, website development, and other fields Java: An object-oriented programming language widely used for developing cross-platform applications with strong library and framework support hudi-operate-tool: a self-developed read and write engine tool for the hudi data lake catalog-tool: a self-developed read-write engine tool for the catalog module of distributed database clusters Catalog: A system table that stores information about objects in a distributed database cluster, including metadata about tables, views, indexes, stored procedures, and other objects. Kerberos: A network authentication protocol used to ensure secure communication between users and services through symmetric encryption, preventing identity forgery and data tampering. Docker Compose: A tool for defining and running multi-container Docker applications, allowing easy management of containers for multiple services through YAML configuration files YAML: A concise markup language, commonly used for configuration files and data exchange, easy to read and write SQL: A standard language for managing relational databases, used to query, insert, update, and delete data Hudi: An open source distributed data lake management framework that supports incremental loading, updating, and deleting of data, optimizing large-scale data storage and querying. Hadoop: An open source distributed storage and processing framework for processing large-scale data sets, supporting storage and parallel computing of massive data Hive: A Hadoop-based data warehouse tool for storing and querying large data sets, supporting a SQL-like query language Spark: An open source distributed computing framework that provides efficient data processing and analysis capabilities, supporting tasks such as batch processing, stream processing, and machine learning. HDFS: A distributed file system used to store and manage massive amounts of data in large clusters, with high fault tolerance and high throughput like Figure 1 As shown, an automated testing method for database external table functions includes the following steps: S1. Build test cases: Based on a unified test interface template, customize the test interface file according to the actual test scenario; S2. Run the Python scheduling tool; S3. Start the container environment and configure multiple external data source environments and distributed database cluster environments according to the parameters in the test interface template; S4. Run the automated test engine: Run the automated test engine corresponding to various external data source environments. The automated test engine reads and writes data according to the configuration in the test interface file. S5. Generate a test report; S6. Destroy the container environment.

[0025] The specific implementation is as follows: Testers construct their own test cases by filling in the corresponding parameter values based on the parameters exposed by the test interface template. Next, a scheduling tool written in Python automatically reads the "type" parameter in the test case to determine the data source type for the operation and starts the corresponding data source container to prepare the environment. The scheduling tool then runs the automated test engine for the corresponding data source based on the type of the data source operation object in the test case. Finally, after the test case is executed, the automated test engine automatically generates a test report, and the scheduling tool destroys the container environment.

[0026] Step 1: Based on the unified test interface template, the tester customizes his or her own test interface file according to the actual test scenario, ensuring that each parameter in the template is configured correctly.

[0027] 1. The content of the test interface template is primarily determined by the requirements of the functional test. First, analyze the data parameters required for the test with the relevant testers. Then, classify the data according to these parameters into four parts: type, setup, operate, and teardown. Finally, fill in the corresponding data parameters in each part.

[0028] 2. In the test interface template, read and write interfaces are designed for various data source environments to standardize the interaction between testers and various data source environments, improve test execution efficiency, and reduce the risk of manual operation errors. For example, when designing the interface for the Hudi data source environment, the first thing to determine is the database name and table name fields in Hudi. Secondly, the HDFS underlying the Hudi data source is a distributed file system, which can be used to determine the field storing the data file path. Then, based on Hudi's support for incremental writes, data merging, overwrite writes, and efficient compression, fields such as the Hudi write type, Hudi storage type, pre-merge columns, partition columns, and data insertion method are determined. Next, Hudi itself provides an interface for Java to operate Hudi tables, which supports defining columns in the form of YAML files and can determine the schema column definition fields. Finally, to allow testers to see the data in each row more intuitively, the row field is used to determine the data in each row, where each item is separated by "|" to separate each column of data.

[0029] Step 2: Start the container environment and configure various data source environments (such as Hudi, Hadoop, Hive, Spark, and HDFS) and distributed database cluster environments based on the parameters in the test interface template. Docker Compose is a tool for defining and managing multi-container Docker applications. A single YAML file configures all containers in an application and allows commands to start, stop, and build these containers.

[0030] 1. Build an external data source environment (1) Write a Docker Compose file according to the syntax of Docker Compose, and define the required data source parameters (such as Hudi, Hadoop, Hive, Spark, and HDFS).

[0031] (2) Use the command docker compose up -d to start the containers in the file. The word "started" will appear after all the containers in the terminal, indicating that the environment is set up.

[0032] 2. Build a distributed database cluster environment (1) Write a Docker Compose file according to the syntax of Docker Compose, which defines the parameters required for the distributed database.

[0033] (2) Use the command docker compose up -d to start the containers in the file. The word "started" will appear after all the containers in the terminal, indicating that the container startup is complete.

[0034] (3) Install the distributed database. The completion of database installation means that the distributed database cluster environment is complete.

[0035] 3. Taking the construction of an external Hudi data source environment as an example, building a complete Hudi environment often involves the coordinated configuration of multiple components, which is a complex and time-consuming process. With the help of Docker Compose orchestration technology, various services that Hudi relies on, such as NameNode, DataNode1, HiveMetastore, HiveServer, SparkMaster, Spark-Worker-1, Zookeeper, Kafka, Presto-Coordinator-1, Presto-Worker-1, Adhoc-1, etc., can be deployed with one click by writing a configuration file. When the user executes the docker compose up -d command, Docker Compose will automatically pull the corresponding images for each service based on the configuration file, and start and configure these services according to pre-set parameters such as network, storage, and environment variables. This greatly simplifies the process of setting up the Hudi environment, reduces manual configuration errors, and significantly shortens the environment deployment cycle.

[0036] Step 3: Run the automated test engines corresponding to the various data source environments. These engines can read and write data based on the configuration in the test interface files. The following describes two of these automated test engines.

[0037] 1. The hudi-operate-tool engine is used to operate Hudi. For data read and write operations in the Hudi environment, the main implementation method is to use Java to operate Spark with Kerberos authentication and complete data read and write operations in the Hudi environment according to the interface in Java.

[0038] The catalog-tool engine is used to operate distributed database clusters, automatically manipulating catalogs within them and configuring Kerberos authentication and distributed cluster parameters within cluster nodes. This is achieved by using Python to operate the distributed database cluster, packaging the program as a binary file, and calling this binary file through a Java API to complete catalog read and write operations within the distributed database cluster.

[0039] Step 4: Store the results of the run in the corresponding server, generate a result directory in the server, record the execution log of the test tool and the execution results of the SQL case, and destroy the container environment after all the execution results are successfully recorded.

[0040] Example 1: Test interface template: # The program parses the test case identifier, which must contain the character "case" case 1: # Identify the data source operation object [hive | hudi | hdfs spark] type: setup: # External data source configuration ext: ip: rootPwd: exec: # Configuration of the database cluster under test db: ips: dbaUser: dbaPwd: rootPwd: username: password: exec: operate: - hive: # Operations performed in hive exec: -hudi: # Operations performed in hudi exec: - spark: # Operations performed in spark exec: -db: # Distinguish the use case files after execution sqlFileName: # Operations performed in the database cluster under test exec: teardown: # External data source configuration ext: exec: # Configuration of the database cluster under test db: exec: Example 2: Interface design of hudi data source environment: hudi: database:test tablename: hudi_mor_dstl write: tablepath: hdfs: / / namenode: 8020 / usr / hive / warehouse / # hudi write type (insert | update | bulk insert | delete) operation: insert # hudi storage type (merge on read | copy on write) storage:merge_on_read recordkey:id # Optional, pre-merge columns precombine: # Optional, partition column partition:name # Data insertion method (append | overwrite | error if exists | ignore) mode: append # Column definition schema: type: struct fields: - name: id type: integer nullable: false metadata: {} - name: name type: string nullable: false metadata: {} - name: age type: integer nullable: false metadata: {} - name: timestamp type: date nullable: false metadata: {} # Row data row: - 4|sagitar|24|2024-12-02 - 5|Obviously|24|2024-12-02 - 8|unable to speak|24|2024-12-02 Example 3: After configuring the test case according to steps 1 to 4 above, run the scheduling tool using the python command: python3 sequence.py -f conf.yaml After execution, a result directory will be generated in the directory where the scheduling tool is located. The result directory records the cluster-side SQL use case execution log and SQL use case execution results: # cd result # Use case execution log result.log # Use case execution results test.sql.result The advantages and beneficial effects of the present invention are as follows: (1) Unified automated deployment of test components: Through containerization technology, Docker Compose is used to quickly and automatically build a unified test environment (with Kerberos encryption authentication) that integrates multiple different external data sources (such as Hudi, Hadoop, Hive, Spark, and HDFS) and the tested distributed database cluster. This eliminates the need for testers to manually perform complex installation and configuration on physical or virtual machines.

[0041] (2) Unified abstraction of test access interfaces for different data sources: For different external data source environments in the database (such as Hudi, Hadoop, Hive, Spark, and HDFS, etc.), a unified test access interface is provided in the form of a YAML configuration file. In addition, the interfaces of different data sources have their own environment configuration and operation interfaces. The overall test case is very easy to maintain and expand.

[0042] (3) Implementing an automated testing method that does not rely on manual interaction: When operating data in different external data source environments, frequent manual interaction with the data source and the database cluster under test is required. This process is time-consuming, labor-intensive, and error-prone. The present invention implements automated access to different external data sources and the database under test, automating the testing process, greatly improving overall testing efficiency and reducing maintenance costs.

[0043] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. An automated testing method for database external table functions, characterized by: The following steps are involved: S1. Build test cases: Based on a unified test interface template, customize the test interface file according to the actual test scenario; S2. Run the Python scheduling tool; S3. Start the container environment and configure multiple external data source environments and distributed database cluster environments according to the parameters in the test interface template; S4. Run the automated test engine: Run the automated test engine corresponding to various external data source environments. The automated test engine reads and writes data according to the configuration in the test interface file. S5. Generate a test report; S6. Destroy the container environment; In step S1, based on the unified test interface template, the test interface file is customized according to the actual test scenario, including: S11. Analyze the test data parameters, divide the data into four parts: type, setup, operate, and teardown according to the data parameters, and fill in the data parameters in each part; S12. In the test interface template, design read and write interfaces for various external data source environments and standardize the interactive operations of various external data source environments.

2. The automated testing method for database external table functions according to claim 1, characterized in that: In step S3, the container environment is started and various external data source environments and distributed database cluster environments are configured according to the parameters in the test interface template, including: S31. Build an external data source environment; S32. Build a distributed database cluster environment.

3. The automated testing method for database external table functions according to claim 1, characterized in that: In step S4, the automated test engines corresponding to the various external data source environments are run. The automated test engines read and write data according to the configuration in the test interface file, including: S41. If the operation is performed on hudi, the hudi-operate-tool engine is used to read and write data in the hudi environment; S42. Use the catalog-tool engine to operate the distributed database cluster, automatically operate the catalog in the distributed database cluster, and automatically configure the Kerberos authentication and distributed cluster parameters in the distributed database cluster nodes.

4. The automated testing method for database external table functions according to claim 1, characterized in that: In step S5, a test report is generated, including: The running results are stored in the server, and a result directory is generated in the server to record the execution log of the test tool and the execution results of the SQL case.

5. The automated testing method for database external table functions according to claim 2, characterized in that: In step S31, an external data source environment is built, including: S311. Write a Docker Compose file according to the Docker Compose syntax and define the external data source parameters in the file. S312. Use the command docker compose up -d to start the containers in the file. If all containers in the terminal display the word "started", the external data source environment is set up.

6. The automated testing method for database external table functions according to claim 2, characterized in that: In step S32, a distributed database cluster environment is built, including: S321. Write a Docker Compose file according to the syntax of Docker Compose and define the parameters of the distributed database in the file. S322. Use the command docker compose up -d to start the containers in the file. If all containers in the terminal display the word "started", the container startup is complete. S323. Install the distributed database. If the database installation is complete, the distributed database cluster environment is set up.

7. The automated testing method for database external table functions according to claim 3, characterized in that: In step S41, the hudi-operate-tool engine is used to perform read and write operations on the data in the hudi environment, including: Use Java to operate Spark with Kerberos authentication, and use the interface in Java to read and write data in the Hudi environment.

8. The automated testing method for database external table functions according to claim 3, characterized in that: In step S42, the catalog in the distributed database cluster is automatically operated, and the Kerberos authentication and distributed cluster parameters in the distributed database cluster nodes are automatically configured, including: Use Python to operate the distributed database cluster, package the program into a binary file, and use the Java interface to call the binary file to perform catalog read and write operations in the distributed database cluster.

9. The automated testing method for database external table functions according to claim 1, characterized in that: In step S12, read and write interfaces are designed for various external data source environments, including: S121. If it is the interface design of the hudi data source environment, first determine the database name and table name fields in hudi; S122, determining a field for storing a data file path; S123. Determine the hudi write type, hudi storage type, pre-merge column, partition column, and data insertion method fields; S124. Use the Java interface provided by Hudi to operate the Hudi table, define the column in the form of a YAML file, and determine the schema column definition field; S125. Use the row field to determine the data in each row, where each item is separated from each column of data by a set symbol.

10. The automated testing method for database external table functions according to claim 1, characterized in that: Multiple external data source environments include Hudi, Hadoop, Hive, Spark, and HDFS.

Images