An automated testing method for database external table functionality

Abstract

The application provides an automatic testing method for a database external table function, comprising the following steps: constructing a test case; based on a unified test interface template, customizing a test interface file according to an actual test scene; running a python scheduling tool; 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 testing engine; running the automatic testing engine corresponding to the plurality of external data source environments, and the automatic testing engine automatically reading and writing data according to the configuration in the test interface file; generating a test report; and destroying the container environment.The application has the beneficial effects of: unified automatic deployment of test components; unified abstraction of test access interfaces of different data sources; and implementation of an automatic testing method independent of manual interactive operation.

Description

TECHNICAL FIELD

[0001] The present application belongs to the technical field of distributed databases, and particularly relates to an automatic testing method for a database external table function. BACKGROUND

[0002] An external table is a special table in a database, which does not store data itself but refers to data in external storage (such as HDFS, S3, OSS, and a file system). The external table is usually used for big data processing, such as Hudi, Hadoop, Hive, Spark, and hdfs tools, and is mainly used for querying data in external storage without changing the data itself.

[0003] The current traditional testing of a database external table usually needs to manually configure a complex environment and perform a large number of testing actions. First, different external data source environments of components such as Hudi, Hadoop, Hive, Spark, and hdfs need to be manually configured, and the distributed database cluster to be tested also needs to be manually installed. In the process of executing the test, the tester also needs to frequently manually interact with the external data source and the database cluster to be tested. The test execution efficiency is relatively low, and the steps are numerous and prone to errors. Moreover, manually writing test cases for the external table has many disadvantages such as poor readability, poor maintainability, and poor scalability.

[0004] Therefore, in view of the above-mentioned testing pain points of the database external table function, a more unified and efficient automatic testing method is needed. SUMMARY

[0005] Therefore, the present application aims to provide an automatic testing method for a database external table function to solve the problem of complex manual environment configuration and a large number of testing action steps in the prior art.

[0006] To achieve the above-mentioned purpose, the technical solution of the present application is as follows:

[0007] An automatic testing method for a database external table function, comprising the following steps:

[0008] S1, constructing a test case: based on a unified test interface template, customizing a test interface file according to an actual test scenario;

[0009] S2, running a python scheduling tool;

[0010] S3, 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;

[0011] S4, running the automation test engine: running the automation test engine corresponding to the multiple external data source environments, and the automation test engine reads and writes data according to the configuration in the test interface file;

[0012] S5, generating a test report;

[0013] S6, destroying the container environment;

[0014] In step S1, based on the unified test interface template, the test interface file is customized according to the actual test scene, including:

[0015] S11, analyzing the data parameters of the test, and dividing the data into type, setup, operate and teardown four parts according to the data parameters, and filling the data parameters in each part;

[0016] S12, in the test interface template, read and write interfaces are designed for multiple external data source environments, and the interactive operation of multiple external data source environments is standardized.

[0017] Further, in step S3, the container environment is started and multiple external data source environments and distributed database cluster environments are configured according to the parameters in the test interface template, including:

[0018] S31, building an external data source environment;

[0019] S32, building a distributed database cluster environment.

[0020] Further, in step S4, the automation test engine corresponding to the multiple external data source environments is run, and the automation test engine reads and writes data according to the configuration in the test interface file, including:

[0021] S41, if the hudi is operated, the hudi-operate-tool engine is used to read and write data of the hudi environment;

[0022] S42, using the catalog-tool engine to operate the distributed database cluster, automatically operating the catalog in the distributed database cluster, and automatically configuring the kerberos authentication and distributed cluster parameters in the distributed database cluster nodes.

[0023] Further, in step S5, a test report is generated, including:

[0024] The running result is stored in the server, a result directory is generated in the server, and the execution log of the test tool and the execution result of the sql case are recorded.

[0025] Further, in step S31, the external data source environment is built, including:

[0026] S311, write a Docker Compose file according to the syntax of Docker Compose, and define external data source parameters in the file;

[0027] S312, start the container in the file using the command docker compose up -d, and if all the containers in the terminal display the word started, the external data source environment is built.

[0028] Further, in step S32, a distributed database cluster environment is built, including:

[0029] S321, write a Docker Compose file according to the syntax of Docker Compose, and define parameters of the distributed database in the file;

[0030] S322, start the container in the file using the command docker compose up -d, and if all the containers in the terminal display the word started, the container is started;

[0031] S323, install the distributed database, and if the database installation is completed, the distributed database cluster environment is built.

[0032] Further, in step S41, read and write operations are performed on the data of the hudi environment by using the hudi-operate-tool engine, including:

[0033] The data of the hudi environment is read and written by using the interface in java to operate the spark with kerberos authentication.

[0034] Further, 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 node are automatically configured, including:

[0035] The distributed database cluster is operated by using python, and the program is packaged as a binary file, and the binary file is called by using the interface of java to perform read and write operations on the catalog in the distributed database cluster.

[0036] Further, in step S12, read and write interfaces are designed for multiple external data source environments, including:

[0037] S121, if the interface design is for the hudi data source environment, first determine the database name, table name field in hudi;

[0038] S122, determine the field of the data file path;

[0039] S123, determine the hudi write type, hudi storage type, pre-merge column, partition column and data insertion method field;

[0040] S124, define the column in the form of a yaml file using the java interface provided by hudi to operate the hudi table, and determine the schema column definition field;

[0041] S125, determine the data of each row with the row field, wherein each item is separated from each column data by a set symbol.

[0042] Further, the plurality of external data source environments include Hudi, Hadoop, Hive, Spark and hdfs.

[0043] Compared with the prior art, the automatic test method for the external table function of the database has the following beneficial effects:

[0044] (1) Unified automatic deployment of test components: through containerization technology, a unified test environment of comprehensive multiple different external data sources (such as Hudi, Hadoop, Hive, Spark and hdfs, etc.) and the measured distributed database cluster (and with kerberos encryption authentication) is quickly and automatically built using docker compose, and manual complex installation and configuration on physical machines or virtual machines is no longer needed.

[0045] (2) Unified abstraction of test access interfaces of different data sources: for different external data source environments (such as Hudi, Hadoop, Hive, Spark and hdfs, etc.) in the database, the test access interfaces are uniformly configured in the form of a yaml configuration file, and the interfaces of different data sources have their own environment configurations and operation interfaces, and the overall test case is very easy to maintain and extend.

[0046] (3) Realize the automatic test method independent of manual interactive operation: when operating data using different external data source environments, frequent manual interaction with the data source and the measured database cluster is needed, which is time-consuming and laborious, and is prone to errors. The present application realizes automatic access to different external data sources and measured databases, and the test process is automated, greatly improving the overall test efficiency and reducing maintenance costs. BRIEF DESCRIPTION OF DRAWINGS

[0047] The accompanying drawings, which form a part of this application, are included to provide a further understanding of the application, and are incorporated in and constitute a part of this application. The illustrative embodiments of the application and their description serve to explain the application. In the drawings:

[0048] Figure 1 The overall flowchart described in the embodiments of the present application is shown. DETAILED DESCRIPTION

[0049] It should be noted that the embodiments in the present application and the features in the embodiments can be combined with each other without conflict.

[0050] In the description of the present application, it should be understood that the terms "center", "longitudinal", "transverse", "upper", "lower", "front", "rear", "left", "right", "vertical", "horizontal", "top", "bottom", "inner", "outer" and the like indicate the orientation or positional relationship based on the orientation or positional relationship shown in the drawings, and are only for the convenience of describing the present application and simplifying the description, and do not indicate or imply that the device or element referred to must have a particular orientation, be constructed and operated in a particular orientation, and therefore cannot be understood as a limitation of the present application. In addition, the terms "first", "second" and the like are only for descriptive purposes and cannot be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Therefore, the features defined with "first", "second" and the like can explicitly or implicitly include one or more of the features. In the description of the present application, unless otherwise specified, the meaning of "a plurality of" is two or more.

[0051] In the description of the present application, it should be noted that unless otherwise explicitly specified and limited, the terms "mounting", "connection", "connection" should be understood broadly, for example, it can be fixedly connected, or it can be detachably connected, or integrally connected; it can be mechanically connected, or it can be electrically connected; it can be directly connected, or it can be indirectly connected through an intermediate medium, or it can be the communication inside two elements. For those skilled in the art, the specific meaning of the above terms in the present application can be understood through specific circumstances.

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

[0053] Glossary:

[0054] Python: a concise, readable and powerful high-level programming language, widely used in data analysis, artificial intelligence, website development and other fields

[0055] Java: an object-oriented programming language, widely used in developing cross-platform applications, with powerful library and framework support

[0056] Hudi-operate-tool: self-developed read-write engine tool for hudi data lake

[0057] catalog-tool: A self-developed read-write engine tool for the catalog module of a distributed database cluster

[0058] catalog: A system table that stores information about objects in a distributed database cluster, containing metadata about tables, views, indexes, stored procedures, etc.

[0059] kerberos: A network authentication protocol that ensures secure communication between users and services through symmetric encryption, preventing identity spoofing and data tampering

[0060] Docker Compose: A tool for defining and running multi-container Docker applications, allowing easy management of multiple service containers through YAML configuration files

[0061] yaml: A concise markup language commonly used in configuration files and data exchange, easy to read and write

[0062] sql: A standard language for managing relational databases, used for querying, inserting, updating, and deleting data

[0063] 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

[0064] Hadoop: An open-source distributed storage and processing framework for handling large-scale data sets, supporting massive data storage and parallel computing

[0065] Hive: A Hadoop-based data warehouse tool for storing and querying large-scale data sets, supporting a SQL-like query language

[0066] 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

[0067] hdfs: A distributed file system for storing and managing massive amounts of data in large-scale clusters, with high fault tolerance and high throughput

[0068] As shown in the following figure, an automated testing method for the external table function of a database includes the following steps: Figure 1

[0069] S1, Construct test cases: Based on a unified test interface template, customize test interface files according to actual test scenarios;

[0070] S2, Run the python scheduling tool;

[0071] ​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;

[0072] S4, run the automated test engine: run the automated test engine corresponding to the multiple external data source environments, and the automated test engine reads and writes data according to the configuration in the test interface file;

[0073] S5, generate a test report;

[0074] S6, destroy the container environment.

[0075] The specific implementation is as follows:

[0076] The tester fills in the corresponding parameter values according to the parameters exposed by the test interface template to construct the test case. Secondly, the scheduling tool written in python automatically reads the type parameter in the test case to determine the data source type of this operation and starts the container of the corresponding data source to prepare the environment. Then the scheduling tool runs the automated test engine of the corresponding data source to execute the test case according to 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.

[0077] Step 1: The tester customizes his own test interface file based on the unified test interface template according to the actual test scenario, and ensures that each parameter in the template is correctly configured.

[0078] 1. The test interface template mainly determines its content according to the requirements of functional testing. First, analyze the data parameters needed in the test with the relevant testers, then classify the data according to these data parameters, divide the data into type, setup, operate, and teardown parts, and finally fill in the corresponding data parameters in each part.

[0079] 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.

[0080] 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.

[0081] 1. Build an external data source environment

[0082] (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).

[0083] (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.

[0084] 2. Build a distributed database cluster environment

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

[0086] (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.

[0087] (3) Install a distributed database, and the completion of database installation indicates that the distributed database cluster environment is built.

[0088] 3. Take building an external hudi data source environment as an example. Building a complete Hudi environment often involves the coordinated configuration of multiple components, which is complex and time-consuming. With the help of Docker Compose orchestration technology, various services that Hudi depends 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 by writing a configuration file. Users execute the docker compose up -d command, and Docker Compose will automatically pull the corresponding images of each service according to the configuration file, start and configure these services according to the pre-set network, storage, environment variables, etc. This greatly simplifies the building process of Hudi environment, reduces the errors of manual configuration, and significantly shortens the environment deployment period.

[0089] Step three: Run the automatic test engine corresponding to the environment of multiple data sources. This engine can read and write data according to the configuration in the test interface file. The following introduces two of the multiple automatic test engines.

[0090] 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 operate the spark with kerberos authentication by java, and complete the data read and write operations in the hudi environment according to the interface in java.

[0091] 2. The catalog-tool engine is used to operate the distributed database cluster. It automatically operates the catalog in the distributed database cluster and automatically configures the kerberos authentication, distributed cluster parameters, etc. in the nodes of the distributed database cluster. The main implementation method is to operate the distributed database cluster by python, and package the program into a binary file. According to the interface of java, this binary file is called to complete the read and write operations of the catalog in the distributed database cluster.

[0092] Step four: Store the running results in the corresponding server, generate the result directory in the server, record the execution log of the test tool and the execution result of the sql use case, and destroy the container environment after the execution result is successfully recorded.

[0093] Example 1:

[0094] Test interface template:

[0095] # Program parses the identification of the test case, which needs to contain the character "case"

[0096] case1:

[0097] # Identify the data source operation object [hive | hudi | hdfs spark] this time

[0098] type:

[0099] setup:

[0100] # External data source configuration

[0101] ext:

[0102] ip:

[0103] rootPwd:

[0104] exec:

[0105] # Configuration of the database cluster being tested

[0106] db:

[0107] ips:

[0108] dbaUser:

[0109] dbaPwd:

[0110] rootPwd:

[0111] username:

[0112] password:

[0113] exec:

[0114] operate:

[0115] - hive:

[0116] # Operation performed in hive

[0117] exec:

[0118] - hudi:

[0119] # Operation performed in hudi

[0120] exec:

[0121] - spark:

[0122] # operations performed in spark

[0123] exec:

[0124] - db:

[0125] # distinguish the use case files after execution

[0126] sqlFileName:

[0127] # operations performed in the database cluster under test

[0128] exec:

[0129] teardown:

[0130] # external data source configuration

[0131] ext:

[0132] exec:

[0133] # configuration of the database cluster under test

[0134] db:

[0135] exec:

[0136] Example 2:

[0137] Interface design for hudi data source environment:

[0138] hudi:

[0139] database: test

[0140] tablename: hudi_mor_dstl

[0141] write:

[0142] tablepath: hdfs: / / namenode: 8020 / usr / hive / warehouse /

[0143] # hudi write type (insert | update | bulk insert | delete)

[0144] operation: insert

[0145] # hudi storage type (merge on read | copy on write)

[0146] storage: merge_on_read

[0147] recordkey: id

[0148] # Optional, pre-merge columns

[0149] precombine:

[0150] # Optional, partition columns

[0151] partition: name

[0152] # Data insertion mode (append | overwrite | error if exists | ignore)

[0153] mode: append

[0154] # Column definitions

[0155] schema:

[0156] type: struct

[0157] fields:

[0158] - name: id

[0159] type: integer

[0160] nullable: false

[0161] metadata: {}

[0162] - name: name

[0163] type: string

[0164] nullable: false

[0165] metadata: {}

[0166] - name: age

[0167] type: integer

[0168] nullable: false

[0169] metadata: {}

[0170] - name: timestamp

[0171] type: date

[0172] nullable: false

[0173] metadata: {}

[0174] # Row data

[0175] row:

[0176] - 4|sagitar|24|2024-12-02

[0177] - 5|Obviously|24|2024-12-02

[0178] - 8|unable to speak|24|2024-12-02

[0179] Example 3:

[0180] After configuring the test case according to steps 1 to 4 above, run the scheduling tool using the python command:

[0181] python3 sequence.py -f conf.yaml

[0182] 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:

[0183] # cd result

[0184] # Use case execution log

[0185] result.log

[0186] # Use case execution results

[0187] test.sql.result

[0188] The advantages and beneficial effects of the present invention are as follows:

[0189] (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.

[0190] (2) Unified abstract test access interface of different data sources: for different external data source environments (such as Hudi, Hadoop, Hive, Spark and hdfs) in the database, the test access interface is unified in the form of a yaml configuration file, and the interfaces of different data sources have their own environment configurations and operation interfaces, and the overall test case is very easy to maintain and extend.

[0191] (3) Implement an automatic testing method that does not depend on manual interactive operation: when operating data using different external data source environments, frequent manual interaction with the data source and the measured database cluster is required, which is time-consuming and labor-intensive, and is prone to errors. The present application realizes the automatic access of different external data sources and the measured database, and the test process is automated, greatly improving the overall test efficiency and reducing the maintenance cost.

[0192] The above only describes the preferred embodiments of the present application and is not intended to limit the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. A method for automated testing of database external table functionality, characterized by: The method comprises the following steps: S1, constructing a test case: based on a unified test interface template, customizing a test interface file according to an actual test scene; S2, running a python scheduling tool; S3, 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; S4, running an automated test engine: running the automated test engine corresponding to the plurality of external data source environments, and the automated test engine reading and writing data according to the configuration in the test interface file; S5, generating a test report; S6, destroying the container environment; In step S1, based on a unified test interface template, a test interface file is customized according to an actual test scene, comprising: S11, analyzing data parameters of the test, and dividing the data into four parts of type, setup, operate and teardown according to the data parameters, and filling data parameters in each part; S12, in the test interface template, reading and writing interfaces are designed for a plurality of external data source environments, and the interaction operation of the plurality of external data source environments is standardized; In step S4, the automated test engine corresponding to the plurality of external data source environments is run, and the automated test engine reads and writes data according to the configuration in the test interface file, comprising: S41, if the hudi is operated, the hudi-operate-tool engine is used to read and write data of the hudi environment; S42, the catalog-tool engine is used to operate the distributed database cluster, and the catalog in the distributed database cluster is automatically operated, and the kerberos authentication and distributed cluster parameters in the distributed database cluster node are automatically configured; In step S41, the hudi-operate-tool engine is used to read and write data of the hudi environment, comprising: The data reading and writing operation of the hudi environment is performed by using the interface in java to operate the spark with kerberos authentication; 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 node are automatically configured, comprising: The distributed database cluster is operated by using python, and the program is packaged into a binary file, and the binary file is called by using the interface of java to perform the reading and writing operation of the catalog in the distributed database cluster.

2. The method for automated testing of external table functionality against a database as claimed in claim 1, wherein: In step S3, the container environment is started, and a plurality of external data source environments and a distributed database cluster environment are configured according to parameters in the test interface template, comprising: S31, building an external data source environment; S32, building a distributed database cluster environment.

3. The method for automated testing of external table functionality against a database as claimed in claim 1, wherein: In step S5, the test report is generated, comprising: The running result is stored into a server, a result directory is generated in the server, and the execution log of the test tool and the execution result of the sql case are recorded.

4. The method for automated testing of external table functionality against a database as claimed in claim 2, wherein: In step S31, the external data source environment is built, comprising: S311, write a Docker Compose file according to the syntax of Docker Compose, define the external data source parameters in the file; S312, use the command docker compose up-d to start the container in the file, if all the containers in the terminal show the started word, the external data source environment is built.

5. The method for automated testing of external table functionality against a database as claimed in claim 2, wherein: In step S32, the distributed database cluster environment is built, including: S321, write a Docker Compose file according to the syntax of Docker Compose, define the parameters of the distributed database in the file; S322, use the command docker compose up-d to start the container in the file, if all the containers in the terminal show the started word, the container is started; S323, install the distributed database, if the database installation is completed, the distributed database cluster environment is built.

6. The method for automated testing of external table functionality against a database as claimed in claim 1, wherein: In step S12, the read-write interface is designed for multiple external data source environments, including: S121, if the interface design is for hudi data source environment, first determine the database name, table name field in hudi; S122, determine the field storing the data file path; S123, determine the hudi write type, hudi storage type, pre-merge column, partition column and data insertion method field; S124, use the interface provided by hudi to operate hudi table in java to define the column in the form of yaml file, determine the schema column definition field; S125, use the row field to determine the data of each row, where each item is separated by a set symbol to separate each column data.

7. The method for automated testing of external table functionality against a database as claimed in claim 1, wherein: The multiple external data source environments include Hudi, Hadoop, Hive, Spark and hdfs.

Images