A star table fast merging method based on ClickHouse column database

Abstract

The application provides a kind of based on ClickHouse column database's massive star table data fast merging method and system, through reducing the complexity of spherical distance calculation and using equidistant grid division index establishment method, the merging process of multi-band star table data is accelerated;The application is aimed at the application scene of astronomical cross identification and star table merging and the data processing demand of CSST high performance, low delay, research uses column database to complete the storage, retrieval and identification process of massive star table data, has good use value, has wide application scene in astronomical data processing field, also provides a new idea and direction for astronomical data processing.

Description

TECHNICAL FIELD

[0001] The present application relates to a star table fast merging method based on ClickHouse columnar database, relates to spherical distance calculation, equal-interval space division and index establishment, and belongs to the field of astronomical data processing. BACKGROUND

[0002] With the continuous development of astronomical survey tasks, the scale of astronomical observation data is rapidly growing, and the traditional data processing method has been unable to meet the storage and fast retrieval requirements of massive astronomical data. In view of the application scenarios of astronomical cross-identification and star table merging and the high-performance and low-latency data processing requirements of CSST, a columnar database is used to complete the storage, retrieval and identification of massive star table data. By widely comparing different index establishment methods, an equal-interval grid division method is used to establish a corresponding spatial index for the star table data, so as to improve the data query and retrieval speed and processing performance.

[0003] With the continuous improvement of the precision and performance of astronomical observation equipment and the progress of observation technology, the range of human observation is becoming wider and wider, and the data collected by observation is becoming more and more. The Large Sky Area Multi-object Fiber Spectroscopic Telescope (LAMOST) collects about 20GB of spectral data every night. The Square Kilometre Array (SKA) jointly constructed by China and foreign countries is expected to produce 157TB of data per second for the low-frequency array and 2TB of data per second for the medium-frequency array. The China Space Station Telescope (CSST) to be launched soon will complete seamless spectral survey observation and deep multi-color imaging observation with high resolution and large sky area, and will detect tens of billions of raw data in the target sky area. The entire running cycle will accumulate about 50PB of data. The massive observation data contains many unknown discoveries, but further processing and analysis are needed to obtain them. Therefore, how to efficiently and quickly process these observation data and how to merge the data collected by observation at different times for the same celestial body or the data in different wave bands have become a problem closely followed by the fields of astronomy and big data.

[0004] With the development and progress of computer and distributed technology, AstroSpark system based on Spark technology framework design, processing method based on MapReduce technology framework and FASTDB system based on distributed array database gradually appeared. These methods greatly improved the processing performance of astronomical star catalog data, to a certain extent, met the data processing needs at that time, but could not well cope with the now more massive data size and more stringent performance requirements. In addition, the progress and development of database technology led to the birth of ClickHouse columnar database based on columnar data storage architecture, which has more excellent query and retrieval performance and good horizontal expansion capability, and can more calmly cope with the demand for rapid processing of massive astronomical data.

[0005] Traditional relational databases based on row-based data storage architecture need to read the entire row data completely when retrieving related data according to the judgment condition, even if only part of the data field information is needed. The columnar database can flexibly read the required field column data and quickly present the corresponding results. The traditional relational database consumes a lot of resources and time in reading and writing invalid data when processing data, thereby reducing the overall performance and efficiency of the data processing process. The use of columnar databases in the field of astronomical star catalog data processing greatly avoids such situations, especially in the star catalog data merging scenario that only relies on a small number of field columns.

[0006] The present application introduces the columnar database ClickHouse into the field of astronomical data processing, establishes a spatial index through an equidistant grid division method, improves the query and retrieval performance of star catalog data, and reduces the time consumption of data merging process. SUMMARY

[0007] In view of the above defects of the prior art, the purpose of the present application is to provide a star catalog fast merging method based on ClickHouse columnar database, which reduces the complexity of spherical distance calculation and speeds up the merging process of multi-band star catalog data.

[0008] The technical solution of the present application is: a star catalog fast merging method based on ClickHouse columnar database,

[0009] comprising the following steps:

[0010] S100 importing multiple star catalog data files of original observations into a database;

[0011] S200 dividing celestial body records in the database into a reference data table and a sample data table,

[0012] judging whether the data records in the above two data tables have the same division number data field using the same division method;

[0013] If the data field is absent, S300 is executed;

[0014] Otherwise, S400 is executed;

[0015] S300 divides the records in the reference data table and the sample data table into equal-interval grids in the right ascension and declination directions respectively, encodes the divided grid blocks, adds a division number data field to store the grid number where the celestial body record is located, and establishes a spatial index accordingly

[0016] S400 performs coordinate system conversion on the records in the reference data table and the sample data table, converts the right ascension and declination coordinates into three-dimensional space coordinates;

[0017] S500 performs data table spatial join on the division number fields in the two data tables as the connection condition, and generates a temporary intermediate result data table;

[0018] S600 calculates the spherical distance corresponding to each record according to the two coordinate point information contained in the temporary intermediate result data table, and compares the calculated distance with a first preset threshold r to filter out the records less than or equal to the first preset threshold r;

[0019] S700 groups the filtered records according to the data fields in the reference data table, records in the same group are multiple observation records of the same celestial body, and the multiple records are sequentially combined in all data fields to obtain the final result.

[0020] Further, the spatial division in S300 further includes the following steps,

[0021] S300a obtains the distribution range of all celestial body records in the right ascension and declination directions in the two data tables, and determines the value of the specific division interval k in combination with the first preset threshold r;

[0022] S300b performs equal-interval grid division according to the right ascension and declination coordinate information in the records, and encodes each divided grid block;

[0023] S300c adds a division number field to the celestial body records in the data table to store the division grid number where the celestial body record is located, and establishes a spatial index in the database according to the field;

[0024] S300d determines the grid search range when data is combined according to the latitude range where the records in the reference data table and the sample data table are located, in combination with the growth and change relationship of the right ascension and declination.

[0025] Further, the specific steps of coordinate conversion in step S400 are,

[0026] S400a converts the right ascension and declination coordinates in the star catalog record into three-dimensional space coordinates in the Cartesian coordinate system, and uses the corresponding conversion formula to perform spatial mapping of the coordinate system;

[0027] S400b synchronously maps the first preset threshold r to the corresponding space, and converts it into the second threshold γ in the Cartesian coordinate system.

[0028] Further, the multi-column data of the celestial body record in the star catalog is stored by column compression, and all hardware resources are fully utilized for parallel processing of query tasks.

[0029] Compared with the prior art, the beneficial effects of the present application are:

[0030] (1) The equal-interval grid division method is adopted to establish the corresponding spatial index. Simple mathematical operations can complete the corresponding division and coding. This division method is fast and convenient, and can be quickly implemented in different databases and other programs;

[0031] (2) The relationship between the declination change δ and the right ascension change α at different latitudes is proposed. In the connection process, according to the latitude range of the record, the corresponding formula is applied to include all possible grid blocks in the data processing range, which can effectively avoid the occurrence of missing source problems;

[0032] (3) A distance calculation method based on coordinate system conversion is proposed. Through the coordinate system conversion formula, the right ascension and declination coordinates in the star catalog data can be converted into three-dimensional space coordinates in the Cartesian coordinate system, and the calculation of spherical distance is converted into the calculation of Euclidean distance in three-dimensional space. Through such operation, the calculation amount of single distance calculation is effectively reduced, and the complexity of calculation is reduced, which can obtain obvious performance improvement in large star catalog data processing. BRIEF DESCRIPTION OF DRAWINGS

[0033] Fig. 1 The basic process diagram of the multi-star catalog merging provided by the embodiment of the present application is shown in the figure;

[0034] Fig. 2 The sky grid division search range diagram provided by the embodiment of the present application is shown in the figure. DETAILED DESCRIPTION

[0035] The embodiments of the present application will be described in detail below. The following embodiments are implemented on the premise of the technical scheme of the present application, and detailed implementation modes and specific operation processes are given, but the protection scope of the present application is not limited to the following embodiments.

[0036] EMBODIMENT

[0037] Please refer to Figs. 1-2 A star catalog fast merging method based on ClickHouse column database,

[0038] comprising the steps of:

[0039] S100 importing multiple original observed star catalog data files into a database;

[0040] S200 dividing the celestial body records in the database into a reference data table and a sample data table,

[0041] determining whether the data records in the above two data tables are divided by the same division number data field using the same division method;

[0042] If not, S300 is executed;

[0043] Otherwise, S400 is executed;

[0044] All star catalog data imported into the database is divided into a reference data table and a sample data table. If there are n celestial body records in the reference data table and m celestial body records in the sample data table, then performing a Cross Join method of data table connection without any optimization will generate a large temporary intermediate result data table with n*m records. This data table will contain coordinate position information in the two data tables involved in the connection, and later the spherical distance corresponding to each record needs to be calculated based on the coordinate information in the table. The time complexity of this algorithm is O(n*m), which is unacceptable in large-scale star catalog data processing. Moreover, the amount of calculation in this process is large and contains a large amount of invalid calculation, reducing the overall data processing efficiency and performance. If the sky area range of the star catalog data distribution can be effectively divided, the data table connection process and the subsequent distance calculation process can be optimized to some extent. If the sky area range is divided into num blocks, then the records in each block will be approximately evenly distributed m records in the sample data table. At this time, when performing data table connection, the number of the division block where the celestial body record is located can be used as the connection condition, and a more efficient Inner Join method connection is performed, which can greatly improve the connection speed and performance. After spatial division, the number of records in the temporary intermediate result data table generated by connection is about , which is reduced by hundreds or even thousands of times in data size compared to the previous non-division method, which can significantly improve the performance in massive star catalog data processing.

[0045] S300 performs equal-interval grid division on the records in the reference data table and the sample data table in the right ascension and declination directions respectively, and encodes the grid blocks generated by the division; adds a division number data field to store the grid number where the celestial body record is located, and establishes a spatial index accordingly;

[0046] The spatial division in S300 also includes the following steps,

[0047] S300a obtains the distribution range of all celestial body records in the right ascension and declination direction in the two data tables, and determines the value of the specific division interval k in combination with the first preset threshold r;

[0048] S300b performs equidistant grid division according to the right ascension and declination coordinate information in the records, and encodes each divided grid block;

[0049] S300c adds a division number field to the celestial body records in the data table, stores the division grid number where the celestial body records are located, and establishes a spatial index according to the number;

[0050] S300d determines the grid search range when data merging is performed according to the latitude range where the records in the reference data table and the sample data table are located, in combination with the growth and change relationship of the right ascension and declination.

[0051] The specific implementation steps are as follows, respectively using (RA min , RA max ) and (Dec min , Dec max ) to represent the distribution range of all celestial body records in the right ascension and declination direction in the database, and using k to represent the division interval when performing spatial division. Then, when performing equidistant grid division in the right ascension and declination direction, the corresponding division grid numbers M and N can be represented by the following formulas,

[0052]

[0053]

[0054] The setting of the division interval k size will affect the number of records falling into the division grid, and then affect the subsequent data table connection process. If the value of this variable is too large, a single division grid will contain multiple celestial body records. These celestial body records not only contain multiple observations of the same celestial body, but also include invalid records with adjacent positions and close distances. The invalid records contained in the division grid will reduce the connection speed in the data table connection process, thereby reducing the overall data processing efficiency. If the division interval k is too small, the data that should be combined will be divided into adjacent grid blocks during division, thereby missing the correct data in this part during data table connection, affecting the accuracy of the final result. According to the role played by the first preset threshold r in the data processing process, the corresponding division interval k should be set to 4 times the first preset threshold r. After determining the value of the division interval k, the division numbers i and j of each record in the right ascension and declination direction can be calculated using the following formula. Finally, the two division numbers are combined together as the number of the division grid, and a corresponding spatial index is established according to the number.

[0055]

[0056]

[0057] The same celestial body may be recorded in different observation records in adjacent grids. Therefore, when searching for potential records in the reference data table, in addition to searching for all records in the grid where the record is located, it is necessary to search for records in adjacent grids. Whether to search for records in adjacent grids is determined according to the search range (ra±r, dec±r) determined by the reference record coordinates (ra, dec) and the first preset threshold r. If the range spans multiple grids, multiple areas need to be searched. Due to the change of the celestial sphere, the growth of the right ascension direction and the declination direction in the actual search range is not always equal to the first preset threshold r. If α represents the growth of the search range in the right ascension direction and δ represents the growth of the search range in the declination direction, the actual search range is approximately an elliptical area (ra±α, dec±δ). The relationship between the declination growth δ and the right ascension change α in the low-latitude area is as follows:

[0058] Change relationship in low-latitude area

[0059]

[0060] Change relationship in middle-latitude area

[0061]

[0062] When performing data table connection, the approximate distribution range of the potential record can be obtained according to the latitude range of the record. The above-mentioned equal-interval grid division formula is applied to the record distribution range to calculate the grid number in the distribution range, and then all possible grid blocks are included in the search and processing range of the record, effectively avoiding the occurrence of data missing source problems. The number of records in the high-latitude area is small, and including all these records in the search and processing range of the corresponding record will not significantly affect the performance, and a balance between speed and accuracy can be achieved.

[0063] S400 performs coordinate system conversion on the records in the reference data table and the sample data table, and converts the right ascension and declination coordinates into three-dimensional space coordinates; the specific steps of coordinate conversion in step S400 are,

[0064] S400a converts the right ascension and declination coordinates in the star table record into three-dimensional space coordinates in the Cartesian coordinate system, and performs spatial mapping of the coordinate system using the corresponding conversion formula;

[0065] S400b synchronously performs corresponding spatial mapping on the first preset threshold r, and converts the first preset threshold r into a second threshold y in a Cartesian coordinate system.

[0066] The specific steps of S400 are as follows: converting the recorded right ascension and declination coordinates (ra, dec) into Cartesian coordinates (x, y, z) in three-dimensional space; and inputting the Cartesian coordinates into a coordinate conversion formula.

[0067]

[0068] The first preset threshold r is determined according to observation device observation accuracy, data distribution characteristics, scientific data processing accuracy requirements and other factors. The threshold is mainly used for the determination of homologous celestial bodies in the data merging process. The corresponding data merging operation is performed only when the calculated distance is less than or equal to the determination threshold. The first preset threshold r is set in the equatorial coordinate system. Since the spatial mapping of the coordinate system is performed in the above process, the first preset threshold r set previously also needs to be transformed using the corresponding formula. The conversion formula of the threshold r is as follows:

[0069]

[0070] S500 takes the division number fields in the two data tables as the connection condition, performs data table spatial connection, and generates a temporary intermediate result data table.

[0071] S600 calculates the spherical distance corresponding to each record according to the two coordinate point information contained in the temporary intermediate result data table; and compares the calculated distance with the first preset threshold r, and selects the records less than or equal to the first preset threshold r.

[0072] S700 groups the selected records according to the data fields in the reference data table. The records in the same group are the multiple observation records of the same celestial body. The multiple records are sequentially merged in all data fields to obtain the final result.

[0073] The equal-interval grid division method is adopted to establish the corresponding spatial index. Simple mathematical operations can complete the corresponding division and coding. The division method is fast and convenient, and can be quickly implemented in different databases and other programs. Using the coordinate system conversion calculation method, the right ascension and declination coordinates stored in the star catalog data can be converted into three-dimensional space coordinates in the Cartesian coordinate system through the coordinate system conversion formula. The calculation of the spherical distance is converted into the calculation of the Euclidean distance in the three-dimensional space. Through such operation, the calculation amount and complexity of single distance calculation are effectively reduced, and the performance is obviously improved in large star catalog data processing.

[0074] The preferred embodiments of the present application have been described above in detail. It should be understood that modifications and variations to the present application can be effected by persons of ordinary skill in the art without departing from the spirit or the broad aspects of the present application. Thus, it is expressly intended that such modifications and variations are to be included within the scope of the present application as defined in the following claims.

Claims

1. A method for fast merging of star lists based on ClickHouse columnar database, characterized in that, Includes the following steps: The S100 imports multiple star catalog data files from the original observations into the database; S200 divides the astronomical records in the database into a reference data table and a sample data table. Determine whether the data records in the two data tables above have a partition number data field that uses the same partitioning method; If the data field does not exist, execute S300; Otherwise, execute S400; S300 divides the records in the reference data table and sample data table into equally spaced grids in the right ascension and declination directions, and encodes the grid blocks generated by the division. Add a grid number field to store the grid number where the celestial record is located, and use this to build a spatial index. S400 performs coordinate system transformation on the records in the reference data table and sample data table, converting the right ascension and declination coordinates into three-dimensional spatial coordinates; S500 uses the partition number field in the two data tables as the join condition to perform a data tablespace join and generate a temporary intermediate result data table. S600 calculates the spherical distance corresponding to each record based on the two coordinate point information contained in the temporary intermediate result data table; compares the calculated distance with the first preset threshold r, and filters out records that are less than or equal to the first preset threshold r. The S700 groups the selected records according to the data fields in the reference data table. Records grouped into the same group are multiple observation records of the same celestial body. The corresponding data of these multiple records are merged in all data fields in turn to obtain the final result.

2. The method for fast merging of star lists based on ClickHouse columnar database according to claim 1, characterized in that, The spatial division in S300 also includes the following steps. S300a obtains the distribution range of all celestial records in the right ascension and declination directions in the two data tables, and determines the value of the specific division interval k in combination with the first preset threshold r; S300b divides the data into equally spaced grids based on the right ascension and declination coordinates recorded in the data, and encodes each grid block. The S300c adds a grid number field to the celestial records in the data table to store the grid number in which the celestial record is located, and creates a spatial index in the database based on this field. Based on the latitude range recorded in the reference data table and sample data table, S300d determines the grid search range for data merging by combining the growth relationship between right ascension and declination.

3. The method for fast merging of star lists based on ClickHouse columnar database according to claim 1, characterized in that, The specific steps of coordinate transformation in step S400 are as follows: S400a converts the right ascension and declination coordinates in the star catalog into three-dimensional spatial coordinates in the Cartesian coordinate system and uses the corresponding conversion formula to perform spatial mapping of the coordinate system. S400b synchronously performs corresponding spatial mapping on the first preset threshold r, converting it into the second threshold γ in the Cartesian coordinate system.

4. The method for fast merging of star lists based on ClickHouse columnar database according to claim 1, characterized in that, A columnar database is used to compress and store the multi-column data of celestial bodies in the star catalog, and to make full use of all hardware resources to process query tasks in parallel.

Images