Method for improving digital reconstruction efficiency of electromechanical equipment of Internet of Things, terminal equipment and storage medium

Abstract

The invention relates to a method for improving the digital reconstruction efficiency of electromechanical equipment of the Internet of Things and terminal equipment, and mainly relates to the field of data processing. According to the method, the data association operation is performed by selecting the big table association key, and the broadcast mechanism is adopted for processing when the data volume meets the condition, so that the connection calculation efficiency is effectively improved. The method comprises the following steps: performing association operation on big table data and storing a processing result; determining whether to broadcast data according to the data volume; executing a left connection operation by using the broadcast data and generating a dynamic partition mark; performing duplicate removal processing on the data; and establishing an index type adapted to the data features. And when the data volume exceeds the memory limit, a data fragmentation and partition adjustment strategy is adopted. The method is especially suitable for the field of big data processing, and can significantly improve data processing speed and system resource utilization rate.

Description

Technical Field

[0001] The present application relates to the field of Spark SQL, and in particular to a method, terminal device and storage medium for improving the efficiency of digital reconstruction of mechatronic devices in the Internet of Things. Background Art

[0002] In the context of big data, the data of electromechanical equipment usually presents the characteristics of large historical base, large increment and high growth rate. When two tables with hundreds of billions of data are directly connected for calculation, it is often encountered that the amount of table data is too large, resulting in a long computing task or even task failure. Therefore, in order to improve computing efficiency, the characteristics of hive table column storage can be used to allow Spark SQL to quickly read the connection association key, first find the duplicate keys in both large tables, if the size of these duplicate keys after deduplication does not exceed the reasonable maximum broadcast variable memory of the cluster, then these keys can be used as broadcast tables and associated with the two large tables respectively, and whether the duplicate key is in the large table is used as a dynamic partition field. The next step is to directly obtain the data of the partition belonging to the duplicate key in the dynamic partition field to calculate and obtain the result.

[0003] Therefore, in view of the above-mentioned shortcomings, the present invention provides a new solution. Summary of the invention

[0004] The main purpose of this application is to provide a method and terminal device for improving the efficiency of digital reconstruction of IoT electromechanical equipment. By first finding the duplicate keys in two large tables, and using the hive table column storage characteristics and Spark SQL's fast reading capabilities, the duplicate keys are dynamically partitioned as broadcast tables, which greatly reduces the amount of data in the calculation process, thereby significantly improving the efficiency of connection calculations, aiming to solve the problem of low computing efficiency due to excessive data volume when calculating large tables.

[0005] To achieve the above objectives, the present application provides a method for improving the efficiency of digital reconstruction of IoT electromechanical equipment, comprising the following steps:

[0006] S1: Select association keys from the IoT device data table A and the IoT environment data table B, perform association operations on the two tables, obtain and remove duplicate association keys, and store the processed results in table C;

[0007] S2: Use the command to check the amount of data generated in the distributed file system, that is, use the count-h command of HDFS to automatically obtain the amount of data in table C, and check whether the amount of data in table C is less than the maximum broadcast variable memory supported by the cluster;

[0008] S3: If the amount of data in table C is smaller than the maximum broadcast variable memory supported by the cluster, the data in table C is broadcast for calculation;

[0009] S4: Use the broadcasted table C, large table A, and large table B to perform a left join operation, generate a dynamic partition flag based on the association key, and write them into different partitions of the result table D respectively;

[0010] S5: De-duplicate the data in the A_INNER and B_INNER partitions and write the processed data into the INNER partition of table D.

[0011] S6: After completing the data processing, delete the A_INNER partition and the B_INNER partition.

[0012] In step S2, when the data volume of table C is larger than the maximum broadcast variable memory supported by the cluster, a data sharding strategy, an adjustment partitioning strategy, or a combination of the two is used to process the data volume of table C.

[0013] In one embodiment, when the amount of data in table C is larger than the maximum broadcast variable memory supported by the cluster, a data sharding strategy or an adjustment partitioning strategy is used for processing.

[0014] In one embodiment, a method for processing table C data whose data volume is greater than the maximum broadcast variable memory supported by the cluster using a data sharding strategy includes the following steps:

[0015] Shard the data in table C according to the hash value or range of the associated key;

[0016] Define each shard as a broadcast variable and perform a left join operation using these broadcast variables;

[0017] Write the result of the left join operation to the corresponding partition;

[0018] Deduplication processing is performed on the data written into the partition, and the deduplication processing step includes: comparing the timestamps or unique identifiers of the records in the corresponding partition, retaining the latest records or merging field values;

[0019] Merge the deduplicated data into the final result table.

[0020] In another embodiment, a method for processing table C data whose data volume is greater than the maximum broadcast variable memory supported by the cluster by using a data sharding strategy includes the following steps:

[0021] Partition the data in table C according to the range of the association key. Each partition corresponds to at least one data table space.

[0022] Perform a left join operation on the data in each partition;

[0023] After the left join operation is completed, the result data is deduplicated by retaining the latest record or merging field values ​​with the same association key;

[0024] Merge the deduplicated data into the final result table;

[0025] Use the first preset management statement of the database management statement to add new partitions as needed, or use the second preset management statement to delete partitions that are no longer needed;

[0026] Use the third preset management statement ALTER TABLE ... COALESCE PARTITION to merge hash partitions, or the fourth preset management statement ALTER TABLE ... MERGE PARTITION to merge adjacent range partitions as needed.

[0027] Among them, the first preset management statement is specifically ALTER TABLE...ADD PARTITION; the second preset management statement is specifically ALTER TABLE...DROP PARTITION; the third preset management statement is specifically ALTER TABLE...COALESCE PARTITION; and the fourth preset management statement is specifically ALTER TABLE...MERGEPARTITION.

[0028] Furthermore, when the amount of data in table C exceeds the maximum broadcast variable memory of the cluster, you can directly combine the data sharding strategy and the adjustment partitioning strategy to first shard the data according to the hash value of the associated key, and then partition each shard according to the range of the associated key.

[0029] In one embodiment, before performing step S4, an index type suitable for data characteristics is established on the association key of the big table A and the big table B.

[0030] In one embodiment, the database index establishment method includes the following steps: S4.1 performing data distribution analysis on the association keys in the large table A and the large table B to determine the data characteristics and cardinality of the association keys.

[0031] S4.2 Determine the cardinality of the data features of the associated key based on the above, the cardinality of the data features of the associated key includes at least: a first preset cardinality and a second preset cardinality, and compare the first preset cardinality and the second preset cardinality respectively through a preset cardinality value, distinguish the cardinality of the data features of the associated key as high cardinality or low cardinality, and then select a suitable index type based on the above distinction result; wherein, the index type is selected only when the first preset cardinality is higher than the second preset cardinality and the preset cardinality value, or the second preset cardinality is higher than the first preset cardinality and the preset cardinality value, the associated key has a high cardinality, and a B-tree index or a bitmap index is selected; in the case of comparison of the other preset cardinality values ​​with the first preset cardinality and the second preset cardinality, the associated key has a low cardinality, and a hash index is selected.

[0032] S4.3 Use the commands or interfaces provided by the database management system to create an index based on the selected index type.

[0033] In one embodiment, the data distribution analysis includes at least the following steps:

[0034] Preliminarily obtain the data type of the association key in large table A and large table B, whether null values ​​are allowed, and the data range;

[0035] Calculate basic statistical data of the association key, the basic statistical data at least including: total number of records, number of unique values, number of null values, number of different values;

[0036] According to the number of unique values, calculate the cardinality of the association key and analyze whether the distribution of the association key values ​​is uniform or whether there is data skew;

[0037] Count the occurrence frequency of each value of the association key, and create a histogram of the values ​​of the association key based on the above statistical results;

[0038] Verify data for accuracy and consistency.

[0039] To achieve the above-mentioned purpose, the present application also provides a terminal device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, the steps of the method for improving the computing efficiency of joining large SQL tables as described above are implemented.

[0040] The beneficial effects of the technical solution of the present invention are:

[0041] By pre-processing the association keys of large tables and judging broadcast variables, the efficiency of connection operations in big data processing is significantly improved. This ensures the quality of data processing while greatly improving the speed of data processing. The data volume is checked by viewing the data volume generated in the distributed file system, that is, the count-h command of HDFS, to ensure the effectiveness and economy of data processing. When the data volume is less than the maximum broadcast variable memory of the cluster, the data is directly broadcast for calculation, avoiding unnecessary resource consumption and reducing calculation time.

[0042] At the same time, when the amount of data to be processed is greater than the maximum broadcast variable memory supported by the cluster, the optimization of the data sharding strategy and partitioning strategy is adopted, which not only optimizes the memory usage, but also reduces the network transmission overhead through the optimization of sharding and partitioning, making the big data processing more efficient and stable. Further, by establishing an index suitable for the data characteristics before step S4, the efficiency of data processing is further improved. Through detailed data distribution analysis and selection of appropriate index types, the query time is reduced. Especially when processing high-cardinality or low-cardinality association keys, the optimized index strategy greatly improves the efficiency of connection operations.

[0043] In addition, through the implementation of terminal equipment, computer programs and storage media, the entire processing flow is easy to manage and maintain, greatly improving the convenience of users in processing big data. In the specific implementation process, by removing duplicate data, optimizing partitioning strategies and providing terminal equipment and other technical means, the problems of low heating efficiency (i.e. low data processing efficiency) and uneven heating (i.e. uneven data distribution) in the prior art are effectively solved, ensuring the uniformity and efficiency of data processing.

[0044] To sum up, the advantage of the present invention is that it not only reduces the demand for computing resources, but also improves the speed of data processing. At the same time, by optimizing the processing logic of duplicate keys, it brings substantial efficiency improvement to the field of big data processing and better adapts to the efficient data processing needs in the big data era. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Figure 1 A schematic diagram of the method steps for improving the efficiency of SQL table join calculations in one embodiment of the present application.

[0046] Figure 2 This is a flow chart of step S2 in one embodiment of the present application;

[0047] Figure 3 This is a flow chart of step S4 in one embodiment of the present application;

[0048] Figure 4 A schematic diagram of a method flow chart of a specific process of establishing an index on an association key in an embodiment of the present application;

[0049] Figure 5 A distribution histogram of the amount of association key data in table A versus the number of association keys in an embodiment of the present application;

[0050] Figure 6 A distribution histogram of the amount of association key data in table B versus the number of association keys in an embodiment of the present application;

[0051] Figure 7 This is a schematic block diagram of the internal structure of a terminal device according to an embodiment of the present application.

[0052] The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0053] The embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended to be used to explain the present application, and should not be construed as limiting the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of the present application.

[0054] In addition, if the description of "first", "second", etc. is involved in this application, it is only used for descriptive purposes (such as for distinguishing the same or similar elements), and cannot be understood as indicating or suggesting its relative importance or implicitly indicating the number of technical features indicated. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In addition, the technical solutions between the various embodiments can be combined with each other, but it must be based on the ability of ordinary technicians in this field to implement. When the combination of technical solutions is contradictory or cannot be implemented, it should be deemed that such combination of technical solutions does not exist and is not within the scope of protection required by this application.

[0055] See also Figure 1 The present invention proposes a method for improving the efficiency of digital reconstruction of mechatronic devices in the Internet of Things, comprising the following steps:

[0056] S1: First, select the association key from large table A and large table B, use the field to associate the two tables, obtain and remove duplicate association keys, and store the processed results in table C.

[0057] S2: Check the data volume generated in the distributed file system through the HDFS count -h command to automatically obtain the data volume of table C and check whether the data volume of table C is less than the maximum broadcast variable memory supported by the cluster.

[0058] Specifically, if the data volume of table C is smaller than the maximum broadcast variable memory supported by the cluster, proceed to the next step; if the data volume of table C is larger than the maximum broadcast variable memory supported by the cluster, the data volume of table C needs to be processed so that the data volume of table C is smaller than the maximum broadcast variable memory supported by the cluster.

[0059] S3: Broadcast the data in table C for calculation.

[0060] Specifically, when the data volume of table C is confirmed to be smaller than the maximum broadcast variable memory supported by the cluster, it is defined as a broadcast variable through spark.sparkContext.broadcast(tableC); tableC is a DataFrame or RDD; and in Spark, broadcast variables are used to cache smaller data sets on each working node for parallel computing of multiple tasks.

[0061] S4: Use the broadcasted table C, large table A, and large table B to perform a left join operation, generate a dynamic partition flag based on the association key - IF (a. key is not null and b. key is not null, 'A_INNER', 'B_INNER') as flag, and write the data into the A_INNER and B_INNER partitions of the result table D respectively.

[0062] Specifically, Left Join A and C: Use large table A as the left table and perform a left join operation with table C, which is the right table and broadcast table, with the association key being k.

[0063] In this process, select the required fields of large table A and add a dynamic partition flag - use the conditional expression if (Ak is not null and Ck is not null, 'A_INNER', 'A_LEFT') to generate a flag. According to this flag, write the data to the A_INNER partition and A_LEFT partition of the result table D respectively.

[0064] Left Join B and C: Use large table B as the left table and table C as the right table and broadcast table to perform a left join operation, and also use k as the association key.

[0065] In this process, the required fields of large table B are selected, and a dynamic partition flag is added - through the conditional expression if (Bk is not null and Ck is not null, 'B_INNER', 'B_LEFT'), the data is written into the B_INNER partition and B_LEFT partition of the result table D according to the flag.

[0066] S5: De-duplicate the data in the A_INNER and B_INNER partitions according to the established rules, and write the processed data into the INNER partition of table D.

[0067] S6: After completing the data processing, delete the A_INNER partition and the B_INNER partition.

[0068] In this embodiment, on the one hand, the efficiency of SQL large table join calculation can be effectively improved by utilizing the broadcast mechanism and Spark dynamic partitioning. The broadcast mechanism can cache smaller data sets on each working node for parallel calculation of multiple tasks, thereby reducing the overhead of data transmission and calculation. Dynamic partitioning can generate different partition flags according to the association key, and write data into different partitions respectively, avoiding the problems of full table scanning and repeated calculation.

[0069] On the other hand, the memory usage can be reduced by processing the data volume of table C so that it is smaller than the maximum broadcast variable memory supported by the cluster. This can better utilize cluster resources and improve computing performance. At the same time, through the left join operation and the generation of dynamic partition marks, the data is written to different partitions of the result table D. In this way, the required fields can be selected according to actual needs, and dynamic partition marks can be added to achieve optimized storage and management of data.

[0070] In addition, the data in the A_INNER and B_INNER partitions are deduplicated and written to the INNER partition of table D. This removes duplicate data and improves data accuracy and consistency. After the data processing is completed, the A_INNER and B_INNER partitions are deleted. This frees up space, avoids the accumulation of useless data, and keeps the system clean and efficient.

[0071] In one embodiment, in step S2, when the data volume of table C is larger than the maximum broadcast variable memory supported by the cluster, a data sharding strategy, an adjustment partitioning strategy, or a combination of the two may be used to process the data volume of table C.

[0072] For example, when the data volume of table C is larger than the maximum broadcast variable memory supported by the cluster, the specific steps for processing the data volume of table C through the data sharding strategy are as follows: Figure 2 As shown:

[0073] S2-a shards the data in table C according to the hash value or range of the associated key;

[0074] S2-b defines each subset as a broadcast variable, then uses the broadcast variable to perform a left join operation and writes the above results to the corresponding partition;

[0075] After the above data is written to the corresponding partition, S2-c deduplicates the above data by retaining the latest record or merging field values, and merges the processed data into the final result table.

[0076] When the data volume of table C is larger than the maximum broadcast variable memory supported by the cluster, the specific steps for adjusting the partition strategy for processing the data volume of table C are as follows: Figure 3 As shown:

[0077] S2-A partitions the data in table C according to the range of the association key. Each partition can correspond to one or more data table spaces.

[0078] S2-B performs a left join operation on the data in each partition;

[0079] After completing the left join operation, S2-C performs deduplication processing on the result data. This can be achieved by retaining the latest record or merging field values ​​with the same association key, and then merging the deduplication-processed data into the final result table.

[0080] In addition, for existing table partitions, you can use the ALTER TABLE ... ADD PARTITION statement to add new partitions, or use the ALTER TABLE ... DROP PARTITION statement to delete partitions that are no longer needed. You can also use the ALTER TABLE ... COALESCE PARTITION statement to merge hash partitions as needed, or use the ALTER TABLE ... MERGE PARTITION statement to merge adjacent range partitions.

[0081] Furthermore, when the amount of data in table C is larger than the maximum broadcast variable memory supported by the cluster, the data sharding strategy and the partition adjustment strategy can be used at the same time. That is, the data in table C is first sharded according to the hash value of the associated key, and then partitioned within each shard according to the range of the associated key.

[0082] In this embodiment, on the one hand, a large data set is divided into multiple smaller subsets through a data sharding strategy, and each subset is defined as a broadcast variable. This ensures that the size of each broadcast variable is suitable for processing in the memory of a single node, thereby avoiding processing failures due to insufficient memory. After the data is sharded, each subset can be left-joined independently, which means that multiple subsets can be processed in parallel, significantly improving the speed and efficiency of data processing.

[0083] On the other hand, adjusting the partitioning strategy, such as partitioning data according to the range of the associated key, can bring advantages in data localization, reduce data movement across the network, and further accelerate the data processing process. After the data is written to the corresponding partition, deduplication is performed by retaining the latest record or merging field values ​​to ensure data consistency and accuracy. Finally, the processed data is merged into the final result table to provide a clean and accurate data set for subsequent analysis and query.

[0084] In addition, the combination of data sharding strategy and adjustment partitioning strategy not only improves the ability to process large amounts of data, but also increases the flexibility and scalability of the system. The system can dynamically adjust the sharding and partitioning strategies according to the actual amount of data and cluster resources to adapt to different workloads. And through sophisticated data management and distributed processing, these strategies can better utilize cluster resources, improve overall resource utilization, and reduce waiting time caused by resource bottlenecks.

[0085] See also Figure 4 In one embodiment, before performing a left join operation on the broadcasted table C, the large table A, and the large table B in step S4, an index may be first created on the association key of the large table A and the large table B to further speed up data retrieval.

[0086] The specific process of creating an index on the association key is as follows:

[0087] S4.1 Perform data distribution analysis on the association keys in the large table A and the large table B to determine the data characteristics and cardinality of the association keys.

[0088] S4.2 Determine the cardinality of the data features of the associated key based on the above, the cardinality of the data features of the associated key includes at least: a first preset cardinality and a second preset cardinality, and compare the first preset cardinality and the second preset cardinality respectively through a preset cardinality value, and distinguish the cardinality of the data features of the associated key as high cardinality or low cardinality, and then select a suitable index type based on the above distinction result.

[0089] Among them, the index type is selected only when the first preset cardinality is higher than the second preset cardinality and the preset cardinality value, or the second preset cardinality is higher than the first preset cardinality and the preset cardinality value, the associated key has a high cardinality, and the B-tree index or bitmap index is selected; when the other preset cardinality values ​​are compared with the first preset cardinality and the second preset cardinality, the associated key has a low cardinality, and the hash index is selected.

[0090] S4.3 Use commands or interfaces provided by the database management system (DBMS) to create an index according to the selected index type.

[0091] For example, the specific comparison between the predetermined base value and the first preset base value and the second preset base value is as follows:

[0092] S4.2a When the comparison shows that the first preset cardinality is greater than the second preset cardinality and the preset cardinality value, or the second preset cardinality is greater than the first preset cardinality and the preset cardinality value, at this time, the associated key has a high cardinality, and a B-tree index or a bitmap index is selected.

[0093] S4.2b When the comparison shows that the first preset cardinality is greater than the preset cardinality value and less than the second preset cardinality, and the second preset cardinality can be greater than the preset cardinality value or less than the preset cardinality value, at this time, the association key has a low cardinality and the hash index is selected.

[0094] S4.2c When the comparison shows that the first preset cardinality and the second preset cardinality are both equal to the preset cardinality value, at this time, the association key also has a low cardinality, and the hash index is selected.

[0095] Furthermore, after determining the index type, the index is created using a command or interface provided by a database management system (DBMS). For example, in a relational database, a SQL command such as CREATE INDEX index_name ON table_name (column_name) can be used to create a B-tree index.

[0096] In one embodiment, data distribution analysis includes: preliminarily reviewing the data type of the association key, whether null values ​​are allowed, and the data range; calculating basic statistical data of the association key, calculating the cardinality, and creating a histogram of the association key values; and verifying the accuracy and consistency of the data.

[0097] Specifically, first, the data type of the association key in the large table A and the large table B, whether null values ​​are allowed, and the data range are preliminarily obtained;.

[0098] Secondly, basic statistical data of the association key are calculated, and the basic statistical data at least include: the total number of records, the number of unique values, the number of null values, and the number of different values.

[0099] Furthermore, based on the number of unique values, the cardinality of the association key is calculated to analyze whether the distribution of the association key values ​​is uniform or whether there is data skew, that is, some values ​​appear frequently while other values ​​are rare.

[0100] Furthermore, the occurrence frequency of each value of the association key is counted, and a histogram of the values ​​of the association key is created based on the above statistical results to intuitively display the data distribution.

[0101] Finally, the accuracy and consistency of the above data were verified to ensure that the analysis was based on a correct and complete data set.

[0102] In one embodiment, Figure 5 and Figure 6 As shown, Figure 5 It is the distribution histogram of the association key data volume of table A - the number of association keys. Figure 6 It is the distribution histogram of the association key data volume of table B minus the number of association keys.

[0103] In addition, the number of unique values ​​of the above association key can be calculated using the COUNT(DISTINCT column_name) function in the SQL query. And the calculated number of unique values ​​is compared with the total number of records in the table according to Formula 1 to obtain the data uniqueness ratio of the association key.

[0104] (Formula 1).

[0105] In this embodiment, by establishing appropriate indexes on the association keys of large table A and large table B, the data retrieval speed of the left join operation can be significantly accelerated. Indexes allow the database system to locate and access data more quickly, thereby reducing query execution time. And based on data distribution analysis and data uniqueness ratio, the index type that best suits the data characteristics (such as B-tree, bitmap or hash index) is selected to use storage space and system resources more efficiently. This optimization reduces the maintenance cost of the index and improves query performance.

[0106] In addition, before the left join operation, data redundancy can be reduced and data processing efficiency can be improved by deduplication and data integration. This helps to simplify data management and ensure the accuracy of analysis results. At the same time, a histogram of the associated key values ​​is created to intuitively display data distribution and help database administrators and developers better understand the statistical characteristics of the data.

[0107] In addition, a terminal device is also provided in an embodiment of the present application. The internal structure of the terminal device can be as follows: Figure 7 As shown. The terminal device includes a processor, a memory, a communication interface and a database connected via a system bus. The processor is used to provide computing and control capabilities. The memory of the terminal device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the terminal device is used to store data called by the computer program. The communication interface of the terminal device is used to communicate data with an external terminal. The input device of the terminal device is used to receive a signal input by an external device. When the computer program is executed by the processor, a method for improving the computing efficiency of large sql table join as described in the above embodiment is implemented.

[0108] Those skilled in the art will understand that Figure 6 The structure shown in is merely a block diagram of a portion of the structure related to the solution of the present application, and does not constitute a limitation on the terminal device to which the solution of the present application is applied.

[0109] Those of ordinary skill in the art can understand that all or part of the processes in the method for improving the computational efficiency of joining large sql tables in the above-mentioned embodiments can be completed by instructing related hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it may include the processes of the embodiments of the method for improving the computational efficiency of joining large sql tables as described above. Among them, any reference to memory, storage, database or other media provided in this application and used in the embodiments may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (SSRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0110] It should be noted that, in this article, the terms "include", "comprises" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, device, article or method for improving the efficiency of sql table join calculations that includes a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such process, device, article or method for improving the efficiency of sql table join calculations. In the absence of more restrictions, an element defined by the sentence "includes a ..." does not exclude the existence of other identical elements in the process, device, article or method for improving the efficiency of sql table join calculations that includes the element.

[0111] The above description is only a preferred embodiment of the present application, and does not limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly used in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A method for improving the efficiency of digital reconstruction of mechatronic equipment in the Internet of Things, characterized in that: The following steps are involved: S1: Select association keys from the IoT device data table A and the IoT environment data table B, perform association operations on the two tables, obtain and remove duplicate association keys, and store the processed results in table C; S2: Automatically obtain the data volume of Table C through the data volume viewing instruction generated in the distributed file system, and check whether the data volume of Table C is less than the maximum broadcast variable memory supported by the cluster; S3: If the amount of data in table C is smaller than the maximum broadcast variable memory supported by the cluster, the data in table C is broadcast for calculation; S4: Use the broadcasted table C, large table A, and large table B to perform a left join operation, generate a dynamic partition flag based on the association key, and write them into different partitions of the result table D respectively; S5: De-duplicate the data in the A_INNER and B_INNER partitions and write the processed data into the INNER partition of table D. S6: After completing the data processing, delete the A_INNER partition and the B_INNER partition; In step S2, when the data volume of table C is larger than the maximum broadcast variable memory supported by the cluster, a data sharding strategy, an adjustment partitioning strategy, or a combination of the two is used to process the data volume of table C.

2. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 1, characterized in that: When the amount of data in table C is larger than the maximum broadcast variable memory supported by the cluster, use the data sharding strategy or adjust the partitioning strategy to handle it.

3. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 2, characterized in that: in, The method of using the data sharding strategy to process the data in table C whose data volume is larger than the maximum broadcast variable memory supported by the cluster includes the following steps: Shard the data in table C according to the hash value or range of the associated key; Define each shard as a broadcast variable and perform a left join operation using these broadcast variables; Write the result of the left join operation to the corresponding partition; Deduplication processing is performed on the data written into the partition, and the deduplication processing step includes: comparing the timestamps or unique identifiers of the records in the corresponding partition, retaining the latest records or merging field values; Merge the deduplicated data into the final result table.

4. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 2, characterized in that: in, The method of adjusting the partition strategy to process the data in table C whose data volume is larger than the maximum broadcast variable memory supported by the cluster includes the following steps: Partition the data in table C according to the range of the association key. Each partition corresponds to at least one data table space. Perform a left join operation on the data in each partition; After the left join operation is completed, the result data is deduplicated by retaining the latest record or merging field values ​​with the same association key; Merge the deduplicated data into the final result table; Use the first preset management statement to add new partitions as needed, or use the second preset management statement to delete partitions that are no longer needed; Use the third preset management statement to merge hash partitions, or use the fourth preset management statement to merge adjacent range partitions, as needed.

5. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 2, characterized in that: When the amount of data in table C exceeds the maximum broadcast variable memory of the cluster, you can directly combine the data sharding strategy and the adjustment partitioning strategy to first shard the data according to the hash value of the associated key, and then partition each shard according to the range of the associated key.

6. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 1, characterized in that: Before performing step S4, an index type suitable for the data characteristics is established on the association key of the large table A and the large table B.

7. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 6, characterized in that: The database index establishment method comprises the following steps: S4.1 Perform data distribution analysis on the association keys in the large table A and the large table B to determine the data characteristics and cardinality of the association keys; S4.2 Determine the cardinality of the data feature of the associated key according to the above, the cardinality of the data feature of the associated key includes at least: a first preset cardinality and a second preset cardinality, and compare the first preset cardinality and the second preset cardinality respectively through a preset cardinality value, distinguish the cardinality of the data feature of the associated key as high cardinality or low cardinality, and then select a suitable index type according to the above identification result; wherein, the index type is selected only when the first preset cardinality is higher than the second preset cardinality and the preset cardinality value, or the second preset cardinality is higher than the first preset cardinality and the preset cardinality value, the associated key has a high cardinality, and a B-tree index or a bitmap index is selected; when the other preset cardinality values ​​are compared with the first preset cardinality and the second preset cardinality, the associated key has a low cardinality, and a hash index is selected; S4.3 Use the commands or interfaces provided by the database management system to create an index based on the selected index type.

8. A method for improving the efficiency of digital reconstruction of Internet of Things electromechanical equipment according to claim 7, characterized in that: The data distribution analysis at least comprises the following steps: Preliminarily obtain the data type of the association key in large table A and large table B, whether null values ​​are allowed, and the data range; Calculate basic statistical data of the association key, the basic statistical data at least including: total number of records, number of unique values, number of null values, number of different values; According to the number of unique values, calculate the cardinality of the association key and analyze whether the distribution of the association key values ​​is uniform or whether there is data skew; Count the occurrence frequency of each value of the association key, and create a histogram of the values ​​of the association key based on the above statistical results; Verify data for accuracy and consistency.

9. A terminal device, characterized in that: The terminal device includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, the steps of the method for improving the calculation efficiency of SQL large table join are implemented as described in any one of claims 1 to 8.

Images