Digital port data storage and analysis method and device

Abstract

The invention discloses a digital port data storage and analysis method and device. The method comprises a data modeling step, a unified storage step, a dynamic expansion step and a query optimization step. According to the method, data of a plurality of traditional discrete business tables are fused and stored in a unified physical table according to a time main line, the association relation between the tables is internalized into a structured format of the data, and dynamic seamless expansion of a data mode and qualitative leap of query performance are achieved through an innovative index strategy and a storage optimization technology. The structure that hundreds of tables are stored in the database is simplified into the structure that only one to three tables are stored, and the maintenance work of the table structure in the database is greatly reduced.

Description

Technical Field

[0001] This invention belongs to the field of industrial Internet of Things and big data management technology, and particularly relates to a digital port data storage and analysis method and device. Background Technology

[0002] With the development of big data and artificial intelligence technologies, data-driven AI has been applied to various industries. The maturity of industrial time-series data acquisition technology has given rise to a technical system that integrates data and AI algorithms. Big data technologies based on the Greenplum / Postgresql ecosystem seamlessly connect with Madlib's complete machine learning engine, making it possible to efficiently implement AI methods based on big data.

[0003] Ports are crucial transportation hubs in my country, generating massive amounts of data that are difficult to store and respond to quickly for various business analyses. Designing a unified storage method tailored to the data characteristics of digital ports aims to significantly save storage space, reduce redundancy, minimize relational queries, and enable rapid retrieval, thereby achieving optimal performance. While existing big data platforms (such as Greenplum) or time-series databases possess the capability to handle massive amounts of data, their underlying storage models have not fundamentally resolved the contradiction between "fixed schemas" and "inefficient queries." Designing a storage method that supports flexible expansion of business models while enabling rapid cross-entity data relational queries has become a key technological bottleneck in digital port data management. Summary of the Invention

[0004] (a) Technical problems to be solved

[0005] To address the shortcomings of existing technologies, this invention integrates data from multiple traditionally discrete business tables into a unified physical table, organized chronologically. It internalizes the relationships between tables into a structured data format and achieves dynamic, seamless expansion of the data model and a significant leap in query performance through innovative indexing strategies and storage optimization techniques. This invention simplifies the structure of a database storing hundreds of tables to storing only 1 to 3 tables, greatly reducing the maintenance workload of table structures in the database.

[0006] (II) Technical Solution

[0007] To achieve the above objectives, the present invention provides the following technical solution: a digital port data storage and analysis method, comprising the following steps:

[0008] S1. Establish a port / plant information table and multiple independent equipment tables for the digital port. The port / plant information table includes port ID, timestamp, name and plant information fields; each equipment table corresponds to one device and includes equipment ID, timestamp, name and multiple measurement point value fields.

[0009] S2. The data from the port / plant information table and all equipment tables are merged into a JSON format or an array format according to the timestamp and then stored in a unified data table.

[0010] S3. When a new device or a new measurement point is added to the device, the new data is stored by expanding the format structure of the data in the unified data table without modifying the table structure definition of the unified data table.

[0011] S4. Create timestamp-based partitions for the unified data table and create indexes for specific fields to support fast retrieval.

[0012] As a preferred embodiment, step S2 employs multidimensional JSON format for data fusion and storage, specifically as follows:

[0013] Using timestamps as indexes, port / plant information data is stored as a first-level JSON object;

[0014] Data from all equipment belonging to the port / plant is encapsulated into sub-JSON objects, which are then stored together as a second-layer JSON structure within the first-layer JSON object.

[0015] As a preferred embodiment, the S2 uses a multi-dimensional array format for data fusion and storage, specifically:

[0016] Create a header information and index record table to sequentially record the identifiers of ports, equipment, and measuring points and their corresponding array index relationships;

[0017] A full data storage table is established, in which the array field order of the data rows strictly corresponds to the index relationship defined in the table header information and index record table; the array field is a multi-dimensional array, and its dimensions correspond to the port, equipment and measurement point values ​​in sequence.

[0018] As a preferred option, in step S4, a GIN index or a GPText full-text search index is established for the field storing the multidimensional JSON format data.

[0019] As a preferred embodiment, the header information and index record table configures an effective time range for each record item; when the device or measurement point changes, the change in the index relationship is managed by adding a new record item and updating its effective time range.

[0020] As a preferred option, in step S2, for data sequences with relatively linear changes, the data is stored using a geometry or geometry array format.

[0021] As a preferred embodiment, in step S4, the data in the unified data table is split into a hot data table and a cold data table; the hot data table is optimized for frequent queries through partitioning and indexing, while the cold data table is optimized for storage space.

[0022] A digital port data storage and analysis device, comprising:

[0023] The data modeling module is used to create a port / plant information table and multiple independent equipment tables for the digital port.

[0024] The unified storage module is used to merge the data from the port / plant information table and all equipment tables according to timestamps and store them in a unified data table.

[0025] The dynamic expansion module is used to store new data by expanding the format structure of the data in the unified data table when new devices or new measurement points are added to the devices, without modifying the table structure definition of the unified data table.

[0026] The query optimization module is used to create timestamp-based partitions for the unified data table and to create indexes for specific fields to support fast retrieval.

[0027] (III) Beneficial Effects

[0028] Compared with the prior art, the present invention provides a digital port data storage and analysis method and apparatus, which has the following beneficial effects:

[0029] I. This invention simplifies complex multi-table join queries into conditional filtering and scanning of a single wide table, completely eliminating the performance bottleneck caused by numerous JOIN operations. Combined with time partitioning and efficient indexes (such as GIN), it improves the response speed of historical data backtracking and real-time analysis across the entire port and across equipment by more than an order of magnitude. Simultaneously, it internalizes the data structure using self-describing / indexed formats such as JSON or arrays. When adding new equipment or measurement points, only JSON key-value pairs need to be expanded or the index mapping table updated at the data level, without performing any database table structure changes (DDL operations). This enables dynamic expansion through schema transformation, greatly reducing system maintenance complexity and upgrade risks. Attached Figure Description

[0030] Figure 1 This is a schematic diagram of the method flow of the present invention. Detailed Implementation

[0031] To better understand the purpose, structure, and function of this invention, the following description, in conjunction with the accompanying drawings and specific embodiments, will further illustrate a digital port data storage and analysis method and apparatus of this invention.

[0032] Example 1

[0033] refer to Figure 1 This invention discloses a digital port data storage and analysis method. It integrates data from multiple traditionally discrete business tables into a unified physical table, organized chronologically, and internalizes the relationships between tables into a structured data format (such as multidimensional JSON or arrays). Based on this, innovative indexing strategies and storage optimization techniques achieve dynamic and seamless expansion of the data model and a significant leap in query performance. The method specifically includes:

[0034] Data modeling steps: Establish a port / plant information table and multiple independent equipment tables for the digital port; the port / plant information table includes port ID, timestamp, name and plant information fields; each equipment table corresponds to one device and includes equipment ID, timestamp, name and multiple measurement point value fields;

[0035] Unified storage steps: The data from the port / plant information table and all equipment tables are merged according to timestamps and stored in a unified data table;

[0036] Dynamic expansion step: When a new device or a new measurement point is added to the device, the new data is stored by expanding the format structure of the data in the unified data table without modifying the table structure definition of the unified data table;

[0037] Query optimization steps: Create timestamp-based partitions for the unified data table and create indexes for specific fields to support fast retrieval.

[0038] Specifically, this embodiment will demonstrate the specific process of implementing the method of the present invention using multidimensional JSON format, including:

[0039] Data modeling

[0040] At the database design level, conceptually creating:

[0041] A port information sheet

[0042] The port / plant information of the digital port is stored in a table, which includes: port ID, timestamp (timestamp type), name (text type), plant information 1 (text type), plant information 2 (text type), plant information 3 (text type)...

[0043] Multiple device tables, each corresponding to one physical device.

[0044] Each device in the plant is stored in a table. N devices are stored in N tables, each corresponding to the device's own table structure, which includes: device ID, timestamp (timestamp type), name (text type), measurement point 1 (float type), measurement point 2 (float type), measurement point 3 (float type)...

[0045] Next is unified storage (JSON format).

[0046] The data is stored in a two-dimensional JSON format, indexed by timestamps and partitioned by date. Port / factory information is stored in the first-level JSON, and all equipment information at the site is stored in the second-level JSON. Large fields are indexed using GpText full-text search or Gin indexes.

[0047] First, a physically unified data table is created. This table has a fixed structure and contains only three core fields: time, port ID, and aggregated data. All business data is packaged into the aggregated_data JSONB field.

[0048] Furthermore, the data fusion process in this embodiment is as follows:

[0049] For a given point in time t1 and a specific port P1, extract data from the original tables and construct a nested JSON object in the application or ETL process.

[0050] Construct the first layer (port information): Construct the attributes (such as name and type) of port P1 at time t1 into JSON key-value pairs.

[0051] Construct the second layer (device information): Create a key named "devices" with a JSON object as its value. Within this object, use the device ID (e.g., "gantry_001") as the key and another JSON object as the value, containing all the device's attributes and measurement point values ​​at time t1.

[0052] When Port 2 needs to add Equipment 3, there is no need to modify the existing table structure; simply add the corresponding Key-Value pairs directly to the relevant JSON data elements. Similarly, when Equipment 2 needs to add a measurement point (e.g., adding measurement point d4 to device_2), there is no need to modify any existing table structure; it is still achieved by directly adding the corresponding Key-Value pairs to the JSON data elements. During data querying, users can query based on the timestamp condition and enter the correct Key. If a query is made for a non-existent Key (i.e., d4) without adding the measurement point d4, the system will return a null value without triggering a program error. This fault-tolerance mechanism significantly improves the convenience and stability of data interpolation and querying.

[0053] Furthermore, the dynamic expansion process of this invention is specifically as follows:

[0054] The aforementioned JSON format storage is optimized to array storage, and stored in two data tables. The `header_index` table records header information using JSON format keys, including the site's sequence number, the sequence number of each device at each site, and the TagName sequence number for each device. The field types for this table can be either JSON or Text arrays.

[0055] The array_value table is used to store port information. It uses an Array type field to store port information data, and this Array type field only stores numeric text arrays. All equipment data for each port is stored in a second-dimensional Array type field, and this field only stores floating-point numeric arrays.

[0056] When a new device or a new measurement point is added to a device, only a new record is inserted into the header_index table to express the order relationship of the values ​​in the array. This design realizes the time-series versioning management of the index relationship and can well support the dynamic changes of the data pattern.

[0057] The advantages of the solution provided by this invention are: all data is integrated and stored in a single large table, allowing direct querying of data for all sites and all devices under each site based on timestamps, eliminating the need for multi-table joins during each query. Furthermore, when using JSON and array formats for storage, the database's TOAST mechanism can be triggered when dealing with large amounts of data, thus significantly saving storage space.

[0058] Query optimization includes:

[0059] Index Query: Based on the query conditions (such as time T, port P1, equipment D1, measurement point M1), quickly query the header_index table to find the unique seq_id that meets the time range conditions and the business semantics.

[0060] Numerical access: Using the obtained seq_id as the array index, directly access the data corresponding to time T in the value_matrix table.

[0061] Example 2

[0062] This invention discloses a digital port data storage and analysis device, which can be deployed on a database server or application server, and includes the following functional modules:

[0063] Data Modeling Module: This module executes the data modeling steps. It provides a configuration interface or API for users or the system to initialize and define the logical model of the port and equipment. Its core function is to generate or manage the initial structure definitions of the port information table and each equipment table in the target database system based on the configuration, providing metadata templates for data acquisition.

[0064] Unified Storage Module: Used to perform unified storage steps. This module is the core of data processing and includes a format recognizer, a data fusion unit, and a writer.

[0065] Format recognizer: Determines whether to use JSON or array format based on the configuration.

[0066] Data Fuder (JSON Path): If JSON format is used, this component reads data from the data source and constructs a nested JSON object as described in Example 1 according to a predefined template.

[0067] Data Fusion Unit (Array Path): If an array format is used, this component works in conjunction with the Index Management submodule.

[0068] Geometric compression unit: Integrated in the fusion unit, it calls a geometric computing library (such as PostGIS) to compress and encode the identified linearly changing data sequences.

[0069] Writer: Writes the merged data (JSON object or numerical array) to the corresponding unified data.

[0070] Dynamic Expansion Module: Used to execute dynamic expansion steps. This module listens for configuration change events in the business system (such as adding or deleting devices / measurement points).

[0071] For JSON format, the module converts new device or measurement point information into JSON key-value pair definitions and notifies the unified storage module to apply the new template during subsequent data fusion.

[0072] For array formats, the module directly inserts new index records into the header_index table and manages when they take effect. This module ensures that all expansion operations do not trigger ALTER commands on the database table structure.

[0073] Query Optimization Module: This module performs query optimization steps. It includes a partition manager, an index manager, and a query router.

[0074] Partition Manager: Automatically creates and manages partitions for a unified data table based on configured time intervals (such as day or month).

[0075] Index Manager: Responsible for creating and maintaining various types of indexes. For example, creating GIN indexes for JSONB fields, creating B-Tree indexes for timestamp fields, and monitoring index performance.

[0076] Hot and Cold Storage Manager: Manages the data lifecycle of hot and cold data tables. This includes developing data migration strategies to move data that meets certain criteria from hot tables to cold tables, and routing queries to the correct physical table based on time conditions.

[0077] Query Router / Rewriter: Receives query requests from upper-layer applications. For queries in array format, it may rewrite them into a "two-query" mode as described in Example 2 to optimize the execution plan.

[0078] In this embodiment, the modules interact with each other through an internal bus or service interface to exchange data and control information, and work together to complete the entire process of automated management from data access, integrated storage, dynamic adaptation to efficient querying.

[0079] It is understood that the present invention has been described through some embodiments, and those skilled in the art will recognize that various changes or equivalent substitutions can be made to these features and embodiments without departing from the spirit and scope of the invention. Furthermore, under the teachings of the present invention, these features and embodiments can be modified to adapt to specific situations and materials without departing from the spirit and scope of the invention. Therefore, the present invention is not limited to the specific embodiments disclosed herein, and all embodiments falling within the scope of the claims of this application are within the protection scope of the present invention.

Claims

1. A method for storing and analyzing data in a digital port, characterized in that, Includes the following steps: S1. Establish a port / plant information table and multiple independent equipment tables for the digital port. The port / plant information table includes port ID, timestamp, name and plant information fields; each equipment table corresponds to one device and includes equipment ID, timestamp, name and multiple measurement point value fields. S2. The data from the port / plant information table and all equipment tables are merged into a JSON format or an array format according to the timestamp and then stored in a unified data table. S3. When new equipment or new measurement points are added to the equipment, the new data is stored by expanding the data format structure in the unified data table; S4. Create timestamp-based partitions for the unified data table and create indexes for specific fields to support fast retrieval.

2. The digital port data storage and analysis method according to claim 1, characterized in that, In step S2, data fusion and storage are performed using multidimensional JSON format, specifically as follows: Using timestamps as indexes, port / plant information data is stored as a first-level JSON object; Data from all equipment belonging to the port / plant is encapsulated into sub-JSON objects, which are then stored together as a second-layer JSON structure within the first-layer JSON object.

3. The digital port data storage and analysis method according to claim 1, characterized in that, The S2 uses a multidimensional array format for data fusion and storage, specifically: Create a header information and index record table to sequentially record the identifiers of ports, equipment, and measuring points and their corresponding array index relationships; A full data storage table is established, in which the array field order of the data rows strictly corresponds to the index relationship defined in the table header information and index record table; the array field is a multi-dimensional array, and its dimensions correspond to the port, equipment and measurement point values ​​in sequence.

4. The digital port data storage and analysis method according to claim 2, characterized in that... In step S4, a GIN index or a GPText full-text search index is established for the fields storing the multidimensional JSON format data.

5. The digital port data storage and analysis method according to claim 3, characterized in that... The header information and index record table configures an effective time range for each record item; when the device or measurement point changes, the change of index relationship is managed by adding a new record item and updating its effective time range.

6. The digital port data storage and analysis method according to claim 1, characterized in that... In step S2, for data sequences with relatively linear changes, they are stored using geometry or geometry array format.

7. The digital port data storage and analysis method according to claim 1, characterized in that... In step S4, the data in the unified data table is split into a hot data table and a cold data table; the hot data table is optimized for frequent queries through partitioning and indexing, while the cold data table is optimized for storage space.

8. A digital port data storage device for implementing the method of any one of claims 1-7, characterized in that, include: The data modeling module is used to create a port / plant information table and multiple independent equipment tables for the digital port. The unified storage module is used to merge the data from the port / plant information table and all equipment tables according to timestamps and store them in a unified data table. The dynamic expansion module is used to store new data by expanding the format structure of the data in the unified data table when new devices or new measurement points are added to the devices, without modifying the table structure definition of the unified data table. The query optimization module is used to create timestamp-based partitions for the unified data table and to create indexes for specific fields to support fast retrieval.

Images