Data processing method and apparatus

Abstract

This invention provides a data processing method and apparatus. The method includes: receiving an input query statement for a data table, the query statement carrying a correspondence between the data table and a data file, used to instruct processing of data in the data table corresponding to the data file, wherein the data table includes at least two data table shards; based on the query statement, obtaining data from the data file and obtaining sharding rules corresponding to the data table; sharding the data in the data file according to the sharding rules to obtain at least two data file shards; matching each data table shard with its corresponding data file shard to obtain data in the data table that matches the data file shard; and returning a response result corresponding to the query statement based on the matched data. This invention simplifies the development process and improves data processing efficiency.

Description

Technical Field

[0001] This invention relates to the field of database processing technology, and in particular to a data processing method and apparatus. Background Technology

[0002] With the development of information technology, massive amounts of data have been generated. This data often needs to be analyzed to optimize business operations and meet user needs. During data analysis, there are numerous data analysis requirements based on data files; that is, filtering data in data tables based on data files, and then processing the filtered data.

[0003] In related technologies, the data file is usually imported into the database or computing engine as a data table. Then, a JOIN statement is used to intersect specified columns of the data table with specified columns of the data file in a table-to-table manner, so as to filter the data in the data table based on the data in the data file.

[0004] The above methods require importing data files as data tables into the database or computing engine, which is a rather cumbersome process. Furthermore, the processing of JOIN statements usually requires data filtering to be performed after all the data in the data table has been fully loaded, which leads to a significant consumption of space and time. Summary of the Invention

[0005] This invention provides a data processing method and apparatus that can simplify the development process and improve data processing efficiency.

[0006] The technical solution of this invention is implemented as follows:

[0007] This invention provides a method comprising:

[0008] The system receives an input query statement for a data table, which carries the correspondence between the data table and the data file and is used to instruct the processing of the data in the data table corresponding to the data file. The data table includes at least two data table shards.

[0009] Based on the query statement, the data in the data file is obtained, and the sharding rules corresponding to the data table are obtained;

[0010] According to the sharding rules, the data in the data file is sharded to obtain at least two data file shards;

[0011] Each data table shard is matched with its corresponding data file shard to obtain the data in the data table that matches the data file shard.

[0012] Based on the matched data, the response result corresponding to the query statement is returned.

[0013] This invention provides an apparatus comprising:

[0014] A receiving module is configured to receive an input query statement for a data table, the query statement carrying the correspondence between the data table and a data file, and is used to instruct the processing of the data in the data table corresponding to the data file, wherein the data table includes at least two data table shards;

[0015] The acquisition module is used to acquire data from the data file based on the query statement, and to acquire the sharding rules corresponding to the data table;

[0016] The sharding module is used to shard the data in the data file according to the sharding rules to obtain at least two data file shards;

[0017] The matching module is used to match each of the data table shards with the corresponding data file shards to obtain the data in the data table that matches the data file shard.

[0018] The response module is used to return the response result corresponding to the query statement based on the matched data.

[0019] In the above scheme, the acquisition module is further used to parse the query statement to obtain the path of the data file;

[0020] Based on the path of the data file, obtain the data file corresponding to the path;

[0021] Read the data from the data file.

[0022] In the above scheme, the acquisition module is further configured to generate a syntax tree corresponding to the query statement based on the statement structure of the query statement, wherein the syntax tree includes the tagging information of the path corresponding to the data file;

[0023] Based on the syntax tree, a logical query plan corresponding to the query statement is generated, and

[0024] During the generation of the logical query plan, the tagging information is pushed down to the data table so that the data table in the generated logical query plan carries the path of the data file;

[0025] The path of the data file is determined based on the data table in the logical query plan.

[0026] In the above scheme, the acquisition module is also used to determine the storage method of data in the at least two data table shards;

[0027] Based on the storage method of the data in the at least two data table shards, the sharding rules corresponding to the data table are determined.

[0028] In the above scheme, the matching module is further configured to distribute each data file fragment to the corresponding computing node through the control node;

[0029] Each computing node loads the corresponding data table shards, and

[0030] The loaded data table shards are matched with the data file shards.

[0031] In the above scheme, the matching module is also used to generate a corresponding data file fragment cache based on the data file fragments;

[0032] The data table shards are matched with the corresponding data file shard caches.

[0033] In the above scheme, the matching module is further used to match the primary key data corresponding to the primary key in each of the data table shards with the data corresponding to the primary key in the data file shards, so as to obtain the primary key data in the data table that is the same as the data corresponding to the primary key in the data file shards;

[0034] Retrieve the data corresponding to the matched primary key data.

[0035] In the above scheme, the response module is also used to store the matched data in the form of a data table or a data file;

[0036] Generate data storage prompt information corresponding to the data, and

[0037] The data storage prompt information is returned as the response result of the corresponding query statement.

[0038] In the above scheme, the response module is further configured to obtain the logical query plan corresponding to the query statement, and the logical query plan is used to indicate the data processing logic corresponding to the data;

[0039] Based on the logical query plan, the matched data is processed according to the data processing logic to obtain the corresponding data processing results;

[0040] The data processing result is returned as the response result for the corresponding query statement.

[0041] This invention provides an electronic device, comprising:

[0042] Memory, used to store executable instructions;

[0043] A processor, when executing executable instructions stored in the memory, implements the method provided in the embodiments of the present invention.

[0044] This invention provides a storage medium storing executable instructions that, when executed by a processor, implement the method provided in this invention.

[0045] The embodiments of the present invention have the following beneficial effects: Based on the query statement, data in the data file is obtained, and the sharding rules corresponding to the data table are obtained; according to the sharding rules, the data in the data file is sharded to obtain at least two data file shards; each data table shard is matched with the corresponding data file shard to obtain the data in the data table that matches the data file shard; thus, data in the data file is directly obtained without needing to import the data file as a data table into the database or computing engine, simplifying the development process; at the same time, by sharding the data in the data file and then matching it with the corresponding data table shards, since the amount of data contained in the shards is relatively small, the matching speed of the present invention is faster than that of matching the entire data, thus improving the efficiency of data processing. Attached Figure Description

[0046] Figure 1 This is an optional architecture diagram of the data processing system 100 provided in an embodiment of the present invention;

[0047] Figure 2 This is an optional structural schematic diagram of the electronic device provided in an embodiment of the present invention;

[0048] Figure 3 This is an optional flowchart illustrating the data processing method provided in an embodiment of the present invention;

[0049] Figure 4 This is an optional flowchart illustrating the matching process provided in an embodiment of the present invention;

[0050] Figure 5 This is an optional flowchart illustrating the data processing method provided in an embodiment of the present invention;

[0051] Figure 6 This is a schematic diagram of an optional component structure of the data processing apparatus provided in an embodiment of the present invention;

[0052] Figure 7 This is an optional flowchart illustrating the SQL statement parsing process provided in an embodiment of the present invention;

[0053] Figure 8 This is an optional flowchart illustrating the execution plan of the execution logic provided in an embodiment of the present invention;

[0054] Figure 9 This is an optional flowchart illustrating the data processing method provided in an embodiment of the present invention;

[0055] Figure 10 This is a schematic diagram of the composition structure of the data processing device provided in the embodiment of the present invention. Detailed Implementation

[0056] To make the objectives, technical solutions, and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings. The described embodiments should not be regarded as limitations on the present invention. All other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0057] In the following description, references are made to “some embodiments,” which describe a subset of all possible embodiments. However, it is understood that “some embodiments” may be the same subset or different subsets of all possible embodiments and may be combined with each other without conflict.

[0058] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used herein is for the purpose of describing embodiments of the invention only and is not intended to limit the invention.

[0059] In the implementation of this application, the collection and processing of relevant data should strictly comply with the requirements of relevant laws and regulations, obtain the informed consent or separate consent of the personal information subject, and carry out subsequent data use and processing within the scope of laws and regulations and the authorization of the personal information subject.

[0060] Before providing a further detailed description of the embodiments of the present invention, the nouns and terms involved in the embodiments of the present invention will be explained, and the nouns and terms involved in the embodiments of the present invention shall be interpreted as follows.

[0061] 1) Data file: A file obtained by packaging data. For example, the data can be multiple user ID (Identity Document) information, and multiple user ID information can be packaged into a data file.

[0062] 2) Data file fragmentation: Based on specific rules, the data in the data file is grouped to obtain sub-data files.

[0063] 3) Tagging information, used to mark query statements to instruct the computing engine to execute the query statement, such as Hints tags.

[0064] To process data from a corresponding data file within a data table, a data processing method based on the JOIN statement is provided in related technologies: First, the data file to be imported into the database or computing engine to form a data table or table. Then, the JOIN statement is used to intersect specified columns of the data table with specified columns of the data file in a way that links tables within tables.

[0065] For example, assuming the data file is located at the path / test.pkg, and the data table is example, where the primary key of the data table is primary, and other columns include value, then to obtain the average value of the data table that matches the data file based on the JOIN statement, the following steps can be taken:

[0066] 1. Import the data file test.pkg into the analysis engine according to the path / test.pkg, and build the data table test based on the data file test.pkg, and set the primary key to primary;

[0067] 2. The developer enters the query statement: select avg(value) from example e join test ton e.primary=t.primary. The analysis engine, based on the entered query statement, intersects the primary key columns of the data table example and the data table test in a table-to-table join manner.

[0068] Here, because the data file needs to be imported into the database or computing engine as a data table, the steps are quite cumbersome. At the same time, it is also necessary to explicitly specify the columns to be associated in the statement. However, the primary key column names may be different for different data tables. The input statement needs to include the primary key of the data table, the name of the data table, the primary key of the data file, and the name of the table corresponding to the data file, resulting in redundancy in the statement.

[0069] Based on this, a data processing method according to an embodiment of the present invention is proposed to at least solve the above-mentioned problems in the related art, which will be described in detail below.

[0070] See Figure 1 , Figure 1 This is an optional architecture diagram of the data processing system 100 provided in an embodiment of the present invention. In order to support an instance application, the terminal (terminal 400-1 and terminal 400-2 are shown as examples) connects to the server 200 through the network 300. The network 300 can be a wide area network or a local area network, or a combination of the two.

[0071] The terminal is used to receive input query statements and send them to the server.

[0072] Server 200 is configured to receive an input query statement targeting a data table, the query statement carrying the correspondence between the data table and a data file, and instructing the processing of data in the data table corresponding to the data file, wherein the data table includes at least two data table shards; based on the query statement, retrieve data from the data file and retrieve the sharding rules corresponding to the data table; shard the data in the data file according to the sharding rules to obtain at least two data file shards; match each data table shard with its corresponding data file shard to obtain data in the data table that matches the data file shard; and return a response result corresponding to the query statement based on the matched data.

[0073] In practical applications, a server can be a single server configured to support various services, or it can be configured as a server cluster; a terminal can be a variety of user terminals such as smartphones, tablets, and laptops, or it can be any combination of two or more of these data processing devices or other data processing devices, such as wearable computing devices, personal digital assistants (PDAs), desktop computers, cellular phones, media players, navigation devices, game consoles, televisions, or these data processing devices or other data processing devices.

[0074] The electronic device implementing the data processing method of this invention will now be described. See also Figure 2 , Figure 2 This is an optional structural schematic diagram of the electronic device provided in an embodiment of the present invention. Figure 2 The illustrated electronic device includes at least one processor 410, a memory 450, at least one network interface 420, and a user interface 430. The various components of the electronic device are coupled together via a bus system 440. It is understood that the bus system 440 is used to implement communication between these components. In addition to a data bus, the bus system 440 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in… Figure 2 The general labeled all buses as Bus System 440.

[0075] Processor 410 can be an integrated circuit chip with signal processing capabilities, such as a general-purpose processor, a digital signal processor (DSP), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor can be a microprocessor or any conventional processor, etc.

[0076] User interface 430 includes one or more output devices 431 that enable the presentation of media content, including one or more speakers and / or one or more visual displays. User interface 430 also includes one or more input devices 432, including user interface components that facilitate user input, such as a keyboard, mouse, microphone, touch screen display, camera, other input buttons and controls.

[0077] The memory 450 may be removable, non-removable, or a combination thereof. Exemplary hardware devices include solid-state storage, hard disk drives, optical disk drives, etc. The memory 450 may optionally include one or more storage devices physically located away from the processor 410.

[0078] The memory 450 may include volatile memory or non-volatile memory, or both. The non-volatile memory may be read-only memory (ROM), and the volatile memory may be random access memory (RAM). The memory 450 described in this embodiment is intended to include any suitable type of memory.

[0079] In some embodiments, memory 450 is capable of storing data to support various operations, examples of which include programs, modules, and data structures or subsets or supersets thereof, as illustrated below.

[0080] Operating system 451 includes system programs for handling various basic system services and performing hardware-related tasks, such as the framework layer, core library layer, driver layer, etc., for implementing various basic business functions and handling hardware-based tasks;

[0081] The network communication module 452 is used to reach other computing devices via one or more (wired or wireless) network interfaces 420, exemplary network interfaces 420 including: Bluetooth, WiFi, and Universal Serial Bus (USB), etc.

[0082] Presentation module 453 is configured to enable the presentation of information (e.g., a user interface for operating peripheral devices and displaying content and information) via one or more output devices 431 associated with user interface 430 (e.g., a display screen, a speaker, etc.).

[0083] The input processing module 454 is used to detect and translate one or more user inputs or interactions from one or more input devices 432.

[0084] In some embodiments, the apparatus provided in this invention can be implemented in software. Figure 2 A data processing device 455 stored in memory 450 is shown. It may be software in the form of programs and plug-ins, including the following software modules: receiving module 4551, acquiring module 4552, slicing module 4553, matching module 4554, and response module 4555. These modules are logical and can therefore be arbitrarily combined or further split according to the functions they implement.

[0085] The functions of each module will be explained below.

[0086] In other embodiments, the data processing apparatus provided in the embodiments of the present invention can be implemented in hardware. As an example, the data processing apparatus provided in the embodiments of the present invention can be a processor in the form of a hardware decoding processor, which is programmed to execute the data processing method provided in the embodiments of the present invention. For example, the processor in the form of a hardware decoding processor can be one or more application-specific integrated circuits (ASICs), DSPs, programmable logic devices (PLDs), complex programmable logic devices (CPLDs), field-programmable gate arrays (FPGAs), or other electronic components.

[0087] The data processing method provided by the embodiments of the present invention will be described in conjunction with exemplary applications and implementations of the server provided in the embodiments of the present invention.

[0088] See Figure 3 , Figure 3 This is an optional flowchart illustrating the data processing method provided in an embodiment of the present invention, which will be combined with... Figure 3 The steps shown are explained.

[0089] Step 301: The server receives the input query statement for the data table.

[0090] Here, the query statement carries the correspondence between data tables and data files, and is used to instruct on how to process the data in the corresponding data files in the data table. The data table includes at least two data table shards.

[0091] It should be noted that the query statement is a Structured Query Language (SQL) statement. SQL is a special-purpose programming language, a database query and programming language used to access, query, update, and manage relational database systems. In practice, the query statement is constructed by the developers as needed, entered into the terminal, and then sent to the server by the terminal.

[0092] This section explains the storage format of the data table. The data table is stored using a distributed storage structure, meaning it comprises multiple data table shards. These shards are created by partitioning the data within the table according to sharding rules. In practice, the data in the data table can be sharded based on the primary key.

[0093] Step 302: Based on the query statement, retrieve the data from the data file and obtain the sharding rules of the corresponding data table.

[0094] In practice, the data in the corresponding data file and the data table sharding rules are obtained based on the correspondence between the data table and the data file in the query statement.

[0095] For example, if a query statement carries the mapping between the data table `example` and the data file `test.pkg`, then the query should retrieve the data from the data file `test.pkg` and the sharding rules for the corresponding data table `example`. Different data tables will have different sharding rules.

[0096] In some embodiments, the server may obtain data from a data file by: parsing a query statement to obtain the path of the data file; obtaining the data file corresponding to the path; and reading the data from the data file.

[0097] In practice, the path of the data file can be marked according to a specific format so that the server can identify the path of the data file from the query statement, and then retrieve the data file from the corresponding storage location based on the path of the data file, and then read the data in the data file.

[0098] In some embodiments, the path to a data file can be marked using hints, where the format of the hints is / *+ package(“data file path”) * / . For example, for the query statement select / *+package(“ / test.pkg”)* / avg(value) from example, the server can identify the data file path as / test.pkg based on the hint format.

[0099] In some embodiments, the path of the data file can also be marked in the following format: withpackage(“data file path”). For example, for the query statement select with package(“ / test.pkg”) avg(value) from example, the server can identify the data file path as / test.pkg based on the marked format; and for the query statement select avg(value) from example with package(“ / test.pkg”), the server can identify the data file path as / test.pkg based on the marked format.

[0100] In some embodiments, the server may obtain the path of the data file by: generating a syntax tree for the corresponding query statement based on the statement structure of the query statement, wherein the syntax tree includes the tagging information of the path of the corresponding data file; generating a logical query plan for the corresponding query statement based on the syntax tree, and during the generation of the logical query plan, pushing the tagging information down to the data table so that the data table in the generated logical query plan carries the path of the data file; and determining the path of the data file based on the data table in the logical query plan.

[0101] In practice, the syntax tree for the corresponding query statement can be generated as follows: Based on the statement structure of the query statement, lexical analysis is performed, transforming the input query statement into multiple words. Then, syntax analysis is performed, with the results of lexical and syntax analysis serving as input for syntax analysis. Based on lexical analysis, syntax analysis determines whether the user-input words conform to grammatical logic. For example, "SELECT FOO+100 FROM POKES" is a grammatically correct sentence, while "SELECT FOO+100 FROM" is an invalid statement because a table name must follow FROM. As syntax analysis proceeds, the syntax tree for the corresponding query statement is constructed, where each node in the syntax tree represents a word.

[0102] Here, after generating the syntax tree for the corresponding query statement, the syntax tree is traversed according to the execution order of the SQL statements to generate an initial logical query plan, which is then optimized. During the optimization process, the marker information is gradually pushed down to the child nodes until the child nodes are data tables, so that the data tables in the generated logical query plan carry the paths to the data files.

[0103] It should be noted that if a query involves multiple tables, the tagging information can be written as a global condition in the top-level query. In the optimization process, the tagging information will be pushed down to the multiple tables corresponding to the query. Alternatively, the tagging information can be written in the subquery statement corresponding to each table.

[0104] In some embodiments, after a logical query plan is generated, the computing engine executes the logical query plan. During the execution of the logical query plan, when it is necessary to filter data in the data table based on the data file, the path to the data file carrying the data table is obtained.

[0105] It should be noted that the path of the data file carried by the data table in the logical query plan can not only be used to determine the storage location of the data file, but also to identify the filtering of data in the data table based on the data file.

[0106] In some embodiments, obtaining the sharding rules of the corresponding data table includes: determining the storage method of data in at least two data table shards; and determining the sharding rules of the corresponding data table based on the storage method of data in at least two data table shards.

[0107] In practice, data table sharding is achieved by dividing the data in a data table into shards according to sharding rules. The sharding rules for the data table and the sharding rules for the data file must correspond for subsequent matching to be possible. Therefore, the sharding rules for the corresponding data table can be determined based on the storage methods of the data in at least two data table shards.

[0108] For example, a data table may consist of two data table shards, with the primary keys in the two data table shards being 1-1000 and 1001-2000 respectively. In this case, the sharding rule can be based on the primary key, with data in the primary key range of 1-1000 forming one shard and data in the primary key range of 1001-2000 forming another shard.

[0109] Step 303: According to the sharding rules, shard the data in the data file to obtain at least two data file shards.

[0110] In practice, according to the sharding rules, the data in the data file is divided into at least two groups of data, and each group of data constitutes a data file shard.

[0111] Step 304: Match each data table shard with the corresponding data file shard to obtain the data in the data table that matches the data file shard.

[0112] In some embodiments, the server may match each data table shard with the corresponding data file shard in the following manner: by distributing each data file shard to the corresponding compute node through the control node; by loading the corresponding data table shard through each compute node, and matching the loaded data table shard with the data file shard.

[0113] In actual implementation, the corresponding operations of the query statement are executed through the computing engine in the server. The computing engine is distributed and includes a control node and at least two computing nodes.

[0114] Figure 4 This is an optional flowchart illustrating the matching process provided in an embodiment of the present invention. See also... Figure 4 The control node distributes data file fragments to the corresponding compute nodes. Then, each compute node retrieves the corresponding data table fragment from the data table storage. The data file fragment and data table fragment retrieved by each compute node are corresponding. In this way, the retrieved data table fragment and data file fragment are matched by each compute node.

[0115] In some embodiments, the server may match each data table shard with the corresponding data file shard in the following ways: generate a corresponding data file shard cache based on the data file shard; and match each data table shard with the corresponding data file shard cache.

[0116] In practice, the control node distributes data file shards to the corresponding compute nodes. The compute nodes store the data in the data file shards as a cache, resulting in a data file shard cache. Then, each data table shard is matched with the corresponding data file shard cache, thus enabling fast data matching.

[0117] In some embodiments, the server can match each data table shard with the corresponding data file shard in the following ways: match the primary key data of the corresponding primary key in each data table shard with the primary key data of the corresponding primary key in the data file shard to obtain the primary key data in the data table that is the same as the primary key data in the data file shard; and obtain the data corresponding to the matched primary key data.

[0118] In practice, the data in the data table is filtered based on the primary key data in the data file shards. That is, only data records whose primary key data is the same as the corresponding primary key data in the data file shards are retained. For each primary key data in a data shard, it is determined whether the data file shard contains that primary key data. If it does, the data corresponding to that primary key data is retained; otherwise, the data corresponding to that primary key data is discarded.

[0119] For example, if a data table shard contains data with primary keys from 1 to 1000, and the corresponding data file shard contains data with primary keys from 500 to 600, then only the data with primary keys from 500 to 600 in the data table shard should be retained.

[0120] Step 305: Based on the matched data, return the response result of the corresponding query statement.

[0121] In practice, a query statement can be used solely to filter data in a data table based on a data file, in which case the matched data is the query result. Alternatively, a query statement can be used to further process the filtered data after filtering it from a data table based on a data file, such as by summing or averaging. In this case, after obtaining the matched data, subsequent operations will be performed based on the query statement.

[0122] In some embodiments, after obtaining the query results of the corresponding query statement, the query results can be stored.

[0123] In some embodiments, the server may return the response result of the corresponding query statement in the following ways: storing the matched data in the form of a data table or a data file; generating data storage prompt information for the corresponding data, and returning the data storage prompt information as the response result of the corresponding query statement.

[0124] In practice, the query statement is only used to filter data in the data table based on the data file. The matched data confirms the query result of the corresponding query statement. The matched data is stored in the form of a data table or a data file. After successful storage, a data storage prompt message is generated and returned to the terminal so that the terminal can display the corresponding prompt message to inform the user.

[0125] In some embodiments, the server may return the response result of the corresponding query statement in the following ways: obtain the logical query plan corresponding to the query statement, the logical query plan being used to indicate the data processing logic of the corresponding data; based on the logical query plan, process the matched data according to the data processing logic to obtain the corresponding data processing result; and return the data processing result as the response result of the corresponding query statement.

[0126] In practice, query statements can also be used to further process the data obtained after filtering the data in the data table based on the data file, such as summing or averaging. After obtaining the matched data, the data is further processed based on the corresponding data processing logic to obtain the query results of the corresponding query statement.

[0127] For example, for the query statement `select / *+ package(“ / test.pkg”) * / avg(value) from example`, after obtaining the matched data, the average value of the `value` column in the data is calculated to obtain the data processing result.

[0128] It should be noted that after obtaining the data processing result, the result can be returned directly to the terminal, or the result can be stored and a corresponding prompt message can be returned to the terminal.

[0129] The embodiments of this invention have the following beneficial effects: Based on a query statement, data in a data file is obtained, and the sharding rules of the corresponding data table are also obtained; according to the sharding rules, the data in the data file is sharded to obtain at least two data file shards; each data table shard is matched with the corresponding data file shard to obtain the data in the data table that matches the data file shard; thus, data in the data file is directly obtained without needing to import the data file as a data table into a database or computing engine, simplifying the development process; at the same time, by sharding the data in the data file and then matching it with the corresponding data table shards, since the amount of data contained in the shards is relatively small, the matching speed of this invention is faster than matching the entire dataset, thus improving the efficiency of data processing.

[0130] The following example, using the average value of the values ​​in the data table that match the data file, illustrates the data processing method of this embodiment. The data file is located at the path / test.pkg, and the data table is named example. The primary key of the data table is 'primary,' and the other columns include 'value.' Figure 5 This is an optional flowchart illustrating the data processing method provided in an embodiment of the present invention. See [link / reference]. Figure 5 The data processing method of this invention includes:

[0131] Step 501: The terminal receives the input query statement: select / *+ package(“ / test.pkg”) * / avg(value) from example.

[0132] Step 502: Send the query statement to the server.

[0133] Step 503: The server determines the data file path as / test.pkg from the query statement based on the format of the hints.

[0134] Step 504: Based on the data file path / test.pkg, retrieve the test.pkg file from the storage location corresponding to the path.

[0135] Step 505: Read the data from the test.pkg file.

[0136] Step 506: Obtain the sharding rules corresponding to the data table example.

[0137] Step 507: According to the sharding rules, shard the data in the test.pkg file to obtain at least two data file shards.

[0138] Step 508: Obtain at least two data table shards included in the data table.

[0139] Step 509: Match the primary key data of the corresponding primary key in each data table shard with the primary key data in the corresponding data file shard to obtain the primary key data in the data table that matches the data file.

[0140] Step 510: Obtain the value corresponding to the primary key data that matches the data file.

[0141] Step 511: Calculate the average value of the obtained values.

[0142] Step 512: Store the average value of the values ​​in the form of a data table and generate the corresponding data storage prompt information.

[0143] Step 513: Return data storage prompt message.

[0144] Step 514: Present the prompt message.

[0145] The following will describe an exemplary application of the embodiments of the present invention in a practical application scenario.

[0146] Figure 6 This is a schematic diagram of an optional component structure of the data processing apparatus provided in an embodiment of the present invention. See also... Figure 6 The data processing device includes an SQL parser 601, a computing engine 602, a data table storage 603, and a data file storage 604.

[0147] SQL parser 601 is used to parse the calculation logic of the input SQL statement (query statement) and generate the corresponding logical execution plan (logical query plan).

[0148] The calculation engine 602 is used to perform corresponding calculations based on the logical execution plan (logical query plan) generated by the SQL parser;

[0149] The data table storage 603 is used to store the data table to be processed. The data table is stored in a distributed storage structure, that is, a data table includes multiple data table shards. The data table shards are obtained by dividing the data in the data table according to the primary key.

[0150] The data file storage 604 is used for data files, which can be uploaded by the user or generated by the computing engine after performing corresponding calculations.

[0151] First, the process of parsing SQL statements will be explained. Figure 7 This is an optional flowchart illustrating the SQL statement parsing process provided in an embodiment of the present invention. See [link / reference]. Figure 7 The SQL statement parsing process includes:

[0152] Step 701: Generate the corresponding syntax tree based on the SQL statement.

[0153] In practice, the SQL statement undergoes syntactic analysis, transforming it into multiple words. Then, syntactic analysis is performed, with the results of syntactic and lexical analysis serving as input. Based on the lexical analysis, the syntactic analysis determines whether the user-input words conform to grammatical logic. For example, "SELECT FOO+100 FROM POKES" is a grammatically correct sentence, while "SELECT FOO+100 FROM" is invalid because a table name must follow "FROM". As the syntactic analysis progresses, a syntax tree is constructed for the corresponding query statement, where each node in the syntax tree represents a word.

[0154] Here, the syntax tree includes tagging information, such as hints, used to mark the paths of data files.

[0155] The format of the number package hints is: / *+ package(“number package path”) * / , used in SQL t-queries or subqueries, for example:

[0156] Query statement: select / *+package(“ / test.pkg”)* / avg(value) from example; or, subquery statement: select avg(value) from (select / *+package(“ / test.pkg”)* / value asvalue from example) a.

[0157] It should be noted that the SQL statement structure does not have to use hints; special SQL syntax can be used instead, such as:

[0158] The query `select / *+package(“ / test.pkg”)* / avg(value) from example` can be represented in a similar way to the following:

[0159] The syntax is: `SELECT WITH package(“ / test.pkg”) AVG(value) FROM example`, or `SELECT AVG(value) FROM example WITH package(“ / test.pkg”). Essentially, any valid way to represent the relationship between the data table and the number packages is acceptable.

[0160] Step 702: Generate a logical execution plan based on the syntax tree.

[0161] After generating the syntax tree for the corresponding SQL statement, the syntax tree is traversed according to the execution order of the SQL statement to generate a logical execution plan.

[0162] Step 703: Optimize the logical execution plan.

[0163] After generating the logical execution plan, it needs to be optimized to avoid redundant calculations. In actual implementation, during the optimization process, the marking information is gradually pushed down to the child nodes until the child nodes are data tables, so that the data tables in the generated logical execution plan carry the paths to the data files.

[0164] It should be noted that if a query involves multiple tables, the tagging information can be written as a global condition in the top-level query. In the optimization process, the tagging information will be pushed down to the multiple tables corresponding to the query. Alternatively, the tagging information can be written in the subquery statement corresponding to each table.

[0165] Here, the data table carries the data file path parameter: with_package, which indicates that the data table needs to be filtered based on the data file, as well as the storage path of the data file.

[0166] Next, the process of the computing engine executing the logic execution plan will be explained.

[0167] In practice, the computing engine is distributed, consisting of one control node and at least two computing nodes. Figure 8 This is an optional flowchart illustrating the execution plan of the execution logic provided in an embodiment of the present invention. See also... Figure 8 When the logical execution plan involves loading data tables, it determines whether filtering of the data tables based on the data file is necessary. If so, it executes as follows: Figure 8 The steps shown are as follows:

[0168] Step 801: The control node retrieves data from the data file in the data file storage.

[0169] In actual implementation, the path information in the with_package field of the data table is loaded, the data file is obtained from the storage location of the corresponding path information in the data file storage, and the data in the data file is read.

[0170] Step 802: The control node shards the data in the data file according to the sharding rules of the corresponding data table, resulting in at least two data file shards.

[0171] Here, since the data tables are stored in a distributed storage structure, the sharding rules for the corresponding data tables can be determined based on their storage format. Different data tables will have different sharding rules.

[0172] In practice, a dedicated data file sharding service can be set up in the computing engine, and the loading and distribution of data files are handled by this separate data file sharding service.

[0173] Step 803: The control node distributes at least two data file shards to the corresponding compute nodes.

[0174] In practice, after the control node distributes the data file fragments to the corresponding compute nodes, the compute nodes store the data in the data file fragments as a cache.

[0175] Step 804: Each compute node loads the corresponding data table shard from the data table storage.

[0176] Step 805: The compute node matches the data table shards with the data file shards to obtain the data in the data table that matches the data file.

[0177] In practice, the primary key data of the corresponding primary key (number) in the data table shard is matched with the primary key (number) data in the data file shard to obtain the primary key data corresponding to the data file, and then the data record corresponding to the obtained primary key data is retrieved.

[0178] After performing the above operations, based on the logical execution plan, determine if there are any remaining operations. If so, continue executing the subsequent operations. After the entire logical execution plan has been completed, export the execution results and store them as a data table or data file.

[0179] The data processing method of this invention will be described in its entirety below, taking into account the parsing process of the SQL statement and the execution process of the logical execution plan. Figure 9 This is an optional flowchart illustrating the data processing method provided in an embodiment of the present invention. See [link / reference]. Figure 9 The data processing method of the present invention includes:

[0180] Step 901: The SQL parser parses the SQL statement and generates a logical execution plan.

[0181] Step 902: The SQL parser sends the logical execution plan to the control node.

[0182] Step 903: The control node retrieves the data file from the data file storage.

[0183] Step 904: The control node splits the data in the data file into fragments, resulting in at least two data file fragments.

[0184] Step 905: The control node distributes the data file fragments to the corresponding compute nodes.

[0185] Step 906: The compute node stores the data in the data file shards as a cache.

[0186] Step 907: The compute node loads the corresponding data table shard from the data table store.

[0187] Step 908: The compute node matches the primary key data in the data table shard with the data file shard cache to obtain the primary key data in the data table that matches the data file.

[0188] Step 909: The compute node obtains the data record corresponding to the primary key data obtained from the matching.

[0189] Step 910: The computing node executes the subsequent logical execution plan based on the data record to obtain the data processing result.

[0190] Step 911: The computing node stores the data processing results in the form of data files to the data file storage.

[0191] The present invention has the following beneficial effects:

[0192] 1. Simplify how data developers describe computational logic, allowing them to focus on the computation process itself when writing computational logic, without needing to worry about data table information.

[0193] 2. Simplify the calculation process. Traditionally, if a similar process is described using SQL, it needs to be handled by JOIN calculation. JOIN calculation is often performed in the database as a matching and filtering process after the full data content is loaded, which consumes a lot of space and time.

[0194] On a computing node consisting of 20 machines, the time (in seconds) for filtering and calculating 1 billion data points using different data file sizes and two different methods is as follows:

[0195]

[0196] As shown in the table above, this invention can reduce the execution time of filtering data tables based on data files and improve execution efficiency.

[0197] The following description further illustrates an exemplary structure of the data processing device 455 provided in this embodiment of the invention as a software module. Figure 10 This is a schematic diagram of the composition structure of the data processing device provided in an embodiment of the present invention. See also: Figure 10 The data processing apparatus of the present invention includes:

[0198] The receiving module 4551 is used to receive an input query statement for a data table, the query statement carrying the correspondence between the data table and the data file, and is used to instruct the processing of the data in the data table corresponding to the data file, wherein the data table includes at least two data table fragments;

[0199] The acquisition module 4552 is used to acquire data from the data file based on the query statement, and to acquire the sharding rules corresponding to the data table;

[0200] The sharding module 4553 is used to shard the data in the data file according to the sharding rules to obtain at least two data file shards;

[0201] The matching module 4554 is used to match each of the data table shards with the corresponding data file shards to obtain the data in the data table that matches the data file shards;

[0202] The response module 4555 is used to return the response result corresponding to the query statement based on the matched data.

[0203] In some embodiments, the acquisition module 4552 is further configured to parse the query statement to obtain the path of the data file;

[0204] Based on the path of the data file, obtain the data file corresponding to the path;

[0205] Read the data from the data file.

[0206] In some embodiments, the acquisition module 4552 is further configured to generate a syntax tree corresponding to the query statement based on the statement structure of the query statement, wherein the syntax tree includes tagging information corresponding to the path of the data file;

[0207] Based on the syntax tree, a logical query plan corresponding to the query statement is generated, and

[0208] During the generation of the logical query plan, the tagging information is pushed down to the data table so that the data table in the generated logical query plan carries the path of the data file;

[0209] The path of the data file is determined based on the data table in the logical query plan.

[0210] In some embodiments, the acquisition module 4552 is further configured to determine the storage method of data in the at least two data table shards;

[0211] Based on the storage method of the data in the at least two data table shards, the sharding rules corresponding to the data table are determined.

[0212] In some embodiments, the matching module 4554 is further configured to distribute each data file fragment to the corresponding computing node through the control node;

[0213] Each computing node loads the corresponding data table shards, and

[0214] The loaded data table shards are matched with the data file shards.

[0215] In some embodiments, the matching module 4554 is further configured to generate a corresponding data file fragment cache based on the data file fragments;

[0216] The data table shards are matched with the corresponding data file shard caches.

[0217] In some embodiments, the matching module 4554 is further configured to match the primary key data corresponding to the primary key in each of the data table shards with the data corresponding to the primary key in the data file shards, so as to obtain primary key data in the data table that is the same as the data corresponding to the primary key in the data file shards;

[0218] Retrieve the data corresponding to the matched primary key data.

[0219] In some embodiments, the response module 4555 is further configured to store the matched data in the form of a data table or a data file;

[0220] Generate data storage prompt information corresponding to the data, and

[0221] The data storage prompt information is returned as the response result of the corresponding query statement.

[0222] In some embodiments, the response module 4555 is further configured to obtain a logical query plan corresponding to the query statement, wherein the logical query plan is used to indicate the data processing logic corresponding to the data;

[0223] Based on the logical query plan, the matched data is processed according to the data processing logic to obtain the corresponding data processing results;

[0224] The data processing result is returned as the response result for the corresponding query statement.

[0225] This invention provides a storage medium storing executable instructions. When these executable instructions are executed by a processor, they cause the processor to perform the method provided in this invention, for example... Figure 3 The method shown.

[0226] In some embodiments, the storage medium may be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, flash memory, magnetic surface memory, optical disk, or CD-ROM; or it may be a variety of devices including one or any combination of the above-mentioned memories.

[0227] In some embodiments, executable instructions may take the form of a program, software, software module, script, or code, written in any form of programming language (including compiled or interpreted languages, or declarative or procedural languages), and may be deployed in any form, including as a standalone program or as a module, component, subroutine, or other unit suitable for use in a computing environment.

[0228] As an example, executable instructions may, but do not necessarily, correspond to files in a file system. They may be stored as part of a file that holds other programs or data, for example, in one or more scripts in a Hyper Text Markup Language (HTML) document, in a single file dedicated to the program in question, or in multiple collaborating files (e.g., a file that stores one or more modules, subroutines, or code sections).

[0229] As an example, executable instructions can be deployed to execute on a single computing device, or on multiple computing devices located in one location, or on multiple computing devices distributed across multiple locations and interconnected via a communication network.

[0230] The above description is merely an embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and scope of the present invention are included within the scope of protection of the present invention.

Claims

1. A data processing method, characterized by, The method comprises: receiving an input query statement for a data table, the query statement carrying a correspondence between the data table and a data file and a path of the data file, for indicating processing of data corresponding to the data file in the data table, wherein the data table comprises at least two data table shards; parsing the query statement to obtain the path of the data file marked in a specific format; obtaining the data file corresponding to the path of the data file according to the path of the data file; determining a storage mode of data in the at least two data table shards, and determining a shard rule corresponding to the data table based on the storage mode of data in the at least two data table shards; reading data in the data file, and performing sharding on the data in the data file according to the shard rule to obtain at least two data file shards; distributing each data file shard to a corresponding computing node through a control node, and loading a corresponding data table shard through each computing node; matching primary key data corresponding to a primary key in each data table shard with data corresponding to the primary key in the data file shard to obtain primary key data in the data table that is the same as the data corresponding to the primary key in the data file shard; returning a response result corresponding to the query statement based on data corresponding to the primary key data obtained through matching.

2. The method of claim 1, wherein, The parsing of the query statement to obtain the path of the data file marked in a specific format comprises: generating a syntax tree corresponding to the query statement according to a statement structure of the query statement, the syntax tree comprising marking information of the path of the data file; generating a logical query plan corresponding to the query statement based on the syntax tree, and pushing down the marking information to the data table in the process of generating the logical query plan, so that the data table in the generated logical query plan carries the path of the data file; determining the path of the data file based on the data table in the logical query plan.

3. The method of claim 1, wherein, The method further comprises: generating a corresponding data file shard cache based on the data file shard; matching each data table shard with a corresponding data file shard cache.

4. The method of claim 1, wherein the returning of the response result corresponding to the query statement based on the data corresponding to the primary key data obtained through matching comprises: storing the data corresponding to the primary key data obtained through matching in the form of a data table or a data file; generating data storage prompt information corresponding to the data, and returning the data storage prompt information as the response result corresponding to the query statement.

5. The method of claim 1, wherein the returning of the response result corresponding to the query statement based on the data corresponding to the primary key data obtained through matching comprises: obtaining a logical query plan corresponding to the query statement, the logical query plan being used to indicate data processing logic corresponding to the data. Based on the logical query plan, data corresponding to the primary key data obtained through matching is processed according to the data processing logic, and a corresponding data processing result is obtained. The data processing result is returned as a response result corresponding to the query statement.

6. A data processing apparatus, characterized by The apparatus comprises: A receiving module is configured to receive an input query statement for a data table, the query statement carrying a corresponding relationship between the data table and a data file and a path of the data file, and used to instruct processing of data corresponding to the data file in the data table, wherein the data table comprises at least two data table shards. An obtaining module is configured to parse the query statement to obtain the path of the data file marked in a specific format, and obtain a data file corresponding to the path of the data file according to the path of the data file, and read data in the data file. A determining module is configured to determine a storage mode of data in the at least two data table shards, and determine a shard rule corresponding to the data table based on the storage mode of data in the at least two data table shards. A sharding module is configured to shard the data in the data file according to the shard rule to obtain at least two data file shards. A matching module is configured to distribute each data file shard to a corresponding computing node through a control node, and load a corresponding data table shard through each computing node. Primary key data corresponding to a primary key in each data table shard is matched with data corresponding to the primary key in the data file shard to obtain primary key data in the data table that is the same as the data corresponding to the primary key in the data file shard. A response module is configured to return a response result corresponding to the query statement based on data corresponding to the primary key data obtained through matching.

7. The apparatus of claim 6, wherein The obtaining module is further configured to generate a syntax tree corresponding to the query statement according to a statement structure of the query statement, the syntax tree comprising marked information corresponding to the path of the data file; generate a logical query plan corresponding to the query statement based on the syntax tree, and push the marked information to the data table in the process of generating the logical query plan, so that the data table in the generated logical query plan carries the path of the data file; determine the path of the data file based on the data table in the logical query plan.

8. The apparatus of claim 6, wherein The matching module is further configured to generate a corresponding data file shard cache based on the data file shard; match each data table shard with the corresponding data file shard cache.

9. The apparatus of claim 6, wherein The response module is further configured to store the data obtained through matching in the form of a data table or a data file; generate data storage prompt information corresponding to the data, and return the data storage prompt information as the response result corresponding to the query statement.

10. The apparatus of claim 6, wherein The response module is further configured to acquire a logical query plan corresponding to the query statement, the logical query plan being used to indicate data processing logic corresponding to the data; Based on the logical query plan, the matched data is processed according to the data processing logic to obtain a corresponding data processing result; The data processing result is returned as a response result corresponding to the query statement.

11. An electronic device, comprising: Comprise: A memory configured to store executable instructions; A processor configured to execute the executable instructions stored in the memory to implement the data processing method of any one of claims 1 to 5.

12. A computer-readable storage medium storing executable instructions, wherein the instructions, when executed by a processor, cause the processor to perform operations comprising: The executable instructions are executed by the processor to implement the data processing method of any one of claims 1 to 5.

Images