ETL task execution method and device, storage medium and equipment

Abstract

The invention provides an ETL task execution method and device, a storage medium and equipment. The method comprises the steps that an ETL tool is constructed based on a big data analysis engine, and an ETL tool project supports a JBDC data source and does not support a JBDC data source. When data is read, for a data source supporting JBDC, data reading is achieved through a Spark JDBC interface, the data source is converted into DataFrame, and for a data source not supporting JBDC, data is directly read through a big data analysis engine. When data is written, for a data source supporting JBDC, the data is written into a target database by adopting a Spark JDBC interface, for a data source not supporting JBDC, the data is written into the target database in batches by adopting a ForeshPartion method, and when an ETL task is submitted through Spark YARN, resource configuration of the task is dynamically adjusted according to the real-time condition of YARN resources. According to the method, efficient access and export of the data can be realized, the execution efficiency of the ETL task is improved, and the resource configuration of the task is dynamically adjusted according to the field YARN resource, so that the task execution speed is optimized.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular, to a method, device, storage medium, and equipment for executing ETL tasks. Background Art

[0002] With the deepening of enterprise digital transformation, the demand for data integration is becoming increasingly diversified. Enterprises not only need to achieve cross-platform and cross-system data integration, but also need to support requirements such as real-time data integration, complex data conversion, and verification. However, some ETL tools have limitations in these aspects and cannot meet the diverse needs of enterprises.

[0003] The current ETL tools on the market play an important role in data integration and data processing, but there are also some deficiencies in multi-platform support and efficiency. For example: Different ETL tools may have compatibility issues on different operating systems or platforms. For instance, certain tools may be more suitable for the Windows environment, but may perform poorly on Linux or Mac OS. This compatibility difference may cause difficulties for users when migrating between different platforms. Another example is that some ETL tools may consume a large amount of system resources such as CPU, memory, and disk space during operation. This may lead to a decline in system performance and even affect the normal operation of other business applications. Especially in resource-constrained environments, this resource consumption problem may be more prominent, resulting in low efficiency during system operation. Summary of the Invention

[0004] In view of this, the present invention provides a method, device, storage medium, and equipment for executing ETL tasks, which can achieve efficient access and export of data, improve the execution efficiency of ETL tasks, and dynamically adjust the resource configuration of tasks according to the on-site YARN resources, thereby optimizing the task execution speed.

[0005] In a first aspect, an embodiment of the present invention provides a method for executing an ETL task, the method comprising:

[0006] Constructing an ETL tool based on a big data analysis engine, the ETL tool project supporting data sources that support JBDC and data sources that do not support JBDC;

[0007] When reading data, for data sources that support JBDC, using the Spark JDBC interface to implement data reading and converting it into a DataFrame, and for data sources that do not support JBDC, directly reading the data using the big data analysis engine;

[0008] When writing data, for data sources that support JDBC, use the Spark JDBC interface to write data to the target database. For data sources that do not support JDBC, use the foreachPartition method to batch write data to the target database;

[0009] When submitting an ETL task through Spark YARN, dynamically adjust the resource configuration of the task according to the real-time situation of YARN resources.

[0010] Furthermore, when building an ETL tool based on a big data analysis engine, adopt a modular design to centrally manage the functional modules of data access, data transformation, and data export.

[0011] Furthermore, the data sources that support JDBC in the ETL tool project include MySQL data source, Oracle data source, and PostgreSQL data source. The data sources that do not support JDBC in the ETL tool project include HDFS data source and Hive data source.

[0012] Furthermore, for data sources that support JDBC, using the Spark JDBC interface to read data and convert it into a DataFrame includes:

[0013] Add the JDBC driver to the big data analysis engine;

[0014] Configure the connection parameters of the JDBC driver. The connection parameters include connection information and the SQL statement to be queried. The connection information includes the URL, username, and password of the database;

[0015] Use the spark.read.jdbc() method to create a DataFrame according to the connection information and SQL statement.

[0016] Furthermore, for data sources that do not support JDBC, directly reading data using the big data analysis engine includes:

[0017] Directly read data using the dedicated connector provided by the big data analysis engine; or

[0018] Write custom data source reading logic to implement the InputFormat or Source interface of the big data analysis engine so that the big data analysis engine can identify and read data; or

[0019] Use an external tool to import data from a data source that does not support JDBC into an intermediate storage that supports JDBC, and then use the JDBC interface of the big data analysis engine to read the data.

[0020] Further, for data sources that support JDBC, writing data into the target database using the Spark JDBC interface includes:

[0021] Determine the connection information of the target database, where the connection information includes the JDBC URL, username, and password;

[0022] Read the source file of the data source that supports JBDC, perform data processing and transformation, and encapsulate the data to be written into a Spark DataFrame;

[0023] Use the jdbc() method of DataFrameWriter to write the data in the DataFrame into the target database.

[0024] Further, for data sources that do not support JDBC, batch writing data into the target database using the foreachPartition method includes:

[0025] Read the source file of the data source that does not support JBDC, perform data processing and transformation, and encapsulate the data to be written into a Spark DataFrame;

[0026] Write a writing function that accepts a partition of the DataFrame as input and batch writes the data in the partition into the target database;

[0027] Call the foreachPartition method on the DataFrame and pass in the writing function.

[0028] In a second aspect, an embodiment of the present invention provides an ETL task execution device, where the device includes:

[0029] A construction module for constructing an ETL tool based on a big data analysis engine, where the ETL tool project supports data sources that support JBDC and data sources that do not support JBDC;

[0030] A reading module for, when reading data, for data sources that support JBDC, implementing data reading using the Spark JDBC interface and converting it into a DataFrame, and for data sources that do not support JBDC, directly reading data using the big data analysis engine;

[0031] A writing module for, when writing data, for data sources that support JBDC, writing data into the target database using the Spark JDBC interface, and for data sources that do not support JBDC, batch writing data into the target database using the foreachPartition method;

[0032] An execution module, configured to dynamically adjust the resource configuration of a task according to the real-time situation of YARN resources when submitting an ETL task through Spark YARN.

[0033] In a third aspect, an embodiment of the present invention provides a computer-readable storage medium, in which a computer program is stored, and the computer program is configured to execute the method described in any item of the first aspect when running.

[0034] In a fourth aspect, an embodiment of the present invention provides an electronic device, including a memory and a processor, characterized in that a computer program is stored in the memory, and the processor is configured to run the computer program to execute the method described in any item of the first aspect.

[0035] The technical solutions provided by the embodiments of the present invention have the following advantages: In the first aspect, they have efficient data processing capabilities. Since they are based on a big data analysis engine architecture, they can utilize the multi-threaded task advantages of the big data analysis engine to significantly improve the execution efficiency of ETL tasks. At the same time, the big data analysis engine supports multi-threading and parallel processing, enabling full utilization of cluster resources to achieve efficient data access and export. In the second aspect, they have extensive data source access and support. This tool supports the access of multiple data sources such as HDFS, Hive, MySQL, Oracle, and PostgreSQL, meeting the data source selection needs of users in different scenarios. It also supports exporting data to multiple target databases such as MySQL, Oracle, and Elasticsearch, meeting different business needs and enhancing the flexibility and adaptability of the system. In the third aspect, they have a dynamic resource adjustment mechanism. According to the real-time situation of YARN resources, the resource configuration of a single ETL task is dynamically adjusted to optimize the task execution efficiency. This mechanism can ensure that tasks are executed quickly when resources are sufficient and resources are reasonably allocated when resources are scarce, avoiding resource waste and waiting. Users can simply modify the configuration as needed to adjust the number of resources used by the task to adapt to different operating environments and requirements, improving the flexibility and scalability of the system. In the fourth aspect, they are easy to maintain and expand. An ETL tool project based on a big data analysis engine is constructed to centrally manage data access and export functions, ensuring a clear and maintainable code structure. This design makes the system easy to expand and upgrade, facilitating the addition and optimization of subsequent functions. The data reading and writing mechanism adopts a modular design, making each module relatively independent and facilitating development and maintenance. In the fifth aspect, they have stability and reliability. By providing a unified interface and mechanism, the development burden caused by driver replacement is reduced, and the risk of system errors is lowered. The foreachPartition is used to implement data writing, and through an efficient batch processing mechanism, fast data export is achieved, improving the writing efficiency and ensuring data integrity and consistency. Description of the Drawings

[0036] Figure 1 is the flowchart of the ETL task execution method provided in the first embodiment of the present invention;

[0037] Figure 2 is the structural schematic diagram of the ETL task execution device provided in the second embodiment of the present invention;

[0038] Figure 3 is the structural schematic diagram of the electronic device provided in the third embodiment of the present invention. Detailed implementation manners

[0039] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0040] Embodiment 1

[0041] See Figure 1 , Figure 1 is the flowchart of the ETL task execution method provided in an embodiment of the present invention, and the method includes the following steps:

[0042] Step 11: Build an ETL tool based on a big data analysis engine, and the ETL tool project supports data sources that support JBDC and data sources that do not support JBDC.

[0043] ETL is the abbreviation of Extract-Transform-Load, and the corresponding Chinese for ETL is extraction-transformation-loading. These three stages respectively correspond to extracting data from a data source (Extract), cleaning and transforming the data to meet the requirements of the target system (Transform), and loading the processed data into the target system or data warehouse (Load). ETL is a widely used process in the fields of data warehousing and data integration, and it is crucial for building and maintaining a data warehouse.

[0044] In this step, when building an ETL tool based on a big data analysis engine, modular design is adopted to centrally manage the functional modules of data access, data transformation, and data export. The data sources that the ETL tool project supports for JBDC include MySQL data source, Oracle data source, and PostgreSQL data source, and the data sources that the ETL tool project does not support for JBDC include HDFS data source and Hive data source.

[0045] In specific implementation, an ETL tool can be constructed, which is capable of processing multiple data sources. Spark is selected as the big data analysis engine (in the embodiments of the specification, the big data analysis engine is exemplified by Spark), because it provides powerful data processing capabilities, distributed computing capabilities, and rich APIs. Design the project structure of the ETL tool, including a data access layer, a data processing layer, and a data export layer. Ensure that the ETL tool can support multiple data sources, including databases (such as MySQL, Oracle, etc., which generally support JDBC), file storage systems (such as HDFS, which does not support JDBC but can be directly read through Spark), etc.

[0046] Step 12: When reading data, for data sources that support JBDC, use the Spark JDBC interface to implement data reading and convert it into a DataFrame. For data sources that do not support JBDC, directly read the data using the big data analysis engine.

[0047] In this step, data is read from data sources that support JDBC and data sources that do not support JDBC. Specifically, for data sources that support JDBC, the following steps can be used to implement data reading using the Spark JDBC interface and convert it into a DataFrame:

[0048] Step 121: Add the JDBC driver to the big data analysis engine.

[0049] The purpose of this step is to enable Spark to recognize and use the JDBC driver to connect to the database. In specific implementation, the JDBC driver JAR file corresponding to the target database can be downloaded and placed in the classpath of Spark. Among them, placing the JDBC driver JAR file in the classpath of Spark can be achieved in the following ways: when submitting a Spark job, specify the location of the JAR file through the --jars option; place the JAR file in the specified directory of each node in the Spark cluster (such as $SPARK_HOME / jars), and Spark will automatically load it when starting. If using Spark Shell or Spark SQL CLI, the JAR file can be placed in a specific directory before starting and the directory can be specified through a configuration option.

[0050] Step 122: Configure the connection parameters of the JDBC driver. The connection parameters include connection information and the SQL statement to be queried. The connection information includes the URL, username, and password of the database.

[0051] This step is to provide the detailed information required for connecting to the target database, including the database URL, username, password, and the SQL query statement to be executed.

[0052] This can be achieved by creating a configuration object or dictionary (in Python) or a Java properties object (in Java / Scala) that contains the connection information.

[0053] Among them, the connection information should include:

[0054] url: The JDBC URL of the database, which specifies how to connect to the database (e.g., jdbc:mysql: / / hostname:port / dbname).

[0055] user: The username used for database authentication.

[0056] password: The password used for database authentication.

[0057] It is also necessary to specify the SQL query statement to be executed and pass it as another parameter to the spark.read.jdbc() method.

[0058] Step 123: Use the spark.read.jdbc() method to create a DataFrame based on the connection information and SQL statement.

[0059] This step is to read data from the database using a JDBC connection and convert it into a Spark DataFrame for further processing.

[0060] Call the spark.read.jdbc() method and pass the following parameters:

[0061] url: The JDBC URL of the database (the same as the url in step 122).

[0062] table: In this embodiment, instead of directly specifying the table name, a SQL query statement is passed as the table name, or the query is passed as the query parameter, depending on the version of Spark and changes in the API).

[0063] columnNames (optional): Specify the column names to be selected from the query result. If not specified, all columns are selected.

[0064] properties: A dictionary or Java properties object containing connection properties, including the username, password, etc. (which have been configured in step 122).

[0065] For data sources that do not support JBDC, the direct reading of data by using a big data analysis engine can be achieved through the following methods:

[0066] Method a: Directly read data by using the dedicated connectors provided by the big data analysis engine.

[0067] Many big data analysis engines, including Apache Spark, provide dedicated connectors for connecting to and reading different types of data sources. These connectors are usually developed by the community or commercial vendors and have been integrated into the ecosystem of the big data analysis engine.

[0068] For Method a, first, check whether there is a dedicated connector suitable for the specific data source type. These connectors may be provided as part of the big data analysis engine or as a separate library or framework. Second, add the dependencies of the connector to the project (code library, build system (such as Maven, Gradle, or SBT, etc.), development environment (such as integrated development environment (IDE), version control system (such as Git)), or runtime environment (such as local computer, cluster node, cloud service, etc.)). Then, according to the documentation of the connector, configure the necessary connection parameters and options. This may include the location of the data source, authentication information, data reading format, etc. Finally, use the API provided by the big data analysis engine and the connector to read the data. This usually involves calling a specific reading method or function and passing the configured connection parameters.

[0069] Method b: Write custom data source reading logic to implement the InputFormat or Source interface of the big data analysis engine so that the big data analysis engine can identify and read the data.

[0070] If there is no ready-made connector available, you can write custom data source reading logic to enable the engine to identify and read the data by implementing the interfaces provided by the big data analysis engine.

[0071] For Method b, first determine the interfaces provided by the big data analysis engine for data source reading (such as the InputFormat or Source interface) when implementing. These interfaces define the methods and functions that must be implemented. Write a class to implement these interfaces. In this class, implement the necessary methods and functions to read data from the data source. Write a class to implement these interfaces. In this class, implement the necessary methods and functions to read data from the data source. In the data analysis application, use the API provided by the big data analysis engine to read the data and specify the custom data source.

[0072] Method c: Use an external tool to import data from a data source that does not support JDBC into an intermediate storage that supports JDBC, and then use the JDBC interface of the big data analysis engine to read the data.

[0073] If the data source does not support JDBC, but you can import the data into an intermediate storage that supports JDBC (such as a relational database or a data warehouse), then you can use the JDBC interface of the big data analysis engine to read the data.

[0074] For Method c, that is, in some cases, it may be necessary to use an external tool (such as Apache Sqoop for data transfer between Hadoop and relational databases) to import data from a data source that does not support JDBC into an intermediate storage that supports JDBC (such as HDFS or Hive), and then use the JDBC interface of Spark to read this data.

[0075] For example: First, select an intermediate storage that supports JDBC and suits your needs. This can be a relational database (such as MySQL, PostgreSQL, etc.) or a data warehouse (such as Hive, Redshift, etc.). Then, use an external tool (such as an ETL tool, a data migration tool, etc.) to import the data from the original data source into the intermediate storage. Configure the JDBC connection on the intermediate storage, including the database URL, username, password, etc. Finally, in the big data analysis engine, use the JDBC interface to connect to the intermediate storage and read the data (for example, you can use the JDBC support of Spark to execute SQL queries and obtain the result set).

[0076] Step 13: When writing data, for a data source that supports JBDC, use the Spark JDBC interface to write the data to the target database. For a data source that does not support JBDC, use the foreachPartition method to write the data to the target database in batches.

[0077] In the scenario of processing big data writing to different databases, different strategies can be adopted according to whether the data source supports the JDBC interface. The following is a detailed description of these two cases:

[0078] If the target database supports JDBC (Java Database Connectivity), you can directly use the JDBC interface provided by Spark to write the data. The advantage of this method is simplicity, efficiency, and the ability to utilize Spark's parallel processing power to accelerate data writing.

[0079] When writing data in step 13, for data sources that support JDBC, the Spark JDBC interface is used to write data into the target database, which can be achieved in the following ways:

[0080] Step 131: Determine the connection information of the target database, and the connection information includes the JDBC URL, username, and password.

[0081] In this step, it is necessary to configure the connection information of the database, including URL, username, password, etc.

[0082] Step 132: Read the source file of the data source that supports JBDC, perform data processing and transformation, and encapsulate the data to be written into a Spark DataFrame.

[0083] In this step, use the write method of Spark DataFrame and specify format("jdbc") to indicate that we want to write into a JDBC database. Then, set the name of the database table, connection properties, and write mode (such as append, overwrite, etc.).

[0084] Step 133: Use the jdbc() method of DataFrameWriter to write the data in the DataFrame into the target database.

[0085] In this step, call the save() method to execute the write operation.

[0086] For data sources that do not support JDBC, the foreachPartition method needs to be used to write data into the target database. The foreachPartition method is a way provided by Spark to process data partitions, which allows custom processing of data for each partition. In this case, the foreachPartition method can be used to batch write data into the target database.

[0087] When writing data in step 13, for data sources that do not support JBDC, the foreachPartition method is used to batch write data into the target database, which can be achieved in the following ways:

[0088] Step 134: Read the source file of the data source that does not support JBDC, perform data processing and transformation, and encapsulate the data to be written into a Spark DataFrame.

[0089] In this step, process the data of the data source into a DataFrame (the implementation process can be understood by referring to step 132).

[0090] Step 135: Write a write function that takes a partition of a DataFrame as input and batch writes the data of this partition to the target database.

[0091] In this step, write a function that takes a partition of a DataFrame (usually an RDD) as input and batch writes the data of this partition to the target database. This may require using the client library or API provided by the database.

[0092] Step 136: Call the foreachPartition method on the DataFrame and pass in the said write function.

[0093] In this step, call the foreachPartition method on the DataFrame and pass in the defined write function.

[0094] Step 14: When submitting an ETL task through Spark on YARN, dynamically adjust the resource configuration of the task according to the real-time situation of YARN resources.

[0095] In this step, when submitting an ETL (Extract, Transform, Load) task through YARN in Spark, some strategies can be adopted to dynamically adjust the resource configuration of the Spark task according to the real-time situation of YARN resources.

[0096] For example, a monitoring and warning system can be established to track the resource usage of the YARN cluster in real time (such as available memory, number of CPU cores, etc.). When resources are tight, the system can send a warning to prompt that it may be necessary to adjust the resource configuration of the running Spark task.

[0097] For example, establish a dynamic resource allocation mechanism. Spark supports Dynamic Resource Allocation, which allows Spark to dynamically increase or decrease the number of executors according to the workload.

[0098] For example, use advanced resource management tools such as Kubernetes, which provides more flexible and dynamic resource management capabilities. Running Spark tasks on Kubernetes (such as using Spark on Kubernetes) can more easily dynamically adjust the resource configuration of Pods according to resource requirements.

[0099] For example, resource reservation and limitation. In YARN, set resource reservation and limitation for different queues or applications. By reasonably setting these parameters, resource contention can be avoided to a certain extent and sufficient resources can be provided for important Spark tasks.

[0100] In the technical solution provided by the embodiment of the present invention, on the first hand, it has efficient data processing capabilities. Since it is based on the big data analysis engine architecture, it can utilize the multi-threaded task advantages of the big data analysis engine to significantly improve the execution efficiency of ETL tasks. At the same time, the big data analysis engine supports multi-threading and parallel processing, and can make full use of cluster resources to achieve efficient data access and export. On the second hand, it has extensive data source access and support. This tool supports the access of multiple data sources such as HDFS, Hive, MySQL, Oracle, and PostgreSQL, meeting the data source selection needs of users in different scenarios. It supports exporting data to multiple target databases such as MySQL, Oracle, and Elasticsearch, meeting different business needs and enhancing the flexibility and adaptability of the system. On the third hand, it has a dynamic resource adjustment mechanism. According to the real-time situation of YARN resources, it dynamically adjusts the resource configuration of a single ETL task to optimize the task execution efficiency. This mechanism can ensure that tasks are executed quickly when resources are sufficient, and resources are reasonably allocated when resources are scarce, avoiding resource waste and waiting. Users can simply modify the configuration according to their needs to adjust the number of resources used by the task to adapt to different operating environments and requirements, improving the flexibility and scalability of the system. On the fourth hand, it is easy to maintain and expand. An ETL tool project based on the big data analysis engine is built to centrally manage data access and export functions, ensuring that the code structure is clear and maintainable. This design makes the system easy to expand and upgrade, facilitating the addition and optimization of subsequent functions. The data reading and writing mechanism adopts a modular design, making each module relatively independent, facilitating development and maintenance. On the fifth hand, it has stability and reliability. By providing a unified interface and mechanism, it reduces the development burden caused by driver replacement and reduces the risk of system errors. It uses foreachPartition to implement data writing, and through an efficient batch processing mechanism, it achieves fast data export, improving the writing efficiency and ensuring the integrity and consistency of data.

[0101] Embodiment 2

[0102] See Figure 2 , Figure 2 which is a schematic structural diagram of the ETL task execution device provided by the second embodiment of the present invention. The device includes:

[0103] A construction module 21, configured to build an ETL tool based on a big data analysis engine, where the ETL tool project supports data sources with JBDC and data sources without JBDC.

[0104] The reading module 22, when reading data, for data sources that support JDBC, uses the Spark JDBC interface to implement data reading and converts it into a DataFrame. For data sources that do not support JDBC, the big data analysis engine is used to directly read the data;

[0105] The writing module 23, when writing data, for data sources that support JDBC, uses the Spark JDBC interface to write the data into the target database. For data sources that do not support JDBC, the foreachPartition method is used to batch-write the data into the target database;

[0106] The execution module 24, when submitting an ETL task through Spark YARN, dynamically adjusts the resource configuration of the task according to the real-time situation of the YARN resources.

[0107] In some embodiments of the present invention, when the construction module 21 constructs an ETL tool based on a big data analysis engine, a modular design is adopted to centrally manage the functional modules of data access, data conversion, and data export. The data sources supported by the ETL tool project that support JDBC include MySQL data sources, Oracle data sources, and PostgreSQL data sources. The data sources that do not support JDBC in the ETL tool project include HDFS data sources and Hive data sources.

[0108] In some embodiments of the present invention, the reading module 22 may include:

[0109] The adding unit 221 is used to add a JDBC driver to the big data analysis engine;

[0110] The configuration unit 222 is used to configure the connection parameters of the JDBC driver. The connection parameters include connection information and the SQL statement to be queried. The connection information includes the URL, username, and password of the database;

[0111] The creating unit 223 is used to use the spark.read.jdbc() method to create a DataFrame according to the connection information and the SQL statement.

[0112] In some embodiments of the present invention, when the reading module 22 directly reads data from a data source that does not support JDBC using the big data analysis engine, it can be implemented in the following ways:

[0113] Directly read the data using the dedicated connector provided by the big data analysis engine; or

[0114] Write custom data source reading logic to implement the InputFormat or Source interface of the big data analysis engine, so that the big data analysis engine can identify and read the data; or

[0115] Use an external tool to import data from a data source that does not support JDBC into an intermediate storage that supports JDBC, and then use the JDBC interface of the big data analysis engine to read the data.

[0116] In some embodiments of the present invention, the writing module 23 may include:

[0117] A determination unit 231 for determining connection information of the target database, where the connection information includes a JDBC URL, a username, and a password;

[0118] A first reading unit 232 for reading the source file of the data source that supports JBDC, performing data processing and conversion, and encapsulating the data to be written into a SparkDataFrame;

[0119] A first writing unit 233 for writing the data in the DataFrame into the target database by using the jdbc() method of DataFrameWriter.

[0120] A second reading unit 234 for reading the source file of the data source that does not support JBDC, performing data processing and conversion, and encapsulating the data to be written into a SparkDataFrame;

[0121] A writing unit 235 for writing a writing function that accepts a partition of a DataFrame as input and batch-writes the data of the partition into the target database;

[0122] A second writing unit 236 for calling the foreachPartition method on the DataFrame and passing in the writing function.

[0123] Therefore, the technical solution provided by the embodiments of the present invention, in the first aspect, has efficient data processing capabilities. Since it is based on the big data analysis engine architecture, it can utilize the multi-threaded task advantages of the big data analysis engine to significantly improve the execution efficiency of ETL tasks. At the same time, the big data analysis engine supports multi-threading and parallel processing, and can make full use of cluster resources to achieve efficient data access and export; in the second aspect, it has extensive data source access and support. This tool supports the access of multiple data sources such as HDFS, Hive, MySQL, Oracle, PostgreSQL, etc., meeting the data source selection needs of users in different scenarios, and supports exporting data to multiple target databases such as MySQL, Oracle, Elasticsearch, etc., meeting different business needs and enhancing the flexibility and adaptability of the system; in the third aspect, it has a dynamic resource adjustment mechanism. According to the real-time situation of YARN resources, it dynamically adjusts the resource configuration of a single ETL task to optimize the task execution efficiency. This mechanism can ensure that tasks are executed quickly when resources are sufficient, and allocate resources reasonably when resources are tight, avoiding resource waste and waiting. Users can simply modify the configuration according to their needs to adjust the number of resources used by the task to adapt to different operating environments and requirements, improving the flexibility and scalability of the system; in the fourth aspect, it is easy to maintain and expand. An ETL tool project based on the big data analysis engine is constructed to centrally manage data access and export functions, ensuring that the code structure is clear and maintainable. This design makes the system easy to expand and upgrade, facilitating the addition and optimization of subsequent functions. The data reading and writing mechanism adopts a modular design, making each module relatively independent, facilitating development and maintenance; in the fifth aspect, it has stability and reliability. By providing a unified interface and mechanism, it reduces the development burden caused by driver replacement and reduces the risk of system errors. It uses foreachPartition to implement data writing, and through an efficient batch processing mechanism, it realizes the fast export of data, improving the writing efficiency and ensuring the integrity and consistency of data.

[0124] It should be noted that the ETL task execution device in the embodiments of the present invention and the ETL task execution method in the above embodiments belong to the same inventive concept. The technical details not described in detail in this device can be referred to the relevant descriptions of the method above and will not be elaborated here.

[0125] In addition, the embodiments of the present invention also provide a computer-readable storage medium, in which a computer program is stored. Among them, the computer program is set to execute the method described above when running.

[0126] Figure 3The schematic structural diagram of the electronic device 10 that can be used to implement the embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are only examples and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0127] As Figure 3 shown, the electronic device 10 includes at least one processor 11 and a memory communicatively connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc. The memory stores a computer program executable by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 into the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. The input / output (I / O) interface 15 is also connected to the bus 14.

[0128] Multiple components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disc, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0129] The processor 11 can be various general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as the idle detection method.

[0130] In some embodiments, the idle detection method may be implemented as a computer program tangibly embodied in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed onto the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the idle detection method described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to perform the idle detection method by any other suitable means (e.g., by means of firmware).

[0131] The various embodiments of the systems and techniques described above in this document may be implemented in digital electronic circuitry, integrated circuit systems, field programmable gate arrays (FPGA), application specific integrated circuits (ASIC), application specific standard products (ASSP), systems on a chip (SOC), complex programmable logic devices (CPLD), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include: implemented in one or more computer programs that may be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a special-purpose or general-purpose programmable processor that receives data and instructions from a storage system, at least one input device, and at least one output device, and transmits the data and instructions to the storage system, the at least one input device, and the at least one output device.

[0132] The computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus, such that the computer programs, when executed by the processor, cause the functions / operations specified in the flowchart and / or block diagram to be implemented. The computer programs may be executed entirely on the machine, partly on the machine, as a stand-alone software package partly on the machine and partly on a remote machine, or entirely on the remote machine or server.

[0133] In the context of the present invention, a computer-readable storage medium can be a tangible medium that can contain or store a computer program for use by or in connection with an instruction execution system, apparatus, or device. The computer-readable storage medium can include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, the computer-readable storage medium can be a machine-readable signal medium. More specific examples of the machine-readable storage medium would include an electrical connection based on one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0134] To provide for interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the electronic device. Other kinds of devices can also be used to provide for interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic, speech, or tactile input).

[0135] The systems and techniques described herein can be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer having a graphical user interface or a web browser through which the user can interact with an implementation of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0136] A computing system may include a client and a server. The client and the server are generally far from each other and usually interact via a communication network. The client-server relationship is created by computer programs running on respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or a cloud host, which is a host product in the cloud computing service system, solving the defects of difficult management and weak business scalability existing in traditional physical hosts and VPS services.

[0137] It should be understood that various forms of the processes shown above can be used, steps can be reordered, added or deleted. For example, the steps recited in the present invention can be executed in parallel, sequentially or in a different order, as long as the desired results of the technical solution of the present invention can be achieved, and no limitation is made herein.

[0138] The above specific embodiments do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. A method for executing an ETL task, characterized in that: The method comprises: Building an ETL tool based on a big data analysis engine, wherein the ETL tool project supports JBDC data sources and data sources that do not support JBDC; When reading data, for data sources that support JBDC, the Spark JDBC interface is used to read the data and convert it into DataFrame. For data sources that do not support JBDC, the big data analysis engine is used to directly read the data. When writing data, for data sources that support JBDC, the Spark JDBC interface is used to write data to the target database. For data sources that do not support JBDC, the foreachPartition method is used to write data in batches to the target database. When submitting ETL tasks through SparkYARN, the resource configuration of the task is dynamically adjusted according to the real-time situation of YARN resources.

2. The method according to claim 1, characterized in that When building ETL tools based on the big data analysis engine, a modular design is adopted to centrally manage the functional modules of data access, data conversion and data export.

3. The method according to claim 1, characterized in that The data sources supported by the ETL tool project for JBDC include MySQL data source, Oracle data source, and PostgreSQL data source, and the data sources not supported by the ETL tool project for JBDC include HDFS data source and Hive data source.

4. The method according to claim 1, characterized in that: For data sources that support JBDC, the Spark JDBC interface is used to read data and convert it into a DataFrame, including: Adding a JDBC driver to the big data analysis engine; Configure the connection parameters of the JDBC driver, the connection parameters including connection information and the SQL statement to be queried, the connection information including the URL of the database, the user name, and the password; Use the spark.read.jdbc() method to create a DataFrame based on the connection information and SQL statement.

5. The method according to claim 1, characterized in that For data sources that do not support JBDC, using the big data analysis engine to directly read data includes: Use the dedicated connector provided by the big data analysis engine to read the data directly; or Write custom data source reading logic to implement the InputFormat or Source interface of the big data analysis engine so that the big data analysis engine can recognize and read data; or Use external tools to import data from data sources that do not support JDBC into intermediate storage that supports JDBC, and then use the JDBC interface of the big data analysis engine to read the data.

6. The method according to claim 1, characterized in that For data sources that support JBDC, using the Spark JDBC interface to write data to the target database includes: Determine the connection information of the target database, the connection information including JDBC URL, user name, and password; Read the source file of the data source supporting JBDC, perform data processing and conversion, and encapsulate the data to be written into SparkDataFrame; Use the jdbc() method of DataFrameWriter to write the data in the DataFrame to the target database.

7. The method according to claim 1, characterized in that For data sources that do not support JBDC, the foreachPartition method is used to write data in batches to the target database, including: Read the source file of the data source that does not support JBDC, perform data processing and conversion, and encapsulate the data to be written into a Spark DataFrame; Write a write function that accepts a partition of a DataFrame as input and writes the data of that partition in batches to the target database; Call the foreachPartition method on the DataFrame and pass in the write function.

8. A device for executing ETL tasks, characterized in that: The device comprises: A construction module is used to build an ETL tool based on a big data analysis engine, wherein the ETL tool project supports data sources of JBDC and data sources that do not support JBDC; The reading module is used to read data. For data sources that support JBDC, the Spark JDBC interface is used to read data and convert it into DataFrame. For data sources that do not support JBDC, the big data analysis engine is used to directly read data. The writing module is used to write data. For data sources that support JBDC, the Spark JDBC interface is used to write data to the target database. For data sources that do not support JBDC, the foreachPartition method is used to write data in batches to the target database. The execution module is used to dynamically adjust the resource configuration of the task according to the real-time situation of YARN resources when submitting ETL tasks through SparkYARN.

9. A computer-readable storage medium, characterized in that: The storage medium stores a computer program, wherein the computer program is configured to execute the method according to any one of claims 1 to 7 when executed.

10. An electronic device comprising a memory and a processor, characterized in that: A computer program is stored in the memory, and the processor is configured to run the computer program to perform the method according to any one of claims 1 to 7.

Images