Clickhome-based e-commerce data query method and device and medium

Abstract

The invention discloses an e-commerce data query method and device based on Clickhome and a medium, and relates to the technical field of e-commerce data query. The method comprises the steps of collecting commodity data of an e-commerce platform through a distributed crawler strategy, wherein the commodity data comprises a price trend and a commodity address; the repeated commodity data are merged, commodity addresses are converted through a geocoding engine, and a cross-platform commodity category mapping engine is constructed; according to a cross-platform commodity category mapping engine, constructing a time and space dual partition strategy in Clickhouse, and constructing a multi-level sorting structure based on hop count indexes; receiving a multi-dimensional query request of a user, and performing distributed query through a query compiler; and aggregating by utilizing a vectorization calculation engine to generate a ranking and trend result set. According to the method, the collaborative optimization problem of real-time cleaning, standardized management and millisecond-level multi-dimensional analysis of cross-platform e-commerce data is realized.

Description

Technical Field

[0001] The present application relates to the technical field of e-commerce data query, and in particular to an e-commerce data query method, device, and medium based on Clickhouse. Background Art

[0002] Existing e-commerce data analysis systems, which generally utilize the Hadoop and Spark technology stack, face two major bottlenecks. First, traditional relational databases experience response delays when querying data across billions of data points, making them incapable of meeting real-time decision-making requirements. Second, cross-platform product data suffers from heterogeneous category systems and non-standardized address descriptions, leading to fragmented analysis dimensions. While ClickHouse, with its columnar storage and vectorization engine, is used for data analysis, existing solutions lack governance mechanisms and query optimization systems tailored to e-commerce's multi-source, heterogeneous data.

[0003] Existing technologies fail to address the collaborative optimization issues of real-time cleansing, standardized governance, and multi-dimensional analysis of cross-platform e-commerce data. These issues are particularly evident in the following aspects: cross-platform mapping of product categories relies on manual rule maintenance, resulting in slow response and high error rates; unstructured addresses render geographic analysis ineffective; traditional partitioning strategies are unable to simultaneously optimize queries in both time and space dimensions; and dynamic sales trend analysis requires pre-modeling, resulting in a lack of flexibility.

[0004] Through the above analysis, the problems and defects of the existing technology are as follows:

[0005] Cross-platform e-commerce data in existing technologies cannot achieve coordinated optimization of real-time cleaning, standardized management and multi-dimensional analysis. Summary of the Invention

[0006] The embodiments of the present application provide a ClickHouse-based e-commerce data query method, device, and medium, which can solve the problem in the prior art that cross-platform e-commerce data cannot achieve real-time cleaning, standardized management, and collaborative optimization of multi-dimensional analysis.

[0007] In the first aspect, an embodiment of the present application provides an e-commerce data query method based on Clickhouse, the method comprising: collecting product data of an e-commerce platform through a distributed crawler strategy, the product data including price trends and product addresses; merging duplicate product data, converting product addresses through a geocoding engine, and building a cross-platform product category mapping engine; based on the cross-platform product category mapping engine, building a dual partitioning strategy of time and space in Clickhouse, and building a multi-level sorting structure based on a hop index; receiving multi-dimensional query requests from users, and performing distributed queries through a query compiler; and using a vectorized computing engine for aggregation to generate a result set of rankings and trends.

[0008] In one implementation of the present application, duplicate product data is merged, and the product addresses are converted through a geocoding engine, and a cross-platform product category mapping engine is constructed, specifically including: verifying the field integrity of the product data and discarding product data with a missing rate exceeding a threshold; using the isolation forest algorithm to detect abnormal fluctuation records of price trends, and correcting the abnormal sales values ​​of the sales field based on historical sales distribution; calling the geocoding engine API to resolve the product address into latitude and longitude coordinates.

[0009] In one implementation of the present application, the method also includes: extracting a category tree of commodity data, the category tree including text descriptions and hierarchical relationships; calculating the semantic similarity between the text descriptions and national standard categories through the BERT model, and matching them; for text descriptions that are not successfully matched, starting a manual review process to generate mapping rules; and updating the category tree changes to a dynamic mapping table of standard categories in real time.

[0010] In one implementation of the present application, a multi-dimensional query request from a user is received, and a distributed query is performed through a query compiler, specifically including: parsing the time range, geographic fence, and keyword combination in the query request to identify high-frequency query patterns; selecting a pre-aggregated materialized view based on the high-frequency query pattern; splitting the keyword combination into product name, brand name, and category name, and performing parallel matching to generate a shard-aware MapReduce-style execution plan.

[0011] In one implementation of the present application, a vectorized computing engine is used for aggregation to generate a result set of rankings and trends, specifically including: obtaining the ranking of target products based on a sliding time window algorithm; performing cross-platform brand sales trend comparison analysis, and attaching a price distribution histogram of similar products.

[0012] In one implementation of the present application, the method also includes: monitoring query requests and identifying high-frequency query patterns; determining hot data based on the high-frequency request patterns, calculating pre-aggregation results based on the hot data, and loading them into a memory table; introducing an LRU-K cache elimination mechanism for the memory table, and starting an incremental update pipeline for pre-aggregation results that have failed in the cache.

[0013] In one implementation of the present application, the method also includes: real-time monitoring of the anti-crawl strategy of the e-commerce platform to adjust the IP proxy pool and request frequency; when incremental data is obtained, triggering a real-time ETL pipeline for the incremental data, and publishing data change events to the Kafka message queue.

[0014] In one implementation of the present application, the method further includes: constructing dynamic threshold alarm rules based on historical data distribution; triggering a correction pipeline for abnormal data, and generating a data quality report to feed back to the distributed crawler strategy.

[0015] In the second aspect, an embodiment of the present application also provides an e-commerce data query device based on Clickhouse, the device including at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions that can be executed by at least one processor, and the instructions are executed by at least one processor so that the at least one processor can: collect product data of the e-commerce platform through a distributed crawler strategy, the product data including price trends and product addresses; merge duplicate product data, and convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; based on the cross-platform product category mapping engine, build a dual partitioning strategy of time and space in Clickhouse, and build a multi-level sorting structure based on hop index; receive multi-dimensional query requests from users, and perform distributed queries through a query compiler; use a vectorized computing engine for aggregation to generate a result set of rankings and trends.

[0016] On the third aspect, an embodiment of the present application also provides a non-volatile computer storage medium for e-commerce data query based on Clickhouse, which stores computer executable instructions, and the computer executable instructions are set to: collect product data of the e-commerce platform through a distributed crawler strategy, and the product data includes price trends and product addresses; merge duplicate product data, and convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; based on the cross-platform product category mapping engine, build a dual partitioning strategy of time and space in Clickhouse, and build a multi-level sorting structure based on hop index; receive users' multi-dimensional query requests, and perform distributed queries through a query compiler; use a vectorized computing engine for aggregation to generate a result set of rankings and trends.

[0017] The embodiments of the present application provide a Clickhouse-based e-commerce data query method, which compresses the response time of associated queries on billions of data and improves the concurrent processing capability through a dual-time-space partitioning strategy and hop index optimization. The constructed cross-platform category mapping engine supports automatic mapping accuracy and is combined with a geocoding engine to achieve province-city-district three-level penetration analysis, thereby improving the coverage of multi-dimensional analysis of products / brands / enterprises. The dynamic pre-aggregation mechanism based on query patterns reduces computing resource consumption and, combined with closed-loop control of data quality, improves the detection rate of abnormal data and significantly reduces the cost of manual intervention. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0019] Figure 1A flowchart of an e-commerce data query method based on ClickHouse provided in an embodiment of the present application;

[0020] Figure 2 A schematic diagram of the internal structure of an e-commerce data query device based on ClickHouse provided in an embodiment of the present application. DETAILED DESCRIPTION

[0021] To make the purpose, technical solutions, and advantages of this application more clear, the technical solutions of this application will be clearly and completely described below in conjunction with the specific embodiments of this application and the corresponding drawings. Obviously, the embodiments described are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0022] The embodiments of the present application provide a ClickHouse-based e-commerce data query method, device, and medium, which solve the problem in the prior art that cross-platform e-commerce data cannot achieve collaborative optimization of real-time cleaning, standardized management, and multi-dimensional analysis.

[0023] The technical solutions proposed in the embodiments of the present application are described in detail below with reference to the accompanying drawings.

[0024] Figure 1 This is a flow chart of an e-commerce data query method based on Clickhouse provided in the embodiment of this application. Figure 1 As shown, the embodiment of the present application provides an e-commerce data query method based on ClickHouse, which specifically includes the following steps:

[0025] Step 10: Collect product data from the e-commerce platform using a distributed crawler strategy. Product data includes price trends and product addresses.

[0026] Step 20: Merge duplicate product data, convert product addresses through the geocoding engine, and build a cross-platform product category mapping engine;

[0027] As an optional embodiment, duplicate product data is merged, product addresses are converted through a geocoding engine, and a cross-platform product category mapping engine is constructed. Specifically, the following steps may be included: Step 201: Verifying the field integrity of product data and discarding product data with a missing rate exceeding a threshold; Step 202: Using an isolation forest algorithm to detect abnormal fluctuation records of price trends, and correcting abnormal sales values ​​of the sales field based on historical sales distribution; Step 203: Calling the geocoding engine API to resolve the product address into latitude and longitude coordinates.

[0028] In this step, invalid data caused by collection errors, incomplete platform data, etc. are filtered to prevent incomplete data from interfering with subsequent analysis. First, a list of key fields for product data is clarified, which usually includes product ID, name, price, sales volume, category, brand, address, price trend timestamp, etc.; the missing rate of each product data is calculated: missing rate = (number of missing key fields ÷ total number of key fields) × 100%. A missing rate threshold is set. If the missing rate of a certain data exceeds the threshold, it is determined to be invalid data and discarded directly. Reduce data storage redundancy, reduce the computational cost of subsequent processing, and ensure that the data involved in the processing contains at least the core analysis elements.

[0029] Furthermore, data anomalies caused by data entry errors, malicious order manipulation, and system failures are corrected to ensure the authenticity of price and sales data and avoid distortion in subsequent trend analysis and ranking statistics. The Isolation Forest algorithm is an efficient unsupervised anomaly detection model suitable for identifying a small number of outliers in high-dimensional data. In price trend analysis, the algorithm recursively partitions the feature space to quickly isolate records with price fluctuations that significantly deviate from the normal range, marking them as abnormal fluctuations.

[0030] Furthermore, the marked abnormal price records need to be further manually verified to exclude reasonable fluctuations such as promotional activities. Once the abnormality is confirmed, they will be directly eliminated or replaced with the average recent price of the product.

[0031] Next, we first analyze the historical sales distribution of each product to determine the normal sales fluctuation range. Abnormal sales values ​​outside this range are corrected by referencing reasonable values ​​in the historical distribution to prevent extreme values ​​from impacting the accuracy of metrics like best-selling rankings and sales trends. Through dual validation of algorithms and historical data, we balance automated processing efficiency with data authenticity, providing reliable baseline metrics for subsequent analysis. To address the inconsistency of text address formats across platforms, we standardize address information using latitude and longitude coordinates, providing a unified benchmark for subsequent geographic analysis. We call the APIs of third-party geocoding engines, such as Amap and Baidu Maps, and input a text description of the product address. The API will return the corresponding longitude and latitude coordinates. Addresses that fail to parse, such as ambiguous addresses in the Jiangsu, Zhejiang, and Shanghai regions, are marked as pending. After manual verification and supplementation of specific information, they are reparsed to ensure address data integrity. Converting unstructured text addresses to structured latitude and longitude eliminates cross-platform address description discrepancies and facilitates geographic queries such as filtering products by region and analyzing regional price distribution.

[0032] As an optional embodiment, the method may also include: step 204: extracting a category tree of commodity data, the category tree including text descriptions and hierarchical relationships; step 205: calculating the semantic similarity between the text description and the national standard category through the BERT model, and matching them; step 206: for text descriptions that are not successfully matched, starting a manual review process to generate mapping rules; step 207: updating the category tree changes to a dynamic mapping table of standard categories in real time.

[0033] In this step, a complete category tree is extracted from the product data. Each category includes a text description, such as "kitchen appliances" in "home appliances / kitchen appliances / microwave ovens," and hierarchical relationships, such as "microwave ovens are a subcategory of kitchen appliances." Category relationships are stored in a tree structure to ensure clear hierarchy. Platform categories are converted from fragmented text into a structured hierarchical system, providing a clear analysis target for subsequent semantic matching and category alignment. Natural language processing technology is used to solve the problem of matching categories with different names but the same meaning, achieving automated matching and reducing labor costs. National standard categories are unified classification standards issued by the state, such as the "National Economic Industry Classification" and "Commodity Classification and Codes," which contain standardized category names and definitions. The BERT model is a pre-trained language model based on Transformers that deeply understands text semantics. The model calculates the semantic similarity score between platform category text descriptions and national standard category text. This addresses marginal categories that AI models cannot handle, such as the newly added niche category "smart sweeping robot accessories," which has no complete corresponding classification in the national standard. Manual intervention is used to ensure that all categories are aligned with the standard system. Unmatched categories with similarity scores below the threshold are automatically pushed to the manual review system. Reviewers, based on category definitions, product attributes, and industry common sense, determine the national standard category to which the category should be assigned, or suggest adding a new national standard category. They also generate clear mapping rules, such as assigning smart robot vacuum accessories to household cleaning appliance accessories. These rules must record information such as category correspondence, applicable scenarios, and effective dates, serving as supplementary evidence for subsequent automated matching. This ensures that the association between platform categories and national standard categories is accurate and real-time, preventing mapping failures due to platform category adjustments. The dynamic mapping table is a core data table that records the correspondence between platform categories and national standard categories. It contains fields such as category ID, name, similarity score, mapping rules, and update time. When platform categories change, the system automatically triggers a mapping table update process: For newly added categories, the AI ​​matching process in step 205 is performed. If a match is unsuccessful, manual review in step 206 is initiated, ultimately writing the new mapping relationship to the table. Deleted or renamed categories are synchronously updated or marked as invalid.

[0034] Step 30: Based on the cross-platform product category mapping engine, build a dual time and space partitioning strategy in ClickHouse, and build a multi-level sorting structure based on hop count index;

[0035] In this step, ClickHouse is a database designed for fast query and analysis of large amounts of data. It partitions product data according to the time dimension, such as the recording time of price trends and the time when products were put on the shelves. Common granularities are days, weeks, and months. Select according to the frequency of data updates and query requirements. For example, all product data on June 1, 2024, is divided into one partition, and the data on June 2, 2024, is divided into another partition. In this way, when a user queries the price trend of a product in the past 7 days, the database only needs to scan the last 7 time partitions, without traversing the entire data, which greatly reduces the scope of data scanning.

[0036] Furthermore, the product address is partitioned based on its converted longitude and latitude coordinates, typically using a geographic grid. For example, the Earth's surface is divided into several grids at intervals of longitude and latitude, with each grid corresponding to a spatial partition. This way, when a user queries product sales within Shanghai, the database only needs to scan the spatial partition corresponding to Shanghai, avoiding a global scan and improving the efficiency of geofence queries.

[0037] In this way, time and space partitions form a two-dimensional cross-index. For example, the partition for Shanghai on June 1, 2024, only contains product data for that time and region. When the query involves the sales volume of mobile phones in East China in the past 30 days in both time and geographical dimensions, the target data can be quickly located through dual partitioning, which is much more efficient than a single partition.

[0038] Furthermore, the hop count index records the maximum and minimum values ​​of statistical information between data blocks, and whether a certain value is included, so as to skip data blocks that do not meet the conditions. After establishing the hop count index on the price field, the data is globally sorted by the time partition key and the space partition key to ensure the physical storage continuity of data in the same time and space partitions, thereby improving the efficiency of queries within the partitions.

[0039] Step 40: Receive the user's multi-dimensional query request and perform distributed query through the query compiler;

[0040] As an optional embodiment, a multi-dimensional query request from a user is received, and a distributed query is performed through a query compiler, which may specifically include: step 401: parsing the time range, geographic fence, and keyword combination in the query request to identify high-frequency query patterns; step 402: selecting a pre-aggregated materialized view based on the high-frequency query pattern; step 403: splitting the keyword combination into product name, brand name, and category name, and performing parallel matching to generate a shard-aware MapReduce-style execution plan.

[0041] Step 50: Use the vectorized computing engine to perform aggregation and generate a result set of rankings and trends.

[0042] As an optional embodiment, a vectorized computing engine is used for aggregation to generate a result set of rankings and trends, which may specifically include: Step 501: obtaining the ranking of the target product based on a sliding time window algorithm; Step 502: performing a cross-platform brand sales trend comparison analysis, and attaching a price distribution histogram of similar products.

[0043] As an optional embodiment, the method may also include: monitoring query requests and identifying high-frequency query patterns; determining hot data based on the high-frequency request patterns, calculating pre-aggregation results based on the hot data, and loading them into a memory table; introducing an LRU-K cache elimination mechanism for the memory table, and starting an incremental update pipeline for pre-aggregation results that have failed in the cache.

[0044] In this step, user query logs are continuously collected and statistically analyzed to identify high-frequency patterns. For example, queries for the top 10 best-selling products in each province between 9:00 and 11:00 each day account for 30% of all queries. Query results corresponding to these high-frequency patterns are pre-calculated and stored as pre-aggregated results to avoid real-time computation during user queries. Pre-aggregated results are loaded into an in-memory table. In-memory data tables have faster read and write speeds than disk, ensuring direct access from memory when users query. When memory space is limited, the LRU-K (Least Recently Used–K) algorithm is used to manage the cache. This algorithm records the last K access times for each pre-aggregated result, prioritizing the elimination of cold data that has not been accessed K times, while retaining frequently accessed hot data. When updates to the original data invalidate the pre-aggregated results, only the changed parts are recalculated. For example, rankings are updated based on the previous hour's sales figures, with newly generated sales data added. This reduces computational costs.

[0045] As an optional embodiment, the method may also include: real-time monitoring of the anti-crawl strategy of the e-commerce platform to adjust the IP proxy pool and request frequency; when incremental data is obtained, triggering a real-time ETL pipeline for the incremental data, and publishing data change events to the Kafka message queue.

[0046] As an optional embodiment, the method may further include: constructing dynamic threshold alarm rules based on historical data distribution; triggering a correction pipeline for abnormal data, and generating a data quality report to feed back to the distributed crawler strategy.

[0047] In this step, when incremental data for new or updated products is obtained, the real-time ETL (Extract, Transform, Load) pipeline is immediately triggered to extract, clean, transform, and load the data. After processing, the price adjustment, sales volume update, and other data change events are published to the Kafka message queue for subscription and consumption by downstream data analysis, business monitoring, and other systems, ensuring timely delivery of data value.

[0048] The above is an embodiment of the method proposed in this application. Based on the same inventive concept, this application embodiment also provides an e-commerce data query device based on Clickhouse, whose structure is as follows Figure 2 shown.

[0049] Figure 2 This is a schematic diagram of the internal structure of an e-commerce data query device based on Clickhouse provided in an embodiment of the present application. Figure 2 As shown, the equipment includes:

[0050] at least one processor 201;

[0051] and, a memory 202 communicatively coupled to the at least one processor;

[0052] Among them, the memory 202 stores instructions that can be executed by at least one processor, and the instructions are executed by at least one processor 201 to enable at least one processor 201 to: collect product data of the e-commerce platform through a distributed crawler strategy, and the product data includes price trends and product addresses; merge duplicate product data, and convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; based on the cross-platform product category mapping engine, build a dual partitioning strategy of time and space in Clickhouse, and build a multi-level sorting structure based on hop index; receive users' multi-dimensional query requests, and perform distributed queries through a query compiler; use a vectorized computing engine for aggregation to generate a result set of rankings and trends.

[0053] Some embodiments of the present application provide corresponding Figure 1 A non-volatile computer storage medium for e-commerce data query based on ClickHouse stores computer-executable instructions, wherein the computer-executable instructions are configured to: collect product data from e-commerce platforms through a distributed crawler strategy, the product data including price trends and product addresses; merge duplicate product data, convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; build a dual time and space partitioning strategy in ClickHouse based on the cross-platform product category mapping engine, and build a multi-level sorting structure based on hop indexing; receive multi-dimensional query requests from users, and perform distributed queries through a query compiler; perform aggregation using a vectorized computing engine to generate a result set of rankings and trends.

[0054] The various embodiments in this application are described in a progressive manner. Similar portions between the various embodiments can be referenced to each other. Each embodiment focuses on the differences from the other embodiments. In particular, the IoT device and media embodiments are generally similar to the method embodiments, so their description is relatively simple. For relevant portions, refer to the description of the method embodiments.

[0055] The system and medium provided in the embodiments of the present application correspond one-to-one to the method. Therefore, the system and medium also have similar beneficial technical effects to their corresponding methods. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the system and medium will not be repeated here.

[0056] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0057] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0058] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0059] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0060] In a typical configuration, a computing device includes one or more processors (CPUs), input / output interfaces, network interfaces, and memory.

[0061] Memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0062] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0063] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0064] The foregoing is merely an embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application should all be included within the scope of the claims of the present application.

Claims

1. An e-commerce data query method based on Clickhouse, characterized in that: The method comprises: Collect product data from e-commerce platforms using a distributed crawler strategy, including price trends and product locations; Merge duplicate product data, convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; Based on the cross-platform product category mapping engine, a dual time and space partitioning strategy is built in ClickHouse, and a multi-level sorting structure based on hop count index is constructed; Receive multi-dimensional query requests from users and perform distributed queries through the query compiler; Utilizes a vectorized computing engine to perform aggregation and generate ranking and trending result sets.

2. The e-commerce data query method based on Clickhouse according to claim 1, characterized in that: The merging of duplicate product data, converting product addresses through a geocoding engine, and building a cross-platform product category mapping engine specifically include: Verify the field integrity of the product data and discard product data with a missing rate exceeding a threshold; An isolation forest algorithm is used to detect abnormal fluctuation records of the price trend, and abnormal sales values ​​of the sales volume field are corrected based on historical sales volume distribution; The geocoding engine API is called to resolve the product address into latitude and longitude coordinates.

3. The e-commerce data query method based on Clickhouse according to claim 2, characterized in that: The method further comprises: Extracting a category tree of the product data, the category tree including text descriptions and hierarchical relationships; Calculate the semantic similarity between the text description and the national standard category using the BERT model and perform matching; For text descriptions that are not successfully matched, a manual review process is initiated to generate mapping rules; Update the category tree changes to the dynamic mapping table of standard categories in real time.

4. The e-commerce data query method based on Clickhouse according to claim 2 is characterized in that: The receiving of a multi-dimensional query request from a user and performing a distributed query through a query compiler specifically includes: Parsing the time range, geo-fence, and keyword combinations in the query request to identify high-frequency query patterns; selecting a pre-aggregated materialized view based on the high-frequency query pattern; The keyword combination is split into product name, brand name, and category name, and parallel matching is performed to generate a shard-aware MapReduce-style execution plan.

5. The e-commerce data query method based on Clickhouse according to claim 2 is characterized in that: The vectorized computing engine is used to perform aggregation and generate a result set of rankings and trends, specifically including: Based on the sliding time window algorithm, the target product ranking is obtained; Perform cross-platform brand sales trend comparison analysis and attach a price distribution histogram of similar products.

6. The e-commerce data query method based on Clickhouse according to claim 4 is characterized in that: The method further comprises: monitoring the query requests and identifying the high-frequency query patterns; Determine hotspot data according to the high-frequency request pattern, calculate pre-aggregation results based on the hotspot data, and load them into a memory table; An LRU-K cache elimination mechanism is introduced to the memory table, and an incremental update pipeline is started for the pre-aggregation results that have failed in the cache.

7. The e-commerce data query method based on Clickhouse according to claim 1, characterized in that: The method further comprises: Monitor the anti-crawl strategy of the e-commerce platform in real time to adjust the IP proxy pool and request frequency; When incremental data is obtained, the real-time ETL pipeline is triggered for the incremental data, and the data change events are published to the Kafka message queue.

8. The e-commerce data query method based on Clickhouse according to claim 1, characterized in that: The method further comprises: Build dynamic threshold alarm rules based on historical data distribution; Trigger the correction pipeline for abnormal data and generate a data quality report to feed back to the distributed crawler strategy.

9. An e-commerce data query device based on Clickhouse, characterized in that: The device comprises: at least one processor; and, a memory communicatively coupled to the at least one processor; The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: Collect product data from e-commerce platforms using distributed crawlers, including price trends and product addresses; Merge duplicate product data, convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; Based on the cross-platform product category mapping engine, a dual time and space partitioning strategy is built in ClickHouse, and a multi-level sorting structure based on hop count index is constructed; Receive multi-dimensional query requests from users and perform distributed queries through the query compiler; Utilizes a vectorized computing engine to perform aggregation and generate ranking and trending result sets.

10. A non-volatile computer storage medium for e-commerce data query based on Clickhouse, storing computer-executable instructions, characterized in that: The computer executable instructions are configured to: Collect product data from e-commerce platforms using distributed crawlers, including price trends and product addresses; Merge duplicate product data, convert product addresses through a geocoding engine, and build a cross-platform product category mapping engine; Based on the cross-platform product category mapping engine, a dual time and space partitioning strategy is built in ClickHouse, and a multi-level sorting structure based on hop count index is constructed; Receive multi-dimensional query requests from users and perform distributed queries through the query compiler; Utilizes a vectorized computing engine to perform aggregation and generate ranking and trending result sets.

Images