A method and apparatus for indexing ORC files in a relational database.

Abstract

This invention proposes a method and apparatus for indexing ORC files in a relational database. The method includes: responding to a query request for data within a data index, using a preset algorithm to determine the location range of the ORC file containing the data in the data index; and determining the specific location of the data based on the location range and a pre-configured buffer, wherein the buffer is used to store newly added ORC files. This invention provides an index for accessing ORC files in a relational database, improving the performance of ORC querying in the relational database. Furthermore, it addresses the shortcomings of learned indexes by adding an easily maintainable buffer, further enhancing performance.

Description

Technical Field

[0001] This invention relates to the field of data indexing technology, and in particular to a method for indexing ORC files in a relational database. Background Technology

[0002] ORC files, or OptimizedRecordColumnar, are a columnar storage format used in Hadoop. They are stored in binary format and cannot be read directly. ORC files contain a lot of metadata, and their structure facilitates querying and reading in big data scenarios. Therefore, ORC is suitable for big data analysis and OLAP (Online Analytics), but not for OLTP (Online Transaction Processing) scenarios.

[0003] However, in actual production environments, users often need a single dataset that serves both OLAP and OLTP purposes. This dataset is used for both big data analytics and transactional business queries. Transactional business queries are characterized by relatively simple query logic but high performance requirements.

[0004] Currently, users solve this problem by extracting data from Hadoop and storing it in a relational database. This results in two formats for the same data: one for big data analysis and one for business queries.

[0005] However, the problem with this is that it consumes a large amount of storage space, and the data extraction process is slow and the data is not updated in a timely manner. One way to further solve this problem is to allow relational databases to directly access ORC format files, but this has the problem of low query performance.

[0006] Furthermore, ORC files are self-indexed, containing metadata and index information. Currently, there is no special technology in the industry for accessing ORC files in relational databases; most rely on this metadata to accelerate queries. However, in big data environments, with massive amounts of data, there can be many ORC files, making it impractical to implement this effectively in real-world applications. Summary of the Invention

[0007] The technical problem to be solved by this invention is how to achieve fast indexing of ORC files in a big data environment; in view of this, this invention provides a method and apparatus for indexing ORC files in a relational database.

[0008] The technical solution adopted in this invention is a method for indexing ORC files in a relational database, comprising:

[0009] Step S1: In response to a query request for data in the data index, a preset algorithm is used to determine the location range of the ORC file containing the data in the data index.

[0010] Step S2: Based on the location range and the pre-configured buffer, determine the specific location of the data, wherein the buffer is used to store the newly added ORC file.

[0011] In one embodiment, the method further includes:

[0012] The preset algorithm is configured based on the acquired ORC file and the newly added and stored ORC file in the buffer.

[0013] In one implementation, determining the location range of the ORC file containing the data within the data index using a preset algorithm includes:

[0014] The preset algorithm includes a first computing layer and a second computing layer. Based on the first computing layer, a corresponding position of the data in the second computing layer and a first fluctuation range are determined.

[0015] Based on the second computing layer's position of the data within this computing layer and the first fluctuation range, the ORC file position and the second fluctuation range corresponding to the data are determined, wherein the ORC file position and the second fluctuation range are the position range of the data in the data index.

[0016] In one implementation, determining the specific location of the data based on the location range and a pre-configured buffer includes:

[0017] Traverse the data index within the specified location range to determine the specific location of the data within the data index;

[0018] Traverse the ORC files in the buffer to determine the specific location of the data in the buffer;

[0019] The positions in the data index and the positions in the buffer are merged and output.

[0020] In one implementation, configuring the preset algorithm based on the acquired ORC file and the newly added and stored ORC file in the buffer includes:

[0021] The preset algorithm is configured using a pre-configured ORC file;

[0022] Newly added ORC files are acquired and stored in the buffer. When the number of ORC files in the buffer exceeds a pre-configured threshold, all ORC files in the current buffer are used for further configuration of the preset algorithm, and the buffer is cleared.

[0023] Another aspect of the present invention provides an indexing device for ORC files in a relational database, comprising:

[0024] The first indexing module is configured to, in response to a query request for data within the data index, use a preset algorithm to determine the location range of the ORC file containing the data within the data index.

[0025] The second indexing module is configured to determine the specific location of the data based on the location range and a pre-configured buffer, wherein the buffer is used to store newly added ORC files.

[0026] In one embodiment, the apparatus further includes:

[0027] The neural network module is configured to configure the preset algorithm based on the acquired ORC file and the newly added and stored ORC file in the buffer.

[0028] Another aspect of the present invention provides an electronic device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program, when executed by the processor, implements the steps of the indexing method for ORC files in a relational database as described in any of the preceding claims.

[0029] Another aspect of the present invention provides a computer storage medium storing a computer program that, when executed by a processor, implements the steps of the indexing method for ORC files in a relational database as described in any of the preceding claims.

[0030] By adopting the above technical solution, the present invention has at least the following advantages:

[0031] 1) This invention provides an index for accessing ORC files in relational databases, which can improve the performance of ORC queries in relational databases.

[0032] 2) This invention improves upon the shortcomings of learning indexes by adding an easily maintainable buffer, thereby further enhancing performance. Attached Figure Description

[0033] Figure 1 This is a schematic diagram of the indexing method for ORC files in a relational database according to an embodiment of the present invention;

[0034] Figure 2 This is a schematic diagram of another method for indexing ORC files in a relational database according to an embodiment of the present invention;

[0035] Figure 3 This is a schematic diagram of the preset algorithm flow according to an embodiment of the present invention;

[0036] Figure 4 This is a schematic diagram of a preset algorithm structure according to an embodiment of the present invention;

[0037] Figure 5 This is a schematic diagram of the processing flow of a query operation according to an embodiment of the present invention;

[0038] Figure 6 This is a schematic diagram of the processing flow for newly added data according to an embodiment of the present invention;

[0039] Figure 7 This is a schematic diagram of the structure of an indexing device for ORC files in a relational database according to an embodiment of the present invention;

[0040] Figure 8 This is a schematic diagram of an electronic device structure according to an embodiment of the present invention. Detailed Implementation

[0041] To further illustrate the technical means and effects of the present invention in achieving its intended purpose, the present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments.

[0042] In the accompanying drawings, the thickness, size, and shape of the objects have been slightly exaggerated for ease of illustration. The drawings are for illustrative purposes only and are not drawn to scale.

[0043] It should also be understood that the terms "comprising," "including," "having," "containing," and / or "comprising," when used in this specification, indicate the presence of the stated features, integrals, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components, and / or combinations thereof. Furthermore, when expressions such as "at least one of..." appear after a list of listed features, they modify the entire listed feature, not individual elements in the list. Additionally, when describing embodiments of this application, the word "may" is used to mean "one or more embodiments of this application." And the term "exemplary" is intended to refer to an example or illustration.

[0044] As used herein, the terms “basically,” “approximately,” and similar terms are used as terms of approximation rather than terms of degree, and are intended to describe inherent biases in measured or calculated values ​​that will be recognized by those skilled in the art.

[0045] Unless otherwise specified, all terms used herein (including technical and scientific terms) shall have the same meaning as commonly understood by one of ordinary skill in the art to which this application pertains. It should also be understood that terms (e.g., those defined in common dictionaries) shall be interpreted as having the meaning consistent with their meaning in the context of the relevant art and shall not be interpreted in an idealized or overly formal sense unless expressly so specified herein.

[0046] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.

[0047] The steps described in the specification and the flowcharts in the accompanying drawings of this invention are not necessarily to be strictly followed according to the step numbers; the execution order of the steps can be changed. Furthermore, certain steps can be omitted, multiple steps can be combined into one step, and / or one step can be broken down into multiple steps.

[0048] The first embodiment of the present invention provides a method for indexing ORC files in a relational database, such as... Figure 1 As shown, it includes:

[0049] Step S1: In response to a query request for data within the data index, a preset algorithm is used to determine the location range of the ORC file containing the data within the data index.

[0050] Step S2: Based on the location range and the pre-configured buffer, determine the specific location of the data, wherein the buffer is used to store the newly added ORC file.

[0051] For reference Figure 1 or Figure 2 The method provided in this embodiment will be described in detail step by step below.

[0052] Step S1: In response to a query request for data within the data index, a preset algorithm is used to determine the location range of the ORC file containing the data within the data index.

[0053] In this embodiment, the preset algorithm can be configured based on the acquired ORC file and the ORC file added and stored in the buffer.

[0054] This embodiment uses ORC files to train the neural network, generating a learned index model. New data is placed in a buffer and stored as an ORC file. When searching for data, the search is first performed through the learned index, and then the buffer is searched. Once the buffer reaches a certain threshold, the ORC files in the buffer are used to train the neural network, and then the buffer is cleared.

[0055] Specifically, the preset algorithm can be a neural network model, including a first computational layer and a second computational layer.

[0056] The first computing layer is used to determine a corresponding position of the data in the second computing layer and a first fluctuation range;

[0057] The second computation layer is used to determine the ORC file location and the second fluctuation range corresponding to the data based on the data's location within this computation layer and the first fluctuation range. The ORC file location and the second fluctuation range are, in effect, the location range of the data within the data index.

[0058] For example, its overall structure is as follows Figure 3 As shown. The input to the prediction model is the key value to be searched. The prediction model outputs a predicted number, "guess," which represents the ORC file number where this key value resides. Because the prediction model cannot provide 100% accurate location information (otherwise, it would make the prediction model extremely complex), it can only provide a range. The correct ORC file number fluctuates within this range, with a fluctuation amplitude of wave. Therefore, after the prediction model provides the guess value, the system needs to traverse the ORC files with numbers between guess-wave and guess+wave to find the actual ORC file.

[0059] To improve performance and reduce complexity, the prediction model is designed with a two-layer structure, such as... Figure 4 As shown. The first layer of the prediction model has a sub-prediction model 1-1. For an input key, it outputs a value guessA (the value of guessA ranges from 1 to 1000). guessA represents the position of a certain sub-prediction model in the second layer, i.e., prediction model 2-guessA. Sub-prediction model 1-1 also outputs the fluctuation range waveA. The accurate position of the second-layer sub-prediction model is between 2-(guessA-waveA) and 2-(guessA+waveA). There are a maximum of 1000 second-layer sub-prediction models, from 2-1 to 2-1000. Its input is the query key passed from sub-prediction model 1-1, and its output is the ORC file number position guessB and the fluctuation range waveB.

[0060] Similarly, ORC files can be used to train neural networks on old data. That is, the prediction model described above can be used to train existing data, and the trained prediction model can output the range of ORC file numbers containing that key, given a key value. (For example, the returned result range is 6 ORC files numbered 100-105; the specific one needs to be determined by traversing these 6 files).

[0061] Step S2: Based on the location range and the pre-configured buffer, determine the specific location of the data, wherein the buffer is used to store the newly added ORC file.

[0062] Specifically, the data index can be traversed within its position range to determine the specific position of the data in the data index; then the ORC file in the buffer can be traversed to determine the specific position of the data in the buffer; finally, the position in the data index and the position in the buffer are merged and output.

[0063] In this embodiment, a virtual buffer can be pre-established. The virtual buffer is a virtual region that corresponds to a storage space in memory, containing newly added ORC files that have not yet been used in neural network training.

[0064] For example, suppose 100 ORC files numbered 1 to 100 have already been trained into a neural network to generate a learned index model. Later, 10 new ORC files numbered 101 to 110 are added. These 10 files have not yet participated in neural network training, so they are placed in a buffer. However, the buffer does not actually exist physically; it merely records the file numbers, 101 to 110. The physical ORC files numbered 101 to 110 are actually stored in the same disk directory as the ORC files numbered 1 to 100. This avoids moving the physical files. When clearing the buffer, only the cached numbers 101 to 110 are deleted.

[0065] Furthermore, when a query request is received, the system first searches the learned index, then searches the ORC file in the cache, and finally merges the two search results before returning them. The specific process is as follows: Figure 5 .

[0066] Data queries are performed using a key. After inputting the key into the learning index model, the model provides a range with an error of wave, including one or more ORC files. The system iterates through these files to see if they contain the key; this search result is recorded as (Result 01). The system then continues iterating through the ORC files in the buffer to see if they contain the key; this search result is recorded as (Result 02). Finally, (Result 01) and (Result 02) are merged and returned to the user.

[0067] In this embodiment, as Figure 6 As shown, a preset algorithm can be configured using a pre-configured ORC file; alternatively, newly added ORC files can be obtained and stored in a buffer. When the number of ORC files in the buffer exceeds a pre-configured threshold, all ORC files in the current buffer are used for further configuration of the preset algorithm, and the buffer is cleared.

[0068] In other words, when new data is added, if the number of cached ORC files has not reached the threshold, the number of the new ORC file is added to the buffer. If the threshold is reached, the ORC files in the buffer are used for neural network training, and the buffer is cleared.

[0069] It is understood that the threshold in this embodiment can be a range, such as 500-1000. When the number of buffer files reaches 500, the system will find a suitable time to train the buffer files for neural networks based on the amount of data and the system's busy level; when the number of buffer files reaches 1000, the system will immediately train the buffer files for neural networks.

[0070] It should be noted that since this system only targets scenarios where data only increases and never decreases, and the data does not change, merging the results simply requires adding the two results together.

[0071] Compared with the prior art, this embodiment has at least the following advantages:

[0072] 1) This embodiment designs an index for accessing ORC files in relational databases, which can improve the performance of querying ORC files in relational databases.

[0073] 2) This embodiment improves upon the shortcomings of learning indexes by adding an easily maintainable buffer, further enhancing performance.

[0074] The second embodiment of the present invention, corresponding to the first embodiment, introduces an indexing device for ORC files in a relational database, such as... Figure 7 As shown, it includes:

[0075] The first indexing module is configured to, in response to a query request for data within the data index, use a preset algorithm to determine the location range of the ORC file containing the data within the data index.

[0076] The second indexing module is configured to determine the specific location of the data based on the location range and a pre-configured buffer, wherein the buffer is used to store newly added ORC files.

[0077] In this embodiment, the device further includes:

[0078] The neural network module is configured to configure the preset algorithm based on the acquired ORC file and the newly added and stored ORC file in the buffer.

[0079] In this embodiment, the first index module is further configured as follows:

[0080] The preset algorithm includes a first computing layer and a second computing layer. Based on the first computing layer, a corresponding position of the data in the second computing layer and a first fluctuation range are determined.

[0081] Based on the second computing layer's position of the data within this computing layer and the first fluctuation range, the ORC file position and the second fluctuation range corresponding to the data are determined, wherein the ORC file position and the second fluctuation range are the position range of the data in the data index.

[0082] In one embodiment, the second index module is further configured to:

[0083] Traverse the data index within the specified location range to determine the specific location of the data within the data index;

[0084] Traverse the ORC files in the buffer to determine the specific location of the data in the buffer;

[0085] The positions in the data index and the positions in the buffer are merged and output.

[0086] In this embodiment, the neural network module is further configured as follows:

[0087] The preset algorithm is configured using a pre-configured ORC file;

[0088] Newly added ORC files are acquired and stored in the buffer. When the number of ORC files in the buffer exceeds a pre-configured threshold, all ORC files in the current buffer are used for further configuration of the preset algorithm, and the buffer is cleared.

[0089] A third embodiment of the present invention provides an electronic device, such as... Figure 8 As shown, it can be understood as a physical device, including a processor and a memory storing processor-executable instructions. When the instructions are executed by the processor, the following operations are performed:

[0090] Step S1: In response to a query request for data within the data index, a preset algorithm is used to determine the location range of the ORC file containing the data within the data index.

[0091] Step S2: Based on the location range and the pre-configured buffer, determine the specific location of the data, wherein the buffer is used to store the newly added ORC file.

[0092] In the fourth embodiment of the present invention, the process of indexing ORC files in a relational database is the same as that in the first, second, or third embodiments. The difference lies in the engineering implementation: this embodiment can be implemented using software plus necessary general-purpose hardware platforms. While hardware can also be used, the former is often a better implementation method. Based on this understanding, the method of the present invention can be embodied in the form of a computer software product stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), including several instructions to cause a device to execute the method of the embodiments of the present invention.

[0093] Through the description of specific embodiments, a more in-depth and specific understanding should be gained of the technical means and effects adopted by the present invention to achieve the intended purpose. However, the accompanying drawings are only provided for reference and illustration and are not intended to limit the present invention.

Claims

1. A method for indexing ORC files in a relational database, characterized in that, include: In response to a query request for data within a data index, a corresponding position and a first fluctuation range of the data in a second calculation layer of the preset algorithm are determined using a first calculation layer of the preset algorithm. Based on the location of the data in this computing layer and the first fluctuation range, the second computing layer determines the location of the ORC file corresponding to the data and the second fluctuation range. The location of the ORC file and the second fluctuation range are taken as the location range of the ORC file containing the data in the data index. Traverse the data index within the specified location range to determine the specific location of the data within the data index; Traverse the ORC files in the pre-configured buffer to determine the specific location of the data in the buffer, wherein the buffer is used to store newly added ORC files; The positions in the data index and the positions in the buffer are merged and output.

2. The indexing method for ORC files in a relational database according to claim 1, characterized in that, The method further includes: The preset algorithm is configured based on the acquired ORC file and the newly added and stored ORC file in the buffer.

3. The indexing method for ORC files in a relational database according to claim 1, characterized in that, The configuration of the preset algorithm based on the acquired ORC file and the newly added and stored ORC file in the buffer includes: The preset algorithm is configured using a pre-configured ORC file; Newly added ORC files are acquired and stored in the buffer. When the number of ORC files in the buffer exceeds a pre-configured threshold, all ORC files in the current buffer are used for further configuration of the preset algorithm, and the buffer is cleared.

4. An indexing device for ORC files in a relational database, characterized in that, include: A first indexing module is configured to, in response to a query request for data within a data index, use a first calculation layer of a preset algorithm to determine a corresponding position and a first fluctuation range of the data in a second calculation layer of the preset algorithm; based on the second calculation layer's position of the data in this calculation layer and the first fluctuation range, determine the position and a second fluctuation range of the ORC file corresponding to the data; and use the ORC file position and the second fluctuation range as the position range of the ORC file containing the data in the data index. The second indexing module is configured to traverse the position range of the data index to determine the specific position of the data in the data index; traverse the ORC files in a pre-configured buffer to determine the specific position of the data in the buffer, wherein the buffer is used to store newly added ORC files; and merge the position in the data index with the position in the buffer and output the result.

5. The indexing device for ORC files in a relational database according to claim 4, characterized in that, The device further includes: The neural network module is configured to configure the preset algorithm based on the acquired ORC file and the newly added and stored ORC file in the buffer.

6. An electronic device, characterized in that, The electronic device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program, when executed by the processor, implements the steps of the indexing method for ORC files in a relational database as described in any one of claims 1 to 3.

7. A computer storage medium storing a computer program, wherein the computer program, when executed by a processor, implements the steps of an indexing method for ORC files in a relational database as described in any one of claims 1 to 3.

Images