Optimized column storage method and system

Abstract

This invention provides an optimized column-based storage method and system, comprising the following steps: setting optimization hints for fields as needed; processing the table to be stored, writing information, and generating a set of columns to be stored; iteratively processing each column to generate optimized columns and order columns; compressing and storing the optimized columns; compressing and storing the order columns; and, upon completion of the iteration, writing Extra3 information. This invention, based on the column-based storage of individual fields in a page, sorts the data within the page during storage, improving the local order of the data and the compression ratio of column-based storage.

Description

Technical Field

[0001] The present invention relates to the field of data storage technology, and in particular to an optimized column storage method and system, and more particularly to a storage method that can be used individually for each column optimization. Background Art

[0002] Columnar databases and columnar storage are currently popular computer technologies. Common columnar databases include SAP HANA, Amazon Redshift, Sybase IQ, ParAccel, Sand / DNA Analytics, Vertica, Aster Data Systems, Greenplum, and ClickHosue. In addition to the aforementioned databases, other common columnar storage options include Apache ORC and Apache Parquet.

[0003] In a relational database, a set of data is called a table. A table consists of numerous rows, and a row consists of numerous fields. The field's name, data type, and attributes form the field schema, which describes how the field is handled. All the data for a single field in a table constitutes a column. Column-based storage organizes data by column order, while traditional row-based storage stores data sequentially by row.

[0004] Compared to row-based storage, column-based storage's advantages are primarily reflected in OLAP (Online Analytical Query) queries. These queries generally don't require complete row data, but rather filtering, calculations, and aggregations of data within a few columns. Therefore, with column-based storage, queries don't require scanning all data, only reading the columns involved in each query. This can reduce I / O consumption by a factor of N. Furthermore, because each column's members are homogeneous, more efficient data compression algorithms can be used for different data types, further reducing I / O.

[0005] A simplified column storage structure is as follows Figure 1As shown in the figure, the structure is applicable to both file-based columnar storage and directory-based columnar storage. For file-based columnar storage, the structure directly corresponds to the structure in the file. For directory-based columnar storage, the structure corresponds to files with the same function, and these files are organized using directories. The structure is described below:

[0006] Field Schemas, which are required information for all columnar storage systems, consist of several FieldSchemas, which correspond one-to-one to the fields in the table. Each Field Schema contains the following information:

[0007] Name, field name;

[0008] Data Type, field data type, including basic types and complex types. Common basic data types include: Int8, Int16, Int32, Int64, Int128, UInt8, UInt16, UInt32, UInt64, UInt128, Float32, Flow64, Char, String, Bytes, DateTime, etc. Complex types include nested types such as Array, Tuple, Map, Struct, etc.

[0009] Attr1, Attr2, ..., a set of field attributes, such as various hints used for optimization processing;

[0010] Row Group: This is required information in all columnar storage formats. For a data table, the total number of rows may be very large, so it is always necessary to split it into several partitions for storage. For row storage, a partition is a series of rows. For column storage format, partitions are also divided by rows, but because they are internally a group of columns, they are called row groups.

[0011] Column1, Column2, Column3, ..., a set of columns, corresponding one-to-one to the fields of the table. The data type is declared by the Data Type of the corresponding field schema in Field Schemas.

[0012] Page1, Page2, Page3, ..., a group of pages. Page is equivalent to sharding column data. Page is the smallest storage unit of column storage. Compression is based on pages, also called data blocks. For column storage without pages, a column can be treated as a single page.

[0013] Extra1: additional information in the storage header, such as the Magic Number, version number, statistics, and metadata. This is optional information for different column-based storages.

[0014] Extra2, additional information before the data, such as indexes. This is optional information for different column storage formats;

[0015] Extra3: additional information after the data, such as footer. This is optional information for different column storages;

[0016] Because columns are all of the same data type (for example, all Int64), compared to different types of data in different fields of a row (for example, String, Int64, Float64, etc.), the data is highly similar and ordered. Therefore, column data can have a higher compression ratio (the original data size divided by the compressed data size) than row data.

[0017] Existing columnar technologies only improve the compression ratio compared to row-based storage, but there is no mechanism to further improve the compression ratio by sorting any column without changing the data order.

[0018] Patent publication number CN107193925A discloses a real-time data compression algorithm based on columnar storage technology. This algorithm, for a sequence within a specific column package, performs trial calculations and evaluations of variable step-size formulas, as well as searches and parameter calculations for cover unit calculation formulas, compressing the real-time data column packages within the compressible partitions. However, this patent application's method changes the page partitioning through evaluation, evaluating only page characteristics. In contrast, this application uses evaluation to determine whether to sort when FCOH = DetectEnable, and evaluates the comparison between the benefit of sorting the page and the gain or loss of adding an Order Page.

[0019] Patent document CN111723089A discloses a method and apparatus for processing data based on a columnar storage format, relating to the field of computer technology. A specific implementation of the method includes: receiving batch target data; determining the similarity between the batch target data based on the field information of the target data; and sorting the batch target data based on the determined similarity; and logically processing the batch target data according to the columnar storage format based on the sorting result. However, the method in this patent document manually selects several fields, evaluates the similarity of these fields together, and then sorts the entire data. If the field selection needs to be adjusted, such as adding, deleting, or changing one or more fields, then due to global sorting, the method needs to modify all fields (including fields other than the above fields that are not involved in the evaluation). This method changes the original data order and actually changes the usage scenario.

[0020] Patent document with publication number CN114047876A discloses a data sorting method, device and storage medium based on column storage, wherein the method includes: after the data stream to be sorted is divided into blocks according to a preset block algorithm to obtain multiple data blocks, for any data block, the data in the data block is sorted according to the priority of the sorting key, and the sorting key with the same key value is converted into a run-length code, and the data in all data blocks are merged and sorted according to the priority of the sorting key and the run-length code. However, the method of the patent document mainly provides a sorting method based on RLE, which sorts within the data block (corresponding to the Page of the present invention) and then uses merge sort between the data blocks, which obviously changes the order of the data. The present application only sorts within the data block, and the purpose of sorting is only to improve the compression storage ratio. There is no operation between data blocks, and the data order will be restored when reading the data.

[0021] Patent document CN114880322A discloses a columnar storage method, system, device, and storage medium for financial data. The method includes: inputting the financial data to be stored into a columnar storage engine, forming the financial data into several column structures, wherein the column structures include source data decomposed from the financial data and metadata identifying the storage location and type of the source data; copying the metadata from the several column structures and storing it in memory to form metadata copies; storing the several column structures in a columnar format on a hard disk, with column structures of the same source data type stored adjacently; and setting index columns for the several column structures in the database, using the index columns as alignment columns. However, this patent document still suffers from the drawback of changing the order of the data. Summary of the Invention

[0022] In view of the deficiencies in the prior art, the present invention aims to provide an optimized column storage method and system.

[0023] An optimized column storage method provided by the present invention includes the following steps:

[0024] Step 1: Set the fields to optimize hints as needed;

[0025] Step 2: Process the table to be stored, write information, and generate a set of columns to be stored;

[0026] Step 3: Iterate each column to generate the optimized column and order column;

[0027] Step 4: Compress and store the Optimized Column;

[0028] Step 5: Compress and store the Order Column;

[0029] Step 6: The iteration ends and the Extra3 information is written.

[0030] Preferably, in step 1, Field Attribute is used to define whether to use optimization Hint and FCOH on each Field;

[0031] The optimization hint has three options:

[0032] a. Disable, disable this optimization;

[0033] b. ForceEnable: Force the optimization to be enabled regardless of the data situation.

[0034] c. DetectEnable: Detect and enable this optimization. Evaluate the data. If the compression rate is higher after enabling this optimization, enable this optimization.

[0035] Preferably, in step 2, the data to be written to the storage includes Field Schemas, overall Columns, metadata, and pre-calculated data;

[0036] After storing related data in columnar format, the Table is sharded into a set of Row Groups and Columns in the Row Groups.

[0037] Preferably, in step 3, for each output Column, generate an OptimizedColumn and a corresponding Order Column according to the optimized FCOH;

[0038] The Order Column is used to restore the original order of the Optimized Column when reading data.

[0039] Preferably, in step 4, a compression algorithm is used to compress the Page in the Optimized Column, and the Pages are written to the disk one by one in a columnar storage format;

[0040] Compression algorithms include run-length encoding, Snappy, zlib, LZO, and zstandard;

[0041] In step 5, the Order Column is compressed using a compression algorithm and written to disk.

[0042] Preferably, in step 6, all Columns generated in step 2 are iterated until completion, and then corresponding Extra3 information is written in a columnar storage format.

[0043] Preferably, the step 2 specifically includes the following steps:

[0044] Step 2.1: Enter the table to be stored;

[0045] Step 2.2: Write the corresponding Extra1 information in column storage format;

[0046] Step 2.3: Update Field Schemas based on Field Hint and store;

[0047] Step 2.4: Write the corresponding Extra2 information in column storage format;

[0048] Step 2.5: Shard the table data and generate a set of row groups;

[0049] Step 2.6: In Row Group, iterate over each Column by Field.

[0050] Preferably, the step 3 specifically includes the following steps:

[0051] Step 3.1: Enter the Column and Field Schema;

[0052] Step 3.2: Split the columns and generate a set of Pages;

[0053] Step 3.3: Iterate each page to generate the optimized page and order page;

[0054] Step 3.4: Cache the Optimized Page and Order Page;

[0055] Step 3.5: The iteration ends and the Optimized Column and Order Column are output.

[0056] Preferably, the step 3.3 specifically includes the following steps:

[0057] Step 3.3.1: Enter Page and FCOH;

[0058] Step 3.3.2: Process according to FCOH;

[0059] Step 3.3.3: If the Optimized Page is the original Page, then the Order Page is not generated; if the Optimized Page is the sorted Page, then the Order Page is generated;

[0060] Step 3.3.4: Evaluate whether the page is optimized;

[0061] Step 3.3.5: If the Optimized Page is the original Page, set the Order Page to All Null;

[0062] Step 3.3.6: Output the Optimized Page and Order Page.

[0063] The present invention also provides an optimized column storage system, comprising the following modules:

[0064] Module M1: Set Fields to optimize Hint as needed;

[0065] Module M2: processes the table to be stored, writes information, and generates a set of columns to be stored;

[0066] Module M3: Iterates each column to generate optimized columns and order columns;

[0067] Module M4: compress and store Optimized Column;

[0068] Module M5: compress and store the Order Column;

[0069] Module M6: Iteration ends and Extra3 information is written.

[0070] Compared with the prior art, the present invention has the following beneficial effects:

[0071] 1. This invention is based on the Page of Fields stored in columnar format. The data in the Page is sorted during storage to improve the local order of the data, thereby further improving the compression ratio of columnar storage. At the same time, an Order Field is added to record the original order of each field, so that the data order can be restored during reading.

[0072] 2. When FCOH=DetectEnable is set, the present invention evaluates whether to enable the optimization method by comparing the bits increased in the Order Page and the bits reduced in the Optimized Page, which greatly reduces the manual evaluation when configuring the Field and improves practicality;

[0073] 3. This invention uses the FCOH of the Field Attribute on the original Field to control whether each Field adopts this method. It can be enabled and disabled independently. When multiple Fields are enabled for optimization, they do not interfere with each other. If there is a change, only the Field itself is affected.

[0074] 4. The Order Field mechanism and Field Attribute mechanism of the present invention are fully compatible with the current column storage format design and have good versatility. BRIEF DESCRIPTION OF THE DRAWINGS

[0075] Other features, objects and advantages of the present invention will become more apparent upon reading the detailed description of non-limiting embodiments with reference to the following drawings:

[0076] Figure 1 This is a simplified diagram of the column storage structure;

[0077] Figure 2 A flowchart of the execution steps of the optimized column storage method of the present invention;

[0078] Figure 3 A flowchart illustrating the steps of generating a set of columns for the table to be stored in step 2 of the present invention;

[0079] Figure 4 This is a flowchart of the steps of iteratively processing each Column in step 3 of the present invention;

[0080] Figure 5 This is a flowchart of the iterative processing of each Page in step 3.3 of the present invention. DETAILED DESCRIPTION

[0081] The present invention will be described in detail below with reference to specific embodiments. The following examples will help those skilled in the art to further understand the present invention, but are not intended to limit the present invention in any form. It should be noted that, for those skilled in the art, several changes and improvements can be made without departing from the scope of the present invention. These all fall within the scope of protection of the present invention.

[0082] Example 1:

[0083] like Figure 1 As shown, this embodiment provides an optimized column storage method, including the following steps:

[0084] Step 1: Set optimization hints for fields as needed. Use Field Attributes to define whether to use optimization hints and FCOH for each field. If a field has this optimization enabled, write this information to the FieldSchema in step 2 and also add the corresponding Order Field (Name and Data Type) for the field to the FieldSchemas.

[0085] There are three options for optimizing Hint:

[0086] a. Disable, disable this optimization;

[0087] b. ForceEnable: Force the optimization to be enabled regardless of the data situation.

[0088] c. DetectEnable: Detect and enable this optimization. Evaluate the data. If the compression rate is higher after enabling this optimization, enable this optimization.

[0089] Step 2: Process the table to be stored, write information, and generate a set of columns to be stored. The data to be written and stored includes field schemas, overall columns, metadata, and pre-calculated data.

[0090] After storing related data in columnar format, the table is sharded into a set of row groups and columns in the row groups;

[0091] Step 2 specifically includes the following steps:

[0092] Step 2.1: Enter the table to be stored. The data to be written and stored consists of field schemas and columns. The length of many columns is consistent, which is actually equal to the total number of rows in the table.

[0093] Step 2.2: Write the corresponding Extra1 information in column storage format;

[0094] Step 2.3: Update and store the Field Schemas based on the Field Hint. For each field, in addition to its own Field Schema, perform the following processing based on the FCOH in its Field Attribute: ForceEnable or DetectEnable, and add the Order Field corresponding to the field.

[0095] Step 2.4: Write the corresponding Extra2 information in column storage format;

[0096] Step 2.5: Shard the table data and generate a set of row groups;

[0097] Step 2.6: In Row Group, iterate over each Column by Field.

[0098] Step 3: Iterate each column to generate the optimized column and order column. For each output column, generate the optimized column and the corresponding order column based on the optimized FCOH.

[0099] The Order Column is used to restore the original order of the Optimized Column when reading data;

[0100] Step 3 specifically includes the following steps:

[0101] Step 3.1: Enter the Column and Field Schema;

[0102] Step 3.2: Split the columns and generate a set of Pages;

[0103] Step 3.3: Iterate each page to generate an optimized page and an order page. For each page output in step 3.2, generate an optimized page and a corresponding order page based on the FCOH. The order page is used to restore the original order of the optimized page.

[0104] Step 3.4: Cache the Optimized Page and Order Page; cache this data before the iteration ends;

[0105] Step 3.5: The iteration ends, and the Optimized Column and Order Column are output. The Optimized Column consists of the cached Optimized Page and directly corresponds to the Field. The Field Schema is used. The Order Column consists of the cached Order Page and generates the Order Field.

[0106] Step 3.3 specifically includes the following steps:

[0107] Step 3.3.1: Enter Page and FCOH;

[0108] Step 3.3.2: Process according to FCOH;

[0109] Step 3.3.3: If the Optimized Page is the original Page, the Order Page is not generated. If the Optimized Page is a sorted Page, the Order Page is generated. When FCOH = ForceEnable, or when FCOH = DetectEnable and the evaluation requires optimization, the Page is sorted and an Order Page is generated to record the original order. The sorting algorithms used include: Bubble Sort, Selection Sort, Insertion Sort, Merge Sort, Quick Sort, Shell Sort, and Heap Sort.

[0110] Step 3.3.4: Evaluate whether the page is optimized;

[0111] Step 3.3.5: If the Optimized Page is the original Page, set the Order Page to All Null;

[0112] Step 3.3.6: Output the Optimized Page and Order Page.

[0113] Step 4: Compress and store the optimized column. Use a compression algorithm to compress the pages in the optimized column and write each page to disk in columnar storage format. Compression algorithms include run-length encoding, Snappy, zlib, LZO, and zstandard.

[0114] Step 5: Compress and store the Order Column; compress the Order Column using a compression algorithm and write it to disk.

[0115] Step 6: After the iteration is complete, write the Extra3 information. Iterate all the columns generated in Step 2 until the end, and then write the corresponding Extra3 information in columnar storage format.

[0116] In this embodiment, for the Field stored in columnar format, an optimization Hint is added to the Field Attribute to configure whether to enable optimization. When the optimization is enabled, the smallest data unit Page based on the Field is sorted to improve the local order of the data, thereby further improving the compression ratio of the column storage. At the same time, the Order Field is added to record each original order so that the data order can be restored when reading. The method of automatically evaluating whether to enable optimization for the Page by comparing the bits added to the Page of the Order Field and the bits reduced after the Field optimization is used is the DetectEnable method of Hint. Using the DetectEnable method can greatly reduce the manual evaluation of the configuration Field and improve practicality. The above mechanism realizes an optimization method that is fully compatible with the current mainstream column storage format design and can improve the compression ratio of a single Field without interference.

[0117] This embodiment also provides an optimized column storage system, which can be implemented by executing the process steps of the optimized column storage method. That is, those skilled in the art can understand the optimized column storage method as a preferred implementation of the optimized column storage system.

[0118] This embodiment provides an optimized column storage method, which is a method based on existing column storage to further improve the compression rate, and is an optimization method.

[0119] Attribute(s): Attribute. Bit: Bit. Column(s): Column. Columnary DB: Columnar Database. Columnary Storage: Columnar Storage. DetectEnable: Enable detection. Disable: Disable. Data Type: Data type. Extra1, Extra2, Extra3: Extra information 1, Extra information 2, Extra information 3. FCOH, Fieldcompression optimization hint: Field compression optimization hint. Field(s): Field. Field Attribute(s): Field attributes. Field Schema(s): Field schema. ForceEnable: Force enable. Hint: Hint. Metadata: Metadata. Name: Field name. Order Field: (Original) order field. Order Column: (Original) order column. Order Page: (Original) order page. Optimized Column: Optimized column. Optimized Page: Optimized page. Page(s): Page. Relation DB: Relational Database. Row: Row. Row Group: Row group. Table: Table.

[0120] Example 2:

[0121] This embodiment provides an optimized column storage system, including the following modules:

[0122] Module M1: Set Fields to optimize Hint as needed;

[0123] Module M2: processes the table to be stored, writes information, and generates a set of columns to be stored;

[0124] Module M3: Iterates each column to generate optimized columns and order columns;

[0125] Module M4: compress and store Optimized Column;

[0126] Module M5: compress and store the Order Column;

[0127] Module M6: Iteration ends and Extra3 information is written.

[0128] Example 3:

[0129] Those skilled in the art may understand this embodiment as a more specific description of Embodiment 1 and Embodiment 2.

[0130] This embodiment proposes a storage method that can be used separately in each column optimization to further improve the compression rate of column storage. The steps of the storage method of this embodiment are as follows: Figure 2 As shown, the description is as follows:

[0131] Step 1: Set optimization hints for fields as needed. Use Field Attribute to define whether to use the hint for this embodiment's optimization method, FCOH (Field compression optimization hint), for each field. This hint has three options:

[0132] Disable, disable this optimization;

[0133] ForceEnable, forcibly enable this optimization, regardless of the data situation;

[0134] DetectEnable: Detects and enables this optimization. Evaluates the data and determines that the compression rate is higher after enabling this optimization. Enables this optimization.

[0135] Step 2: Process the table to be stored, write information, and generate a set of columns to be stored. The data to be written and stored generally consists of field schemas and overall columns, and may also include some metadata and pre-calculated data. After storing the relevant data in a columnar format, this step shards the table into a set of row groups and columns within the row groups.

[0136] Step 3: Iterate each column to generate the optimized column and order column. For each column output in step 3, this step generates the optimized column and the corresponding order column based on the FCOH. The order column is used to restore the original order of the optimized column when reading data.

[0137] Step 4: Compress and store the optimized column. In this step, a common compression algorithm can be used to compress the pages in the optimized column in step 3. The pages are then written to disk one by one in columnar storage format. If there are indexes in the columnar storage, they are also processed in this step. Common compression algorithms include run-length encoding (Run-Length Encoding), Snappy, zlib, LZO, and zstandard. Because the optimization in step 4 improves the orderliness of the data in the page, these compression algorithms can achieve higher compression ratios.

[0138] Step 5: Compress and store the Order Column. This step uses the same compression algorithm as step 4 to compress the Order Column and write it to disk. For fields with FCOH set to Disable, no Order Column is actually generated, so this step is not required.

[0139] Step 6: After the iteration is complete, write the Extra3 information. Iterate all the columns generated in Step 2 until the end. Then, write the corresponding Extra3 information in columnar storage format.

[0140] Step 2 "Process the table to be stored, write information, and generate a set of columns to be stored" includes the following sub-steps: Figure 3 The specific instructions are as follows:

[0141] Step 2.1: Enter the table to be stored. The data to be written and stored generally consists of Field Schemas and Columns. The length of the columns must be consistent and actually equal to the total number of rows in the table.

[0142] Step 2.2: Write Extra1 and write the corresponding Extra1 information in columnar storage format.

[0143] Step 2.3: Update and store the Field Schemas based on the Field Hint. For each Field, in addition to its own Field Schema, the following processing is required based on the FCOH in its Field Attribute:

[0144] ForceEnable or DetectEnable, add the Order Field corresponding to the Field. The Name of the Order Field can be in the form of Name_$Order$, where Name is the name of the Field and _$Order$ is a unified suffix to avoid conflicts with general fields. The Data Type of the Order Field is one of UInt8, UInt16, and UInt32. The selection rule is: the smallest type among the above data types that is greater than the number of data in the Page. For example, if the number of data in the Page is less than 256, use UInt8; if it is less than 65536, use UInt16; if it is larger, use UInt32.

[0145] Disable, no additional fields are required.

[0146] Step 2.4: Write to Extra2 and write the corresponding Extra2 information in columnar storage format.

[0147] Step 2.5: Shard the Table data to generate a set of Row Groups. The Table data may be large, for example, 100_000. If the Row Group is 10_000, then all columns need to be sharded by 10_000. In this embodiment, it is divided into 10 slices, and then the slice parts corresponding to all columns are taken to form corresponding Row Groups, a total of 10 Row Groups.

[0148] Step 2.6: In the Row Group, iterate each Column by Field. Each Row Group has corresponding fragments of all Columns, so iterate the fragment data of each Column.

[0149] Step 3 "Iterate each column to generate optimized columns and order columns" includes the following sub-steps: Figure 4 The specific instructions are as follows:

[0150] Step 3.1: Enter the Column and Field Schema.

[0151] Step 3.2: Split the column into a set of pages. The column data is further split into a set of pages. For example, every 1,000 data is a fragmented page. In this case, a column fragment of 10,000 data can be divided into 10 page fragments of 1,000 data.

[0152] Step 3.3: Iterate each Page to generate the Optimized Page and Order Page. For each Page output in Step 3.2, this step generates the Optimized Page and the corresponding Order Page based on the FCOH. The Order Page is used to restore the original order of the Optimized Page.

[0153] Step 3.4: Cache the Optimized Page and Order Page. Cache this data until the iteration is complete.

[0154] Step 3.5: The iteration ends, and the Optimized Column and Order Column are output. The Optimized Column consists of the cached Optimized Page and directly corresponds to the Field. The Field Schema is used. The Order Column consists of the cached Order Page and generates the Order Field. Its Field Schema is described in Step 2.3. For a Page with a data volume of 1_000, the Data Type of the Order Field is UInt16.

[0155] Step 3.3 "Iterate each Page to generate Optimized Page and Order Page" contains the following sub-steps: Figure 5 The specific instructions are as follows:

[0156] Step 3.3.1: Enter Page and FCOH.

[0157] Step 3.3.2: Process according to FCOH.

[0158] Step 3.3.3: The Optimized Page is the original Page, and the Order Page is not generated. When FCOH = Disable, this field is not optimized, so the original Page is used directly, and there is no need to generate an Order Page.

[0159] Step 3.3.4: The Optimized Page is the sorted Page, and the Order Page is generated. When FCOH = ForceEnable, or when FCOH = DetectEnable and the evaluation requires optimization, the Page is sorted and an Order Page is generated to record the original order. Common sorting algorithms can be used for sorting, such as bubble sort, selection sort, insertion sort, merge sort, quick sort, shell sort, heap sort, etc.

[0160] Step 3.3.5: Evaluate whether to optimize within the Page. When FCOH = DetectEnable, it is necessary to evaluate whether to enable this optimization. Unlike some common compression algorithms that compare the actual compressed data size with the original data size to determine whether to use compression, this method can subsequently superimpose various compression algorithms. In order to avoid the actual calculation overhead, this embodiment uses an estimation method for evaluation.

[0161] Step 3.3.6: The Optimized Page is the original Page, and the Order Page is all Null. When FCOH = DetectEnable and the evaluation does not require optimization, the original Page is used directly. However, an Order Page needs to be generated at this time. To indicate that the Order Page is not actually enabled, all values ​​are set to Null.

[0162] Step 3.3.7: Output the Optimized Page and Order Page.

[0163] The method to further improve the compression ratio is to further improve the local order of the data, that is, to improve the order of the data within the page. This embodiment adopts the method of sorting within the page, but at the same time, it is necessary to add an Order Page to restore the field order within the page when reading the data. Therefore, step 3.3.5 "Evaluate whether it is optimized within the page" is to evaluate whether the increased Order Page storage is worth the reduced Page storage. The following two analyses are performed:

[0164] The order page is a UInt from 0 to the data number within the page - 1. After the compression algorithm is used, the prefix 0 can be greatly compressed. Therefore, the increased storage is actually determined by the page number, and the increased number of bits is a certain amount. For example, for a page with data 1_000, a maximum of 10 bits is required, and the average is 1 / 2 of the maximum, 9 bits.

[0165] The number of bits is: Ceil(lg(Number))-1.

[0166] The reduced page storage comes from sorting the originally randomly distributed data, which greatly reduces the randomness. Different types of data are measured differently, as shown below:

[0167] For Int and UInt of various lengths, the numerical range can be measured in the following way, and then converted to Bit number using Ceil(lg(N)):

[0168] Metric 1: N = (maximum value - minimum value) * K. K is a value between 0.5 and 1.0; the default is 0.5, which is the median value.

[0169] Metric 2: N = mean * K. K is a value between 1 and 2; the default is 0, which means the median is used.

[0170] Metric 3: N = mean + K * variance. K is a value between 0 and 3; the default is 0, which takes the median value.

[0171] For floats of various lengths, the following bit numbers can be used:

[0172] N = number of bits of data type length / 2;

[0173] For strings, the following bit numbers can be used:

[0174] N = average string length in bits / 2 * K. K is a value between 1 and 2; the default value is 1.

[0175] For nested structures, calculate the bits of each basic type in turn and then summarize them.

[0176] Step 3.3.5, "Evaluate Optimization Within a Page," compares the bit benefit within the page: the number of bits reduced by the page minus the number of bits added by the order page. If the benefit is greater than 0, the optimization is enabled; if the benefit is less than or equal to 0, the optimization is disabled. K, in the "number of bits reduced by the page," is one of the few global configuration values ​​in this invention. You can optimize the results based on your usage scenarios.

[0177] For example, for Field, FCOH=DetectEnable, DataType=UInt32, a Column has data:

[0178] Page: [10_000_000,10,9_999,1,10_000_000,10,1,10_000],

[0179] The increase in the Order Page bit is calculated as Ceil(lg(8))–1=2.

[0180] The metric for calculating the number of bits reduced by Page (K=0.5) is Ceil(log(9999999))=24.

[0181] Evaluation: Optimization,

[0182] The optimized Optimized Page and Order Page are as follows:

[0183] Optimized Page: [1,1,10,10,9_999,10_000,10_000_000,10_000_000],

[0184] Order Page: [3,6,1,5,2,7,0,4],

[0185] When reading data, the Page is restored based on the Optimized Page and Order Page.

[0186] In this embodiment, the actual data Page has a large value range (using Int64, UInt64, Float32, Float64, etc., the actual data value range is even larger) and is randomly distributed. After sorting the Page, the order is greatly improved, which is reflected in two aspects:

[0187] a. Repeated data is connected and can be compressed significantly using RLE or other methods. For example, just two consecutive 10_000_000 repeated codes can save 24 bits;

[0188] b. The difference between adjacent data is reduced, which can also significantly reduce the difference encoding required by algorithms such as RLE. For example, after sorting, the difference between 9_999 and 10_000 is 1 bit, while the original difference between 9_999 and 1 is -9_998, saving 31 bits. At the same time, after sorting, only the adjacent differences are reduced, and no adjacent differences are increased. The effective bits added to the Order Page are: 2 effective bits for each data, and a total of 16 effective bits for 8 data.

[0189] The present invention is based on a page with multiple fields stored in a columnar manner. The data in the page is sorted during storage to improve the local order of the data, thereby further improving the compression ratio of columnar storage. At the same time, an Order Field is added to record the original order of each field, so that the data order can be restored during reading.

[0190] Those skilled in the art will appreciate that, in addition to implementing the system and its various devices, modules, and units provided by the present invention in purely computer-readable program code, it is entirely possible to implement the same functions of the system and its various devices, modules, and units provided by the present invention in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. Therefore, the system and its various devices, modules, and units provided by the present invention can be considered a hardware component, and the devices, modules, and units included therein for implementing various functions can also be considered as structures within the hardware component; the devices, modules, and units for implementing various functions can also be considered as both software modules implementing the method and structures within the hardware component.

[0191] The above describes specific embodiments of the present invention. It should be understood that the present invention is not limited to the specific embodiments described above, and those skilled in the art may make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. The embodiments of this application and the features in the embodiments may be combined with each other in any manner unless there is a conflict.

Claims

1. An optimized column storage method, characterized in that: The steps include: Step 1: Set the fields to optimize hints as needed; Step 2: Process the table to be stored, write information, and generate a set of columns to be stored; Step 3: Iterate each column to generate the optimized column and order column; Step 4: Compress and store the Optimized Column; Step 5: Compress and store the Order Column; Step 6: The iteration ends and the Extra3 information is written.

2. The optimized column storage method according to claim 1, characterized in that: In step 1, use FieldAttribute to define whether to use optimization Hint and FCOH on each Field; The optimization hint has three options: a. Disable, disable this optimization; b. ForceEnable: Force the optimization to be enabled regardless of the data situation. c. DetectEnable: Detect and enable this optimization. Evaluate the data. If the compression rate is higher after enabling this optimization, enable this optimization.

3. The optimized column storage method according to claim 1, characterized in that: In step 2, the data to be written to the storage includes Field Schemas, overall Columns, meta data, and pre-calculated data; After storing related data in columnar format, the Table is sharded into a set of Row Groups and Columns in the Row Groups.

4. The optimized column storage method according to claim 1, characterized in that: In step 3, for each output column, generate the optimized column and the corresponding order column according to the FCOH; The Order Column is used to restore the original order of the Optimized Column when reading data.

5. The optimized column storage method according to claim 1, characterized in that: In step 4, a compression algorithm is used to compress the pages in the optimized column, and the pages are written to disk one by one in a columnar storage format. Compression algorithms include run-length encoding, Snappy, zlib, LZO, and zstandard; In step 5, the Order Column is compressed using a compression algorithm and written to disk.

6. The optimized column storage method according to claim 1, characterized in that: In step 6, all Columns generated in step 2 are iterated until completion, and then corresponding Extra3 information is written in columnar storage format.

7. The optimized column storage method according to claim 1, characterized in that: The step 2 specifically includes the following steps: Step 2.1: Enter the table to be stored; Step 2.2: Write the corresponding Extra1 information in column storage format; Step 2.3: Update Field Schemas based on Field Hint and store; Step 2.4: Write the corresponding Extra2 information in column storage format; Step 2.5: Shard the table data and generate a set of row groups; Step 2.6: In Row Group, iterate over each Column by Field.

8. The optimized column storage method according to claim 1, characterized in that: The step 3 specifically includes the following steps: Step 3.1: Enter the Column and Field Schema; Step 3.2: Split the columns and generate a set of Pages; Step 3.3: Iterate each page to generate the optimized page and order page; Step 3.4: Cache the Optimized Page and Order Page; Step 3.5: The iteration ends and the Optimized Column and Order Column are output.

9. The optimized column storage method according to claim 1, characterized in that: The step 3.3 specifically includes the following steps: Step 3.3.1: Enter Page and FCOH; Step 3.3.2: Process according to FCOH; Step 3.3.3: If the Optimized Page is the original Page, then the Order Page is not generated; if the Optimized Page is the sorted Page, then the Order Page is generated; Step 3.3.4: Evaluate whether the page is optimized; Step 3.3.5: If the Optimized Page is the original Page, set the Order Page to All Null; Step 3.3.6: Output the Optimized Page and Order Page.

10. An optimized column storage system, characterized in that: Includes the following modules: Module M1: Set Fields to optimize Hint as needed; Module M2: processes the table to be stored, writes information, and generates a set of columns to be stored; Module M3: Iterates each column to generate optimized columns and order columns; Module M4: compress and store Optimized Column; Module M5: compress and store the Order Column; Module M6: Iteration ends and Extra3 information is written.

Images