A method and apparatus for improving data ETL performance

Abstract

This invention discloses a method and apparatus for improving data ETL performance. The method includes: using the ClickHouse-Local tool provided by ClickHouse, and defining custom data processing functions based on the tool's source code; using the custom data processing functions to process data and handle exceptions using the CPU's SIMD instruction set, generating a data file in a big data platform format, writing it to the target storage, and moving it to a Hive table; if the Hive table has partitions, refreshing the Hive table partitions. This method and apparatus, using the ClickHouse-Local tool and extending the custom functions based on its source code, utilizes automatic code generation and combines it with a vectorized execution engine to achieve high-performance data processing operations.

Description

Technical Field

[0001] This invention relates to the field of data ETL performance, and in particular to a method and apparatus for improving data ETL performance. Background Technology

[0002] The main performance bottlenecks of traditional data ETL operations using Spark or Flink are as follows:

[0003] (1) JVM languages ​​based on Java / Scala are used to convert, clean and parse data (such as JSON). Compared with C / C++, they are time-consuming and memory-intensive. When processing very large records, they are prone to OOM.

[0004] (2) Traditional data processing, data conversion or function calls are broken down into individual operators, and then branch judgment and calculation are performed. Each piece of data has to call an operator once, which is very time-consuming when the amount of data is large.

[0005] like Figure 1 The image shows a comparison of computing performance across different computer storage media. From left to right, the computing response speed decreases progressively. Data processing performance is highest in the CPU (registers). Accessing data in the CPU (registers) is 300 times faster than accessing data in memory and 30 million times faster than accessing data on a disk.

[0006] (3) Traditional data processing cannot achieve automatic runtime code generation.

[0007] like Figure 2 As shown, the left side represents the underlying data processing model for function calls in a traditional database management system. Here, the function expression is broken down into individual operators, followed by branching and evaluation. The right side represents runtime code generation, which first converts the expression into corresponding code, then compiles and executes it. This automatic code generation method eliminates a large number of operator calls and avoids if-else branches. Summary of the Invention

[0008] To address the aforementioned problems in existing technologies, this invention provides a method and apparatus for improving data ETL performance. It utilizes the ClickHouse-Local binary tool provided by ClickHouse, and extends its source code with custom functions to achieve data processing and fault tolerance. Leveraging its vectorized execution engine and automatic code generation capabilities, it processes the data, generating Hive table-formatted files in HDFS, moving them to the Hive table, and refreshing the Hive table partitions (if any). The vectorized execution engine utilizes the SIMD instruction set, enabling parallel operations. For data performing the same operation, a single SIMD instruction can process multiple data entries simultaneously. Extensive optimizations have been made at the CPU (register) level, resulting in high-performance data processing capabilities.

[0009] To achieve the above objectives, the present invention adopts the following technical solution:

[0010] In one embodiment of the present invention, a method for improving data ETL performance is proposed, the method comprising:

[0011] We can use the Clickhouse-Local tool provided by ClickHouse and customize data processing functions based on its source code.

[0012] By using custom data processing functions and the CPU's SIMD instruction set, the data is processed and fault-tolerant, generating a data file in the format of a big data platform, which is written to the target storage and moved to a Hive table. If the Hive table has partitions, the Hive table partitions are refreshed.

[0013] Furthermore, for data that cannot be handled due to anomalies, filtering can be performed using custom data processing functions or the data can be stored in a separate field.

[0014] Furthermore, a pipeline is created for each partition of the distributed dataset to process data using the CPU's SIMD instruction set, and a materialized view is created in the pipeline of each partition, specifying the HDFS engine base table; the data in each partition of the distributed dataset exists in the form of iterators.

[0015] Furthermore, the rules for creating materialized views are as follows:

[0016] The creation statement for a materialized view uses SQL to perform data processing after the AS keyword;

[0017] A custom data processing function processes the input data and returns two columns: the first column contains unprocessed exception data, and the second column contains normally processed data, returned in a nested format.

[0018] Filter data using WHERE clauses;

[0019] Following the select statement, the data of the nested fields is expanded, and the expanded nested field data is processed using any ClickHouse function. The processed data is returned and stored in the HDFS engine base table.

[0020] The data source for temporary tables is standard input.

[0021] When data is inserted into a temporary table, the materialized view automatically performs data processing and writes the processed data into the HDFS engine base table.

[0022] Furthermore, the creation rules for the HDFS engine base tables are as follows:

[0023] The base table uses HDFS as its table engine, and the HDFS engine allows you to specify the HDFS path and file format for data storage.

[0024] When creating the base table, the field names and field data types must be consistent with the fields and field data types returned by the SQL logic of the materialized view;

[0025] The HDFS path for data storage is used as a temporary directory and is identified by a partition number, which is dynamically adjusted based on the partition it belongs to.

[0026] In one embodiment of the present invention, an apparatus for improving data ETL performance is also provided, the apparatus comprising:

[0027] The function definition module is used to define custom data processing functions based on the Clickhouse-Local tool provided by ClickHouse and its source code.

[0028] The data processing module is used to process data and perform fault tolerance using the CPU's SIMD instruction set through custom data processing functions. It generates data files in the format of a big data platform, writes them to the target storage, and moves them to the Hive table. If the Hive table has partitions, it refreshes the Hive table partitions.

[0029] Furthermore, for data that cannot be handled due to anomalies, filtering can be performed using custom data processing functions or the data can be stored in a separate field.

[0030] Furthermore, a pipeline is created for each partition of the distributed dataset to process data using the CPU's SIMD instruction set, and a materialized view is created in the pipeline of each partition, specifying the HDFS engine base table; the data in each partition of the distributed dataset exists in the form of iterators.

[0031] Furthermore, the rules for creating materialized views are as follows:

[0032] The creation statement for a materialized view uses SQL to perform data processing after the AS keyword;

[0033] A custom data processing function processes the input data and returns two columns: the first column contains unprocessed exception data, and the second column contains normally processed data, returned in a nested format.

[0034] Filter data using WHERE clauses;

[0035] Following the select statement, the data of the nested fields is expanded, and the expanded nested field data is processed using any ClickHouse function. The processed data is returned and stored in the HDFS engine base table.

[0036] The data source for temporary tables is standard input.

[0037] When data is inserted into a temporary table, the materialized view automatically performs data processing and writes the processed data into the HDFS engine base table.

[0038] Furthermore, the creation rules for the HDFS engine base tables are as follows:

[0039] The base table uses HDFS as its table engine, and the HDFS engine allows you to specify the HDFS path and file format for data storage.

[0040] When creating the base table, the field names and field data types must be consistent with the fields and field data types returned by the SQL logic of the materialized view;

[0041] The HDFS path for data storage is used as a temporary directory and is identified by a partition number, which is dynamically adjusted based on the partition it belongs to.

[0042] In one embodiment of the present invention, a computer device is also provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to achieve the aforementioned improvement in data ETL performance.

[0043] In one embodiment of the present invention, a computer-readable storage medium is also provided, which stores a computer program that performs an action to improve data ETL performance.

[0044] Beneficial effects:

[0045] 1. This invention utilizes the rich ecosystem of Spark or Flink to access data sources, and data processing is achieved through a vectorized execution engine, thus realizing high-performance data processing.

[0046] 2. This invention has fault tolerance for data that cannot be processed.

[0047] 3. This invention is lightweight and easy to use. It does not require the deployment of Clickhouse-Server. The data processing logic is implemented through custom functions of the Clickhouse-Local tool. In the Spark / Flink code, you only need to pass the data to the Clickhouse-Local tool through standard input to complete the processing. Attached Figure Description

[0048] Figure 1 This is a comparison chart of the computing performance of different computer storage media;

[0049] Figure 2 This is a comparison chart of traditional function calls and automatically generated code;

[0050] Figure 3 This is a schematic diagram of the method for improving data ETL performance according to the present invention;

[0051] Figure 4 This is a schematic diagram of the device structure for improving data ETL performance according to the present invention;

[0052] Figure 5 This is a schematic diagram of the computer device structure of the present invention. Detailed Implementation

[0053] The principles and spirit of the present invention will now be described with reference to several exemplary embodiments. It should be understood that these embodiments are provided merely to enable those skilled in the art to better understand and implement the present invention, and are not intended to limit the scope of the present invention in any way. Rather, these embodiments are provided to make this disclosure more thorough and complete, and to fully convey the scope of this disclosure to those skilled in the art.

[0054] Those skilled in the art will recognize that embodiments of the present invention can be implemented as an apparatus, device, node, method, or computer program product. Therefore, this disclosure can be specifically implemented in the following forms: entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0055] The present invention proposes a method and apparatus for improving data ETL performance. It utilizes the Clickhouse-Local tool provided by ClickHouse and extends the tool's source code with custom functions to achieve data processing and fault tolerance. It also leverages the tool's vectorized execution engine and automatic code generation capabilities to process the data, generate data files in the format of a big data platform in HDFS, move the data to a Hive table, and refresh the Hive table partitions (if partitions exist).

[0056] The principles and spirit of the present invention will be explained in detail below with reference to several representative embodiments.

[0057] Figure 3 This is a schematic diagram of the method for improving data ETL performance according to the present invention. Figure 1 As shown, the method includes:

[0058] 1. Implement the data processing functions of the vectorized execution engine.

[0059] ClickHouse provides a binary tool called Clickhouse-Local, which can be understood as a standalone version of ClickHouse, but it can be used without starting any services. Its features include:

[0060] (a) Implements most of the functionality of ClickHouseServer, such as table engine, functions, and view statements.

[0061] (b) It does not depend on ClickHouseServer and can run independently.

[0062] (1) Here is an example of using ClickHouse-Local:

[0063] echo "a\nb\nc\n"|. / clickhouse-local-S"nameString"-Ntmp_table-q"createtablet_batch(nameString,create_time Date)ENGINE=HDFS('hdfs: / / ns:8020 / data'); insertintot_batch selectname,'2022-11-06'fromtmp_table".

[0064] In the above statement, three data entries a, b, and c are first constructed using the echo statement, connected by a newline character (\n). Then, the field name is specified as 'name' using -S, and the temporary table 'tmp_table' is specified using -N. This is equivalent to 'tmp_table' having only one field 'name', as shown in Table 1 below:

[0065] Table 1

[0066] name a b c

[0067] The base table `t_batch` is created using the `-q` option, with HDFS as the storage engine and the specified HDFS storage path. Then, an `INSERT` statement is used to insert data from the temporary table `tmp_table` into the base table `t_batch`. Due to Clickhouse-Local's own mechanism, it exits after execution, at which point all tables (`tmp_table` and `t_batch`) are cleared, but the data is stored as files in the HDFS directory where the base table `t_batch` resides.

[0068] (2) Data processing functions of the vectorized execution engine

[0069] The ClickHouse-Local tool is open-source, allowing users to define custom data processing functions. These functions receive standard input data, process each input, and generate data files in formats compatible with big data platforms like Hive, such as ORC or Parquet. It's highly lightweight, requiring no ClickHouse Server deployment or configuration. Furthermore, the custom data processing functions can filter or store exceptional data in a dedicated field.

[0070] Because ClickHouse uses a vectorized execution engine, leveraging the CPU's SIMD (single instruction multiple data) instruction set, it can perform parallel operations. For data performing the same operation, a single SIMD instruction can process multiple data points simultaneously. For example, when extracting strings from a batch of data, a single SIMD instruction can complete the string extraction operation for multiple data points, instead of calling the string extraction function for each data point individually. In contrast, functions using Spark or Flink can only process one data point per instruction.

[0071] By using the ClickHouse-Local tool provided by ClickHouse and customizing data processing functions based on its source code, the tool has the capability of a vectorized execution engine, which can greatly improve the efficiency of data processing.

[0072] Custom data processing functions should have the following capabilities: They should process the input data and transform it into data with multiple fields. For example, if there is only one field, a, b, and c, splitting it by commas will result in three strings, which can then correspond to three fields. For abnormal data that cannot be processed, a dedicated field (e.g., error) should be used for separate storage.

[0073] For example, for JSON formatted data, a custom data processing function can be defined according to the following rules. The custom data processing function receives the JSON string to be processed, maps the table's schema field to the JSON string, and outputs a tuple containing two elements: the first element is the error string that cannot be processed, and the second element is the data that can be processed normally. The normally processed data is processed using the passed schema to generate nested table fields.

[0074] For example, a custom data processing function named `parse_with_errors` takes two parameters: the JSON field to be processed and the table's schema field. The data processing function is defined as follows:

[0075] parse_with_errors(line,{schema}).

[0076] Where line is the name of the field to be processed, and {schema} is the definition of the schema. Assume the schema is defined as: {"schema":[{"field":"id","type":"Int32"},{"field":"name","type":"String"},{"field":"create_time","type":"DateTime"}]}.

[0077] In the schema definition above, field names are defined by field and data types are specified by type.

[0078] A total of three fields are defined: the data type of the id field is Int32, the data type of the name field is String, and the data type of the create_time field is DateTime.

[0079] The following is an example of calling the data processing function:

[0080] parse_with_errors(line,{{\"schema\":[{\"field\":\"id\",\"type\":\"Int32\"},{\"field\":\"name\",\"type\":\"String\"},{\"field\":\"create_time\",\"type\":\"DateTime\"}]}}).

[0081] The data for the line is shown in Table 2 below:

[0082] Table 2

[0083] line {"id":12,"name":"aa","create_time":"2022-11-0512:33:32"} {"id":13,"name":"bb","create_time":"2022-11-0615:15:17"} {"id":14,"name":,"create_time":"2022-}

[0084] The custom data processing function `parse_with_errors` is called to handle the data.

[0085]

[0086]

[0087] The `with` statement calls a custom data processing function, which returns a tuple `t`.

[0088] Get the first element of the tuple using t.1. The alias for the specified field is error, which is specifically used to store exception data that cannot be processed. If the data is processed normally, the current element will be empty.

[0089] The second element of the tuple is obtained through t.2. The alias for the specified field is nested_field, which is specifically used to store the processed nested field. If the processing fails, this element will be empty.

[0090] The processed data is shown in Table 3 below:

[0091] Table 3

[0092] error id name create_time 12 aa 2022-11-0512:33:32 13 bb 2022-11-0615:15:17 {"id":14,"name":,"create_time":"2022-} 14

[0093] The first two fields represent normal processing data, while the last field represents abnormal data and handles the abnormalities.

[0094] 2. Data Processing Flow

[0095] Spark or Flink reads data as a distributed dataset. The typical data processing flow involves iterating through each partition of the distributed dataset, performing string cleaning, filtering, and transformation operations on each data entry within each partition, and then writing the processed data to a target storage system such as HDFS / Hive. Data processing is primarily implemented using Java or Scala code. Here, we need to optimize the data processing flow, as follows:

[0096] The distributed dataset is traversed through partitions. For each partition, a vectorized execution engine is started, which is a pipeline that uses the CPU's SIMD instruction set to process the data. Each piece of data in the partition is processed through the pipeline to generate a data file in the format of the big data platform. Then, the HDFS API is called to move the generated data file to the Hive table. If the Hive table has partitions, the partition refresh command is called to ensure that the data is visible in the Hive table.

[0097] (1) Logic of materialized views

[0098] Each partition's vectorized execution engine needs to create materialized views to process the data.

[0099] The template for creating a materialized view is as follows:

[0100] --Create Materialized View

[0101]

[0102]

[0103] Create a materialized view, specify the name of the materialized view, and specify the HDFS engine base table, i.e., t_batch.

[0104] (a) Rules for creating materialized views

[0105] The statement for creating a materialized view uses SQL to process the data, following the AS keyword.

[0106] A custom data processing function processes the input data and returns two columns: the first column contains unprocessed abnormal data, and the second column contains normally processed data, returned in a nested format.

[0107] You can filter data using the WHERE clause. For example, if you only want to get data that has been processed normally, you can restrict the fields for abnormal data to be empty.

[0108] Following the SELECT statement, the data of nested fields is expanded. At the same time, the expanded nested field data can also be processed using any ClickHouse function (such as string truncation). The processed data is returned and stored in the HDFS engine base table.

[0109] The data source for the temporary table temp_table is standard input.

[0110] When data is inserted into a temporary table, the materialized view will automatically process the data and write the processed data into the HDFS engine base table, without requiring manual triggering.

[0111] (b) HDFS Engine Base Table Creation Rules

[0112] The base table uses HDFS as its table engine. The HDFS engine allows you to specify the HDFS path for data storage and the file format (such as ORC and Parquet).

[0113] When creating the HDFS engine base table, the field names and field data types are consistent with the fields and field data types returned by the SQL logic of the materialized view.

[0114] The HDFS path for data storage is only a temporary directory, so it needs to be identified by partition number. For example, the HDFS directory is: hdfs: / / ns:8020 / data / {partition number}. The partition number is dynamically adjusted according to the partition. For example, in partition 1, the HDFS directory is: hdfs: / / ns:8020 / data / 1.

[0115] Example as follows:

[0116] Create a materialized view:

[0117] --Create Materialized View

[0118]

[0119] The materialized view, named view_materialized_topic, was created above, and the base table is named base_table_topic.

[0120] The "POPULATE TO" keyword specifies the name of the base table. Here, POPULATE is an optional keyword, indicating that the existing data in the table will also be processed as a materialized view.

[0121] The SQL logic reads data from a temporary table named temp_table. The temporary table has only one field, line. The line is processed, and the returned fields are id, name, and create_time.

[0122] The filtering condition is to retrieve data where the error is empty (using the `where error is null` check), meaning only data that can be processed normally is retrieved. Whether to retain or filter error data depends on specific business requirements; in this example, error data is filtered out.

[0123] When creating the base table, the field names and data types must match those returned by the materialized view. The table creation statement differs for each partition. In partition 1, the table creation statement is as follows:

[0124]

[0125] Using the same local table name across multiple partitions has no impact, as the data processing logic for each partition is independent. However, the HDFS paths must be different because the data is stored on HDFS.

[0126] The HDFS path is specified as hdfs: / / ns:8020 / data, and the file format is ORC.

[0127] The HDFS path can be obtained by querying the Hive table, obtaining the storage path of the Hive table, and then using the storage path of the Hive table as the HDFS path.

[0128] If Hive uses a partitioned table, specify the partitioning field when creating the base table. For example, if partitioning by the name field, specify the partitioning field as follows:

[0129] createtablebase_table_topic

[0130] {

[0131] idUInt32,

[0132] nameString,

[0133] create_timeDateTime

[0134] }ENGINE=HDFS('hdfs: / / ns:8020 / data / 1','ORC')

[0135] partitionbyname;

[0136] (2) Partition traversal

[0137] Within each partition, the partition's data exists as an iterator. The iterator is traversed, and when a certain amount of data accumulates, this data is concatenated using newline characters and passed as standard input to the clickhouse-local tool.

[0138] Example as follows:

[0139] echo "data\ndata\ndata"||. / clickhouse-local-S"line String"-Ntmp_table-q"{statements for creating the base table};{statements for creating a materialized view with the POPULATE keyword};"

[0140] The amount of accumulated data can be specified as a large value, such as 1000. When the amount of data reaches 1000, the clickhouse-local tool is called once. After the call is completed, the remaining data in the iterator is traversed and called again.

[0141] The insert statement was not specified after -q above because when data is inserted into the temporary table tmp_table, the materialized view automatically completes the data insertion operation and performs data processing.

[0142] Each time the clickhouse-local tool is executed, a data file is generated in the HDFS directory, as follows:

[0143] (a) If the table creation statement does not specify a partition

[0144] / / View HDFS directory

[0145] #hdfsdfs-lshdfs: / / ns:8020 / data / 1

[0146] / data / 1 / part-xxx1.orc

[0147] / data / 1 / part-xxx2.orc

[0148] (2) If the table creation statement specifies the partition field as name

[0149] / / View HDFS directory

[0150] #hdfsdfs-lshdfs: / / ns:8020 / data / 1

[0151] / data / 1 / name=aa / part-xxx1.orc

[0152] / data / 1 / name=bb / part-xxx2.orc

[0153] The process reads the metadata of the Hive table to obtain its storage path. Then, using the HDFS API, it moves the HDFS files to the Hive table. This step only modifies the HDFS NameNode's metadata, making it very fast. If the Hive table is partitioned, it performs a refresh operation on the relevant Hive partitions. For example, in the example above, the Hive partitions involved are name=aa and name=bb. Therefore, a refresh is performed on these two Hive partitions to ensure that data written to the Hive partitions can be queried. The refresh statement is as follows:

[0154] alter table hive_table add if not exists partitions(name='aa');

[0155] alter table hive_table add if not exists partitions(name='bb').

[0156] It should be noted that although the operation of the method of the present invention has been described in a specific order in the above embodiments and figures, this does not require or imply that the operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.

[0157] Based on the same inventive concept, this invention also proposes an apparatus for improving data ETL performance. The implementation of this apparatus can refer to the implementation of the method described above, and repeated details will not be elaborated further. The term "module" as used below can refer to a combination of software and / or hardware that implements a predetermined function. Although the apparatus described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0158] Figure 4 This is a schematic diagram of the device structure for improving data ETL performance according to the present invention. Figure 4 As shown, the device includes:

[0159] Function definition module 101 is used to define custom data processing functions based on the Clickhouse-Local tool provided by ClickHouse and its source code.

[0160] The data processing module 102 is used to process data using the CPU's SIMD instruction set through a custom data processing function, generate a data file in the format of a big data platform, write it to the target storage, and move it to the Hive table. If the Hive table has partitions, the Hive table partitions are refreshed.

[0161] For data that cannot be handled due to anomalies, filter it using a custom data processing function or store it in a separate field.

[0162] Each partition of the distributed dataset creates a pipeline that uses the CPU's SIMD instruction set to process the data, and creates a materialized view in the pipeline of each partition, specifying the HDFS engine base table; the data in each partition of the distributed dataset exists in the form of iterators.

[0163] The rules for creating materialized views are as follows:

[0164] The creation statement for a materialized view uses SQL to perform data processing after the AS keyword;

[0165] A custom data processing function processes the input data and returns two columns: the first column contains unprocessed exception data, and the second column contains normally processed data, returned in a nested format.

[0166] Filter data using WHERE clauses;

[0167] Following the select statement, the data of the nested fields is expanded, and the expanded nested field data is processed using any ClickHouse function. The processed data is returned and stored in the HDFS engine base table.

[0168] The data source for temporary tables is standard input.

[0169] When data is inserted into a temporary table, the materialized view automatically performs data processing and writes the processed data into the HDFS engine base table.

[0170] The rules for creating HDFS engine base tables are as follows:

[0171] The base table uses HDFS as its table engine, and the HDFS engine allows you to specify the HDFS path and file format for data storage.

[0172] When creating the base table, the field names and field data types must be consistent with the fields and field data types returned by the SQL logic of the materialized view;

[0173] The HDFS path for data storage is used as a temporary directory and is identified by a partition number, which is dynamically adjusted based on the partition it belongs to.

[0174] It should be noted that although several modules of the apparatus for improving data ETL performance have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of the present invention, the features and functions of two or more modules described above can be embodied in one module. Conversely, the features and functions of one module described above can be further divided and embodied by multiple modules.

[0175] Based on the aforementioned inventive concept, such as Figure 5 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220. When the processor 220 executes the computer program 230, it implements the aforementioned method for improving data ETL performance.

[0176] Based on the aforementioned inventive concept, the present invention also proposes a computer-readable storage medium storing a computer program that performs the aforementioned data ETL performance improvement.

[0177] The method and apparatus for improving data ETL performance proposed in this invention utilize the rich ecosystem of Spark or Flink to access data sources and achieve high-performance data processing through a vectorized execution engine; it has fault tolerance for data that cannot be processed; it is lightweight, easy to use, and does not require the deployment of Clickhouse-Server. The data processing logic is implemented through custom functions of the clickhouse-local tool. In the Spark / Flink code, data only needs to be passed to the clickhouse-local tool through standard input to complete the processing.

[0178] The technical terms mentioned above are explained below:

[0179] ETL: an abbreviation for Extract-Transform-Load, used to describe the process of extracting, transforming, and loading data from a source to a destination.

[0180] ClickHouse: A database name used for data analysis.

[0181] Clickhouse-Local: A tool provided by ClickHouse for handling local files, eliminating the need to deploy and configure ClickHouseServer.

[0182] ClickHouseServer: ClickHouse server.

[0183] HDFS: Hadoop Distributed File System.

[0184] Hive: A data warehouse analysis system built on Hadoop that provides rich SQL query methods to analyze data stored in the Hadoop Distributed File System.

[0185] OOM: An abbreviation for Out of Memory, indicating that insufficient memory has caused an overflow.

[0186] ORC is an abbreviation for Optimized Row Columnar. The ORC file format is a storage format within the Hadoop ecosystem. ORC is a columnar storage format with a high compression ratio.

[0187] Parquet: A column-oriented storage format primarily used in the Hadoop ecosystem. Similar to ORC, it offers a high compression ratio.

[0188] While the spirit and principles of the invention have been described with reference to several specific embodiments, it should be understood that the invention is not limited to the disclosed specific embodiments, and the division of aspects does not imply that features in these aspects cannot be combined for benefit; such division is merely for ease of description. The invention is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.

[0189] Regarding the limitation of the scope of protection of this invention, those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solution of this invention are still within the scope of protection of this invention.

Claims

1. A method for improving data ETL performance, characterized in that, The method includes: We can use the Clickhouse-Local tool provided by ClickHouse and customize data processing functions based on its source code. By using custom data processing functions and the CPU's SIMD instruction set, data is processed and exceptions are handled. For data that cannot be processed due to exceptions, the custom data processing functions perform filtering or store it in a separate field, generating a data file in the format of a big data platform, which is then written to the target storage and moved to the Hive table. If the Hive table has partitions, the Hive table partitions are refreshed. Each partition of the distributed dataset creates a pipeline that uses the CPU's SIMD instruction set to process the data, and creates a materialized view in the pipeline of each partition, specifying the HDFS engine base table; the data in each partition of the distributed dataset exists in the form of iterators; The rules for creating the materialized view are as follows: The creation statement for a materialized view uses SQL to perform data processing after the AS keyword; A custom data processing function processes the input data and returns two columns: the first column is the abnormal data that cannot be processed, and the second column is the normally processed data, returned in a nested type; Filter data using WHERE clauses; Following the select statement, the data of the nested fields is expanded, and the expanded nested field data is processed using any ClickHouse function. The processed data is returned and stored in the HDFS engine base table. The data source for temporary tables is standard input. When data is inserted into a temporary table, the materialized view automatically performs data processing and writes the processed data into the HDFS engine base table; The creation rules for the HDFS engine base tables are as follows: The base table uses HDFS as its table engine, and the HDFS engine allows you to specify the HDFS path and file format for data storage. When creating the base table, the field names and field data types must be consistent with the fields and field data types returned by the SQL logic of the materialized view; The HDFS path for data storage is used as a temporary directory and is identified by a partition number, which is dynamically adjusted based on the partition it belongs to.

2. An apparatus for improving data ETL performance, characterized in that, The device includes: The function definition module is used to define custom data processing functions based on the Clickhouse-Local tool provided by ClickHouse and its source code. The data processing module utilizes custom data processing functions and the CPU's SIMD instruction set to process data and handle exceptions. For data that cannot be processed, the custom data processing function performs filtering or stores it in a separate field, generating a data file in the format of a big data platform, writing it to the target storage, and moving it to a Hive table. If the Hive table has partitions, the partitions are refreshed. A pipeline using the CPU's SIMD instruction set is created for each partition of the distributed dataset, and a materialized view is created in each partition's pipeline, specifying the HDFS engine base table. Data in each distributed dataset partition exists as an iterator. The creation rules for the materialized view are as follows: the materialized view creation statement uses SQL after the AS keyword to implement data processing; the custom data processing function processes the input data and returns two columns: the first column represents the unprocessable exception data. The second column contains normally processed data, returned as a nested type. Data is filtered using a WHERE clause. Following the SELECT statement, the nested fields are expanded, and any ClickHouse function is applied to these expanded fields. The processed data is then returned and stored in the HDFS engine base table. The temporary table's data source is standard input. When data is inserted into the temporary table, the materialized view automatically processes the data and writes it to the HDFS engine base table. The HDFS engine base table is created according to the following rules: the base table's engine is HDFS, and the HDFS path and file format for data storage can be specified. When creating the base table, the field names and data types must match those returned by the materialized view's SQL logic. The HDFS path for data storage is used as a temporary directory, identified by a partition number, which is dynamically adjusted based on the partition.

3. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method of claim 1.

4. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that performs the method of claim 1.

Images