Industrial data acquisition and transfer method based on PostgreSQL

Abstract

The invention provides an industrial data collection and transfer method based on PostgreSQL, and relates to the technical field of industrial data collection and processing, and the method comprises the steps: collecting structured and unstructured data of an industrial site through one or more modes of interface docking, database docking, file docking or communication protocol docking; a row-level security policy of PostgreSQL is configured, the access permission of a user to sensitive data rows is limited, sensitive column data is subjected to column-level encryption processing, and an SSL encryption function is started in the data transmission process; an InfluxDB plug-in is integrated to carry out compression storage and query on time sequence data, real-time aggregation calculation is realized based on PipelineDB extension, and millisecond-level window calculation is supported; defining and applying data formats, cleaning, converting, verifying and naming rules, and eliminating data isomerism; a data access interface is dynamically generated through a strategy mode and an abstract factory, data processing logic is decoupled in combination with a chain-of-responsibility mode, and a unified standardized data service interface is provided.

Description

Technical Field

[0001] The present application belongs to the technical field of industrial data collection and processing, and specifically relates to an industrial data collection and transfer method based on PostgreSQL. Background Art

[0002] With the rapid development of industrial internet technologies, modern factory production sites generate massive amounts of heterogeneous data constantly. This data originates from multiple sources, including sensors, intelligent devices, production control systems, and management systems, creating a complex, multi-source, heterogeneous data environment. Traditional industrial data collection methods typically communicate directly with production equipment and systems, such as connecting to PLC point addresses or synchronizing MES system data in real time through industrial gateways. However, due to the varying communication protocols and data formats across different devices and systems, traditional methods often require customized development for each data source. This not only increases development costs but also reduces system flexibility and struggles to meet the storage and processing requirements of large-scale industrial data collection scenarios. Furthermore, traditional methods cannot provide adequate security in factory environments with stringent requirements for network security and data confidentiality.

[0003] The inventors discovered that traditional industrial data collection methods, requiring customization for different data sources, result in high costs and low flexibility. Existing methods also lack data access control and encrypted transmission, failing to effectively guarantee the security and confidentiality of industrial data. Traditional database solutions are inefficient when processing specific data types, such as time series data, and struggle to support high-performance requirements such as millisecond-level window computing. Summary of the Invention

[0004] This application provides an industrial data collection and transfer method based on PostgreSQL to solve one of the above technical problems.

[0005] The technical solutions adopted in this application are:

[0006] The present application embodiment provides an industrial data collection and transfer method based on PostgreSQL, comprising:

[0007] Collect structured and unstructured data from industrial sites through one or more of the following methods: interface docking, database docking, file docking, or communication protocol docking;

[0008] Configure PostgreSQL's row-level security policy to restrict user access to sensitive data rows, perform column-level encryption on sensitive columns, and enable SSL encryption during data transmission.

[0009] Integrate the InfluxDB plug-in to compress, store, and query time series data. Implement real-time aggregation calculations based on the PipelinDB extension and support millisecond-level window calculations.

[0010] Define and apply data format, cleaning, conversion, validation and naming rules to eliminate data heterogeneity;

[0011] Data access interfaces are dynamically generated through strategy patterns and abstract factories, and data processing logic is decoupled by combining the chain of responsibility pattern to provide a unified and standardized data service interface.

[0012] According to one embodiment of the present application, the row-level security policy is implemented through the CREATE POLICY statement of PostgreSQL, and the column-level encryption processing adopts the AES encryption algorithm extended by pgcrypto.

[0013] According to one embodiment of the present application, the strategy pattern defines different data query strategies through the @Strategy annotation, and the abstract factory dynamically generates a data access interface through the @Factory annotation.

[0014] According to one embodiment of the present application, the responsibility chain pattern connects data processing units in series through the HandlerChain class, and each processing unit includes data cleaning, data conversion and data aggregation modules.

[0015] According to one embodiment of the present application, the PipelineDB extension implements real-time aggregate computing through the CREATE CONTINUOUS VIEW statement and supports millisecond-level updates of sliding time windows.

[0016] According to one embodiment of the present application, the InfluxDB plug-in maps the PostgreSQL table to an InfluxDB time series data table through a CREATE FOREIGN TABLE statement.

[0017] According to an embodiment of the present application, the metadata index of the unstructured data further includes a data source identifier and a data version number, so as to support fast retrieval based on metadata.

[0018] A computer program product comprising instructions, when executed on a device, causes the device to execute the steps in the method.

[0019] A computer-readable storage medium stores a program, which implements the steps of the method when executed by a processor.

[0020] An electronic device comprises a memory, a processor and a program stored in the memory and executable on the processor, wherein the steps in the method are implemented when the processor executes the program.

[0021] Due to the adoption of the above technical solution, the beneficial effects achieved by this application are as follows:

[0022] This application can easily adapt to multiple data sources through interface docking (such as RESTful API, SOAP), database docking (such as JDBC, ODBC), file docking (such as CSV, Excel) and communication protocol docking (such as OPC UA, MQTT, Modbus-TCP), significantly reducing the need for custom development and improving the compatibility and flexibility of the system.

[0023] This application uses PostgreSQL's row-level security policy to limit access rights to sensitive data rows and processes sensitive information through column-level encryption. At the same time, SSL encryption is enabled during data transmission to ensure the security of data during network transmission, meeting the strict data confidentiality requirements of industrial scenarios.

[0024] This application integrates the InfluxDB plug-in to achieve efficient compressed storage and query of time series data. Combined with the PipelineDB extension, it achieves millisecond-level window calculation capabilities, solving the problem of low efficiency of traditional databases when processing large amounts of time series data. It is suitable for high-concurrency, low-latency data processing needs in industrial environments.

[0025] This application defines and applies standardized rules including data format conversion, cleaning, and verification, eliminating data heterogeneity, simplifying the upper-level application development process, and improving data consistency and availability.

[0026] This application dynamically generates data access interfaces through the strategy pattern and abstract factory, and decouples data processing logic in combination with the responsibility chain pattern, providing a unified set of standardized data service interfaces, which greatly simplifies the API calling method and reduces the system maintenance cost. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] 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:

[0028] Figure 1 A flowchart of an industrial data collection and transfer method based on PostgreSQL is provided in an embodiment of the present application. DETAILED DESCRIPTION

[0029] In order to more clearly illustrate the overall concept of the present application, a detailed description is given below in an illustrative manner in conjunction with the accompanying drawings.

[0030] The following description sets forth many specific details to facilitate a thorough understanding of the present application. However, the present application may also be implemented in other ways than those described herein, and therefore, the scope of protection of the present application is not limited by the specific embodiments disclosed below. It should be noted that the embodiments of the present application and the features of each embodiment may be combined with each other unless there is a conflict.

[0031] In this application, unless otherwise expressly specified and limited, a first feature "above" or "below" a second feature may be that the first and second features are in direct contact, or the first and second features are in indirect contact through an intermediate medium. In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in an appropriate manner in any one or more embodiments or examples.

[0032] Example 1

[0033] like Figure 1 As shown, a method for collecting and transferring industrial data based on PostgreSQL includes:

[0034] Collect structured and unstructured data from industrial sites through one or more of the following methods: interface docking, database docking, file docking, or communication protocol docking.

[0035] As mentioned above, data sources can include device sensors, control systems, and management systems, and data formats include but are not limited to structured data (such as data in relational database tables) and unstructured data (such as images and audio files). This process involves a variety of integration methods to meet the needs of different scenarios: interface integration (such as RESTful API or SOAP), database integration (such as JDBC or ODBC), file integration (CSV, Excel, etc.), and communication protocol integration that directly communicates with the device (such as OPC UA, MQTT, Modbus-TCP).

[0036] For example, suppose a factory's quality inspection system provides a RESTful API interface for obtaining the latest quality inspection results. This solution can obtain this data in real time by calling the API and import it into a PostgreSQL database for subsequent processing. For the manufacturing execution system (MES) within the factory, if it uses MySQL to store production planning information, this data can be synchronized to PostgreSQL through a JDBC connection for unified management and analysis. Some older equipment may only support exporting data as CSV files. In this case, this solution can download these files regularly via FTP or SMB protocols and parse the contents to extract data. In order to collect real-time operating status data on the PLC, a direct connection to the PLC can be established via the Modbus-TCP protocol to read and record key parameters such as temperature and pressure in real time.

[0037] It should be noted that in specific implementation scenarios, the above solution can also be used as a basis for enterprises to integrate data from different suppliers and systems. This solution not only supports the aforementioned connection methods, but can also be further expanded to other standard or proprietary communication protocols to ensure seamless integration of all necessary data sources.

[0038] In specific implementation scenarios, based on the above solution, an intelligent pre-processing module can be added to the data access stage, such as automatic identification of data types, format conversion, preliminary cleaning and other operations, so that the data entering the system is cleaner and more standardized, reducing the workload of subsequent processing.

[0039] In specific implementation scenarios, based on the above solution, taking into account network latency and bandwidth limitations, lightweight edge computing nodes can be deployed close to the data source to perform preliminary filtering and compression on the original data before uploading it to the central database. This can not only reduce the pressure on the central server, but also improve the response speed of the overall system.

[0040] Configure PostgreSQL's row-level security policy to restrict user access to sensitive data rows, perform column-level encryption on sensitive columns, and enable SSL encryption during data transmission.

[0041] As mentioned above, by configuring PostgreSQL's row-level security (RLS), you can precisely control access permissions for specific data rows by different users or roles. For particularly sensitive data columns, column-level encryption is used to further enhance data protection. SSL encryption is enabled during data transmission to prevent data theft or tampering over the network.

[0042] Row-level security policy: allows administrators to set fine-grained access control rules based on business needs to ensure that only authorized users can access specific data records.

[0043] Column-level encryption: Highly sensitive information such as device IDs and process parameters is encrypted and stored, making it difficult to decipher even if the database is accessed without authorization.

[0044] SSL encrypted transmission: ensures the security of data during network transmission, preventing man-in-the-middle attacks and other forms of network eavesdropping.

[0045] For example, in a production management system, operators can only view production data related to their production lines, while management can access data for all production lines. This role-based access control can be easily implemented through RLS policies.

[0046] Imagine that the temperature and pressure data sent by a PLC device in a factory is considered highly sensitive. To prevent leakage, this data is encrypted before being stored in a database. Only a specific application with the decryption key can read this data.

[0047] When the MES system needs to synchronize the latest production plan updates to the central database, SSL encryption is enabled on the entire communication link. This ensures that even if the data is intercepted during network transmission, it will not be exposed to unauthorized third parties.

[0048] It should be noted that, in specific implementation scenarios, the above solution can also be used to automatically adjust row-level security policies based on real-time risk assessment results. For example, when abnormal activity is detected, the system can temporarily increase the security level of certain data rows, restricting access to more users until the risk is resolved.

[0049] In specific implementation scenarios, in addition to column-level encryption, additional encryption layers at the file or field level can be introduced based on the above solution to provide more flexible security for different types of data. For example, image data can be compressed and then encrypted before storage, saving storage space and enhancing security.

[0050] In specific implementation scenarios, the above solutions can also be combined with modern authentication protocols such as OAuth2.0 or OpenIDConnect to enhance the security of user authentication, and seamlessly integrate with existing enterprise-level directory services (such as LDAP) to simplify the permission management process.

[0051] The InfluxDB plug-in is integrated to compress, store and query time series data, and the PipelineDB extension is used to implement real-time aggregation calculations, supporting millisecond-level window calculations.

[0052] As mentioned above, by integrating the InfluxDB plugin, you can leverage its efficient compression algorithm to store time-series data, reducing storage space while maintaining fast query performance. The PipelineDB extension allows the system to perform real-time streaming data processing tasks, such as sliding window aggregation and persistent views, enabling millisecond-level data analysis. This approach is particularly suitable for applications that require efficient analysis and immediate response to sensor data, device status information, and more.

[0053] InfluxDB plug-in: used for efficient compression storage and query of time series data, ensuring that large-scale time series data can be stored for a long time with minimized storage costs and that results can be returned quickly when queried.

[0054] PipelineDB extension: Provides powerful real-time data processing capabilities, including but not limited to sliding window calculations and time series aggregation, enabling the system to process and draw conclusions immediately as data flows in.

[0055] For example, consider a manufacturing plant with hundreds of temperature sensors generating new readings every second. Using the InfluxDB plugin, this massive amount of time-series data can be efficiently compressed and stored, while still enabling quick retrieval of historical data for trend analysis or troubleshooting.

[0056] To monitor the health of a production line within a factory, it may be necessary to monitor the status parameters of each device (such as temperature and pressure) in real time. With the PipelineDB extension, the average temperature over the past minute can be calculated as soon as data enters the database, and this value can be used to determine whether maintenance measures are necessary.

[0057] It should be noted that in specific implementation scenarios, in addition to the existing time-series data storage, a hot / cold data tiered storage strategy can be introduced based on the above solution. For example, data from the last few days (hot data) can be stored on high-speed SSDs for fast access, while older historical data (cold data) can be migrated to lower-cost high-capacity HDDs or cloud storage services. This ensures high-performance queries while reducing overall storage costs.

[0058] In specific implementation scenarios, the above solutions can be combined with machine learning algorithms to perform preliminary pre-aggregation operations during the data collection phase and predict data trends over the next period of time. This helps to identify potential problems in advance, such as predicting when equipment may fail, thereby arranging preventative maintenance.

[0059] In specific implementation scenarios, standardized interfaces can be developed based on the above solutions to enable seamless integration of this system with other third-party platforms (such as ERP and MES systems) to achieve two-way synchronization and sharing of data. This not only improves data utilization but also promotes efficient collaboration between departments within the enterprise.

[0060] Define and apply data formatting, cleaning, transformation, validation, and naming rules to eliminate data heterogeneity.

[0061] As mentioned above, data format: define unified data format standards, such as date format, number format, etc., to ensure the consistency and interoperability of data in the entire system.

[0062] Data cleaning: remove duplicate data, fill missing values, correct erroneous data, and other operations to improve data quality and accuracy.

[0063] Data conversion: Converting data from different sources into a unified standard format or unit, such as converting temperature from Fahrenheit to Celsius, or standardizing currency units into RMB.

[0064] Data validation: Set data validity check rules, such as range check, type check, etc., to ensure that only legal and valid data can enter the system.

[0065] Naming rules: Establish unified data field naming standards for easy understanding and use, such as using snake_case or camelCase naming.

[0066] These steps work together to effectively eliminate data heterogeneity, improve data consistency and availability, and thus simplify subsequent data analysis and application development processes.

[0067] For example, in a factory, different devices may output timestamps in different formats (such as YYYY-MM-DDHH:MM:SS or DD / MM / YYYY HH:MM). By defining a unified time format standard and applying it to all data sources, the consistency of time information in the system can be ensured.

[0068] Suppose a sensor occasionally sends duplicate readings or data records containing null values. Using cleansing rules, you can identify and remove duplicates while filling null values ​​with reasonable default values ​​to ensure the completeness and accuracy of the dataset.

[0069] If some devices report temperature data in Fahrenheit and other devices report temperature data in Celsius, you can use a conversion rule to convert all temperature data to Celsius for easier comparison and analysis.

[0070] For order data in a production management system, validation rules can be set to ensure that the order quantity is not negative and the amount does not exceed a certain limit, to prevent illegal data from affecting the normal operation of the system.

[0071] To facilitate cross-departmental data sharing, all field names are defined according to a unified naming convention. For example, "Customer ID" is uniformly named customer_id, rather than mixing different names such as clientID or custId.

[0072] It should be noted that in specific implementation scenarios, intelligent algorithms can be introduced based on the above solution to automatically identify the data structure of newly connected data sources and automatically generate corresponding conversion rules, reducing the workload of manual configuration. For example, when a new sensor is connected, the system can automatically detect its data format and convert it according to predefined rules.

[0073] In specific implementation scenarios, this approach can be combined with a feedback loop to allow the system to continuously optimize data cleaning and transformation rules based on actual operational performance. For example, if a particular type of error is found to be recurring frequently, the system can automatically adjust the relevant rules to better handle similar issues.

[0074] In specific implementation scenarios, based on the above solution, multi-language support can be provided to meet the needs of multinational companies, so that data field names and descriptions can automatically switch language versions according to the user's region, facilitating global team collaboration.

[0075] Data access interfaces are dynamically generated through strategy patterns and abstract factories, and data processing logic is decoupled by combining the chain of responsibility pattern to provide a unified and standardized data service interface.

[0076] As mentioned above, the Strategy pattern is used to define different data query or processing strategies and dynamically select the appropriate strategy based on specific needs. This allows the system to easily adapt to changing requirements in different business scenarios.

[0077] Abstract Factory Pattern: Used to create a series of related objects or interfaces without specifying a specific class. This allows for dynamic generation of data access interfaces, shielding underlying data storage differences and simplifying upper-level application development.

[0078] Chain of Responsibility pattern: Multiple processing units are connected in series, with each unit responsible for a specific data processing task (such as cleaning, conversion, aggregation, etc.), thereby decoupling the data processing logic. If a processing unit cannot complete the task, the request is passed to the next processing unit.

[0079] These design patterns work together to not only provide high flexibility and scalability, but also ensure the modularity and maintainability of the system, ultimately providing users with a unified and standardized data service interface.

[0080] For example, consider an industrial production management system that needs to provide different data views based on different user roles (e.g., operator, administrator). Using the policy pattern, you can define multiple query policies (e.g., operators can only view data for the current production line, while administrators can view data for all production lines). The system dynamically selects the appropriate policy based on the user's role and provides the corresponding data view.

[0081] In a multi-source, heterogeneous data environment, data may come from different databases (such as PostgreSQL and MySQL) or file formats (such as CSV and Excel). The Abstract Factory pattern allows you to create a universal data access interface. Regardless of the underlying data source type, upper-layer applications can use the same interface to access data without worrying about specific implementation details.

[0082] In a data processing workflow, data first needs to be cleaned (removing duplicates and filling missing values), then formatted (such as standardizing date formats), and finally validated (checking data validity). The Chain of Responsibility pattern allows these processing steps to be linked together to form a chain. If a step fails to process the data, the problem is automatically passed to the next processing unit until all steps are completed or fail.

[0083] It should be noted that in specific implementation scenarios, the above solution can also be used to allow users to add new data processing strategies or access interfaces through simple configuration or plug-in forms without modifying existing code. For example, when an enterprise introduces a new sensor device, its data access method can be quickly integrated through a plug-in, maintaining the flexibility and scalability of the system.

[0084] In specific implementation scenarios, building on the above solution, intelligent scheduling algorithms can be introduced in high-concurrency scenarios to dynamically allocate resources based on real-time load conditions and optimize data processing. For example, load balancing can be achieved across multiple data processing nodes to ensure that each node can operate efficiently and avoid overloading a single point.

[0085] In specific implementation scenarios, based on the above solution, you can also integrate a distributed cache (such as Redis) to improve query performance, especially when the same data is frequently accessed. By caching hot data, the number of direct database accesses is reduced, significantly improving response speed.

[0086] In some embodiments of the present application, the row-level security policy is implemented through the CREATE POLICY statement of PostgreSQL, and the column-level encryption processing adopts the AES encryption algorithm extended by pgcrypto.

[0087] As mentioned above, row-level security policies in PostgreSQL allow database administrators to define fine-grained access control rules for specific tables. This means that different access permissions can be set for each row of data, ensuring that only authorized users or roles can access specific data records. This policy is particularly suitable for scenarios where strict control over data access is required, such as in industrial production environments where different departments or personnel can only access data relevant to their responsibilities.

[0088] CREATE POLICY Statement: Use the CREATE POLICY statement to define specific access control rules. This statement allows you to specify which users or roles can read, insert, update, or delete certain rows under what conditions. For example, you can create a policy that only employees in the quality inspection department can view the quality inspection results of a specific production line.

[0089] Example: CREATE POLICY quality_check_policy ON production_data FOR SELECTTO quality_team USING(department='QualityControl');

[0090] This statement defines a policy called quality_check_policy, which is applied to the production_data table and only allows the quality_team role to perform query operations when the department='QualityControl' condition is met.

[0091] Column-level encryption encrypts sensitive columns (such as device IDs and process parameters) within database tables, preventing unauthorized users from directly accessing this information, even if they have database access permissions. PostgreSQL provides powerful encryption capabilities through the pgcrypto extension, supporting multiple encryption algorithms. AES (Advanced Encryption Standard) is a widely used symmetric encryption algorithm, widely recognized for its efficiency and security.

[0092] The pgcrypto extension and the AES encryption algorithm: First, enable the pgcrypto extension. You can then use the functions it provides to encrypt and decrypt data in specific columns. The AES encryption algorithm is often used to protect highly sensitive information, ensuring that data is encrypted during storage and can only be decrypted and read by applications or users with the correct key.

[0093] For example, suppose there is a column containing device IDs that needs to be stored encrypted. You can use the encrypt function provided by pgcrypto to encrypt it and the decrypt function to decrypt it.

[0094] Encryption example: UPDATE device_data SET encrypted_device_id=pgp_sym_encrypt(device_id::text,'aes_key')WHERE id=12345;

[0095] Decryption example: SELECT pgp_sym_decrypt(encrypted_device_id::bytea,'aes_key')FROM device_data WHERE id=12345;

[0096] The above command will store the device ID column in encrypted form and decrypt it when needed. This way, even if the database is accessed without authorization, sensitive data remains secure.

[0097] By combining row-level security policies and column-level encryption processing, this solution provides a multi-level security mechanism that can not only precisely control data access rights but also effectively protect sensitive information, ensuring the security and confidentiality of industrial data throughout its entire life cycle.

[0098] In some embodiments of the present application, the strategy pattern defines different data query strategies through the @Strategy annotation, and the abstract factory dynamically generates a data access interface through the @Factory annotation.

[0099] As mentioned above, in order to achieve unified access and flexible processing of multi-source heterogeneous data, the strategy pattern and abstract factory pattern in object-oriented design are introduced, and custom annotations (such as @Strategy and @Factory) are used to enhance the configurability and scalability of the system.

[0100] The Strategy pattern is used to encapsulate different business rules or processing logic and allows the appropriate policy execution to be selected at runtime based on the context. In this solution, this pattern is primarily used to define and manage different types of data query policies, such as querying by time range, querying by device type, and querying by data source.

[0101] By marking a class as a specific query strategy implementation through the @Strategy annotation, the system can automatically match and call the corresponding strategy based on the request parameters.

[0102] This approach allows adding or modifying query strategies without changing the original logic code. All you need to do is add a new strategy class and annotate it with the corresponding annotation, which improves the flexibility and maintainability of the system.

[0103] The Abstract Factory pattern is a creational design pattern used to construct a family of related or dependent objects without specifying specific classes. In this solution, the pattern is used to dynamically generate data access interfaces for different data sources, thereby masking differences in underlying storage and providing a unified calling method for upper-level applications.

[0104] By using the @Factory annotation to identify a class as the creator of the data access interface, the system can dynamically instantiate the corresponding data access implementation based on the current data source type (such as PostgreSQL, InfluxDB, or other databases).

[0105] The abstract factory encapsulates the specific implementation details so that the upper-level modules do not need to care about which database or data format is used by the underlying layer. They only need to call a unified interface to complete the operation.

[0106] Taken together, the strategy pattern is responsible for selecting the appropriate data processing method based on different business needs, while the abstract factory pattern is responsible for generating an adaptive access interface based on the data source type. The two work together to support a highly decoupled, easy-to-scalable, and adaptable data service architecture.

[0107] In some embodiments of the present application, the responsibility chain pattern connects data processing units in series through the HandlerChain class, and each processing unit includes data cleaning, data conversion and data aggregation modules.

[0108] As mentioned above, the Chain of Responsibility pattern is employed to achieve a flexible and efficient data processing workflow. This design pattern allows multiple processing units (or processors) to be connected in series, forming a chain. Each processing unit is responsible for a specific type of data processing task, such as data cleansing, data transformation, and data aggregation. If a processing unit is unable to complete its task, the request is passed to the next processing unit in the chain until all processing units have completed their task or a suitable processing unit is found.

[0109] Chain of Responsibility pattern: The core idea of ​​this pattern is to organize a series of processing logic into a chain structure, so that each handler has the opportunity to handle the request. If the current handler cannot handle the request (that is, if certain conditions are not met), the request is passed to the next handler in the chain.

[0110] HandlerChain class: In this solution, the HandlerChain class acts as the chain of responsibility manager, responsible for creating and maintaining the chain of processing units. It provides a mechanism to dynamically add, remove, or adjust the order of processing units, making the entire data processing process highly flexible and configurable.

[0111] Each processing unit in the chain undertakes specific data processing tasks, including but not limited to the following categories:

[0112] Data cleaning module: Ensures data quality and consistency. Specific operations include removing duplicate data, filling missing values, and repairing erroneous data.

[0113] In a production environment, sensors may occasionally send duplicate temperature readings. The data cleansing module can identify and remove these duplicate records, ensuring that subsequent analysis is based on an accurate data set.

[0114] Data conversion module: converts data from different sources into a unified standard format or unit for further processing and analysis.

[0115] Suppose a device reports temperature data in Fahrenheit, but the system requires Celsius as the standard unit. The data conversion module converts all temperature data from Fahrenheit to Celsius to ensure data consistency and comparability.

[0116] Data Aggregation Module: Aggregates and calculates large amounts of raw data to generate more meaningful information. Common operations include sliding window average calculation and time series aggregation.

[0117] For real-time monitoring data on the production line, the data aggregation module can calculate the average operating status parameters (such as temperature and pressure) of each device in the past hour, helping managers quickly understand the overall production situation.

[0118] When a new data request arrives, the HandlerChain class first determines which processing unit should start processing the request.

[0119] Each processing unit determines whether it can handle the current request based on its own responsibilities. If it can, it performs the corresponding operation; if not, it passes the request to the next processing unit in the chain.

[0120] This process continues until all processing units have attempted to process the request or a processing unit has successfully completed the processing task.

[0121] Ultimately, the processed data is passed to downstream systems or stored in databases for subsequent analysis and decision-making.

[0122] By adopting the Chain of Responsibility pattern and utilizing the HandlerChain class to connect data processing units in series, this paper provides a flexible and scalable data processing framework. This architecture not only improves the system's modularity and reduces the coupling between processing units, but also makes it easy to add or modify processing logic, adapting to new business needs without making significant changes to existing code. Furthermore, it supports dynamic adjustment of the order and combination of processing units to address ever-changing data processing requirements.

[0123] In some embodiments of the present application, the PipelineDB extension implements real-time aggregate calculations through the CREATE CONTINUOUS VIEW statement and supports millisecond-level updates of sliding time windows.

[0124] As mentioned above, to enable real-time processing and analysis of industrial data, the PipelineDB extension was used. PipelineDB is a PostgreSQL extension specifically designed for streaming data processing and real-time aggregate calculations. It allows users to define continuous views, which automatically process incoming data streams and perform real-time aggregate calculations based on predefined rules.

[0125] PipelineDB extension: The PipelineDB extension adds stream processing capabilities to the PostgreSQL database, enabling the database to not only process static data but also efficiently handle continuously flowing data streams. This is particularly important in industrial scenarios that require real-time monitoring and analysis of large amounts of sensor data or device status information.

[0126] CREATE CONTINUOUS VIEW Statement: CREATE CONTINUOUS VIEW is a key command provided by PipelineDB for creating persistent views. Persistent views are similar to traditional relational views, but rather than being snapshots of one-time query results, they continuously ingest new data from the data stream and perform real-time calculations and updates.

[0127] Using the CREATE CONTINUOUS VIEW statement, you can define specific aggregation logic, such as calculating averages, sums, and counts. These aggregation operations are continuously updated as new data flows in, providing the latest statistical results.

[0128] Suppose that in a production environment, you need to monitor the temperature changes on a production line in real time. You can use the following SQL statement to create a persistent view to calculate the average temperature over the past minute:

[0129] CREATE CONTINUOUS VIEW avg_temperature AS

[0130] SELECT device_id,

[0131] time_bucket('1minute',timestamp)AS bucket,

[0132] AVG(temperature)AS avg_temp

[0133] FROM temperature_data_stream

[0134] GROUP BY device_id, bucket;

[0135] The time_bucket function groups the timestamps by minute and then calculates the average temperature within each group. Whenever new temperature data enters the temperature_data_stream table, the persistent view automatically updates the corresponding average temperature value.

[0136] In addition to basic aggregation calculations, PipelineDB also supports sliding time windows, which enable the system to dynamically update statistical data within very short intervals (e.g., milliseconds). Sliding time windows allow users to define a fixed time range and continuously scroll within it, capturing data trends in real time.

[0137] Sliding time windows: In some application scenarios, you may need to quickly analyze data from a recent period, rather than aggregating data over a fixed time period. For example, when monitoring device health, you may want to know the average load over the past five seconds. Setting a sliding time window can meet this requirement.

[0138] Millisecond-level updates: Industrial data is often highly frequent, requiring data processing and updates to be completed in record time. PipelineDB is designed to respond to changes in data streams within milliseconds, ensuring that statistical results remain up-to-date.

[0139] To achieve millisecond-level updates for the sliding time window, you can adjust the time unit in the above SQL statement. For example, to calculate the average temperature in the past 5 seconds:

[0140] CREATE CONTINUOUS VIEW sliding_avg_temperature AS

[0141] SELECT device_id,

[0142] time_bucket('5seconds',timestamp)AS bucket,

[0143] AVG(temperature)AS avg_temp

[0144] FROM temperature_data_stream

[0145] GROUP BY device_id, bucket;

[0146] This continuous view automatically updates the average temperature value within a 5-second time window, ensuring real-time and accurate data analysis.

[0147] By leveraging the PipelineDB extension and its CREATE CONTINUOUS VIEW statement, this solution implements real-time aggregate computations on industrial data streams and supports millisecond-level updates within sliding time windows. This approach not only improves data processing efficiency but also enhances the system's real-time responsiveness, making it suitable for a variety of industrial scenarios requiring immediate feedback and decision support.

[0148] In some embodiments of the present application, the InfluxDB plug-in maps a PostgreSQL table to an InfluxDB time series data table through a CREATE FOREIGN TABLE statement.

[0149] As mentioned above, the InfluxDB plug-in was introduced to improve the storage and query efficiency of large amounts of time-series data generated in industrial sites (such as sensor data and equipment operating status). This plug-in, acting as a PostgreSQL foreign data wrapper (FDW), can map regular tables in PostgreSQL to time-series data tables in InfluxDB, enabling efficient management of time-series data.

[0150] Specifically, the system uses the CREATE FOREIGN TABLE statement provided by PostgreSQL to create an external table. This external table logically matches the internal PostgreSQL table structure but physically points to the corresponding time series data table in InfluxDB. This mechanism allows users to use standard SQL statements in the PostgreSQL environment to operate on time series data, while InfluxDB is responsible for efficiently storing and retrieving the underlying data.

[0151] CREATE FOREIGN TABLE statement: This is the standard syntax for defining external data sources in PostgreSQL. This statement allows you to declare a table structure and specify that its data comes from an external system (here, InfluxDB). For example:

[0152] CREATE FOREIGN TABLE sensor_data(

[0153] time TIMESTAMP,

[0154] device_id TEXT,

[0155] temperature DOUBLE PRECISION,

[0156] humidity DOUBLE PRECISION )

[0158] SERVER influxdb_server

[0159] OPTIONS(database'factory_sensors',retention_policy'autogen');

[0160] The above statement defines an external table named sensor_data with fields including timestamp, device ID, temperature, and humidity. This data is actually stored in the factory_sensors database in InfluxDB. This allows users to perform queries and aggregations on this data directly in PostgreSQL without having to worry about the underlying data storage details.

[0161] InfluxDB Time Series Data Tables: InfluxDB is a database designed specifically for processing time series data. It features efficient compression, fast time range query performance, and good write throughput. Combining it with time-sensitive industrial data can significantly improve data processing efficiency.

[0162] Through the above approach, this solution achieves seamless integration between PostgreSQL and InfluxDB, leveraging the strengths of both: PostgreSQL provides a unified data access interface and transaction support, while InfluxDB specializes in high-performance time series data processing. This architecture not only improves overall system performance but also provides strong technical support for subsequent business functions such as real-time analysis and historical data backtracking.

[0163] In some embodiments of the present application, the metadata index of the unstructured data further includes a data source identifier and a data version number, so as to support fast retrieval based on metadata.

[0164] As mentioned above, a structured management approach is adopted for unstructured data collected at industrial sites (such as images, audio, and log files). This approach improves manageability and searchability by appending metadata indexes to unstructured data. In addition to basic information such as timestamps and device IDs, the metadata indexes further include two key fields: data source identifier and data version number. This enhances the system's ability to identify, track, and retrieve unstructured data.

[0165] Data source identification: This records the original location or collection path of unstructured data, such as the production line, device, sensor, or external system from which it was imported. This identification allows accurate tracing of the data's source during subsequent data management and analysis, facilitating data traceability, quality control, and multi-source data fusion analysis.

[0166] Data version numbers are used to identify changes in the same type of unstructured data at different points in time or during different processing stages. For example, a photo of an equipment inspection might be the original version when initially collected, and then an optimized version generated after image enhancement. Each version should have a unique version number. By introducing a version number mechanism, data content in different states can be effectively distinguished, avoiding confusion and supporting the tracing and comparative analysis of historical versions.

[0167] These two pieces of information are stored as part of the metadata, alongside the unstructured data itself, in the PostgreSQL database, typically as separate index tables or extension fields. When searching for data, the system can quickly locate the required data by querying these metadata fields, without having to scan the entire unstructured data, significantly improving search efficiency.

[0168] In addition, combined with the database's indexing mechanism (such as B-tree index or GIN / GiST index), the system can establish an efficient index structure for these metadata fields, further supporting metadata-based fast retrieval, conditional filtering, and batch query functions, meeting the management requirements for high availability and strong controllability of unstructured data in industrial scenarios.

[0169] A computer program product comprising instructions, when executed on a device, causes the device to execute the steps in the method.

[0170] A computer-readable storage medium stores a program, which implements the steps of the method when executed by a processor.

[0171] An electronic device comprises a memory, a processor and a program stored in the memory and executable on the processor, wherein the steps in the method are implemented when the processor executes the program.

[0172] Anything not described in this application can be achieved by adopting or drawing on existing technologies.

[0173] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other. Each embodiment focuses on the differences from other embodiments.

[0174] 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. A method for collecting and transferring industrial data based on PostgreSQL, characterized in that: include: Collect structured and unstructured data from industrial sites through one or more of the following methods: interface docking, database docking, file docking, or communication protocol docking; Configure PostgreSQL's row-level security policy to restrict user access to sensitive data rows, perform column-level encryption on sensitive columns, and enable SSL encryption during data transmission. Integrate the InfluxDB plug-in to compress, store, and query time series data, implement real-time aggregation calculations based on the PipelineDB extension, and support millisecond-level window calculations; Define and apply data format, cleaning, conversion, validation and naming rules to eliminate data heterogeneity; Data access interfaces are dynamically generated through strategy patterns and abstract factories, and data processing logic is decoupled by combining the chain of responsibility pattern to provide a unified and standardized data service interface.

2. The method according to claim 1, characterized in that The row-level security policy is implemented through the CREATE POLICY statement of PostgreSQL, and the column-level encryption process adopts the AES encryption algorithm extended by pgcrypto.

3. The method according to claim 1, characterized in that The strategy pattern defines different data query strategies through the @Strategy annotation, and the abstract factory dynamically generates data access interfaces through the @Factory annotation.

4. The method according to claim 1, wherein The responsibility chain model connects data processing units in series through the HandlerChain class, and each processing unit includes data cleaning, data conversion and data aggregation modules.

5. The method according to claim 1, wherein The PipelineDB extension implements real-time aggregate calculations through the CREATE CONTINUOUS VIEW statement and supports millisecond-level updates of sliding time windows.

6. The method according to claim 1, characterized in that The InfluxDB plug-in maps the PostgreSQL table to an InfluxDB time series data table through the CREATE FOREIGNTABLE statement.

7. The method according to claim 1, characterized in that The metadata index of the unstructured data also includes a data source identifier and a data version number, which is used to support fast retrieval based on metadata.

8. A computer program product comprising instructions, which, when executed on a device, is characterized in that: The device is enabled to execute the steps in the method according to any one of claims 1 to 7.

9. A computer-readable storage medium having a program stored thereon, characterized in that: When the program is executed by a processor, the steps in the method according to any one of claims 1 to 7 are implemented.

10. An electronic device comprising a memory, a processor, and a program stored in the memory and executable on the processor, wherein: When the processor executes the program, the steps in the method according to any one of claims 1 to 7 are implemented.

Images