Data warehouse construction method based on electronic components

Abstract

The invention relates to the technical field of data warehouses, in particular to a data warehouse construction method based on electronic components. The method comprises the following steps: carrying out electronic component data acquisition and data preprocessing through a crawler technology to obtain a standardized data set; performing feature engineering processing on the standardized data set to obtain a feature engineering data set; performing distributed storage processing according to the feature engineering data set to obtain a distributed data set; performing metadata management on the distributed data set to obtain metadata of the distributed data set; and performing index dimension analysis on the metadata of the distributed data set to obtain a selected dimension sorting list. According to the method, the performance bottleneck caused by the data scale and complexity is broken through, the query and analysis efficiency is greatly improved, effective collection, integration and semantic processing of data of different sources and different formats are achieved, and the processing capacity of a data warehouse for diversified data is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouses, and in particular to a method for constructing a data warehouse based on electronic components. Background Art

[0002] The electronic components industry is developing rapidly, generating massive amounts of data, including component specifications, prices, inventory, supplier information, etc. In order to better utilize this data for market analysis, supply chain management, and product design, electronic component data warehouses came into being. Early data warehouse construction methods mainly relied on relational databases and stored data in a structured manner. However, with the explosive growth of data volume and the increase in data dimensions, traditional relational databases have the disadvantages of low query and analysis efficiency and difficulty in coping with data diversity when processing massive, high-dimensional electronic component data.

[0003] Manifestations of low query and analysis efficiency: Relational databases are inefficient when processing complex queries, multi-table associations, and aggregation operations, and are unable to meet the real-time and flexibility requirements of electronic component data analysis.

[0004] Difficulty in coping with data diversity: Electronic component data comes from a wide range of sources and in various formats. Traditional methods find it difficult to effectively process and integrate these heterogeneous data. Summary of the invention

[0005] Based on this, it is necessary to provide a method for constructing a data warehouse based on electronic components to solve at least one of the above technical problems.

[0006] To achieve the above purpose, a method for constructing a data warehouse based on electronic components includes the following steps:

[0007] Step S1: Collect electronic component data through crawler technology and perform data preprocessing to obtain a standardized data set; perform feature engineering processing on the standardized data set to obtain a feature engineering data set;

[0008] Step S2: Perform distributed storage processing on the feature engineering data set to obtain a distributed data set; perform metadata management on the distributed data set to obtain distributed data set metadata;

[0009] Step S3: performing index dimension analysis on the metadata of the distributed data set to obtain a sorted list of selected dimensions; performing index data distribution processing according to the sorted list of selected dimensions to obtain distributed index data; performing index metadata management on the distributed index data to obtain index metadata;

[0010] Step S4: converting the data format of the distributed data set metadata according to the distributed index data and constructing a column storage database to obtain a column storage database; optimizing the index according to the column storage database to obtain a column storage index optimization database;

[0011] Step S5: perform query parsing and optimization according to the column storage index optimization database to obtain a logical query optimization plan; perform parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set; perform result caching and pre-fetching according to the query result set to obtain cached query result data.

[0012] The present invention collects data from multiple electronic component distributor websites through web crawler technology, ensuring the richness and real-time nature of data sources. The data preprocessing process improves the quality of data through operations such as cleaning and standardization, providing a reliable data basis for subsequent analysis. Feature engineering extracts key features according to specific business needs, reduces data dimensions, and improves the efficiency and accuracy of subsequent model training. According to the characteristics of the data, a suitable distributed storage solution is selected, and the data is stored in pieces, making full use of the scalability and fault tolerance of the distributed system, and improving the reliability and scalability of data storage. Metadata management facilitates the positioning and access of data by users and applications, and improves data management efficiency. By analyzing the data query mode, selecting the appropriate index dimension and index structure, and using the distributed computing framework for construction, the query performance of the data warehouse is effectively improved, and efficient data access services are provided to users. Column storage technology is adopted, and data storage structure, compression algorithm, data block size, index and other aspects are optimized, which effectively improves the storage efficiency and query performance of the data warehouse, and provides users with efficient and stable data services. Select a suitable query engine, and by optimizing query plans, executing queries in parallel, caching query results and other means, the query efficiency is improved to the greatest extent, providing users with fast and stable data query services. At the same time, by collecting user query logs, training query behavior prediction models, predicting query results, pre-fetching data and other means, the user's query needs are effectively predicted, and the data required by the user is loaded into the cache in advance, thereby further improving the query efficiency and optimizing the user experience. Therefore, the present invention provides a data warehouse construction method based on electronic components, which effectively solves the shortcomings of traditional electronic component data warehouses in query and analysis efficiency and data diversity. On the one hand, by using distributed storage, multidimensional indexing, column storage, query optimization and parallel execution and other technical means, the performance bottleneck caused by data scale and complexity is broken through, and the query and analysis efficiency is greatly improved. On the other hand, by means of crawler technology, data preprocessing, metadata management and knowledge graph construction, the effective collection, integration and semantic processing of data from different sources and formats are realized, and the data warehouse's processing ability for diversified data is improved.

[0013] Preferably, step S1 comprises the following steps:

[0014] Step S11: Collect electronic component data by crawler technology to obtain an original data set;

[0015] Step S12: performing data cleaning on the original data set to obtain a cleaned data set; performing data standardization on the cleaned data set to obtain a standardized data set;

[0016] Step S13: Acquire business demand data; perform feature extraction on the standardized data set according to the business demand data to obtain a feature extraction data set;

[0017] Step S14: performing feature selection on the feature extraction data set to obtain a feature engineering data set.

[0018] The present invention collects data from multiple electronic component distributor websites through crawler technology, and can obtain rich and comprehensive electronic component information, providing sufficient data basis for subsequent data analysis and application, while avoiding the tediousness and inefficiency of manually collecting data. Data cleaning can identify and correct erroneous data in the original data set, such as duplicate data, missing data, format error data, etc., to improve data quality; data standardization unifies data from different sources and formats to the same specification, eliminates data differences, ensures data consistency and comparability, and provides high-quality and high-value data for subsequent data analysis and mining. According to specific business needs, such as price trend analysis, market demand forecasting, etc., relevant features are extracted from standardized data sets in a targeted manner, which can avoid the interference of redundant information, improve data processing efficiency, and lay the foundation for the subsequent construction of accurate and efficient data analysis models. Feature selection can screen out features that have a greater impact on the target variable and a higher contribution, eliminate redundant features and irrelevant features, simplify data dimensions, reduce computational complexity, improve model training efficiency, and reduce the risk of model overfitting, ultimately improving the accuracy and generalization ability of the data analysis model.

[0019] Preferably, step S2 comprises the following steps:

[0020] Step S21: Select a storage solution based on the feature engineering data set to obtain a distributed storage solution;

[0021] Step S22: Slice the feature engineering data set according to data rules to obtain a data slicing set;

[0022] Step S23: performing slice data format conversion on the data slice set to obtain a data slice conversion set;

[0023] Step S24: Distributed storage is performed on the data shard conversion set according to the distributed storage solution to obtain a distributed data set;

[0024] Step S25: Perform metadata management on the distributed data set to obtain the distributed data set metadata.

[0025] The present invention can give full play to the advantages of different storage systems and improve data storage efficiency and query performance by selecting a suitable distributed storage solution according to the characteristics of feature engineering data sets, such as selecting HBase to store massive sparse data. The feature engineering data set is fragmented according to predefined rules, such as fragmented according to product models, so that data can be evenly distributed to different nodes, avoiding data skew and improving the parallelism of data storage and query. The data fragments are converted into a data format supported by the target storage system, such as converted into an HBase table structure, which can ensure that the data can be correctly identified and processed by the target storage system, thereby improving data loading efficiency. The data fragments are stored in a distributed storage system, such as in an HBase cluster, which can fully utilize the scalability and fault tolerance of the distributed system and improve the reliability and scalability of data storage. The metadata of the distributed data set is managed, such as recording the name, storage path, size, creation time and other information of the data fragment, which can facilitate users and applications to quickly locate and access data and improve data management efficiency.

[0026] Preferably, step S25 comprises the following steps:

[0027] Step S251: extracting metadata from the distributed data set to obtain original metadata; performing metadata unified processing on the original metadata to obtain unified metadata;

[0028] Step S252: extracting and fusing knowledge from the unified metadata using natural language technology to obtain domain knowledge fusion data;

[0029] Step S253: construct a knowledge graph for the domain knowledge fusion data to obtain an electronic component knowledge graph; extract data lineage information from the distributed data set to obtain data lineage information;

[0030] Step S254: performing data lineage association on the data lineage information and the electronic component knowledge graph to obtain an electronic component knowledge graph with lineage information;

[0031] Step S255: Store graph data of the electronic component knowledge graph with lineage information to obtain distributed data set metadata.

[0032] The present invention can eliminate the differences between data sources, improve the consistency and comprehensibility of metadata, and lay the foundation for subsequent knowledge extraction and knowledge graph construction by extracting original metadata from distributed data sets and performing unified processing, such as format unification and semantic disambiguation. By using natural language technology to deeply analyze and understand metadata, extract key information, such as entities, attributes, relationships, etc., and perform semantic fusion, metadata can be converted into a machine-understandable knowledge representation form, providing high-quality materials for knowledge graph construction. By constructing an electronic component knowledge graph, concepts such as electronic components, manufacturers, and packaging types and the relationships between them are organized and managed in the form of a graph, users can be provided with intuitive and comprehensive domain knowledge, and applications such as knowledge query and reasoning can be supported. At the same time, data lineage information is extracted, and information such as the source and processing flow of the data is recorded, which can help users understand the source and quality of the data and improve the credibility of the data. Data lineage information is associated with the electronic component knowledge graph, and data is linked to knowledge, which can help users better understand the business meaning behind the data, and support applications such as data tracing and impact analysis, and improve the efficiency and accuracy of data analysis and decision-making. Storing the knowledge graph of electronic components with lineage information in a graph database can fully utilize the efficient graph query and analysis capabilities of the graph database, support complex relationship queries and knowledge reasoning, and provide users with more intelligent data services.

[0033] Preferably, step S31 includes the following steps:

[0034] Step S31: perform index dimension analysis on the distributed data set metadata to obtain a sorted list of selected dimensions;

[0035] Step S32: performing multi-dimensional selection of index structures according to the dimension factor data in the selected dimension sorting list to obtain a collection of candidate index structures;

[0036] Step S33: evaluating and selecting the index structure for the candidate index structure collection to obtain a selected index structure list;

[0037] Step S34: using the distributed computing framework to construct a distributed index for the distributed data set metadata according to the selected dimension sorting list and the selected index structure list, to obtain a distributed index structure;

[0038] Step S35: Distribute and store index data according to the distributed index structure to obtain distributed index data; perform index metadata management on the distributed index data to obtain index metadata.

[0039] The present invention determines the main query dimensions by analyzing the metadata and query mode of the distributed data set, and sorts them according to their importance to obtain a sorted list of selected dimensions, which can provide a clear direction and basis for subsequent index construction, and ensure that the constructed index can effectively accelerate high-frequency queries. For different query dimensions and data types, select a suitable index structure, such as selecting a hash index for an exact match query, selecting a B-tree index for a range query, and selecting an inverted index for a fuzzy query, which can give full play to the advantages of different index structures and improve query efficiency. By performing performance testing and evaluation on the candidate index structure, selecting the index structure combination with the best comprehensive performance, it is possible to achieve a good balance in terms of query performance, storage space occupancy, index construction time, etc., avoid building too many or too large indexes, and reduce the system burden. Using a distributed computing framework to build an index, such as using Spark to group, sort and index data, cluster resources can be fully utilized, index construction efficiency can be improved, and index construction time can be shortened. Distributed index data is stored in a distributed file system, such as HDFS, and index metadata is managed, such as recording information such as index file storage path, size, creation time, etc., which can facilitate users and applications to quickly locate and access index data and improve index management efficiency.

[0040] Preferably, step S31 includes the following steps:

[0041] Step S311: performing query pattern analysis on the distributed data set metadata to obtain query pattern analysis data;

[0042] Step S312: Perform preliminary analysis of query dimensions based on query pattern analysis data to obtain a set of candidate dimensions;

[0043] Step S313: performing dimension cardinality statistics on the candidate dimension set to obtain a dimension cardinality statistics table;

[0044] Step S314: Dimension importance evaluation is performed based on the query pattern analysis data and the dimension cardinality statistics table to obtain a dimension importance ranking table;

[0045] Step S315: select and sort the candidate dimension set according to the dimension importance ranking table to obtain a selected dimension ranking list.

[0046] The present invention analyzes the user query log, extracts the fields, filter conditions, sorting methods and other information involved in the query statement, and counts the frequency of occurrence, value distribution and other characteristics to form query pattern analysis data, which can provide data support for subsequent dimension analysis and ensure that the selected index dimension can effectively accelerate the actual query operation. According to the frequency of occurrence of each field in the query pattern analysis data, the high-frequency query field is screened out as the candidate dimension, and the field with a greater impact on the query performance can be preliminarily determined, providing a candidate set for the subsequent dimension cardinality statistics and importance evaluation. The number of different values ​​of each dimension in the candidate dimension set, that is, the dimension cardinality, is counted, which can provide a reference indicator for the subsequent dimension importance evaluation. Generally, the larger the dimension cardinality, the more dispersed the data distribution of the dimension is, and the worse the effect of indexing using the dimension can be. Comprehensively considering the query frequency and dimension cardinality, the candidate dimension is evaluated for importance. For example, the query frequency multiplied by the logarithm of the dimension cardinality is used as the importance score, which can more accurately evaluate the impact of each dimension on the query performance, and avoid only considering the query frequency, resulting in the selection of dimensions with too large a dimension cardinality and low indexing efficiency. According to the dimension importance ranking table, the top N dimensions with the highest scores are selected as the final index dimensions, and they are sorted according to the scores. This ensures that the selected index dimensions can maximize query performance while avoiding the extra overhead caused by building too many indexes.

[0047] Preferably, step S4 comprises the following steps:

[0048] Step S41: performing column storage scheme evaluation on the distributed index data and index metadata to obtain column storage scheme evaluation data;

[0049] Step S42: selecting a column storage engine according to the column storage solution evaluation data to obtain a selected column storage engine; optimizing a data table design according to the column storage solution evaluation data to obtain data table optimized structure data;

[0050] Step S43: adjusting the data sharding strategy according to the selected column storage engine and the data table optimization structure data to obtain a data sharding adjustment strategy;

[0051] Step S44: converting the data format of the distributed data set metadata according to the data sharding adjustment strategy to obtain column storage format data; loading the column storage format data into the selected column storage engine to construct a column storage database to obtain a column storage database;

[0052] Step S45: performing dictionary encoding of small cardinality columns on the column storage database to obtain a dictionary encoded data set; performing run-length encoding of multiple repeated value columns on the column storage database to obtain a run-length encoded data set;

[0053] Step S46: adjusting the data block size of the dictionary encoding data set and the run-length encoding data set to obtain data block size optimized data;

[0054] Step S47: performing index optimization according to the column storage database, the data block size optimization data, and the index metadata to obtain a column storage index optimization database.

[0055] The present invention evaluates different column storage schemes, such as comparing the performance of Parquet and ORC formats in terms of compression rate, query performance, write performance, etc., and can select the scheme that best meets the data characteristics and performance requirements, laying the foundation for the subsequent construction of an efficient column storage database. According to the evaluation results, a suitable column storage engine is selected, and the data table is designed and optimized according to the data characteristics and query mode, such as storing high-frequency query fields and low-frequency query fields in different column families, which can give full play to the advantages of column storage and improve data compression rate and query efficiency. According to the selected column storage engine and data table design scheme, the data sharding strategy is adjusted, such as sharding according to product models, which can further optimize the data storage structure, improve data locality, and reduce the amount of data read. The distributed data set metadata is converted into column storage format data, and the data is loaded into the selected column storage engine to complete the construction of the column storage database, which can effectively organize and manage massive data and provide high-performance data storage services for subsequent queries and analysis. Dictionary encoding is performed on small cardinality columns, and run length encoding is performed on multiple repeated value columns, which can further compress data storage space, reduce storage costs, and improve data reading efficiency. Adjusting the data block size according to the data query mode and hardware environment can achieve a balance between data reading efficiency and memory usage, thereby improving query performance. Optimizing data and index metadata according to data distribution and data block size, and optimizing indexes, such as adjusting index granularity and index cache size, can further improve index query efficiency and accelerate data access.

[0056] Preferably, step S5 comprises the following steps:

[0057] Step S51: selecting a query engine according to the column storage index optimization database to obtain a selected query engine; deploying the query engine of the selected query engine in the data warehouse cluster to obtain a deployed query engine;

[0058] Step S52: using the deployed query engine to parse the query statement to obtain an abstract syntax tree; performing semantic analysis and verification on the abstract syntax tree to obtain semantic verification data; performing logical query plan conversion on the abstract syntax tree according to the semantic verification data to obtain a logical query plan;

[0059] Step S53: acquiring metadata from the column storage index optimization database to obtain metadata; optimizing the logic query plan using predefined optimization rules to obtain a logic query optimization plan;

[0060] Step S54: performing parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set;

[0061] Step S55: caching and pre-fetching results according to the query result set to obtain cached query result data.

[0062] The present invention can give full play to the performance advantages of the query engine and improve query efficiency by selecting a suitable query engine according to the characteristics of the column storage database, such as selecting Presto for interactive query analysis, and deploying it to the data warehouse cluster. The query engine is used to parse the SQL query statement submitted by the user, generate an abstract syntax tree, and perform semantic analysis and verification, which can ensure the grammatical correctness and semantic legitimacy of the query statement and avoid executing incorrect queries. According to the metadata of the column storage database, such as data table structure information, statistical information, index information, etc., the logical query plan is optimized, such as predicate pushdown, column pruning, connection order optimization, etc., which can reduce the amount of data read, reduce the calculation complexity, and improve query efficiency. According to the metadata information, the logical query optimization plan is converted into a physical query plan that can be executed in parallel, and the query task is decomposed into multiple subtasks, which are executed in parallel, which can make full use of cluster resources and improve query speed. The query result set is cached in memory or distributed cache, and the data queried by the user is predicted according to the user's query history and data access mode, and the data is loaded into the cache in advance, which can reduce the number of disk I / O times, improve the query response speed, and optimize the user experience.

[0063] Preferably, step S54 includes the following steps:

[0064] Step S541: performing cost analysis of different physical query plans on the logical query optimization plan according to the metadata to obtain a candidate physical query plan;

[0065] Step S542: select a query plan for the candidate physical query plan to obtain a query execution plan; decompose the query execution plan into query tasks to obtain parallel execution subtasks;

[0066] Step S543: obtaining cluster resource status data; scheduling subtasks of different nodes for parallel execution subtasks according to the cluster resource status data to obtain a query task list;

[0067] Step S544: performing node query task execution processing according to the query task list to obtain node query task execution data;

[0068] Step S545: Transmit the node query task execution data to the preset master node, generate a query result set, and obtain a query result set.

[0069] The present invention analyzes the logical query optimization plan according to metadata information, such as data table statistics, data distribution information, index information, etc., and generates multiple candidate physical query plans, such as plans based on different connection modes, and uses a cost model to estimate the execution cost of each plan, such as CPU usage time, memory occupancy, network transmission volume, etc., which can provide a basis for selecting the optimal execution plan. According to the cost analysis result, the candidate physical query plan with the smallest cost is selected as the final query execution plan, and it is decomposed into multiple subtasks that can be executed in parallel, such as decomposing operations such as data reading, filtering, connecting, and aggregation into different subtasks, which can minimize the query execution time and improve query efficiency. The resource usage of each node in the cluster, such as the remaining CPU amount, the remaining memory amount, the network bandwidth, etc., is obtained, and the subtasks are scheduled to different computing nodes for execution according to the cluster resource status and the resource requirements of the subtasks, such as scheduling the subtasks that require a large amount of CPU resources to the nodes with more remaining CPU amount for execution, which can make full use of cluster resources, avoid resource competition and waste, and improve query parallelism. Each computing node executes the corresponding subtask according to the received query task list, and stores the processed data in the local disk or memory, which can distribute the query tasks to multiple nodes for parallel execution to speed up the query. All computing nodes transmit the processed data to the preset master node, which summarizes the results of all nodes, performs the final processing, and generates the final query result set, which can summarize the calculation results of each node to obtain a complete query result.

[0070] Preferably, step S55 comprises the following steps:

[0071] Step S551: collecting user query logs according to the query result set to obtain user query logs; extracting query features from the user query logs to obtain query feature data;

[0072] Step S552: using a machine learning algorithm to train a query behavior prediction model based on the query feature data to obtain a query behavior prediction model;

[0073] Step S553: ​​obtaining real-time user query request data; using the query behavior prediction model to predict query results for the real-time user query request data to obtain query result prediction data;

[0074] Step S554: If the query result prediction data is not cached or the cache has expired, data pre-fetching is performed on the column storage index optimization database and loaded into the memory; if the query result prediction data has been cached, the query result is returned to obtain the cached query result data.

[0075] The present invention can provide a data basis for subsequent training of query behavior prediction models by collecting user query logs and extracting query features therefrom, such as table names, field names, filter conditions, sorting methods, etc. involved in the query. A model that can predict user query behavior is trained based on historical user query feature data and query result data using machine learning algorithms, such as logistic regression, support vector machine, neural network, etc., and can predict the data range of user queries based on user query requests, providing guidance for data pre-fetching. Using the trained query behavior prediction model, real-time user query requests are predicted to obtain query result prediction data, such as predicting which product models, which manufacturers' electronic component data, and price ranges that users will query, etc., which can predict the user's query intention in advance and provide a more accurate target for data pre-fetching. According to the query result prediction data, relevant data is read from the column storage database in advance and loaded into the memory cache, or data is returned directly from the cache, which can reduce the number of disk I / Os, shorten the query response time, and improve the user experience.

[0076] The present invention collects data from multiple electronic component distributor websites through web crawler technology, ensuring the richness and real-time nature of data sources. The data preprocessing process improves the quality of data through operations such as cleaning and standardization, providing a reliable data basis for subsequent analysis. Feature engineering extracts key features according to specific business needs, reduces data dimensions, and improves the efficiency and accuracy of subsequent model training. According to the characteristics of the data, a suitable distributed storage solution is selected, and the data is stored in pieces, making full use of the scalability and fault tolerance of the distributed system, and improving the reliability and scalability of data storage. Metadata management facilitates the positioning and access of data by users and applications, and improves data management efficiency. By analyzing the data query mode, selecting the appropriate index dimension and index structure, and using the distributed computing framework for construction, the query performance of the data warehouse is effectively improved, and efficient data access services are provided to users. Column storage technology is adopted, and data storage structure, compression algorithm, data block size, index and other aspects are optimized, which effectively improves the storage efficiency and query performance of the data warehouse, and provides users with efficient and stable data services. Select a suitable query engine, and by optimizing query plans, executing queries in parallel, caching query results and other means, the query efficiency is improved to the greatest extent, providing users with fast and stable data query services. At the same time, by collecting user query logs, training query behavior prediction models, predicting query results, pre-fetching data and other means, the user's query needs are effectively predicted, and the data required by the user is loaded into the cache in advance, thereby further improving the query efficiency and optimizing the user experience. Therefore, the present invention provides a data warehouse construction method based on electronic components, which effectively solves the shortcomings of traditional electronic component data warehouses in query and analysis efficiency and data diversity. On the one hand, by using distributed storage, multidimensional indexing, column storage, query optimization and parallel execution and other technical means, the performance bottleneck caused by data scale and complexity is broken through, and the query and analysis efficiency is greatly improved. On the other hand, by means of crawler technology, data preprocessing, metadata management and knowledge graph construction, the effective collection, integration and semantic processing of data from different sources and formats are realized, and the data warehouse's processing ability for diversified data is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0077] Figure 1 A flowchart of the steps of a method for building a data warehouse based on electronic components;

[0078] Figure 2 for Figure 1 Detailed implementation steps of step S3 in FIG.

[0079] Figure 3 for Figure 1 Detailed implementation steps of step S5 are shown in the flowchart.

[0080] The purpose, features and advantages of the present invention will be further described with reference to the accompanying drawings in conjunction with the embodiments. DETAILED DESCRIPTION

[0081] The technical method of the present invention is described clearly and completely below in conjunction with the accompanying drawings. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by technicians in this field without creative work are within the scope of protection of the present invention.

[0082] In addition, the accompanying drawings are only schematic illustrations of the present invention and are not necessarily drawn to scale. The same reference numerals in the figures represent the same or similar parts, and their repeated description will be omitted. Some of the block diagrams shown in the accompanying drawings are functional entities and do not necessarily correspond to physically or logically independent entities. The functional entities can be implemented in software form, or implemented in one or more hardware modules or integrated circuits, or implemented in different networks and / or processor methods and / or microcontroller methods.

[0083] It should be understood that, although the terms "first", "second", etc. may be used herein to describe various units, these units should not be limited by these terms. These terms are used only to distinguish one unit from another unit. For example, without departing from the scope of the exemplary embodiments, the first unit may be referred to as the second unit, and similarly the second unit may be referred to as the first unit. The term "and / or" used herein includes any and all combinations of one or more of the listed associated items.

[0084] To achieve this, please refer to Figures 1 to 3 , a method for constructing a data warehouse based on electronic components, comprising the following steps:

[0085] Step S1: Collect electronic component data through crawler technology and perform data preprocessing to obtain a standardized data set; perform feature engineering processing on the standardized data set to obtain a feature engineering data set;

[0086] Step S2: Perform distributed storage processing on the feature engineering data set to obtain a distributed data set; perform metadata management on the distributed data set to obtain distributed data set metadata;

[0087] Step S3: performing index dimension analysis on the metadata of the distributed data set to obtain a sorted list of selected dimensions; performing index data distribution processing according to the sorted list of selected dimensions to obtain distributed index data; performing index metadata management on the distributed index data to obtain index metadata;

[0088] Step S4: converting the data format of the distributed data set metadata according to the distributed index data and constructing a column storage database to obtain a column storage database; optimizing the index according to the column storage database to obtain a column storage index optimization database;

[0089] Step S5: perform query parsing and optimization according to the column storage index optimization database to obtain a logical query optimization plan; perform parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set; perform result caching and pre-fetching according to the query result set to obtain cached query result data.

[0090] In the embodiment of the present invention, reference Figure 1 The above is a schematic flow chart of the steps of the method for constructing a data warehouse based on electronic components of the present invention. In this example, the method for constructing a data warehouse based on electronic components includes the following steps:

[0091] Step S1: Collect electronic component data through crawler technology and perform data preprocessing to obtain a standardized data set; perform feature engineering processing on the standardized data set to obtain a feature engineering data set;

[0092] In an embodiment of the present invention, a web crawler is first written using the Python Scrapy framework to collect information such as product models, manufacturers, package types, operating temperatures, prices, etc. from electronic component distributor websites such as Digikey, Mouser, and ICnet, and saved as an original data set in JSON format. The original data set is then cleaned using the Pandas library, including deleting duplicate data, filtering erroneous data, filling missing data, etc. Next, the product models are uniformly converted to uppercase letters, and the values ​​of fields such as package types and operating temperatures are mapped to a predefined standard code table, and the currency units of the price fields are uniformly converted to U.S. dollars to obtain a standardized data set. Finally, according to the analysis requirements confirmed by communication with the business department, such as price trend analysis, market demand forecasting, etc., relevant features such as product models, manufacturers, dates, prices, historical sales, etc. are extracted from the standardized data set to form a feature engineering data set.

[0093] Step S2: Perform distributed storage processing on the feature engineering data set to obtain a distributed data set; perform metadata management on the distributed data set to obtain distributed data set metadata;

[0094] In the embodiment of the present invention, after evaluating the data scale, data type, query mode and other factors of the feature engineering data set, HBase is selected as the distributed storage solution. The product models are sorted in alphabetical order, and 10,000 consecutive product models are divided into the same data shard. Each data shard is converted into an HBase table structure, with the product model as the row key, the feature name as the column name, and the feature value as the column value, and each HBase table is stored in the HBase cluster. Finally, a metadata table is constructed to record the data shard name, storage path, size, creation time and other information, and stored in the MySQL database for management and maintenance of distributed data sets.

[0095] Step S3: performing index dimension analysis on the metadata of the distributed data set to obtain a sorted list of selected dimensions; performing index data distribution processing according to the sorted list of selected dimensions to obtain distributed index data; performing index metadata management on the distributed index data to obtain index metadata;

[0096] In an embodiment of the present invention, common query patterns of electronic component data warehouses are analyzed to determine the main query dimensions, such as product model, manufacturer, package type, operating temperature, etc., and the query frequency and data distribution are sorted in order of importance to obtain a sorted list of selected dimensions. For each dimension, a suitable index structure is selected, such as a hash index, an inverted index, a bitmap index, etc., and the distributed data set metadata is grouped, sorted, and indexed using the Spark distributed computing framework to ultimately obtain a distributed storage index structure. Finally, the constructed distributed index structure is stored in the HDFS distributed file system, and the index file storage path, size, creation time, and other information are recorded as index metadata and stored in the Zookeeper distributed coordination service.

[0097] Step S4: converting the data format of the distributed data set metadata according to the distributed index data and constructing a column storage database to obtain a column storage database; optimizing the index according to the column storage database to obtain a column storage index optimization database;

[0098] In the embodiment of the present invention, the performance of two column storage formats, Apache Parquet and Apache ORC, in terms of data compression rate, query performance, data writing performance, etc., is evaluated, and Apache Parquet is finally selected as the column storage engine. According to the characteristics of electronic component data, the data table is designed and optimized, for example, high-frequency query fields and low-frequency query fields are stored in different column families. According to the data storage characteristics of Apache Parquet, the data is sharded according to the product model, each shard contains all the data of the corresponding product model, and is stored as a Parquet file. Spark is used to read the data in HBase, group them according to the product model, and convert the data of each group into Parquet format and store them in HDFS. Then, Spark SQL is used to create an external table, map the Parquet file on HDFS to a data table, and build a column storage database. Dictionary encoding is performed on the small cardinality columns in the column storage database, run length encoding is performed on the columns with a large number of repeated values, and the data block size is adjusted according to the data query mode and hardware environment. Finally, the index is optimized according to the data distribution, data block size optimization data and index metadata.

[0099] Step S5: performing query parsing and optimization according to the column storage index optimization database to obtain a logical query optimization plan; performing parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set; performing result caching and pre-fetching according to the query result set to obtain cached query result data;

[0100] In the embodiment of the present invention, Presto is selected as the query engine, and it is deployed on each computing node of the data warehouse cluster, and Presto is configured to connect to the column storage database. When the user submits an SQL query statement, Presto performs lexical analysis, grammatical analysis and semantic analysis on it, and converts it into a logical query plan. Then, Presto obtains the structural information, statistical information, index information, etc. of the data table from the metadata service of the column storage database, and optimizes the logical query plan using predefined optimization rules to generate a logical query optimization plan. According to metadata information, such as data sharding information, index information, etc., the logical query optimization plan is converted into a physical query plan that can be executed in parallel, and the query task is decomposed into multiple subtasks that can be executed in parallel. The Presto scheduler schedules the subtasks to different computing nodes for execution according to the cluster resource status and the resource requirements of the subtasks. Each computing node executes the corresponding subtask and stores the processed data in the local disk or memory. All computing nodes transmit the processed data to the preset master node, and the master node summarizes the results of all nodes, performs final processing, and generates the final query result set. Finally, the query result set is cached in memory or distributed cache, and the cache expiration time is recorded.

[0101] Preferably, step S1 comprises the following steps:

[0102] Step S11: Collect electronic component data by crawler technology to obtain an original data set;

[0103] Step S12: performing data cleaning on the original data set to obtain a cleaned data set; performing data standardization on the cleaned data set to obtain a standardized data set;

[0104] Step S13: Acquire business demand data; perform feature extraction on the standardized data set according to the business demand data to obtain a feature extraction data set;

[0105] Step S14: performing feature selection on the feature extraction data set to obtain a feature engineering data set.

[0106] In an embodiment of the present invention, a web crawler program is written in Python language, and the Scrapy framework is used to crawl web pages. The target website is set as a website of a well-known electronic component distributor such as Digikey, Mouser, and ICnet. The crawler program simulates a user visiting a website, and uses XPath or CSS selectors to locate and extract the electronic component information contained in the web page, such as product model, manufacturer, package type, operating temperature, price, inventory quantity, etc. In order to avoid excessive burden on the target website, the crawler access frequency is set to no more than 5 times per second, and the robots.txt protocol is followed. Finally, the acquired data is stored in JSON format to form an original data set. The Pandas library is used to clean the original data set. First, duplicate data records are deleted, and obviously erroneous data is filtered out according to preset rules, such as: the product model is empty, the price is negative, etc. Secondly, different filling strategies are adopted for missing data: for numerical data, the average or median is used for filling; for text data, an empty string or a specific tag is used for filling. After data cleaning, data standardization is performed: product models are uniformly converted to uppercase letters, values ​​of fields such as package type and operating temperature are mapped to predefined standard code tables, and currency units of price fields are uniformly converted to US dollars. Finally, a cleaned and standardized data set is obtained. Through communication with the business department, data analysis requirements are collected and sorted, such as: analyzing the price trends of electronic components from different manufacturers, predicting the market demand for specific models of electronic components in the next month, etc. According to specific business needs, relevant features are extracted from the standardized data set. For example, for price trend analysis, the extracted features include: product model, manufacturer, date, price; for market demand forecasting, the extracted features include: product model, historical sales, market trend index, etc. The extracted feature data is stored in a new data set to form a feature extraction data set. The chi-square test method is used to rank the features in the feature extraction data set by importance. The chi-square test can evaluate the correlation between features and the target variable and select features with higher correlation with the target variable. For example, for price trend analysis, the target variable is price, and features with higher correlation with price are selected, such as: product model, manufacturer, date. Features with lower correlation are eliminated, such as: product description, technical specifications, etc. The features finally retained constitute the feature engineering dataset for the subsequent construction of the data warehouse.

[0107] Preferably, step S2 comprises the following steps:

[0108] Step S21: Select a storage solution based on the feature engineering data set to obtain a distributed storage solution;

[0109] Step S22: Slice the feature engineering data set according to data rules to obtain a data slicing set;

[0110] Step S23: performing slice data format conversion on the data slice set to obtain a data slice conversion set;

[0111] Step S24: Distributed storage is performed on the data shard conversion set according to the distributed storage solution to obtain a distributed data set;

[0112] Step S25: Perform metadata management on the distributed data set to obtain the distributed data set metadata.

[0113] In an embodiment of the present invention, factors such as the data scale, data type, and query mode of the feature engineering data set are evaluated. Considering that the amount of electronic component data is large and it is necessary to support high-concurrency queries, HBase is selected as a distributed storage solution. Data is sharded according to the product model field in the feature engineering data set. The product models are sorted in alphabetical order, and 10,000 consecutive product models are divided into the same data shard. For example, product models starting with the letter A are divided into the first shard, those starting with the letter B are divided into the second shard, and so on. Each data shard contains all feature data of the corresponding product model. Convert each data shard into an HBase table structure. Use the product model as the row key, the feature name as the column name, and the feature value as the column value. For example, the data of the product model "SN74LS00N" has a row key of "SN74LS00N", contains columns such as "manufacturer", "package type", and "operating temperature", and the corresponding column values ​​are "TI", "SOIC-14", and "-55℃~125℃". Store each HBase table in an HBase cluster. The HBase cluster contains multiple nodes, each of which is responsible for storing a portion of the data. Data shards are distributed to different nodes according to the preset hash algorithm to achieve distributed data storage. A metadata table is constructed to record the metadata information of the distributed data set. The metadata table contains the following fields: data shard name, data shard storage path, data shard size, data shard creation time, data shard update time, etc. The metadata table is stored in the MySQL database and is used to manage and maintain distributed data sets.

[0114] Preferably, step S25 comprises the following steps:

[0115] Step S251: extracting metadata from the distributed data set to obtain original metadata; performing metadata unified processing on the original metadata to obtain unified metadata;

[0116] Step S252: extracting and fusing knowledge from the unified metadata using natural language technology to obtain domain knowledge fusion data;

[0117] Step S253: construct a knowledge graph for the domain knowledge fusion data to obtain an electronic component knowledge graph; extract data lineage information from the distributed data set to obtain data lineage information;

[0118] Step S254: performing data lineage association on the data lineage information and the electronic component knowledge graph to obtain an electronic component knowledge graph with lineage information;

[0119] Step S255: Store graph data of the electronic component knowledge graph with lineage information to obtain distributed data set metadata.

[0120] In an embodiment of the present invention, the HBase API is used to read the table structure information of each HBase table in the distributed data set, including the table name, column family name, column name, etc., as well as the storage path, size, creation time, etc. of each data shard, to form the original metadata. Regular expressions are used to unify the format of the table name, column name, etc. in the original metadata, for example, all characters are converted to lowercase, and spaces are replaced with underscores. At the same time, metadata items with the same meaning from different data sources are merged, for example, "creation time" and "data storage time" are merged into a unified "creation time" metadata item, and unified metadata is finally obtained. The Stanford CoreNLP tool is used to perform natural language processing on the text information in the unified metadata, including word segmentation, part-of-speech tagging, named entity recognition, etc. For example, "product model" is identified as the named entity type "electronic components", "operating temperature" is identified as the attribute name, and "-55℃~125℃" is identified as the attribute value. Then, semantic dictionaries such as WordNet are used to perform semantic disambiguation and concept aggregation on the identified entities, attributes, and attribute values. For example, "TI" and "Texas Instruments" are identified as the same manufacturer entity. Finally, the extracted and fused knowledge is stored in the form of RDF triples to form domain knowledge fusion data. Neo4j graph database is used to store and manage domain knowledge fusion data to construct the electronic component knowledge graph. Concepts such as electronic components, manufacturers, package types, and operating temperatures are used as nodes in the graph, and the relationships between concepts are used as edges in the graph. For example, there is a "production" relationship between "product model" and "manufacturer", and there is a "property" relationship between "product model" and "operating temperature". At the same time, the source information of each data shard in the distributed data set is recorded, such as the data source website, data acquisition time, data processing flow, etc., to form data lineage information. The data lineage information is stored in JSON format. The data lineage information is parsed to establish an association relationship between the data shard and the corresponding node in the electronic component knowledge graph. For example, if a data shard contains data for a product model of "SN74LS00N", an association relationship is established between the data shard and the node representing "SN74LS00N" in the knowledge graph. The electronic component knowledge graph with lineage information is stored in the Neo4j graph database as metadata for the distributed dataset.

[0121] Preferably, step S31 includes the following steps:

[0122] Step S31: perform index dimension analysis on the distributed data set metadata to obtain a sorted list of selected dimensions;

[0123] Step S32: performing multi-dimensional selection of index structures according to the dimension factor data in the selected dimension sorting list to obtain a collection of candidate index structures;

[0124] Step S33: evaluating and selecting the index structure for the candidate index structure collection to obtain a selected index structure list;

[0125] Step S34: using the distributed computing framework to construct a distributed index for the distributed data set metadata according to the selected dimension sorting list and the selected index structure list, to obtain a distributed index structure;

[0126] Step S35: Distribute and store index data according to the distributed index structure to obtain distributed index data; perform index metadata management on the distributed index data to obtain index metadata.

[0127] As an example of the present invention, refer to Figure 2 As shown, in this example, step S3 includes:

[0128] Step S31: perform index dimension analysis on the distributed data set metadata to obtain a sorted list of selected dimensions;

[0129] In the embodiment of the present invention, common query patterns of the electronic component data warehouse are analyzed to determine the main query dimensions, such as product model, manufacturer, package type, operating temperature, etc. These dimensions are ranked in importance according to the query frequency and data distribution. For example, the product model has the highest query frequency, followed by the manufacturer, and finally the package type and operating temperature. The selected dimension ranking list is: [product model, manufacturer, package type, operating temperature].

[0130] Step S32: performing multi-dimensional selection of index structures according to the dimension factor data in the selected dimension sorting list to obtain a collection of candidate index structures;

[0131] In an embodiment of the present invention, for each dimension in the selected dimension sorting list, a suitable index structure is selected according to its data type and query requirements. For example, the product model field is of string type, and the query condition is usually an exact match, so a hash index can be selected; the manufacturer field is also of string type, but the query condition includes fuzzy matching, so an inverted index can be selected; the package type and operating temperature fields are enumeration types, and a bitmap index can be selected. The candidate index structures corresponding to each dimension are combined to form a collection of candidate index structures, for example: [hash index-inverted index-bitmap index-bitmap index].

[0132] Step S33: evaluating and selecting the index structure for the candidate index structure collection to obtain a selected index structure list;

[0133] In the embodiment of the present invention, a test data set is used to perform a performance test on a collection of candidate index structures, and indicators such as query performance, storage space usage, and index building time under different index structure combinations are compared. According to the performance test results, the index structure combination with the best comprehensive performance is selected as the selected index structure list, for example: [hash index-inverted index-bitmap index-bitmap index].

[0134] Step S34: using the distributed computing framework to construct a distributed index for the distributed data set metadata according to the selected dimension sorting list and the selected index structure list, to obtain a distributed index structure;

[0135] In the embodiment of the present invention, the distributed data set metadata is loaded into the Spark RDD using the Spark distributed computing framework. According to the selected dimension sorting list and the selected index structure list, the data in the RDD is grouped and sorted, and the corresponding index construction function is called to build the index. For example, for the product model field, the product model is grouped using a hash function, and the data of each group is stored in the same index file; for the manufacturer field, an inverted index is created using an inverted index construction function. Finally, the index structure of distributed storage is obtained.

[0136] Step S35: Distribute and store index data according to the distributed index structure to obtain distributed index data; perform index metadata management on the distributed index data to obtain index metadata;

[0137] In the embodiment of the present invention, the constructed distributed index structure is stored in the HDFS distributed file system. At the same time, the storage path, size, creation time, index field and other information of each index file are recorded to form index metadata. The index metadata is stored in the Zookeeper distributed coordination service for managing and maintaining distributed index data.

[0138] Preferably, step S31 includes the following steps:

[0139] Step S311: performing query pattern analysis on the distributed data set metadata to obtain query pattern analysis data;

[0140] Step S312: Perform preliminary analysis of query dimensions based on query pattern analysis data to obtain a set of candidate dimensions;

[0141] Step S313: performing dimension cardinality statistics on the candidate dimension set to obtain a dimension cardinality statistics table;

[0142] Step S314: Dimension importance evaluation is performed based on the query pattern analysis data and the dimension cardinality statistics table to obtain a dimension importance ranking table;

[0143] Step S315: select and sort the candidate dimension set according to the dimension importance ranking table to obtain a selected dimension ranking list.

[0144] In an embodiment of the present invention, log information of users querying the electronic component data warehouse within a period of time is collected through a log analysis tool (such as Elasticsearch Logstash Kibana). Parse the log information and extract the fields, filter conditions, sorting methods and other information involved in the query statement. For example, it is analyzed that users often query the product model, manufacturer, and package type fields, where the product model query is usually an exact match, the manufacturer query contains fuzzy matching, and the package type query is often combined with other fields for filtering. The analysis results are stored in the form of structured data to form query pattern analysis data, for example: {"field":"product model","operation":"=","frequency":1000},{"field":"manufacturer","operation":"like","frequency":500}, etc. According to the frequency of occurrence of each field in the query pattern analysis data, high-frequency query fields are screened out as candidate dimensions. For example, if the frequency threshold is set to 100, the fields with a frequency greater than or equal to 100 in the query pattern analysis data are extracted to form a candidate dimension set, for example: [product model, manufacturer, package type, operating temperature]. For each dimension in the candidate dimension set, count the number of different values, i.e., the dimension cardinality. For example, the product model field has 10,000 different values, the manufacturer field has 100 different values, the package type field has 10 different values, and the operating temperature field has 5 different values. Store the statistical results in a table to form a dimension cardinality statistics table, for example: |dimension|cardinality||---|---||product model|10000||manufacturer|100||package type|10||operating temperature|5|. Comprehensively consider the query frequency and dimension cardinality to evaluate the importance of the candidate dimensions. For example, define the importance score = query frequency*log2(dimension cardinality). The higher the score, the more important the dimension. Sort the candidate dimensions according to the calculated scores to form a dimension importance ranking table, for example: |dimension|importance score||---|---||product model|13287.71||manufacturer|3321.93||package type|332.19||operating temperature|232.19|. According to the dimension importance ranking table, select the top N dimensions with the highest scores as the final index dimensions, and sort them according to the scores to form a selected dimension ranking list. For example, select the 4 dimensions with the highest scores, and the selected dimension ranking list is: [product model, manufacturer, package type, operating temperature].

[0145] Preferably, step S4 comprises the following steps:

[0146] Step S41: performing column storage scheme evaluation on the distributed index data and index metadata to obtain column storage scheme evaluation data;

[0147] Step S42: selecting a column storage engine according to the column storage solution evaluation data to obtain a selected column storage engine; optimizing a data table design according to the column storage solution evaluation data to obtain data table optimized structure data;

[0148] Step S43: adjusting the data sharding strategy according to the selected column storage engine and the data table optimization structure data to obtain a data sharding adjustment strategy;

[0149] Step S44: converting the data format of the distributed data set metadata according to the data sharding adjustment strategy to obtain column storage format data; loading the column storage format data into the selected column storage engine to construct a column storage database to obtain a column storage database;

[0150] Step S45: performing dictionary encoding of small cardinality columns on the column storage database to obtain a dictionary encoded data set; performing run-length encoding of multiple repeated value columns on the column storage database to obtain a run-length encoded data set;

[0151] Step S46: adjusting the data block size of the dictionary encoding data set and the run-length encoding data set to obtain data block size optimized data;

[0152] Step S47: performing index optimization according to the column storage database, the data block size optimization data, and the index metadata to obtain a column storage index optimization database.

[0153] In an embodiment of the present invention, different column storage solutions are evaluated according to the data characteristics and query requirements of the electronic component data warehouse. For example, two column storage formats, Apache Parquet and Apache ORC, are evaluated to compare their performance in data compression rate, query performance, data writing performance, etc. At the same time, considering the compatibility with existing systems and tools, the evaluation results are recorded to form column storage solution evaluation data, for example: {"format":"Parquet","compression_ratio":"0.3","query_performance":"Excellent","write_performance":"Good"}. According to the column storage solution evaluation data, the solution with the best comprehensive performance is selected as the selected column storage engine. For example, according to the evaluation results, Apache Parquet is selected as the column storage engine. According to the characteristics of the electronic component data, the data table is designed and optimized, for example, high-frequency query fields and low-frequency query fields are stored in different column families, and fields with the same prefix are stored in adjacent columns to reduce the amount of data read and improve query efficiency. The optimized data table structure information is recorded to form data table optimized structure data. Optimize the structured data according to the selected columnar storage engine and data table, and adjust the data sharding strategy to optimize data storage and query efficiency. For example, according to the data storage characteristics of Apache Parquet, the data is sharded according to the product model. Each shard contains all the data of the corresponding product model and is stored as a Parquet file. According to the data sharding adjustment strategy, convert the metadata of the distributed dataset into columnar storage format data. For example, use Spark to read the metadata of the distributed dataset in HBase, group them according to the product model, and convert the data of each group into Parquet format and store them in HDFS. Then, use Spark SQL to create an external table, map the Parquet file on HDFS to a data table, and build a columnar storage database. Dictionary encode the small cardinality columns in the columnar storage database and replace duplicate values ​​with numeric indexes to reduce data storage space. For example, compress the value of the package type field using dictionary encoding, and replace values ​​such as "SOIC-14" and "DIP-8" with corresponding numeric indexes. Run-length encode the columns in the columnar storage database that have a large number of duplicate values ​​to further compress data storage space. For example, the value of the product series field is compressed using run-length encoding, and consecutive identical values ​​are represented by a value and the number of repetitions. The data block size is adjusted according to the data query mode and hardware environment to optimize data reading efficiency.For example, if data queries usually involve only a small amount of data, the data block size can be set smaller to reduce the number of disk I / Os; conversely, if data queries usually involve a large amount of data, the data block size can be set larger to reduce the number of data reads. Optimize the index based on the data distribution of the columnar storage database, data block size optimization data, and index metadata. For example, adjust the index granularity based on the data distribution and adjust the index cache size based on the data block size to improve the efficiency of index queries.

[0154] Preferably, step S5 comprises the following steps:

[0155] Step S51: selecting a query engine according to the column storage index optimization database to obtain a selected query engine; deploying the query engine of the selected query engine in the data warehouse cluster to obtain a deployed query engine;

[0156] Step S52: using the deployed query engine to parse the query statement to obtain an abstract syntax tree; performing semantic analysis and verification on the abstract syntax tree to obtain semantic verification data; performing logical query plan conversion on the abstract syntax tree according to the semantic verification data to obtain a logical query plan;

[0157] Step S53: acquiring metadata from the column storage index optimization database to obtain metadata; optimizing the logic query plan using predefined optimization rules to obtain a logic query optimization plan;

[0158] Step S54: performing parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set;

[0159] Step S55: caching and pre-fetching results according to the query result set to obtain cached query result data.

[0160] As an example of the present invention, refer to Figure 3 As shown, in this example, step S5 includes:

[0161] Step S51: selecting a query engine according to the column storage index optimization database to obtain a selected query engine; deploying the query engine of the selected query engine in the data warehouse cluster to obtain a deployed query engine;

[0162] In the embodiment of the present invention, considering the characteristics of the column storage database and the need for the electronic component data warehouse to support high concurrent queries, Presto is selected as the query engine. Presto is a memory-based distributed SQL query engine suitable for interactive query analysis and can provide high-performance and low-latency query services. The Presto query engine is deployed on each computing node of the data warehouse cluster to form a deployed query engine, and Presto is configured to connect to the column storage database to access the data.

[0163] Step S52: using the deployed query engine to parse the query statement to obtain an abstract syntax tree; performing semantic analysis and verification on the abstract syntax tree to obtain semantic verification data; performing logical query plan conversion on the abstract syntax tree according to the semantic verification data to obtain a logical query plan;

[0164] In an embodiment of the present invention, when a user submits an SQL query statement, the deployed Presto query engine first performs lexical analysis and syntax analysis on the query statement and converts it into an abstract syntax tree (AST). For example, for the query statement "SELECT product model, manufacturer FROM components WHERE price <10", it is parsed into an AST containing nodes such as SELECT, FROM, WHERE, etc. Then, Presto performs semantic analysis on the AST to check whether the table name and column name in the query statement exist, whether the data type matches, etc., and records the analysis results in the semantic verification data. If the semantic verification passes, the AST is converted into a logical query plan, such as LogicalProject(product model, manufacturer)->LogicalFilter(price <10)->LogicalTableScan(components).

[0165] Step S53: acquiring metadata from the column storage index optimization database to obtain metadata; optimizing the logic query plan using predefined optimization rules to obtain a logic query optimization plan;

[0166] In the embodiment of the present invention, the Presto query engine obtains the structural information, statistical information, index information, etc. of the data table from the metadata service of the column storage database, for example, obtaining information such as that the product model field is a string type, the price field is a numeric type, and that the product model field has a hash index. The predefined optimization rules, such as predicate pushdown, column pruning, and connection order optimization, are used to optimize the logical query plan and generate a logical query optimization plan. For example, the filter condition "price < 10" is pushed down to the data reading stage, and only data with a price less than 10 is read to reduce the amount of data read.

[0167] Step S54: performing parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set;

[0168] In an embodiment of the present invention, a logical query optimization plan is converted into a physical query plan that can be executed in parallel based on metadata information, such as data sharding information, index information, etc. For example, a query task is decomposed into multiple subtasks, each of which is responsible for reading and processing a portion of the data, and the subtasks are assigned to different computing nodes for parallel execution. The computing nodes use index information to quickly locate and read data, and return the processed data to the master node. The master node aggregates the results of all computing nodes to form a final query result set.

[0169] Step S55: caching and pre-fetching results according to the query result set to obtain cached query result data;

[0170] In the embodiment of the present invention, the query result set is cached in the memory or distributed cache, and the expiration time of the cache is recorded. When the user submits the same or similar query request, the result is directly returned from the cache to improve the query speed. At the same time, based on the user's query history and data access mode, the data queried by the user is predicted, and the data is loaded into the cache in advance to further improve the query efficiency.

[0171] Preferably, step S54 includes the following steps:

[0172] Step S541: performing cost analysis of different physical query plans on the logical query optimization plan according to the metadata to obtain a candidate physical query plan;

[0173] Step S542: select a query plan for the candidate physical query plan to obtain a query execution plan; decompose the query execution plan into query tasks to obtain parallel execution subtasks;

[0174] Step S543: obtaining cluster resource status data; scheduling subtasks of different nodes for parallel execution subtasks according to the cluster resource status data to obtain a query task list;

[0175] Step S544: performing node query task execution processing according to the query task list to obtain node query task execution data;

[0176] Step S545: Transmit the node query task execution data to the preset master node, generate a query result set, and obtain a query result set.

[0177] In an embodiment of the present invention, the optimizer of the Presto query engine analyzes the logical query optimization plan based on the obtained metadata, such as data table statistics, data distribution information, index information, etc., and generates multiple candidate physical query plans. For example, for queries containing join operations, different physical query plans based on broadcast joins, hash joins, sort-merge joins, etc. can be generated. The optimizer uses a cost model to estimate the execution cost of each candidate physical query plan, such as CPU usage time, memory usage, network transmission volume, etc. The optimizer of the Presto query engine selects the candidate physical query plan with the lowest cost as the final query execution plan based on the cost analysis result. For example, if the cost of the physical query plan based on hash join is the lowest, then the plan is selected as the final execution plan. Then, the query execution plan is decomposed into multiple subtasks that can be executed in parallel, such as decomposing operations such as data reading, filtering, joining, and aggregation into different subtasks. The scheduler of the Presto query engine obtains the resource usage of each node in the cluster from resource management systems such as YARN, such as CPU remaining, memory remaining, network bandwidth, etc. Subtasks are scheduled to different computing nodes for execution based on the cluster resource status and the resource requirements of the subtasks. For example, subtasks that require a large amount of CPU resources are scheduled to nodes with more CPU remaining, and subtasks that need to read a large amount of data are scheduled to nodes that are closer to the data storage node. Each computing node executes the corresponding subtasks based on the received query task list. For example, the node that executes the data reading subtask reads data from HDFS, the node that executes the filtering subtask filters data according to the filtering conditions, and the node that executes the connection subtask connects data from different data sources. Each node stores the processed data in the local disk or memory to form the node query task execution data. All computing nodes transmit the node query task execution data to the preset master node. The master node is responsible for aggregating the results of all nodes and performing final processing, such as sorting and aggregation, to generate the final query result set.

[0178] Preferably, step S55 comprises the following steps:

[0179] Step S551: collecting user query logs according to the query result set to obtain user query logs; extracting query features from the user query logs to obtain query feature data;

[0180] Step S552: using a machine learning algorithm to train a query behavior prediction model based on the query feature data to obtain a query behavior prediction model;

[0181] Step S553: ​​obtaining real-time user query request data; using the query behavior prediction model to predict query results for the real-time user query request data to obtain query result prediction data;

[0182] Step S554: If the query result prediction data is not cached or the cache has expired, data pre-fetching is performed on the column storage index optimization database and loaded into the memory; if the query result prediction data has been cached, the query result is returned to obtain the cached query result data.

[0183] In an embodiment of the present invention, the system records each query request submitted by the user, including information such as query statements, query time, query result size, etc., to form a user query log. The query statement is analyzed using natural language processing technology to extract query features, such as the table name, field name, filter condition, sorting method, etc. involved in the query. The extracted features are converted into a numerical vector to form query feature data, such as: [0,1,0,1,10,0,1], where each element represents a feature, for example, the first element represents whether to query the product model, the second element represents whether to query the manufacturer, and the fifth element represents the upper limit of the price filter condition. Using historical user query log data, the query feature data is used as input, and the actual query result data is used as output to train a machine learning model, such as a logistic regression model, a support vector machine model, a neural network model, etc., to predict the user's query behavior. When a user submits a new query request, the system obtains the user's query statement in real time and extracts the query feature data. The query feature data is predicted using the trained query behavior prediction model to obtain the query result prediction data. For example, it is predicted that the user will query the electronic component data with the product model "SN74LS00N", the manufacturer "TI", and the price less than $10. The system checks whether the query result prediction data has been cached and whether the cache has expired. If the query result prediction data has not been cached or the cache has expired, the relevant data is pre-read from the column storage database according to the prediction results and loaded into the memory cache. For example, the electronic component data with the product model "SN74LS00N", the manufacturer "TI", and the price less than $10 is loaded into the memory cache. If the query result prediction data has been cached and the cache has not expired, the data is directly read from the cache and returned to the user.

[0184] Therefore, the embodiments should be regarded as illustrative and non-restrictive from all points, and the scope of the present invention is limited by the appended claims rather than the above description, and it is therefore intended that all changes falling within the meaning and range of equivalent elements of the application documents are included in the present invention.

[0185] The above description is only a specific embodiment of the present invention, so that those skilled in the art can understand or implement the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but should conform to the widest scope consistent with the principles and novel features invented herein.

Claims

1. A method for constructing a data warehouse based on electronic components, characterized in that: The following steps are involved: Step S1: Collect electronic component data and perform data preprocessing through crawler technology to obtain a standardized data set; Perform feature engineering on the standardized data set to obtain a feature engineering data set; Step S2: Perform distributed storage processing on the feature engineering data set to obtain a distributed data set; perform metadata management on the distributed data set to obtain distributed data set metadata; Step S3: performing index dimension analysis on the metadata of the distributed data set to obtain a sorted list of selected dimensions; performing index data distribution processing according to the sorted list of selected dimensions to obtain distributed index data; performing index metadata management on the distributed index data to obtain index metadata; Step S4: converting the data format of the distributed data set metadata according to the distributed index data and constructing a column storage database to obtain a column storage database; optimizing the index according to the column storage database to obtain a column storage index optimization database; Step S5: performing query analysis and optimization according to the column storage index optimization database to obtain a logical query optimization plan; Perform parallel query execution on the logical query optimization plan according to metadata to obtain the query result set; Result caching and pre-fetching are performed based on the query result set to obtain cached query result data.

2. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S1 includes the following steps: Step S11: Collect electronic component data by crawler technology to obtain an original data set; Step S12: performing data cleaning on the original data set to obtain a cleaned data set; performing data standardization on the cleaned data set to obtain a standardized data set; Step S13: Acquire business demand data; perform feature extraction on the standardized data set according to the business demand data to obtain a feature extraction data set; Step S14: performing feature selection on the feature extraction data set to obtain a feature engineering data set.

3. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S2 includes the following steps: Step S21: Select a storage solution based on the feature engineering data set to obtain a distributed storage solution; Step S22: Slice the feature engineering data set according to data rules to obtain a data slicing set; Step S23: performing slice data format conversion on the data slice set to obtain a data slice conversion set; Step S24: Distributed storage is performed on the data shard conversion set according to the distributed storage solution to obtain a distributed data set; Step S25: Perform metadata management on the distributed data set to obtain the distributed data set metadata.

4. The method for constructing a data warehouse based on electronic components according to claim 3, characterized in that: Step S25 includes the following steps: Step S251: extracting metadata from the distributed data set to obtain original metadata; performing metadata unified processing on the original metadata to obtain unified metadata; Step S252: extracting and fusing knowledge from the unified metadata using natural language technology to obtain domain knowledge fusion data; Step S253: construct a knowledge graph for the domain knowledge fusion data to obtain an electronic component knowledge graph; extract data lineage information from the distributed data set to obtain data lineage information; Step S254: performing data lineage association on the data lineage information and the electronic component knowledge graph to obtain an electronic component knowledge graph with lineage information; Step S255: Store graph data of the electronic component knowledge graph with lineage information to obtain distributed data set metadata.

5. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S31 includes the following steps: Step S31: perform index dimension analysis on the distributed data set metadata to obtain a sorted list of selected dimensions; Step S32: performing multi-dimensional selection of index structures according to the dimension factor data in the selected dimension sorting list to obtain a collection of candidate index structures; Step S33: evaluating and selecting the index structure for the candidate index structure collection to obtain a selected index structure list; Step S34: using the distributed computing framework to construct a distributed index for the distributed data set metadata according to the selected dimension sorting list and the selected index structure list, to obtain a distributed index structure; Step S35: Distribute and store index data according to the distributed index structure to obtain distributed index data; perform index metadata management on the distributed index data to obtain index metadata.

6. The method for constructing a data warehouse based on electronic components according to claim 5, characterized in that: Step S31 includes the following steps: Step S311: performing query pattern analysis on the distributed data set metadata to obtain query pattern analysis data; Step S312: Perform preliminary analysis of query dimensions based on query pattern analysis data to obtain a set of candidate dimensions; Step S313: performing dimension cardinality statistics on the candidate dimension set to obtain a dimension cardinality statistics table; Step S314: Dimension importance evaluation is performed based on the query pattern analysis data and the dimension cardinality statistics table to obtain a dimension importance ranking table; Step S315: select and sort the candidate dimension set according to the dimension importance ranking table to obtain a selected dimension ranking list.

7. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S4 includes the following steps: Step S41: performing column storage scheme evaluation on the distributed index data and index metadata to obtain column storage scheme evaluation data; Step S42: selecting a column storage engine according to the column storage solution evaluation data to obtain a selected column storage engine; optimizing a data table design according to the column storage solution evaluation data to obtain data table optimized structure data; Step S43: adjusting the data sharding strategy according to the selected column storage engine and the data table optimization structure data to obtain a data sharding adjustment strategy; Step S44: converting the data format of the distributed data set metadata according to the data sharding adjustment strategy to obtain column storage format data; loading the column storage format data into the selected column storage engine to construct a column storage database to obtain a column storage database; Step S45: performing dictionary encoding of small cardinality columns on the column storage database to obtain a dictionary encoded data set; performing run-length encoding of multiple repeated value columns on the column storage database to obtain a run-length encoded data set; Step S46: adjusting the data block size of the dictionary encoding data set and the run-length encoding data set to obtain data block size optimized data; Step S47: performing index optimization according to the column storage database, the data block size optimization data, and the index metadata to obtain a column storage index optimization database.

8. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S5 includes the following steps: Step S51: selecting a query engine according to the column storage index optimization database to obtain a selected query engine; deploying the query engine of the selected query engine in the data warehouse cluster to obtain a deployed query engine; Step S52: using the deployed query engine to parse the query statement to obtain an abstract syntax tree; performing semantic analysis and verification on the abstract syntax tree to obtain semantic verification data; performing logical query plan conversion on the abstract syntax tree according to the semantic verification data to obtain a logical query plan; Step S53: acquiring metadata from the column storage index optimization database to obtain metadata; optimizing the logic query plan using predefined optimization rules to obtain a logic query optimization plan; Step S54: performing parallel query execution on the logical query optimization plan according to the metadata to obtain a query result set; Step S55: caching and pre-fetching results according to the query result set to obtain cached query result data.

9. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S54 includes the following steps: Step S541: performing cost analysis of different physical query plans on the logical query optimization plan according to the metadata to obtain a candidate physical query plan; Step S542: select a query plan for the candidate physical query plan to obtain a query execution plan; decompose the query execution plan into query tasks to obtain parallel execution subtasks; Step S543: obtaining cluster resource status data; scheduling subtasks of different nodes for parallel execution subtasks according to the cluster resource status data to obtain a query task list; Step S544: performing node query task execution processing according to the query task list to obtain node query task execution data; Step S545: Transmit the node query task execution data to the preset master node, generate a query result set, and obtain a query result set.

10. The method for constructing a data warehouse based on electronic components according to claim 1, characterized in that: Step S55 includes the following steps: Step S551: collecting user query logs according to the query result set to obtain user query logs; extracting query features from the user query logs to obtain query feature data; Step S552: using a machine learning algorithm to train a query behavior prediction model based on the query feature data to obtain a query behavior prediction model; Step S553: ​​obtaining real-time user query request data; using the query behavior prediction model to predict query results for the real-time user query request data to obtain query result prediction data; Step S554: If the query result prediction data is not cached or the cache has expired, data pre-fetching is performed on the column storage index optimization database and loaded into the memory; if the query result prediction data has been cached, the query result is returned to obtain the cached query result data.

Images