Big data exporting method and device based on script push-down and asynchronous compression transmission

Abstract

The invention relates to the technical field of big data processing, in particular to a big data exporting method and device based on script push-down and asynchronous compression transmission. According to the method, the query logic corresponding to the export task is packaged into the executable script, and the executable script is executed on the database server, so that query and file generation are both completed in the database server, and the risk of memory overflow is fundamentally avoided; furthermore, the local file of the server is compressed, only the script and the compressed result file are transmitted, and only the initial small script and the final compressed result file need to be transmitted through the network, so that the real-time streaming transmission of original mass data is avoided, the size of the transmitted file is reduced, and the network bandwidth is saved; the client program does not need to be blocked and waited during task execution, other requests can be processed, a result is obtained through asynchronous monitoring, and the overall throughput and the resource utilization rate of the system are improved.

Description

Technical Field

[0001] This invention relates to the field of big data processing technology, and in particular to a method and apparatus for exporting big data based on script pushdown and asynchronous compressed transmission. Background Technology

[0002] In the field of data processing, it is often necessary to export large amounts of query results from databases to files. Traditional Java application-based export methods, such as those using JDBC (Java Database Connectivity), typically follow this process: the application sends a query to the database, the database returns a result set, and the application reads the data line by line via I / O streams and writes it to a local file. This approach exposes the following serious drawbacks when handling massive amounts of data (e.g., hundreds of millions of rows): Java programs need to create and maintain database connections and I / O streams. When the data volume is extremely large, the result set continuously consumes a large amount of memory on the database side, during network transmission, and on the Java application side, which can easily lead to Java Virtual Machine memory overflow and cause program crashes. The entire result set needs to be transferred from the database server to the application server over the network, consuming a large amount of network bandwidth, and the transmission process is time-consuming. The exported file is finally written to the application server's disk, and the writing of a large amount of data consumes valuable disk I / O resources, which may affect the performance of other core business functions on the application server. Traditional export is a synchronous or semi-synchronous process. The application needs to continuously wait for the database to return and for the data to be written to complete. During this period, resources are occupied for a long time, resulting in low system throughput.

[0003] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0004] The technical problem to be solved by this invention is to address the issues of client memory overflow, high network resource consumption, high application server disk I / O pressure, low system throughput due to synchronization blocking, and inconvenience in small file management that exist in traditional batch export of large amounts of data, thereby achieving the goal of exporting massive amounts of data efficiently and with low power consumption.

[0005] The present invention adopts the following technical solution: In the first aspect, a big data export method based on script pushdown and asynchronous compressed transmission is provided, including: encapsulating the query logic corresponding to the export task into an executable script, and transmitting the executable script to a database server; The executable script is executed on the database server to drive the database client to perform the export task and write the export results to a local file on the server. The local files on the server are compressed to obtain a compressed result file; The status of the export task on the database server is monitored asynchronously. Once the export task is completed, the compressed result file is retrieved from the database server to the local machine.

[0006] Preferably, the step of encapsulating the query logic corresponding to the export task into an executable script specifically includes: The client program queries the system.parts system table in the database to obtain the partition key and the corresponding partition value range of the target table to obtain partition information. The partition key is a time field or a business-defined field, and the partitions are divided in days or months. An executable script containing a loop execution structure is dynamically generated based on the partition information. The executable script includes a query command, a temporary output path, and a file format definition for each partition. The file format is CSV, and the query command is: clickhouse-client -q "SELECT FROM target_table_{partition_name}WHERE partition_key='{partition_name}' AND {query_parameter}" --format CSV> / tmp / export_task_{taskID}_{partition_name}.csv

[0007] Preferably, executing the executable script on the database server specifically includes: By querying the system.metrics and system.processes tables in the database, key performance indicators including CPU load, memory usage, and current concurrent query count are collected. For each of the key performance indicators, a safety threshold and a warning threshold are preset; When all the key performance indicators are below the corresponding safety threshold, multiple clickhouse-client processes are started at the maximum concurrency to execute sharded queries. When all the key performance indicators exceed the corresponding safety threshold but are below the warning threshold, the system enters the throttling mode, reducing the number of concurrent queries to a preset value or adding random delays during the query process. If any of the key performance indicators exceeds the corresponding warning threshold, the corresponding exported task is marked as paused and the checkpoint is recorded. It will be re-evaluated and executed again in the next scheduling cycle.

[0008] Preferably, the step of compressing the local file on the server to obtain the compressed result file specifically includes: The file characteristics of the local files on the server are analyzed, and a target compression algorithm is dynamically selected from multiple compression algorithms based on the file characteristics to compress the local files on the server; wherein, the file characteristics include at least one of the following: number of files, text redundancy, and file size.

[0009] Preferably, the step of dynamically selecting a target compression algorithm from multiple compression algorithms based on the file characteristics to compress local files on the server specifically includes: When the number of files exceeds the number threshold or the text redundancy exceeds the redundancy threshold, the Zstandard algorithm or gzip algorithm is selected to compress the local files on the server to obtain the compressed result file. When the number of files is less than or equal to a preset value and the size of a single file is less than a size threshold, the LZ4 algorithm is used to compress the local files on the server to obtain the compressed result file.

[0010] Preferably, before compressing the local files on the server, the method further includes: Identify and filter local server files with fewer than a certain number of rows. Multiple selected local server files are merged until the number of data rows in the merged local server file reaches or exceeds the specified row count threshold.

[0011] Preferably, the asynchronous monitoring of the status of the export task on the database server, and the retrieval of the compressed result file from the database server to the local machine after the export task is detected as completed, specifically includes: The client program starts a scheduled monitoring task and sets the monitoring period. The monitoring content includes: the existence of the compressed result file on the local database server, the stability of the file size, and the complete execution of the query process corresponding to the executable script. When all three conditions are met, the export task is considered complete, and the client program uses the secure copy command to pull the corresponding compressed result file to the local machine.

[0012] Preferably, the client program is a Java application, the database server is a ClickHouse server, the executable script is a Shell script, and the database client is the native ClickHouse-client tool of the ClickHouse server.

[0013] Secondly, a big data export device based on script pushdown and asynchronous compression transmission is provided, the big data export device based on script pushdown and asynchronous compression transmission includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the big data export method based on script pushdown and asynchronous compressed transmission.

[0014] Thirdly, a non-volatile computer storage medium is provided, the computer storage medium storing computer-executable instructions, which are executed by one or more processors to perform the big data export method based on script pushdown and asynchronous compressed transmission described in the first aspect.

[0015] Fourthly, a chip is provided, comprising: a processor and an interface for calling and running a computer program stored in memory from memory, performing a big data export method based on script pushdown and asynchronous compressed transmission as described in the first aspect.

[0016] Fifthly, a computer program product containing instructions is provided, which, when executed on a computer or processor, cause the computer or processor to perform the big data export method based on script pushdown and asynchronous compressed transmission as described in the first to fourth aspects and any one of them.

[0017] Compared with the prior art, the beneficial effects of the present invention are as follows: This invention encapsulates the query logic corresponding to the export task into an executable script and executes the script on the database server. This allows both querying and file generation to be completed on the database server, pushing the time-consuming querying and file generation processes down to the database server. The client program no longer needs to create and maintain massive data streams, fundamentally avoiding the risk of memory overflow. Furthermore, the local files on the server are compressed, and only the script and the compressed result file are transmitted. Only the initial small script and the final compressed result file need to be transmitted over the network, avoiding the real-time streaming transmission of the original massive data, reducing the size of the transmitted files, and saving network bandwidth. The client program does not need to block and wait during task execution, and can handle other requests and obtain results through asynchronous monitoring, improving the overall throughput and resource utilization of the system. Attached Figure Description

[0018] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0019] Figure 1 This is a flowchart illustrating a big data export method based on script pushdown and asynchronous compressed transmission provided in an embodiment of the present invention; Figure 2This is a flowchart illustrating an embodiment of the present invention for encapsulating an executable script; Figure 3 This is a schematic diagram of a process for executing an executable script according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a process for merging local files on a server, provided by an embodiment of the present invention. Figure 5 This is a schematic diagram of a process for compressing local files on a server, provided by an embodiment of the present invention; Figure 6 This is a schematic diagram of a process for retrieving compressed result files according to an embodiment of the present invention; Figure 7 This is a signaling diagram of a big data export method based on script pushdown and asynchronous compressed transmission provided in an embodiment of the present invention; Figure 8 This is a schematic diagram of a big data export device based on script pushdown and asynchronous compression transmission provided in an embodiment of the present invention. Detailed Implementation

[0020] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0021] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0022] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0023] In describing some embodiments, the terms "coupled," "coupled," and "connected," and their derivative expressions, may be used. For example, the term "connected" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact with each other. Similarly, the term "coupled" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact. However, the terms "connected" or "coupled" may also refer to two or more components that do not have direct contact with each other but still cooperate or interact with each other, such as "optical coupling," "wireless connection," etc. The embodiments disclosed herein are not necessarily limited to the scope of this invention.

[0024] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0025] Example 1: To address the problems of existing technologies, this embodiment proposes a big data export method based on script pushdown and asynchronous compressed transmission. In one embodiment, such as... Figure 1 As shown, it includes: Step 101: Encapsulate the query logic corresponding to the export task into an executable script, and transmit the executable script to the database server.

[0026] The core of this step is to offload the query task, which was originally executed on the client side, to the database server, thus avoiding the client directly processing massive amounts of data. First, the client program (such as a Java application) parses and exports the query logic corresponding to the requirements, including core information such as the target data table, filtering conditions, and data range. Simultaneously, it retrieves the partition information of the target table (e.g., day partitioning by the event_date field) by querying the database system table (e.g., ClickHouse's system.parts table). Based on this information, the client dynamically generates an executable script (preferably a shell script). The script contains independent query commands for each partition, local output paths, and file format definitions (e.g., CSV format). For example, the command template is: `clickhouse-client -q "SELECT FROM large_table_16 WHERE event_date='20251216'" --format CSV> / tmp / export_task123_20251216.csv`.

[0027] In one embodiment, it's worth noting that because the partitioned table creation was categorized by day, and a month has a maximum of 31 days, 31 target tables were created. Queries specify the corresponding tables for each data item; for example, the above statement queries data for December 16th.

[0028] After the script is generated, the client transmits it to a specified directory on the database server via a secure channel such as SSH. Only a small script needs to be transmitted, avoiding the transmission of massive amounts of data initially.

[0029] Step 102: Execute the executable script on the database server to drive the database client to perform the export task and write the export results to a local file on the server.

[0030] After receiving the script, the database server executes it via remote invocation or local scheduling, driving the database's native client tools (such as ClickHouse's clickhouse-client) to perform the export task. The executable script executes query commands concurrently or sequentially by partition, directly processing data query and write operations using the database server's hardware resources. The query results are not passed through the client and are directly written to a temporary file on the database server's local machine (i.e., a file like ` / tmp / export_task123_20251216.csv` defined in step 101) via standard output redirection.

[0031] In one embodiment, during execution, the executable script obtains metrics such as CPU load and memory usage by querying the system.metrics and system.processes tables. If the metrics are below a safe threshold, it executes at maximum concurrency (e.g., 20 processes). If the metrics exceed the threshold, it reduces concurrency or pauses the task to avoid overloading the database server. The specific process will be described below.

[0032] Step 103: Compress the local file on the server to obtain a compressed result file.

[0033] In order to reduce network overhead and file management costs during subsequent transmission, the local files (which may be multiple partition files) generated on the database server need to be preprocessed and compressed first.

[0034] In one embodiment, before compressing local files on the server, small file merging is performed first. This includes counting the number of lines in each file, sorting and merging small files with fewer than 1 million lines by line count, and ensuring that the number of lines in the merged file does not exceed 1 million, thus solving the problem of managing too many small files.

[0035] Subsequently, an adaptive compression strategy is adopted. By analyzing the characteristics of the file set (number, size, data type), if the number of files exceeds 100 and each file is less than 20MB, the Zstandard high compression ratio algorithm is selected; otherwise, the LZ4 high-speed low-latency algorithm is selected. For example, the following is executed: `lz4 -f / tmp / export_task123 / The compressed file `.csv / tmp / export_task123.csv.lz4` can reduce the file size to about 1 / 4 of the original size, thus balancing compression efficiency and transmission speed.

[0036] Step 104: Asynchronously monitor the status of the export task on the database server. After the export task is completed, pull the compressed result file from the database server to the local machine.

[0037] The client employs an asynchronous monitoring mode, eliminating the need to block and wait for task execution, and allowing for simultaneous processing of other business operations. Specifically, a scheduled task (e.g., every 30 seconds) is initiated to check the task status on the database server via the SSH protocol: this includes checking the existence of the target compressed file, the stability of the file size (no change in two consecutive monitoring sessions), and whether the script execution process has terminated. When all three conditions are met, the export task is considered complete. At this point, the client can use file transfer protocols such as SCP to retrieve the single compressed result file (e.g., `export_task123.csv.lz4`) from the server to its local machine with a single operation.

[0038] Compared to traditional streaming of massive amounts of raw data, this step significantly reduces network bandwidth usage and transmission time, while also avoiding the risk of client memory overflow.

[0039] In one embodiment, the client program is a Java application, the database server is a ClickHouse server, the executable script is a Shell script, and the database client is the ClickHouse server's native clickhouse-client tool.

[0040] In summary, this embodiment encapsulates the query logic corresponding to the export task into an executable script and executes the executable script on the database server. This allows both querying and file generation to be completed on the database server, pushing the time-consuming querying and file generation processes down to the database server. The client program no longer needs to create and maintain massive data streams, fundamentally avoiding the risk of memory overflow. Furthermore, by compressing local files on the server, only the script and the compressed result file are transmitted. Only the initial small script and the final compressed result file need to be transmitted over the network, avoiding the real-time streaming transmission of the original massive data, reducing the size of the transmitted files, and saving network bandwidth. The client program does not need to block and wait during task execution and can handle other requests, obtaining results through asynchronous monitoring, thus improving the overall throughput and resource utilization of the system.

[0041] In one embodiment, such as Figure 2 As shown, the process of encapsulating the query logic corresponding to the export task into an executable script specifically includes: Step 1011: The client program queries the system.parts system table in the database to obtain the partition key and the corresponding partition value range of the target table to obtain partition information.

[0042] The partition key is a time field or a business-defined field, and the partitions are divided in days or months.

[0043] In this process, the client program (such as a big data export application written in Java) actively connects to the database server (in this example, the ClickHouse server). By executing the standardized query statement `SELECT partition, name FROM system.parts WHERE table='target table name' AND active=1`, it reads the built-in system.parts table of ClickHouse. The system.parts table is the core system table used by ClickHouse to store the metadata of data table partitions. By using system.parts, it determines whether the table is a partitioned table, then queries the partition key of the table, and performs SQL splitting based on the query parameters associated with the partition key, which can accurately return the partition attributes of the target table.

[0044] The partition key is a partition dimension field defined when the target table is created. In this embodiment, it is limited to two types: one is a time field (such as `event_date`, `order_create_time`, which is suitable for common scenarios of exporting data by time dimension), and the other is a business-defined field (such as `region_code` region code, `merchant_id` merchant ID, which is suitable for scenarios of exporting by business dimension).

[0045] The range of partition values ​​corresponds to the specific value of the partition key, and the partition granularity is fixed as day or month (e.g., the partition values ​​of the time field `event_date` are `20251201`, `20251202` (by day), or `202512`, `202601` (by month); the partition values ​​of the business field `region_code` are `310000`, `110000`, divided by provincial administrative region).

[0046] The client program will integrate the partition keys (such as `event_date`) and the list of partition values ​​(such as `20251216` and `20251217`) read from the `system.parts` table into structured partition information, which will serve as the core basis for subsequent script generation. This ensures that the script only performs queries on the partitions that need to be exported, breaking down the massive data export task into multiple small-granularity partition query tasks, avoiding the performance loss caused by full table scans, and ensuring the targeted nature of the subsequent executable script generation.

[0047] Step 1012: Dynamically generate an executable script containing a loop execution structure based on the partition information.

[0048] The executable script includes a query command, temporary output path, and file format definition for each partition. The file format is CSV, and the query command is: clickhouse-client -q "SELECT FROM target_table_{partition_name} WHERE partition_key='{partition_name}' AND {query_parameters}" --format CSV> / tmp / export_task_{taskID}_{partition_name}.csv.

[0049] First, the client program constructs a loop execution structure (such as a `for` loop in a shell script) based on the list of partition values ​​contained in the partition information. The loop iterates over each specific partition name (such as `20251216`, `20251217`), ensuring that the script can process the data of each partition in sequence.

[0050] Secondly, within the loop body, the client program populates the standardized query command template: `clickhouse-client-q "SELECT FROM target_table_{partition_name} WHERE partition_key='{partition_name}' AND {query_parameters}" --format CSV> / tmp / export_task_{taskID}_{partition_name}.csv`, where: - The partition value obtained corresponding to `{partition name}` (e.g., `20251216`). - `{partition key}`: The corresponding partition key obtained (e.g., `event_date`); - `{query parameters}`: Supplement the filtering conditions at the business level (such as `order_status=1`, `pay_amount>0`); - `Target table_{partition name}`: The physical table name stored by partition in ClickHouse, which must be consistent with the table name recorded in the `system.parts` table; - ` / tmp / export_{taskID}_{partition_name}.csv`: Specifies the temporary output path for the temporary output file generated after the script is executed. ` / tmp` is the local temporary directory of the database server, and `{taskID}` is the globally unique identifier of the export task (such as `task_12345`) to ensure that the output files of different tasks and different partitions do not have the same name; - `CSV format`: This is the file format uniformly defined in this solution. It specifies the data format written to the temporary output file, providing a unified parsing basis for subsequent file merging and compression steps.

[0051] Finally, the client program generates a complete executable script (such as a shell script), as shown in the following example: #! / bin / bash query_paras=1 TASK_ID="task_12345"# Defines the list of partitions to be exported PARTITIONS=("2025-12-16" "2025-12-15" "2025-12-14")for PARTITION in "{PARTITIONS[@]}" do # Generate separate query and output files for each partition day={PARTITION: -2} clickhouse-client \ -q "SELECT FROM large_table_{DAY} WHERE event_date='{PARTITION} and{query_paras}'" \ --format CSV > / tmp / export_{TASK_ID}_{PARTITION}.csv& # Done# Waiting for all background export jobs to complete.

[0052] In summary, this embodiment uses partitioning and loop script design to export massive amounts of data into multiple smaller partition queries, reducing the resource consumption of a single query while ensuring the versatility and scalability of the executable script.

[0053] In one embodiment, such as Figure 3 As shown, executing the executable script on the database server specifically includes: Step 1021: Collect key performance metrics, including CPU load, memory usage, and current concurrent queries, by querying the system.metrics and system.processes tables in the database.

[0054] The core purpose of this step is to perceive the resource load status of the database server in real time, providing data support for subsequent dynamic adjustment of execution strategies. All operations are completed based on the native system tables of the ClickHouse database, ensuring the accuracy and compatibility of metric collection.

[0055] Before or during the execution of the executable script, the database server (ClickHouse server) reads the system's core monitoring tables using built-in SQL query commands. 1. Read the `system.metrics` table: This table is a system table used by ClickHouse to record real-time performance metrics of the server. You can use `SELECT metric, value FROM system.asynchronous_metrics WHEREMetric IN ('OSMemoryAvailable', 'OSMemoryTotal','OSUserTimeCPU0')` to accurately collect CPU load (percentage) and memory usage (percentage).

[0056] 2. Read the `system.processes` table: This table records information about all running query processes in the current database. This can be done via `SELECT COUNT(...` `FROM system.processes` counts the current number of concurrent queries (i.e., the number of query tasks executed simultaneously).

[0057] The above three types of key performance indicators will be collected in real time (once per second) and stored in the temporary monitoring log on the local database server to ensure that the latest indicator data can be directly accessed for subsequent policy adjustments.

[0058] Step 1022: Set a safety threshold and a warning threshold for each of the key performance indicators.

[0059] For the three types of key performance indicators collected in step 1021, a unified and configurable threshold rule is preset (the following is a general configuration example, which can be adjusted according to the server hardware): For CPU load, the safe threshold range can be 65%-75%, preferably 70%; the warning threshold range can be 80%-90%, preferably 85%. The safe threshold represents the server's CPU resources not being saturated, while the warning threshold represents a high load state close to full CPU load.

[0060] For memory utilization, the safe threshold range can be 65%-75%, preferably 70%; the warning threshold range can be 80%-90%, preferably 85%. The safe threshold represents sufficient memory resources, while the warning threshold represents memory being about to run out, easily triggering the risk of OOM (Out of Memory) errors. For the current number of concurrent queries, a safe threshold can be 100; a warning threshold can be 150. The safe threshold represents a moderate level of database query concurrency, while the warning threshold indicates that excessive concurrency can easily lead to query queuing and blocking. All thresholds are written into the configuration section of the executable script (such as the variable definition section of the shell script) to ensure that subsequent steps can directly read the threshold variables and complete the comparison and judgment of various indicators with the corresponding thresholds.

[0061] Step 1023: When all the key performance indicators are below the corresponding safety threshold, start multiple clickhouse-client processes with the maximum concurrency to execute sharded queries.

[0062] Specifically, according to the various threshold items set in step 1022, when the real-time collected CPU load is ≤70%, memory utilization is ≤70%, and the current number of concurrent queries is ≤100 (i.e., all indicators are below the safety threshold), the executable script will trigger high-concurrency execution logic.

[0063] In one embodiment, from the partition information obtained in step 1011, all partitions are split into multiple shards (e.g., 5 partitions per shard); multiple independent `clickhouse-client` processes are started at the maximum concurrency (preset to 20, adjustable according to the number of server cores, e.g., 16 cores for a 16-core server). Each process is responsible for executing the query command for one shard. For example, when exporting data from 20 partitions, 20 `clickhouse-client` processes are started, and `clickhouse-client -q "SELECT"` is executed simultaneously. The command `FROM target_table_{partition_name} ..."` allows each process to query one partition, and the processes do not block each other, maximizing the use of server CPU and memory resources and shortening the overall export time.

[0064] Step 1024: When all the key performance indicators exceed the corresponding safety threshold but are below the warning threshold, enter the throttling mode to reduce the number of concurrent queries to a preset value or add random delays during the query period.

[0065] Similarly, based on the thresholds set in step 1022, when the real-time collected CPU load, memory utilization, and current concurrent query count all exceed the safety threshold (e.g., CPU load 75%, memory utilization 78%, concurrent query count 120), but do not reach the warning threshold (all <85%, <150), a script can be executed to enter throttling mode and perform one of the following operations (choose one, configurable): 1. Reduce concurrent queries: Reduce the maximum concurrency from 20 to a preset value (e.g., 5). For example, start only 5 `clickhouse-client` processes to execute query commands for each partition in sequence, thereby reducing the number of processes running at the same time and reducing CPU and memory usage.

[0066] 2. Add random delay: Before each `clickhouse-client` process executes a query command, add a random delay of 1-5 seconds using the `sleep((RANDOM % 5 + 1))` command to avoid multiple processes launching queries simultaneously, which could cause a sudden increase in server load. In throttling mode, the export task is still guaranteed to continue to execute, only the execution efficiency is reduced to adapt to the server load status.

[0067] Step 1025: When any of the key performance indicators exceeds the corresponding warning threshold, mark the corresponding exported task as paused and record the checkpoint, and wait for the next scheduling cycle to re-evaluate before execution.

[0068] Specifically, based on the thresholds set in step 1022, when any of the key performance indicators collected in real time exceeds the warning threshold (such as CPU load 88%, memory utilization 86%, concurrent queries 155), the executable script immediately triggers the protection mechanism, which includes: 1. Mark the export task status: In the task status file on the local database server (such as ` / tmp / export_task_{TASK_ID}_status.txt`), mark the task status from "Executing" to "Paused".

[0069] 2. Record checkpoints: Save the list of partitions that have been queried and completed (e.g., `Completed: 20251216, Incomplete: 20251217-20251220`), the last executed query command, and the current server load data to ensure that the task can continue from the breakpoint when it is restored, without having to repeatedly query the completed partitions.

[0070] 3. Terminate the currently running `clickhouse-client` process, which means terminating all query processes related to the current export task and releasing server resources.

[0071] 4. Wait for the next scheduling cycle: The preset scheduling cycle is 5 minutes. After 5 minutes, step 1021 is executed again to collect performance indicators. If the indicators fall below the warning threshold, the task execution is resumed from the checkpoint; if they still exceed the threshold, the task is paused again until the indicators meet the requirements.

[0072] In summary, by dynamically adjusting the concurrency of the `clickhouse-client` process, the execution efficiency of the export task is maximized while ensuring the stability of the database server, avoiding resource waste or server overload caused by a single strategy. All thresholds and concurrency are configurable, and the checkpoint mechanism ensures that tasks can resume from where they left off after being paused, without having to repeat completed partition queries.

[0073] Since the data queried from some files in certain partitions may be relatively small in quantity, there are two drawbacks to treating them as a single file. Firstly, a large number of small files are not convenient for management. Secondly, a large number of files leads to low merging efficiency. To solve this problem, in one embodiment, as Figure 4 shown, before compressing the server local files, the method further includes: Step 201: Identify and filter out server local files with the number of data rows less than the row threshold.

[0074] Among them, the server local file is the CSV format partition export file generated at the path ` / tmp / export_task_{task ID}_{partition name}.csv` after the database server executes the executable script in step 102; the row threshold is a preset quantization standard (uniformly set to 1 million rows in this embodiment, which can be adjusted according to the server IO performance). The specific execution logic includes: 1. The database server traverses all target files through a Shell command (such as `wc -l`) to count the total number of rows of each server local file. The execution command example is: ```bash # Traverse all partition export files, count the number of rows and output to a temporary log for FILE in / tmp / export_task_{TASK_ID}_ .csv; do LINE_COUNT=(wc -l<FILE) echo "{FILE},{LINE_COUNT}">> / tmp / export_task_{TASK_ID}_file_lines.log done<000​​​​​​​​​​

[0077] The process involves defining a temporary merge file with an initial line count of 0. Small files are read from the list of files to be merged in ascending order of line count. The first small file to be merged is read and its contents are appended to the temporary merge file. For example, if the number of lines in the merged file is 500,000 at this point (below the threshold), the next small file to be merged is read and appended to the temporary merge file. For example, if the total number of lines reaches 1.1 million (exceeding the 1 million line threshold), the temporary merge file is immediately renamed to the official merge file, and a new temporary merge file is initialized to continue processing the remaining small files to be merged.

[0078] In one embodiment, if the total number of lines in the remaining small files to be merged is still less than the threshold (e.g., only one file with 300,000 lines remains), then it is directly retained as the final merged file to avoid the file becoming too large due to forced merging.

[0079] After the merge is complete, delete the original small files to be merged (or move them to the backup directory), keeping only the merged file and the large files (≥1 million rows) that were not filtered. The merged local file on the server remains in CSV format, with the field order and encoding format consistent with the original file, ensuring compatibility with subsequent compression processing and client parsing.

[0080] In one embodiment, such as Figure 5 As shown, the step of compressing the local file on the server to obtain the compressed result file specifically includes: Step 1031: Analyze the file characteristics of the local files on the server, wherein the file characteristics include at least one of the following: number of files, text redundancy, and file size.

[0081] After all the sharded query files are generated, the script calls the decision module again. All operations are completed locally on the database server, and the analysis object is the CSV format local server file after merging in step 202.

[0082] In one embodiment, the number of files is first counted by traversing the directory containing the files to be compressed and determining the total number of local files on the server that need to be compressed, which directly reflects the degree of file dispersion. Secondly, text redundancy is calculated. Based on the structured characteristics of CSV files, a small sample of data is randomly extracted from each file. By statistically analyzing the proportion of repeated fields and strings in the samples, the average text redundancy of all files is calculated. Higher redundancy indicates more repeated data and greater compression potential. Finally, file size is counted by obtaining the specific byte size of each local file on the server, and then the average size of a single file and the total file size are calculated to determine the overall scale characteristics of the files.

[0083] Step 1032: Based on the file characteristics, dynamically select a target compression algorithm from multiple compression algorithms to compress the local files on the server.

[0084] For data with a large number of files or high text redundancy, high compression ratio algorithms (such as Zstandard / gzip) are preferred to minimize the number of files and the total size, making it easier to transmit and manage. For scenarios with a moderate number of files and a large file size, high-speed and low-latency algorithms (such as LZ4) are selected.

[0085] Specifically, when the number of files exceeds a certain threshold or the text redundancy exceeds a certain threshold, the Zstandard algorithm or gzip algorithm is used to compress the local files on the server to obtain the compressed result file; when the number of files is less than or equal to a preset value and the size of a single file is less than a certain size threshold, the LZ4 algorithm is used to compress the local files on the server to obtain the compressed result file.

[0086] After determining the target compression algorithm, compression is performed on all local files on the database server. After compression, only the generated compressed files are retained, and the original local files are deleted. This reduces the storage space occupied by the database server and also reduces the network transmission pressure for subsequent file retrieval.

[0087] In one embodiment, such as Figure 6 As shown, the asynchronous monitoring of the status of the export task on the database server, and the retrieval of the compressed result file from the database server to the local machine after the export task is completed, specifically includes: Step 1041: The client program starts a scheduled monitoring task and sets the monitoring period. The monitoring content includes: the existence of the compressed result file on the local database server, the stability of the file size, and the complete execution of the query process corresponding to the executable script.

[0088] The client program (such as a Java application) starts an independent timed monitoring task and sets a fixed monitoring period (in one embodiment, it can be 30 seconds, which can be flexibly adjusted according to the scale of the exported task).

[0089] In one embodiment, the monitoring includes checking the existence of the compressed result file. The client accesses the target file directory of the database server via a secure communication protocol to verify whether the compressed result file corresponding to the current export task has been generated, ensuring that the compression step has been completed. Secondly, it verifies the stability of the file size. If the compressed result file exists, the client application will obtain the file's byte size twice (with an interval of one monitoring cycle) to determine if the two sizes are completely consistent, thus confirming that file compression is complete and that no data is still being written. Finally, it confirms the complete execution of the query process corresponding to the executable script. The client queries the database server's process list to verify whether all clickhouse-client query processes related to the export task have terminated normally, with no residual running processes, ensuring that the query and file generation stages have been completely completed. The results of each monitoring session are recorded and archived by the client application, providing a complete basis for subsequent task completion determination.

[0090] Step 1042: When all three conditions are met, the export task is considered complete, and the client program uses the secure copy command to pull the corresponding compressed result file to the local machine.

[0091] Once the task is completed, the client program executes a file retrieval operation using a secure copy command (such as the SCP command). This command is based on the SSH protocol and has the advantages of high transmission security and strong stability, which can effectively prevent data loss or leakage during file transmission.

[0092] In one embodiment, during the file retrieval process, the client program only needs to transmit the compressed result file generated on the database server. Compared to traditional solutions that transmit raw, massive amounts of data, this significantly reduces network bandwidth usage and transmission time. After the compressed result file is retrieved to the client's local machine, it is stored in a preset directory, and the file name is consistent with the compressed result file on the database server. This ensures that the client can accurately trace the data source during subsequent data parsing and usage, thereby completing the closed loop of the entire big data export process.

[0093] Example 2: To further illustrate the big data export method based on script pushdown and asynchronous compressed transmission described in Example 1, in one example, as... Figure 7 As shown in the figure, this embodiment provides an example to specifically illustrate the big data export method based on script pushdown and asynchronous compressed transmission.

[0094] In one big data analytics platform, it was necessary to export a table containing 1 billion rows of records from the ClickHouse cluster.

[0095] Traditional method: Java applications initiate SELECT statements via JDBC. The program crashed with an `OutOfMemoryError` after reading millions of rows from `FROM large_table`.

[0096] The specific process of the big data export method based on script pushdown and asynchronous compressed transmission includes: 1. A Java application generates a shell script that divides the query into multiple SQL commands based on the query time dimension. The content is as follows: `clickhouse-client -q "SELECT FROM large_table_{partition}" --formatCSV> / tmp / export_data_{task_id}_{partition}.csv`.

[0097] 2. Upload the Shell script to a specified directory on the ClickHouse server via a secure channel such as SSH.

[0098] 3. Execute the shell script remotely via SSH.

[0099] Query: monitor the number of SQL executions and memory information of the CK resource server using system.metrics and system.processes. The current performance of the CK big data server includes CPU load and memory utilization. When the CPU load is greater than 70%, the memory utilization is greater than 70%, and / or the number of executions is greater than 100, the throttling mode is enabled, reducing the number of concurrent SQL commands to 5. Otherwise, the full-speed mode is used, executing 20 SQL statements concurrently each time, thereby obtaining the local files on the server.

[0100] 4. After the shell script finishes execution, it checks whether local files on the server need to be merged (for files with fewer than 1 million records). If so, it determines the compression method based on the number and size of the files. If the number of files exceeds 100 and the file size is less than 20N, it uses the zstd compression algorithm; otherwise, it calls the lz4 command for fast compression to obtain the compressed file. Since the exported file is still a CSV file, the file size limit is set to 1 million records.

[0101] 5. Meanwhile, a scheduled task in the Java application (e.g., every 30 seconds) checks via SSH whether the compressed file exists and whether the relevant commands have been executed in the process.

[0102] 6. Once confirmed, the Java application initiates an SCP command to pull the only compressed file to the local machine. Due to LZ4 compression, the file size may be reduced to 1 / 3 of the original size, and the transfer time is greatly shortened.

[0103] In summary, this embodiment encapsulates the query logic corresponding to the export task into an executable script and executes the executable script on the database server. This allows both querying and file generation to be completed on the database server, pushing the time-consuming querying and file generation processes down to the database server. The client program no longer needs to create and maintain massive data streams, fundamentally avoiding the risk of memory overflow. Furthermore, by compressing local files on the server, only the script and the compressed result file are transmitted. Only the initial small script and the final compressed result file need to be transmitted over the network, avoiding the real-time streaming transmission of the original massive data, reducing the size of the transmitted files, and saving network bandwidth. The client program does not need to block and wait during task execution and can handle other requests, obtaining results through asynchronous monitoring, thus improving the overall throughput and resource utilization of the system.

[0104] Example 3: In Embodiment 1, a method for exporting large data based on script pushdown and asynchronous compressed transmission was provided. In this embodiment, a device for exporting large data based on script pushdown and asynchronous compressed transmission will be proposed. The device for exporting large data based on script pushdown and asynchronous compressed transmission includes: a processor and a memory for storing processor-executable instructions; wherein, the processor is configured to execute the method for exporting large data based on script pushdown and asynchronous compressed transmission described in Embodiment 1.

[0105] like Figure 8 As shown, the big data export device based on script pushdown and asynchronous compression transmission includes a processor 21 and a memory 22, wherein the processor 21 and the memory 22 can be connected by a bus or other means.

[0106] Processor 21 can be a Central Processing Unit (CPU). Processor 21 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0107] The memory 22, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the big data export method based on script pushdown and asynchronous compressed transmission in Embodiment 1 of this invention. The processor executes various functional applications and training processes by running the non-transitory software programs, instructions, and modules stored in the memory.

[0108] The memory 22 may include a program storage area and a training storage area. The program storage area may store the operating system and applications required for at least one function; the training storage area may store training data created by the processor. Furthermore, the memory may include high-speed random access memory and non-transitory memory, such as at least one disk storage device, flash memory, or other non-transitory solid-state storage device. In some embodiments, the memory 22 may optionally include memory remotely located relative to the processor, which can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof. The one or more modules stored in the memory 22, when executed by the processor 21, perform functions such as... Figure 1 The large data export method based on script pushdown and asynchronous compressed transmission shown in Example 1 is illustrated. For specific details of the above-described large data export method based on script pushdown and asynchronous compressed transmission, please refer to the relevant documentation. Figure 1 , Figure 2 and Figure 3 The relevant descriptions and effects in the embodiments shown are for reference only and will not be repeated here.

[0109] This embodiment also provides a computer storage medium storing a computer program that can be executed by a processor to complete the big data export method based on script pushdown and asynchronous compressed transmission described in Embodiment 1.

[0110] The computer storage medium stores computer-executable instructions, which can execute the big data export method based on script pushdown and asynchronous compressed transmission in any of the above method embodiments. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0111] The specific steps of the big data export method based on script pushdown and asynchronous compressed transmission are described in Example 1, and will not be repeated in this example.

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

Claims

1. A method for exporting large amounts of data based on script pushdown and asynchronous compressed transmission, characterized in that, include: The query logic corresponding to the export task is encapsulated into an executable script, and the executable script is transmitted to the database server; The executable script is executed on the database server to drive the database client to perform the export task and write the export results to a local file on the server. The local files on the server are compressed to obtain a compressed result file; The status of the export task on the database server is monitored asynchronously. Once the export task is completed, the compressed result file is retrieved from the database server to the local machine.

2. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 1, characterized in that, The process of encapsulating the query logic corresponding to the export task into an executable script specifically includes: The client program queries the system.parts system table in the database to obtain the partition key and the corresponding partition value range of the target table to obtain partition information. The partition key is a time field or a business-defined field, and the partitions are divided in days or months. An executable script containing a loop execution structure is dynamically generated based on the partition information. The executable script includes a query command, a temporary output path, and a file format definition for each partition. The file format is CSV, and the query command is: clickhouse-client -q "SELECT FROM target_table_{partition_name} WHERE partition_key='{partition_name}' AND {query_parameters}" --format CSV > / tmp / export_{task_ID}_{partition_name}.csv 3. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 1, characterized in that, The execution of the executable script on the database server specifically includes: By querying the system.metrics and system.processes tables in the database, key performance indicators including CPU load, memory usage, and current concurrent query count are collected. For each of the key performance indicators, a safety threshold and a warning threshold are preset; When all the key performance indicators are below the corresponding safety threshold, multiple clickhouse-client processes are started at the maximum concurrency to execute sharded queries. When all the key performance indicators exceed the corresponding safety threshold but are below the warning threshold, the system enters the throttling mode, reducing the number of concurrent queries to a preset value or adding random delays during the query process. If any of the key performance indicators exceeds the corresponding warning threshold, the corresponding exported task is marked as paused and the checkpoint is recorded. It will be re-evaluated and executed again in the next scheduling cycle.

4. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 1, characterized in that, The process of compressing the local files on the server to obtain the compressed result file specifically includes: The file characteristics of the local files on the server are analyzed, and a target compression algorithm is dynamically selected from multiple compression algorithms based on the file characteristics to compress the local files on the server; wherein, the file characteristics include at least one of the following: number of files, text redundancy, and file size.

5. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 4, characterized in that, The step of dynamically selecting a target compression algorithm from multiple compression algorithms based on the file characteristics to compress local files on the server specifically includes: When the number of files exceeds the number threshold or the text redundancy exceeds the redundancy threshold, the Zstandard algorithm or gzip algorithm is selected to compress the local files on the server to obtain the compressed result file. When the number of files is less than or equal to a preset value and the size of a single file is less than a size threshold, the LZ4 algorithm is used to compress the local files on the server to obtain the compressed result file.

6. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 1, characterized in that, Before compressing the local files on the server, the method further includes: Identify and filter local server files with fewer than a certain number of rows. Multiple selected local server files are merged until the number of data rows in the merged local server file reaches or exceeds the threshold number.

7. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 1, characterized in that, The asynchronous monitoring of the status of the export task on the database server, and the retrieval of the compressed result file from the database server to the local machine after the export task is completed, specifically includes: The client program starts a scheduled monitoring task and sets the monitoring period. The monitoring content includes: the existence of the compressed result file on the local database server, the stability of the file size, and the complete execution of the query process corresponding to the executable script. When all three conditions are met, the export task is considered complete, and the client program uses the secure copy command to pull the corresponding compressed result file to the local machine.

8. The big data export method based on script pushdown and asynchronous compressed transmission according to claim 7, characterized in that, The client program is a Java application, the database server is a ClickHouse server, the executable script is a Shell script, and the database client is the native ClickHouse-client tool of the ClickHouse server.

9. A big data export device based on script pushdown and asynchronous compressed transmission, characterized in that, The big data export device based on script pushdown and asynchronous compression transmission includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the big data export method based on script pushdown and asynchronous compressed transmission as described in any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the big data export method based on script pushdown and asynchronous compressed transmission as described in any one of claims 1-8.

Images