Design And Implementation Of Smart Gas Database Based On IoT

With the rapid development of urbanization,IoT smart gas applications with household metering and remote reading are receiving more and more attention from government and enterprises.Gas companies use the Internet of Things technology to collect and upload gas data to servers through gateways.After the data is parsed and stored,the gas companies provide users with various services.Because there are more and more data centralized communications and high concurrent performance,the system's read and write performance is reduced.It is intuitively displayed that the response time for users to obtain services is long and the experience is bad.Therefore,in order to improve the response speed of data operations,improve the user experience and meet the actual application requirements,this paper conducts in-depth research and design of smart gas database.Firstly,this paper investigates and analyzes the current business needs of smart gas.Then,this paper establishes requirements models,RQM,and adopts the New Orleans design method to establish conceptual data models,CDM,and physical data models,PDM.Through the horizontal comparison analysis,MySQL is selected as the system database,and JAVA is selected as the system programming language.Therefore,object-oriented models,OMM,are established and the data forms of database are designed.Secondly,this paper analyzes the characteristics of smart gas data and proposes that smart gas data are divided into "basic data" and "persistent data" according to their characteristics.That is,the data such as user information,address information,and appliance information that existed before the system went online was classified as"basic data";the data such as meter reading information,alarm information,and account information that were continuously generated during online operation of the system was classified as "persistent data".For "basic data",this paper designs a strategy that Excel file converts comma-separated values file,CSV,and verifies through experiments that it is more efficient than stored by database procedures;for"persistent data",this paper designs a strategy that the database connection pool,c3p0,combined with the distributed streaming platform,Kafka,and proves its feasibility through experiments.Thirdly,in order to improve the query efficiency of gas data and improve the users' experience during the use process of users,this paper uses multiple methods for gas data query optimization which includes selecting the appropriate form fields for indexing,reconstructing the structured query language,SQL,making storage of form segmentation,writing SQL standardization tools and configuring MySQL query cache.Through experimental comparison,it is proved that the optimized database can significantly improve the data query efficiency.Finally,considering that the designed database in the process of operation,it may encounter the loss of gas data due to system hardware and software failures.It may cause unnecessary losses and even security risks to users and businesses.This paper uses a full-backup strategy for cold backup locally and hot backup on the cloud.Cold backup locally uses one-click scripting to back up the "basic data".Hot backup on the cloud uses the Database Backup(DBS)of Alibaba Group's Ali-Cloud service to back up "persistent data." Through experiments,the scenario of database recovery after a failure is simulated,and lost data are successfully recovered,which increases the practicability and security of the database.The pressure test of the designed intelligent gas database shows that the designed database achieves the desired goal.