A data warehouse management system

Abstract

The present invention relates to the technical field of database management, and in particular to a data warehouse management system, comprising: a database configured as a plurality of distributed spaces, wherein the capacity of each distributed space is configured as a corresponding fixed value; an archive library configured as a plurality of archive spaces, and a pedometer, which is used to execute a reset instruction to the archive library according to the state of the running archive and the archive directory; a cache manager connected to the archive library and the pedometer, which is used to record the origin of the archive directory and overwrite the origin of the archive directory in the archive library in response to the reset instruction; the present invention utilizes the method of configuring an archive library for a database, configuring a pedometer and a cache manager outside the database, to record and cache the directory of the database, and while effectively improving the management performance of the database directory, utilizes the database directory to perform reverse operations on the database, and while improving the capacity efficiency of the database, effectively improves the controllability of the distributed database.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a data warehouse management system. Background Art

[0002] Hive is a data warehouse tool built on Hadoop. It provides data management and storage functions, including the creation and management of data tables, partition tables, and other functions. When Hive chooses the distributed file storage system HDFS as data storage, the table created by Hive will put the table name, field, underlying file address location and other information into the metadata information, and the real data will be stored in the form of files in the path corresponding to the location in HDFS.

[0003] Chinese patent application number: CN202410502416.7 discloses an enterprise operation and maintenance management service system and method based on a cloud service platform, including: obtaining system equipment composition information and equipment connection relationship, and grouping system equipment according to the system equipment composition information and connection relationship to obtain multiple sub-equipment groups, and the cloud data server generates management containers for the corresponding sub-equipment groups respectively; the maintenance management module obtains the maintenance mode in the maintenance task information, obtains the maintenance plan according to the maintenance mode, the maintenance center control module confirms the maintenance plan, generates a confirmed maintenance plan, and sends the confirmed maintenance plan to the cloud data server, the cloud data server confirms the management container of the corresponding sub-equipment group included in the maintenance plan, connects to the maintenance management container, and performs equipment maintenance. If there is no abnormality in the equipment, the equipment maintenance management is completed. The efficiency, flexibility and scalability of cloud services can be used to provide a new equipment management method.

[0004] However, the above method has the following problems: When users use Hive for development iteration, there will be inconsistencies between metadata and underlying storage. For example, when the external table is deleted, the underlying HDFS data will not be deleted. In addition, when the HDFS storage resources are tight, some tables may not be cleaned up because it is not sure whether anyone is using them. In addition, it is impossible to obtain information about which database tables use more storage resources and need to be compressed, which tables have too many small files and need to be merged, and which file directories have redundant data and need to be cleaned up. Therefore, it is impossible to effectively manage Hive's database tables and underlying data. Summary of the invention

[0005] To this end, the present invention provides a data warehouse management system to overcome the problem in the prior art that when a user updates and iterates a distributed database, the data in the database cannot be effectively reset, resulting in the database overflowing due to directory memory, leading to the ineffective management of the database tables and underlying data, thereby reducing the controllability of the database.

[0006] To achieve the above object, the present invention provides a data warehouse management system, comprising:

[0007] The database is set as several distributed spaces, and the capacity of each distributed space is set to a corresponding fixed value;

[0008] An archive library, which is configured as a plurality of archive spaces, including an operation archive for recording the operation status of the database, and an archive directory for recording the operation archive;

[0009] A pedometer connected to the archive library, for determining the used capacity and the number of operations of the corresponding distributed space according to the state of the running archive and the archive directory, and issuing a reset instruction to the archive library according to the used capacity and / or the number of operations;

[0010] A cache manager connected to the archive repository and the pedometer, for recording an archive directory origin and overwriting the archive directory origin in the archive repository in response to the reset instruction;

[0011] The fixed value is related to the hardware of the database, and corresponds to different capacities for different distributed spaces;

[0012] The archive directory has a corresponding overflow value according to the setting of the remaining capacity of the distributed space;

[0013] The pedometer accumulates the usage counts, and when the accumulated counts reach the overflow value, the pedometer triggers a reset instruction and adjusts the overflow value according to the remaining capacity of the distributed space;

[0014] The overflow value is positively correlated with the fixed value.

[0015] Furthermore, for a single run archive, include:

[0016] Archive metadata, which are the operational properties of the corresponding distributed space;

[0017] Archive cache, which is a number of running state slices of the corresponding distributed space;

[0018] An archive fragment, which is a reset state snapshot of the distributed space;

[0019] The operation attributes include the file size, file path, user, group, modification time, and access time in the distributed space.

[0020] Furthermore, for a single distributed space, when a single opening, closing and / or modification operation is performed on it, each operation state slice of the corresponding operation is formed;

[0021] For the archive cache, each operation status slice is recorded in the time sequence of the operation, and a queue is formed in the time sequence, and the sequence number of each cache is stacked according to the queue, and a corresponding overflow number is set according to the queue;

[0022] When the running status slices reach the overflow quantity, the archive cache triggers a clear instruction and clears several running status slices located at the front of the queue and / or corresponding to several sequence numbers at the bottom of the stack.

[0023] Furthermore, the archive fragment responds to the clear instruction and records the operation status slice with the smallest sequence number or located at the bottom of the stack cleared from the archive cache.

[0024] Furthermore, for a single distributed space, it corresponds to a single feature code, which is used to mark the content of the distributed space;

[0025] The archive library marks the archive directory of the corresponding database according to each feature code.

[0026] Furthermore, the archive directory stores the feature codes corresponding to the running archives, sorts the running archives in order according to the activation time of the running archives, and counts the number of times they have been used.

[0027] Further, the cache manager is provided with a fixed cache area for storing the archive directory origin and a temporary cache area for storing temporary directory archives;

[0028] When responding to the reset instruction, the cache manager reads the archive directory origin in the fixed cache area and records the triggering time of the reset instruction as the zeroing time.

[0029] Further, the temporary cache area responds to the clear instruction and records the corresponding archive directory as the temporary directory origin.

[0030] Furthermore, the temporary cache area is also provided with a zeroing time threshold. When the zeroing time reaches the zeroing time threshold, the temporary cache area updates the archive directory origin and overwrites the temporary directory origin as the archive directory origin in the fixed cache area.

[0031] Furthermore, in response to the overwrite, the archive library merges several files whose total file sizes are smaller than the fixed value into the same distributed space, generates a corresponding directory in the next stack cycle of the sequence number stack, and stores the corresponding directory at the bottom of the stack.

[0032] Compared with the prior art, the beneficial effect of the present invention lies in that, by setting up an archive library for the database and setting up a pedometer and a cache manager outside the database, the directory of the database is recorded and cached, which effectively improves the management performance of the database directory. At the same time, the database directory is used to perform reverse operations on the database, which improves the capacity efficiency of the database and effectively improves the controllability of the distributed database.

[0033] Furthermore, by setting archive metadata, archive cache and archive fragments for database archives, the archives are updated in real time, and the corresponding stacks are set for counting, which effectively improves the reliability of archive reset and further improves the controllability of the distributed database.

[0034] Furthermore, by recording the characteristic codes of the distributed database, the directories of the database are counted, which effectively improves the overall reliability of the database. At the same time, the database is reset at a reasonable time to improve the cache redundancy of the database, thereby further improving the controllability of the distributed database.

[0035] Furthermore, by counting the operations on the database and organizing the database files when the preset count is reached, the capacity utilization of the database is effectively improved while avoiding the database crash caused by cache overflow, thereby further improving the controllability of the distributed database. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 It is a connection diagram of the data warehouse management system of the present invention;

[0037] Figure 2 The following is a flowchart of the operation of an embodiment of the present invention in the HDFS database. DETAILED DESCRIPTION

[0038] In order to make the objects and advantages of the present invention more clearly understood, the present invention is further described below in conjunction with embodiments; it should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0039] The preferred embodiments of the present invention are described below with reference to the accompanying drawings. It should be understood by those skilled in the art that these embodiments are only used to explain the technical principles of the present invention and are not intended to limit the protection scope of the present invention.

[0040] It should be noted that, in the description of the present invention, terms such as "up", "down", "left", "right", "inside" and "outside" indicating directions or positional relationships are based on the directions or positional relationships shown in the drawings. This is merely for the convenience of description and does not indicate or imply that the device or element must have a specific orientation, be constructed and operated in a specific orientation. Therefore, it cannot be understood as a limitation on the present invention.

[0041] In addition, it should be noted that in the description of the present invention, unless otherwise clearly specified and limited, the terms "installed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For those skilled in the art, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0042] See also Figure 1 As shown, it is a connection diagram of the data warehouse management system of the present invention, including:

[0043] The database is set as several distributed spaces, and the capacity of each distributed space is set to a corresponding fixed value;

[0044] An archive library, which is configured as a plurality of archive spaces, including an operation archive for recording the operation status of the database, and an archive directory for recording the operation archive;

[0045] A pedometer connected to the archive library is used to determine the used capacity and number of operations of the corresponding distributed space according to the state of the running archive and the archive directory, and to reset the archive library according to the used capacity and / or the number of operations;

[0046] A cache manager connected to the archive library and the pedometer, for recording the archive directory origin and responding to a reset instruction to overwrite the archive directory origin in the archive library;

[0047] The fixed value is related to the database hardware. Different distributed spaces have different capacities.

[0048] For a single distributed space, it corresponds to a single feature code, which is used to mark the content of the distributed space;

[0049] The archive library marks the archive directory of the corresponding database according to each feature code.

[0050] In particular, when setting the fixed value, it should be an integer multiple of 2k bytes or 4k bytes, and when operating on any distributed space, the new capacity occupied by a single operation is the minimum unit of the corresponding distributed space, that is, the distributed space divided into 2k bytes as the minimum unit, after a single operation, the new minimum capacity occupied is 2k bytes; the distributed space divided into 4k bytes as the minimum unit, after a single operation, the new minimum capacity occupied is 4k bytes.

[0051] Compared with the prior art, the beneficial effect of the present invention lies in that, by setting up an archive library for the database and setting up a pedometer and a cache manager outside the database, the directory of the database is recorded and cached, which effectively improves the management performance of the database directory. At the same time, the database directory is used to perform reverse operations on the database, which improves the capacity efficiency of the database and effectively improves the controllability of the distributed database.

[0052] Specifically, for a single run archive, include:

[0053] Archive metadata, which are the operational properties of the corresponding distributed space;

[0054] Archive cache, which is a number of running state slices of the corresponding distributed space;

[0055] Archive fragments, which are reset state snapshots of distributed space;

[0056] The operation attributes include the file size, file path, user, group, modification time, and access time in the distributed space.

[0057] In particular, for a single distributed space, when a single opening, closing and / or modification operation is performed on it, each operation state slice of the corresponding operation is formed;

[0058] For the archive cache, each operation status slice is recorded in the time sequence of the operation, and a queue is formed in the time sequence, and the sequence number of each cache is stacked according to the queue, and the corresponding overflow number is set according to the queue;

[0059] When the running status slices reach the overflow quantity, the archive cache triggers a clear instruction and clears several running status slices located at the front of the queue and / or corresponding to several sequence numbers at the bottom of the stack.

[0060] In particular, the archive fragment responds to the purge command and records the run state slice with the smallest sequence number or the bottom of the stack that is purged from the archive cache.

[0061] Furthermore, by setting archive metadata, archive cache and archive fragments for database archives, the archives are updated in real time, and the corresponding stacks are set for counting, which effectively improves the reliability of archive reset and further improves the controllability of the distributed database.

[0062] In particular, the archive directory stores the feature codes corresponding to the running archives, and sorts the running archives in order according to the activation time of the running archives, and counts the number of times they are used.

[0063] In particular, the archive directory has a corresponding overflow value according to the remaining capacity of the distributed space;

[0064] The pedometer accumulates the number of times used. When the accumulated number reaches an overflow value, the pedometer triggers a reset instruction and adjusts the overflow value according to the remaining capacity of the distributed space.

[0065] When setting the overflow value, taking the distributed space with a minimum storage unit of 2k and a remaining capacity of 2048k as an example, it can be understood that the maximum number of operations of the distributed space is 1024 times, and its overflow value can be set to 900 times;

[0066] That is, when the operation is performed 900 times, the overflow value of the above-mentioned distributed space is reached.

[0067] In implementation, taking the above distributed space as an example, after a certain operation, after storing a file of 600k capacity, the remaining capacity is 1448k capacity, and the maximum number of operations remaining in the distributed space is 724 times. At this time, the overflow value can be set to 650 times;

[0068] In the above embodiment, the overflow value is set to 90% of the maximum number of operations. Through such settings, the problem of the distributed space being occupied by non-file content due to several invalid operations can be effectively monitored, and when the overflow occurs, it is automatically reset, which can effectively prevent the distributed space from being changed.

[0069] In particular, the cache manager is provided with a fixed cache area for storing archive directory origins and a temporary cache area for storing temporary directory archives;

[0070] When responding to a reset instruction, the cache manager reads the archive directory origin in the fixed cache area and records the triggering time of the reset instruction as the zeroing time.

[0071] Furthermore, by recording the characteristic codes of the distributed database, the directories of the database are counted, which effectively improves the overall reliability of the database. At the same time, the database is reset at a reasonable time to improve the cache redundancy of the database, thereby further improving the controllability of the distributed database.

[0072] In particular, the temporary cache area responds to the clear instruction and records the corresponding archive directory as the temporary directory origin.

[0073] In particular, the temporary cache area is also provided with a zeroing time threshold. When the zeroing time reaches the zeroing time threshold, the temporary cache area updates the archive directory origin and overwrites the temporary directory origin as the archive directory origin in the fixed cache area.

[0074] In particular, the archive library responds to overwriting, merges several files whose total file size is less than a fixed value into the same distributed space, generates a corresponding directory in the next stack cycle of the sequence stack, and stores the corresponding directory at the bottom of the stack.

[0075] Furthermore, by counting the operations on the database and organizing the database files when the preset count is reached, the capacity utilization of the database is effectively improved while avoiding the database crash caused by cache overflow, thereby further improving the controllability of the distributed database.

[0076] See also Figure 2 As shown in FIG. 1 , it is a flowchart of the operation of an embodiment of the present invention in an HDFS database. The above solution is jointly operated with the database during implementation and is divided into four steps: reading, parsing, processing, associating, and outputting. By parsing the stored file details and associating the Hive metadata information upward, the management basis information of the Hive table and the underlying data is output, including:

[0077] 1. Read: Use the shell script plus the hdfs command to pull the Fs image information of the NameNode in the Standby state of HDFS to the local computer, format it, and then upload it to the specified path of hdfs.

[0078] 2. Parsing: Use Spark as the computing engine to read the Fs Image formatting information stored on HDFS, and parse the file information of each stored file, including key information such as file size, file path, user, group, block size, number of blocks, modification time, access time, etc.

[0079] 3. Processing: Use spark as the computing engine, take the file path path in the data in the previous step as the key field, and use the library name identifier .db as the identification mark to process the data into additional library name, table name, partition name, table-level path, partition path and other information. Further aggregate and process to produce the library statistics table dbInfo with Hive library name as the dimension, the table information statistics table tableInfo with library name and table name as the dimension, and the statistics table partition Info with library name and table name as the dimension. The statistical information includes the aggregated total file size, total number of files, last access time, last modification time and other information.

[0080] 4. Association: Use Spark as the computing engine to query Hive metadata information from the Hive metadata database and provide a metadata table metaInfo that contains the database name, table name, partition name, and table storage path location information. Associate the metaInfo table with dbInfo, tableInfo, and partitionInfo by matching the paths in the three tables through metaInfo.location. Determine the subordinate relationship between the underlying storage and the table in the metadata.

[0081] 5. Output: Load the user configuration rules, determine whether the table and table data are useless tables according to the last access data time matching rules, determine whether it is a large table according to the table storage size matching rules, determine whether small files need to be merged according to the partition data size and file number matching rules of the table partition, and determine whether it is redundant data according to the metadata information of the data under the library path that does not match the table

[0082] The above method can accurately obtain the time when the Hive table was last modified and accessed, and determine whether the table can be abandoned and cleaned up, thereby removing upstream output tasks and saving computing resources and storage resources. It can obtain information such as storage occupancy size and number of files at the library level, table level, and partition level in Hive, find out the extremely large occupied tables, compress data or optimize tasks, and save storage costs. It can perceive redundant garbage data without metadata in Hive, clean it up to reduce storage pressure, identify small file tables in Hive, merge small files, reduce the number of HDFS files and improve table read performance, identify table partitions with abnormal empty data, and improve data quality of Hive tables.

[0083] In particular, operations on HDFS can include:

[0084] Step S1, the project implementation personnel need to prepare in advance to create a custom directory ${hdfsPath} on hdfs with read and write permissions for the current operating user, and modify hdfs-site.xml to ensure that the dfs.namenode.accesstime.precision parameter is enabled and use the dfs.namenode.metadata.persist.interval parameter to set the required collection custom time ${interval};

[0085] Step S2, deploy the task jar and start the shell script to the Standby node of the NameNode of HDFS, or to a machine with an HDFS client. Ensure that the script starter user has read and write permissions to the current directory;

[0086] Step S3, modify the shell execution script, replace the hdfs path in the script with the custom path ${hdfsPath}, put the relevant spark Jar package into the task execution path in the script, and execute the script manually once;

[0087] Step S4: After the shell script is executed, check the relevant Hive result tables defined in the program to confirm that the result tables at the library level, table level, partition level, and associated metadata are successfully created and the values ​​are consistent with the actual storage information. This means that the deployment is successful;

[0088] Step S5, start the script and add it to the timing module, the timing interval>collection custom time ${interval}.

[0089] So far, the technical solutions of the present invention have been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it is easy for those skilled in the art to understand that the protection scope of the present invention is obviously not limited to these specific embodiments. Without departing from the principle of the present invention, those skilled in the art can make equivalent changes or substitutions to the relevant technical features, and the technical solutions after these changes or substitutions will fall within the protection scope of the present invention.

[0090] So far, the technical solutions of the present invention have been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it is easy for those skilled in the art to understand that the protection scope of the present invention is obviously not limited to these specific embodiments. Without departing from the principle of the present invention, those skilled in the art can make equivalent changes or substitutions to the relevant technical features, and the technical solutions after these changes or substitutions will fall within the protection scope of the present invention.

[0091] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A data warehouse management system, characterized in that: include: The database is set as several distributed spaces, and the capacity of each distributed space is set to a corresponding fixed value; Archive library, which is set up as several archive spaces, Including, a running archive for recording the running status of the database, and an archive directory for recording the running archive; A pedometer connected to the archive library, for determining the used capacity and the number of operations of the corresponding distributed space according to the state of the running archive and the archive directory, and for resetting the archive library according to the used capacity and / or the number of operations; A cache manager connected to the archive repository and the pedometer, for recording an archive directory origin and overwriting the archive directory origin in the archive repository in response to the reset instruction; The fixed value is related to the hardware of the database, and corresponds to different capacities for different distributed spaces; The archive directory has a corresponding overflow value according to the setting of the remaining capacity of the distributed space; The pedometer accumulates the count of the number of operations, and when the accumulated number reaches the overflow value, the pedometer triggers a reset instruction and adjusts the overflow value according to the remaining capacity of the distributed space; The overflow value is positively correlated with the fixed value; In response to the overwriting, the archive library merges several files whose sum of file sizes is smaller than the fixed value into the same distributed space.

2. The data warehouse management system according to claim 1, characterized in that: For a single run archive, include: Archive metadata, which are the operational properties of the corresponding distributed space; Archive cache, which is a number of running state slices of the corresponding distributed space; An archive fragment, which is a reset state snapshot of the distributed space; The operation attributes include the file size, file path, user, group, modification time, and access time in the distributed space.

3. The data warehouse management system according to claim 2, characterized in that: For a single distributed space, when a single opening, closing and / or modification operation is performed on it, each operation state slice of the corresponding operation is formed; For the archive cache, each operation status slice is recorded in the time sequence of the operation, and a queue is formed in the time sequence, and the sequence number of each cache is stacked according to the queue, and a corresponding overflow number is set according to the queue; When the running status slices reach the overflow quantity, the archive cache triggers a clear instruction and clears several running status slices in the running status slices that are located at the front of the queue and / or correspond to several sequence numbers at the bottom of the stack.

4. The data warehouse management system according to claim 3, characterized in that: The archive fragment responds to the clear instruction and records the running status slice with the smallest sequence number or located at the bottom of the stack cleared from the archive cache.

5. The data warehouse management system according to claim 1, characterized in that: For a single distributed space, it corresponds to a single feature code, which is used to mark the content of the distributed space; The archive library marks the archive directory of the corresponding database according to each feature code.

6. The data warehouse management system according to claim 5, characterized in that: The archive directory stores the feature codes corresponding to the running archives, sorts the running archives in order according to the activation time of the running archives, and counts the number of operations.

7. The data warehouse management system according to claim 1, characterized in that: The cache manager is provided with a fixed cache area for storing the archive directory origin and a temporary cache area for storing temporary directory archives; When responding to the reset instruction, the cache manager reads the archive directory origin in the fixed cache area and records the triggering time of the reset instruction as the zeroing time.

8. The data warehouse management system according to any one of claims 4 or 7, characterized in that: The temporary cache area responds to the clear instruction and records the corresponding archive directory as the temporary directory origin.

9. The data warehouse management system according to claim 8, characterized in that: The temporary buffer area is also provided with a zeroing time threshold. When the zeroing time reaches the zeroing time threshold, the temporary buffer area updates the archive directory origin and overwrites the temporary directory origin as the archive directory origin in the fixed buffer area.

Images